L Leo小站

MySQL 面试考点全景：从 B+ 树到主从切换的回答框架

技术 13 分钟 阅读 —

面 MySQL 挂掉，很少是因为不知道。

更多时候是这样：问 MVCC，你答出了「多版本并发控制」，问间隙锁，你答出了「锁住区间」——名词都对，但面试官听完没有任何信息增量，于是在评分表上写了「了解概念，缺乏深度」。

面试和写文档是反的。文档要把事情说全，面试要在两分钟内证明你踩过坑。

下面按七个层次把高频考点过一遍。每个考点给三样东西：考点内核、回答要点、加分句。加分句是真正拉开差距的地方，通常来自线上事故和排查经历。

一、索引与存储结构

1. 为什么是 B+ 树

考点内核：数据结构选型的权衡。

回答要点：

  • 相比 B 树，B+ 树非叶子节点只存键、不存数据，同样 16KB 的页能塞更多键，树更矮。三层大概能撑两千万行，一次查询最多三四次磁盘 IO。
  • 叶子节点用双向链表串起来，范围查询和 ORDER BY 可以顺序扫，不用回到根节点重新定位。
  • 哈希索引等值查询 O(1)，但不支持范围和排序；跳表内存里好用（Redis 就是这么选的），磁盘上局部性差，一次查找要跳很多次。

加分句：能说出「InnoDB 一个页 16KB，主键是 bigint 的话一层能放约一千个键」这种量级估算，比背「B+ 树矮胖」有效十倍。

2. 聚簇索引与回表

主键索引的叶子节点存整行数据，这叫聚簇索引。二级索引的叶子节点只存主键值，所以查非索引列要拿主键再回主键树查一次，这就是回表。

回答时要补一句代价：回表是随机 IO，一行一次。所以 SELECT * 在小结果集上比覆盖索引慢，不是因为传输大，而是因为回表次数多。

3. 联合索引与最左前缀

(a, b, c) 的索引，等值条件下优化器会自动调整顺序，不需要和定义一致。但范围条件会截断后面的列：

-- a 用到定位，b 用到定位，c 只能靠过滤
WHERE a = 1 AND b > 2 AND c = 3

这里的细节值得说清楚：c 不是完全没用，如果查询列都在索引里，它还能在索引层过滤（ICP，索引条件下推），省掉回表；但不能用来缩小扫描范围。

4. 覆盖索引与 ICP

覆盖索引是 EXPLAIN 里 Extra 出现 Using index，意味着不回表。ICP 是 Using index condition，意味着条件下推到存储引擎层过滤。

很多人把这两个混着说。区别是：覆盖索引省的是回表，ICP 省的是把不匹配的行回表再丢掉。

5. 索引失效

这个能展开讲半小时，单独写过一篇：MySQL 索引失效的六个真实场景。

面试里只需要给一句话收口：索引失效的本质是优化器判断「用索引更贵」——函数破坏了有序性、隐式转换引入了逐行计算、区分度太低导致回表成本超过顺序扫描。

二、事务与 MVCC

6. ACID 靠什么实现

这张对应表建议直接背下来，被问到时能一口气说完：

特性实现手段
原子性 Aundo log（回滚日志）
一致性 C由 A、I、D 共同保证，加上业务约束
隔离性 I锁 + MVCC
持久性 Dredo log + 双写（doublewrite）

注意一致性不是某个机制实现的——它是结果，不是手段。答「一致性靠 MVCC」会直接被判为背题。

7. 隔离级别与三种异常

隔离级别脏读不可重复读幻读
READ UNCOMMITTED可能可能可能
READ COMMITTED不可能可能可能
REPEATABLE READ不可能不可能InnoDB 下基本不可能
SERIALIZABLE不可能不可能不可能

MySQL 默认 RR，Oracle 和 PostgreSQL 默认 RC。这个差异面试常问：为什么 MySQL 敢用更严格的默认级别？因为早期 binlog 只有 statement 格式，RC 下主从可能不一致；现在 binlog_format=ROW 加 MGR 之后，不少大厂会主动改成 RC 来减少间隙锁和死锁。

8. MVCC 的实现

三件套：隐藏字段 + undo log 版本链 + ReadView。

每行有两个隐藏列：DB_TRX_ID（最后修改它的事务 ID）、DB_ROLL_PTR（指向 undo log 里的上一个版本）。所有历史版本用 roll pointer 串成链表。

