MySQL ORDER BY LIMIT 为什么有时不走过滤索引：排序索引、filesort 和核对方法

订单列表只显示最近 20 条，接口却在高峰时偶尔变慢。EXPLAIN 里还出现了一个反直觉的现象：表上明明有按状态建的索引，优化器却选择按主键顺序读取，再边读边过滤。很多人看到这里就想强制索引，结果把一次局部波动改成了长期负担。先把问题拆开：这条 SQL 同时在争取更少的过滤行和更快的排序输出，LIMIT 会改变两者的成本权重。

LIMIT 20 让“先过滤”与“先排序”发生竞争

看一个常见订单页：用户只看已支付订单，并要求按订单号倒序返回 20 条。过滤索引可以快速定位 status = 'PAID' 的范围，但若它无法同时满足 ORDER BY id DESC，服务器还要再排一次序。另一条路是沿着主键倒序读取，遇到已支付记录就收下，够 20 条就停；如果最近订单大多已支付，这条路可能更省。

反过来，若已支付记录在最近订单中很稀疏，按主键一路回扫会读过很多不匹配行，过滤索引加排序反而可能更合适。MySQL 官方文档说明，带 LIMIT 的排序查询会影响索引选择；可用有序索引时很快，若必须 filesort，则要先选出并排序匹配行。这里别急着给某一种路径贴“永远更快”的标签。

先用 EXPLAIN 看清它准备怎样读取

先把线上真实条件缩成可复现的查询，再看 key、rows 和 Extra。下面的 SQL 只作为示意，订单状态、时间窗口和排序字段应替换成自己的业务条件。

EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY id DESC
LIMIT 20;

• key 表示本次路径选择的索引，不等于“表上最好的索引”。
• rows 是优化器预计要检查的行数；对 InnoDB 来说它是估算值，不能把它当成精确计数。
• Extra 出现 Using filesort 说明排序没有直接由当前读取顺序解决，但它本身不是判定慢查询的结论。

把两条候选 SQL 的 key、rows、返回行数和响应时延放在一起看，才能知道瓶颈是筛选稀疏、排序量过大，还是回表读取的字段太宽。只盯着一个 Using filesort，很容易把原本读取很少的数据路径误判掉。

复合索引要同时服务筛选与稳定排序

如果产品定义是“某状态下按创建时间倒序，订单号做同时间的稳定次序”，索引和 SQL 的排序表达就应该一致。等值过滤列通常放在前面，随后才是排序列；把唯一的 id 放进排序，可以避免同一时间值下翻页顺序漂移。

CREATE INDEX idx_orders_status_created_id
ON orders (status, created_at, id);

SELECT id, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 20;

这不是要求每个列表页都新增索引。它是一个待核对的候选结构：先确认 WHERE 与 ORDER BY 是否真是这个组合，再看写入成本、索引体积和已有索引能否复用。MySQL 官方文档也提醒，索引不是越多越好；每个额外索引都会占用空间，并增加插入、更新和删除的维护成本。

上面的查询只取 id 与 created_at。若结果所需字段都在同一个索引里，就有机会作为覆盖索引读取，减少回到表数据页的需要。把 SELECT * 原样搬进来通常就失去这个条件，因此应先确认列表页真正渲染了哪些字段。

两个反例比“加索引”更值得先看

第一个反例是排序方向或列顺序和索引不一致。例如业务按 created_at DESC, id ASC 排序，索引却按另一种方向与顺序建立；不能凭肉眼认定它一定能复用排序。第二个反例是过滤条件本身范围很宽，或者一次查询需要取大量不在索引中的字段。此时即使索引名称看起来匹配，回表和读取量也可能抵消优势。

还有一种常见误区是只为一个低频后台页面加很宽的覆盖索引。读侧少了一点回表，写侧却让每次状态更新都多维护一份大索引。架构上的取舍不在“覆盖索引好不好”，而在这个读取模式是否足够高频、字段是否足够稳定、写入成本是否能接受。

把 prefer_ordering_index 当成对比开关，不当成长期答案

MySQL 针对带 ORDER BY 和 LIMIT 的查询会偏向考虑有序索引。官方文档提供了 prefer_ordering_index 开关，便于比较另一条候选路径。更稳妥的做法是在隔离的会话或测试环境里做一次对比，不要直接把全局默认改掉。

SET SESSION optimizer_switch = 'prefer_ordering_index=off';

EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY id DESC
LIMIT 20;

这一步的价值是帮助理解“排序优先”和“过滤优先”各自会选什么，不是替业务定结论。需要实际度量时，EXPLAIN ANALYZE 会真正运行 SELECT 并报告测量数据，应该只对受控查询使用，避免把昂贵的生产读请求额外再跑一遍。

上线前按这张表收口

相关问题

Using filesort 一出现就必须优化吗？

不一定。它说明当前排序没有直接按索引顺序完成，但还要看匹配行数、排序量和实际时延。少量数据上的 filesort 可能比沿着不合适的排序索引扫很多行更省。

为什么 ORDER BY 要加 id？

当主要排序列存在相同值时，加唯一列可以让返回顺序稳定。MySQL 文档也指出，只按非唯一排序列配合 LIMIT 时，并列行的相对次序不保证固定。

覆盖索引是不是越宽越好？

不是。只有高频读取确实需要的少量字段才值得考虑放进索引；宽索引会占空间，也会增加写入维护成本。

能直接在生产请求上跑 EXPLAIN ANALYZE 吗？

要谨慎。它会运行 SELECT 并收集测量结果。先在可控副本、低风险时间窗或等价测试数据上确认读取量，再决定是否需要线上抽样。

核对资料

• MySQL Reference Manual：LIMIT Query Optimization，核对 LIMIT 对有序索引、filesort、并列行顺序与 prefer_ordering_index 的影响。
• MySQL Reference Manual：EXPLAIN Statement，核对 EXPLAIN 与 EXPLAIN ANALYZE 的语义。
• MySQL Reference Manual：How MySQL Uses Indexes，核对复合索引、覆盖索引和索引维护成本的边界。