MySQL：ORDER BY update_time不在索引里产生filesort，40万行列表页从秒开到8秒，排序字段该不该塞进联合索引？

一、问题定义：8 秒是怎么来的

1.1 现象描述与可复现的最小场景

典型表结构如下（脱敏后）：一张约 40 万行的业务列表表，主键 id BIGINT，存在二级索引 idx_status(status)，排序列 update_time DATETIME 上没有任何索引。查询形如：

SELECT id, title, status, update_time
FROM biz_list
WHERE status = 1
ORDER BY update_time DESC
LIMIT 20 OFFSET 0;

在数据量较小时（数万行），该查询可能仍在百毫秒级；当行数增长到 40 万、且 status=1 的选择性较高（例如命中 20 万行）时，EXPLAIN 会出现 Using filesort，响应时间跃升至秒级甚至更高。本文标题中的“从秒开到 8 秒”，来自笔者在生产环境与模拟环境中的观测区间，属于模拟与实测混合数据，已在下文标注，不作为任何基准测试结论引用。

1.2 为什么“秒开”会突然变成“8 秒”

性能劣化往往不是线性的，而是阈值型的。原因在于 MySQL 排序时存在两条分支：当参与排序的行数较少、且排序缓冲区（sort_buffer_size）足以容纳全部行时，优化器走“内存排序 + 优先队列”；一旦行数或单行宽度超过缓冲区，就会退化为“内存分块排序 + 磁盘临时文件归并”。后者涉及多次磁盘 I/O 与归并，耗时会出现阶跃式上升。这一机制在 MySQL 官方文档“ORDER BY Optimization”一节中有明确描述（Oracle, MySQL 8.0 Reference Manual, §8.2.1.16）。

本文评述：很多团队把“加机器、调大 sort_buffer_size”当作第一反应，但这只是把阈值往后推，并未消除排序本身。只要排序字段不在索引的有序序列中，成本就随命中行数线性增长，调参只是延迟问题爆发的时间点。

1.3 本文的分析主线

全文围绕一条主线展开：排序字段是否进入联合索引，本质是“消除排序成本”与“增加索引维护与回表成本”之间的博弈。 这条主线贯穿四个判断维度——排序是否可被索引序满足、回表次数是否可控、分页语义是否可改写、写入放大是否可接受。后续每一节都服务于这条主线，避免陷入“见 filesort 就加索引”的经验主义。

二、原理拆解：MySQL 排序的执行路径与成本模型

2.1 filesort 的两种形态

所谓 filesort，并不一定真的写文件，它是 MySQL 对“无法利用索引有序性完成排序”这一情形的统称。其内部有两种实现：

• 双路排序（two-pass / rowid sort）：先取出排序键与行标识（rowid 或主键），排序后再按 rowid 回表取其余列。优点是可排序的行更窄、缓冲区能容纳更多行；缺点是回表次数多。
• 单路排序（single-pass / full row sort）：一次性取出查询需要的所有列放入排序缓冲区，排序后直接输出。优点是省去回表；缺点是单行宽，缓冲区容纳行数少，更容易触发磁盘归并。

优化器根据 max_length_for_sort_data（MySQL 8.0.20 起该参数被移除，改由优化器内部估算）与查询列宽决定采用哪种形态。本文评述：这一自动选择机制意味着，同一 SQL 在 SELECT 列增减后，排序性能可能突变，这也是“昨天还快、今天变慢”的常见诱因之一，排查时不能只看 WHERE 条件。

2.2 优先队列：LIMIT 的救命稻草及其边界

当查询带 LIMIT n 时，MySQL 会使用优先队列（priority queue）只维护 n 个候选行，而非对全部行完整排序。这使 LIMIT 20 的排序开销显著低于全量排序。但边界在于：优先队列仍需扫描所有满足 WHERE 的行，只是不做全量排序。因此当命中行数达 20 万时，扫描与比较成本依然存在，只是避免了磁盘归并。

这也解释了为何“加 LIMIT 就能快”是错觉：LIMIT 优化的是排序阶段，不是扫描阶段。若 WHERE 条件本身选择性差，扫描成本仍是主导项。

