两条 SQL 看起来一样:都是按 customer_id 查最近 10 笔订单。一条在压测里稳定 2ms,另一条在另一个环境要 200ms。索引也都有,字段也一样。差别在哪?一条只需要 id、amount、created_at,另一条多查了一个 remark;一条的索引是 (customer_id, created_at),另一条的索引顺序反了。

这类问题没法靠「多试几个写法」解决,得知道索引在磁盘上长什么样、一次查询要读几个页、回表到底贵在哪。这篇从磁盘页讲起,把 B+ 树、聚簇索引、二级索引、回表和页分裂串成一条线。

场景走查:多查一列,慢了 100 倍

订单表在演示库里有 1998 行,数据量小,两种写法都是毫秒级,看不出差别。但把数据量放大到千万级,差别会被放大:

-- 写法 A:只查索引覆盖的列
SELECT customer_id, created_at FROM orders WHERE customer_id = 7;

-- 写法 B:多查一个不在索引里的列
SELECT customer_id, created_at, remark FROM orders WHERE customer_id = 7;

A 可以只读索引,20 行数据在索引叶子节点上连续放着,一次顺序扫描就够;B 要先在索引里找到 20 个主键,再拿主键回主键索引取整行,20 次随机读。单次随机读在内存里可能只要几十纳秒,但一旦落到磁盘,就是毫秒级——这就是 2ms 和 200ms 的来源。

磁盘不是按字节读的,是按页

数据库和操作系统打交道的最小单位是页(page),InnoDB 默认一页 16KB。你读一行 100 字节的记录,磁盘也会把整页 16KB 读进内存。顺序读一页和随机读一页,机械盘上差两个数量级,SSD 上也有明显差距。

所以索引设计的第一目标是:让一次查询尽量少读页,并且尽量顺序读。这解释了后面所有结论:为什么树要矮胖、为什么主键要短、为什么顺序插入比随机插入快、为什么覆盖索引能省一大截。

一次索引查询的路径
根页(常驻内存)
非叶页定位区间
叶子页读到主键
回表取整行

为什么不是红黑树,也不是哈希

红黑树是内存里的结构,每个节点只存一个键,两个孩子。数据量到千万级,树高约 20 多层,每下一层就是一次新的页读取,20 多次随机 IO 直接把查询拖垮。B+ 树把「一个节点」放大成一整页,一页能放几百上千个键,树高降到 3 到 4 层,读 3 个页就能定位到千万行里的任意一行。

哈希索引能做到 O(1) 等值查找,听起来更快,但它只支持「等于」,不支持范围、排序、前缀匹配。WHERE created_at > ...、ORDER BY、LIKE 'abc%' 全都用不上。业务查询大多带范围和时间排序,所以主索引选 B+ 树。InnoDB 另有自适应哈希索引(AHI),是运行期自动建的辅助结构,只加速热点等值查询,不能替代 B+ 树。

为什么不选 B 树?B 树的非叶节点也存数据行,每个节点能放的键更少,扇出变小,同样数据量树会更高。B+ 树把数据全部放到叶子层,非叶只存键和指针,扇出大、树矮;代价是等值查询也必须走到叶子,多下一层,但换来的是更少的层数和更好的范围性能。

B+ 树的形状

根页:只存少量键和指向下一层的指针,通常常驻内存
非叶页:存「键 + 子页指针」,负责把查询范围缩小
叶子页:存「键 + 数据(或主键)」,页与页之间用链表串起来

B+ 树有两个关键特征。第一,所有数据都在叶子层,非叶层只做导航,所以每次查询都要走到叶子,路径长度稳定。第二,叶子页之间是双向链表,范围查询找到起点后可以顺着链表扫,不用回到根节点。这就是 BETWEEN、>、ORDER BY 能高效的原因。

按 16KB 一页、每行 100 字节估算,一个叶子页能放约 160 行;非叶页如果存 8 字节主键加 6 字节指针,一页能放上千个条目。三层树大约能覆盖「上千 × 上千 × 160」量级的数据,千万行通常只需要三层。这是数量级估算,实际取决于行宽、页填充率和键长度。

