第 30 章 MySQL面试题精讲：进阶

📖 本篇简介：本篇进入面试进阶题，主攻 MVCC、锁、执行计划、主从复制、分库分表这五块"能拉开差距"的内容。如果你在上一篇能对答如流，这一篇就是你从"会用 MySQL"到"懂 MySQL"的分水岭。学完你应该能讲清 Read View 的生成时机、说清 EXPLAIN 每一列、画完主从复制的三个线程。所有示例基于 MySQL 8.0 语法书写，本示例基于 MySQL 8.0 环境。

目录

• 本篇导读与答题思路
• 题目 1：什么是 MVCC，它是怎么解决读写并发的
• 题目 2：InnoDB 有哪些锁，行锁表锁间隙锁的区别
• 题目 3：EXPLAIN 重点看哪几个字段
• 题目 4：主从复制的原理是什么，binlog 三种格式有什么区别
• 题目 5：什么时候要分库分表，分片键怎么选
• 题目 6：redo log 和 binlog 有什么区别，两阶段提交是什么
• 题目 7：间隙锁什么时候触发，它解决了什么问题
• 题目 8：MySQL 有哪几种日志，分别记什么内容
• 速查表：进阶高频结论速查
• 注意事项：进阶题的丢分点
• 练习题：动手自测与参考
• 复习清单

本篇导读与答题思路

进阶题的特点是：光背定义没用，面试官会顺着你的话往下追三层。比如你说"MVCC 解决读写并发"，他立刻追问"Read View 什么时候生成？RC 和 RR 的区别在哪？undo log 版本链怎么串？"。

所以答进阶题要做到两件事：

1. 画出时序图或结构图。MVCC 画 undo 版本链 + Read View，主从画 binlog/dump thread/SQL thread，锁画索引区间。

2. 说出 RC 与 RR 的差异。很多进阶题的答案本质都绕回"两种隔离级别下行为不一样"，比如 Read View 生成时机、半一致性读。

下面的参考答案故意把"对比项"做成表格，方便你直接背。

进阶题有个偷懒技巧：很多题答案最后都能绕到"RC 和 RR 行为不同"。比如问锁范围、问 Read View、问半一致性读，你都可以先答"默认是 RR，但 RC 下……"，一句话同时覆盖两种场景，显得你考虑周全。

另一个技巧：答原理题时主动对比"为什么不用更简单的方案"。比如讲 MVCC 时对比"为什么不用纯锁"，讲 B+Tree 时对比"为什么不用哈希"，这种"反证"特别容易拿分。

题目 1：什么是 MVCC，它是怎么解决读写并发的

考点

• MVCC（多版本并发控制）的目标：读不阻塞写、写不阻塞读。
• 三个核心组件：隐藏列（trx_id、roll_pointer）、undo log 版本链、Read View。
• RC 与 RR 在 Read View 生成时机上的差别。

参考答案

一句话结论：MVCC 让一行数据维护多个历史版本，读事务根据 Read View 决定自己能看到哪个版本，从而做到"读快照、写最新"，读写互不阻塞。

它的三件套：

• 隐藏列：InnoDB 给每行偷偷加了 trx_id（最近修改这行的事务 id）和 roll_pointer（指向上一个版本的 undo log）。
• undo log 版本链：每次修改都留一条 undo，串成链表，越旧的版本越在链尾。
• Read View（读视图）：事务开启时拍的一张"快照"，记录当时活跃（未提交）的事务 id 列表，用来判断版本链上哪个版本对自己可见。

可见性判断规则（面试爱问）：顺着版本链找到第一个满足"trx_id 不在活跃列表、且小于高水位"的版本，就是本事务能看到的行。

RC 与 RR 的关键差异：

| 隔离级别 | Read View 生成时机 | 效果 |

|---|---|---|

| RC 读提交 | 每次 SELECT 都重新生成 | 能看到别的事务新提交的数据 → 不可重复读 |

| RR 可重复读 | 事务内第一次 SELECT 时生成，之后复用 | 整个事务看到同一个快照 → 可重复读 |

一句话记住：RR 下 Read View 只建一次，RC 下每条 SELECT 都建新的。这就是 RR 能可重复读的根本原因。

