第 20 章 MySQL 架构与存储引擎

本章给出后面七章的地基：一条 SQL 究竟穿过哪几层，InnoDB 把数据组织成了什么形状。

学习目标

读完本章，你应当能够：

1. 推导一条 SQL 从连接建立到结果返回的完整路径，并说清每一步失败的报错现象；
2. 判断一个查询为什么"看起来该走索引却没走"，并用统计信息、代价模型、直方图三个角度解释；
3. 画出 buffer pool 的分段 LRU 结构，说明全表扫描如何被 innodb_old_blocks_time 挡住；
4. 按表空间 → 段 → 区 → 页 → 行五级复述 InnoDB 的磁盘组织，并说明"为什么按区分配"；
5. 逐字节拆解 COMPACT 行格式，计算单行最大长度与溢出条件；
6. 串联一条 UPDATE 的十个步骤，指出每一步崩溃后的恢复结果。

前置知识检查

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，所以不池化时代价全在建连上。

20.1.2 SQL 层：解析、优化、执行

SQL 层（server layer）不碰数据文件，只处理命令。它包含：

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 能自行决定页大小、行格式、索引结构而对优化器透明。

20.2 一条 SQL 的完整生命周期

以 SELECT * FROM t WHERE a = 1 AND b > 5 为例，逐步展开：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

 客户端
   │ ① TCP 三次握手
   ▼
 [连接层] ② 认证：用户名/密码/host 匹配 → 加载该账号权限
   │
   ▼
 [SQL 层]
   ③ 词法分析：切 token，"SELECT" "FROM" "t" 是标识符
   ④ 语法分析：按 SQL 标准文法构解析树（syntax tree）
   ⑤ 预处理：表 t 存在吗？列 a、b 存在吗？当前账号对 t 有 SELECT 权限吗？
             常量折叠：b > 5 中的常量先算完
   ⑥ 优化器：逻辑变换（外连接转内连接、子查询解嵌套、条件下推）
             → 枚举候选计划 → 代价模型比较 → 选出索引与访问路径
             产出执行计划（access path + 索引）
   ⑦ 执行器：先调引擎接口做第一次取数
             → 引擎在 B+ 树中定位 → 返回"一行"
             → 执行器对每行做条件复核（不能全信引擎的过滤）
   │
   ▼
 [存储引擎层] ⑧ 页不在 buffer pool → 从表空间文件读 16 KB 页
              返回的记录要被复制的列 → 组装成结果行
   │
   ▼
 ⑨ 结果集分批发给客户端（默认不缓存整个结果集）

每一步出问题时的现象：

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 折算成同一单位：

1.0 : 0.25 就是"内存比磁盘快 4 倍"的工程近似（真实量级差 10⁵ 倍，见第 13 章 13.1 节）。代价模型里最不可靠的一项正是回表代价：优化器假设"按索引顺序回表"是廉价的，实际上二级索引的顺序与主键顺序经常不一致，回表就退化成随机 I/O；缓冲池命中率低时误差被放大到几个数量级。

20.3.3 直方图与统计信息的补丁

MySQL 8.0 引入直方图（histogram），用来描述列值的分布，弥补基数只给"不同值个数"、不给"分布形状"的缺陷：

1
2
3
4

-- 用直方图补上"分布形状"这一维：优化器原本只知道 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 的代价

1
2
3
4
5
6
7
8
9

-- 为什么这样改写：优化器对 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';

20.4 InnoDB 与 MyISAM 的取舍

为什么现在基本只剩 InnoDB：现代后端的默认前提是"崩溃恢复不能丢已提交事务"“并发写不能锁整表”。MyISAM 的 COUNT(*) 是 O(1) 这点优势，在 InnoDB 上可以用计数表或缓存替代，而它失去的行锁与崩溃安全无法替代。Memory 引擎只在临时表、会话级中间结果这类可重建数据上有位置。

