开发者日记

外观

回表 IO 减半：列表页覆盖索引设计实录 ​

列表页查询为什么越翻越慢 ​

在后台管理系统中，订单列表、商品列表、用户操作记录这类页面通常具备三个共同特征：查询字段固定、排序字段固定、分页访问频繁。数据量较小时，SQL 是否走索引差异不明显；当单表达到数百万甚至上千万行后，慢查询往往不是因为“没有索引”，而是因为索引不能覆盖查询所需字段，MySQL 只能在二级索引与聚簇索引之间不断回表。

InnoDB 的二级索引叶子节点保存的是索引列值和主键值。如果查询字段不在索引中，存储引擎需要拿着主键再访问聚簇索引，读取完整行记录。一次业务查询看起来只执行了一条 SQL，底层却可能发生：根据条件扫描二级索引、对每条记录回表、再排序或丢弃不需要的行。对列表页而言，单次请求可能返回 20 条数据，但在分页较深、筛选选择性较差时，参与回表的记录可能远大于 20。

覆盖索引的目标，是让查询需要的过滤列、排序列和返回列都能从同一个二级索引中获得，从而在执行计划中出现 Using index，避免大量随机回表 IO。

普通索引与覆盖联合索引的执行差异 ​

假设有一张订单表，业务最常见的查询是：查询某个用户在某状态下的订单，按下单时间倒序分页，只展示订单编号、金额、下单时间。

sql
CREATE TABLE `t_order` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `status` TINYINT NOT NULL COMMENT '订单状态：0待支付 1已支付 2已取消',
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
  `created_at` DATETIME NOT NULL COMMENT '下单时间',
  `updated_at` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

初始只有 idx_user_id 时，下面的列表 SQL 可以通过用户 ID 缩小范围，但 status 过滤、created_at 排序以及 order_no、amount 的读取仍需回表。

sql
SELECT id, order_no, amount, created_at
FROM t_order
WHERE user_id = 10086
  AND status = 1
ORDER BY created_at DESC
LIMIT 0, 20;

这个执行过程可以概括为：先在 idx_user_id 中找到该用户的主键，再回到聚簇索引判断状态、读取展示字段并排序。若某用户订单很多，或者业务上常见状态占比较高，就会产生大量额外 IO。

列表页查询的两种索引路径普通二级索引扫描 user_id得到主键值回聚簇索引过滤排序取字段覆盖联合索引过滤并逆序扫描user_id/status/created_at直接返回字段Using index

联合索引字段顺序：等值过滤、排序、覆盖列 ​

覆盖索引不是简单把 SELECT 后面的字段全部塞进索引，字段顺序直接决定它能否同时承担过滤、排序和覆盖三类职责。一个常用设计公式是：

高频等值条件列 + 排序列 + 列表页返回列。

在本例中，user_id 和 status 都是等值过滤，其中 user_id 的选择性通常更高，应放在前面；created_at 承担倒序排序，放在过滤列之后；order_no、amount 只用于返回，可以追加到索引尾部。由于 InnoDB 二级索引天然包含主键 id，无需显式放入。

sql
ALTER TABLE t_order
ADD INDEX idx_user_status_time_cover (
  user_id,
  status,
  created_at,
  order_no,
  amount
);

该索引能按 (user_id, status) 精确定位一段有序数据，而这段数据内部已经按 created_at 维护顺序。执行 ORDER BY created_at DESC LIMIT 20 时，MySQL 可以反向扫描索引，取够 20 条即停止；尾部的 order_no 和 amount 又保证查询字段可直接从索引获得。

使用 EXPLAIN 验证：

sql
EXPLAIN
SELECT id, order_no, amount, created_at
FROM t_order
WHERE user_id = 10086
  AND status = 1
ORDER BY created_at DESC
LIMIT 0, 20;

理想结果应关注以下几点：

  • key 显示 idx_user_status_time_cover，说明优化器选择了联合索引；
  • type 至少达到 ref，而不是全表扫描或大范围索引扫描；
  • Extra 出现 Using index，表示不需要回表读取完整数据行；
  • Extra 不应出现 Using filesort，否则排序仍可能产生额外开销；
  • rows 预估扫描行数应接近分页需要访问的范围，而不是该用户的全部订单量。

也可以通过慢查询分析中的 Handler_read_* 状态辅助判断。优化前若 Handler_read_rnd_next 很高，通常意味着大量回表或不必要扫描；覆盖索引生效后，相关计数器增长会明显收敛。需要强调的是，“回表 IO 减半”并不是固定数学比例，实际收益取决于条件选择性、分页深度、缓冲池命中率以及返回列数量；在不少列表页场景中，减少的随机回表次数可能远高于一半。

可选方案对比：单列索引、联合索引与索引下推 ​

只给 user_id 建单列索引，适合以用户为核心的点查，但无法覆盖状态过滤和时间排序。分别给 user_id、status、created_at 建多个单列索引，MySQL 通常只能选择其中一个较优索引，或者在特定版本中使用索引合并，多个索引的回表与去重成本并不稳定，不适合作为高频列表页的主要方案。