可以用下面两条 SQL 区分快照读和当前读（MySQL 8.0）：

-- 快照读：普通 SELECT，走 MVCC 读历史快照，不加锁
SELECT * FROM t_goods WHERE id = 1;

-- 当前读：加 FOR UPDATE，读最新版本并上排他行锁
SELECT * FROM t_goods WHERE id = 1 FOR UPDATE;

-- UPDATE / DELETE 本质也是当前读，读最新值并加锁
UPDATE t_goods SET stock = stock - 1 WHERE id = 1;

理解了这个区别，就明白"为什么 RR 下一个事务看不到自己刚插入的行被别的事务改了"——因为普通 SELECT 走的是老快照。

追问

• 追问：MVCC 能挡住写吗？ 不能。写操作仍要加行锁，MVCC 只解决"读"的可见性，写并发由锁负责。
• 追问：快照读和当前读区别？ 普通 SELECT 是快照读（走 MVCC）；SELECT ... FOR UPDATE、LOCK IN SHARE MODE、UPDATE、DELETE 是当前读（读最新版本并加锁）。

题目 2：InnoDB 有哪些锁，行锁表锁间隙锁的区别

考点

• 按粒度：全局锁、表锁、行锁。
• 按意图：共享锁 S、排他锁 X；意向锁。
• 行锁的三种：记录锁、间隙锁、临键锁。

参考答案

先给一张分层表：

| 锁类型 | 加锁对象 | 特点 |

|---|---|---|

| 全局锁 | 整个库（FTWRL） | 全库只读，用于逻辑备份 |

| 表锁 | 整张表 | DDL 或显式 LOCK TABLE，粒度大 |

| 意向锁 | 表级 | 表上标记"我下面某行要加 S/X 锁"，加速表锁判断 |

| 记录锁 Record Lock | 索引上的一条记录 | 精确等值命中唯一索引时 |

| 间隙锁 Gap Lock | 两条索引记录之间的开区间 | 防止别的事务在间隙里 INSERT |

| 临键锁 Next-Key Lock | 左开右闭区间（记录+前面间隙） | RR 默认行锁算法，防幻读 |

核心结论：InnoDB 的行锁是加在索引上的。如果 UPDATE/DELETE 的条件列没走索引，会退化为锁全表（其实是扫一遍加锁），并发瞬间崩。

验证行锁加在索引上的 SQL（MySQL 8.0）：

-- 会话 A：开启事务，更新 id=1 的行（走主键索引，只锁这一行）
BEGIN;
UPDATE t_user SET name = 'a' WHERE id = 1;   -- 只锁聚簇索引上 id=1 这一行

-- 会话 B：更新 id=2 不阻塞（行锁粒度小）
UPDATE t_user SET name = 'b' WHERE id = 2;   -- 成功

-- 反例：条件列 city 没索引，会扫描并锁住大量行
UPDATE t_user SET age = age + 1 WHERE city = '北京';  -- city 无索引 → 锁范围放大

追问

• 追问：意向锁是干啥的？ 事务要加表锁前，先看表上有没有意向锁，避免逐行检查。意向锁之间互不阻塞，只和表锁互斥。
• 追问：什么时候锁会升级成表锁？ InnoDB 没有自动锁升级，但单行锁数量极多时由优化器可能直接用表锁，DDL 也会拿表级 MDl 锁。

题目 3：EXPLAIN 重点看哪几个字段

考点

• EXPLAIN 输出列：type、key、rows、Extra、filtered。
• type 的性能档次从好到坏。
• 怎么从执行计划反推索引问题。

参考答案

一句话：看 EXPLAIN 就盯四列——type（访问类型）、key（实际用的索引）、rows（预估扫描行数）、Extra（额外信息）。

type 列从好到坏记这个顺序（面试必背）：

system > const > eq_ref > ref > range > index > ALL

• const：主键或唯一索引等值查询，最多一行，很快。
• ref：普通索引等值查询，命中多行。
• range：索引范围扫描，如 BETWEEN、>、IN。
• index：扫整棵索引树（比 ALL 略好）。
• ALL：全表扫描，必须优化。

Extra 列高频值：

| Extra | 含义 | 好坏 |

|---|---|---|

| Using index | 覆盖索引，不回表 | 好 |