2.3 成本模型的形式化表达

可以把单次查询的排序相关成本粗略表示为：

C_sort ≈ R × c_cmp + R × c_fetch + max(0, R×w − B)/w × c_io

R      : 满足 WHERE 的行数
c_cmp  : 单次比较成本
c_fetch: 取行/回表成本
w      : 单行宽度
B      : 排序缓冲区大小
c_io   : 单位磁盘 I/O 成本

当 R×w ≤ B 时，第三项为 0，成本主要是比较与取行；一旦越界，第三项线性增长并叠加归并开销，形成阶跃。该模型为本文的定性分析工具，非 MySQL 官方公式，属于笔者基于执行路径的归纳模型，用于解释阈值现象，不作为精确预测。

2.4 索引有序性如何消除排序

B+ 树索引本身按索引键有序存储。若 ORDER BY 的列序列恰好与某个索引的键序列前缀一致，且 WHERE 条件对该索引形成等值前缀约束，优化器即可沿索引顺序扫描，天然获得有序结果，Using filesort 消失。这是“排序字段进联合索引”的理论依据。但“能消除排序”不等于“一定更快”，因为沿索引扫描可能带来更多回表——这正是下一节要展开的博弈。

三、索引序与最左前缀：排序字段进联合索引的硬约束

3.1 最左前缀与“等值在前、范围在后、排序跟随”

联合索引 (a, b, c) 的有序性表现为：先按 a 有序，a 相同再按 b 有序，b 相同再按 c 有序。要让 ORDER BY c 利用该索引，必须满足：a、b 在 WHERE 中均为等值条件，使扫描区间内 a、b 恒定，此时 c 在区间内有序。若 b 是范围条件（如 b > 10），则 c 的有序性被范围打破，排序无法消除。

由此得到一条工程口诀：等值列在前，范围列其次，排序列尽量紧随等值列之后。本文评述：这条口诀在单表简单查询中成立，但在多条件动态拼接的列表页中，WHERE 条件随筛选器变化，等值/范围的角色会漂移，因此“一个索引打天下”往往不现实，需要按高频查询组合设计索引，而非追求万能索引。

3.2 排序方向与索引方向的一致性

MySQL 8.0 支持降序索引（descending index）。若查询为 ORDER BY update_time DESC，而索引定义为 (status, update_time) 默认升序，MySQL 仍可反向扫描索引来满足 DESC，因此单列方向通常不构成障碍。但多列混合方向（如 ORDER BY a ASC, b DESC）则要求索引方向与之匹配，否则仍会 filesort。这一点在 MySQL 8.0 官方文档“Descending Indexes”中有说明。

3.3 覆盖索引：把回表成本压到零

若联合索引包含查询所需的全部列（SELECT 列 + WHERE 列 + ORDER BY 列），则成为覆盖索引，扫描索引即可返回结果，无需回表。对列表页而言，这通常意味着把 id, title, status, update_time 都纳入索引。代价是索引变宽、写入放大、缓冲池占用增加。

本文评述：覆盖索引是“排序字段进索引”的最强形态，但它把问题从“排序成本”转移到了“索引维护成本”。是否值得，取决于读写比。读多写少的列表页，覆盖索引收益显著；写多读少的表，宽索引可能拖垮写入吞吐。这一权衡在下一节以矩阵形式给出。

3.4 一个常见的误判：索引存在 ≠ 排序被消除

很多排查者看到“update_time 上有索引”就认为排序已优化，但若该索引是单列索引 (update_time)，而 WHERE 是 status=1，优化器面临两难：走 status 索引过滤再排序，或走 update_time 索引顺序扫描再过滤。后者在 status 选择性差时可能扫描大量行，优化器往往选择前者并 filesort。因此单列排序索引常常“存在但无用”，必须与过滤列组成联合索引才可能生效。验证方法见 5.1 节的 EXPLAIN 判读。

四、决策矩阵：什么情况下该塞、什么情况下不该塞

4.1 四个判断维度

结合前文主线，把决策拆为四个可观测维度：命中行数 R、过滤列选择性、查询列宽（是否可覆盖）、写入频率。下表为笔者归纳的决策矩阵（经验模型，非官方标准），用于快速定位方案方向，最终仍需 EXPLAIN 与压测验证。

