覆盖索引救不了排序：2 亿行订单表 ORDER BY + LIMIT 的索引取舍实录

作者陈砚舟-202

MySQL覆盖索引ORDER BYLIMIT慢查询治理DBA

一、现象：一条"看起来很简单"的查询，P99 卡在 1.8s

去年我们迁移订单中心到 MySQL 8.0.32，主表 orders 约 2.1 亿行，单表 190GB。客服后台有一个高频接口：查某用户最近的 20 条订单：

SELECT id, order_no, amount, status, created_at
FROM orders
WHERE user_id = ? AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

压测数据：QPS 峰值约 600，P50 只有 9ms，但 P99 稳定在 1.8s 左右，客服端频繁超时。EXPLAIN 显示 Using filesort，rows 估算 35 万。业务侧一度以为是连接池问题，我们拉了慢日志才发现：慢的全部是这一类带排序的查询，纯点查的 P99 只有 3ms。

影响面：客服工单平均响应从 22s 恶化到 41s，投诉率周环比涨了 3 倍。

二、根因：filesort 为什么这么贵，以及"加覆盖索引"为什么不是万能药

先说机制。InnoDB 的聚簇索引按主键组织，二级索引叶子存「索引列 + 主键」。这条查询命中的二级索引 idx_user_status (user_id, status) 只能定位到约 35 万行，但 created_at 不在索引有序维度上，所以必须把 35 万行取出后做一次 filesort，再取前 20 条。

filesort 的成本点有两个：

回表 + 排序内存。created_at、amount 等列不在 idx_user_status 里，sort 前要先回表取完整行。InnoDB 的 filesort 有两种模式：add_output_fields（排序键 + 全部输出列一起进内存，sort_buffer 放不下就落盘成临时文件）和 packed sort key（只排 id，排完再回表取行）。8.0 默认倾向后者，但行大小超过 max_length_for_sort_data（默认 1024 字节）时直接走前者。我们这张表一行 380 字节，看似安全，但 35 万行 × 380B ≈ 133MB，远超 sort_buffer_size（当时线上配 2MB），必然多次落盘归并——这就是 P99 的 1.8s 来源。
排序是 O(N log N)，与 LIMIT 无关。LIMIT 20 只能减少最终输出，减少不了排序工作量。这是最反直觉的一点：LIMIT 再小，前面的 filesort 该扫多少行就扫多少行。

那为什么不能简单加个覆盖索引？因为覆盖索引（让二级索引直接包含所有 SELECT 列）在这张表上代价很大：order_no 是 32 字符 varchar，amount decimal，一行索引条目从 24B 膨胀到 ~120B，2.1 亿行的索引体积会多出 20GB+，且每笔订单写入都要多维护一份更宽的二叉树，写放大直接上升。客服后台读多写少，但订单创建是核心链路，我们不能为了一个后台查询牺牲交易写入 15% 的吞吐（我们实测过，加宽索引后 insert 的 P99 从 1.2ms 升到 1.4ms，QPS 上限从 8200 掉到 7000）。

所以真正的取舍是：不要加"全列覆盖索引"，而是加一个"排序维度覆盖索引"——只把排序列塞进索引，让优化器走「索引有序扫描 + LIMIT 提前终止」，彻底绕开 filesort。

三、具体做法：一条索引解决 90% 的场景

我们最终加的索引是：

ALTER TABLE orders
  ADD INDEX idx_user_status_time (user_id, status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

关键设计点：

列顺序 user_id, status, created_at：等值列在前，排序列在后。这样 WHERE user_id=? AND status='PAID' 定位后，created_at 在索引内天然有序，ORDER BY created_at DESC LIMIT 20 直接倒序扫 20 条就停，零 filesort、零全量回表（只回表 20 次拿 order_no、amount）。
用 ALGORITHM=INPLACE, LOCK=NONE：2.1 亿行建这个索引耗时 47 分钟（凌晨低峰做），期间 DML 不阻塞。千万别用 LOCK=SHARED，那会锁写。
验证方式：

EXPLAIN FORMAT=JSON SELECT ... ; -- 看 "using_filesort": false
-- 对比前后 P99：1.8s -> 4.2ms，P50 9ms -> 3.1ms

上线后一周的监控：该查询 P99 从 1.8s 降到 4.2ms，慢日志里这一类条目清零；idx_user_status 的旧索引我们保留了两周观察后删掉，避免双写维护。

如果业务还要求按 created_at 区间过滤（比如"最近 3 个月的订单"），同一个索引也能复用，因为区间条件落在排序列上仍然有序。

四、踩过的坑与反直觉之处

坑一：覆盖索引 ≠ 加列就完事。 我们最初试过 ADD INDEX idx_cover (user_id, status, created_at, order_no, amount)，EXPLAIN 确实 Using index 了，但压测发现 insert 延迟上升、且 amount 是 decimal(12,2) 定长 5 字节、order_no 变长，索引页分裂频率明显变高（SHOW ENGINE INNODB STATUS 里 Number of pages created 增速翻倍）。结论：覆盖索引要按"读收益 / 写代价"算账，不是越覆盖越好。
反直觉二：ORDER BY 的列必须在等值列之后才有效。 如果写成 idx (status, user_id, created_at)，user_id 是范围扫描的话 created_at 的有序性就断了，filesort 又会回来。等值-范围-排序的列顺序是硬规则。
坑三：LIMIT 的优化是"提前终止"，不是"只排 20 条"。 很多人以为 LIMIT 20 能让排序变快，实际上它只是让引擎排完（或扫够）后早停。在有序索引上它才能把"扫全量"变成"扫 20 条"。
反直觉四：删旧索引要留观察期。 我们删 idx_user_status 后第二天发现有一条历史报表 SQL 还依赖它（它把 user_id 当范围扫），P99 又飙了。教训：删索引前先 grep 慢日志 + 业务代码双确认，别信 EXPLAIN 的"应该没人用"。

五、结论与可复用清单

核心结论：对 等值过滤 + ORDER BY + LIMIT 的高频查询，最优解通常是"等值列 + 排序列"的复合索引，而不是"全列覆盖索引"。 前者用索引有序性换掉 filesort，写代价小；后者用体积换掉回表，写代价大。两者不是替代关系，而是不同成本模型下的选择。

可复用清单（按顺序做）：

慢日志里按 using_filesort 聚合，找出 TOP 5 的 filesort 查询——这是 ROI 最高的靶子。
对每个查询，确认 WHERE 里的等值列和 ORDER BY 的排序列，按「等值在前、排序在后」设计复合索引。
用 EXPLAIN FORMAT=JSON 验证 using_filesort: false 且 rows 接近 LIMIT 值。
建索引用 ALGORITHM=INPLACE, LOCK=NONE，2 亿行级预计 30–60 分钟，放低峰。
压测对比 insert/update 的 P99，确认写放大可接受（我们红线是 insert P99 增幅 < 10%）。
旧索引保留 ≥ 2 周，grep 慢日志和代码确认无依赖后再删。

一句话：覆盖索引是"宽"的代价，排序索引是"窄"的优雅——大多数后台查询只需要后者。

赞 0 · 收藏 0 · 评论 0 · 浏览 0

暂无评论。