叶子页之间的双向链表是范围查询的加速器。找到起点后,顺着链表读下一页,不用每页都从根重新下降。这也是连续区间比一堆离散值更容易预测的原因:BETWEEN 可以顺着叶子读,IN (1, 500, 9000) 要多次定位。范围太大时,链表遍历会退化成顺序扫描,返回行数越多,越接近全表。

页填充率会直接影响树高。理想情况每页都写满,实际 InnoDB 页通常填到 50% 到 90%,因为分裂和删除会留下空隙。按 70% 估算,上面的容量打七折,三层树能覆盖的行数仍然远超千万级。这说明只要主键不太宽,B+ 树对「千万到亿级」都够用;真到放不下时,问题往往在单表行数、索引数量和内存,而不是树的层数。

聚簇索引与二级索引:两棵树

InnoDB 的主键索引叫聚簇索引(clustered index)。它的叶子节点存的是完整行数据,也就是说「表本身就是按主键组织的一棵 B+ 树」。没有主键时,InnoDB 会选一个唯一非空索引代替,都没有就生成隐藏的 row id。

二级索引(也叫辅助索引)是另外一棵 B+ 树,叶子节点存「索引列 + 主键值」,不存整行。给 customer_id 建索引,叶子节点存的是 customer_id 和对应的主键 id。查 SELECT * FROM orders WHERE customer_id = 7 时,先在二级索引找到一批主键,再拿主键去聚簇索引取整行,这就是回表。

这带来两个直接后果。第一,主键越短,二级索引的叶子越小,同样的页能放更多条目,树更矮、缓存命中率更高。第二,主键最好有序,否则二级索引里的主键值也乱,回表时随机跳。

回表为什么贵

回表本身是一次「按主键查聚簇索引」,走的是树,理论复杂度不高。贵在两点:一是次数,返回 1000 行就要回表 1000 次;二是随机性,主键顺序和二级索引的顺序通常不一致,每次都可能跳到不同的页。

InnoDB 用 Buffer Pool 缓存页,热点数据能命中内存,回表就快;如果 Buffer Pool 太小、数据量太大,回表就落到磁盘。本机演示环境的 innodb_buffer_pool_size 只有 64MB,真实生产环境通常是几十 GB,这个参数直接决定回表的实际成本。

实测对比:

-- 覆盖索引:Extra = Using index
EXPLAIN SELECT customer_id, created_at FROM orders WHERE customer_id = 7;

-- 需要回表:Extra 里没有 Using index
EXPLAIN SELECT customer_id, created_at, amount FROM orders WHERE customer_id = 7;

第一条的 Extra 是 Using index,rows=20;第二条多取 amount,只能回表 20 次。在小表上看不出差距,在千万行表上就是数量级的区别。

还有个容易忽略的细节:二级索引里存的就是主键值,所以查询要的列如果正好只有主键,也不必回表——主键本来就在索引里。这就是为什么 SELECT id FROM orders WHERE customer_id = 7 的 Extra 也会出现 Using index,哪怕它没有专门为 id 建过索引。

覆盖索引省掉了什么

覆盖索引(covering index)指的是「查询需要的列全在索引里」,数据库只读索引就能返回结果,完全跳过回表。它省掉的是:N 次回表的随机 IO、N 次聚簇索引树的下降、以及对应的 Buffer Pool 压力。

设计上有个实用技巧:列表页查询通常只要几列,给这几列建联合索引,让查询变成覆盖索引。比如订单列表要 id, customer_id, status, amount, created_at,可以建 (customer_id, created_at, status, amount),主键 id 本来就在二级索引里,这样整条查询不回表。

覆盖索引的代价是索引变大、写入变慢。把五个列塞进一个索引,等于每次写入多维护一份数据。只给真正高频、且列数不多的查询做覆盖索引,别为了「可能有用」把所有列都塞进去。

翻车现场:20 行数据,11 个页和 71 个页

「回表贵」这句话很容易背,但贵多少,得用页数说话。在演示库上执行两条只差一个列的查询,用 Innodb_buffer_pool_read_requests 统计逻辑读页数(执行前后各取一次值相减):

-- 覆盖索引
SELECT customer_id, created_at FROM orders WHERE customer_id = 7;
-- 逻辑读 11 个页

