今日核心关键词：Using temporary、Using filesort、内部临时表、文件排序、sort_buffer、联合索引、ORDER BY 优化、GROUP BY 隐式排序、单路排序、双路排序
之前我们详细拆解了 InnoDB 的内存架构与三大日志，这些都属于引擎层面的底层原理。这次换一个视角，回到 SQL 优化这个更贴近日常开发的战场，重点聊聊 ORDER BY 的优化策略。
触发点是上周帮同事排查一条慢查询。一个看似普通的报表分页 SQL，数据量并不大，却耗时 3.2 秒。执行 EXPLAIN 一看，Extra 列赫然标注着 Using temporary; Using filesort，两个“警告灯”同时亮起。同事问我这两个标记具体意味着什么，我才意识到，虽然知道它们“不是什么好信号”，但要讲清楚背后的执行机制，真需要系统梳理一番。
我花了不少时间查阅文档、动手做实验，终于把这块内容彻底理清了。接下来，我会从 EXPLAIN 如何解读，到临时表的内部运作，再到排序算法的选择逻辑，一步步把核心原理拆解清楚。
一、EXPLAIN 里的两个“警告灯”
先看一条典型的慢查询：
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;
假设 orders 表上建有 idx_user_id(user_id) 索引，但缺少 (user_id, create_time) 的联合索引。那么，EXPLAIN 的 Extra 列很可能会出现以下两个标记：
Using temporary
这个标记表示 MySQL 在执行查询时，需要创建一张内部临时表来存放中间结果。可以把它想象成做数学题时草稿纸不够用，需要额外拿一张纸来记录中间步骤——这张“草稿纸”就是内部临时表。
Using filesort
这个命名容易产生误解，它并不代表“文件排序”，而是指 MySQL 无法利用索引的有序性来满足 ORDER BY，必须自行执行一次额外的排序操作。称之为“额外排序”更为贴切。
需要注意：出现一个标记就要引起警惕，两个同时出现则必须着手优化。当查询既需要建临时表又需要额外排序时，一旦数据量增大，性能问题几乎必然出现。
二、内部临时表到底是怎么运作的？
2.1 哪些操作会触发临时表？
触发临时表的场景远比你想象的要多，并不局限于 GROUP BY：
GROUP BY分组聚合DISTINCT去重UNION（不含 ALL 时，需要去重）- 派生表 / 子查询
- 多表 JOIN
- ORDER BY（排序字段不在驱动表索引中）
2.2 内存临时表 vs 磁盘临时表
创建临时表时，MySQL 会优先使用 MEMORY 引擎将其保存在内存中。但这有一个前提：临时表的大小不能超过 tmp_table_size 和 max_heap_table_size 两者中较小的那个值。
默认值是 16MB，听起来不小？但如果你的临时表里包含 VARCHAR(5000) 这样的长字段，几百行数据就足以让它超出限制。
一旦超限，MySQL 就会把临时表从内存“降级”为磁盘临时表，转而使用 InnoDB 或 MyISAM 引擎存储。性能差距有多大？当前最快的 NVMe SSD，随机读写延迟约在 100 微秒级别，而内存访问延迟是 100 纳秒级别，两者相差整整一千倍。一旦临时表溢出到磁盘，查询耗时翻几倍是常有的事。
2.3 一个容易忽略的陷阱
MEMORY 引擎有一个硬伤：不支持 BLOB 和 TEXT 类型。如果查询里包含这类字段，MySQL 会直接跳过内存临时表，从一开始就使用磁盘临时表。
这也是为什么在数据库建模时，能用 VARCHAR(200) 就尽量别用 TEXT 的原因之一。并不是说 TEXT 不好，而是在排序和临时表场景下，它会显著压缩你的优化空间。
三、ORDER BY 的两种排序算法
3.1 索引排序（最优解）
如果 ORDER BY 的字段正好在索引中，并且索引的顺序与 ORDER BY 的方向一致，MySQL 可以直接按索引顺序读取数据，完全不需要额外的排序步骤。
-- 假设有联合索引 idx_user_time(user_id, create_time)EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC;-- Extra: 无 Using filesort ✓
这种情况下，EXPLAIN 的 Extra 列不会出现 Using filesort。数据从索引读出来本身就有序，就像按拼音查字典，无需再次排序。
3.2 文件排序（额外排序）
当索引排序无法满足时，MySQL 就必须借助 sort_buffer 来完成额外排序。这里面有两种算法：
单路排序（Single-pass）：一次性将 ORDER BY 需要的所有字段都读进 sort_buffer，在内存中排好序后直接返回。整个过程一次完成，所以叫“单路”。
双路排序（Two-pass）：当 sort_buffer 放不下所有字段时，MySQL 会先只读取排序键和行指针进行排序，然后根据排序后的指针回表提取其他字段。需要两次操作，因此叫“双路”。
那么，MySQL 如何决定使用哪种算法？这取决于 max_length_for_sort_data 参数，默认值是 4096 字节。如果查询涉及的所有字段总长度超过这个值，就使用双路排序；否则使用单路排序。
单路排序看似更优，但同样存在陷阱。如果 SELECT 的字段过多或过长，一次性全部塞进 sort_buffer 会占用大量内存，反倒可能比双路排序更慢。因此，不能简单认为单路一定优于双路，需要根据具体情况判断。
四、GROUP BY 的隐式排序陷阱
4.1 MySQL 5.7 的“贴心”行为
在 MySQL 5.7 及更早版本中，GROUP BY 隐藏了一个 ORDER BY 的效果。也就是说，你写了 GROUP BY status，结果会自动按 status 排序返回。很多开发者并不知道这是“隐式排序”，误以为 GROUP BY 本身自带排序功能。
4.2 MySQL 8.0 的改动
从 8.0 开始，GROUP BY 不再保证返回数据的顺序。官方文档的原话是“不再隐含排序”（no longer implies sorting）。这个改动在升级时坑了不少人。
一个典型场景是：升级到 8.0 之后，报表页面的数据顺序突然乱了，排查了半天才发现是 GROUP BY 的隐式排序特性被移除。添加明确的 ORDER BY 即可解决，但这类陷阱确实防不胜防。
4.3 GROUP BY 与 ORDER BY 双重排序
更棘手的场景是 GROUP BY 和 ORDER BY 同时出现，但排序字段不一致：
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC;
这条 SQL 可能触发两次排序：一次是 GROUP BY 分组所需，一次是 ORDER BY 输出所需。此时 EXPLAIN 中会同时看到 Using temporary 和 Using filesort。
一个明确的建议：无论使用 MySQL 5.7 还是 8.0，在 GROUP BY 后面永远显式加上 ORDER BY。这样能确保代码在不同版本间的行为一致，无需猜测 MySQL 的排序意图。
五、优化实战：一条慢查询从 3.2 秒到 0.05 秒
回到开头提到的那个报表 SQL。
原始 SQL
SELECT order_id, user_id, amount, create_timeFROM ordersWHERE user_id = 100ORDER BY create_time DESCLIMIT 20;
原始 EXPLAIN
type: refkey: idx_user_idExtra: Using where; Using temporary; Using filesortrows: 18543
查询走了 idx_user_id 索引，但索引里没有 create_time。因此，MySQL 需要先根据 user_id 找到所有匹配的行并放入临时表，再对临时表进行文件排序。数据量一大，性能瓶颈就出现了。
优化思路
索引设计有一个公认的“黄金公式”：WHERE 字段 → GROUP BY 字段 → ORDER BY 字段，按这个顺序放入联合索引。
这条 SQL 中：
- WHERE 条件是
user_id - ORDER BY 是
create_time DESC
所以，添加一个联合索引：
ALTER TABLE orders ADD INDEX idx_user_time(user_id, create_time);
优化后 EXPLAIN
type: refkey: idx_user_timeExtra: Using index conditionrows: 18543
Using temporary 和 Using filesort 都消失了。虽然 rows 数相同，但查询不再需要创建临时表和额外排序，直接按索引顺序读取数据即可。
执行耗时对比
优化前: 3.2 秒优化后: 0.05 秒
64 倍的性能提升，仅仅是一个索引的差别。
sort_buffer_size 调优
在某些情况下，你无法添加索引（例如 ORDER BY 的字段是动态计算的）。此时可以考虑调大 sort_buffer_size。但务必注意：这个参数是每个线程独立分配的。设置 2MB，100 个并发线程就是 200MB 的内存开销。
因此，并非越大越好，需要根据实际并发量来规划。通常，OLTP 场景下，256KB 到 2MB 就足够了；对于特殊的 OLAP 报表场景，可以适当加大。
六、快速诊断 Checklist
下次在 EXPLAIN 中看到“警告灯”，可以从以下几个方面审视：
- Extra 有 Using temporary： 优先检查 GROUP BY / DISTINCT / 子查询。看能否改写 SQL 或通过索引覆盖来避免。
- Extra 有 Using filesort： 确认 ORDER BY 字段是否被索引覆盖，以及索引顺序与排序方向是否一致。
- 两个同时出现： 首先考虑联合索引（WHERE + GROUP BY + ORDER BY 组合），实在不行再调整
sort_buffer_size。 - 临时表溢出磁盘： 关注
Created_tmp_disk_tables这个状态值。如果它持续增长，说明tmp_table_size不够用了。同时，SELECT *是临时表的大敌，只取需要的字段能显著减小临时表体积。 - GROUP BY 顺序不对： 先确认 MySQL 版本。8.0 必须显式添加 ORDER BY，不要依赖旧版本的行为。
一个值得养成的习惯：每次编写包含 GROUP BY 或 ORDER BY 的查询，写完后第一时间执行一遍 EXPLAIN，确认 Extra 那栏是否干净。一开始会觉得繁琐，但比起上线后再排查，这个步骤能节省大量时间。
七、新手避坑清单
- ✅ EXPLAIN 看到 Using temporary / Using filesort，第一反应是检查索引覆盖。
- ✅ 联合索引设计公式：WHERE + GROUP BY + ORDER BY 字段组合。
- ✅ ORDER BY 混合 ASC / DESC 时，确保索引方向与之对应（MySQL 8.0 支持 DESC 索引）。
- ✅ GROUP BY 在 8.0 不再隐含排序，不要依赖旧行为。
- ✅ 避免 SELECT *，只选需要的字段以减少临时表溢出磁盘的概率。
- ✅
tmp_table_size默认 16MB，监控Created_tmp_disk_tables观察是否有频繁溢出。 - ✅
sort_buffer_size每线程独立分配，设置过大在高并发下可能导致 OOM。 - ✅ 包含 BLOB/TEXT 的查询会跳过内存临时表，建表时能用 VARCHAR 就尽量别用 TEXT。
说到底，Using temporary 和 Using filesort 这两个标记并不是“错误”，而是 MySQL 在告诉你：“我没办法利用索引的捷径，只能老老实实干活了。” 优化的目标，就是用巧妙的索引设计，帮 MySQL 把这些“体力活”给“偷懒”掉。
本文实验基于 MySQL 8.0。临时表和排序行为在不同版本间存在差异，尤其是 GROUP BY 隐式排序在 8.0 被取消，建议在自己环境中通过 EXPLAIN 验证。
