排序与临时表的代价
当 ORDER BY、GROUP BY 或 DISTINCT 缺少可用索引时，MySQL 只能把结果放入排序缓冲区做 filesort，或者建立临时表来完成分组与去重。行数越多代价越大，而且排序与临时表都可能落盘。优化方向是让排序或分组列与索引前缀对齐，从根本上减少需要处理的行数。
基本信息
| 适用 | MySQL 5.7 / 8.0 |
|---|---|
| 判断入口 | EXPLAIN 的 Extra 出现 Using filesort 或 Using temporary |
| 相关参数 | sort_buffer_size、tmp_table_size、max_heap_table_size |
| 方向 | 索引对齐、缩减结果集、减少排序行数 |
优化示例
优化示例
-- 待优化：按 user_id 过滤后按时间倒序，缺少匹配索引
EXPLAIN SELECT id FROM orders WHERE user_id=42 ORDER BY created_at DESC LIMIT 10;
-- 建立与过滤、排序列一致的联合索引
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
-- 复合条件也要求顺序与查询一致，范围条件后的列无法用于排序
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);
-- 分组场景：确认分组列是否有可用索引
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- 显式观察临时表与排序是否消除
EXPLAIN FORMAT=JSON SELECT id FROM orders WHERE user_id=42 ORDER BY created_at DESC LIMIT 10\G信号与对策
| 信号 | 成因 | 对策 |
|---|---|---|
| Using filesort | 排序列没有索引可用 | 按过滤列加排序列建立联合索引 |
| Using temporary | 分组或去重需要临时结构 | 确认分组列有索引，或改用覆盖索引 |
| 排序行数很大 | 过滤条件不够精确 | 先缩小结果集再排序 |
| 临时表落盘 | 结果超过内存阈值 | 缩减结果集，而不是一味调大缓冲区 |
| 排序方向不一致 | 复合索引方向与排序要求不符 | 8.0 可用降序索引解决 |
典型使用场景
- 列表分页接口响应变慢，检查排序是否走 filesort
- 报表分组查询，确认分组列是否有索引支撑
- 上线前 SQL 评审，把排序与分组列为重点检查项
- 大偏移量分页优化，考虑改用游标式分页
- 索引设计：把过滤列与排序列组合成联合索引
小结果集上的 filesort 未必比走索引慢：排序代价由行数、缓冲区与结果大小共同决定，先量化再优化。
盲目调大 sort_buffer_size 或 tmp_table_size 会推高内存压力，参数要按并发量与实例总内存预算设置。