数据库基础体系 · 第 15/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL InnoDB 存储与索引：页、聚簇索引、回表和 Buffer Pool

在 InnoDB 中，一条查询从“找到索引项”到“返回完整行”，通常要经过多个层次：

  1. SQL 被优化器转换为访问路径；
  2. 存储引擎按索引树查找记录；
  3. 索引查找以页为基本 I/O 和缓存单位；
  4. 二级索引无法提供全部列时，根据主键再次访问聚簇索引；
  5. 所需页面可能已经在 Buffer Pool 中，也可能需要从磁盘读取；
  6. 对于修改操作，页面先在内存中改变，随后通过 redo log、刷脏页等机制持久化。

理解这条链路，才能解释以下常见问题：

  • 为什么主键过长会使所有二级索引变大？
  • 为什么“走了索引”仍然可能很慢？
  • 为什么一次查询只返回几行，却可能读取很多页？
  • 为什么随机主键插入可能导致更多页分裂？
  • 为什么 Buffer Pool 命中率高，不代表查询一定快？
  • 为什么回表次数和回表页数不是同一个概念？

下文以 MySQL 8.4、InnoDB、单机部署为主要语境。除非特别说明，示例使用默认的 16 KiB InnoDB 页大小、普通 B-tree 索引和事务表。

一、先建立整体模型：逻辑记录最终落在页上

1. 页是 InnoDB 的基本管理单位

InnoDB 不会把磁盘上的一行记录单独读入内存。它通常以 页（page） 为单位进行：

  • 磁盘读写；
  • Buffer Pool 缓存；
  • 脏页刷盘；
  • B+Tree 节点存储；
  • 页分裂与合并。

默认情况下，InnoDB 页大小为 16 KiB。innodb_page_size 可以在初始化实例时设置为其他受支持的值，但它不是普通运行时参数，不能在实例创建后随意修改。实际可用值受版本和平台限制，常见范围包括 4 KiB、8 KiB、16 KiB、32 KiB 和 64 KiB。

因此，下面这个直觉很重要：

SQL 以行为单位表达，索引以记录为单位组织，但存储引擎以页为单位缓存和读写。

一页中通常包含：

  • 页头和页尾；
  • 页面管理信息；
  • 多条 InnoDB 记录；
  • 页目录；
  • 对于 B+Tree 节点，还包含指向子页的记录。

页的实际可用空间小于 16 KiB，因为需要扣除页元数据和记录管理开销。

2. B+Tree 中的页是什么角色

InnoDB 的普通索引采用 B+Tree 结构。可以抽象为：

                    根页
                  /      \
              中间页    中间页
             /    \      /    \
          叶子页  叶子页 叶子页  叶子页

在这个结构中：

  • 根节点和中间节点保存键值范围以及子页位置；
  • 叶子节点保存实际索引记录；
  • 叶子页按索引顺序组织；
  • 相邻叶子页通常通过页链表连接，便于范围扫描。

一次索引查找大致经过：

根页
  ↓
中间页
  ↓
叶子页
  ↓
目标索引记录

如果树高为 h，一次点查大致需要访问 h 个索引层级的页。实际访问次数还受到以下因素影响：

  • 某些页已经在 Buffer Pool 中；
  • 根页和部分中间页通常比较热；
  • 页预读；
  • 访问模式是否为顺序范围扫描；
  • 并发情况下是否发生锁等待或资源竞争。

B+Tree 的价值不只是“查找复杂度低”。它还把大量有序记录分布在固定大小的页中，使磁盘 I/O、缓存和范围访问可以围绕页工作。

二、InnoDB 记录、索引键与主键

理解聚簇索引前，需要区分三个概念：

  • 用户记录：表中的一行数据；
  • 索引记录：某个索引中的键以及与查找相关的附加信息；
  • 主键：表定义中用于唯一标识记录的索引键。

一个表可以有多个索引，但 InnoDB 只能有一个聚簇索引。通常，聚簇索引就是主键索引。

1. InnoDB 优先如何选择聚簇索引

对于 InnoDB 表，聚簇索引的选择顺序通常是：

  1. 如果定义了 PRIMARY KEY，使用主键作为聚簇索引；
  2. 否则选择第一个满足唯一且所有列都为 NOT NULL 的唯一索引；
  3. 如果两者都不存在，InnoDB 创建隐藏的 6 字节行 ID，并以此建立隐藏聚簇索引。

因此，下面三种表的物理组织不同：