4.2 回表代价的量化直觉

假设 (status, update_time) 联合索引可消除排序，但查询需要 title 等非索引列，则每取一行都要按主键回表一次。若命中 20 万行、LIMIT 20，理论上只需回表 20 次（优先队列只保留前 20），回表成本可忽略；但若查询是导出或深分页，回表次数随输出行数增长。本文评述：LIMIT 小的时候，联合索引消除排序几乎稳赚；LIMIT 大或导出场景，覆盖索引或延迟关联才是关键。 这解释了为何同一索引在不同接口上表现迥异。

4.3 写入放大的边界

每增加一个二级索引，INSERT/UPDATE/DELETE 都需维护对应 B+ 树。对 update_time 这类每次更新都会变化的列，将其放入联合索引意味着每次业务更新都触发索引键变更（本质是删除+插入），放大写放大与页分裂概率。InnoDB 的 change buffer 可缓解非唯一二级索引的随机写，但对频繁更新的索引列效果有限。因此对高更新频率表，把 update_time 放进索引需评估 TPS 影响，建议在影子库压测写入吞吐后再定。

4.4 一个反直觉结论

并非所有 filesort 都值得消除。当 R 很小（如过滤后仅数百行）时，内存排序的绝对耗时可能只有几毫秒，而新增联合索引带来的写入成本与优化器选择复杂度却是持续的。本文评述：优化的目标是端到端延迟与吞吐，而非 EXPLAIN 里“消灭 filesort”这一指标本身。 把 filesort 当作必须清零的告警，是典型的指标替代目标的误区。

五、工程落地：从诊断到改写的完整操作路径

5.1 第一步：用 EXPLAIN 与 optimizer trace 定位

先看 EXPLAIN 的 Extra 列：出现 Using filesort 即确认排序未被索引满足；再看 rows 估算与 filtered，估算实际参与排序的行数。需要更细的决策依据时，开启 optimizer trace：

SET optimizer_trace = 'enabled=on';
SELECT id, title, status, update_time
FROM biz_list WHERE status = 1
ORDER BY update_time DESC LIMIT 20;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

在 trace 的 filesort 节点可看到是否使用优先队列、排序行数、是否落盘。官方文档“Tracing the Optimizer”给出了字段含义。拓展阅读可参考 MySQL 官方手册 ORDER BY Optimization 与 Optimizer Tracing。

5.2 第二步：设计候选索引并验证

以本文场景为例，候选索引为 (status, update_time)。创建后再次 EXPLAIN，确认 Extra 中 filesort 消失、key 命中新索引、rows 合理。注意：优化器可能因统计信息陈旧而不选新索引，需 ANALYZE TABLE 更新统计。验证清单如下：

• EXPLAIN 的 key 是否为预期索引；
• Extra 是否仍含 filesort；
• 实际执行时间（SHOW PROFILES 或 performance_schema）是否下降；
• 写入吞吐是否受影响（压测对比）。

5.3 第三步：覆盖索引或延迟关联的取舍

若回表成为瓶颈，可考虑覆盖索引 (status, update_time, id, title)；若索引过宽不可接受，则用延迟关联：先用覆盖索引取出主键，再回表取详情。

SELECT b.id, b.title, b.status, b.update_time
FROM biz_list b
JOIN (
  SELECT id FROM biz_list
  WHERE status = 1
  ORDER BY update_time DESC
  LIMIT 20 OFFSET 0
) t ON b.id = t.id
ORDER BY b.update_time DESC;

子查询走覆盖索引完成排序与分页，外层按主键回表仅 20 次。本文评述：延迟关联在深分页时收益更明显，因为 OFFSET 越大，外层回表仍只有 LIMIT 行，而内层排序始终在窄索引上完成。

5.4 第四步：压测与灰度

