MySQL 数据库高频面试与深度调优 (2026 版)

写在前面：

数据库是后端开发的命门。在面试中，MySQL 往往是考察深度的重灾区。面试官不仅关心你会不会写 SQL，更关心你是否理解 索引底层、事务隔离级别的实现机制、锁的细节以及 SQL 调优的方法论。

本文聚焦于 MySQL (InnoDB) 的核心原理与实战调优。

---

💾 第一部分：索引底层与优化原理

1. B+树：为什么它是神？

高频问法：

• “为什么 MySQL 使用 B+树而不是 B 树、Hash 或红黑树？”
• “一张表能存多少数据？怎么计算的？”

深度解析：

• B+树 vs B 树：
  • B+树：非叶子节点只存索引（Key），叶子节点存所有数据。这使得非叶子节点能容纳更多 Key，树的高度更低（通常 3 层就能存 2000W+ 数据），减少磁盘 I/O。
  • 范围查询：B+树叶子节点由双向链表连接，非常适合 > 5 这种范围查询。B 树需要中序遍历，效率低。
• B+树 vs Hash：
  • Hash 适合等值查询 (=)，但不适合范围查询。
• 计算树高：
  • 假设页大小 16KB，主键 BigInt (8字节) + 指针 (6字节) = 14字节。
  • 非叶子节点能存 $16384 / 14 \approx 1170$ 个指针。
  • 假设每行数据 1KB，叶子节点能存 16 行。
  • 3 层 B+树容量：$1170 \times 1170 \times 16 \approx 2190$ 万行。

2. 索引失效与优化实战

场景题： “我建了索引 idx_a_b_c (a, b, c)，请问以下 SQL 走索引吗？”

1. SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3

   • 答案：走索引。但是 c 用不到索引。
   • 原因：最左前缀原则。a 匹配，b 范围查询，索引匹配在 b 处截断，后续的 c 只能作为过滤条件（Index Condition Pushdown, ICP）。

2. SELECT * FROM t WHERE a = 1 ORDER BY c

   • 答案：走索引 a，但 c 排序用不到索引，会产生 Using filesort。
   • 原因：中间断了 b。

覆盖索引 (Covering Index)：

• 核心技巧：SELECT id, a, b FROM t WHERE a = 1。
• 查询的列完全在索引树中，不需要回表（回到主键索引查数据）。这是 SQL 优化的杀手锏。

---

🔒 第二部分：事务、锁与 MVCC

1. MVCC (多版本并发控制)

高频问法：

• “可重复读 (RR) 隔离级别是如何实现的？”
• “Read View 是什么时候生成的？”

核心解析：

• 核心组件：
  • Undo Log：记录数据的历史版本（回滚指针）。
  • Read View：事务启动时生成的快照，包含当前活跃事务的 ID 列表。
• 可见性规则：
  • 如果数据版本的事务 ID < Read View 的最小活跃 ID (min_id)，说明是已提交的旧事务，可见。
  • 如果数据版本的事务 ID >= Read View 的最大 ID (max_id)，说明是未来启动的事务，不可见。
• RC vs RR：
  • RC (Read Committed)：每次 SELECT 都生成新的 Read View。能读到别人刚提交的数据。
  • RR (Repeatable Read)：第一次 SELECT 生成 Read View，之后复用。保证同一个事务内看到的数据一致。

2. 锁机制 (Next-Key Lock)

高频问法：

• “MySQL 如何解决幻读？”
• “什么是间隙锁 (Gap Lock)？”

核心解析：

• 当前读 vs 快照读：
  • SELECT * FROM t 是快照读，靠 MVCC 解决幻读。
  • SELECT * FROM t FOR UPDATE 是当前读，靠 Next-Key Lock 解决幻读。
• Next-Key Lock：
  • Record Lock (行锁) + Gap Lock (间隙锁)。
  • 它锁住记录本身，并且锁住记录之前的间隙，防止其他事务插入新数据。
  • 举例：表中有 id=1, 5, 10。事务 A 执行 SELECT * FROM t WHERE id > 5 FOR UPDATE。
  • 锁住范围：(5, 10] (Next-Key), (10, +∞) (Gap)。此时插入 id=8 会被阻塞。

---

🛠️ 第三部分：生产级调优案例

1. 深分页问题

问题：LIMIT 1000000, 10 为什么慢？ 原因：MySQL 需要扫描 1000010 行，抛弃前 1000000 行，回表次数太多。

优化方案：

1. 子查询优化 (利用覆盖索引)：

   SELECT * FROM t JOIN (
       SELECT id FROM t LIMIT 1000000, 10
   ) AS tmp ON t.id = tmp.id;

   • 先在索引树上查出 10 个 ID（不回表），再根据 ID 回表取数据。
2. 游标法 (Seek Method)：
   • 前提：ID 连续且自增，或者业务允许记住上一次的 ID。
   • SELECT * FROM t WHERE id > 1000000 LIMIT 10。
   • 性能极快，O(1)。

2. 死锁排查

步骤：

1. 执行 SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK。
2. 分析两个事务分别持有什么锁，在等待什么锁。
3. 常见原因：
   • 交叉更新：A 锁 1 待 2，B 锁 2 待 1。
   • Gap Lock 冲突：多个事务同时对不存在的记录加锁（如 INSERT ... ON DUPLICATE KEY）。

---

总结：MySQL 的面试核心在于理解数据在磁盘和内存中是如何组织的，以及并发下如何保证一致性。掌握这些原理，SQL 优化就是降维打击。