开发者日记

外观

从全表 filesort 到索引有序：ORDER BY 分页优化实战 ​

问题背景：ORDER BY 分页为什么总会出现 filesort ​

在业务列表页中，最常见的 SQL 形态是：按某个条件过滤，按创建时间倒序，再取分页数据。数据量小时，MySQL 即使在内存里排序也很快；当订单表达到百万、千万行后，Using filesort 往往会成为慢查询的直接信号。

需要先澄清一点：filesort 不等于一定发生了磁盘文件排序，它只表示优化器没有按索引顺序直接获得结果，需要额外执行排序过程。排序可能在内存中完成，也可能超出 sort_buffer_size 后使用临时文件。因此，优化目标不是机械地追求执行计划里没有 filesort，而是让过滤、排序、分页尽可能利用同一棵 B+Tree，减少扫描行数和排序数据量。

下面建立一张可直接运行的订单明细表：

sql
CREATE TABLE t_order (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL COMMENT '租户 ID',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态',
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '金额',
  created_at DATETIME(3) NOT NULL COMMENT '创建时间',
  remark VARCHAR(200) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (id),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 插入一批测试数据，可用存储过程或应用程序生成
INSERT INTO t_order(tenant_id, status, amount, created_at, remark)
VALUES
(1001, 1, 99.00, '2026-01-10 10:00:00.000', '首单'),
(1001, 2, 199.00, '2026-01-11 11:20:00.000', '已支付'),
(1002, 1, 299.00, '2026-01-12 09:15:00.000', '待处理');

典型查询如下：

sql
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

如果只有 idx_created_at，优化器可能选择按时间索引倒序扫描，再逐行判断 tenant_id 和 status；也可能根据过滤度选择其他索引，最后对结果集排序。使用下面的语句观察执行计划：

sql
EXPLAIN
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

重点关注 key、rows、filtered 和 Extra。当 Extra 中出现 Using where; Using filesort，且 rows 很大时，说明 MySQL 扫描了较多记录后还要排序。

方案对比：文件排序、单条件索引与联合索引 ​

排序优化通常有三类路径。

第一类是接受 filesort，通过 sort_buffer_size、系统配置或提升硬件缓解问题。这种方式成本低，但只能治标。分页越靠后、满足条件的行越多，排序代价越明显。

第二类是只给排序列建索引，例如 KEY idx_created_at(created_at)。它可以让 ORDER BY created_at DESC LIMIT n 在没有过滤条件时直接逆序读取，但一旦 WHERE 中还有高选择性条件，索引顺序和过滤条件无法同时满足，仍可能扫描大量无效记录。

第三类是设计与查询匹配的联合索引，让等值条件列在前、排序列随后，使数据库先定位到某个连续区间，再按索引天然顺序向后取数。对上面的 SQL，更合适的索引是：

sql
ALTER TABLE t_order
ADD INDEX idx_tenant_status_created (tenant_id, status, created_at DESC);

在 MySQL 8.0 中，索引可以显式声明升序或降序；在 MySQL 5.7 中， descending index 语法通常会被解析但实际仍以升序存储，不过反向扫描二级索引也可以避免排序。跨版本场景应使用真实数据库版本验证执行计划。

下图展示联合索引如何同时完成过滤与排序：

等值定位 tenant_idstatus区间内顺序读取created_at DESC无需额外排序消除 filesort取 20 行LIMIT索引顺序：等值过滤列在前，排序列紧随其后目标：连续区间扫描 + 有序返回 + 尽早停止

实操步骤：设计索引顺序并验证执行计划 ​

第一步：按查询模板确定索引列顺序 ​

联合索引的常见经验是：等值条件列在前，范围条件列和排序列谨慎放置，最后再考虑覆盖列。对于以下语句：

sql
WHERE tenant_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT ?

tenant_id 与 status 都是等值比较，放在索引左侧；created_at 承担排序，放在后面。建立索引后查看计划：

sql
ALTER TABLE t_order
ADD INDEX idx_tenant_status_created (tenant_id, status, created_at DESC);

EXPLAIN
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

理想情况下，key 显示 idx_tenant_status_created，Extra 不再出现 Using filesort。由于二级索引叶子节点包含主键值，如果只查询 id 和 created_at，还可能出现 Using index，即覆盖索引，无需回表。

第二步：用覆盖索引减少回表 ​

如果列表页只需要主键、金额和时间，可以把 amount 追加到索引末尾：

sql
ALTER TABLE t_order
DROP INDEX idx_tenant_status_created,
ADD INDEX idx_tenant_status_created_amount (
  tenant_id,
  status,
  created_at DESC,
  amount
);

EXPLAIN
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

此时查询列为主键或索引内字段，Extra 可能显示 Using index。但不要把所有 SELECT 列都塞进索引：索引越宽，写入放大、缓存命中和维护成本越差。覆盖列只应放高频、轻量、收益明确的字段。

第三步：范围查询不要阻断排序利用 ​

如果条件改为时间范围：

sql
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND created_at >= '2026-01-01'
  AND created_at <  '2026-02-01'
ORDER BY status, created_at DESC
LIMIT 20;

索引 (tenant_id, status, created_at) 就很难同时利用 status 排序，因为 status 没有等值条件，却位于范围列之前。若真实模板是固定租户、按时间范围过滤、按时间排序，则更自然的索引是：

sql
ALTER TABLE t_order
ADD INDEX idx_tenant_created (tenant_id, created_at DESC);

这说明索引设计不能只背“最左前缀”，还要区分等值、范围、排序、分组四类访问方式。

第四步：优化深分页而不是盲目扩大 OFFSET ​

下面的语句在第 100000 行之后仍然要先丢弃大量记录：

sql
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 100000, 20;

即使排序走了索引，大偏移量也会造成大量顺序扫描和回表。更稳定的方式是游标分页，也称 seek pagination。第一页记录最后一条的时间和 ID 后，下一页使用：

sql
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
  AND (
    created_at < '2026-01-11 11:20:00.000'
    OR (
      created_at = '2026-01-11 11:20:00.000'
      AND id < 20000
    )
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

为了支持相同时间下的稳定排序，索引也应包含用于去重排序的主键：

sql
ALTER TABLE t_order
ADD INDEX idx_tenant_status_created_id (
  tenant_id,
  status,
  created_at DESC,
  id DESC
);

如果业务允许，推荐让 created_at 本身保持唯一，或直接使用雪花 ID 等单调有序 ID，简化游标条件。

filesort 与索引顺序的判断方法 ​

不要只看 SQL 文本，应结合 EXPLAIN 和真实响应时间。可以用以下命令查看执行计划：

sql
EXPLAIN FORMAT=JSON
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

在 JSON 计划中关注 used_columns、access_type、rows_examined_per_scan、ordering_operation 等信息。MySQL 8.0 还可以使用：

sql
EXPLAIN ANALYZE
SELECT id, amount, created_at
FROM t_order
WHERE tenant_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

它会展示实际行数、循环次数和各阶段耗时，比传统估算计划更适合验证索引是否真正生效。

避坑总结：排序索引最容易踩的 4 个坑 ​

1. 对索引列使用函数导致索引顺序失效 ​

错误写法：

sql
SELECT id, created_at
FROM t_order
WHERE tenant_id = 1001
ORDER BY DATE(created_at) DESC
LIMIT 20;

常见现象是 key 不符合预期，Extra 出现 Using filesort。原因是 DATE(created_at) 生成了计算结果，无法直接使用 created_at 的有序性。修正方式是改为原生列范围：

sql
SELECT id, created_at
FROM t_order
WHERE tenant_id = 1001
  AND created_at >= '2026-01-01'
  AND created_at <  '2026-02-01'
ORDER BY created_at DESC
LIMIT 20;

如确需表达式排序，MySQL 8.0 可考虑函数索引，但要确保 ORDER BY 表达式与索引表达式完全一致。

2. ORDER BY 方向混用导致无法顺序扫描 ​

例如索引是 (tenant_id, status, created_at)，SQL 却写成：

sql
WHERE tenant_id = 1001 AND status = 1
ORDER BY created_at ASC, id DESC;

多个排序列方向不一致时，数据库难以沿一个方向顺序读取，仍可能排序。修正方式是统一业务允许的方向，或建立与排序方向匹配的 descending index，例如 (tenant_id, status, created_at DESC, id DESC)。

3. 范围列放在排序列前面，打断后续排序 ​

索引 (tenant_id, created_at, status) 面对 WHERE tenant_id=? AND created_at>=? ORDER BY status 时，status 在索引中的顺序被范围扫描切断。常见现象是 WHERE 已使用索引，但 Using filesort 仍存在。修正方式是重新梳理高频查询：能等值的列放前面，范围列和排序列谁承担主要访问路径，要结合过滤度与分页方式决定，必要时为不同模板建立独立索引。

4. 只看执行计划不看回表与深分页成本 ​

有些 SQL 已经没有 filesort，但 LIMIT 100000,20 依然很慢，因为扫描和回表成本没有消失。不要把“消除 filesort”当作唯一目标。首页接口优先设计过滤与排序匹配的联合索引；翻页接口优先改为游标分页；只有后台批处理或低频管理页面，才考虑接受较大的 OFFSET 成本。

最终的索引顺序应服务于具体 SQL 模板，而不是追求一张表只有一个万能索引。通过等值列定位、排序列顺序读取、覆盖列减少回表、游标分页避免深偏移，才能真正把 ORDER BY 分页从“排序后取数”优化为“沿索引直接取数”。

免责声明

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

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

免责声明

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