索引变更属于 DDL，MySQL 8.0 的 Online DDL 对二级索引创建支持 INPLACE 与并发 DML，但仍会占用 I/O 与临时空间。建议在低峰期执行，并使用 ALGORITHM=INPLACE, LOCK=NONE 显式声明。上线后观察慢查询日志与 performance_schema.events_statements_summary_by_digest 中该 SQL 的延迟分布变化。关于 Online DDL 的锁行为，可参考 InnoDB Online DDL Operations。

六、深分页与游标：比加索引更彻底的解法

6.1 OFFSET 的线性成本

LIMIT 20 OFFSET 200000 要求 MySQL 先定位并丢弃前 20 万行，即便排序被索引消除，扫描成本仍随 OFFSET 线性增长。这是分页语义本身的问题，加索引无法根治。本文评述：排序字段入索引解决的是“排序”成本，深分页解决的是“跳过”成本，两者是不同维度的问题，不能混为一谈。

6.2 游标分页（keyset pagination）

用上一页最后一行的排序键作为下一页起点，把 OFFSET 转为 WHERE 条件：

-- 第一页
SELECT id, title, update_time FROM biz_list
WHERE status = 1
ORDER BY update_time DESC, id DESC LIMIT 20;

-- 下一页（last_time/last_id 为上一页末行值）
SELECT id, title, update_time FROM biz_list
WHERE status = 1
  AND (update_time, id) < (?, ?)
ORDER BY update_time DESC, id DESC LIMIT 20;

配合 (status, update_time, id) 索引，每页都从索引定位起点，扫描量恒定为 LIMIT 行，与页码无关。注意排序键需包含唯一列（如 id）以避免 update_time 重复导致跳行或重复。该方案要求接口从“页码”改为“游标”，属于语义改造，需前后端协同。关于 keyset pagination 的通用讨论，可参考 Use The Index, Luke：No Offset。

6.3 何时仍需要页码

若产品必须支持“跳到第 N 页”，游标分页不适用。此时可考虑：限制最大可跳页数、用覆盖索引 + 延迟关联降低单页成本、或引入搜索引擎/OLAP 侧做排序分页。本文评述：技术方案应服从产品语义，强行把页码改成游标可能引发体验与需求冲突，需在架构评审阶段明确取舍。

七、对比与局限：索引不是免费的午餐

7.1 三种方案横向对比

7.2 局限与失效条件

联合索引消除排序并非万能：WHERE 含范围条件会破坏后续列有序性；动态多条件查询难以用单一索引覆盖；ORDER BY 含表达式或函数（如 ORDER BY DATE(update_time)）无法利用普通索引；字符集与排序规则不一致也可能导致索引失效。这些边界条件应在设计阶段逐一核对，而非上线后被动救火。

7.3 与优化器演进的互动

MySQL 8.0 引入直方图统计（histogram），可改善非索引列选择性估算，间接影响排序路径选择；8.0.20 后移除 max_length_for_sort_data，排序形态由优化器内部估算决定。本文评述：优化器越“聪明”，人工经验规则的适用边界越窄，因此任何索引决策都应以实测 EXPLAIN 与 trace 为准，而非套用旧版本经验。关于直方图，可参考 Optimizer Statistics。

八、结论与展望

8.1 结论：回到主线

排序字段该不该塞进联合索引，答案不是“该”或“不该”，而是在“消除排序”与“控制回表/写入放大”之间找到当前负载下的最优点。对 40 万行、过滤列等值、LIMIT 较小的列表页，(filter, sort) 联合索引通常是高性价比选择；对深分页、导出、写密集场景，游标分页、延迟关联、覆盖索引需组合使用。判断依据应来自 EXPLAIN、optimizer trace 与压测，而非 EXPLAIN 里是否还有 filesort 这一单一指标。

8.2 展望

随着 MySQL 优化器持续演进、以及业务侧对列表页实时性要求提升，排序分页的解法正在从“单库索引调优”走向“读写分离 + 搜索/OLAP 侧排序 + 游标语义”的组合架构。本文评述：索引设计仍是第一道防线，但它解决的是单机单表内的有序性问题；当数据规模与查询模式超出单机边界时，架构分层比继续堆索引更可持续。工程团队应建立“排序成本可观测”的机制——把 filesort 次数、排序行数、临时文件落盘量纳入监控，让阈值型劣化在爆发前被看见。