建立 (user_id, status, created_at) 联合索引后，过滤和排序问题基本解决，但如果列表还要返回 order_no、amount，仍需对最终 20 条记录回表。把返回列追加到索引尾部，可以进一步消除这批回表，代价是索引体积变大、写入维护成本上升。

MySQL 5.6 之后支持索引下推（ICP），在某些范围或模糊查询中可以在存储引擎层提前过滤，减少回表次数。例如复合索引包含 status 和 created_at，即使条件不能完全使用最左前缀，ICP 也可能降低回表量。但 ICP 只是减少回表，不等同于覆盖索引；执行计划中看到 Using index condition 时，仍然可能发生回表，只有出现 Using index 才表示查询被索引覆盖。

数据准备与效果验证 ​

下面的 SQL 可用于构造可复现实验数据。实际压测时可根据需要把存储过程循环次数调大。

sql
-- 插入若干用户订单，便于观察索引选择
INSERT INTO t_order (order_no, user_id, status, amount, created_at, updated_at)
VALUES
('NO202401010001', 10086, 1, 99.00, '2024-01-01 10:00:00', '2024-01-01 10:05:00'),
('NO202401010002', 10086, 0, 129.00, '2024-01-02 09:20:00', '2024-01-02 09:21:00'),
('NO202401010003', 10086, 1, 259.00, '2024-01-03 15:30:00', '2024-01-03 15:35:00'),
('NO202401010004', 20001, 1, 88.00, '2024-01-01 11:00:00', '2024-01-01 11:02:00');

-- 分析表，保证统计信息较新
ANALYZE TABLE t_order;

-- 查看执行计划
EXPLAIN FORMAT=JSON
SELECT id, order_no, amount, created_at
FROM t_order
WHERE user_id = 10086
  AND status = 1
ORDER BY created_at DESC
LIMIT 0, 20;

如果线上表已经存在 (user_id, status, created_at) 索引，而新增覆盖列会导致索引较宽，可以使用在线 DDL 工具或在低峰期执行。MySQL 8.0 的部分场景支持在线添加二级索引，但仍应评估主从延迟、磁盘空间和写入尖峰。

避坑总结：覆盖索引常见失效点 ​

1. 查询列加入大字段，导致索引臃肿甚至无法覆盖 ​

有同学为了彻底消除回表，把 remark、address、goods_snapshot 等 TEXT 或长 VARCHAR 字段也放进覆盖索引。结果索引体积暴涨，缓冲池能缓存的索引页减少，写入放大明显，查询性能反而下降。覆盖索引应优先服务列表页的固定小字段，详情页仍通过主键查询完整记录。若确实需要较长字段，也要结合字段长度、更新频率和访问比例重新评估，不能无脑覆盖。

2. WHERE 列发生函数或隐式类型转换，索引前缀失效 ​

例如写成 DATE(created_at) = '2024-01-03'，或者 user_id 字段是数字却传入字符串并触发字符集转换，都可能使索引无法按有序范围定位，执行计划中出现非预期扫描甚至 Using filesort。修正方法是改为范围条件：created_at >= '2024-01-03 00:00:00' AND created_at < '2024-01-04 00:00:00'，并保证参数类型与表结构一致。

3. 索引顺序与排序方向不匹配，误以为建了索引就不会排序 ​

索引设计为 (user_id, created_at, status)，但查询条件是 user_id = ? AND status = ? ORDER BY created_at DESC，由于 status 位于排序列之后，无法先按等值状态定位再利用 created_at 的全局顺序，可能出现 Using filesort。正确做法是把等值列放在排序列之前，即 (user_id, status, created_at)。MySQL 8.0 虽支持降序索引，但只有在多列混合升降序等复杂场景下才更关键，单列倒序扫描通常可直接反向使用普通 B+Tree 索引。

4. 深分页仍按大 offset 扫描 ​

即使覆盖索引生效，LIMIT 100000, 20 也需要先跳过大量索引记录。常见现象是页码越深越慢，rows 扫描量随偏移量增大。业务上应改为游标分页或“延迟关联”：先用覆盖索引定位目标主键，再回表取完整行。例如以 created_at, id 作为游标，查询 WHERE user_id = ? AND status = 1 AND (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20，避免维护昂贵的偏移量。

5. 冗余索引长期不清理，写入成本被忽视 ​

新增 (user_id, status, created_at, order_no, amount) 后，原来的 (user_id) 单列索引在部分场景下可能已被覆盖。若确认没有仅按 user_id 查询且对索引体积敏感的 SQL，应考虑下线冗余索引。可通过 sys.schema_unused_indexes、性能快照和慢日志持续观察，不要仅凭一次执行计划就删除线上索引。

覆盖索引的本质，是用可控的索引空间换取稳定的读路径。列表页 SQL 越固定、访问越频繁，这种交换越划算；而对于筛选条件多变、返回字段庞大、写入极敏感的场景，则应保留必要的主键回表，配合缓存、搜索引擎或宽表方案，而不是让一个联合索引承担所有查询。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。