CREATE TABLE t_pk (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

id 是聚簇索引键。

CREATE TABLE t_unique (
    code CHAR(16) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

code 会被用作聚簇索引键。

CREATE TABLE t_no_key (
    name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

InnoDB 会使用内部隐藏行 ID。这个隐藏 ID 对用户不可直接查询，也不适合作为业务上的稳定标识。

工程上通常应显式定义主键，但这不是因为 InnoDB 没有默认行为，而是因为显式主键能让：

  • 行定位语义明确；
  • 外键和业务关联更清晰；
  • 二级索引中携带的定位键可控；
  • 复制、数据迁移、分页和诊断更容易进行。

2. 聚簇索引叶子页保存什么

聚簇索引的叶子页保存的是“整行记录”，更准确地说，是 InnoDB 管理的行记录及其内部字段。

抽象表示：

聚簇索引叶子页：

主键  →  该主键对应的整行数据
101   →  (101, 'Alice', 30, ...)
102   →  (102, 'Bob',   28, ...)
103   →  (103, 'Carol', 35, ...)

所以：

SELECT *
FROM user
WHERE id = 102;

如果 id 是主键，这次查询沿着聚簇索引找到 id = 102 的叶子记录后，已经获得整行数据，不需要再查另一个“表数据结构”。

这就是为什么在 InnoDB 中：

聚簇索引本身就是表数据的主要物理组织。

“表”和“主键索引”在这里不是两个彼此独立的完整数据副本。

三、聚簇索引与二级索引：为什么二级索引叶子节点包含主键

1. 二级索引保存的是索引键和行定位信息

除聚簇索引之外的索引称为 二级索引（secondary index）。

例如：

CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(50) NOT NULL,
    KEY idx_city_age (city, age)
) ENGINE = InnoDB;

二级索引 idx_city_age 的叶子记录可以抽象为：

(city, age)  →  primary key

(Beijing, 20) → 101
(Beijing, 30) → 108
(Shanghai, 25) → 115

这里的 101、108、115 是主键值。它们不是完整行数据，而是继续定位聚簇索引记录所需的信息。

因此，一条通过二级索引执行的查询可能经历两阶段：

二级索引：
(city = 'Beijing', age = 30)
                ↓
             得到 id = 108

聚簇索引：
主键 id = 108
                ↓
             得到整行数据

第二次根据主键访问聚簇索引的过程，通常被称为 回表。

2. 回表不是“回到磁盘”

“回表”是执行路径概念，不等价于“发生磁盘 I/O”。

二级索引找到主键后，存储引擎会访问聚簇索引对应的页：

  • 如果聚簇索引页已经在 Buffer Pool 中，回表可能只涉及内存访问；
  • 如果不在 Buffer Pool 中，需要从磁盘读取该页；
  • 如果多个主键记录位于同一个聚簇索引页，多个回表可能共享一次页读取；
  • 如果访问的主键分布很随机，可能触发许多不同页的读取。

所以应区分：

  • 回表记录数：需要根据二级索引结果继续取整行的记录数；
  • 回表页数：实际需要访问的聚簇索引页数量；
  • 回表磁盘 I/O 次数：其中不在缓存而实际从存储设备读取的次数。

三者通常不相等。

3. 一个完整例子

创建测试表：

CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_city_age (city, age)
) ENGINE = InnoDB;

查询一：

SELECT id, age
FROM user
WHERE city = 'Beijing' AND age = 30;

idx_city_age 中已经包含：

  • city；
  • age；
  • 主键 id。

因此，查询需要的列都能从二级索引记录中获得。这种访问称为 覆盖索引（covering index），通常不需要回表。

查询二：

SELECT id, name, age
FROM user
WHERE city = 'Beijing' AND age = 30;

二级索引能提供：

  • id；
  • age；
  • city。

但不能提供 name。执行过程可能是：

1. 在 idx_city_age 中定位 city = 'Beijing' 且 age = 30 的索引记录；
2. 从每条二级索引记录中取得主键 id；
3. 根据 id 查找聚簇索引；
4. 从聚簇索引记录中读取 name；
5. 组装结果。

这就是回表。

4. 覆盖索引降低了什么成本

覆盖索引的主要收益是减少二级索引到聚簇索引的访问，不一定意味着“完全不读数据页”。它仍然需要读取二级索引叶子页，只是避免了后续的聚簇索引访问。

例如：

CREATE INDEX idx_city_age_name
ON user (city, age, name);

此后：

SELECT name
FROM user
WHERE city = 'Beijing' AND age = 30;

可能只扫描 idx_city_age_name。但覆盖索引也会带来代价：

  • 索引占用更多空间；
  • 插入和更新需要维护更多索引内容；
  • name 修改时可能产生更多写放大；
  • 更大的索引可能降低缓存有效性；
  • 长字符串列可能显著降低单页可容纳的记录数。

因此，覆盖索引不是“把所有查询列都塞进索引”的通用规则，而是读成本、写成本、空间成本之间的取舍。

四、回表的成本：从记录数量推导到页访问

假设二级索引过滤后得到 N 条记录，记：

  • N：二级索引匹配记录数；
  • P：这些记录对应的聚簇索引页数量；
  • H：其中已经在 Buffer Pool 中的聚簇索引页数量；
  • D：其中需要实际从磁盘读取的聚簇索引页数量。

则有：

D ≤ P ≤ N

并且：

D = P - H

这里是简化模型，实际还会受到预读、并发、淘汰和异步 I/O 的影响。

1. 连续主键访问的情况

如果二级索引结果对应的主键大致连续：

id = 1001, 1002, 1003, ..., 1100

这些记录可能集中在较少的聚簇索引页中。即使有 100 条回表记录，P 也可能远小于 100。

2. 随机主键访问的情况

如果二级索引结果对应的主键分布在全表各处：

id = 7, 98321, 401, 765432, ...

这些记录可能分布在大量不同的聚簇索引页上，P 接近 N 的可能性会增加。

这解释了一个常见现象：

同样是“二级索引命中 1000 行”，顺序分布和随机分布的执行成本可能完全不同。

3. 低选择性索引为什么可能变慢

假设：

SELECT name
FROM user
WHERE city = 'Beijing';

如果 city = 'Beijing' 匹配了表中很大比例的行，那么执行器有两种主要选择：

  1. 扫描二级索引，再大量回表；
  2. 直接扫描聚簇索引。

当需要回表的记录很多、分布又很随机时，二级索引的收益会下降，优化器可能选择全表扫描。这个判断由统计信息、成本模型、索引结构和估算基数共同影响，而不是由“有索引”这一事实决定。

五、复合索引如何决定查找范围

复合索引 (city, age) 按如下顺序排序：

(city, age)

先比较 city，只有 city 相同才比较 age。这就是联合索引的 最左前缀 结构。

1. 可以形成连续范围的条件

WHERE city = 'Beijing' AND age = 30

可以定位到：

(city = 'Beijing', age = 30)

的连续索引范围。

WHERE city = 'Beijing'

可以扫描所有：

(city = 'Beijing', 任意 age)

的连续范围。

2. 中间列出现范围条件后的限制

考虑索引：

KEY idx_a_b_c (a, b, c)

查询：

WHERE a = 10 AND b > 20 AND c = 30

索引可以先利用：

a = 10
b > 20

定位和扫描范围，但 c = 30 通常不能继续把 B+Tree 的扫描范围缩小到单一连续区间。它可能作为索引条件过滤或存储引擎层面的额外判断，但不能简单理解为“完整使用了 a、b、c 三列进行有序定位”。

这是“使用了索引列”和“使用索引缩小了搜索范围”的区别。

3. 失去索引顺序的例子

WHERE age = 30

对于 (city, age)，不能直接按 age 在整个索引中形成一个连续范围，因为所有城市的 age = 30 记录交错分布在索引中。

优化器仍可能出于覆盖索引、索引扫描成本等原因选择扫描这个索引，但这不等于按照 age 高效查找。

六、页内记录组织与页分裂

1. 页不是简单的数组

InnoDB 页内的记录并不只是从偏移量 0 到偏移量末尾的紧密数组。页中包含记录管理信息和页目录，记录之间通过内部结构组织。

因此，插入一条新记录通常不是简单地“在文件末尾追加一行”。存储引擎需要：

  1. 找到目标索引叶子页；
  2. 判断该页是否有足够空间；
  3. 如果有空间，将记录插入有序位置；
  4. 如果没有足够空间，执行页分裂；
  5. 更新父节点中的键范围和子页引用；
  6. 必要时继续向上分裂；
  7. 记录相关 redo log，并将修改后的页标记为脏页。

2. 页分裂的抽象过程

假设某个叶子页按主键排序：

[10, 20, 30, 40, 50]

插入 35 时，如果页空间足够：

[10, 20, 30, 35, 40, 50]

如果空间不足，可能分裂为：

左页:  [10, 20, 30]
右页:  [35, 40, 50]

然后父节点增加一个指向右页的分隔键。真实实现会考虑记录大小、页目录、空间利用率和内部页结构，不能把“平均一半”当作精确规范。

3. 为什么随机主键更容易造成结构性成本

顺序递增主键的插入通常集中在索引右侧的新页或末端页附近。随机主键则可能把插入分散到整个 B+Tree：

  • 更多已有页被修改；
  • 更容易遇到目标页空间不足；
  • 可能增加页分裂；
  • 新记录在物理存储上的局部性通常更差；
  • 需要读取和维护更多不同的数据页。

但“递增主键一定最好”也不是绝对结论。主键还必须满足：

  • 业务语义是否需要；
  • 是否需要跨节点生成；
  • 是否存在批量导入和删除；
  • 数据分布是否导致热点；
  • 主键宽度是否可接受。

例如，UUID 等随机值如果直接作为较宽的聚簇主键，除了插入局部性问题，还会扩大所有二级索引，因为二级索引需要携带主键值。

七、主键宽度会放大所有二级索引

考虑：

CREATE TABLE order_item (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    KEY idx_order (order_id),
    KEY idx_sku (sku_id)
) ENGINE = InnoDB;

抽象地看：

idx_order 叶子记录 = order_id + id
idx_sku   叶子记录 = sku_id   + id

如果把主键从 8 字节整数换成 36 字符字符串，二级索引的每条叶子记录都可能增加相应的主键存储开销。结果包括：

  • 单页能容纳的二级索引记录减少；
  • 二级索引树可能变高；
  • 索引占用空间增加；
  • Buffer Pool 能缓存的索引记录数量减少；
  • 插入、更新和维护成本上升。

实际记录大小还包括变长字段、NULL 标记、长度信息、记录头和页结构开销，因此不能只用“列定义长度相加”精确计算页容量。但方向是明确的：

聚簇主键不仅影响聚簇索引，也会通过二级索引中的主键副本影响整个表的索引体系。

八、Buffer Pool：InnoDB 的页缓存与修改工作区

1. Buffer Pool 保存什么

Buffer Pool 是 InnoDB 在内存中管理的一组页框，用于缓存从磁盘读取的 InnoDB 页。缓存对象可以包括：

  • 聚簇索引页；
  • 二级索引页；
  • 段和区相关页面；
  • undo 相关页面；
  • 插入缓冲或 change buffer 相关页面；
  • 其他 InnoDB 管理页。

查询读取某个索引页时，InnoDB 首先检查该页是否已在 Buffer Pool：

页在 Buffer Pool 中
    ↓
直接使用内存中的页

页不在 Buffer Pool 中
    ↓
从表空间读取页
    ↓
放入 Buffer Pool
    ↓
执行访问

Buffer Pool 的缓存单位是页，不是单条记录。因此，读取某一行通常意味着把包含该行的整页加载到内存。

2. Buffer Pool 不是查询结果缓存

Buffer Pool 缓存的是 InnoDB 页，不是：

SQL 文本 → 查询结果

下面两次查询可能命中同一个索引页：

SELECT name FROM user WHERE id = 101;
SELECT name FROM user WHERE id = 102;

但它们的结果不同，Buffer Pool 也不会因为第一次查询返回了某个结果，就直接缓存第二次 SQL 的结果。

此外，MySQL 8.0 已移除传统 Query Cache，不能把 Buffer Pool 和 Query Cache 混为一谈。

3. Buffer Pool 命中和查询快慢

Buffer Pool 命中通常减少磁盘 I/O，但它不能独立决定查询耗时。查询仍可能受以下因素影响：

  • 需要扫描的页数量；
  • CPU 过滤和表达式计算；
  • 排序、聚合和临时表；
  • 锁等待；
  • 并发争用；
  • redo log 写入；
  • 网络传输；
  • 结果集大小。

例如，一条查询命中率很高，但扫描了几百万个内存页，仍然可能很慢。

九、Buffer Pool 的页面状态与淘汰

1. 干净页和脏页

Buffer Pool 中的页至少可以从修改状态上分为：

  • 干净页（clean page）：内存内容与磁盘内容一致；
  • 脏页（dirty page）：内存中的内容已经被修改，但磁盘上的旧页尚未更新。

更新语句的抽象过程是：

磁盘页
  ↓ 读入
Buffer Pool 中的页
  ↓ 修改
脏页
  ↓ 刷盘
磁盘上的新页

脏页不能像干净页一样直接丢弃。如果要淘汰脏页，必须先将其写回磁盘，或者等待其他刷新机制完成。

2. 页面淘汰不是简单的“最久未使用”

InnoDB 使用带有冷热区域思想的 LRU 类机制，而不是一个完全朴素的“最近访问顺序列表”。其中一个重要目的，是避免大范围扫描把大量一次性访问页面全部推入热区，从而淘汰真正频繁使用的页。

还需要注意：

  • Buffer Pool 可以划分为多个实例；
  • 页面访问存在并发保护；
  • 读请求可能触发预读；
  • 页面淘汰受脏页刷盘和内存压力影响；
  • 具体内部策略属于实现细节，不能把某个版本的内部链表行为当作 SQL 层规范保证。

3. 预读可能有帮助，也可能浪费

当 InnoDB 判断访问具有顺序性或范围性时，可能预先读取相邻页。预读的目标是把“很可能马上使用的页”提前放入 Buffer Pool。

如果后续确实顺序扫描，预读可以降低等待；如果查询很快结束或访问模式并不连续，则可能：

  • 读取了实际不会使用的页；
  • 占用 I/O 带宽；
  • 挤出其他热页。

所以不能简单认为“预读越多越好”。

十、从查询到页访问：一个端到端示例

创建并填充示例表：

CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_city_age (city, age)
) ENGINE = InnoDB;