ReadView 里有四个关键字段：

  • m_ids：生成快照时活跃（未提交）的事务 ID 列表
  • min_trx_id：其中最小的
  • max_trx_id：下一个将要分配的事务 ID
  • creator_trx_id：自己的事务 ID

可见性判断按顺序走：小于 min_trx_id 可见；大于等于 max_trx_id 不可见；介于两者之间看是否在 m_ids 里，在就不可见，不在就可见。不可见就顺着版本链往下找。

RC 和 RR 的唯一区别：RC 每次快照读都重新生成 ReadView，RR 只在事务里第一次快照读时生成一次，之后一直复用。

9. RR 怎么解决幻读

要分两条路说，这是最容易露怯的地方：

  • 快照读（普通 SELECT）：靠 MVCC。幻读的行 DB_TRX_ID 大于我的 max_trx_id，判定不可见，看不见就不会”多出来”。
  • 当前读（SELECT ... FOR UPDATE、UPDATE、DELETE）：靠 Next-Key Lock，锁住记录本身加前面的间隙，别的事务插不进来。

然后补一句真实的边界：RR 下依然可能出现幻读的表象——先快照读拿到 3 行，另一个事务插入并提交，自己再 UPDATE 全表（当前读，能看到新行）之后再快照读，就会看到 4 行。这不是 bug，是 MVCC 和当前读混用的必然结果。

10. 快照读与当前读

SELECT 默认快照读；加 FOR UPDATE、LOCK IN SHARE MODE，或者执行 INSERT/UPDATE/DELETE 都是当前读，读最新已提交版本并加锁。

加分句：UPDATE 语句里的 WHERE 也是当前读，所以「先查再改」在并发下不安全，正确做法是让 UPDATE 自己带条件判断，看返回的影响行数。

三、锁

11. 锁的粒度

  • 全局锁：FLUSH TABLES WITH READ LOCK，全库只读，一般只用于全库逻辑备份。
  • 表锁：LOCK TABLES ... READ/WRITE，以及 MDL（元数据锁）。MDL 是隐形杀手——一个长事务持有 MDL 读锁，后面的 DDL 就堵住，DDL 再堵住后面所有查询。
  • 行锁：InnoDB 独有，实现在索引上。

意向锁（IS/IX）是表级锁，作用是快速判断「表里有没有行被锁」，避免逐行扫描。它由行锁自动加，不需要手动干预。

12. 行锁锁的是索引，不是行

这是最高频的一句正确废话，但要接上推论才有价值：

-- id 无索引，锁全表
UPDATE t SET status = 1 WHERE name = 'leo';

没有索引时，InnoDB 扫过的每一行都要加锁，等价于锁住所有记录加所有间隙。所以给更新条件加索引不只是性能问题，是并发安全问题。

13. 三种行锁形态

  • Record Lock：锁单条索引记录。
  • Gap Lock：锁开区间，防止插入。只在 RR 及以上生效。
  • Next-Key Lock：Record + 前面的 Gap，左开右闭。RR 下的默认形态。

唯一索引等值命中时，Next-Key Lock 会退化成 Record Lock——因为唯一性保证了不会有第二行，没必要锁间隙。这个细节常被追问。

14. 死锁

四个条件不用背，重点说怎么产生和怎么排查：

产生：两个事务以相反顺序更新两行，各自持有对方需要的锁。InnoDB 有死锁检测（innodb_deadlock_detect），发现环路会主动回滚代价小的事务，报 Deadlock found when trying to get lock。

排查：

SHOW ENGINE INNODB STATUS;   -- LATEST DETECTED DEADLOCK 段落
SELECT * FROM information_schema.innodb_trx;
SELECT * FROM performance_schema.data_locks;   -- 8.0

治理思路分两层：减少锁范围（走索引、缩短事务、按固定顺序访问）和降低检测成本（高并发下死锁检测本身是 O(n) 的，可以关掉检测改用 innodb_lock_wait_timeout 兜底）。

四、日志与持久化

15. 三种日志的分工

日志层级类型用途
redo logInnoDB物理（physiological）崩溃恢复，保证持久性
undo logInnoDB逻辑事务回滚 + MVCC 版本链
binlogServer逻辑主从复制 + 数据恢复

一句话区分 redo 和 binlog：redo 是循环写的、固定大小、记录”哪个页改了什么”；binlog 是追加写的、可以归档、记录”这条 SQL 做了什么”。

