MySQL 面试题

1. MySQL 慢SQL排查与索引优化

❓ 题目： 一条平时运行很快的 SQL 突然变慢了，你会从哪些方向入手排查？

追问1：EXPLAIN 执行计划中，type 字段从好到差有哪些取值？什么情况下 possible_keys 不为空但 key 为空？追问2：联合索引 (a, b, c)，查询条件 WHERE b = 1 AND a > 10 AND c = 2 实际用到了索引的哪几列？为什么？

💡 答案：

主问题： 一条 SQL 突然变慢，排查思路分几个层面。第一步，确认是不是偶尔慢——先用 SHOW PROCESSLIST 看当前是否有大量连接堆积，或者是否有其他长事务/DDL 操作在持有锁。第二步，用 EXPLAIN 看执行计划是否发生了改变——MySQL 优化器可能因为统计信息不准选择了错误的索引（或者从走索引变成了全表扫描）。统计信息可以用 ANALYZE TABLE 重新收集。第三步，检查是否出现了锁等待——SELECT * FROM performance_schema.data_locks 或 information_schema.INNODB_TRX 可以查看当前事务和锁的情况，如果 SQL 长时间处于 “Waiting for table metadata lock” 或行锁等待状态，那就是并发事务导致的。第四步，系统层面——检查磁盘 IO 是否打满、buffer pool 命中率是否下降（SHOW ENGINE INNODB STATUS 可以看 buffer pool 的命中率和脏页比例）。还有一个容易被忽略的点：如果这条 SQL 是分页查询，可能是数据量增长后 offset 过大导致的——深分页的问题本质上是 MySQL 需要扫描并丢弃前面大量行。

追问1： 从好到差大致是：system > const > eq_ref > ref > range > index > ALL。system 是表只有一行数据的特例；const 是主键或唯一索引的等值查询；eq_ref 是关联查询中驱动表的一行在被驱动表中最多匹配一行（主键或唯一键关联）；ref 是普通索引的等值匹配；range 是索引范围扫描；index 是全索引扫描，虽然比全表扫描 ALL 好但本质上也遍历了整个索引树；ALL 是全表扫描，性能最差。possible_keys 有但 key 为空，说明优化器认为有索引但用索引还不如全表扫描。常见原因：查询条件的区分度太低（比如对性别字段建索引），走索引需要回表而优化器估算回表代价大于直接全表扫描，或者是统计信息不准确导致优化器误判。

追问2： 实际只用到了索引的 a 和 b 两列，c 没有用到。联合索引遵循最左前缀原则，查询会先按 a 排序过滤，然后按 b 过滤。条件是 b = 1 AND a > 10 AND c = 2，MySQL 优化器会先把它调整为 a > 10 AND b = 1 AND c = 2。a > 10 是范围查询，范围查询之后的索引列 c 就无法使用了。所以 a 走了范围扫描，b 用来做索引条件下推（ICP，在索引层面过滤），但 c 完全没能用到索引，只能在回表后再过滤。注意 b = 1 是等值，所以虽然 a 是范围，b 仍然可以参与索引过滤，只是范围列 a 之后的 c 失效。

📌 易错点 / 加分项：

能提到索引条件下推（ICP）和 Multi-Range Read（MRR）这两个优化
type=index 和 type=ALL 的区别很多候选人说不清，前者遍历索引树、后者遍历数据页
能说清楚 Extra 字段中 Using index condition、Using where、Using index 的区别

2. MySQL InnoDB B+树索引原理

❓ 题目： InnoDB 为什么选择 B+ 树作为默认索引数据结构，而不选 B 树或哈希表？

追问1：聚集索引（聚簇索引）和二级索引（辅助索引）在数据存储上有什么不同？二级索引的回表是什么过程？追问2：为什么建议主键自增，而且不要用长字符串？这与 B+ 树的物理存储有什么关系？

💡 答案：

主问题： InnoDB 选择 B+ 树主要基于五点考虑。与哈希表对比：哈希表只支持等值查询，不支持范围查询（BETWEEN、> <），而实际业务中范围查询非常普遍，哈希表不适合作为通用存储引擎的默认索引。与 B 树对比：B+ 树的所有数据只存储在叶子节点，非叶子节点只存索引 key，这使得非叶子节点能存更多的索引 key、出度更大、树更矮——同样的页大小，B+ 树的树高更小，磁盘 IO 次数更少；B+ 树的叶子节点用双向链表连接，范围查询只需找到起点然后顺序遍历链表，B 树因为没有叶子层的链表，范围查询需要在树的层次上反复回溯，效率低很多。另外 B+ 树的稳定性更好——所有查询最终都要走到叶子节点，查询耗时是均匀的。