INSERT INTO user (id, name, age, city, created_at) VALUES
(101, 'Alice', 30, 'Beijing',  '2024-01-01 10:00:00'),
(102, 'Bob',   30, 'Beijing',  '2024-01-02 10:00:00'),
(103, 'Carol', 28, 'Shanghai', '2024-01-03 10:00:00');

1. 覆盖索引查询

EXPLAIN ANALYZE
SELECT id, age
FROM user
WHERE city = 'Beijing' AND age = 30;

该查询需要的列全部在 idx_city_age 中：

  • city 和 age 用于定位；
  • id 作为 InnoDB 二级索引记录中的主键值可用于返回。

因此，执行路径可能是：

idx_city_age 根页
    ↓
idx_city_age 叶子页
    ↓
直接返回 id、age

不会因为“查询的是表”就自动访问聚簇索引。是否回表取决于二级索引是否包含查询所需的列，以及执行计划和存储引擎实现。

EXPLAIN ANALYZE 会实际执行语句，并显示各执行节点的实际行数和耗时。它适合验证“估算与实际是否偏离”，但对写操作不能随意使用，因为实际执行可能改变数据或产生锁影响。

2. 需要回表的查询

EXPLAIN ANALYZE
SELECT id, name, age
FROM user
WHERE city = 'Beijing' AND age = 30;

