(2025年)mysql数据库试题及答案解析

(2025年)mysql数据库试题及答案解析单项选择题（共20题，覆盖基础语法、核心特性、运维基础考点）1.下列关于MySQL8.0版本默认密码认证插件的描述，正确的是A.默认使用mysql_native_password，支持基于SHA256的强加密认证B.默认使用caching_sha2_password，兼顾认证安全性与验证性能C.默认使用sha256_password，要求客户端必须支持SSL连接才能完成认证D.默认使用mysql_old_password，兼容所有5.x版本的旧客户端答案：B解析：MySQL8.0版本彻底调整了默认认证逻辑，取消了旧版本的低加密强度认证插件，默认启用caching_sha2_password，该插件服务端会缓存认证过的用户密码哈希值，既实现了SHA256级别的强加密安全能力，又避免了每次认证都做全量加密的性能损耗；只有主动配置才会切换为mysql_native_password以兼容旧客户端，sha256_password要求强制SSL连接并未设为默认，mysql_old_password属于5.1版本前的废弃特性已被彻底移除。2.原子DDL是MySQL8.0版本推出的核心特性，其核心作用是A.所有DDL操作执行过程中自动提交事务，避免长锁阻塞业务B.所有DDL操作要么全部执行成功，要么全部回滚，不会出现数据字典状态不一致的问题C.大表DDL操作自动在线执行，不会锁表阻塞读写业务D.DDL操作执行前自动生成全量备份，执行失败可一键恢复答案：B解析：在MySQL5.7及更早版本中，DDL操作属于非原子性操作，若执行过程中数据库进程意外崩溃，很容易出现数据字典、表空间文件、FRM文件状态不一致的问题，比如执行DROPTABLE中途崩溃会出现表数据已删除但元信息残留的异常场景。MySQL8.0将所有DDL操作纳入InnoDB的事务机制，实现了全链路原子性保证，DDL执行出现异常时自动回滚所有修改；原子DDL本身不自带在线DDL能力，也不会自动生成全量备份，因此A、C、D描述均错误。3.下列函数中，不属于MySQL8.0原生支持的窗口函数是A.ROW_NUMBER()B.RANK()C.DENSE_RANK()D.ROWNUM答案：D解析：ROWNUM是Oracle数据库的伪列特性，不属于MySQL原生支持的函数，MySQL8.0正式全量引入窗口函数能力，除题干中前三个排序类窗口函数外，还支持LAG()、LEAD()、NTILE()、PERCENT_RANK()等共计11类标准窗口函数，彻底替代了过去需要通过子查询、变量自定义实现排序统计的复杂写法，统计性能提升3倍以上。4.InnoDB引擎MVCC多版本并发控制机制在可重复读（RR）隔离级别下，生成ReadView一致性视图的时机是A.事务中任意执行第一个SELECT语句时生成，整个事务生命周期内复用该视图B.事务启动瞬间立即生成，后续所有SELECT操作都复用该视图C.事务中每执行一次SELECT语句，都重新生成全新的ReadViewD.事务提交前最后一次执行SELECT语句时生成答案：A解析：该考点是极易混淆的高频考点，InnoDBRR隔离级别下不会在事务启动时直接生成视图，而是在事务执行第一个SELECT语句时生成全局一致性视图，后续整个事务的所有查询操作都复用该视图，以此保证整个事务内所有查询结果的一致性；读已提交（RC）隔离级别下则是每执行一次SELECT都重新生成新的ReadView，以此实现查询能看到其他已提交事务的最新修改，避免不可重复读问题。5.关于MySQL8.0支持的隐藏索引（InvisibleIndex）特性，下列描述正确的是A.隐藏索引会直接从物理磁盘删除索引数据，仅保留索引元信息B.默认情况下优化器生成执行计划时会自动忽略隐藏索引C.隐藏索引对所有SQL查询完全不可见，任何场景都无法被触发使用D.将普通索引修改为隐藏索引，需要重建整个索引文件答案：B解析：隐藏索引的核心作用是安全的灰度删除索引，将索引标记为隐藏后，优化器默认不会选择该索引生成执行计划，但索引本身的物理数据完全保留，写入操作还会同步维护索引数据，若线上删除索引后出现业务异常，可快速将索引改回可见状态，无需重新创建索引；可通过设置optimizer_switch='use_invisible_indexes=on'参数强制让优化器选择隐藏索引，因此C选项描述错误，修改索引可见性仅修改元信息，不会触发索引重建，D选项错误。6.下列关于InnoDB间隙锁（GapLock）的适用场景描述，正确的是A.间隙锁仅在RC读已提交隔离级别下生效，用于避免幻读问题B.间隙锁的作用是锁住两个索引记录之间的间隙，防止其他事务向该间隙插入新数据C.间隙锁会和意向共享锁、意向排他锁产生冲突，引发锁等待D.所有普通SELECT语句不加锁时，也会自动生成间隙锁答案：B解析：间隙锁是InnoDBRR可重复读隔离级别下特有的锁机制，核心作用是通过锁定索引间隙，避免其他事务插入新记录，从机制层面彻底解决幻读问题；间隙锁之间不会产生互斥冲突，多个事务可以同时持有同一个间隙的间隙锁，间隙锁仅在当前查询需要加排他锁/共享锁的场景下才会生成，普通快照读不会触发生成间隙锁，因此A、C、D描述均错误。7.MySQLGTID模式主从复制中，GTID全局事务ID的标准组成格式是A.服务器server_id:事务执行的时间戳B.数据库实例UUID:该实例上执行的事务递增序列号C.binlog文件名:binlog内的事务偏移量D.库名+表名:事务修改的行数答案：B解析：GTID的全称为GlobalTransactionIdentifier，格式为UUID:TID，其中UUID是每个MySQL实例首次启动时自动生成的全局唯一128位字符串，TID是该实例上按执行顺序自增的事务序列号，GTID可以保证全局范围内每个事务的ID唯一，主从复制切换时无需手动匹配binlog文件名和偏移量，大幅降低了主从切换的运维复杂度。8.InnoDB引擎默认的单页大小是A.8KBB.16KBC.32KBD.64KB答案：B解析：InnoDB默认页大小为16KB，该参数可以在实例初始化时自定义调整，不同页大小适配不同业务场景：8KB页适合高并发小额交易场景，32KB/64KB页适合大数据量OLAP分析场景，16KB默认值是经过大量场景验证的最优值，平衡了随机IO性能和页内空间利用率。9.现有联合索引idx_a_b_c(a,b,c)，下列查询语句中无法命中该索引的是A.select*fromtestwherea=1andb=2andc=3B.select*fromtestwherea=1andc=3C.select*fromtestwhereb=2andc=3D.select*fromtestwherea=1andb>2andc=3答案：C解析：联合索引严格遵循最左匹配原则，查询条件必须从索引最左侧的列开始依次匹配，不能跳过前置列直接匹配后续列，C选项直接指定b和c的条件，跳过了最左侧的a列，完全无法触发联合索引的匹配；B选项跳过b列，仅匹配a和c，可命中索引中a列的部分；D选项在b列遇到范围匹配后停止匹配后续c列，依然可以命中a和b列的部分索引，因此C是唯一无法命中索引的选项。10.MySQL执行计划EXPLAIN输出的type字段表示访问数据的方式，下列访问方式中性能最优的是A.refB.rangeC.constD.ALL答案：C解析：type字段的性能排序从高到低为system>const>eq_ref>ref>range>index>ALL，其中const表示通过主键或者唯一二级索引等值匹配，最多只能返回1行数据，是性能最高的访问方式，ALL代表全表扫描，是性能最差的访问方式。11.下列特性中，已经在MySQL8.0版本中被彻底移除不再支持的是A.查询缓存（QueryCache）B.在线DDLC.半同步复制D.通用表空间答案：A解析：MySQL5.7版本就已经将查询缓存标记为废弃特性，8.0版本彻底从代码中移除该功能，查询缓存的失效机制是只要对应表有任何写入操作，该表所有缓存的查询结果都会被清空，在高并发写入场景下不仅无法提升性能，反而会引发大量锁冲突、CPU资源浪费，因此最终被官方彻底移除。12.下列InnoDB死锁优化手段中，不合理的是A.所有涉及多资源修改的事务，固定统一资源的访问顺序B.拆分大事务为多个执行时长更短的小事务，减少锁持有时间C.为所有操作尽可能加更大范围的表级锁，避免行锁冲突D.降低隔离级别到RC，取消间隙锁的生成概率，减少锁冲突场景答案：C解析：加锁范围越大，不同事务抢占不同锁资源的冲突概率越高，反而会大幅提升死锁的出现概率，优化死锁的核心原则是尽可能缩小锁的粒度，减少锁持有时间，统一加锁顺序，因此C选项的描述完全错误。13.MySQL慢查询日志的long_query_time慢查询阈值，默认值为A.1秒B.10秒C.30秒D.60秒答案：B解析：MySQL默认慢查询阈值为10秒，即执行时长超过10秒的SQL会被记录到慢查询日志中，线上业务通常会根据实际性能要求将该阈值调整为1秒，快速定位执行效率不达标的SQL。14.下列分库分表方案中，属于垂直分库的是A.将订单表按照用户ID哈希拆分到16个库中B.将商品库、订单库、用户库分别部署到独立的数据库实例中C.将单表2亿条数据的日志表按照创建时间按月拆分为12张子表D.将大表的大文本字段拆分到独立的扩展表中，和主表通过主键关联答案：B解析：垂直分库的核心逻辑是按照业务模块拆分不同的数据库，将不同业务的库部署到不同实例，实现资源隔离；A属于水平分库，C属于水平分表，D属于垂直分表。15.当InnoDB缓冲池BufferPool的Free空闲列表全部耗尽时，InnoDB后台线程会优先淘汰哪一部分的数据页腾出空间存放新数据A.LRU链表头部的热数据页B.LRU链表中部的访问频率较高的数据页C.LRU链表尾部的冷数据页D.最新修改的未刷盘脏页答案：C解析：InnoDB为LRU链表做了冷热分区优化，将链表分为热区和冷区两部分，冷区占总长度的37%，新读取的页优先放到冷区头部，被访问过一次超过1秒后才会移动到热区，当空闲空间不足时优先淘汰冷区尾部长时间未被访问的数据页，大幅减少了批量全表扫描将全部热数据淘汰出缓冲池的问题。多项选择题（共10题，覆盖进阶核心知识点）1.下列属于InnoDB引擎原生支持的锁类型的有A.意向共享锁（IS）B.记录行锁C.间隙锁D.临键锁答案：ABCD解析：InnoDB实现了完整的两级锁体系，表级层面支持意向共享锁、意向排他锁，行级层面支持记录锁、间隙锁、临键锁，其中临键锁是记录锁和间隙锁的组合，是InnoDBRR隔离级别下行加锁的默认形态。2.MySQL8.0原生支持的窗口函数包含下列哪些A.LAG()取上一行数据B.LEAD()取下一行数据C.NTILE()分组排序D.GROUP_CONCAT()行转列聚合答案：ABC解析：GROUP_CONCAT是标准聚合函数，不属于窗口函数范畴，其余三个都是8.0原生支持的窗口函数。3.下列场景中，会导致索引失效需要走全表扫描的有A.查询条件使用LIKE'%关键词'前缀模糊匹配B.对索引字段做函数运算，比如wheredate(create_time)='2025-01-01'C.字符串字段传入数值类型做等值匹配，隐式类型转换触发D.OR连接的多个查询条件中，只有部分字段创建了索引答案：ABCD解析：四种场景都是高频的索引失效场景，日常SQL开发中需要重点规避，比如前缀模糊匹配可以通过搜索引擎优化，函数运算可以调整为wherecreate_timebetween'2025-01-01'and'2025-01-02'避免。4.下列属于MySQL高可用主从复制主流部署方案的有A.一主多从读写分离拓扑B.级联复制拓扑解决从库过多主库IO压力过大问题C.MGRMySQL组复制全对等高可用集群D.半同步复制保证主库提交事务后至少一个从库收到事务日志答案：ABCD解析：四类方案都是当前生产环境广泛使用的高可用部署方案，可根据业务对数据一致性、故障切换速度的不同要求选择适配的方案。5.下列属于MySQL8.0版本新特性的有A.支持隐藏索引B.支持通用表空间自定义存放路径C.支持资源组隔离，将不同业务线程绑定到指定CPU核心D.支持降序索引，适配排序需求减少排序文件生成答案：ABCD解析：四个特性都是MySQL8.0正式发布后新增的生产级特性，大幅提升了MySQL的运维灵活性和复杂场景适配能力。实操应用题（共4题，覆盖实战落地能力）1.现有学生成绩表结构：```sqlCREATETABLEstudent_score(sidINTPRIMARYKEYAUTO_INCREMENT,s_nameVARCHAR(32)NOTNULL,class_idINTNOTNULL,scoreINTNOTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;```要求编写SQL查询每个班级总分排名前三的学生信息，兼容同分场景下的排序规则，使用MySQL8.0窗口函数实现。参考答案：```sqlSELECT*FROM(SELECT*,RANK()OVER(PARTITIONBYclass_idORDERBYscoreDESC)ASrkFROMstudent_score)tWHERErk<=3;```解析：使用RANK()窗口函数可以实现同分同排名的效果，若需要严格控制只返回3条结果，同分场景下截断排序可以替换为ROW_NUMBER()，若需要同分不跳跃排名则替换为DENSE_RANK()，实现逻辑比旧版本通过变量自增排序的写法可读性提升60%，性能提升2倍以上。2.订单表order_total数据量为1200万条，现有慢SQL语句：`select*fromorder_totalwherecreate_time>='2024-01-01'andstatus=1orderbyiddesc`，执行计划显示全表扫描执行时长超过8秒，给出完整优化方案。解析：首先创建覆盖联合索引`idx_status_ctime_id(status,create_time,id)`，将等值匹配的status字段放在索引最左侧，其次是范围匹配的create_time，最后放上排序字段id，整个查询可以直接通过索引拿到所有需要的字段，无需回表访问聚簇索引，优化后SQL执行时长可以降低到100毫秒以内；若业务允许可进一步取消select*，只查询业务需要的字段，进一步减少索引数据体积。3.GTID模式一主两从架构下主库意外宕机，给出完整的主从切换操作流程。解析：第一步登录两个存活从库，执行`showslavestatus\G`查看Executed_Gtid_Set字段的事务完成量，选择GTID集合最大、数据最完整的从库作为新主库；第二步在选中的新主库上执行`stopslave;resetmaster;`，清空从库同步配置，开启新主库的读写权限；第三步将另一个从库的主库指向切换为新主库，执行`changemastertomaster_host='新主库IP',master_auto_position=1forchannel'';startslave;`，同步配置完成后即可快速恢复业务，整个过程无需手动匹配binlog偏移，切换时长可控制在30秒以内。4.线上业务出现偶发死锁，给出排查定位死锁的完整操作流程。解析：首先开启参数`innodb_print_all_deadlocks=1`，将所有死锁日志打印到MySQL错误日志中，或者直接执行`showengineinnodbstatus`查看最新的死锁记录，死锁日志中会完整记录两个冲突事务执行的SQL语句、各自持有的锁资源、等待的锁资源信息，通过分析锁的争抢顺序，调整业务SQL的加锁顺序，拆分大事务即可解决绝大多数死锁问题。综合案例分析题某头部电商平台核心订单库单表数据量1.2亿条，日订单增量200万条，高峰期出现数据库CPU占用率100%