追问1： 聚集索引的叶子节点存储的是”完整行数据”。每张 InnoDB 表必须有一个聚集索引，如果有主键则用主键，没有主键则用第一个非空唯一索引，都没有则 InnoDB 自动生成一个 6 字节的隐藏 row_id 作为聚集索引。二级索引的叶子节点存储的是”索引列的值 + 对应的聚集索引 key（主键值）“。回表过程：通过二级索引找到主键值后，再用主键值回到聚集索引的 B+ 树中查找完整行数据，相当于一次额外的 B+ 树查找。如果查询只需要主键和索引列的值，可以直接从二级索引的叶子节点获取而不回表，这就是”覆盖索引”。

追问2： 自增主键插入时数据总是追加到 B+ 树的最右侧叶子节点。如果节点满了，分裂时原节点保留原有数据、新数据进入新节点，页面利用率高且数据连续存储，物理 IO 效率最佳。如果主键不是自增的（比如用 UUID），插入位置是随机的——经常需要在 B+ 树已有页面的中间位置插入，导致频繁的页分裂和页内数据搬迁，页空间利用率降低（页分裂往往产生约 50% 的碎片），而且数据页在磁盘上变得不连续，随机 IO 大幅增加。长字符串的问题在于：一是比较开销大——B+ 树内部查找每次都要比较 key 大小，字符串比较比整数比较慢得多；二是存储占用大——非叶子节点存 key，key 越长每个节点能装的 key 越少，树就越高，IO 次数越多；三是二级索引叶子节点存主键值，主键越长，每个二级索引叶子节点能装的条目越少，二级索引树也越高——所以长主键的伤害是连锁反应，越大越慢。

📌 易错点 / 加分项：

InnoDB 页默认 16KB，B+ 树每层能存多少数据可以估算——按 key 大小除一下就知道树高
覆盖索引（Using index）不需要回表，Extra 中看到它是好事
页分裂除影响写入性能外还会造成”数据空洞”——插入大量随机主键数据后表空间比预期大很多

3. MySQL 事务隔离级别与 MVCC

❓ 题目： MySQL InnoDB 四个事务隔离级别分别解决了什么问题？脏读、不可重复读、幻读是如何被解决的？

追问1：InnoDB 的 MVCC 机制是如何工作的？ReadView 在什么时机创建，不同隔离级别下创建时机有什么不同？追问2：RR 隔离级别下真的完全解决幻读了吗？有没有特殊情况下 RR 依然会出现幻读？

💡 答案：

主问题： SQL 标准定义四种隔离级别。读未提交（Read Uncommitted）：一个事务可以读到其他事务尚未提交的修改，性能最好但会引发脏读——读到一个可能被回滚的值。读已提交（Read Committed / RC）：一个事务只能读到其他事务已提交的修改，解决脏读；但同事务内两次相同的查询可能读到不同结果（不可重复读），因为其他事务提交了更新。可重复读（Repeatable Read / RR）：同一事务内多次查询结果一致，解决脏读和不可重复读，这是 InnoDB 的默认级别。串行化：所有事务串行执行，读写都加锁，解决所有并发问题但并发度极低。

InnoDB 解决脏读、不可重复读、大部分幻读的组合方案是 MVCC + 临键锁。MVCC 的核心是每行记录的两个隐藏列（trx_id：最后一次修改该行的的事务ID；roll_pointer：指向 undo log 的指针）+ ReadView（当前活跃事务 ID 的列表）。读操作通过 ReadView 判断哪些版本的 undo log 可见，从而读到合适的快照版本，避免了加锁，实现”读不加锁、写不影响读”的并发模式。

追问1： ReadView 包含四个核心信息：creator_trx_id（当前事务ID）、m_ids（生成 ReadView 时所有活跃事务的 ID 集合）、min_trx_id（m_ids 中的最小值）、max_trx_id（下一个即将分配的事务 ID）。RC 隔离级别下，每次快照读都会生成新的 ReadView，所以能读到其他事务新提交的数据（不可重复读的根源）。RR 隔离级别下，ReadView 仅在第一次快照读时生成，之后都复用同一个，因为 ReadView 不变，通过 undo log 回滚到的数据版本也不变，实现了可重复读。判断某行版本是否可见的规则：如果 trx_id == creator_trx_id，说明是自己改的，可见；如果 trx_id < min_trx_id，说明修改它的事务在 ReadView 创建前就提交了，可见；如果 trx_id >= max_trx_id，说明修改它的事务在 ReadView 创建后才开始，不可见，沿 roll_pointer 向上找更早的版本继续判断。