name 不在 idx_city_age 中，可能的路径是：

1. 读取 idx_city_age 的根页和中间页；
2. 扫描满足 city = 'Beijing'、age = 30 的叶子范围；
3. 对每条匹配记录取得主键 id；
4. 访问聚簇索引中 id = 101、102 的记录；
5. 读取 name 和其他需要的列；
6. 返回结果。

如果 id = 101 和 id = 102 位于同一个聚簇索引页，那么两次回表可能共享该页；如果它们分散在不同页，访问成本会更高。

3. 验证计划时要看什么

EXPLAIN 的输出中，常见关注点包括：

  • key：实际选择的索引；
  • key_len：可能使用的索引键长度；
  • type：访问类型；
  • rows：优化器估算需要检查的行数；
  • filtered：估算过滤比例；
  • Extra：是否出现覆盖索引、范围条件、排序或临时表等信息。

但 EXPLAIN 只是优化器的估算。诊断回表成本时，尤其要比较：

估算行数 vs 实际行数
估算过滤率 vs 实际过滤率
索引扫描量 vs 最终返回量

EXPLAIN ANALYZE 可以提供实际执行信息，但它本身也会执行查询，并且测量结果会受到缓存、并发和数据分布影响。

十一、修改操作：Buffer Pool、redo log 与刷脏页

