简介这份 MySQL 面试题知识点总结资源面向准备数据库岗位面试的求职者与需要系统梳理 MySQL 核心原理的开发者内容覆盖关系型与非关系型数据库区别、SQL 执行步骤、索引底层数据结构与类型、MyISAM 与 InnoDB 的 B 树索引差异、B 树设计原因、普通索引与唯一索引的选择、覆盖索引和索引下推、索引失效场景等高频考点。资源为 1 个 docx 文档约 40KB便于阅读、标注和打印复习。目前已有 251 人学习。文档从基础概念到进阶优化逐层展开还包含 change buffer、redo log 与 binlog 等日志机制并针对常见坑点给出实用建议如优先使用非唯一索引、避免模糊匹配与函数运算导致索引失效。适合考前冲刺与日常查漏补缺可帮助读者快速建立 MySQL 知识框架提升面试应答的准确性与深度。1. MySQL 面试题为什么总绕着锁和事务转一个行锁案例引出全部高频考点「行锁变表锁」是 MySQL 面试题里我最喜欢的第一问。场景很具体一张订单表除了主键 id只有 order_no 一个普通列执行UPDATE t_order SET status 2 WHERE order_no NO2024001并发一高整表更新开始排队。你抓执行计划type 是 ALL走的是全表扫描InnoDB 对扫描到的每一行都加锁「行锁」在实现上就成了「表锁」。这一题把索引设计、InnoDB 锁机制、事务隔离级别和慢查询排查全串了起来也正好是 MySQL 面试题反复出现的高频骨架。下面不按八股背题而是拆到每个考点背后的原理、验证手段和踩坑点让你在现场能把答案自己推出来而不是只记得半句概念。2. 索引设计与 EXPLAIN 验证从 B 树到最左前缀的送命题索引是 MySQL 面试题里出现频率最高的类目几乎每轮都会遇到。面试官问「为什么用 B 树」「联合索引为什么是最左前缀」「这条 SQL 为什么没走索引」本质考的都是你有没有从结构上去推导而不是背结论。这章我们先从 B 树推出三个基本结论再用 EXPLAIN 把它们落到验证上。2.1 从 B 树结构推出三个结论扇出、最左前缀、回表「为什么 MySQL 用 B 树而不用红黑树」几乎是索引类题目的开场。我的回答思路是先讲磁盘 IO索引存在的意义是减少随机 IO而 B 树内节点不存完整行数据一个 16KB 的页能放下几百个键树非常「矮胖」三层左右就能覆盖千万行。每次从根到叶子大约三到四次 IO随机 IO 被压到常数级别。叶子节点按序排列相邻叶子用链表串起来范围查询从第一个命中的叶子往后扫就行。红黑树虽然平衡但层数远高于 B 树节点在磁盘上分散预读效果差。这就是第一个结论B 树是磁盘场景下专门为「少读盘、好范围扫」设计的结构。第二个结论和最左前缀直接相关。联合索引(a, b, c)的叶子节点先按 a 排序a 相同再按 b最后按 c整体是字典序。你写WHERE b 1在索引树里根本定位不出连续区间因为 b 是第二排序键写WHERE a 1 AND c 2能用到 a但 c 用不上因为中间隔着 b。所以最左前缀不是 MySQL 的规则偏好而是索引节点本身的排序结构决定的。反过来WHERE a 1 ORDER BY b可以直接沿着索引取数省掉 filesort这也是「MySQL 排序」类题目里很常考的一个点。第三个结论是回表和覆盖索引。二级索引叶子节点存的是索引键加主键值查询时先到二级索引找主键再回聚簇索引取整行。如果查询的列恰好都包含在二级索引里MySQL 不需要回表Extra会显示Using index。面试里被问到「为什么不要SELECT *」时除了网络流量更值得答的是覆盖索引SELECT *基本注定回表而按需查列有可能让整个查询只在一棵索引树上完成。2.2 EXPLAIN 读法type、key、rows、Extra 四列怎么组合讲完结构面试题多半会跟进一条 SQL 让你判断走不走索引。常见做法是直接上 EXPLAIN别猜。EXPLAIN SELECT * FROM t_order WHERE order_no NO2024001\G如果t_order只在 id 上有主键、order_no没有索引执行结果里会看到type: ALL、key: NULLrows接近全表行数。注意rows是优化器基于统计信息的估算值不是实际扫描行数不同版本略有差异但量级足以说明问题。判断顺序我一般固定四步先看type再看key然后看rows最后读Extra。type从好到差大致是system const eq_ref ref range index ALL。面试里能分清ref、range、ALL就够用ref是非唯一索引等值匹配range是范围扫描ALL是全表扫描。key为 NULL 说明优化器放弃了索引key有值还要对比Extra里有没有Using index有则说明是覆盖索引。Using filesort和Using temporary是另外两个高频关注点分别代表排序和去重没有用好索引SQL 优化时优先把这两项消掉。2.3 索引失效的四个现场与验证方法「索引失效」是 MySQL 面试题里最容易被背成口诀的部分。我不建议背建议自己跑一遍-- 第 1 类函数包列 EXPLAIN SELECT * FROM t_order WHERE DATE(created_at) 2024-01-01\G -- 第 2 类隐式类型转换order_no 是 VARCHAR EXPLAIN SELECT * FROM t_order WHERE order_no 1024\G -- 第 3 类前导模糊 EXPLAIN SELECT * FROM t_order WHERE order_no LIKE %2024%\G -- 第 4 类OR 两边一个有索引、一个没有 EXPLAIN SELECT * FROM t_order WHERE status 1 OR source app\G四个场景的失效原因不一样。函数包列等于让索引树没法做值比较因为比较的是函数结果隐式转换本质是优化器对列做了 CAST同样是绕过索引排序前导模糊无法确定扫描起点只能全扫OR 要合并两个条件的结果集其中一边没有索引时优化器很可能选择全表扫。每个场景都能在 EXPLAIN 里看到type: ALL或key: NULL验证成本很低。注意一些边界LIKE abc%可以走索引只有前导%才危险!、IS NOT NULL在不同版本和统计信息下可能走也可能不走千万不要背死结论。MySQL 8.0 还支持函数索引写法类似ALTER TABLE t_order ADD INDEX ((DATE(created_at)))但函数索引对前缀查询不一定友好落地前一样要用 EXPLAIN 验证。面试时能说出「我加索引前先 EXPLAIN加索引后再验证一次」比背十条失效规则更有说服力。3. 事务隔离级别与 MVCC 可见性一致性读的边界在哪事务题目在 MySQL 面试题里的出现频率仅次于索引而且经常和锁混在一起问。面试官的典型套路是先问「RR 和 RC 有什么区别」再追问「MVCC 怎么做到的」最后抛一个「两个事务并发更新同一行会怎样」。这章把这条线拆开。3.1 隔离级别矩阵脏读、不可重复读、幻读分别被谁解决先落到面试官最常问的对照表。四个隔离级别对应解决的并发问题如下隔离级别脏读不可重复读幻读InnoDB 实现READ UNCOMMITTED可能可能可能读最新版本不加锁READ COMMITTED不会可能可能每次 SELECT 创建新的 read viewREPEATABLE READ不会不会基本不会事务内复用 read view配合 next-key lockSERIALIZABLE不会不会不会读写都加锁并发最低这里有一个很多人会答错的点官方定义里 RR 仍然存在幻读风险但 InnoDB 在 RR 下用间隙锁把常见的幻读挡掉了所以生产环境里 InnoDB 默认 RR 并不会像课本说的那么容易幻读。MySQL 早期默认 RR 还有主从复制方面的历史原因面试问到可以提一句不必展开。实操层面两个会话验证隔离级别最直接-- 会话 A SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT v FROM t WHERE id 1; -- 会话 B UPDATE t SET v 2 WHERE id 1; COMMIT; -- 会话 A 再次查询RR 下仍是旧值 SELECT v FROM t WHERE id 1; COMMIT;换成READ COMMITTED重复同样步骤第二次SELECT会读到 2。面试里把这组实验讲清楚比背概念有用得多因为这证明了你是实际操作过而不是只看了文档。3.2 MVCC 版本链与 read view一行记录的前世今生MVCC 是事务题里最容易让新手发怵的部分其实拆开只有两件事版本链和 read view。InnoDB 聚簇索引行上有两个隐藏列db_trx_id记录最后修改该行的事务 IDdb_roll_ptr指向 undo log 里的旧版本。每次 UPDATE 不是直接覆盖而是把旧版本写进 undo log再用db_roll_ptr把新旧版本串成一条链。这条链就是一致性读要遍历的「历史版本」。read view 在事务做一致性读时生成核心是三个信息创建时刻的活跃事务列表、活跃事务里的最小 ID、当前已分配的最大事务 ID。可见性判断可以简化成两句话版本事务 ID 小于最小活跃事务 ID说明它早已提交可见版本事务 ID 大于当前最大事务 ID说明它在我创建视图之后才启动不可见落在两者之间则看它是否还在活跃事务列表里不在就可见。判断不可见时就顺着db_roll_ptr往旧版本继续找直到找到可见版本。RR 和 RC 在这套机制里的差别只有一个RR 下 read view 在事务第一次一致性读时创建之后整个事务复用RC 每次 SELECT 都新建 read view。所以同一事务里两次查询结果是否可能不同答案就是隔离级别决定的。面试问「RR 为什么可重复读」本质答这一句就够了。3.3 一致性读与当前读为什么 SELECT 不加锁UPDATE 要加锁MVCC 的普通SELECT是一致性读也叫快照读直接从版本链里挑可见版本不加锁所以不阻塞其他事务的写入。SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE走的是当前读读最新已提交版本并且对命中的记录加锁。这是事务和锁题目的交汇点为什么一条SELECT不会和UPDATE互相阻塞但两条UPDATE会。面试里常追问「两个事务同时 UPDATE 同一行会怎样」。正确答法是后到的 UPDATE 会进入锁等待等待前一个事务提交或回滚等待超过innodb_lock_wait_timeout就报锁等待超时如果双方各自持有对方需要的锁则形成死锁由 InnoDB 死锁检测机制回滚其中一个事务。到这里题目自然就从事务滑向了锁下一章专门拆锁。4. 锁机制与死锁排查间隙锁、MDL、死锁日志怎么读锁是 MySQL 面试题里最容易被说成「玄学」的部分因为它看不见摸不着只能靠日志和实验去推。但只要抓住一个主线就好办锁的对象是索引记录锁的生命周期是事务。这章把行锁、间隙锁、MDL 和死锁日志逐个讲透。4.1 行锁的两个维度和兼容性共享锁与排他锁InnoDB 行锁按模式分共享锁 S 和排他锁 X按对象分记录锁、间隙锁、next-key lock。兼容性只有两条规则S 和 S 兼容其他组合都互斥。用表表示当前请求已持有 S已持有 XS兼容不兼容X不兼容不兼容面试题喜欢问「SELECT ... FOR UPDATE会阻塞普通 SELECT 吗」答案是不会因为普通 SELECT 是快照读不加 S 锁。但如果另一个事务也执行SELECT ... FOR UPDATE就会阻塞。这里最容易踩坑的是把「普通 SELECT 不加锁」和「SELECT 完全不阻塞」等同起来实际上加了FOR UPDATE就成了当前读。行锁在 InnoDB 里本质是索引记录锁锁在索引项上。如果一条 UPDATE 的 WHERE 条件没有索引InnoDB 要扫描全表就会对扫描过程中访问到的每一行加锁表现成行锁退化为表锁。回到第 1 章的案例验证方法就是EXPLAIN UPDATE t_order SET status 2 WHERE order_no NO2024001\G看到type: ALL时就要警惕这行命令在高并发下会拖垮整个表。面试里答这个题先给结论「取决于 WHERE 是否命中索引」再补 EXPLAIN 的判断方法比直接说「InnoDB 是行锁」严谨得多。4.2 间隙锁与 next-key lockRR 下幻读靠什么拦住间隙锁锁的是索引记录之间的区间next-key lock 是记录锁加前面间隙锁的组合。RR 隔离级别下InnoDB 对当前读默认使用 next-key lock。举一个面试高频场景-- 会话 A START TRANSACTION; SELECT * FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE; -- 会话 B INSERT INTO t (id, v) VALUES (15, 1);会话 B 的插入会阻塞因为 id15 落进了会话 A 锁住的区间。这就是 InnoDB 挡幻读的物理方式不仅锁住存在的那几条记录还锁住可能出现新记录的间隙。面试题如果问「明明只锁了两行为什么插入却被卡住」答案就在这里。这个机制也有代价。两个事务各自锁了相邻区间插入时互相进入对方间隙就可能死锁。线上最常见的现象是「一条 INSERT 报死锁但你根本看不出它和哪条 SQL 冲突」多半就是间隙锁在起作用。8.0 里加锁分析可以用performance_schema.data_locks表比SHOW ENGINE INNODB STATUS更直观面试能提到这个表会加分。4.3 MDL 锁一条 DDL 怎样把整个表查询堵死MDL 锁是元数据锁保护表结构不在 DDL 期间被 DML 修改。它的坑非常现实执行ALTER TABLE需要 MDL 写锁但如果表上有一个长事务一直不提交DDL 就会停在Waiting for table metadata lock而后续所有查询都排在 DDL 后面表现成整个表查询卡死。我的排查习惯是先看SHOW PROCESSLIST找State列里大量Waiting for table metadata lock的会话再去performance_schema.metadata_locks看谁持有锁。最常见的根源是某个连接autocommit0却忘了提交或业务代码里事务开着没关闭。解决方式是先把长事务 kill 掉再执行 DDL或者用pt-osc、gh-ost这类在线改表工具把 MDL 写锁持有时间压到最短。面试里讲这个案例比背ALTER TABLE语法有价值得多因为它是真实线上故障。4.4 死锁日志解读从 LATEST DETECTED DEADLOCK 看出两个事务的等待方向死锁排查不能靠猜要看日志。SHOW ENGINE INNODB STATUS\G输出里定位LATEST DETECTED DEADLOCK段落重点看两个TRANSACTION块*** (1) TRANSACTION: ... *** (1) HOLDS THE LOCK(S): ... *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ... *** (2) TRANSACTION: ... *** (2) HOLDS THE LOCK(S): ... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: ...上面是截取的核心结构真实输出会标出每个事务持有的锁对象和等待的锁对象。读日志时先画等待方向事务 1 持有 A 锁等待 B 锁事务 2 持有 B 锁等待 A 锁环就形成了。解决方向也很明确让所有事务按相同顺序加锁或者缩小事务范围减少持锁时间。有两个参数面试经常被问innodb_lock_wait_timeout默认 50 秒是锁等待超时时间innodb_deadlock_detect默认开启死锁时 InnoDB 会回滚代价较小的事务。关掉死锁检测可以避免高并发下的检测开销但代价是锁等待超时才能发现我一般不建议关。8.0 还提供了NOWAIT和SKIP LOCKED适合任务队列表避免更新同一条记录时无谓排队这也是一个不错的加分点。5. 主从复制与慢查询排查复制延迟和慢 SQL 定位的实操清单索引、事务、锁都聊完面试官通常会转向「你线上怎么定位问题」。MySQL 面试题问到这里实际考的是你有没有处理过真实故障。这章讲两条最常被问的线主从复制延迟和慢 SQL 排查。5.1 复制链路与延迟判定Seconds_Behind_Master 为什么不可尽信主从复制链路可以简单分成四段主库写 binlog主库 Dump 线程把 binlog 推给从库 IO 线程从库 IO 线程写 relay log从库 SQL 线程重放 relay log。面试题常问「从库延迟怎么看」答案先是SHOW SLAVE STATUSSHOW SLAVE STATUS\G重点关注几列Slave_IO_Running和Slave_SQL_Running是否都是 YesSeconds_Behind_Master的值以及Read_Master_Log_Pos和Exec_Master_Log_Pos的差值。IO 线程断了或 SQL 线程停了都会导致主从数据不一致这比延迟本身更严重。Seconds_Behind_Master是一个容易误导人的指标。它表示从库 SQL 线程相对主库当前时间的落后秒数是从库自己算出来的估值。常见问题如果主库最近没有新事务它的值会是 0但此时从库可能还有大量 relay log 没重放完。所以我的习惯是不只看这一列同时对比Read_Master_Log_Pos和Exec_Master_Log_Pos再用主从两侧实际查一条最新数据确认。面试里能说出「这个指标不能证明没有延迟」就已经比大部分背概念的人强了。复制延迟的常见成因也值得准备从库重放是单线程时主库并发高就会积压8.0 可以通过replica_parallel_workers开启并行复制大事务和 DDL 在从库要重新执行耗时远高于主库无主键表更新时从库回表慢非常容易被忽略。排查顺序是先看SHOW PROCESSLIST里从库 SQL 线程在跑什么 SQL再拿 binlog 里的事务大小做判断。5.2 慢查询日志打开阈值、确认文件、用 mysqldumpslow 归类慢查询日志是 SQL 优化最直接的入口。面试题问「给你一条慢 SQL 怎么优化」第一步不是改 SQL而是先确认它为什么会慢。生产上临时开启慢日志的常见做法是SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON; SHOW VARIABLES LIKE slow_query_log_file;参数说明long_query_time单位是秒线上默认 10 秒排查时临时调到 1 秒log_queries_not_using_indexes会把没走索引的查询也记进日志经常能提前暴露潜在问题。注意SET GLOBAL只对之后的新连接生效并且重启后失效正式环境要把参数写进配置文件。拿到慢日志文件后我一般先用mysqldumpslow -s t按耗时排序找出 TOP N 的 SQL再单独对每条 SQL 跑 EXPLAIN。定位方向通常是三类没走索引、扫描行数过多、排序或临时表太重。没走索引看key和type扫描行数看rows排序问题看Extra里的Using filesort和Using temporary。把这三类列出来优化动作就清楚了。5.3 常问性能参数不背默认值讲怎么验证收益面试官问「MySQL 有哪些重要参数」时最怕听到一堆默认值。参数只有和「改了之后怎么看收益」连在一起才值钱。我常用来答题的几项如下参数含义与常见设置怎么看是否有效innodb_buffer_pool_sizeInnoDB 缓存池生产常见按物理内存 60% 左右SHOW ENGINE INNODB STATUS 里的 Buffer pool hit rateinnodb_flush_log_at_trx_commit1 最安全每次提交刷盘2 性能更好但可能丢最近事务结合业务对数据丢失的容忍度选sync_binlog1 时每次提交同步 binlog配合上面保证不丢事务看每秒事务提交量判断刷盘开销max_connections默认 151连接爆满时报 Too many connectionsSHOW PROCESSLIST 看是否有大量 sleep 连接long_query_time慢查询阈值排查时临时调 1慢日志里 SQL 数量是否下降binlog_format生产一般用 ROW复制更可靠对比 ROW 和 STATEMENT 的 binlog 体积答这些参数时我喜欢补一句调参不是拍脑袋改完要看对应指标变化比如innodb_buffer_pool_size调大后缓存命中率有没有上升。面试官要的是「你会不会用工具验证」而不是「你记了多少数字」。5.4 主从与慢查询的四个高频坑现象、原因、解决这节把上面提到的坑集中成短条目都是我见过或处理过的高频情况。现象一从库Seconds_Behind_Master为 0但业务读从库明显读到旧数据。原因主库暂时没有新事务从库 SQL 线程落后但差值被算成 0。解决用Read_Master_Log_Pos和Exec_Master_Log_Pos的差值辅助判断别单看这个字段。现象二一条 SQL 实际执行 3 秒慢查询日志里却没有。原因long_query_time还是默认 10 秒3 秒小于阈值。解决临时SET GLOBAL long_query_time 1并确认当前会话没有用SET SESSION覆盖全局值。现象三MySQL 8.0 装好后老客户端连接报caching_sha2_password相关错误。原因8.0 默认认证插件是caching_sha2_password老驱动不支持。解决升级驱动或建用户时指定IDENTIFIED WITH mysql_native_password BY ...。现象四应用报Too many connections。原因连接池配置过大、有连接泄漏或某个长事务把连接占住不放。解决先SHOW PROCESSLIST看 State是 sleep 连接多还是卡在 MDL 锁等待再决定调大max_connections还是清连接。6. 面试答法的最后一公里把「背概念」变成「讲边界」前面几章的原理和命令都齐了最后聊怎么在现场组织语言。面试官问概念题时最忌讳一上来铺背景。比如问「MySQL 为什么用 B 树」先给结论「为了少读磁盘」再补两个支撑点内节点不存数据、扇出大所以树矮层数少叶子有序链表范围查询天然友好。先结论后边界的结构能让对方在 30 秒内抓住你的思路。遇到「一条 UPDATE 会锁表吗」这类题套同一个结构先答「取决于 WHERE 条件是否命中索引」再补「用 EXPLAIN 看 type 是 ALL 时行锁就会退化成表锁」。面试官追一句「那你怎么验证」你就能顺手把第 2 章的 EXPLAIN 四步读法讲出来。这套回答没有一句是背诵全是推导出来的。平时准备时我建议你建一个测试库把本章前面所有验证命令都跑一遍确认索引失效的四个场景、亲手制造一次间隙锁阻塞、用SHOW ENGINE INNODB STATUS看一次死锁。过程中你会记住输出长什么样面试被追问到细节时能描述出真实字段而不是含糊带过。面试最后让讲案例时我习惯选 MDL 锁那次长事务没提交一条 DDL 卡住全表查询堆积抓 processlist找到Waiting for table metadata lockkill 长事务。从现象到原因到解决三分钟刚好讲完。这样的收尾比罗列参数更像一个真正干过活的人。希望这套方法能帮你在下一次 MySQL 面试里把题目变成证明自己的机会。本文还有配套的精品资源点击获取