-- 多取一个 amount,需要回表
SELECT customer_id, created_at, amount FROM orders WHERE customer_id = 7;
-- 逻辑读 71 个页

同样返回 20 行,页数差了 6 倍多。多出来的 60 个页,就是 20 次回表各自落到的聚簇索引页——因为二级索引按 customer_id 排序,主键在这个区间里是跳跃的,回表跳到哪个页并不连续。数据量越大、主键越随机,这个倍数越难看。

把同样的方法放到 19.98 万行的 orders_big 上,做深分页对比:只取 id 的覆盖查询逻辑读 2281 个页,多取 status、amount 后变成 22800 个页,整整 10 倍。这不是「多读了几行」,而是「多访问了两万个页」。Buffer Pool 命中时,读页不落磁盘,但照样消耗内存带宽和 CPU;一旦未命中,就是真实的随机 IO。

查询返回行数逻辑读页数是否回表
覆盖查询(customer_id=7)2011否
多取 amount(customer_id=7)2071是
深分页只取 id102281否
深分页多取 status、amount1022800是

这也解释了为什么「把 SELECT * 改成只查需要的列」常常比「加一个索引」还有效:它直接把回表从查询里删掉了。判断方法很朴素——看 Extra 里有没有 Using index,再用上面这个页数差值验证,别只看耗时,因为耗时会被缓存状态干扰。

逻辑读页数用 Innodb_buffer_pool_read_requests 统计,物理读用 Innodb_buffer_pool_reads。前者反映「查了多少页」,后者反映「有多少页真的落盘」。排查回表成本看前者,判断 IO 压力看后者。

索引下推:回表前先过滤

没有 ICP 时,联合索引只能按最左列定位,剩余条件要回表后在整行上判断。ICP 把「能在索引里判断的条件」提前到索引层,不符合的行直接跳过,不回表。

EXPLAIN SELECT amount FROM orders FORCE INDEX (idx_cust_remark)
WHERE customer_id = 7 AND remark LIKE '%急%';

实测 Extra=Using index condition,rows=20。它先用 customer_id 定位 20 行,再在索引里过滤 remark,只有命中的才回表取 amount。如果没有 ICP,这 20 行都要回表,回来再判断备注。

页分裂:为什么自增主键更快

B+ 树的叶子页有容量上限。自增主键总是往最右边追加,页写满就新开一页,顺序写,代价低。随机主键(UUID、雪花 ID 的随机部分)可能插到中间,目标页写满时要「分裂」:把一半数据挪到新页,更新父节点指针,再插入。

页分裂带来三个代价:写入时的额外拷贝、页填充率下降(每页只用一半)、索引扫描时读更多页。数据量大、写入频繁时,这种随机写会明显拖慢插入,也让索引文件膨胀。这就是「用 UUID 当主键,插入越来越慢」的底层原因,不是 UUID 本身有错,是它破坏了顺序性。

如果业务必须用全局唯一 ID,选趋势递增的方案(UUIDv7、带时间前缀的雪花 ID),让新键尽量落在右侧,减少中间插入。

本机实测把这件事量化了一下:同样插入 20000 行,自增主键用时 0.131 秒,随机 UUID 主键用时 0.310 秒,约 2.4 倍。表还很小,页分裂只发生了一部分;行数继续增长,差距会拉大。注意这个测试里 UUID 用的是 CHAR(36),本身就是 36 字节,比 BIGINT 的 8 字节大四倍多,二级索引也会跟着变宽——随机性和宽度两个因素叠加。

选择性与前缀索引

选择性(selectivity)指「不同值的数量 / 总行数」。手机号几乎唯一,选择性接近 1;状态只有五种,选择性 0.2。选择性高的列适合做索引前缀,因为能快速把范围缩小到很少几行。

长字符串列可以只索引前几个字符,叫前缀索引:KEY idx_email (email(20))。它省空间,但有两个限制:无法用索引完成覆盖查询(因为存的不是完整值),前缀选得太短会导致大量回表。选前缀长度的办法是测「前缀去重后的数量」,接近全列去重数量即可。