查询主要关心“页是否可用”；更新还必须处理“修改如何持久化”。

执行：

UPDATE user
SET age = 31
WHERE id = 101;

抽象过程如下。

第一步：定位聚簇索引记录

id 是主键，因此 InnoDB 通过聚簇索引找到包含 id = 101 的页。

如果该页不在 Buffer Pool，先读入内存。

第二步：在内存页中修改记录

Buffer Pool 中的页面被修改：

age: 30 → 31

该页变成脏页。

第三步：生成 redo log

InnoDB 记录能够重做该修改所需的 redo log。redo log 记录的是面向存储引擎恢复的物理或偏物理变更信息，作用是：

  • 事务提交时提供持久化路径；
  • 崩溃后重放已记录但未刷入数据页的修改；
  • 允许数据页晚于日志落盘。

这遵循常见的 Write-Ahead Logging 思想：相关日志需要先于依赖它恢复的数据页持久化。

第四步：事务提交

提交是否在数据库进程崩溃、操作系统崩溃或主机断电后仍然保留，受到以下因素影响：

  • innodb_flush_log_at_trx_commit；
  • 操作系统和存储设备对 fsync 等操作的实际保证；
  • 存储设备是否具备可靠的持久化缓存；
  • 事务是否真正提交；
  • 复制场景中的 binlog 刷盘与提交配置。