| Using where | 服务器层过滤，可能回表 | 中 |

| Using filesort | 额外排序，需优化 | 差 |

| Using temporary | 用临时表，常见于 GROUP BY | 差 |

我用 SQLite 实跑过一个对照实验（原理与 MySQL 8.0 的 EXPLAIN 一致，SQLite 用 EXPLAIN QUERY PLAN），对 t_user(city) 先查后建索引：

=== 建索引前：按 city 等值查询的执行计划 ===
(2, 0, 216, 'SCAN t_user')

=== 建索引后：同样查询的执行计划 ===
(3, 0, 62, 'SEARCH t_user USING INDEX idx_city (city=?)')

对应到 MySQL 8.0：建索引前 type=ALL（全表扫描），建索引后 type=ref、key=idx_city。这就是一次标准的"执行计划反推索引缺失"。

追问

• 追问：rows 是精确值吗？ 不是，是优化器基于统计信息的预估值，可能和真实行数差很多，统计信息过期时要 ANALYZE TABLE。
• 追问：Using filesort 一定很差吗？ 不一定。如果排序结果集很小，filesort 内存就能搞定；结果集大才需要优化（加联合索引消除排序）。

题目 4：主从复制的原理是什么，binlog 三种格式有什么区别

考点

• 主从复制三线程：主库 dump thread、从库 IO thread、SQL thread。
• binlog 格式：STATEMENT、ROW、MIXED。
• 主从延迟的来源。

参考答案

一句话流程：主库把变更写 binlog → 从库 IO 线程拉到本地存 relay log → 从库 SQL 线程重放 relay log。

三个线程分工：

• 主库 Binlog Dump Thread：把 binlog 实时推给从库。
• 从库 IO Thread：连主库，读 binlog 写进本地 relay log（中继日志）。
• 从库 SQL Thread：把 relay log 里的事务重演一遍，保持数据一致。

binlog 三种格式对比（高频题）：

| 格式 | 记录内容 | 优点 | 缺点 |

|---|---|---|---|

| STATEMENT | 记录原始 SQL 语句 | 日志体积小 | 函数（如 NOW()、UUID()）在从库重放结果可能不一致 |

| ROW | 记录每行被改成了什么 | 绝对一致，复制安全 | 日志体积大，UPDATE 全表会暴涨 |

| MIXED | 上面两者混合 | MySQL 自动选 | 兼顾，默认推荐 |

现在线上基本都用 ROW 格式，因为复制一致性最重要，体积问题靠压缩解决。

主从延迟怎么产生：从库单线程（老版本）重放追不上主库并发写入、大事务一次回放很久、从库自身在跑慢查询抢占资源。

一条最小可用的主从搭建关键 SQL（MySQL 8.0，主库侧）：

-- 主库：创建专门给从库复制用的账号
CREATE USER 'repl'@'从库IP' IDENTIFIED BY '强密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库IP';

-- 从库：指定主库坐标并开启复制（8.0 用 CHANGE REPLICATION SOURCE）
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='主库IP',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='强密码',
    SOURCE_LOG_FILE='binlog.000123',   -- 主库 SHOW MASTER STATUS 拿到
    SOURCE_LOG_POS = 4;               -- 对应位点
START REPLICA;                         -- 开启复制（老版本叫 START SLAVE）

-- 从库查看复制状态，重点看 IO/SQL 两个线程是不是 Yes
SHOW REPLICA STATUS\G

追问

• 追问：怎么减小主从延迟？ 从库开并行复制（MySQL 5.7 后基于组提交并行）、拆大事务、给从库配更好的机器、读写分离时强一致读走主库。
• 追问：binlog 和 redo log 区别？ 见下一题，这是连环问。

题目 5：什么时候要分库分表，分片键怎么选

考点

• 垂直拆分与水平拆分的区别。
• 什么时候单表到了该拆的临界点。
• 分片键（sharding key）选择原则与分布式 ID。

参考答案

先区分两种拆分：

• 垂直拆分：按业务拆库，用户库、订单库、商品库各管一摊，解决"库太大、表太多"。
• 水平拆分：同一张表按某个维度（如 user_id）拆散到多个库/表，解决"单表行数过大"。

