MySQL Using temporary / Using filesort 实测：EXPLAIN ANALYZE 怎么把 142ms 的排序压到 5ms
在 MySQL 8.0.46 测试实例、50 万行自造订单表上复现两类最常见的 Extra：Using filesort 与 Using temporary; Using filesort。用 EXPLAIN ANALYZE 对比加索引前后耗时，说明什么时候排序能被索引干掉、什么时候临时表躲不掉，附只读巡检 SQL 和可复跑脚本。
status='paid' ORDER BY amount 在没索引时 Extra 是 Using filesort，EXPLAIN ANALYZE 两轮 142ms / 153ms；加上 (status, amount) 后变成 Backward index scan，4.74–5.25ms，filesort 没了。GROUP BY customer_id, status ORDER BY SUM(amount) 在日期范围上两轮 171–226ms（Using temporary; Using filesort），覆盖索引后仍有临时表，但落到 68.8–72.8ms。聚合结果上的 ORDER BY 往往消不掉临时表，别指望「再加一个索引」就完事。
适用对象
- Using filesort 不等于「一定慢到不能用」，它只是说排序没走索引顺序。有 LIMIT 的 top-N 往往还能接受；全表过滤后再排几十万行才会明显拖后腿。
- 能被索引直接替代的排序最划算：WHERE status=? ORDER BY amount DESC LIMIT n，复合索引 (status, amount) 实测从 142–153ms 降到 4.74–5.25ms，Extra 变成 Backward index scan。
- ORDER BY 聚合结果（SUM/COUNT）时，InnoDB 必须先算出临时结果再排：覆盖索引能减扫描成本（226ms→73ms 量级），但 Extra 里 Using temporary; Using filesort 通常还在。
- 看 TREE 格式的 EXPLAIN ANALYZE 比只看 Extra 更清楚：Sort / Aggregate using temporary table / Covering index range scan 各自的 actual time 一眼能对上。
WHERE status='paid' ORDER BY amount：filesort 能被索引整段换掉
语句：SELECT id, customer_id, amount, created_at FROM orders WHERE status='paid' ORDER BY amount DESC LIMIT 50。传统 EXPLAIN 的 Extra 是 Using where; Using filesort，type=ALL，估计要扫整表再过滤。
EXPLAIN ANALYZE 两轮：actual time 142ms / 153ms。树里写得很直白——先 Table scan 50 万行，Filter 出约 12.5 万行 paid，再 Sort … limit input to 50。SHOW SESSION STATUS 也能对上：Sort_scan=1、Sort_rows=50。
加上索引 idx_status_amount (status, amount) 之后，同一条语句 Extra 变成 Backward index scan，key=idx_status_amount。ANALYZE 两轮 5.25ms / 4.74ms，树里只剩 Index lookup … (reverse)，不再出现 Sort 节点。这是我最想先教会同事的一招：等值条件 + 排序列，合成最左前缀，LIMIT 小时几乎是「读索引尾巴」。
本节判断
- filesort 消失不代表「永远不用排序」：换一个不能进索引的表达式（例如 ORDER BY amount+1）又会回来。
- 索引是写放大换读：50 万行表上建 (status, amount) 很轻，但宽表、高频写入要自己量一下 INSERT 变慢多少。
| 阶段 | Extra / 访问方式 | 第 1 轮 | 第 2 轮 | Sort 节点 |
|---|---|---|---|---|
| 无 idx_status_amount | Using where; Using filesort | 142 | 153 | 有（top-N） |
| 有 (status, amount) | Backward index scan | 5.25 | 4.74 | 无 |
GROUP BY + ORDER BY SUM(amount)：临时表经常消不掉
语句：按 created_at 三个月窗口，GROUP BY customer_id, status，再 ORDER BY SUM(amount) DESC LIMIT 20。Extra 一开始就是 Using index condition; Using temporary; Using filesort（用上了已有的 idx_created 做范围）。
ANALYZE 两轮 226ms / 171ms：Index range scan 读出约 7.7 万行 → Aggregate using temporary table（2300 个分组）→ Sort revenue DESC。临时表在这里是必须的，因为排序键是算出来的聚合值，不是表上的列。
再加覆盖索引 idx_created_cust_status (created_at, customer_id, status, amount) 后，扫描变成 Covering index range scan，两轮 72.8ms / 68.8ms。Extra 仍写着 Using temporary; Using filesort——这很正常。覆盖索引减的是回表和读行成本，不是取消「先聚合再按聚合值排序」这一步。
本节判断
- 若业务可以接受「按 customer_id 排」而不是按 SUM 排，有时能改成索引顺序 GROUP BY，临时表压力会小很多——那是改需求，不是改索引魔术。
- Created_tmp_tables 在会话里对这条语句会 +1；若同时看到 Created_tmp_disk_tables 上涨，说明临时表落到了磁盘，需要另看 tmp_table_size / max_heap_table_size（本次 2300 行分组仍在内存）。
| 阶段 | Extra 要点 | 第 1 轮 | 第 2 轮 | 扫描 |
|---|---|---|---|---|
| 仅 idx_created | Using temporary; Using filesort | 226 | 171 | index range + 回表读列 |
| 覆盖索引 | Using index; Using temporary; Using filesort | 72.8 | 68.8 | Covering index range scan |
Extra 三个词，我实际怎么用
Using filesort：结果顺序不能由当前索引直接提供。先看有没有 LIMIT、扫描行数，再用 ANALYZE 看 Sort 节点的 actual time。
Using temporary：中间结果进了临时表，常见于 GROUP BY / DISTINCT / UNION。结合 Created_tmp_disk_tables 判断是否落盘。
Using index：覆盖索引，二级索引里已经有 SELECT 需要的列。它和 filesort/temporary 可以同时出现，像实测二那样。
从 Extra 到改法
- 01
1
先跑 EXPLAIN ANALYZE
传统 Extra 只定性；TREE 格式的 actual time 告诉你时间花在 Scan、Aggregate 还是 Sort。
- 02
2
能对齐索引的 ORDER BY 优先
等值/范围条件列在前，排序列在后；有 LIMIT 时收益最大，像实测一那种 30 倍差距。
- 03
3
聚合排序别硬刚
ORDER BY SUM(...) 消不掉临时表时，目标改成减少扫描（覆盖索引、缩小时间窗、预聚合表），而不是死磕「去掉 Using temporary」。
- 04
4
用只读 SQL 找线上候选
events_statements_summary_by_digest 里看 SUM_CREATED_TMP_* 和 SUM_SORT_*，先抓总耗时最高的那几条再逐条 ANALYZE。
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
Using filesort 是不是一定要消灭？
不一定。有 LIMIT 的小结果排序成本往往可接受。优先消灭「大扫描 + 大排序」；像本次 50 行 top-N，没索引时 140ms 级，有索引 5ms 级，这种才值得立刻改。
调大 sort_buffer_size 能去掉 Using filesort 吗？
不能。sort_buffer_size 影响的是排序是否需要多路归并（Sort_merge_passes），不是「还要不要 filesort」。真正去掉 filesort 通常靠索引顺序或改写 SQL。
为什么加了覆盖索引还有 Using temporary？
因为 ORDER BY 的是 SUM(amount) 这种算出来的值。覆盖索引只让扫描更快，聚合结果仍然要进临时表再排序。这是预期行为，不是索引没生效。
这些毫秒能直接套到生产吗？
不能。这是本机测试实例和自造 50 万行数据。结论看相对关系：索引替代排序、覆盖减扫描、聚合 ORDER BY 难消临时表。生产请用同一套 EXPLAIN ANALYZE 在只读副本上复测。