因此，“事务提交成功”与“数据页立刻写回表空间”不是同一件事。正常情况下，提交不要求每个被修改的数据页都立即刷盘；只要恢复所需日志已经按配置持久化，崩溃恢复可以重做这些修改。

第五步：后台刷脏页

后台线程随后把脏页刷回表空间。刷脏页可能由以下因素触发：

  • Buffer Pool 空间压力；
  • checkpoint 推进；
  • 脏页比例；
  • 后台刷新策略；
  • 正常关闭；
  • 系统 I/O 调度。

如果 redo log 可用空间不足或脏页积累过多，前台事务可能受到反压，表现为写入变慢。

十二、崩溃恢复路径：为什么数据页可以晚于日志

设某个事务完成了：

1. 数据页在 Buffer Pool 中修改；
2. redo log 已写入并按提交配置持久化；
3. 数据页还没有刷入表空间；
4. 服务器突然崩溃。

重启后，InnoDB 会执行恢复流程，核心思想是：

读取已有数据页
    ↓
根据 redo log 找到已记录的修改
    ↓
重做必要变更
    ↓
恢复到日志所描述的状态

这意味着 Buffer Pool 不是持久化介质，脏页丢失本身不一定导致已提交事务丢失；关键在于恢复所需的 redo log 是否可靠持久化，以及配置和硬件是否满足相应保证。

反过来，如果日志尚未按要求持久化，发生主机断电时，事务提交后的修改可能无法恢复。这是为什么不能只看应用收到“提交成功”，而忽略数据库和存储设备的持久化配置。

十三、MVCC 与“读到的行”并不总是当前页中的最新版本

InnoDB 的普通一致性读通常使用 MVCC。MVCC 的目标是在事务隔离语义下，让读操作获得一个符合当前读视图的数据版本，而不是简单地把 Buffer Pool 中当前最新字节直接返回。

1. 记录中的事务信息和 undo

InnoDB 记录包含与事务可见性相关的内部信息。更新记录时，旧版本信息会通过 undo 机制保留下来，形成版本链的逻辑结构。

抽象表示：

当前记录版本：age = 31
        ↓ undo
旧记录版本：age = 30

某个事务读取记录时，InnoDB 会根据：

  • 当前事务的 Read View；
  • 记录版本的事务信息；
  • undo 版本链；

判断哪个版本对当前事务可见。

2. 一致性读与当前读

普通 SELECT 通常是一致性读。例如：

SELECT age
FROM user
WHERE id = 101;

在默认隔离级别和具体事务状态下，它可能读取符合 Read View 的历史版本，而不一定是数据库中最新提交的版本。

锁定读和修改语句属于当前读范畴，例如：

SELECT age
FROM user
WHERE id = 101
FOR UPDATE;

当前读需要面向当前版本处理锁和并发修改，其行为不能用普通一致性读的规则替代。

因此，“回表读取聚簇索引”并不简单等价于“从聚簇索引叶子页取出一份当前字节”。在存在并发更新和历史版本时，InnoDB 还可能需要访问 undo 相关信息来构造对当前事务可见的版本。

十四、二级索引与 MVCC：为什么可能还要检查聚簇记录

二级索引叶子记录主要保存索引列和主键定位信息，不是完整的事务版本记录副本。执行二级索引访问时，InnoDB 需要结合聚簇记录中的事务信息判断可见性。

因此，某些一致性读即使从二级索引中能得到查询列，也不应机械地认为“只要覆盖索引就完全不需要聚簇索引相关检查”。是否需要进一步访问、访问到什么程度，取决于：

  • 查询是否为覆盖索引；
  • 记录是否可见；
  • 是否存在并发更新；
  • 是否需要从 undo 构造旧版本；
  • 存储引擎和执行器的具体访问路径。