什么时候该拆：一般经验是单表行数到 千万级、数据文件几十 GB，且增删改查已经明显变慢，就要考虑水平拆分。拆太早会徒增复杂度。

分片键怎么选（面试重点）：

1. 高离散度：数据均匀分布，避免热点。

2. 高频查询都带它：绝大部分 SQL 的 WHERE 里都有分片键，否则要扫所有分片（广播）。

3. 尽量不跨片 JOIN：选业务上最常一起查的列。

-- 以订单表为例：按 user_id 哈希分成 8 张表 t_order_0 ~ t_order_7
-- 路由规则：分片号 = user_id % 8
SELECT * FROM t_order_3 WHERE user_id = 10086;   -- 路由到 t_order_3，单分片命中

分片后的代价（一定要主动说，体现成熟度）：跨片 JOIN 难、分布式事务复杂、全局唯一 ID 要靠雪花算法、扩容要数据迁移。

-- 基因法举例：把 user_id 的哈希嵌进 order_id 的高位
-- 查订单时从 order_id 直接算出分片号，不用再查映射表
-- order_id = (user_hash << 32) | 序列值
-- 路由：分片号 = (order_id >> 32) % 8
SELECT * FROM t_order_5 WHERE order_id = 100862001;  -- 高位 10086 决定走 5 号片

追问

• 追问：分片键选 user_id 还是 order_id？ 看业务主要按谁查。C 端查"我的订单"按 user_id；客服按 order_id 查单，可能要单独建一张 order_id→user_id 的映射表（基因法）。
• 追问：分库分表用中间件吗？ 常用 ShardingSphere、MyCat，或者业务层一致性哈希路由。

题目 6：redo log 和 binlog 有什么区别，两阶段提交是什么

考点

• redo log（InnoDB 层，物理日志，循环写）与 binlog（Server 层，逻辑日志，追加写）。
• 两阶段提交（prepare → commit）保证两份日志一致。

参考答案

一句话对比：redo log 是 InnoDB 自己的" crash-safe 日志"，binlog 是 MySQL Server 层的"复制与归档日志"，两阶段提交让它们在崩溃时不出现矛盾。

| 对比项 | redo log | binlog |

|---|---|---|

| 所属层 | InnoDB 引擎层 | MySQL Server 层 |

| 日志性质 | 物理日志（某页改成啥样） | 逻辑日志（SQL 或行镜像） |

| 写入方式 | 循环写，固定大小，会覆盖 | 追加写，不会覆盖 |

| 作用 | 崩溃恢复，保证持久性 | 主从复制、数据恢复 |

| 谁能不用 | 换引擎就没了 | 所有引擎通用 |

两阶段提交流程（画图讲）：

1. InnoDB 先写 redo log，状态置为 prepare；

2. Server 层写 binlog；

3. 把 redo log 状态改成 commit。

崩溃恢复时：如果 redo log 是 prepare 状态就去检查 binlog 在不在——binlog 完整就提交，不完整就回滚。这样保证"binlog 里有的事务，主从都能一致地提交"，不会出现主库提交了但从库没这条 binlog 的裂脑。

追问

• 追问：为什么需要两份日志？ redo log 保证本机 crash-safe，binlog 负责复制和恢复，职责不同；只有一份会要么没法复制，要么崩溃恢复慢。
• 追问：redo log 多大合适？ 一般设成能存 1 小时左右的写入量，太小会频繁刷脏页，太大崩溃恢复慢。

题目 7：间隙锁什么时候触发，它解决了什么问题

考点

• 间隙锁加在索引记录之间的开区间。
• 触发条件：RR 隔离级别 + 范围查询/普通索引查询。
• 间隙锁之间不互斥，只阻止 INSERT。

参考答案

一句话：间隙锁锁住的是"两条索引记录之间的空隙"，目的是不让别的事务在这个空隙里 INSERT，从而配合临键锁防止幻读。

触发条件（要答全）：

1. 隔离级别必须是 RR（RC 没有间隙锁）；

2. 查询用到了索引（不走索引就锁全表，谈不上间隙）；

3. 条件是范围查询或非唯一索引等值查询且没命中唯一记录。

举个例子，索引上已有 id = 10、20、30 三条记录：

