S25-J09 · MySQL 题库讲评：索引、MVCC、锁与两阶段提交的机制链

适用读者：准备后端中高级面试，要把"背过的 MySQL 答案"连成一条机制链的人。
前置：S11（MySQL 系列）、S25-J07（答题三层骨架）、J05（原理速查）。
覆盖旧文：3474–3480、3500/3501、3571（Mysql 面试题，7 域 60+ 问）→ 10 篇 / 约 12 万字符。
扩展点：E-S25-027（锁的是索引项：行锁为何"升级"）、E-S25-028（MVCC 的读视图与三种读）、E-S25-029（redo/undo 与两阶段提交的崩溃点分析）。
下一篇：S25-J10 · Redis 与缓存题库。

1. 一句话结论

MySQL 的所有"怪现象"都能从一句话推出来：InnoDB 的锁加在索引项上，一致性靠"日志 + 版本链"。 行锁变表锁、间隙锁锁不存在的行、MVCC 让读写不互斥、两阶段提交防丢数据——都是这两条的自然推论。

出处：A-3476（行锁为什么升级为表锁）、A-3477（为什么引入 MVCC）、A-3478（事务提交就丢不了吗）、A-3480、A-3571（题库）。

2. 索引：五个必答点

问题	答法要点
为什么用 B+ 树	矮胖（3~4 层撑千万行）、叶子有序且相连（范围扫描快）、非叶子只存键（页内扇出大）
聚簇 vs 二级索引	聚簇索引叶子存整行；二级索引叶子存主键 → 回表
覆盖索引	查询列全在索引里 → 免回表（`EXPLAIN` 出现 `Using index`）
最左匹配	联合索引 `(a,b,c)` 只能从 a 开始连续用；`a=1 and c=3` 用到 a，c 不行
索引下推 ICP	5.6+ 把 `WHERE` 中索引列条件下推到引擎层过滤，减少回表（`Using index condition`）

索引失效的六种真实原因：隐式类型转换（字符串列传数字）、函数/表达式包裹列、前导模糊 LIKE '%x'、范围条件后的列失效、OR 混非索引列、优化器估算回表代价过高而放弃。

判据："索引失效"不是规则清单，是优化器的成本选择。 答"因为用了函数"只是表层。

3. 锁：锁的是索引项（E-S25-027）

核心一句：InnoDB 锁的是索引记录，不是数据行；具体锁哪个索引，取决于优化器最终选用的索引。

场景	结果
`WHERE` 无索引 → 全表扫描	无法在索引上定位 → 锁粒度退化为表级（"行锁升级为表锁"的真相）
`SELECT ... WHERE id = 150 FOR UPDATE`，150 不存在	在相邻记录间加临键锁 `(120, 170]`，阻止插入 150
`WHERE id > 330 FOR UPDATE`	构造 `(330, supremum]` 的临键锁
唯一索引等值命中	退化为记录锁（Next-Key 退化）

三种锁的层次（必背）：

记录锁 Record Lock   ：锁单条索引记录
间隙锁 Gap Lock      ：锁开区间，只防插入，不防已有行
临键锁 Next-Key Lock ：(前开, 后闭] 区间 = 记录锁 + 间隙锁（RR 默认）

为什么 RR 需要间隙锁：只有锁住"间隙"才能防幻读——已存在的行锁不住"将要插入的行"。

4. MVCC：为什么有锁还要它（E-S25-028）

动机（一句话）：纯锁模型下写会阻塞读，几十毫秒的 UPDATE 会让同一行的 SELECT 全部排队，吞吐崩掉。MVCC 让"读不加锁"，实现读写并发。

三个零件：

零件	作用
隐藏字段	`DB_TRX_ID`（最后修改事务）、`DB_ROLL_PTR`（指向 undo 版本链）
undo 版本链	每次修改把旧值串成链，供历史版本读取
ReadView	快照读时生成，含 `m_ids`（活跃事务）、`min_trx_id`、`max_trx_id`、`creator_trx_id`

可见性判定（答到这一层就够深）：

trx_id < min_trx_id           → 可见（已提交）
trx_id >= max_trx_id          → 不可见（未来事务）
trx_id in m_ids               → 不可见（仍活跃）
else                          → 可见（已提交且早于快照）
不可见则顺着 roll_ptr 找上一个版本

三种读的分界（高频追问）：

类型	语句	读什么
快照读	普通 `SELECT`	ReadView + 版本链（不加锁）
当前读	`SELECT ... FOR UPDATE` / `LOCK IN SHARE MODE` / `UPDATE` / `DELETE`	最新已提交版本 + 加锁
半一致性读	`UPDATE` 的匹配阶段（5.7+）	先读最新版本判断是否匹配，减少锁等待

RR 与 RC 的差别只在 ReadView 的生成时机：RR 在第一次快照读时生成并复用；RC 在每条语句生成新的。

