📌 今日关键词：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 里的两个"红灯"
🔥 核心本质：这两个标记说明 MySQL 没法用索引完成你的排序或分组，需要额外的"体力活"。
先看一条最基础的慢查询：
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
不是"文件排序"的意思！这个命名真的误导了好多人，包括我。它真正的意思是：没法利用索引的有序性来满足 ORDER BY，MySQL 得自己做一次额外的排序操作。叫"额外排序"更准确。
注意：出现一个要注意，出现两个必须优化。
两个同时出现，说明你的查询既要建临时表又要额外排序，数据量一大就很容易出问题。
二、内部临时表到底是怎么运作的？
🔥 核心本质：MySQL 处理复杂查询时，需要一个"暂存区"来放中间结果，这个暂存区就是内部临时表。
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 引擎来存。差距有多大呢？我之前做设计师的时候，觉得 SSD 开 PS 已经够快了，但 SSD 随机读写跟内存比还是差两个数量级。从内存读一条数据大概 100 纳秒，从 SSD 读要 100 微秒，中间隔了一千倍。临时表一旦溢出到磁盘，查询耗时翻好几倍都是常事。
2.3 一个容易忽略的坑
MEMORY 引擎有个硬伤：不支持 BLOB 和 TEXT 类型。如果你的查询里包含这类字段，MySQL 会直接跳过内存临时表，从一开始就用磁盘临时表。
这也是我之前提到的，为什么建表的时候能用 VARCHAR(200) 就别用 TEXT 的原因之一。不是说 TEXT 不好，而是在排序和临时表的场景下，它会让你的优化空间变窄不少。
三、ORDER BY 的两种排序算法
🔥 核心本质：MySQL 排序就两条路，要么靠索引，要么靠 sort_buffer，后者又分单路和双路。
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 文件排序（extra sort）
当索引排序搞不定的时候，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 的隐式排序陷阱
🔥 核心本质：GROUP BY 的默认行为在 8.0 前后不一样了，不知道这个改动可能会写出有 bug 的代码。
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 都亮了。
我的建议：GROUP BY 后面永远显式写 ORDER BY，不管用的是 5.7 还是 8.0。 这样代码在不同版本之间行为一致，不用猜 MySQL 会怎么排。
五、优化实战：一条慢查询从 3.2 秒到 0.05 秒
🔥 核心本质：大部分 filesort + temporary 的问题，一根联合索引就能解决。
回到开头说的那条报表 SQL。
原始 SQL
SELECT order_id, user_id, amount, create_time
FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 20;
原始 EXPLAIN
type: ref
key: idx_user_id
Extra: Using where; Using temporary; Using filesort
rows: 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: ref
key: idx_user_time
Extra: Using index condition
rows: 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 / 子查询，能改写就改写，能加索引就加索引
- Extra 有 Using filesort → ORDER BY 字段有没有被索引盖住？方向对不对？
- 两个同时出现 → 先上联合索引（WHERE + GROUP BY + ORDER BY 组合），实在不行再调 sort_buffer_size
- 临时表溢出磁盘 → 看一眼
Created_tmp_disk_tables这个状态值，如果一直在涨就说明 tmp_table_size 不够用。另外 SELECT * 是大忌，只取需要的字段能大幅减少临时表体积 - GROUP BY 顺序不对 → 先确认版本，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 把这些体力活用索引"偷"掉。
👋 我是 数据库小学妹，一个用设计师思维学数据库的转行人。你有没有遇到过 ORDER BY 把查询搞慢的情况？或者 EXPLAIN 看到过什么看不懂的标记？评论区聊聊，大家一起少踩坑！💪
本文实验基于 MySQL 8.0。临时表和排序行为在不同版本间有差异，尤其是 GROUP BY 隐式排序在 8.0 被取消，建议在自己的环境上跑一遍 EXPLAIN 验证。