-- 会话 A：RR 下执行范围查询，会给 (10,20)、(20,30) 这两个间隙加间隙锁
BEGIN;
SELECT * FROM t_goods WHERE id > 10 AND id < 30 FOR UPDATE;
-- 锁住了 (10,20] 和 (20,30] 两个临键区间

-- 会话 B：试图往间隙里插 id=15 会被阻塞（被间隙锁挡住）
INSERT INTO t_goods (id) VALUES (15);   -- 阻塞，直到 A 提交

一个反直觉点：间隙锁和间隙锁之间不互斥，两个事务可以同时持有同一个间隙的间隙锁——它的唯一目的就是"阻止别人 INSERT"，不阻止彼此加锁。

追问

• 追问：RC 级别为什么没有间隙锁？ RC 本来就允许不可重复读，不追求防幻读，所以去掉间隙锁换更高并发，这也是很多公司线上用 RC 的原因。
• 追问：死锁和间隙锁有关吗？ 有关。间隙锁互相等待对方释放再插入，很容易形成死锁，SHOW ENGINE INNODB STATUS 里能看到。

题目 8：MySQL 有哪几种日志，分别记什么内容

考点

• redo log、undo log、binlog、slow log、error log、general log 的分工。
• 哪些在引擎层、哪些在 Server 层。
• 线上分别什么时候看哪种日志。

参考答案

一句话结论：面试把六种日志按"职责 + 所在层"背熟，基本能答满分。

| 日志 | 所在层 | 记什么 | 主要用途 |

|---|---|---|---|

| redo log | InnoDB | 物理页修改 | crash-safe、崩溃恢复 |

| undo log | InnoDB | 反向操作（旧版本） | 回滚、MVCC 版本链 |

| binlog | Server | 逻辑变更（SQL/行） | 主从复制、数据恢复 |

| slow query log | Server | 超过阈值的 SQL | 慢查询优化 |

| error log | Server | 启动/运行错误告警 | 排障 |

| general log | Server | 所有执行过的 SQL | 调试（线上一般不开） |

线上最常用的是三种：slow log 找慢 SQL、error log 看启动报错、binlog 做复制和恢复。general log 因为记录所有 SQL 量太大，平时关闭。

-- 查看各日志位置和开关（MySQL 8.0）
SHOW VARIABLES LIKE 'log_error';               -- 错误日志路径
SHOW VARIABLES LIKE 'slow_query_log';         -- 慢日志开关
SHOW VARIABLES LIKE 'slow_query_log_file';    -- 慢日志文件路径
SHOW VARIABLES LIKE 'log_bin';                -- binlog 是否开启（ON 即开）
SHOW VARIABLES LIKE 'binlog_format';          -- 一般 ROW

一句话区分 redo 和 binlog：redo 是 InnoDB 自己的"安全日志"，binlog 是整个 Server 的"归档复制日志"，两阶段提交把它们绑在一起保证一致。

追问

• 追问：error log 里一般能看到什么？ MySQL 启动参数、崩溃栈、主从报错、锁等待超时等，是线上告警第一现场。
• 追问：slow log 里怎么找到最慢的几条？ 用 mysqldumpslow 或 pt-query-digest 按 Query_time 排序聚合，别直接翻文件。

速查表：进阶高频结论速查

| 考点 | 一句话结论 |

|---|---|

| MVCC | Read View + undo 版本链，RC 每次建、RR 建一次 |

| 行锁 | 加在索引上，不走索引会锁全表 |

| EXPLAIN | 重点看 type/key/rows/Extra，ALL 和 Using temporary 要优化 |

| 主从复制 | binlog → IO 线程 relay log → SQL 线程重放 |

| binlog 格式 | 线上用 ROW，一致性最好 |

| 分库分表 | 垂直拆业务，水平拆数据，分片键要高离散且高频 |

| redo vs binlog | redo 引擎层物理循环写，binlog Server 层逻辑追加写 |

| 两阶段提交 | prepare → 写 binlog → commit，崩溃时以此判断回滚还是提交 |

| 间隙锁 | RR + 索引 + 范围，锁空隙防 INSERT 防幻读 |

注意事项：进阶题的丢分点

1. MVCC 答不全三件套：只说"读快照"不够，必须说出隐藏列、undo 版本链、Read View 三个组件。

