2025年mysql数据库期末大学考试题及答案

2025年mysql数据库期末大学考试题及答案一、单项选择题（共15题，每题1分，共15分）1.MySQL8.0版本默认的存储引擎是？A.MyISAMB.InnoDBC.MemoryD.CSV答案：B解析：MySQL5.5版本后将InnoDB设为默认存储引擎，8.0版本进一步废弃了MyISAM的系统表，InnoDB支持事务、行级锁、外键、崩溃安全恢复等特性，适配绝大多数OLTP场景。2.MySQL哪个版本开始支持原子DDL，解决了DDL执行中途失败残留垃圾文件的问题？A.5.7B.8.0C.5.6D.8.1答案：B解析：MySQL8.0引入原子DDL特性，将DDL操作涉及的元数据变更、数据文件操作、binlog写入封装为原子事务，要么全部执行成功，要么完全回滚，保证了数据字典的一致性。3.存储国内11位手机号，以下哪种数据类型最合适？A.INTUNSIGNEDB.BIGINTC.CHAR(11)D.VARCHAR(20)答案：C解析：手机号属于标识类数据，不参与数值运算，固定长度为11位，使用CHAR(11)存储检索效率更高，同时避免数值类型导致的前导0丢失问题；INTUNSIGNED最大仅支持存储10位数字，无法覆盖11位手机号。4.标准SQL规范下，以下哪个事务隔离级别可以避免脏读、不可重复读，但存在幻读风险？A.READUNCOMMITTEDB.READCOMMITTEDC.REPEATABLEREADD.SERIALIZABLE答案：C解析：READUNCOMMITTED存在脏读、不可重复读、幻读；READCOMMITTED避免脏读，存在不可重复读、幻读；REPEATABLEREAD避免脏读、不可重复读，标准SQL规范下仍存在幻读，InnoDB通过MVCC+间隙锁机制解决了幻读问题；SERIALIZABLE可以避免所有事务并发问题，但性能极低。5.以下哪种索引可以避免回表操作，直接从索引节点获取查询所需的全部字段？A.普通索引B.唯一索引C.覆盖索引D.全文索引答案：C解析：覆盖索引的叶子节点包含了查询需要的所有字段，不需要回表到聚簇索引查询完整行数据，大幅提升查询效率。6.执行`SELECT*FROMtableAaLEFTJOINtableBbONa.id=b.a_idWHEREb.idISNULL`，该语句的查询结果是？A.tableA和tableB的所有关联记录B.tableA中在tableB没有关联匹配的记录C.tableB中在tableA没有关联匹配的记录D.tableA和tableB的所有无关联记录答案：B解析：左连接保留左表的所有行，右表无匹配的字段为NULL，加WHEREb.idISNULL筛选后，即可得到左表中未关联到右表的记录。7.MySQL8.0提供的窗口函数中，哪个函数可以实现排名功能，相同值排名不跳号？A.RANK()B.ROW_NUMBER()C.PERCENT_RANK()D.DENSE_RANK()答案：D解析：RANK()相同值排名相同，后续排名跳号；ROW_NUMBER()无论值是否相同，排名连续唯一；DENSE_RANK()相同值排名相同，后续排名不跳号。8.InnoDB的行级锁是基于什么实现的？A.行记录B.索引项C.数据页D.表空间答案：B解析：InnoDB行锁通过锁住索引项实现，如果SQL执行没有命中索引，会升级为表级锁，导致并发性能大幅下降。9.慢查询日志参数`long_query_time`的单位是？A.微秒B.毫秒C.秒D.分钟答案：C解析：该参数默认值为10，代表执行时间超过10秒的SQL会被记录到慢查询日志，生产环境通常调整为1秒或0.5秒。10.以下关于DELETE和TRUNCATE的描述，错误的是？A.DELETE是DML语句，TRUNCATE是DDL语句B.DELETE可以回滚，TRUNCATE不可回滚C.TRUNCATE会重置表的自增主键值，DELETE不会D.批量删除全表数据时，DELETE效率高于TRUNCATE答案：D解析：TRUNCATE直接删除表的数据文件，不需要逐行记录日志，删除全表数据的效率远高于DELETE。11.MySQL主从复制架构中，运行在主库上的线程是？A.BinlogDump线程B.IO线程C.SQL线程D.Worker线程答案：A解析：主库的BinlogDump线程负责读取binlog事件并发送给从库；从库的IO线程负责接收binlog并写入中继日志，SQL线程负责重放中继日志的事件。12.以下哪种约束可以保证字段值唯一且不允许为NULL？A.UNIQUEB.PRIMARYKEYC.NOTNULLD.FOREIGNKEY答案：B解析：UNIQUE约束允许字段存在多个NULL值，PRIMARYKEY同时具备唯一和非空约束，一个表仅能有一个主键。13.以下哪种方式可以有效预防SQL注入？A.直接拼接用户输入的参数到SQL语句B.开启慢查询日志C.使用预处理语句（PreparedStatement）D.给数据库用户授予ALL权限答案：C解析：预处理语句将SQL逻辑和参数分离，用户输入的参数会被当作字符串处理，不会被解析为SQL指令，从根源上避免SQL注入。14.EXPLAIN执行计划的输出字段中，哪个表示SQL执行时扫描的行数？A.typeB.keyC.rowsD.Extra答案：C解析：type表示访问类型，性能从好到坏为system>const>eq_ref>ref>range>index>ALL；key表示实际用到的索引；rows表示预估扫描的行数，数值越小性能越好。15.MySQL8.0版本默认的字符集是？A.utf8B.latin1C.utf8mb4D.gbk答案：C解析：utf8mb4支持存储emoji表情等4字节Unicode字符，8.0版本之前默认字符集为latin1，5.7版本可手动修改为utf8mb4，8.0版本默认设为utf8mb4。二、多项选择题（共10题，每题2分，共20分，漏选得1分，错选不得分）1.以下属于InnoDB存储引擎特性的有？A.支持事务ACID特性B.支持行级锁C.支持外键约束D.支持全文索引答案：ABCD解析：InnoDB从5.6版本开始支持全文索引，8.0版本对全文索引的性能做了大幅优化，已经超过MyISAM的全文索引性能。2.以下属于MySQL逻辑备份工具的有？A.mysqldumpB.xtrabackupC.mysqlpumpD.直接复制ibd数据文件答案：AC解析：逻辑备份导出的是SQL语句或结构化数据文件，可读性高，mysqldump是单线程逻辑备份工具，mysqlpump是5.7版本推出的并行逻辑备份工具；xtrabackup和复制数据文件属于物理备份，备份的是底层数据文件，速度更快。3.事务的ACID特性包括？A.原子性（Atomicity）B.一致性（Consistency）C.隔离性（Isolation）D.持久性（Durability）答案：ABCD解析：四个特性是事务的核心属性，原子性保证事务操作要么全成功要么全失败，一致性保证事务执行前后数据完整性约束不被破坏，隔离性保证多个事务并发执行时互不干扰，持久性保证事务提交后数据永久生效。4.MySQL支持的索引类型有？A.B+树索引B.哈希索引C.全文索引D.空间索引答案：ABCD解析：B+树索引是默认索引类型，适配范围查询、排序等场景；Memory存储引擎支持哈希索引，InnoDB支持自适应哈希索引；全文索引适用于文本模糊检索场景；空间索引适用于地理位置数据检索。5.以下操作会触发表级锁的有？A.MyISAM执行SELECT查询B.InnoDB执行无索引的UPDATE操作C.执行ALTERTABLE加字段操作D.手动执行LOCKTABLES语句答案：ABCD解析：MyISAM的读写操作都加表级锁；InnoDB无索引的UPDATE无法命中行锁，会升级为表锁；DDL操作会加元数据锁（属于表级锁），阻塞该表的所有读写操作；LOCKTABLES会手动加表级锁。6.以下属于MySQL8.0新特性的有？A.默认字符集为utf8mb4B.支持窗口函数C.支持原子DDLD.移除了查询缓存答案：ABCD解析：查询缓存在8.0版本被完全移除，该特性并发场景下锁冲突严重，性能收益极低；窗口函数可以实现复杂的分组排名、滑动窗口计算等功能，大幅简化SQL编写。7.以下属于SQL优化常用手段的有？A.为高频查询条件创建合适的索引B.避免使用SELECT*，只查询需要的字段C.拆分大的DELETE/UPDATE操作，分批执行D.不需要去重的场景下用UNIONALL代替UNION答案：ABCD解析：UNION会对结果集做去重排序，性能远低于UNIONALL；大的批量更新操作会持有锁时间过长，导致主从延迟、锁冲突，分批执行可以降低影响。8.以下关于聚簇索引的描述正确的有？A.一个表只能有一个聚簇索引B.聚簇索引的叶子节点存储完整的行数据C.InnoDB默认使用主键作为聚簇索引D.无主键时InnoDB会选择第一个非空唯一索引作为聚簇索引答案：ABCD解析：聚簇索引的顺序就是数据的物理存储顺序，所以一个表只能有一个；无主键也无非空唯一索引时，InnoDB会生成隐藏的6字节ROW_ID作为聚簇索引。9.MySQL主从复制的常见模式有？A.异步复制B.半同步复制C.全同步复制D.组复制（MGR）答案：ABCD解析：异步复制是默认模式，主库写完binlog就返回事务成功，不关心从库是否同步；半同步复制等待至少一个从库接收binlog后返回，保证数据至少有两个副本；全同步复制等待所有从库执行完事务才返回，性能极低；组复制基于Paxos共识算法，支持多主写入，数据一致性更高。10.数据库死锁的必要条件有？A.互斥条件B.持有并等待C.不可剥夺D.循环等待答案：ABCD解析：四个条件同时满足时才会发生死锁，InnoDB会自动检测死锁，回滚代价最小的事务解除死锁。三、判断题（共10题，每题1分，共10分）1.MySQL中VARCHAR(10)的长度指的是字节数，最多可以存储3个utf8编码的汉字。答案：×解析：VARCHAR的长度是字符数，VARCHAR(10)可以存储10个任意字符，utf8mb4编码下最多占用40字节。2.InnoDB引擎下COUNT(1)的执行效率远高于COUNT(*)。答案：×解析：MySQL8.0对COUNT(*)做了优化，会选择最小的二级索引扫描，COUNT(1)和COUNT(*)的执行效率几乎一致，不存在明显差异。3.唯一索引的字段允许为NULL，且可以存在多个NULL值。答案：√解析：NULL不等于任何值（包括NULL），所以唯一索引可以存在多个NULL值，不会违反唯一性约束。4.事务提交后才会写入binlog。答案：×解析：事务执行过程中会将binlog写入缓存，事务提交时会将缓存中的binlog刷入磁盘，之后才会返回事务提交成功。5.表的索引越多，查询性能越高。答案：×解析：索引会加速查询，但会降低增删改的性能，因为每次数据变更都需要更新所有相关索引，同时索引会占用更多存储空间，需要按需创建。6.MySQL中NULL和空字符串''是等价的。答案：×解析：NULL是未知值，空字符串是长度为0的有效字符串，使用`=`比较NULL和任意值的结果都是NULL，需要用`ISNULL`判断NULL值。7.全表扫描的效率一定低于索引扫描。答案：×解析：当查询需要返回表中20%以上的数据时，全表扫描的效率高于索引扫描+回表的效率，因为索引扫描需要随机IO，全表扫描是顺序IO，此时优化器会选择全表扫描。8.InnoDB的自增主键一定是连续的。答案：×解析：事务回滚、批量插入预分配自增ID、实例重启等场景都会导致自增主键出现间隙，无法保证完全连续。9.开启慢查询日志会严重影响数据库性能。答案：×解析：只要`long_query_time`阈值设置合理（比如1秒），慢查询日志的性能损耗在5%以内，几乎可以忽略，是排查SQL性能问题的核心工具。10.预处理语句可以完全避免SQL注入风险。答案：√解析：预处理语句将SQL逻辑和参数分离，参数会被当作纯字符串处理，不会被解析为SQL指令，从根源上避免了SQL注入。四、简答题（共4题，每题5分，共20分）1.简述InnoDBMVCC（多版本并发控制）的实现原理。答：MVCC是InnoDB实现读写不冲突的核心机制，核心依赖undo日志和一致性视图（ReadView）实现：①每行数据都有三个隐藏字段：DB_TRX_ID（最近修改该行的事务ID）、DB_ROLL_PTR（指向undo日志中该行历史版本的指针）、DB_ROW_ID（无主键时生成的隐藏主键）。②ReadView包含四个核心属性：m_ids（创建视图时当前活跃未提交的事务ID集合）、min_trx_id（活跃事务的最小ID）、max_trx_id（下一个待分配的事务ID）、creator_trx_id（创建该视图的事务ID）。③查询时对比每行的DB_TRX_ID：小于min_trx_id说明事务已提交，数据可见；大于等于max_trx_id说明事务在视图创建后启动，数据不可见；介于两者之间时，若DB_TRX_ID不在m_ids中说明事务已提交，可见，否则不可见。④不可见的数据通过DB_ROLL_PTR回溯undo日志中的历史版本，直到找到可见版本或遍历完所有版本。⑤MVCC仅在READCOMMITTED和REPEATABLEREAD隔离级别下生效，RC级别每次查询都生成新的ReadView，RR级别第一次查询生成ReadView，因此RR可以实现可重复读。2.对比MyISAM和InnoDB存储引擎的核心区别。答：①事务支持：MyISAM不支持事务，InnoDB支持ACID事务；②锁粒度：MyISAM仅支持表级锁，并发性能差；InnoDB支持行级锁，基于索引实现，并发性能高；③外键支持：MyISAM不支持外键，InnoDB支持外键约束；④索引结构：MyISAM采用非聚簇索引，索引和数据文件分离，叶子节点存储数据地址；InnoDB采用聚簇索引，叶子节点存储完整行数据；⑤崩溃恢复：MyISAM崩溃后易出现数据损坏，恢复困难；InnoDB通过redolog和undolog支持崩溃安全恢复；⑥计数效率：MyISAM内部存储了表的总行数，不带WHERE的COUNT(*)直接返回，效率极高；InnoDB需要扫描索引统计行数，效率较低；⑦适用场景：MyISAM仅适合读多写少、无事务要求的场景，目前已基本被淘汰；InnoDB是默认存储引擎，适合绝大多数OLTP场景。3.简述MySQL主从复制的核心流程。答：主从复制基于binlog实现，分为三个核心步骤：①主库执行事务提交前，将数据变更记录按格式写入binlog文件；②从库启动IO线程与主库建立连接，主库启动BinlogDump线程，读取binlog事件发送给从库IO线程，从库IO线程将接收到的binlog事件写入中继日志（relaylog），并记录当前同步的binlog位置；③从库的SQL线程读取中继日志中的事件，按顺序重放执行，保证从库数据和主库一致。按照同步策略可分为异步复制、半同步复制、全同步复制、组复制四种模式。4.列举至少5种索引失效的常见场景。答：①查询条件对索引字段使用函数、表达式运算、隐式类型转换，比如`WHEREage+1=18`、`WHEREDATE(create_time)='2025-01-01'`、字符串字段不加引号查询；②模糊查询使用左通配，比如`WHEREnameLIKE'%张三'`，右通配`'张三%'`可以用到索引；③联合索引不满足最左前缀匹配原则，比如联合索引(a,b,c)，查询条件仅包含b、c，或a使用范围查询后，后续的b、c字段索引失效；④查询条件用OR连接，且OR两侧有一个字段没有索引，整个查询会走全表扫描；⑤索引字段使用ISNOTNULL查询，且NULL值占比较高时，优化器会选择全表扫描；⑥查询返回的数据占表总数据的20%以上时，优化器认为全表扫描效率更高，放弃使用索引。五、实操题（共2题，每题10分，共20分）1.现有三张表：学生表`student(s_idINTPRIMARYKEY,s_nameVARCHAR(20)NOTNULL,s_ageINT,s_deptVARCHAR(30),enroll_dateDATE)`；课程表`course(c_idINTPRIMARYKEY,c_nameVARCHAR(30)NOTNULL,t_nameVARCHAR(20))`；成绩表`sc(s_idINT,c_idINT,scoreINT,PRIMARYKEY(s_id,c_id),FOREIGNKEY(s_id)REFERENCESstudent(s_id),FOREIGNKEY(c_id)REFERENCEScourse(c_id))`。按要求编写SQL：①查询选修了“数据库原理”课程且成绩大于80分的学生姓名、成绩，按成绩降序排序。答：`SELECTs.s_name,sc.scoreFROMstudentsJOINscONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_idWHEREc.c_name='数据库原理'ANDsc.score>80ORDERBYsc.scoreDESC;`②统计每个学院的学生人数，只显示人数大于100的学院名称和人数。答：`SELECTs_dept,COUNT(*)ASstu_numFROMstudentGROUPBYs_deptHAVINGstu_num>100;`③查询所有没有选修“高等数学”课程的学生姓名。答：`SELECTs_nameFROMstudentsWHERENOTEXISTS(SELECT1FROMscJOINcoursecONsc.c_id=c.c_idWHEREsc.s_id=s.s_idANDc.c_name='高等数学');`④用窗口函数查询每个学生的所有课程成绩，以及该学生在所属学院的成绩排名（相同成绩排名不跳号）。答：`SELECTs.s_id,s.s_name,s.s_dept,c.c_name,sc.score,DENSE_RANK()OVER(PARTITIONBYs.s_deptORDERBYsc.scoreDESC)ASdept_rankFROMstudentsJOINscONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_id;`⑤将所有选修“计算机网络”课程的学生成绩加5分，分数不超过100。答：`UPDATEscJOINcoursecONsc.c_id=c.c_idSETsc.score=LEAST(sc.score+5,100)WHEREc.c_name='计算机网络';`2.现有慢查询SQL：`SELECT*FROMorderWHEREuser_id=123ANDcreate_time>'2024-01-01'ANDstatus=1;`，EXPLAIN结果显示type为ALL，rows为120万，key为NULL。①分析慢查询原因：该SQL没有命中任何索引，走全表扫描，扫描数据量过大，导致查询耗时过长。②提出优化方案：创建联合索引`idx_user_status_time(user_id,status,create_time)`，符合最左前缀匹配原则，三个查询条件都可以用到索引；同时避免使用SELECT*，只查询需要的字段，若要进一步优化可将需要的字段加入索引，创建覆盖索引`idx_user_status_time(user_id,status,create_time,order_id,order_amount)`，避免回表操作。③验证优化效果：优化后再次执行EXPLAIN，type变为range或ref，key字段显示为创建的联合索引名，rows扫描行数降至几千甚至几百，查询耗时从秒级降至毫秒级。六、综合设计题（共1题，15分）某电商平台需要设计订单系统，核心需求：①存储用户、订单、订单商品明细信息；②支持用户查询自己的所有订单，按下单时间倒序；③支持查询单个订单的所有商品明细；④支持统计任意时间段的订单总金额、订单量；⑤数据规模：用户量1000万，年订单量1亿，年订单明细量5亿。要求：①设计三张核心表的表结构（包含字段、约束、索引）；②说明索引设计依据；③提出3种以上架构层面的优化方案支撑大流量查询。答：①表结构设计```sql-用户表CREATETABLE`user`(`user_id`BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'用户ID',`username`VARCHAR(32)NOTNULLCOMMENT'用户名',`phone`CHAR(11)NOTNULLCOMMENT'手机号',`create_time`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'注册时间',`update_time`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`user_id`),UNIQUEKEY`uk_phone`(`phone`),KEY`idx_create_time`(`create_time`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='用户表';-订单表CREATETABLE`order`(`order_id`BIGINTUNSIGNEDNOTNULLCOMMENT'订单ID（雪花算法生成）',`user_id`BIGINTUNSIGNEDNOTNULLCOMMENT'用户ID',`order_amount`DECIMAL(10,2)NOTNULLCOMMENT'订单总金额',`status`TINYINTUNSIGNEDNOTNULLDEFAULT0COMMENT'订单状态：0待支付1已支付2已发货3已完成4已取消',`pay_time`DATETIMECOMMENT'支付时间',`create_time`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'下单时间',`update_time`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`order_id`),KEY`idx_user_time`(`user_id`,`create_time`DESC)COMMENT'用户订单倒序查询',KEY`idx_time_amount`(`create_time`,`order_amount`)COMMENT'时间段订单统计')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4CO