追问2： RR 没有完全解决幻读，至少有两种情况还会发生幻读。第一种：事务 A 执行 SELECT * FROM t WHERE id > 10（快照读、无锁），事务 B 插入一条 id=15 的数据并提交，事务 A 再次执行相同的快照读——不会幻读，因为 ReadView 没变。但如果事务 A 在两次快照读之间执行了 UPDATE t SET name = 'X' WHERE id > 10（当前读），这个 UPDATE 会看到 B 新插入的 id=15 行并将其修改，之后事务 A 再快照读就会看到 id=15 的新行——发生了幻读。第二种：SELECT ... FOR UPDATE 加临键锁，它通过间隙锁解决了其他事务插入数据的问题。但如果事务 A 用快照读，事务 B 插入并提交，事务 A 用 SELECT ... FOR UPDATE 当前读——会读到 B 插入的新行，再次产生幻读。所以精确的表述是：MVCC 的快照读解决了幻读，但一旦涉及当前读操作，RR 仍然可能幻读，这就是为什么还需要间隙锁的原因。

📌 易错点 / 加分项：

RC 和 RR 的默认选择是场景决定的——国内公司多用 RR，国外公司 RC 更常见
gap lock 只出现在 RR 级别，RC 下没有 gap lock，所以 RC 的插入并发度更高但会有幻读
能说清”快照读”（SELECT）和”当前读”（SELECT ... FOR UPDATE、UPDATE、DELETE）的区别说明理解深入

4. MySQL 锁的类型与死锁排查

❓ 题目： InnoDB 的行锁有哪几种类型？什么是间隙锁（Gap Lock）和临键锁（Next-Key Lock），它们的设计目的是什么？

追问1：一条 UPDATE ... WHERE name = 'xxx' 且 name 列有索引，它会加什么锁？如果 name 列没有索引呢？追问2：发生了死锁你会怎么排查？SHOW ENGINE INNODB STATUS 中的哪些信息对定位死锁有用？

💡 答案：

主问题： InnoDB 的行锁分为三种核心类型：记录锁（Record Lock）直接锁住索引记录本身；间隙锁（Gap Lock）锁住索引记录之间的间隙，阻止其他事务在间隙中插入新记录；临键锁（Next-Key Lock）是 Record Lock + 它之前的 Gap Lock 的组合，锁住一个”左开右闭”的区间，比如索引记录为 5,10,15，对 10 加临键锁则锁住 (5, 10] 区间。间隙锁和临键锁的设计目的是解决幻读问题——在 RR 隔离级别下，防止其他事务在锁定的范围内插入新数据。要注意 InnoDB 的所有锁都是加在索引上的，没有索引就无法精确锁住行。

追问1： 这取决于索引情况，这是 InnoDB 锁的一个关键坑点。如果 name 列有索引，InnoDB 会通过索引定位到匹配行，对匹配的索引记录加 Next-Key Lock。如果 name 列没有索引，InnoDB 必须在聚集索引上做全表扫描，但全表扫描途中对每一条扫描到的行都会尝试加锁，最终所有满足条件的行都会被锁住，不仅如此，全表扫描路径上的间隙也会被锁住，效果上接近锁全表。查询优化器选择全表扫描还是走索引会直接影响锁范围——同样的 SQL，执行计划变了，锁的范围就完全不一样。所以线上做 DML 操作前一定要看 EXPLAIN 确认走什么索引。