2. 忘记区分快照读和当前读：FOR UPDATE、UPDATE 是当前读，不走 MVCC，会加锁。

3. EXPLAIN 只说 type：要主动提 Extra 里的 Using filesort / Using temporary，这才是优化点。

4. 主从复制只说"同步 binlog"：要说清三个线程各自在哪台机器、干什么。

5. 分库分表只讲好处：成熟的答法必须补"跨片 JOIN、分布式事务、扩容迁移"三大代价。

6. 把 redo log 和 binlog 说成一个东西：一个引擎层物理循环写、一个 Server 层逻辑追加写，两阶段提交才让它们一致，别混。

7. 间隙锁答成表锁：间隙锁锁的是索引记录之间的"空隙"，不是锁整行也不是锁整表，要讲清楚它只阻止 INSERT。

练习题：动手自测与参考

练习 1：一条 SQL 在 EXPLAIN 里 type=ALL、key=NULL、rows=100 万，你会怎么优化？

<details>

<summary>参考答案</summary>

说明没走索引。先看 WHERE 条件列，给高频过滤列建合适的联合索引，再确认是否违反最左前缀、是否有隐式类型转换导致索引失效。优化后 type 应至少到 ref 或 range。

</details>

练习 2：为什么 RC 隔离级别下并发性能通常比 RR 好？

<details>

<summary>参考答案</summary>

RC 没有间隙锁，锁的范围更小、持锁时间更短；同时每条 SELECT 重新生成 Read View 虽然读到的数据更新，但锁竞争更少。代价是允许不可重复读。

</details>

练习 3：主库更新了一条数据，从库却迟迟没更新，怎么排查？

<details>

<summary>参考答案</summary>

先在从库 SHOW REPLICA STATUS\G（8.0，5.7 是 SHOW SLAVE STATUS）看 Seconds_Behind_Source 延迟秒数、IO/SQL 线程是否 Yes；再看是不是大事务回放、从库是否在跑重查询、从库机器性能是否不足。

</details>

练习 4：快照读和当前读各举两个例子。

<details>

<summary>参考答案</summary>

快照读：普通 SELECT（走 MVCC 读快照）。当前读：SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE、INSERT（读最新版本并加锁）。

</details>

练习 5：一条 UPDATE 的 WHERE 条件列没有索引，会锁多少行？

<details>

<summary>参考答案</summary>

InnoDB 行锁加在索引上，WHERE 列没索引时会扫描全表并对扫过的行加行锁（RR 下还可能加间隙锁），实际效果接近锁全表，并发急剧下降。所以更新语句的 WHERE 一定要走索引。

</details>

练习 6：binlog 设成 STATEMENT 格式，在从库可能出现什么不一致？

<details>

<summary>参考答案</summary>

NOW()、UUID()、LIMIT 不确定行等函数在主从重放时结果不同：主库用当时的时间/随机值，从库重放时是另一个值，导致数据不一致。这就是线上一律用 ROW 格式的原因。

</details>

复习清单

| 题目 | 核心结论 | 易错点 |

|---|---|---|

| 1 MVCC | 隐藏列+undo 链+Read View，RR 建一次 RC 建每次 | 忘了区分快照读和当前读 |

| 2 InnoDB 锁 | 行锁加在索引上，粒度记录/间隙/临键 | 以为不走索引也只是锁几行 |

| 3 EXPLAIN | 看 type/key/rows/Extra，ALL 与临时表要优化 | 只看 type 不看 Extra |

| 4 主从复制 | 三线程 + binlog，线上用 ROW 格式 | 说不清三个线程分工 |

| 5 分库分表 | 垂直拆业务水平拆数据，分片键要离散高频 | 只说好处不说跨片代价 |

| 6 redo vs binlog | redo 引擎物理循环写，binlog Server 逻辑追加写 | 说不出两阶段提交作用 |

| 7 间隙锁 | RR+索引+范围，锁空隙防 INSERT | 以为间隙锁之间互斥 |

| 8 日志体系 | redo/undo 在引擎层，binlog 在 Server 层，线上常看 slow/error/binlog | 把 redo 和 binlog 当一种日志 |

下一篇预告：第 31 章 MySQL面试：场景与调优