MySQL / 系统课时

为 ORDER BY 设计索引顺序

为 ORDER BY 设计索引顺序

这节课解决 ORDER BY + LIMIT 的索引设计:如何把等值过滤列和排序列排进联合索引,让 MySQL 按 B+Tree 顺序直接取前 N 行,尽量避免 filesort。以下以 MySQL 8.x InnoDB 为准。

学习目标

  • 根据等值过滤前缀与排序列推导联合索引顺序
  • 判断 ASC、DESC、混合方向与范围条件是否破坏索引排序
  • 用 EXPLAIN 验证计划中是否仍出现 Using filesort

先把排序需求写成索引前缀

B+Tree 联合索引按键值从左到右排序。若查询先把若干前导列固定为单值,那么这些列相当于常量,后面的索引列仍能为结果提供连续有序的扫描路径。

例如订单列表固定租户和状态,再按创建时间与主键倒序分页:

WHERE tenant_id = 1 AND status = 'PAID' ORDER BY created_at DESC, id DESC LIMIT 3

可优先考虑索引 (tenant_id, status, created_at DESC, id DESC)。前两列负责缩小范围,后两列负责稳定排序;id 作为并列时间的第二排序键,可避免同一时间戳下分页顺序不稳定。

等值过滤前缀与排序列组成联合索引

下面的 SQL 可直接建立一个最小实验:

sql
DROP TABLE IF EXISTS order_feed;

CREATE TABLE order_feed (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  KEY idx_tenant_status_created_id
    (tenant_id, status, created_at DESC, id DESC)
) ENGINE=InnoDB;

INSERT INTO order_feed
  (id, tenant_id, status, created_at, amount)
VALUES
  (101, 1, 'PAID', '2026-09-01 10:00:00', 100.00),
  (102, 1, 'PAID', '2026-09-01 10:05:00', 80.00),
  (103, 1, 'NEW',  '2026-09-01 10:06:00', 20.00),
  (104, 1, 'PAID', '2026-09-01 10:05:00', 120.00),
  (105, 2, 'PAID', '2026-09-01 10:07:00', 60.00);

SELECT id, created_at, amount
FROM order_feed
WHERE tenant_id = 1
  AND status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 3;

预期结果

text
id   created_at           amount
104  2026-09-01 10:05:00  120.00
102  2026-09-01 10:05:00   80.00
101  2026-09-01 10:00:00  100.00

判断方向与范围条件

排序方向要看整个索引扫描能否产生目标顺序,而不是机械地给每个 ORDER BY 列都声明 DESC。若排序列方向一致,MySQL 可以正向或反向扫描索引;真正需要重点设计的是混合方向,例如 created_at DESC, id ASC,它不能由 (created_at ASC, id ASC) 简单整体反扫得到。

查询形态 索引形态 能否直接提供目标顺序 关键原因
两列等值过滤后 ORDER BY created_at DESC, id DESC (tenant_id, status, created_at, id) 通常可以 排序方向一致,可整体反向扫描
两列等值过滤后 ORDER BY created_at DESC, id ASC (tenant_id, status, created_at DESC, id ASC) 可以作为匹配设计 混合方向需要与索引方向模式一致
tenant_id = ? AND created_at >= ? 后再 ORDER BY amount (tenant_id, created_at, amount) 通常不可以 created_at 是范围,多段 amount 顺序不能合并成一个全局顺序
tenant_id IN (...) 后按后续列排序 以 tenant_id 为首列 需看执行计划 前导键不再是单一常量,可能形成多个有序区间

用 EXPLAIN 验证是否省掉 filesort

索引设计只是候选方案,最终必须看优化器实际选择。对上面的查询执行:

sql
EXPLAIN
SELECT id, created_at, amount
FROM order_feed
WHERE tenant_id = 1
  AND status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 3;

预期结果

text
关键检查项:
key 应优先为 idx_tenant_status_created_id
Extra 不应包含 Using filesort
rows、filtered 等估算值会随统计信息和数据规模变化

若 Extra 仍出现 Using filesort,先检查实际使用的 key,再核对排序列是否紧跟在可固定的索引前缀之后。不要仅为了消除 filesort 就强制索引;当过滤选择性差、回表成本高或待排序数据很少时,优化器选择其他访问路径可能更便宜。

把 LIMIT 当成收益放大器

LIMIT 本身不会让一个错误的索引突然满足排序,但当索引已经能同时完成定位与排序时,它允许执行器在得到足够结果后尽早停止扫描。对于“最新 20 条订单”“最高 50 个分数”这类 Top-N 查询,这通常比先收集大量候选行再排序更有价值。

如果还有未进入索引的残余过滤条件,执行器可能需要扫描超过 N 个索引项才能得到 N 行结果。因此最终要同时观察扫描行数、回表次数和 Using filesort,而不是只盯着一个 Extra 标记。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

为 ORDER BY 设计索引顺序

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通