追问2： 排查死锁最核心的工具就是 SHOW ENGINE INNODB STATUS，关注其中的 LATEST DETECTED DEADLOCK 段落。它会告诉你最近一次死锁的详细信息：涉及的事务 ID、每个事务持有哪些锁（*** HOLD THE LOCK(S)）、正在等待哪个锁（WAITING FOR THIS LOCK TO BE GRANTED）、具体的 SQL 语句是什么、以及”InnoDB 选择了回滚哪个事务来解除死锁”（WE ROLL BACK TRANSACTION (1) 或 (2)）。此外还需要配合看 information_schema.INNODB_TRX 查看当前活跃事务，INNODB_LOCKS（5.7）或 performance_schema.data_locks（8.0+）查看当前持锁情况。定位到死锁 SQL 后，常见的解决策略：调整加锁顺序使不同事务按相同顺序获取锁；减少事务持有锁的时间（拆分大事务）；对高频并发更新操作做排队或合并；在某些场景下减少隔离级别到 RC（RC 下没有 gap lock，死锁概率降低但会有幻读）。

📌 易错点 / 加分项：

RC 隔离级别没有 Gap Lock，不是 InnoDB 不支持，是锁的实现策略不同
死锁不是 InnoDB 的 bug，是并发系统中不可避免的，重点在于快速发现和解除
8.0 把锁信息移到了 performance_schema.data_locks 和 data_lock_waits，面试时能提一句表明跟上了版本

5. SQL 优化实战与执行计划

❓ 题目： 一条 SELECT * FROM orders WHERE status = 'paid' ORDER BY create_time DESC LIMIT 10 现在执行很慢，你如何分析和优化？

追问1：建了索引 (status, create_time) 后，这个查询能完全走索引吗？Extra 里可能会出现什么？追问2：如果订单表有几千万行数据，即使走了索引分页到后面还是很慢（比如 LIMIT 1000000, 10），怎么办？

💡 答案：

主问题： 首先用 EXPLAIN SELECT ... 看执行计划，关注 type（是否 ALL 全表扫描）、key（是否用了索引）、rows（扫描行数预估）、Extra（看是否有 Using filesort）。如果 status 列和 create_time 列都没有索引，MySQL 需要全表扫描所有订单，再排序取前 10 条，数据量一大就极慢。优化方案：建立复合索引 (status, create_time)，查询先通过索引过滤 status=‘paid’ 的所有行（这些行本身在索引中已经按 create_time 有序），直接取前 10 条即可。如果要进一步优化，结合覆盖索引——如果不需要 SELECT * 而是只查部分列，可以建立 (status, create_time, 需要查询的列) 的复合索引，避免回表。

追问1： 建立 (status, create_time) 后，这个查询理论上性能大幅提升，但 SELECT * 导致必须回表——索引的叶子节点只有 status、create_time 和主键值，需要拿主键回到聚集索引取完整的行。Extra 中会显示 Using index condition（ICP 优化，在索引层过滤 status=‘paid’ 的完整行后再回表）或 Using where。如果建了 (status, create_time, 其他需要查询的列) 的覆盖索引，Extra 会显示 Using index（直接从索引获取所有字段，无需回表）。另外需要注意，如果有 ORDER BY create_time DESC，MySQL 可以利用索引的逆序扫描，Extra 中会显示 Backward index scan（8.0 特性），不需要 Using filesort。

追问2： 深分页 LIMIT offset, size 当 offset 很大时，MySQL 需要扫描前面的 100 万行数据并丢弃，只保留最后 10 行，前面 100 万行的回表和过滤是白白浪费的。两种经典优化方案。一是”延迟关联”：先通过覆盖索引定位到目标主键 ID，再回表取完整数据——SELECT * FROM orders o INNER JOIN (SELECT id FROM orders WHERE status='paid' ORDER BY create_time DESC LIMIT 1000000, 10) t ON o.id = t.id。子查询只走覆盖索引 (status, create_time, id)，不需要回表就能定位到 10 个主键 ID，然后再用这 10 个 ID 去主表查完整数据，扫描量极大缩减。二是”游标分页”：前端将上一页最后一条记录的 create_time 和 id 传回来，查询变为 WHERE status='paid' AND (create_time < last_create_time OR (create_time = last_create_time AND id < last_id)) ORDER BY create_time DESC, id DESC LIMIT 10，充分利用索引，每次只扫描 10 行，页码越深优势越明显。游标分页的代价是不支持随机跳页，需要根据产品形态做取舍。

📌 易错点 / 加分项：

深分页慢的原因不是索引不生效，而是”扫描的行太多了”，即使索引全都覆盖也得扫描 offset 那么多行
Using filesort 不一定慢——排序数据量小时内存排序很快，数据量大时需要磁盘临时文件才慢
延迟关联和游标分页是两个不同方向的优化，前者适合需要跳页但性能要求高的场景，后者适合无限滚动的 Feed 流