一句话主线：MySQL 在面试里考的不是\”会用 SQL\”，而是\”当数据量上涨、并发上来、机器宕机时，它怎么保证不丢、不慢、不错\”——一切设计都是围绕 持久性 / 一致性 / 性能 的三角权衡。
本篇定位：与《JVM 专业深挖》互补（JVM 管运行时内存，InnoDB 管磁盘持久化与事务）。适合大厂一面深挖、二面连环追问、架构师面试。
阅读建议：先读「〇、设计哲学」，再按模块推进。每章末尾有「面试官连环追问」与「架构师决策」。
图 0 · MySQL 知识体系全景
一、存储引擎InnoDB 架构二、磁盘结构表空间/页/段三、事务并发MVCC/锁四、日志redo/undo/binlog五、索引B+树/联合索引六、查询执行计划/优化七、排障慢查询/死锁八、架构选型/分库分表九、高可用 · 主从/集群复制/MGR/容灾
一、InnoDB 存储引擎架构
1.1 为什么是 InnoDB（设计哲学）
一句话：InnoDB 是\”为事务和崩溃安全而生\”的存储引擎——它用「缓冲池 + 双写 + WAL」把随机写变成顺序写，用「MVCC + 行锁」让读不阻塞写、写不阻塞读。
- MyISAM 只有表锁、无事务、崩溃后可能损坏；InnoDB 有行锁、事务、崩溃恢复。
- 面试常问：\”为什么默认引擎是 InnoDB？\” → 事务安全 + 行级锁 + 崩溃恢复 + 外键。
1.2 内存结构与磁盘结构的分工
内存（快·易失）· Buffer Pool 缓冲池（缓存页）· Change Buffer 变更缓冲· Adaptive Hash Index· Log Buffer 日志缓冲作用：减少磁盘 IO，提升读写磁盘（慢·持久）· 表空间 .ibd（段/区/页）· redo log（物理，崩溃恢复）· undo log（逻辑，回滚/MVCC）· binlog（Server 层，复制）作用：持久化、可恢复刷盘
面试追问：Buffer Pool 满了怎么淘汰？（LRU 改进版：分 young/old 区，避免全表扫描污染）
二、磁盘存储结构：表空间、段、区、页
本节将覆盖：表空间类型（系统/独立）、区(1MB)/页(16KB)的层级、行格式(Compact/Dynamic)、溢出页、什么情况下单条记录存不下。
关键数字：默认页大小 16KB；一个区 64 个页 = 1MB；B+树叶子节点存数据，非叶子存主键索引。
2.1 行记录格式：Compact vs Dynamic
InnoDB 行格式默认 Dynamic（MySQL 5.7+）。变长字段（VARCHAR/TEXT/BLOB）若太长放不下页，只存 20 字节指针，真实数据溢出到\”溢出页\”——这就是 Dynamic 相比老的 Compact 更省空间的原因。
| Compact | 前 768 字节留本页，其余溢出 | 老版本 |
| Dynamic | 本页只留 20B 指针，全溢出 | 默认推荐 |
| Compressed | Dynamic + 页级压缩 | 磁盘紧张 |
面试要点：一条记录存不下不是\”报错\”，而是溢出到溢出页；这解释了\”为什么大 TEXT 字段查询慢\”——要额外读溢出页，可能多一次 IO。
2.2 页结构图解（16KB 一页）
File Header (38B) · 页的身份证：页号/前后页指针/B+树层级Page Header (56B) · 本页记录数 / 槽数 / 空闲空间偏移Infimum + Supremum（最小/最大虚拟记录，链表头尾）User Records（真实行记录，单向链表，按主键排序）Free Space（未用空间，插入从这里分配）Page Directory（槽位，二分查找定位记录，避免全链表扫）File Trailer (8B) · 校验页是否完整写入（防半截写）
碎片与空洞：频繁 DELETE/UPDATE 变长字段会产生页内碎片；OPTIMIZE TABLE / ALTER TABLE … ENGINE=InnoDB 可重建表回收空间（在线 DDL 已大幅降低锁表代价）。
面试追问：\”为什么 Page Directory 用槽位？\” → 行记录是单向链表，顺序查慢；槽位把记录分组，组内最大键入槽，查找用二分，把 O(N) 降到 O(logN)。
三、事务与并发控制：ACID 与 MVCC
3.1 ACID 分别靠什么实现
- A（原子性）：undo log —— 回滚靠它。
- C（一致性）：应用层 + 约束 + 事务机制共同保证。
- I（隔离性）：MVCC + 锁。
- D（持久性）：redo log + 双写缓冲 —— 崩溃后靠它恢复。
3.2 MVCC 核心：隐藏事务ID + ReadView
一句话：MVCC 让「读不加锁」——每个事务看到的是它开启那一刻的快照，靠每行隐藏的 trx_id 和一致性视图(ReadView)判断可见性。
MVCC 读：这行对我可见吗？取行记录 DB_TRX_ID（最近修改它的事务ID）DB_TRX_ID < up_limit_id（已提交且在视图前）→ 可见DB_TRX_ID >= low_limit_id（视图后才开启）→ 不可见，找 undo 历史版本在 [up, low) 区间内 → 看是否在活跃事务集合：不在才可见
面试连环追问：
- \”RR 怎么解决幻读的？\” → 快照读靠 MVCC；当前读（select … for update）靠间隙锁(Gap Lock)。
- \”RC 和 RR 的 ReadView 区别？\” → RC 每次读新建视图（不可重复读）；RR 事务内复用首个视图（可重复读）。
3.3 崩溃恢复实操（InnoDB 重启后到底干了啥）
结论先行：InnoDB 崩溃后靠\”redo 保证已提交的不丢、undo 保证未提交的回滚\”——这是一个幂等的两步法，无论宕机在哪一刻，恢复结果都一致。
崩溃恢复三步：
① 表空间校验：扫描 .ibd 的 checksum，确认页没被\”半截写\”破坏（双写缓冲兜底）
② 重放 redo log（ib_logfile）：把\”已提交但未刷盘\”的修改按 redo 重做 → 保证持久性(D)
③ 回滚 undo log：把\”未提交事务\”的修改用 undo 反向撤销 → 保证原子性(A)
关键认知：redo 重放不管事务是否提交——先把所有 redo 都重放（包括后来要回滚的），再靠 undo 把未提交的撤掉。这样即使\”已提交但 redo 没刷盘\”或\”未提交但脏页已写盘\”都能对齐。
innodb_force_recovery 四级（救库用，慎用）：
| 0 | 正常恢复（默认） | 日常 | 无 |
| 1 | 跳过 corrupted page 检查 | 轻微损坏，能起即可导数据 | 可能漏坏页 |
| 2 | 不回滚未提交事务 | 回滚卡死/极慢时 | 留脏数据，需手动处理 |
| 3 | 不应用 redo（只做 ①② 部分） | redo 重放卡死 | 数据可能不一致 |
| 4 | 不合并插入缓冲 | 插入缓冲损坏 | 索引可能缺 |
| 5 | 不读 undo（不回滚） | undo 损坏起不来 | 事务状态乱 |
| 6 | 不重放 redo、不回滚 | 前面都救不了的最后手段 | 数据极可能损坏 |
实操铁律：
- 级别从 1 往上试，能起就停，别一上来用 6。
- 用 force_recovery 起来后只能读/导出，立刻 mysqldump 搬数据到新实例重建——这个模式下的写操作会进一步损坏数据。
- 真正根治是先修底层（磁盘/raid/文件系统），force_recovery 只是\”把数据捞出来\”的临停手段。
架构师决策：崩溃恢复能力是\”免费\”的（只要 redo/undo 正常写），但它救不了\”binlog 已落、主库崩、从库没收到\”的那段——那是半同步/GTID 要解决的。force_recovery 是运维逃生舱，不是日常工具；生产环境应配合定期全量备份 + binlog 增量，让任何崩溃都能\”从备份点重放 binlog\”恢复，而不是靠 force_recovery 赌运气。
四、锁机制：行锁、间隙锁、死锁
本节将覆盖：记录锁/间隙锁/Next-Key Lock、意向锁、锁的兼容矩阵、死锁检测与回滚代价、如何避免死锁。
4.1 锁的三种粒度（行级）
| Record Lock 记录锁 | 单行索引记录 | 防止并发改同一行 | WHERE id=1 FOR UPDATE |
| Gap Lock 间隙锁 | 两条记录之间的\”间隙\” | 防止插入幻影行（防幻读） | RR 下的范围当前读 |
| Next-Key Lock | 记录锁 + 其前面的间隙 | 默认行锁形态，左开右闭 | 范围查询 id>10 AND id<20 |
核心认知：InnoDB 默认行锁其实是 Next-Key Lock（记录 + 间隙），只有\”等值命中且唯一索引\”才降级为纯 Record Lock。这是 RR 级别防幻读的关键。
4.2 间隙锁图解（为什么能防幻读）
索引记录链：5 — 10 — 15 — 20 — 25510152025gapgapgapgap事务A：SELECT * FROM t WHERE id>10 AND id<20 FOR UPDATE→ 锁住 10,15,20 三行 + 其间隙（Next-Key）→ 其他事务无法插入 id=11/12/16/17 等\”幻影行\”RC 级别无间隙锁 → 会出现幻读；RR 级别靠它防住
4.3 锁兼容矩阵（面试必画）
| Record S | ✅ | ❌ | ✅ | ❌ |
| Record X | ❌ | ❌ | ✅ | ❌ |
| Gap（插入意图） | ✅ | ✅ | ✅ | ✅ |
| Next-Key | ❌ | ❌ | ✅ | ❌ |
记忆口诀：
- S 和 X 互斥（读写冲突），但 Gap 之间互不冲突（多个事务可同时持有间隙锁，因为大家都只是\”防插入\”，不冲突）。
- 插入意图锁（Insert Intention）和已存在的 Gap 兼容，但和两个插入同一间隙的记录锁冲突 → 这是死锁高发点。
4.4 死锁四要素（复述 + 破局）
见第七章 7.3 的\”死锁四要素\”。补充 InnoDB 的自动处理：
- 检测到死锁 → 选择 undo 量小（回滚代价低） 的事务作为受害者回滚，返回 1213 错误。
- innodb_deadlock_detect=ON（默认）主动检测；关掉则依赖 lock_wait_timeout 超时，可能雪崩，别关。
避免死锁的工程手段：
面试连环追问：
- \”为什么 Gap 锁之间兼容？\” → 因为 Gap 只是\”声明我在这块区域防插入\”，不阻止别人也防同一块区域，只有真正插入记录时才会和对方冲突。
- \”Next-Key Lock 的左开右闭怎么理解？\” → 锁区间是 (prev_record, current_record]，所以查 id>10 会锁住 10 之后的间隙，包含右边界记录本身。
4.5 间隙锁的代价（架构师必算的一笔账）
结论先行：间隙锁是 RR 防幻读的基石，但它的代价是把\”范围\”变成\”排他带\”——大范围当前读会锁住一整段间隙，并发插入被全堵，吞吐骤降甚至死锁。它不是免费午餐。
真实踩坑场景：
某订单表按 create_time 范围归档，SQL：
DELETE FROM orders WHERE create_time < \’2024-01-01\’ LIMIT 5000; — RR 级别
问题：Ne