在没有并发修改、数据版本简单的场景中，覆盖索引通常可以显著减少聚簇索引访问；在高并发更新场景中，实际路径可能更复杂。

十五、长事务为什么会影响存储和 Buffer Pool

长事务会延迟某些历史版本的清理，因为仍可能有事务需要访问这些版本。由此可能产生：

  • undo 历史积累；
  • purge 延迟；
  • 表空间增长；
  • 读取旧版本时额外的版本链访问；
  • 写入和后台清理压力增加。

一个常见误解是：

只要查询本身是只读，就不会影响系统写入。

长时间运行的一致性读可能保持较老的 Read View，使旧版本不能及时清理。它未必直接锁住所有数据，但会影响 MVCC 版本回收和后台 purge。

诊断时可以结合：

SHOW ENGINE INNODB STATUS\G

查看 InnoDB 的事务、锁、历史列表和 I/O 等信息。不同版本输出细节可能变化，诊断时应关注现场数据，而不是把某个字段的单次值当成固定阈值。

十六、change buffer 与二级索引写入

对于不在 Buffer Pool 中的某些二级索引页，InnoDB 可以在满足条件时使用 change buffer 暂存部分二级索引修改，等页面以后被读入时再合并。它的目标是减少对冷二级索引页的立即随机 I/O。

需要注意几个边界：

  • change buffer 主要针对二级索引，不用于聚簇索引；
  • 不是所有二级索引修改都可以延迟合并；
  • 唯一索引需要检查唯一性，通常不能简单绕过相关页面访问；
  • 这不是应用可见的“索引最终一致性”，事务语义仍由 InnoDB 保证；
  • 合并工作会在后台或页面访问时发生，成本只是被推迟，不是消失。

因此，插入性能、后台合并压力、二级索引数量和 Buffer Pool 工作集之间存在联系。不能只看到前台写入暂时变快，就认为系统总成本降低了。

十七、页压缩、行格式与“16 KiB”边界

默认页大小是 16 KiB，但实际部署中可能使用：

  • 不同的实例页大小；
  • ROW_FORMAT=COMPRESSED；
  • 表空间压缩；
  • 页面压缩；
  • 外部存储的大字段页。

这些能力会改变磁盘占用、I/O 行为和页中记录布局，但不会改变本文的基本模型：

索引记录组织在 B+Tree 页中
Buffer Pool 以页为单位缓存
聚簇索引叶子页保存整行
二级索引通常通过主键定位聚簇记录

对于 TEXT、BLOB、较长的 VARCHAR 等字段，部分内容可能不直接放在记录的主要位置，而是通过溢出页等结构管理。于是：

查询命中聚簇索引页
    ↓
如果还需要读取长字段
    ↓
可能继续访问相关溢出页

所以“只回表一次”也不必然意味着只读一个物理页。

十八、如何诊断“走索引但仍然很慢”

应将问题拆成几层，而不是只问“有没有索引”。

1. 先确认访问路径

EXPLAIN
SELECT id, name, age
FROM user
WHERE city = 'Beijing' AND age = 30;

关注：

  • 是否使用预期索引；
  • 估算扫描行数是否过大；
  • 是否存在额外排序或临时表；
  • 是否因为条件形式导致索引范围不理想。

2. 再比较实际行数

EXPLAIN ANALYZE
SELECT id, name, age
FROM user
WHERE city = 'Beijing' AND age = 30;

如果优化器估算返回几十行，实际却返回几十万行，问题可能在：

  • 统计信息过旧；
  • 数据分布严重倾斜；
  • 多列相关性未被准确估计；
  • 谓词选择性发生变化。

这时不应直接通过强制索引解决。强制索引可能暂时改变计划，却掩盖估算问题，并在数据分布变化后变得更差。

3. 判断是否发生大量回表

典型表现是：

  • 二级索引扫描行数较多；
  • 最终返回行数较少；
  • 查询列没有被索引覆盖；
  • 主键值分布随机；
  • 磁盘读取或 Buffer Pool 淘汰明显。

可以分别测试：

-- 可能覆盖索引
SELECT id, age
FROM user
WHERE city = 'Beijing' AND age = 30;

-- 需要 name，可能回表
SELECT id, name, age
FROM user
WHERE city = 'Beijing' AND age = 30;

