# 第 20 章 MySQL 架构与存储引擎
> 本章给出后面七章的地基：一条 SQL 究竟穿过哪几层，InnoDB 把数据组织成了什么形状。
## 学习目标
读完本章，你应当能够：
1. 推导一条 SQL 从连接建立到结果返回的完整路径，并说清每一步失败的报错现象；
2. 判断一个查询为什么"看起来该走索引却没走"，并用统计信息、代价模型、直方图三个角度解释；
3. 画出 buffer pool 的分段 LRU 结构，说明全表扫描如何被 `innodb_old_blocks_time` 挡住；
4. 按表空间 → 段 → 区 → 页 → 行五级复述 InnoDB 的磁盘组织，并说明"为什么按区分配"；
5. 逐字节拆解 COMPACT 行格式，计算单行最大长度与溢出条件；
6. 串联一条 `UPDATE` 的十个步骤，指出每一步崩溃后的恢复结果。
### 前置知识检查
| 你需要知道 | 在哪一节补齐 |
|---|---|
| 虚拟内存、页、缺页与页替换算法 | 见第 8 章 8.3 节（buffer pool 是它在用户态的翻版） |
| 磁盘随机 I/O 与顺序 I/O 的延迟量级 | 见第 13 章 13.1 节 |
| TCP 连接建立、TIME_WAIT 与应用层连接池 | 见第 16 章 16.2 节 |
| B+ 树的查找过程与树高估算 | 见第 23 章 23.1 节（本章只讲索引页在内存里怎么占位） |
| 日志先行（write-ahead logging, WAL）的思想 | 见第 21 章 21.1 节（本章 20.9 节只做链路串联） |
## 20.1 三层架构：一条 SQL 要穿过谁
MySQL 的逻辑架构是一条单向流水线，任何一次查询都要依次穿过连接层、SQL 层、存储引擎层。分层的意义不在"好看"，而在**把与存储无关的通用能力（语法、权限、优化）与与存储强相关的持久化能力（事务、索引、崩溃恢复）解耦**。
### 20.1.1 连接层：线程模型与最大连接数
连接层（connection layer）负责 TCP 握手、账号密码认证、权限加载、以及每个连接分配一个线程。
- **线程模型**：MySQL 是"一连接一线程"（one-thread-per-connection）。连接建立时从线程缓存（`thread_cache_size`）取，取不到才 `pthread_create`。线程栈按 `thread_stack`（默认 1 MB 左右）预留虚拟内存，因此**连接数上限本质上受内存与 `ulimit` 约束，而不是受某个"数据库自带上限"约束**。
- **最大连接数**：`max_connections` 默认 151，`max_user_connections` 限制单账号。超过 `max_connections` 时新连接直接被拒并报 `ERROR 1040 (HY000): Too many connections`；注意**超级账号会额外保留一个连接槽**，所以管理员仍能登进去救火。
- **连接池（connection pool）**：池属于客户端/驱动侧（HikariCP、Druid 等），MySQL 服务端并不知道"池"的存在。池的意义是省掉每次认证 + 建 TCP 的开销；同机房 RTT 约 0.1~0.5 ms，一次建连含认证通常要 1 ms 量级，而一条走主键的查询本身可能只有 0.2 ms，所以**不池化时代价全在建连上**。
> [!WARNING]
> 把 `max_connections` 调到几千是常见的错误处理方式。每个连接背后有一个线程与一份会话级内存（sort buffer、join buffer 等按需分配），连接数翻十倍会把内存和上下文切换（约 1~5 μs/次）一起推高，最终整体 QPS 下降。正确做法是先查连接为什么被长时间占用。
### 20.1.2 SQL 层：解析、优化、执行
SQL 层（server layer）不碰数据文件，只处理命令。它包含：
| 组件 | 职责 | 面试常问的机制点 |
|---|---|---|
| 解析器（parser） | 词法分析 + 语法分析，产出解析树 | 语法错误在优化器之前就报出，与索引无关 |
| 预处理器（preprocessor） | 语义检查、表/列存在性、权限校验、常量折叠 | 视图与别名的展开在此完成 |
| 优化器（optimizer） | 逻辑变换 + 基于代价选择执行计划 | 本章 20.3 节整节 |
| 执行器（executor） | 按计划调用存储引擎接口取行 | 逐行取，`handler_read_*` 计数在此累加 |
| 查询缓存（query cache） | 以 SQL 文本为 key 缓存结果集 | 8.0 已移除，见下 |
**查询缓存为什么在 8.0 被移除**：它的失效粒度是**表级**——表上任何一行被任何语句修改，该表所有缓存条目全部失效。这意味着写密集场景下缓存命中率接近零，而每一次写都要额外维护全局互斥的缓存失效；且它仍要经过完整解析与权限校验，省下的只是执行阶段。收益趋零、成本恒定，于是被整体删除。
### 20.1.3 存储引擎层：可插拔的意义
存储引擎层（storage engine layer）以 `handler` 接口向上暴露一套统一原语：`ha_open`/`index_read`/`write_row`/`delete_row`/`rnd_next`/`commit`/`rollback` 等。SQL 层只认识这组接口，因此：
- 同一套 SQL 语法、权限、优化器可以服务于不同引擎；- 引擎可以按表指定：`CREATE TABLE t (...) ENGINE = InnoDB`；- 引擎实现不了的能力，上层靠能力标志位降级（例如引擎不支持事务时 `BEGIN` 无意义）。
**可插拔真正的价值不是"能换引擎"，而是"SQL 层可以完全不假设数据怎么存"**——这也是为什么 InnoDB 能自行决定页大小、行格式、索引结构而对优化器透明。
> [!NOTE]
> 面试官追问的下一层：既然存储引擎可插拔，为什么 `EXPLAIN` 的结果与引擎无关？因为 `EXPLAIN` 输出的是**计划**，不是实际执行；真正的差异要到 `EXPLAIN ANALYZE` 或 `Handler_read_*` 状态变量才看得出来。
## 20.2 一条 SQL 的完整生命周期
以 `SELECT * FROM t WHERE a = 1 AND b > 5` 为例，逐步展开：
```text
客户端
│ ① TCP 三次握手
▼
[连接层] ② 认证：用户名/密码/host 匹配 → 加载该账号权限
│
▼
[SQL 层]
③ 词法分析：切 token，"SELECT" "FROM" "t" 是标识符
④ 语法分析：按 SQL 标准文法构解析树（syntax tree）
⑤ 预处理：表 t 存在吗？列 a、b 存在吗？当前账号对 t 有 SELECT 权限吗？
常量折叠：b > 5 中的常量先算完
⑥ 优化器：逻辑变换（外连接转内连接、子查询解嵌套、条件下推）
→ 枚举候选计划 → 代价模型比较 → 选出索引与访问路径
产出执行计划（access path + 索引）
⑦ 执行器：先调引擎接口做第一次取数
→ 引擎在 B+ 树中定位 → 返回"一行"
→ 执行器对每行做条件复核（不能全信引擎的过滤）
│
▼
[存储引擎层] ⑧ 页不在 buffer pool → 从表空间文件读 16 KB 页
返回的记录要被复制的列 → 组装成结果行
│
▼
⑨ 结果集分批发给客户端（默认不缓存整个结果集）
```
每一步出问题时的现象：
| 步骤 | 失败现象 | 关键判据 |
|---|---|---|
| ① 连接 | `ERROR 2003` 连不上；`ERROR 1040` 连接数满 | 与 SQL 无关，先看网络与 `max_connections` |
| ② 认证/权限 | `ERROR 1045 Access denied` | 密码错与 host 不匹配是同一报文，需查 `mysql.user` |
| ③ 词法 | `ERROR 1064 You have an error in your SQL syntax` | 报出的位置是 token 位置，不是逻辑错误 |
| ④ 语法 | 同上，但位置在语句末尾附近 | 常见于 JOIN 条件缺失、括号不配对 |
| ⑤ 预处理 | `ERROR 1146 Table doesn't exist`、`ERROR 1054 Unknown column`、`ERROR 1142 SELECT command denied` | **权限错误在这里报，不在执行时**；优化器还没跑 |
| ⑥ 优化 | 不报错，但计划变差（`type: ALL`、`Using filesort`） | 用 `EXPLAIN` / `optimizer_trace` 看 |
| ⑦ 执行 | 报错或超时；也可能"不报错但慢" | `SHOW PROCESSLIST` 看状态（`Sending data`、`Copying to tmp table`） |
| ⑧ 引擎 | `ERROR 1114 The table is full`、`ERROR 1205 Lock wait timeout` | 引擎层错误码，见第 24 章 24.5 节 |
> [!TIP]
> 面试话术：被问"一条 SQL 怎么执行的"，不要背流水线名字，而要主动交代**"权限校验发生在预处理阶段，所以没权限的语句不会进入优化器"**与**"执行器必须自己对每行做条件复核"**——这两点是区分背过书与读过实现的分水岭。
## 20.3 优化器为什么可能选错索引
优化器的全部依据是**代价（cost）**，代价来自**统计信息**。统计信息是抽样出来的估计值，估计就会错。
### 20.3.1 统计信息：索引基数与采样
InnoDB 为每个索引维护**基数（cardinality）**，即"不同值的个数估计"。它不精确统计，而是**随机采样若干页**，对页内记录做基数估算再外推。
- `innodb_stats_persistent`（默认 ON）：统计信息持久化到 `mysql.innodb_index_stats` / `innodb_table_stats`，避免每次重启重算导致计划漂移。
- `innodb_stats_persistent_sample_pages`（默认 20）：采样页数。20 页 × 每页约 100 条 = 约 2000 条记录来代表上亿行，误差自然可观。
- `innodb_stats_auto_recalc`（默认 ON）：表变更行数超过约 10% 时自动重算，这解释了"为什么同一条 SQL 昨天快今天慢"。
`SHOW INDEX FROM t` 的 `Cardinality` 列就是它。基数错 → 优化器认为"走索引要回表 N 次" → 于是选了全表扫描。
### 20.3.2 代价模型与常量
MySQL 用两个基本常量把 I/O 与 CPU 折算成同一单位：
| 常量 | 含义 | 默认值 |
|---|---|---|
| `io_block_read_cost` | 读一个数据块的成本 | 1.0 |
| `memory_block_read_cost` | 从内存读一个块的成本 | 0.25 |
| `innodb_page_size` | 页大小 | 16 KB |
| `innodb_old_blocks_time` | 页进入 old 区后多久才允许晋升 | 1000 ms |
`1.0 : 0.25` 就是"内存比磁盘快 4 倍"的工程近似（真实量级差 10⁵ 倍，见第 13 章 13.1 节）。代价模型里最不可靠的一项正是**回表代价**：优化器假设"按索引顺序回表"是廉价的，实际上二级索引的顺序与主键顺序经常不一致，回表就退化成随机 I/O；缓冲池命中率低时误差被放大到几个数量级。
### 20.3.3 直方图与统计信息的补丁
MySQL 8.0 引入**直方图（histogram）**，用来描述列值的分布，弥补基数只给"不同值个数"、不给"分布形状"的缺陷：
```sql
-- 用直方图补上"分布形状"这一维：优化器原本只知道 a 有 100 万个不同值，
-- 不知道 a = 1 其实占了 90% 的行，所以会误判走索引更划算
ANALYZE TABLE t UPDATE HISTOGRAM ON a WITH 64 BUCKETS;
SHOW INDEX FROM t;
```
单列直方图有两种：等高的 singleton（每个桶一个值）与等宽的 equi-height（用于范围查询估算）。
### 20.3.4 干预手段与 ANALYZE TABLE 的代价
| 手段 | 写法 | 适用与代价 |
|---|---|---|
| 强制索引 | `SELECT ... FROM t FORCE INDEX(idx_a) WHERE ...` | 直接指定，绕过代价比较；写死后再改数据分布可能更糟 |
| 忽略索引 | `IGNORE INDEX(idx_a)` | 用于排除某个被误选的索引 |
| 重算统计 | `ANALYZE TABLE t;` | 重新采样，代价与表大小成正比，会持有表级元数据锁（MDL） |
| 增加采样页 | `SET GLOBAL innodb_stats_persistent_sample_pages = 256;` | 更准但 `ANALYZE` 更慢，DDL 期间也变慢 |
| 改写 SQL | 把 `OR` 拆成 `UNION ALL`、把函数从列上移到常量侧 | 让代价模型能看清，而不是骗它 |
```sql
-- 为什么这样改写：优化器对 OR 的两支要分别估代价，估错就退化成全表扫描；
-- 拆成 UNION ALL 后每条子查询都能用最左前缀，代价估算的输入变简单了
SELECT id, a FROM t WHERE a = 1
UNION ALL
SELECT id, a FROM t WHERE b = 2;
-- 为什么这样改写：列上套函数会让索引失效（对索引列做运算，B+ 树无法按序定位），
-- 把运算搬到常量侧，索引才能被用上
SELECT id FROM t WHERE created_at >= '2024-01-01 00:00:00';
```
> [!IMPORTANT]
> `FORCE INDEX` 是**急救手段而不是优化手段**。它把优化器的判断替换成人的判断，一旦数据分布变化、索引被删或改名，语句会直接报错或性能崩塌。生产上优先做的是"让统计信息更准 + 让 SQL 的形状更容易被估算"。
## 20.4 InnoDB 与 MyISAM 的取舍
| 维度 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 事务 | 支持（ACID） | 不支持 | 不支持 |
| 锁粒度 | 行级（含间隙），另有表级意向锁 | 表级 | 表级 |
| 外键 | 支持 | 不支持 | 不支持 |
| 崩溃恢复 | redo/undo 保证，可恢复到崩溃前已提交 | 表易损坏，需 `REPAIR TABLE`，可能丢数据 | 数据全丢 |
| 索引结构 | 聚簇索引（主键即数据），二级索引存主键值 | 非聚簇，索引与数据分离，索引存行地址 | 哈希索引 |
| `COUNT(*)` | 需遍历最小的二级索引逐行计数 | 维护行计数，O(1) 返回 | O(1) |
| 全文索引 | 5.6+ 支持 | 支持（更早） | 不支持 |
| 压缩 | 支持（`COMPRESSED` 行格式） | 支持（`myisampack`，只读） | 不支持 |
**为什么现在基本只剩 InnoDB**：现代后端的默认前提是"崩溃恢复不能丢已提交事务""并发写不能锁整表"。MyISAM 的 `COUNT(*)` 是 O(1) 这点优势，在 InnoDB 上可以用计数表或缓存替代，而它失去的行锁与崩溃安全无法替代。Memory 引擎只在临时表、会话级中间结果这类**可重建数据**上有位置。
> [!CAUTION]
> "MyISAM 查询比 InnoDB 快"是会被追着打的回答。要区分：**纯读、无并发、数据量小**时 MyISAM 因为少了事务与 MVCC 的簿记确实可能略快；但只要出现并发写，MyISAM 的表级锁会让它整体吞吐远低于 InnoDB 的行锁。用单线程 benchmark 得出"MyISAM 快"的结论，是典型的测错场景。
## 20.5 InnoDB 内存结构
### 20.5.1 buffer pool 的组织与分段 LRU
buffer pool（缓冲池）以**页**（page，16 KB）为管理单位缓存数据页与索引页。它的三层组织是：
```text
innodb_buffer_pool_size = 8 GB, innodb_buffer_pool_instances = 4
┌──────────────────────────────────────────────────────────────┐
│ instance 0 instance 1 instance 2 instance 3 │
│ 2 GB 2 GB 2 GB 2 GB │
│ └ 由 chunk 组成（innodb_buffer_pool_chunk_size 默认 128 MB）│
│ 每个 chunk 是一段连续内存，内含多个 16 KB 的页描述符 │
└──────────────────────────────────────────────────────────────┘
每个 instance 内部各有一套 LRU 链表：
┌──────────────────────── LRU list ────────────────────────┐
│ young（new）子链 ~5/8 │ old 子链 ~3/8 │
│ ← 头部 = 最近使用 │ 尾部 = 即将淘汰 │
│ 新读入的页先插到 old 子链的头部（交界处中点） │
└──────────────────────────────────────────────────────────┘
页被访问时：若在 old 子链 且 停留时间 < innodb_old_blocks_time(1000ms)
→ 不移动到 young 头部（防止一次性扫描把热点挤出去）
```
关键参数：`innodb_old_blocks_pct`（默认 37，即 old 区占 3/8）、`innodb_old_blocks_time`（默认 1000 ms）。**分段 LRU 解决的不是"LRU 不准"，而是"全表扫描或备份类大查询会顺序读完所有页，把整个 LRU 冲一遍"**：在 `old_blocks_time` 内被反复访问的页不会被晋升，扫描完就自己在 old 区尾部淘汰掉。
### 20.5.2 change buffer：为什么唯一索引不能用
change buffer（写缓冲，5.5 前叫 insert buffer）缓存的是**对非唯一二级索引页的修改**。当一条 `INSERT`/`UPDATE`/`DELETE` 要修改的二级索引页不在 buffer pool 时，不必立刻把页从磁盘读进来，而是把"这个页要加一条记录"记在 change buffer 里，等该页因其他原因被读入时再合并（merge）。
- **为什么唯一索引不能用**：唯一性约束要求写入前必须确认"不存在重复值"，这个确认必须读索引页；页都已经读进来了，缓冲就失去意义。- **适用条件**：写多读少、且二级索引多的表收益最大（把多次随机 I/O 攒成一次）。
### 20.5.3 log buffer 与自适应哈希索引
- **log buffer**（`innodb_log_buffer_size`，默认 16 MB）：redo log 先写这里，再按 `innodb_flush_log_at_trx_commit` 策略刷到磁盘。它存在的意义是把"每次事务提交一次写"合并成"按块写"。
- **自适应哈希索引（adaptive hash index, AHI）**：InnoDB 观察等值查询的访问模式，若同一个索引前缀被频繁以相同方式访问，就在 buffer pool 里为它建一个哈希索引，把 B+ 树的 `O(log n)` 查找降为 `O(1)`。争议在于：哈希表本身占用 buffer pool 内存、由 `innodb_adaptive_hash_index_parts`（默认 8）分区保护、高并发下 `rw-latch` 会成为争用点，且它只对等值查询有效。因此在读写混合且热点分散的负载上，关掉它反而更稳。
> [!NOTE]
> 面试官追问的下一层：AHI 与"我们自己建哈希索引"有什么区别？AHI 是**自动、局部、可丢弃**的：它只覆盖被证明热的前缀，且丢失后不影响正确性；手建哈希索引是全局的、要显式维护、且不支持范围查询。
## 20.6 InnoDB 磁盘结构：表空间到行
```text
表空间 (tablespace)
├─ 系统表空间 ibdata1：数据字典、change buffer、双写缓冲等
├─ 独立表空间 t.ibd（innodb_file_per_table=ON，默认）
├─ 通用表空间（CREATE TABLESPACE ... 多个表共享）
├─ 临时表空间（ibtmp1，会话级排序/中间结果）
└─ undo 表空间（undo_001/undo_002，8.0 起独立）
│
└─ 段 (segment)：B+ 树的非叶子段/叶子段、回滚段。
InnoDB 按段分配区，段内区可以不连续
│
└─ 区 (extent)：连续 64 个页 = 64 × 16 KB = 1 MB
（innodb_page_size 变小/变大时，区大小按 1 MB 折算：
4 KB 页→256 页，32 KB 页→32 页）
│
└─ 页 (page)：16 KB，B+ 树的节点 = 一页
│
└─ 行 (row)：COMPACT / DYNAMIC 等行格式
```
**为什么按区（1 MB）而不是按页分配**：若每次只向表空间申请一页，同一棵 B+ 树的相邻页会散布在整个文件里，顺序扫描退化成随机 I/O。一次申请 1 MB 连续空间，能让 B+ 树的**顺序扫描在一段连续磁盘区间内完成**，把随机 I/O 变成顺序 I/O（SSD 随机读约 100 μs，机械盘随机读约 5~10 ms，顺序 I/O 可高出 1~2 个数量级）。同时"区"也是段空间回收的粒度。
## 20.7 页的物理结构
每一个 16 KB 页的结构（页内偏移从 0 开始，以默认 16 KB 页计）：
```text
偏移 0 ┌──────────────────────────────┐
│ File Header（38 字节） │ 页号、上一页/下一页页号、
│ │ 页类型、LSN、校验和
38 ├──────────────────────────────┤
│ Page Header（56 字节） │ 槽数、堆顶位置、记录数、
│ │ 空闲空间起始、最大事务 ID
94 ├──────────────────────────────┤
│ Infimum（最小伪记录，13 字节）│ 页内下界，固定值
├──────────────────────────────┤
│ Supremum（最大伪记录，13 字节）│ 页内上界，固定值
├──────────────────────────────┤
│ User Records（用户记录） │ 按主键升序，用 next_record
│ ↑ 从 Free Space 空间"倒着"分配│ 串成单向有序链表
├──────────────────────────────┤
│ Free Space（空闲空间） │
├──────────────────────────────┤
│ Page Directory（页目录） │ 若干"槽"(slot)，每个槽指向
│ │ 一条记录；槽对应记录按主键有序
16384−8 ├──────────────────────────────┤
│ File Trailer（8 字节） │ 校验和 + 页 LSN 低 32 位
└──────────────────────────────┘
```
**Page Directory 与页内查找的关系**：页目录把有序记录按每 4~8 条一组分成若干槽，槽内记录数控制在 4~8 之间（平衡二分深度与插入时的维护成本）。页内定位一条记录是**两段式**：
1. 在页目录上用**二分查找**找到"最后一个大于等于目标值"的槽，复杂度 `O(log n)`；2. 从该槽指向的记录沿 `next_record` **顺序遍历**若干条（≤ 8 条）。页内查找因此既不是全链表遍历（`O(n)`），也不需要维护一棵树（插入代价高）。`File Trailer` 的校验和用于检测**页写了一半（partial page write）**，与双写缓冲（doublewrite buffer）配合完成页级崩溃修复。
> [!NOTE]
> 面试官追问的下一层：既然页目录能二分，为什么不用"槽数固定、每槽 1 条记录"让二分更纯？因为**槽的分裂与合并是有代价的**：每次插入都可能引起页分裂并重排槽；槽越大（每槽记录越多）、目录越小，插入时改动的槽越少。4~8 这个区间是 InnoDB 权衡后的经验值。
## 20.8 行格式与行溢出
四种行格式的差异集中在"变长列怎么记、长值放哪"：
| 行格式 | 变长列长度信息 | NULL 信息 | 长列（BLOB/大 VARCHAR）处理 | 说明 |
|---|---|---|---|---|
| REDUNDANT | 每个字段 1~2 字节偏移数组 | 字节位图 | 前 768 字节存在页内，其余溢出 | 兼容旧版本 |
| COMPACT | 变长字段长度列表（逆序、按需 1~2 字节） | 字节位图 | 前 768 字节存页内，其余溢出 | 5.0~5.6 常用 |
| DYNAMIC | 同 COMPACT | 同 COMPACT | **20 字节指针，值全部溢出到溢出页** | 5.7+ 默认 |
| COMPRESSED | 同 DYNAMIC | 同 DYNAMIC | 溢出页以 zlib 压缩，页大小可 1/2/4/8 KB | 换 I/O 为 CPU |
COMPACT 行的完整布局：
```text
┌──────────────────┬────────────────┬──────────────────────────────┐
│ 变长字段长度列表 │ NULL 值列表 │ 记录头信息（5 字节） │
│ 逆序存放，每个 │ 每列 1 bit， │ delete_mask next_record │
│ 变长列 1~2 字节 │ 只记可为 NULL │ heap_no record_type │
│ │ 的列 │ n_owned (5 字节共 40 bit) │
├──────────────────┴────────────────┴──────────────────────────────┤
│ 列 1 值 │ 列 2 值 │ 列 3 值 │ ... │ 主键列值 由 B+ 树顺序决定 │
└──────────────────────────────────────────────────────────────────┘
记录头信息的关键位：
delete_mask 0=正常 1=已删除（标记删除，不立即回收空间）
next_record 到下一条记录地址的偏移（负数表示向左）；页内记录成链
heap_no 页内堆序号；0=Infimum 1=Supremum，用户记录从 2 开始
n_owned 该记录所"拥有"的槽内记录数，只有槽的"最大记录"才非 0
record_type 0=普通 1=目录槽记录(非叶子节点) 2=Infimum 3=Supremum
```
**行溢出与单行最大长度**：DYNAMIC 下，长变长列在记录中只留 20 字节指针，真正的值放在**溢出页（off-page）**组成的链表里，链表的头 20 字节内记录"还剩多少字节"。因此**一个 16 KB 的表空间页可以容纳远超 16 KB 的逻辑行**。
但行内部分仍受页大小约束，于是有两条硬限制：
- **MySQL 层**：`VARCHAR` 的最大声明长度为 65535 字节，且**所有列的长度之和（含开销）不得超过 65535**。**InnoDB 层**：单页 16 KB 减去页头（38 + 56 字节）、伪记录（13 + 13 字节）、页目录与校验和，可用空间约 8126 字节；再减去记录头与长度列表，**单行页内部分上限约为 8000 字节量级**。所以 `CREATE TABLE t (a VARCHAR(65532))` 必然报 `ERROR 1118 Row size too large`。
> [!WARNING]
> 常见错误回答："`TEXT`/`BLOB` 一定存在溢出页"。不是——**只有长到放不下页内部分才会溢出**。COMPACT 是"能放多少放多少，最多页内留 768 字节"；DYNAMIC 是"要么完整留页内，要么整个挪到溢出页，只留 20 字节指针"。这也是 5.7 把默认行格式换成 DYNAMIC 的原因：指针固定 20 字节，让优化器对行长度的估计更稳定。
## 20.9 一条 UPDATE 的完整生命周期
`UPDATE t SET v = v + 1 WHERE id = 1` 的执行链路（每个编号是一个可崩溃点）：
```text
① 执行器调引擎接口，按主键定位记录（引擎接口带锁语义）
② 目标页不在 buffer pool → 从表空间文件读入 16 KB 页（随机 I/O 约 100 μs）
③ 写 undo log：记录"旧值"(v = 10)。undo 页同样先写 buffer pool 的 undo 页
④ 修改 buffer pool 中的页：v = 11。该页被标记为脏页（dirty page）
⑤ 写 redo log 到 log buffer，内容包含数据页改动与 undo 页改动
⑥ 事务进入 prepare 阶段：redo log 从 log buffer 刷盘并标记 prepare
⑦ 写 binlog 到 binlog 文件（按 sync_binlog 策略刷盘）
⑧ 事务提交：redo log 由 prepare 改为 commit，写 commit 记录
⑨ 释放行锁；⑩ 脏页由后台线程（page cleaner）异步刷回表空间，change buffer 中的相关页同时完成 merge
```
| 崩溃点 | 崩溃后的恢复结果 | 原因 |
|---|---|---|
| ③ 之前 | 无影响 | 什么都没改 |
| ③ 之后、⑥ 之前 | 改动丢失（未提交） | redo 未刷盘的这部分不存在；undo 已刷盘的部分由回滚段标注为已提交/未提交 |
| ⑥ 与 ⑦ 之间（redo 已 prepare，binlog 未写） | **回滚** | 两阶段提交判定 binlog 不完整 → 事务不提交 |
| ⑦ 与 ⑧ 之间（binlog 已写，redo 未 commit） | **提交** | binlog 完整 → 按 binlog 内容补提交 redo |
| ⑧ 之后、⑩ 之前 | 数据不丢 | 崩溃前已提交；脏页未落盘但 redo 可前滚重放 |
> [!IMPORTANT]
> 上表最后两行是两阶段提交的全部意义：**redo 的 prepare 与 binlog 的完整性共同决定了"提交还是回滚"**，从而保证 redo log 与 binlog 不会互相矛盾。完整推导与三种崩溃时机的恢复算法见第 21 章 21.5 节；本章只要求你能把链路串起来。
## 本章小结
| 关键结论 | 一句话表述 | 常见错误 |
|---|---|---|
| 架构分层的意义 | SQL 层不假设数据怎么存，引擎层可插拔 | 认为"可插拔"是为了让用户随时换引擎 |
| 查询缓存被移除 | 表级失效让写密集场景命中率趋零，成本却恒定 | 答"因为它占用内存" |
| 权限校验位置 | 在预处理阶段，早于优化器 | 答"在执行时才检查权限" |
| 优化器选错索引 | 根因是统计信息抽样误差 + 回表代价被低估 | 答"优化器有 bug"或"加 FORCE INDEX 就好" |
| buffer pool 分段 LRU | old 区 + `innodb_old_blocks_time` 挡住全表扫描污染 | 认为 LRU 就是标准的最近最少使用 |
| change buffer | 只服务非唯一二级索引，因为唯一性必须读页确认 | 答"它能加速所有写" |
| 按区分配 | 1 MB 连续区让 B+ 树顺序扫描落在连续磁盘上 | 答"为了防止碎片"却说不清碎哪种片 |
| 行溢出的条件 | 只有页内放不下才溢出；DYNAMIC 只留 20 字节指针 | 答"有 BLOB 就溢出" |
## 自测题
**Q1**（计算）默认配置下，`innodb_page_size = 16 KB`，一个区有多少页、多大？若某表主键为 `BIGINT`（8 字节）+ 6 字节行开销，二级索引键为 `INT`（4 字节）+ 主键，估算一个 16 KB 叶子页能放多少条二级索引记录。
答案与解析
一个区 = 64 页 = `64 × 16 KB = 1 MB`。若页大小改为 4 KB，则一个区 = 256 页（InnoDB 保持"区约 1 MB"的口径）。
二级索引记录 = 索引列 + 主键列 + 记录头 5 字节 + 变长长度列表 + 页目录与页头开销。按 `4 + 8 + 5 ≈ 17` 字节/条算，16 KB 页可用约 15 KB，理论上限约 `15000 / 17 ≈ 880` 条；实际 InnoDB 经验值是**每页 500~1000 条**，取决于是否留出页分裂余量（通常保留约 `1/16` 的空闲）。
**错在哪**：最常见错误是忘了二级索引里存主键（InnoDB 的二级索引叶子节点存的是主键值），只按 4 字节算，结果偏大一倍以上。
**面试官会追问**：那树高是多少？按每页 800 条、非叶子节点每页能放 `16000 / (8 + 6) ≈ 1100` 个"键 + 页号"算，三层 B+ 树可索引 `1100 × 1100 × 800 ≈ 9.7 × 10⁸` 行——所以**上亿行的表树高通常仍是 3**，一次等值查询最多 3 次页访问。完整估算见第 23 章 23.1 节。
答案与解析
MyISAM 维护了一个精确的行计数，`COUNT(*)` 是 O(1)；InnoDB 因为 MVCC 下"不同事务看到的行数可能不同"，**无法维护一个全局精确计数**，只能选一个最小的二级索引做全索引扫描，是 O(n)。
但这个对比只在"只需要计数"这一种负载上成立。真正的问题是：**为了一个 `COUNT(*)` 把整张表换成没有事务、没有行锁、崩溃后可能损坏的引擎**，是把次要需求当成主要需求。替代方案：维护一张计数表（在同一事务里 `INSERT ... ON DUPLICATE KEY UPDATE cnt = cnt + 1`），或用 Redis 计数器并接受最终一致（一致性方案见第 27 章 27.8 节）。
**错在哪**：把"单语句的耗时对比"当成"引擎选型依据"，忽略了并发写、崩溃恢复、事务语义这些加权项。
**面试官会追问**：`COUNT(*)` 和 `COUNT(1)` 和 `COUNT(列)` 有区别吗？`COUNT(*)` 与 `COUNT(1)` 语义与性能一致（都统计行数）；`COUNT(列)` 要判断该列是否为 NULL，因此**不会走最小的索引、也不能用列可空的优化**。详见第 25 章 25.6 节。
答案与解析
优化器的判断链条是：走索引的代价 = 索引页访问 + `估计行数 × 回表代价`；全表扫描代价 = `页数 × io_block_read_cost`。它用 `rows × filtered` 估出约 4000 万行要回表，把回表折算成 `memory_block_read_cost = 0.25`（因为它假设页会命中 buffer pool）。可是 40% 的行意味着**几乎每个数据页都要回一次**，而顺序扫描每页只读一次；优化器把"随机回表"低估成了"廉价的顺序读"。干预方式是 `FORCE INDEX`/`IGNORE INDEX` 直接改变候选集，或者让统计信息更准（`ANALYZE TABLE`、加大 `innodb_stats_persistent_sample_pages`、对该列建直方图）。
**错在哪**：直接答"因为优化器有 bug"。优化器没有 bug，它在**错误的输入假设**下做了正确的比较；连"回表代价被低估在缓冲池命中率低时更严重"都说不出，就停在第一层。
**面试官会追问**：什么情况下走索引一定比全表扫快？大致分界是**结果集占比低于约 20%~30%**（经验值，取决于行宽、缓冲池命中率、是否覆盖索引）；如果是**覆盖索引**（要的列都在索引里，不需要回表），即使扫全表也能走索引且更快。
答案与解析
原因有两层：一是 `status` 只有 3 个取值，**基数极低**，优化器其实倾向于忽略它直接全表扫（或扫描 `status` 索引叶子）；二是即使走了 `status` 索引，索引内部的顺序是 `(status, 主键)`，**`created_at` 并不按序**，所以 `ORDER BY created_at DESC` 必须额外排序（`Using filesort`），而 `LIMIT 20` 救不了——排序要在取完全部匹配行之后才能确定前 20 条。方案是建联合索引，把等值列放前、排序列放后：
```sql
-- 为什么列顺序是 (status, created_at)：等值条件列必须在前，
-- 排序列在后，这样在 status = 1 这个"等值前缀段"内，created_at 天然有序，
-- 优化器就能从索引末端（DESC）反向扫描 20 条即止，完全免排序
ALTER TABLE t ADD INDEX idx_status_created (status, created_at DESC);
```
**错在哪**：只答"加索引"，不解释**为什么单列索引不够**——联合索引的可排序性来自"等值前缀 + 后续列有序"这个结构，而不是来自列的堆叠。
**面试官会追问**：如果业务还要 `WHERE status = 1 AND type = 2 ORDER BY created_at DESC`，索引该怎么建？顺序是 `(status, type, created_at)`；若 `type` 的选择性比 `status` 高很多，也可以 `(type, status, created_at)`，因为两个等值列之间的顺序不影响排序段的有序性，只影响扫描的起点范围。详见第 23 章 23.5 节。
答案与解析
机制是"分两次访问才晋升"的时间门槛。新页进入时插在 old 子链头部；只有**在 old 区停留超过 1000 ms 后又被访问**的页，才会被移动到 young 区头部。顺序扫描对每一页的访问只发生一次（或两次，间隔极短），因此永远达不到 1000 ms 的门槛，扫完就自己在 old 区尾部被淘汰，**young 区的热点数据完全不受影响**。设成 0 时，任何页只要被再次访问就立刻晋升到 young 区头部——这等于取消了保护：一次全表扫描会把热点页全部挤出，缓存命中率断崖式下降。
**错在哪**：把 `innodb_old_blocks_time` 理解成"页的存活时间"或"淘汰延迟"。它约束的是**晋升资格**，不是淘汰时间。
**面试官会追问**：那 `innodb_old_blocks_pct = 37` 又是什么？它是 old 区占整个 LRU 链表的比例（默认 37%，即约 3/8）。调大它会让"抗扫描"能力变强（更多页放在易淘汰区），代价是热点页能占据的空间变小、有效缓存容量下降。两者要一起看。