20.5 InnoDB 内存结构

20.5.1 buffer pool 的组织与分段 LRU

buffer pool（缓冲池）以页（page，16 KB）为管理单位缓存数据页与索引页。它的三层组织是：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

 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 会成为争用点，且它只对等值查询有效。因此在读写混合且热点分散的负载上，关掉它反而更稳。

20.6 InnoDB 磁盘结构：表空间到行

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

 表空间 (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 页计）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

 偏移 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）配合完成页级崩溃修复。

20.8 行格式与行溢出

四种行格式的差异集中在"变长列怎么记、长值放哪"：

COMPACT 行的完整布局：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

 ┌──────────────────┬────────────────┬──────────────────────────────┐
 │ 变长字段长度列表  │ 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。

20.9 一条 UPDATE 的完整生命周期

UPDATE t SET v = v + 1 WHERE id = 1 的执行链路（每个编号是一个可崩溃点）：

1
2
3
4
5
6
7
8
9

 ① 执行器调引擎接口，按主键定位记录（引擎接口带锁语义）
 ② 目标页不在 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

本章小结

自测题

Q1（计算）默认配置下，innodb_page_size = 16 KB，一个区有多少页、多大？若某表主键为 BIGINT（8 字节）+ 6 字节行开销，二级索引键为 INT（4 字节）+ 主键，估算一个 16 KB 叶子页能放多少条二级索引记录。

Q2（辨析）有人用 SELECT COUNT(*) FROM t 在 InnoDB 与 MyISAM 上做对比，得出"MyISAM 快得多，所以大表统计行数应该用 MyISAM"。请指出这个结论的问题，并给出 InnoDB 上的替代方案。

Q3（推导）一张 1 亿行的订单表，EXPLAIN 显示主键范围查询（覆盖全表 40% 的行）走了主键索引，结果比全表扫描还慢。请从代价模型角度解释优化器为什么"看起来选对了却慢了"，并给出两种干预方式。

Q4（设计）一张表的 id 是自增主键，业务要频繁执行 SELECT ... FROM t WHERE status = 1 ORDER BY created_at DESC LIMIT 20，status 只有 3 个取值。现在只有 status、created_at 两个单列索引，执行计划选择了 status 索引并出现 Using filesort。请解释原因并给出索引改造方案。

1
2
3
4

-- 为什么列顺序是 (status, created_at)：等值条件列必须在前，
-- 排序列在后，这样在 status = 1 这个"等值前缀段"内，created_at 天然有序，
-- 优化器就能从索引末端（DESC）反向扫描 20 条即止，完全免排序
ALTER TABLE t ADD INDEX idx_status_created (status, created_at DESC);

Q5（推导）解释：为什么 innodb_old_blocks_time 默认 1000 ms，而一次全表扫描通常在 200 ms 内就能读完 128 MB 的 buffer pool？这个参数设成 0 会发生什么？

面试高频问题与陷阱

延伸阅读

• MySQL 官方手册《MySQL Internals Manual》的 Server Layer、Handler API 与《The InnoDB Storage Engine》的 InnoDB Buffer Pool、Change Buffer、Adaptive Hash Index 两处：20.1.3 节的可插拔接口与 20.5 节全部参数的默认值口径。
• MySQL 官方手册《InnoDB Row Formats》《Overview of InnoDB Page Structure》与《Optimizer Statistics》《Cost Model》《File Space Management》：20.3、20.6、20.7、20.8 节的字段级定义与统计信息机制。
• MySQL EXPLAIN 输出格式说明与 SHOW ENGINE INNODB STATUS 的 BUFFER POOL AND MEMORY 段：观测 buffer pool 实际命中率与页状态的手段。
• 《MySQL 是怎样运行的：从根儿上理解 MySQL》：页内结构、行格式与 buffer pool 的分段 LRU 有逐字节级别的图示，可与 20.7、20.8 节对照阅读。