联合索引的列顺序也受选择性影响:等值条件、选择性高的列放前面;范围条件放后面。这不是绝对规则,最终要看实际查询模式,用 EXPLAIN 的 key_len 验证。

现象对照

现象真实原因第一步查什么
多查一列就慢很多覆盖索引变成回表,随机 IO 增加看 Extra 里的 Using index 是否消失
范围查询比等值查询慢范围要扫多个叶子页,等值只定位一页看 type 是 range 还是 ref,估算返回行数
UUID 主键写入越来越慢随机插入导致页分裂和随机写看主键是否有序,索引文件是否异常膨胀
二级索引比预想的大叶子节点存了主键,主键太长算一下主键字节数 × 行数,考虑换成整型
同样 SQL 换环境快慢差很多Buffer Pool 大小不同,回表命中率不同对比两边的 innodb_buffer_pool_size 和命中率
加了索引写入明显变慢每次写入要维护多棵 B+ 树统计索引数量,评估是否值得
同一份数据换台机器慢 10 倍Buffer Pool 更小,回表的页命中率低,随机 IO 变多对比两边 Innodb_buffer_pool_read_requests 与物理读
联合索引明明有,范围扫描还是读很多页列顺序和查询条件不匹配,只有前缀生效看 key_len 断在第几列,再对照 WHERE 顺序

边界与代价

B+ 树不是唯一选择。PostgreSQL 默认也是 B+ 树变体,但还支持 GIN、GiST、BRIN 等索引类型,适合全文、地理、大范围顺序数据;MySQL 8.0 支持全文索引和空间索引,但能力边界不同。选型要看查询形态,不是「B+ 树万能」。

索引能加速读,代价是写。每个索引都是一棵独立的树,插入、更新、删除都要同步维护;更新索引列时,还要先删旧键再插新键。写多读少的表(日志、事件流),索引要克制;读多写少的表(配置、商品),可以适当多建。

还有一个常被忽略的边界:索引只对「能转成区间或等值」的条件有效。复杂表达式、跨列计算、JSON 内部字段,B+ 树帮不上忙,需要函数索引、生成列或搜索引擎。别把所有慢查询都归咎于「索引没建对」。

页填充率也是成本的一部分。新建索引时 innodb_fill_factor 可以控制每页预留多少空间,留得多,后续插入分裂少,但索引更大、扫描要读更多页。删除大量数据后,页里的空洞不会自动回收,需要重建索引(ALTER TABLE ... FORCE 或 OPTIMIZE TABLE)才会整理。重建本身要读全表、写新索引,属于重操作,别在业务高峰做。

本篇的页数对比是在 MySQL 8.0.12、innodb_buffer_pool_size=64MB 下实测的。如果你在 8.0.18 及以上,可以再加 EXPLAIN ANALYZE 观察回表次数:覆盖查询的 actual rows 应等于返回行数,非覆盖查询会多出一层按主键查找的节点,loops 接近扫描行数。具体节点命名未在本机验证,标记为待确认;Innodb_buffer_pool_read_requests 差值这个验证方法在 8.0.12 和 8.0.18 都适用。

小结

数据库按页读写,B+ 树用「矮胖」的结构把千万行压到三层左右;聚簇索引的叶子存整行,二级索引的叶子存主键,所以二级索引查询通常要回表;覆盖索引跳过回表,索引下推把过滤提前到索引层;随机主键引发页分裂,顺序主键写入更快。

当天可以自己验证的练习:

  1. 对比覆盖索引和非覆盖索引的 EXPLAIN,记录 Extra 差异。
  2. 把主键从自增改成随机字符串,批量插入一万行,对比耗时和表空间。
  3. 给 email 建 email(6) 前缀索引,观察 key_len 和回表次数。
  4. 用 FORCE INDEX 触发 Using index condition,理解 ICP 在哪里生效。
  5. 查 SHOW ENGINE INNODB STATUS 里的 Buffer Pool 命中率,估算回表成本。

下一篇讲事务与 ACID。索引解决「读得快」,事务解决「写得对」。下单要同时扣库存、写订单、记流水,中间任何一步失败都不能留半成品;并发下还要防丢失更新。接下来从一次扣成负数的库存开始。