2025年数据库(MySQL)中级认证历年真题试卷及答案

2025年数据库(MySQL)中级认证历年真题试卷及答案一、单项选择题（共15题，每题2分，总计30分。每题只有1个正确答案，错选、不选均不得分）1.下列MySQL事务隔离级别中，能够同时解决脏读、不可重复读、幻读三类数据一致性问题的是（）A.READUNCOMMITTEDB.READCOMMITTEDC.REPEATABLEREADD.SERIALIZABLE【答案】D【解析】MySQLInnoDB引擎默认隔离级别为REPEATABLEREAD（可重复读），仅能从隔离级别定义层面解决脏读、不可重复读问题，虽可通过MVCC+临键锁在当前读场景下规避幻读，但并非隔离级别本身的能力。只有SERIALIZABLE（串行化）隔离级别通过强制事务串行执行，从根本上彻底解决三类数据一致性问题。2.InnoDB缓冲池（BufferPool）默认采用的内存页淘汰策略是（）A.FIFO（先进先出）B.LRU（最近最少使用）C.改进型LRU（冷热数据分离）D.LFU（最不经常使用）【答案】C【解析】InnoDB对传统LRU算法做了针对性优化，将缓冲池划分为热数据区（默认占比5/8）和冷数据区（默认占比3/8），首次加载的数据页会放入冷数据区头部，仅当后续被再次访问且访问间隔超过innodb_old_blocks_time参数默认值（1秒）时，才会被移入热数据区，避免全表扫描等批量读取操作一次性淘汰大量热数据。3.现有联合索引idx_abc(a,b,c)，下列SQL语句中无法触发该索引的是（）A.SELECT*FROMtWHEREa=1ANDb>2ANDc=3B.SELECT*FROMtWHEREb=1ANDc=2C.SELECT*FROMtWHEREa=1ANDc=3D.SELECT*FROMtWHEREa=1ANDb=2ORDERBYc【答案】B【解析】联合索引遵循最左前缀匹配原则，必须从索引的最左列开始匹配，B选项的查询条件未包含索引首列a，无法触发idx_abc索引；A选项触发最左前缀匹配到b列，c列的过滤可通过索引下推实现；C选项可匹配到a列；D选项可匹配到a、b列，c列用于排序无需回表。4.下列关于InnoDB行锁的描述正确的是（）A.所有UPDATE操作都会触发行锁B.行锁的粒度是数据行，不会升级为表锁C.当UPDATE语句的WHERE条件未命中任何索引时，会升级为表级意向锁D.当UPDATE语句的WHERE条件未命中任何索引时，会触发表级排他锁【答案】D【解析】InnoDB行锁是通过索引上的锁实现的，当更新操作的过滤条件未命中任何索引时，会对全表所有行加排他锁，等价于表锁；A选项错误，未命中索引时触发表锁而非行锁；B选项错误，未命中索引时行锁会升级为表锁；C选项错误，意向锁是表级锁，用于表明后续将要加的行锁类型，不会替代实际的排他锁。5.MySQL慢查询日志的默认阈值参数long_query_time的默认值是（）A.1秒B.2秒C.10秒D.0秒【答案】C【解析】MySQL5.7及8.0版本中，long_query_time参数的默认值为10秒，即执行时间超过10秒的SQL会被记录到慢查询日志，生产环境通常会调整为1秒甚至更短来定位慢查询。6.下列关于InnoDBredolog与undolog的描述，正确的是（）A.redolog用于保证事务的原子性，undolog用于保证事务的持久性B.redolog是逻辑日志，undolog是物理日志C.redolog采用循环写入模式，undolog支持多版本复用D.事务提交后，对应的undolog会被立即删除【答案】C【解析】A选项错误，redolog保证事务持久性，undolog保证事务原子性和MVCC多版本；B选项错误，redolog是物理日志，记录数据页的物理修改，undolog是逻辑日志，记录数据修改的反向操作；C选项正确，redolog固定大小循环写入，undolog可被多版本查询复用；D选项错误，事务提交后undolog不会立即删除，会等待所有早于该事务的readview销毁后才会被purge线程回收。7.InnoDBMVCC（多版本并发控制）的实现不依赖下列哪项技术（）A.undo日志B.ReadView（一致性视图）C.表级锁D.行记录的隐藏列（trx_id、roll_pointer）【答案】C【解析】MVCC的核心实现依赖三个组件：1.行记录的隐藏列：记录该行的最新事务ID（trx_id）和指向undo日志的回滚指针（roll_pointer）；2.undo日志：存储行记录的历史版本；3.ReadView：判断事务可见性的一致性视图；表级锁与MVCC实现无关，MVCC的核心作用就是避免读写冲突时加锁，提升并发性能。8.MySQL半同步复制中，参数rpl_semi_sync_master_wait_point设置为AFTER_SYNC的含义是（）A.主库写入binlog并刷盘后，等待至少一个从库收到binlog并写入relaylog刷盘后，再向客户端返回事务提交成功B.主库提交事务到存储引擎后，等待至少一个从库收到binlog并写入relaylog刷盘后，再向客户端返回事务提交成功C.主库写入binlog后无需刷盘，等待至少一个从库收到binlog后立即返回提交成功D.主库无需等待从库响应，直接向客户端返回事务提交成功【答案】A【解析】AFTER_SYNC是MySQL5.7及以上版本半同步复制的默认配置，主库将binlog写入并刷盘后，等待至少一个从库确认收到binlog并写入relaylog刷盘，再提交事务到存储引擎并返回客户端成功，可保证主从数据一致性，避免主库宕机时数据丢失；AFTER_COMMIT是旧版半同步配置，主库先提交到存储引擎再等待从库响应，可能出现主库宕机时客户端已收到提交成功但从库未收到数据的情况。9.使用mysqldump备份MySQL数据库时，需要备份存储过程和事件调度器，需添加的参数是（）A.--single-transactionB.-R-EC.--master-data=2D.-d【答案】B【解析】-R参数等价于--routines，用于备份存储过程和函数；-E参数等价于--events，用于备份事件调度器；A选项--single-transaction用于在InnoDB引擎上生成一致性快照备份，避免锁表；C选项--master-data=2用于在备份文件中注释记录主库的binlog位点，用于主从复制搭建；D选项-d用于仅备份表结构不备份数据。10.InnoDB默认的死锁处理策略是（）A.持有锁时间最长的事务优先回滚B.等待锁时间最长的事务优先回滚C.回滚权重最小的事务（通常是修改数据量最少的事务）D.同时回滚所有涉及死锁的事务【答案】C【解析】InnoDB默认死锁检测开启，当检测到死锁时，会选择权重最小的事务回滚，权重判断依据为事务修改、插入、删除的数据量，数据量越少权重越低，回滚成本越低。11.使用EXPLAIN分析SQL执行计划时，type列的下列取值中性能最优的是（）A.refB.rangeC.constD.index【答案】C【解析】type列性能从高到低排序为：system>const>eq_ref>ref>range>index>ALL；const表示通过主键或唯一索引的等值查询，最多返回1行数据，性能仅次于系统表查询system。12.下列场景中最容易触发InnoDB数据页分裂的是（）A.主键使用自增ID，批量插入数据B.主键使用UUID，随机插入数据C.普通索引使用有序数值，批量插入数据D.普通索引使用固定长度字符串，等值更新【答案】B【解析】InnoDB数据页默认按索引顺序有序存储，UUID是无序字符串，插入主键索引时会随机插入到已有数据页的中间位置，当数据页剩余空间不足时就会触发页分裂，导致大量数据迁移，性能下降；自增ID作为主键时数据按顺序写入页尾部，几乎不会触发页分裂。13.MySQLbinlog的三种格式中，能够完全避免主从数据不一致问题的是（）A.STATEMENTB.ROWC.MIXEDD.三种都可以【答案】B【解析】ROW格式记录行数据的物理修改，不记录SQL语句的上下文，不会出现STATEMENT格式中因函数、触发器、存储过程导致的主从执行结果不一致的问题；MIXED格式会自动判断SQL类型，可能切换为STATEMENT格式，仍存在不一致风险。14.事务ACID特性中，持久性的实现依赖InnoDB的哪项组件（）A.缓冲池B.redologC.undologD.自适应哈希索引【答案】B【解析】持久性要求事务提交后，即使数据库宕机，修改的数据也不会丢失，InnoDB通过WAL（预写日志）机制，事务提交时先将修改写入redolog并刷盘，再修改缓冲池中的数据页，宕机后可通过redolog恢复未刷到磁盘的数据页，保证持久性。15.下列关于MySQL会话临时表的描述错误的是（）A.仅对创建它的会话可见，其他会话无法访问B.会话断开后临时表会被自动删除C.临时表可以和普通表重名D.临时表的数据会被写入binlog，支持主从同步【答案】D【解析】会话临时表的数据不会写入binlog，主库上的临时表不会同步到从库，因为仅对当前会话有效，同步到从库无意义；A、B、C选项描述均正确。二、多项选择题（共10题，每题3分，总计30分。每题有2-4个正确答案，少选且选对每个选项得0.5分，多选、错选、不选均不得分）1.下列关于MySQL索引下推（ICP）的描述正确的有（）A.仅支持InnoDB存储引擎B.可在联合索引遍历过程中直接过滤索引包含的字段条件，减少回表次数C.MySQL8.0中默认处于开启状态D.适用于所有使用联合索引的查询场景【答案】BC【解析】A选项错误，MySQL5.6及以上版本中MyISAM也支持索引下推；D选项错误，当查询条件存在子查询、使用非索引字段过滤、或者开启SERIALIZABLE隔离级别时无法使用ICP；B选项为ICP核心优化逻辑，C选项MySQL5.6及以上版本默认开启innodb_icp=ON，8.0保持默认配置。2.MySQLInnoDB在REPEATABLEREAD隔离级别下，能够避免的问题有（）A.脏读B.不可重复读C.快照读场景下的幻读D.当前读场景下的幻读（配合临键锁）【答案】ABCD【解析】RR隔离级别本身可解决脏读、不可重复读问题；快照读（普通SELECT）场景下通过MVCC多版本读取历史数据，不会出现幻读；当前读（SELECT...FORUPDATE、UPDATE、DELETE）场景下，InnoDB通过临键锁（Next-KeyLock）锁定查询范围及间隙，避免其他事务插入新数据，解决幻读问题。3.MySQL半同步主从复制架构中，包含的线程有（）A.主库的BinlogDump线程B.从库的IO线程C.从库的SQL线程D.主库的Purge线程【答案】ABC【解析】半同步复制属于主从复制的一种，核心线程包含主库的BinlogDump线程：负责读取主库binlog并发送给从库；从库IO线程：负责接收主库发送的binlog并写入relaylog；从库SQL线程：负责读取relaylog并回放执行；D选项Purge线程是InnoDB用于回收undo日志的后台线程，与主从复制无关。4.下列关于Xtrabackup物理备份工具的优势描述正确的有（）A.备份过程全程不锁表，对业务影响极小B.支持增量备份，备份速度快、占用空间小C.支持热备份，备份过程中数据库可正常提供读写服务D.备份恢复速度比逻辑备份工具mysqldump快【答案】ABCD【解析】Xtrabackup是Percona推出的开源物理备份工具，所有描述均为其核心优势：通过InnoDB的redolog实现热备份，备份过程仅在最后同步元数据时加短时间全局读锁，几乎不影响业务；支持全量+增量备份，备份和恢复速度远快于逻辑备份。5.下列属于InnoDB锁分类的有（）A.共享锁（S锁）B.排他锁（X锁）C.意向共享锁（IS锁）D.意向排他锁（IX锁）【答案】ABCD【解析】InnoDB锁分为行级锁和表级意向锁：行级锁包含共享锁（读锁）和排他锁（写锁）；表级意向锁包含意向共享锁和意向排他锁，用于表明后续事务将要加的行锁类型，避免表级锁和行锁的冲突。6.MySQL8.0相比MySQL5.7新增的特性有（）A.原子DDLB.窗口函数C.undo表空间自动回收D.GTID主从复制【答案】ABC【解析】GTID主从复制是MySQL5.6版本就已支持的特性，不属于8.0新增；A选项原子DDL保证DDL操作要么全部成功要么回滚，不会出现部分修改的情况；B选项窗口函数支持OVER()语法，可实现复杂的分组统计需求；C选项8.0支持undo表空间自动收缩，无需手动重建回收空间。7.大表DDL操作的优化方案包括（）A.使用OnlineDDL，避免锁表B.业务低峰期执行DDLC.使用pt-online-schema-change或gh-ost工具在线改表D.直接执行ALTERTABLE语句，无需其他操作【答案】ABC【解析】大表DDL直接执行ALTERTABLE会锁表，导致业务长时间无法写入，D选项错误；OnlineDDL是MySQL5.6及以上版本支持的在线改表功能，大部分DDL操作可支持读写不阻塞；pt-osc和gh-ost是业界常用的开源在线改表工具，通过触发器或binlog同步实现改表过程全程不锁表；低峰期执行可降低对业务的影响。8.下列关于事务回滚的描述正确的有（）A.大事务回滚会占用大量IO和CPU资源，耗时较长B.事务回滚会删除对应的redolog记录C.事务回滚依赖undo日志执行反向操作D.事务回滚过程中数据库会停止所有服务【答案】AC【解析】A选项正确，大事务修改的数据量多，回滚时需要执行大量反向操作，消耗大量资源且耗时久；B选项错误，redolog写入后不会被删除，会循环覆盖；C选项正确，undo日志记录了数据修改的反向操作，回滚时直接执行undo日志中的逻辑即可恢复数据；D选项错误，事务回滚是后台线程执行，不会停止数据库服务。9.慢查询优化的常规步骤包括（）A.开启慢查询日志，定位慢SQLB.使用EXPLAIN分析SQL执行计划，判断是否命中索引C.优化SQL语句，添加合适的索引D.调整数据库参数，提升整体性能【答案】ABCD【解析】四个选项均为慢查询优化的标准流程：首先通过慢查询日志定位执行时间过长的SQL，然后通过EXPLAIN、PROFILE等工具分析执行计划的瓶颈，优先优化SQL语句和索引，最后结合业务场景调整数据库参数提升性能。10.下列能够有效降低InnoDB死锁概率的措施有（）A.保证所有事务按相同顺序加锁B.尽量使用低隔离级别C.拆分大事务为小事务，减少锁持有时间D.避免WHERE条件未命中索引的更新操作【答案】ABCD【解析】A选项正确，加锁顺序一致可避免循环等待；B选项正确，低隔离级别如READCOMMITTED可减少间隙锁的使用，降低死锁概率；C选项正确，小事务锁持有时间短，冲突概率低；D选项正确，未命中索引的更新会触发表锁，不会出现行锁冲突导致的死锁。三、实操题（共2题，每题10分，总计20分）1.现有生产环境MySQL8.0实例，业务侧反馈用户订单表orders查询缓慢，表结构如下：CREATETABLE`orders`(`order_id`bigintunsignedNOTNULLAUTO_INCREMENTCOMMENT'订单ID',`user_id`intunsignedNOTNULLCOMMENT'用户ID',`create_time`datetimeNOTNULLCOMMENT'订单创建时间',`order_amount`decimal(10,2)NOTNULLCOMMENT'订单金额',`status`tinyintunsignedNOTNULLCOMMENT'订单状态：1待支付2已支付3已取消',PRIMARYKEY(`order_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;慢查询语句为：SELECTuser_id,SUM(order_amount)FROMordersWHEREcreate_timeBETWEEN'2025-01-01'AND'2025-06-30'ANDstatus=2GROUPBYuser_id;已知该表除主键外无其他索引，表数据量约5000万行。要求：（1）写出最优的索引创建语句并说明理由；（4分）（2）写出用EXPLAIN验证索引生效的关键判断字段及符合要求的取值；（3分）（3）若创建索引后该查询仍然缓慢，给出2种可落地的优化方案并说明适用场景。（3分）【参考答案】（1）索引创建语句：CREATEINDEXidx_ct_st_uid_amtONorders(create_time,status,user_id,order_amount);理由：联合索引遵循最左前缀匹配原则，前两列create_time、status对应WHERE条件的过滤字段，可快速筛选出符合时间范围和状态的订单；第三列user_id对应GROUPBY分组字段，可直接在索引中完成分组，避免创建临时表；第四列order_amount对应聚合函数字段，所有查询字段均包含在索引中，无需回表查询主键索引，属于覆盖索引，性能最优。（2）关键判断字段及取值：①type列：取值为range，说明通过索引范围扫描过滤数据，符合时间范围查询的特性；②key列：取值为idx_ct_st_uid_amt，说明优化器选择了创建的联合索引；③Extra列：取值包含Usingindex，说明使用了覆盖索引，无需回表；无Usingtemporary、Usingfilesort，说明分组和排序操作均在索引中完成，没有额外的临时表和文件排序开销。（3）优化方案：①表分区优化：将orders表按create_time字段创建范围分区，每个分区对应一个月的数据，查询时只需扫描对应时间范围的分区，减少数据扫描量，适用于数据量持续增长、查询固定按时间范围过滤的场景；②预汇总中间表：创建定时任务，每日凌晨统计前一日每个用户的已支付订单总金额，存入中间表user_order_sum(user_id,stat_date,total_amount)，查询时直接按时间范围聚合中间表数据，数据扫描量可降低到原表的1%以下，适用于查询对实时性要求不高（允许T+1延迟）的统计类场景。2.某线上MySQL集群采用一主两从半同步复制架构（基于GTID），现主库所在服务器磁盘硬件故障，无法启动，需要将其中一台从库提升为新主库，要求数据零丢失，业务停机时间最短，写出完整的操作步骤及注意事项。【参考答案】操作步骤：（1）流量切停：通知业务侧暂停写入流量，将所有读流量切到剩余的两台从库，禁止新的写入请求进入旧主库；（2）确认从库数据同步完成：分别在两台从库执行SHOWSLAVESTATUS\G，确认Seconds_Behind_Master取值为0，说明所有中继日志已全部回放完成，无延迟数据；（3）选择新主库：执行SHOWGLOBALVARIABLESLIKE'GTID_EXECUTED';对比两台从库的GTID集合，选择GTID范围更大的从库作为新主库（已应用的事务更多，数据最完整）；（4）配置新主库：在选中的新主库上执行：STOPSLAVE;SETGLOBALread_only=OFF;（若需保留原有GTID集合无需执行RESETMASTER），为从库创建复制权限账号（若已有则无需重复创建）；（5）配置从库指向新主库：在另一台从库上执行：STOPSLAVE;CHANGEMASTERTOMASTER_HOST='新主库IP',MASTER_USER='复制账号',MASTER_PASSWORD='复制密码',MASTER_AUTO_POSITION=1;STARTSLAVE;（6）验证主从状态：在从库执行SHOWSLAVESTATUS\G，确认Slave_IO_Running和Slave_SQL_Running均为Yes，Seconds_Behind_Master为0，主从复制正常；（7）流量切换：将业务读写流量全部切换到新主库，恢复业务；（8）旧主库修复：待旧主库硬件修复后，将其配置为新主库的从库，重新加入集群。注意事项：（1）切换前必须确认两台从库的中继日志全部回放完成，避免数据丢失；（2）基于GTID的复制无需手动查找binlog位点，通过MASTER_AUTO_POSITION=1可自动匹配GTID集合，降低切换出错概率；（3）切换完成后需使用pt-table-checksum工具校验新主从的数据一致性，确保无数据差异；（4）半同步复制参数需在新主库上重新配置，保证集群架构和切换前一致。四、案例分析题（共1题，总计20分）某电商业务MySQL8.0实例，在618大促期间出现大量事务超时，错误日志中频繁出现Deadlockfoundwhentryingtogetlock;tryrestartingtransaction报错，同时CPU使用率持续维持在95%以上，慢查询日志中存在大量同类型的UPDATE语句：UPDATEgoodsSETstock=stock-1WHEREgoods_id=123ANDstock>0;已知goods表结构为CREATETABLEgoods(goods_idintunsignedPRIMARYKEY,stockintunsignedNOTNULL)ENGINE=InnoDB;goods_id为主键，大促期间该热门商品的每秒下单请求超过2500次。要求：（1）分析死锁产生的可能原因；（6分）（2）分析CPU使用率过高的核心原因；（6分）（3）给出完整的优化方案，要求满足大促期间的性能需求。（8分）【参考答案】（1）死锁产生的可能原因：①交叉加锁：部分事务包含多个商品的库存扣减操作，事务A先扣减goods_id=123的库存再加锁goods_id=456，事务B先扣减goods_id=456的库存再加锁goods_id=123，两个事务互相持有对方需要的行锁，形成循环等待，触发死锁；②间隙锁冲突：如果