2025年mysql中级面试题及答案

2025年mysql中级面试题及答案1.MySQL8.0中索引的新增特性有哪些？实际业务中如何利用这些特性优化查询？答案：MySQL8.0针对索引体系做了多项核心优化，具体特性及使用场景如下：（1）隐藏索引（不可见索引）：索引创建后可以设置为invisible状态，优化器不会选择该索引，但索引会正常维护。使用场景：上线新索引前先设为隐藏，观察业务无异常后再设为可见；需要下线索引时先设为隐藏，观察1-3天无性能问题再删除，避免直接删索引导致的业务故障。语法：ALTERTABLEt1ALTERINDEXidx_nameINVISIBLE/VISIBLE。（2）降序索引：支持为索引列指定DESC排序规则，底层B+树的叶子节点按指定顺序存储。使用场景：解决联合索引多列排序方向不一致导致的filesort问题，比如业务常用查询为SELECT*FROMorderWHEREuser_id=1ORDERBYcreate_timeDESC,idASC，创建联合索引idx_user_ctime_id(user_id,create_timeDESC,idASC)即可避免文件排序，性能提升可达40%以上。（3）函数索引：支持对函数、表达式的计算结果创建索引。使用场景：解决查询条件带函数导致的索引失效问题，比如常用查询为SELECT*FROMuserWHEREDATE(create_time)='2025-01-01'，创建索引idx_func_ctime((DATE(create_time)))即可命中索引，无需全表扫描，另外支持对JSON字段的指定节点创建索引，比如idx_json(CAST(json_data->>'$.order_id'ASUNSIGNED))，大幅提升JSON字段的查询性能。（4）直方图：对非索引列的数据分布进行统计，优化器可以基于直方图生成更准确的执行计划。使用场景：针对低基数、很少作为查询条件不需要建索引的列，执行ANALYZETABLEt1UPDATEHISTOGRAMONcol1WITH100BUCKETS，优化范围查询的执行计划准确率，避免全表扫描，比如性别、状态等枚举列的范围查询性能可提升2-3倍。（5）跳跃扫描（SkipScan）：优化器自动对联合索引的首列低基数场景做优化，不需要等值匹配首列也能命中联合索引。比如联合索引为idx_gender_phone(gender,phone)，gender只有男、女两个枚举值，查询SELECT*FROMuserWHEREphone=时，8.0会自动拆分为gender='男'ANDphone=UNIONALLgender='女'ANDphone=，无需全表扫描。2.什么是索引下推（ICP）？什么情况下会失效？结合MySQL8.0的优化点说明。答案：索引下推是MySQL5.6引入的优化特性，核心原理是将Server层的索引条件过滤下推到存储引擎层执行，存储引擎层遍历索引时直接过滤不符合条件的索引项，仅将符合条件的主键回表查询完整数据，大幅减少回表次数和IO开销。比如联合索引idx_name_age(name,age)，查询SELECT*FROMuserWHEREnameLIKE'张%'ANDage>30，未开启ICP时，存储引擎层会返回所有name以“张”开头的索引项的主键，Server层拿到回表后的数据再过滤age>30的记录；开启ICP后，存储引擎层直接在索引遍历阶段过滤age>30的索引项，仅返回符合条件的主键回表，回表次数可减少60%以上。ICP失效场景包括：（1）使用不支持二级索引的存储引擎，比如MyISAM引擎；（2）查询条件包含子查询、存储函数、用户自定义函数，存储引擎无法解析函数逻辑，无法下推；（3）涉及全文索引的MATCH()AGAINST()查询，不支持ICP；（4）查询需要回表到聚簇索引的条件无法下推，仅二级索引包含的列的条件可以下推。MySQL8.0针对ICP做的优化：8.0开始支持分区表的二级索引下推，5.7及之前版本分区表的二级索引无法使用ICP，8.0优化后分区表的二级索引查询性能提升30%左右。3.联合索引的最左前缀原则的底层原理是什么？哪些场景会打破最左前缀原则？答案：最左前缀原则指使用联合索引时，查询条件需要从联合索引的最左列开始匹配，不能跳过中间列，否则无法命中后续列的索引。底层原理是联合索引的B+树叶子节点按索引列的顺序排序：首先按第一列的值排序，第一列值相同的节点再按第二列排序，以此类推，只有确定了前序列的等值条件，才能快速定位后续列的范围，否则只能遍历整个索引。打破最左前缀原则的场景仅存在于MySQL8.0及以上版本的跳跃扫描特性：当联合索引的首列基数极低（比如枚举值不超过10个）时，优化器会自动拆分首列的所有枚举值，分别匹配后续列的查询条件，无需指定首列的等值条件也能命中联合索引。比如联合索引为idx_status_uid(status,user_id)，status仅有0、1、2三个值，查询SELECT*FROMorderWHEREuser_id=123时，8.0会自动拆分为status=0ANDuser_id=123UNIONALLstatus=1ANDuser_id=123UNIONALLstatus=2ANDuser_id=123，实现索引命中。需要注意的是，如果首列基数较高，跳跃扫描的性能反而低于全表扫描，优化器不会选择该策略。4.什么是MVCC？底层实现原理是什么？可重复读和读已提交隔离级别下的MVCC有什么差异？答案：MVCC即多版本并发控制，是InnoDB引擎为了提升读写并发性能、实现读写不阻塞的核心机制，原理是通过读取数据的历史版本，不需要加锁就可以实现非阻塞的读操作，高并发场景下整体性能可提升50%以上。底层实现依赖三个核心组件：（1）undo日志版本链：每行数据的聚簇索引中包含两个隐藏列：trx_id（最近一次修改该行数据的事务ID）、roll_pointer（指向undo日志中该行数据的上一个版本的指针），每次修改数据都会生成一个新的undo版本，通过roll_pointer串联成版本链。（2）ReadView：一致性读视图，包含四个核心字段：m_ids（当前活跃的未提交的事务ID集合）、min_trx_id（活跃事务的最小ID）、max_trx_id（下一个要分配的事务ID）、creator_trx_id（当前创建ReadView的事务ID）。（3）可见性判断规则：遍历undo版本链，找到第一个符合可见性规则的版本返回：①如果版本的trx_id等于creator_trx_id，说明是当前事务自己修改的，可见；②如果版本的trx_id<min_trx_id，说明修改该版本的事务已经提交，可见；③如果版本的trx_id>=max_trx_id，说明修改该版本的事务是在ReadView创建之后启动的，不可见；④如果版本的trx_id在min_trx_id和max_trx_id之间，判断trx_id是否在m_ids中，如果在说明事务还未提交，不可见，如果不在说明已经提交，可见。两种隔离级别下的MVCC差异：读已提交（RC）级别下，每次执行快照读都会生成一个新的ReadView，所以同一个事务内的多次快照读可能拿到不同的版本，出现不可重复读；可重复读（RR）级别下，只有事务执行第一次快照读的时候生成ReadView，后续所有快照读都复用同一个ReadView，所以同一个事务内的多次快照读拿到的版本一致，解决了不可重复读问题。5.MySQL的事务隔离级别有哪些？分别解决了什么问题？8.0默认隔离级别和5.7相比有什么性能差异？答案：MySQL的事务隔离级别从低到高分为4类：（1）读未提交：可以读取其他事务未提交的修改，存在脏读、不可重复读、幻读问题，生产环境几乎不用。（2）读已提交：只能读取其他事务已经提交的修改，解决了脏读问题，仍存在不可重复读、幻读问题，适合对一致性要求不高、并发要求高的场景。（3）可重复读：同一个事务内的多次快照读结果一致，解决了脏读、不可重复读问题，通过next-keylock解决了当前读的幻读问题，是MySQL的默认隔离级别。（4）串行化：所有事务串行执行，解决了所有一致性问题，但性能极低，仅适合对数据一致性要求极高、并发极低的场景。MySQL8.0默认隔离级别仍为可重复读，但相比5.7的RR级别有明显性能提升：①8.0的RR级别下，唯一索引的等值查询命中唯一记录时，next-keylock会自动退化为记录锁，大幅减少锁冲突概率，高并发更新场景性能提升30%左右；②8.0的undo日志支持独立表空间部署、自动截断回收，无需手动清理undo碎片，大事务场景下的回滚和MVCC版本链遍历性能提升25%以上；③8.0的事务元数据存储在共享数据字典中，替代了5.7的frm文件存储，事务启动和元数据查询的速度提升40%以上。6.什么是意向锁？意向共享锁（IS）、意向排他锁（IX）和表级共享锁（S）、表级排他锁（X）的兼容关系是什么？解决了什么问题？答案：意向锁是InnoDB引擎的表级锁，事务在加行级共享锁/排他锁之前，会先给表加对应的意向共享锁/意向排他锁，用于标识当前表有行级锁正在被持有或即将被加锁。四类锁的兼容矩阵如下（横向为已持有锁，纵向为请求锁）：请求锁ISIXSXIS兼容兼容兼容冲突IX兼容兼容冲突冲突S兼容冲突兼容冲突X冲突冲突冲突冲突7.MySQL的redolog、undolog、binlog的作用、写入机制、区别分别是什么？8.0对这三类日志做了哪些优化？答案：三类日志的核心信息如下：（1）redolog：InnoDB引擎层的物理日志，记录数据页的物理修改，用于崩溃恢复，保证数据持久性。写入机制遵循WAL（预写日志）原则，修改数据页前先将修改记录写入redologbuffer，事务提交时按innodb_flush_log_at_trx_commit参数的配置刷盘：0表示每秒刷盘，性能最高但崩溃时最多丢1秒数据；1表示每次提交都刷盘，数据最安全但性能最低；2表示每次提交写到操作系统缓存，每秒刷盘，性能折中。（2）undolog：InnoDB引擎层的逻辑日志，记录数据修改前的版本，用于事务回滚和MVCC版本链。写入机制：修改数据页前先写undo日志，undo日志的修改也会被记录到redolog中保证持久性，事务提交后undo日志会被标记为可回收，由purge线程异步清理。（3）binlog：Server层的逻辑日志，记录所有DDL和DML的逻辑操作，用于主从复制和数据恢复。写入机制：事务执行过程中将修改记录写入binlogcache，事务提交时按sync_binlog参数配置刷盘：0表示由操作系统控制刷盘，1表示每次提交都刷盘，N表示每N个事务刷盘。三类日志的核心区别：层级不同（redo/undo是引擎层，binlog是Server层）、类型不同（redo是物理日志，undo/binlog是逻辑日志）、用途不同（redo用于崩溃恢复，undo用于回滚和MVCC，binlog用于主从复制和数据恢复）、写入时机不同（redo在修改数据前写，binlog在事务提交时写）、生命周期不同（redo是循环写入，undo写完后可回收，binlog是追加写入，可保留多天。MySQL8.0的优化点：①redolog支持并行写入，减少日志缓冲区的锁竞争，高并发写入场景性能提升20%左右；②undolog支持单独部署到高速存储，支持自动截断，无需手动清理undo表空间碎片；③binlog支持writeset并行复制，从库可以基于事务修改的行集合并行回放，主从延迟降低80%以上，原生支持binlog加密，无需第三方插件。8.explain执行计划的各个字段核心含义是什么？重点需要关注哪些字段？答案：explain执行计划的核心字段及含义如下：（1）id：SQL执行的优先级，id值越大越先执行，id值相同则从上到下依次执行。（2）select_type：查询类型，常见值包括SIMPLE（简单查询，无关联/子查询）、PRIMARY（主查询，外层查询）、SUBQUERY（子查询）、DERIVED（派生表查询）、UNION（联合查询）。（3）table：当前执行步骤操作的表。（4）type：存储引擎的访问类型，性能从高到低依次为system>const>eq_ref>ref>range>index>ALL，生产环境要求至少达到range级别，核心查询需要达到ref级别。（5）possible_keys：可能命中的索引列表。（6）key：实际命中的索引，为NULL表示未使用索引。（7）key_len：实际使用的索引长度，单位为字节，可用于判断联合索引的命中列数，长度越短性能越高。（8）rows：优化器预估需要扫描的行数，数值越小性能越高。（9）Extra：额外执行信息，核心标识包括：Usingindex（覆盖索引，无需回表，性能优秀）、Usingindexcondition（索引下推，性能优秀）、Usingwhere（存储引擎返回数据后Server层过滤，性能一般）、Usingfilesort（文件排序，性能较差）、Usingtemporary（使用临时表，性能极差）。重点需要关注type、key、rows、Extra四个字段，可直接定位SQL的性能瓶颈。9.千万级以上大表的分页查询如何优化？答案：大表分页查询的核心优化手段包括：（1）覆盖索引+子查询优化：针对大偏移量的limit查询，先通过覆盖索引定位到起始主键，再通过主键查询完整数据，避免全表扫描和大量回表。比如将SELECT*FROMorderLIMIT1000000,10优化为SELECT*FROMorderWHEREid>=(SELECTidFROMorderORDERBYidLIMIT1000000,1)LIMIT10，性能可提升10倍以上。（2）游标分页：针对滚动分页场景，前端传递上一页的最大主键或排序字段值，后端直接按范围查询，避免大偏移量的limit。比如上一页最后一条数据的id为1000000，查询下一页的SQL为SELECT*FROMorderWHEREid>1000000LIMIT10，性能几乎不受数据量影响。（3）冷热数据分离：将超过3个月的历史冷数据归档到归档库，主库仅保留热点数据，单表数据量控制在5000万以内，从根源上降低分页查询的扫描量。（4）分库分表：当单表数据量超过1亿时，按时间、用户ID等维度水平拆分，拆分后每个子表数据量控制在1000万以内，分页查询性能可保持稳定。（5）搜索引擎查询：针对多条件复杂分页查询，将数据同步到Elasticsearch，由ES提供分页查询能力，MySQL仅存储原始数据，避免复杂条件导致的全表扫描。10.