16. 为什么需要两阶段提交

因为 redo 和 binlog 是两个独立的写入，必须保证要么都生效，要么都不生效，否则主库和从库会不一致。

流程：

  1. InnoDB 写 redo，标记 prepare
  2. 写 binlog
  3. InnoDB 把 redo 标记 commit

崩溃恢复时：redo 处于 prepare 状态，就去检查对应的 binlog 是否完整（有 XID 事件）。完整则提交，不完整则回滚。

加分句：如果不用两阶段提交，先写 redo 后写 binlog，写完 redo 就崩了——主库有数据、binlog 没有，从库和备份都少了这条，恢复后不一致。

17. 刷盘参数

innodb_flush_log_at_trx_commit = 1   # 每次提交 fsync redo
sync_binlog = 1                      # 每次提交 fsync binlog

这两个都设 1 就是常说的「双 1 配置」，最安全，代价是每次提交两次 fsync。金融场景必须双 1；日志类业务可以放宽到 2 和 100，最多丢一秒数据，吞吐能上去一个数量级。

18. WAL 为什么快

写数据页是随机 IO，而且一个页 16KB，改一个字节也要写整页。WAL 是先顺序追加日志，再由后台线程批量刷脏页——把随机写变顺序写，把多次小写合并成批量写。

19. doublewrite

redo 不能修复「页写了一半」的损坏（页本身已经坏了，重放也没用）。所以 InnoDB 刷脏页前先顺序写到 doublewrite buffer，再写到数据文件。崩溃时如果发现页校验和不通过，就从 doublewrite 恢复整页。

五、SQL 优化与执行

20. EXPLAIN 看什么

按重要性排序：

  • type：ALL（全表）→ index（全索引扫）→ range → ref → eq_ref → const。看到 ALL 且 rows 很大就要警惕。
  • key：实际用的索引，NULL 就是没走。
  • rows：预估扫描行数，乘上过滤比例就是成本。
  • Extra：Using filesort（排序没走索引）、Using temporary（用了临时表）、Using index（覆盖索引）。

EXPLAIN 是估算，MySQL 8 的 EXPLAIN ANALYZE 会真正执行并给实际耗时和实际行数，估算偏差大的时候用它。

21. 深分页

-- 慢：扫 100 万行丢掉前 100 万
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

三种解法，按场景选：

-- 1. 延迟关联：先用覆盖索引拿主键，再回表
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t USING (id);

-- 2. 游标：记住上一页最后一个 id（推荐，O(1)）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

-- 3. 业务限制：只允许翻到前 200 页

游标分页的限制是不能跳页，所以适合「下拉加载」而不适合页码导航。这个取舍要主动说出来。

22. count(*) 为什么慢

InnoDB 不存总行数，因为 MVCC 下每个事务看到的行数不一样，存一个数字没法满足所有人。

count(*)、count(1)、count(主键) 在 MySQL 8 基本等价，优化器会选最小的二级索引来扫。count(列) 会额外判断 NOT NULL，稍慢。

想快就维护计数表，或者用 SHOW TABLE STATUS 的估算值（误差可能很大，只能做展示用）。

23. join 的三种算法

  • NLJ（嵌套循环）：驱动表每行去被驱动表查一次，被驱动表必须有索引。
  • BNL（Block Nested Loop）：被驱动表没索引时，把驱动表数据放 join buffer，批量扫描。8.0.18 之后等值连接会被 Hash Join 取代，性能好很多。
  • BKA：NLJ 加了批量键值查找，需要索引 + MRR。

回答重点：小表驱动大表，以及「被驱动表的连接列必须有索引」——这一条比算法名字重要。

24. 一条慢查询的完整排查链路

这个问题是送分题，也是最容易答得零碎的，按顺序说：

  1. 开慢查询日志（slow_query_log），设 long_query_time
  2. 用 mysqldumpslow 或 pt-query-digest 聚合，按总耗时排序，而不是按单次最慢
  3. EXPLAIN 看执行计划，重点看 type、rows、Extra
  4. 判断是索引问题、SQL 写法问题，还是数据量问题
  5. 索引问题就加/改索引，写法问题就改写，数据量问题就归档或分片
  6. 改完用 EXPLAIN ANALYZE 对比，上线后观察慢日志是否归零

加分句：先按总耗时排序。一条 0.5 秒的查询每秒跑 1000 次，危害远大于一条偶尔 5 秒的查询，但后者才是用户投诉的。