两者的差异有助于判断回表是否是主要成本来源，但测试必须控制：

  • 同一数据集；
  • 相同事务和隔离级别；
  • 相近缓存状态；
  • 相同并发条件；
  • 相同参数分布。

4. 判断是 CPU、锁还是 I/O

如果数据几乎都在 Buffer Pool 中，查询仍然慢，可能不是磁盘 I/O，而是：

  • 扫描页太多；
  • CPU 过滤成本高；
  • 排序或聚合成本高；
  • 锁等待；
  • 并发争用；
  • 返回结果太大。

反之，查询第一次执行慢、随后执行快，可能与页面从磁盘加载到 Buffer Pool 有关，但也不能仅凭两次执行时间断定，因为操作系统缓存、后台 I/O 和并发都会影响结果。

十九、常见误解与反例

误解一：主键索引和数据表是两个完整副本

对 InnoDB 来说，聚簇索引叶子页保存整行数据。主键索引并不是在完整表之外额外复制一份整表数据。

正确模型是：

聚簇索引叶子页 = 表记录的主要存储位置
二级索引叶子页 = 二级索引列 + 主键定位信息

误解二：使用二级索引就一定要回表

反例：

SELECT id, age
FROM user
WHERE city = 'Beijing' AND age = 30;

如果索引是 (city, age)，且主键 id 可由二级索引记录提供，该查询可能覆盖索引，不需要读取整行聚簇记录。

误解三：回表次数等于磁盘 I/O 次数

反例：

二级索引匹配 1000 行
其中 1000 个主键记录位于 20 个聚簇索引页
且这 20 个页都已在 Buffer Pool 中

此时可以有 1000 次逻辑上的行定位，但不需要 1000 次磁盘读，甚至可能没有磁盘读。

误解四：Buffer Pool 越大，所有查询都会越快

Buffer Pool 较大通常有助于缓存更大的工作集，但收益受以下因素限制：

  • 工作集是否真的被重复访问；
  • 查询是否扫描过多页面；
  • 内存是否导致操作系统或其他组件压力；
  • 访问是否主要是一次性大范围扫描；
  • 瓶颈是否其实在锁、CPU、排序或网络。

误解五：索引中的列越多越好

覆盖更多列可能减少回表，但会增加：

  • 页大小和树高度；
  • 写入维护成本；
  • Buffer Pool 占用；
  • 索引更新和存储空间；
  • 长字段带来的溢出页或压缩复杂度。

索引设计需要围绕真实查询的过滤、连接、排序、返回列和写入模式进行评估。

误解六：事务提交后数据页必须立刻落盘

InnoDB 可以先持久化 redo log，再延迟刷数据页。崩溃恢复时通过 redo log 重做修改。事务提交持久性和数据页即时刷盘是不同概念。

但这并不意味着可以忽略：

  • innodb_flush_log_at_trx_commit；
  • binlog 刷盘配置；
  • 存储设备的持久化保证；
  • 崩溃和复制故障场景。

二十、把四个核心概念串起来

以查询：

SELECT name
FROM user
WHERE city = 'Beijing' AND age = 30;

为例，完整的可能路径是：

SQL
 ↓
优化器选择 idx_city_age
 ↓
访问二级索引 B+Tree
 ↓
根页、中间页、叶子页
 ↓
得到若干主键 id
 ↓
根据主键访问聚簇索引
 ↓
读取包含完整行的聚簇索引叶子页
 ↓
必要时结合 undo 判断可见版本
 ↓
返回 name

每一步和本文主题的对应关系是：

  • 页：B+Tree 节点和 InnoDB I/O、缓存的基本单位；
  • 聚簇索引：按主键组织整行记录的索引；
  • 回表：从二级索引取得主键后，再访问聚簇索引取整行；
  • Buffer Pool：缓存这些索引页和数据页，并承载脏页修改；
  • redo log：使内存页修改能够在崩溃后恢复；
  • MVCC/undo：使读取结果符合事务可见性语义。

最终，索引优化不能只停留在“是否命中索引”这一层。更准确的问题是：

选择了哪棵树？
扫描了多少索引页？
匹配了多少索引记录？
是否需要回表？
回表记录分布在哪些聚簇索引页？
这些页是否在 Buffer Pool 中？
是否存在版本链、锁等待、排序或聚合成本？

只有把 SQL、B+Tree、页、聚簇索引、回表和 Buffer Pool 放在同一条数据流中分析，才能正确解释 InnoDB 查询和写入的实际成本。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。