5. 日志与两阶段提交（E-S25-029）

日志	层	作用	关键点
undo log	InnoDB	原子性 + MVCC 版本链	INSERT 的 undo 是 DELETE，DELETE 的是 INSERT，UPDATE 记旧值
redo log	InnoDB	崩溃恢复（持久性）	物理日志、循环写、`innodb_flush_log_at_trx_commit` 控制刷盘
binlog	Server	复制与归档	逻辑日志；`STATEMENT/ROW/MIXED` 三格式

"事务提交后数据真的不丢吗？"——分三层答：

① 提交成功 = redo 已按 innodb_flush_log_at_trx_commit=1 刷盘 → 崩溃可恢复
② 若 =0/2 或 OS 缓存未落盘 → 宕机可能丢最近 1 秒内事务
③ 若磁盘/文件系统本身损坏 → 需 binlog 或备份兜底（redo 也救不了）

两阶段提交（内部 XA）为什么必须有：

prepare：redo 写盘并置 prepare 状态
commit ：binlog 写盘 → redo 置 commit

崩溃点分析（面试的"高级题"）：

崩溃时刻	结果
redo prepare 后、binlog 前	恢复时回滚（binlog 无记录）
binlog 写完、redo commit 前	恢复时提交（binlog 已完整，保证主从一致）

判据：两阶段提交解决的不是"原子性"，而是"redo 与 binlog 的一致性"——否则主库与从库会出现数据分叉。

6. 主从延迟：五个原因与四种解法

原因	说明
从库单线程回放（老版本）	主库并发写 → 从库串行追
大事务	一个事务回放十分钟
无主键/无索引的 `UPDATE`	从库行锁扫描放大
从库压力大	查询 + 备份争抢 IO
网络与半同步等待	`semi-sync` 增加确认时延

解法：并行复制（slave_parallel_workers + LOGICAL_CLOCK）、拆分大事务、强制主键、读写分离时"写后读主"、关键路径用半同步。

7. 场景题标准答法

7.1 亿级数据分库分表后，分页怎么查

错误答法：每库 LIMIT offset,size 再归并 → 深分页时每库都要扫 offset 行，越翻越慢
正确答法：
  ① 禁止跳页（业务妥协）：只允许"下一页"，用上次最大主键做游标（keyset 分页）
  ② 二次查询法：全局排序键上做"每库取 offset+size 条 → 归并取阈值 → 各库回查 >阈值 的记录"
  ③ 异构：把列表页数据同步到 ES / 宽表，分页交给搜索引擎
  ④ 冷热分离：历史数据归档，热表只留近 N 月

7.2 锁等待与死锁现场

SHOW ENGINE INNODB STATUS\G     -- 看 LATEST DETECTED DEADLOCK
SELECT * FROM performance_schema.data_locks;      -- 8.0+ 当前锁
SELECT * FROM performance_schema.data_waits_current;

死锁四要素的工程解法：统一加锁顺序（按主键排序批量更新）、缩短事务、降低隔离级别（RC + 应用层补偿）、innodb_deadlock_detect 取舍（高并发下关闭检测靠超时）。

8. 反例：四种"背了会答错"的说法

说法	问题
"行锁升级为表锁"	InnoDB 没有锁升级机制（那是 SQL Server）；真相是无索引导致全表扫描加锁
"MVCC 解决了幻读"	只解决快照读的幻读；当前读仍需间隙锁
"RR 一定不会幻读"	快照读不幻读，当前读靠临键锁；两者混用仍可能出现
"binlog 是 InnoDB 的"	binlog 属 Server 层，与存储引擎无关

9. 自测

为什么说"锁的是索引项"？什么情况下会退化成表级锁？
RR 与 RC 的 ReadView 差别在哪一行代码语义上？
两阶段提交在"binlog 写完、redo 未 commit"时崩溃，恢复后是提交还是回滚？为什么？
深分页为什么越翻越慢？三种工程解法？

答案

InnoDB 在索引记录上加锁；无可用索引 → 全表扫描 → 只能锁全表（表现为表锁）。
RR 首次快照读生成 ReadView 并复用；RC 每条语句重新生成。
提交：binlog 已完整，按 binlog 重放保证主从一致。
每库都要扫 offset 行再丢弃；解法：游标/keyset、二次查询、异构到 ES、冷热分离。

10. 下一篇

S25-J10 · Redis 与缓存题库 —— 五种缓存模式与一致性、三座大山、命中率数学、过期与淘汰的分工、bigkey 与主从过期陷阱。

本篇属于系列《S25 · 求职与面试》。旧文来源：A-3474…3480、A-3500/3501、A-3571；扩展点 E-S25-027/028/029。技术细节以 duplicate_ref 指回 S11 主讲系列。