六、复制与高可用

25. 主从复制的三个线程

  1. 主库 dump thread：把 binlog 发给从库
  2. 从库 IO thread：接收并写入 relay log
  3. 从库 SQL thread：读 relay log 重放

两个关键参数：relay_log_recovery（崩溃后自动从 SQL 线程位置重拉，避免重复执行）和 read_only（防误写从库，但 super 用户无效）。

26. 主从延迟

常见原因：大事务、从库单线程重放、从库配置差、无主键表导致 ROW 格式下每行全表扫。

解决方案按演进顺序说：

  • 拆大事务（批量更新改成小批次）
  • 从库开并行复制：slave_parallel_workers + LOGICAL_CLOCK（8.0 用 write set 更细粒度）
  • 半同步复制：AFTER_SYNC 模式保证至少一个从库收到 binlog 才返回成功
  • 架构层面：关键读走主库，或者用 MGR / 云上的强一致读

27. 读写分离的一致性坑

「刚发布完文章，刷新页面看不到」——这是读写分离最经典的问题。

解法三档：

  1. 强制走主库：写后一段时间内该用户的读都走主库（打标记）
  2. 半同步 + 等待 GTID：读之前先确认从库追上了指定 GTID
  3. 业务容忍：非关键场景直接接受延迟，别过度设计

面试时把三档都说出来，比只说「读主库」显得成熟。

七、架构与工程

28. 什么时候分库分表

不要一上来就说分片。先给判断线：

  • 单表 2000 万行或 50GB 以上，且索引优化已到极限
  • 单库连接数/QPS 打满，读写分离也扛不住
  • 单机磁盘容量瓶颈

顺序：先索引优化 → 再读写分离 → 再垂直拆分（按业务拆库）→ 最后水平分片。跳过前面几步直接分片是大坑。

分片键选择的要点：高基数、分布均匀、绝大多数查询都带它。用户中心用 user_id，订单表用 user_id（而不是 order_id），因为查询几乎都是「我的订单」。代价是运营侧按 order_id 查要处理跨分片。

跨库后要面对的问题：分布式事务、跨分片分页排序、全局唯一 ID（雪花算法/号段模式）、扩容迁移（一致性哈希或双倍扩容）。

29. 大表 DDL

ALTER TABLE 加索引、改字段类型在老版本会锁表重建。现在有三条路：

  • Online DDL（8.0）：ALGORITHM=INPLACE, LOCK=NONE，大部分场景不阻塞读写。8.0.12 起支持 INSTANT 加列（只能在末尾加）。
  • gh-ost：解析 binlog 同步到影子表，无触发器，可暂停限流。副作用是需要额外的磁盘和主库负载。
  • pt-online-schema-change：触发器方案，老一些，触发器有性能开销。

加分句：DDL 前先查 information_schema.innodb_trx 有没有长事务，否则 MDL 会把你堵死，而且堵住的是后面所有查询——比 DDL 本身危险得多。

30. 连接数与连接池

max_connections 不是越大越好。每个连接一个线程 + 排序缓冲 + join buffer，几百个连接就能把内存吃光，而且线程切换开销会让 QPS 反而下降。

经验值：连接数控制在 CPU 核数 * 2~4 的量级，剩下的靠连接池排队。连接池要注意 maxLifetime 小于数据库 wait_timeout，否则会拿到已被服务端断开的连接。

面试回答的通用结构

七个层次说完，最后给个方法论。技术问题都可以套这个五段式：

  1. 一句话结论：先给判断，别铺垫
  2. 原理：数据结构/机制层面怎么实现的
  3. 边界：什么情况下不成立，有什么例外
  4. 实践：我遇到过什么，怎么定位的
  5. 取舍：如果换个场景我会怎么选

大部分候选人只做到 1 和 2，所以答案听起来像 Wikipedia。3 和 4 是分水岭——「RR 下依然可能幻读」「没有索引时行锁会锁全表」这类边界知识，只有真正排查过问题的人才会说。

还有一个反直觉的建议：不确定的地方直接说不确定。面试官更怕的是把「大概是」说成「一定是」。你可以说：「这块我没在生产上验证过，按我的理解是……如果有偏差请指正。」

最后，准备两三个能完整讲五分钟的案例。一个索引优化、一个死锁或锁等待、一个主从延迟或数据不一致。有案例的人，面试节奏是完全不一样的。

#MySQL#数据库#面试