ORDER BY 的实现原理：面试官最爱问的排序优化
大家好，我是一名拥有10年以上经验的DBA老兵（没有那多）。
做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。
让我们开始今天的第13天内容。
背景引入
💡 写 SQL 天天用 ORDER BY，但你有没有想过——MySQL 到底是怎么排的？
来个灵魂拷问：你写 ORDER BY create_time DESC 的时候，MySQL 是不是一定会做排序？
答案是：不一定。
如果命中了索引，MySQL 直接从 B+ 树里按顺序读数据，连排都不排。如果没命中，它就老老实实走 filesort——在内存甚至磁盘上排序。
今天的目标：搞懂 MySQL 排序的两种方式、它们的底层原理，以及面试官最爱的三个排序优化问题。
核心概念
排序方式一：利用索引排序（不费吹灰之力）
B+ 树索引天生就是有序的。叶子节点上的数据按索引列从小到大排好，就像图书馆的书架一样，书脊上的编号已经是顺序的了。
所以如果 MySQL 发现你的 ORDER BY 条件正好匹配了某个索引，它就懒得再排一遍了——直接从索引里按顺序取数据，一步到位。
-- 假设有个联合索引 idx(a, b)
SELECT * FROM t WHERE a = 1 ORDER BY b;
-- 不用排序！因为 idx 已经按 a,b 排好了
你 EXPLAIN 会看到 Extra: Using index，没有 Using filesort，说明 MySQL 直接从 B+ 树按顺序取数据，压根没排序。后面实战案例会真的演示一遍。
关键条件：
- ORDER BY 的字段必须和索引的最左前缀匹配
- 不能混用 ASC 和 DESC（除非 MySQL 8.0 的降序索引）
- 排序字段不能有函数包裹，比如
ORDER BY YEAR(create_time)肯定走不了索引
面试必问：
- 什么时候 ORDER BY 可以走索引？什么时候不行？
- 如果 WHERE 条件已经用了一个索引，ORDER BY 还能再用另一个索引吗？
- 联合索引下，ORDER BY 字段放在什么位置才能避免 filesort？
📝 面试解答：
Q: 什么时候 ORDER BY 能走索引？
满足两个条件：
1）ORDER BY 的字段必须是某个索引的一部分；
2）WHERE 条件用到的索引键和 ORDER BY 的索引键能配合起来（最左前缀原则）。
简单说就是——MySQL 能在同一个索引上搞定"查"和"排"两件事。
Q: WHERE 条件用了一个索引，ORDER BY 还能用另一个吗？
不能。MySQL 一次查询最多只能用一个索引来排序，如果 WHERE 条件已经用了索引 A，那 ORDER BY 就没法再用索引 B 了，只能走 filesort。
不过你可以建一个覆盖索引，把 WHERE 和 ORDER BY 的字段都包进去。
Q: 联合索引下，ORDER BY 放哪里？
假设联合索引是
(a, b, c)WHERE a = 1 ORDER BY b→ 走索引（a 定值，b 有序）。WHERE a > 1 ORDER BY b→ 不会走索引排序（a 范围查，b 无序了）。
记住：范围查询之后的所有字段，都不再按索引顺序。
排序方式二：Filesort（真正干活的排序）
走不了索引的时候，MySQL 就只能自己动手了。
你可以在 EXPLAIN 的 Extra 列看到 Using filesort——看到这个就要警觉了。
Filesort 有两种算法：
双路排序（Two-pass / Original）
MySQL 4.1 之前的古老方案，但场景合适时仍然在用。
流程：
- 取排序字段 + 行指针（row ID）到 sort buffer
- sort buffer 满了就 quicksort 排一次，写入临时文件
- 所有数据读完，对临时文件做归并排序
- 最后拿着排好的 row ID 回表取完整数据
问题很明显：排了两趟。第一趟排序，第二趟回表取数据。回表是随机 IO，数据量大时非常慢。
单路排序（Single-pass / Modified）
MySQL 4.1 之后引入，核心优化是：少回一次表。
流程：
- 取所有需要的字段（不光是排序字段）到 sort buffer
- 在 buffer 里排完直接返回
好处：不回表，没有随机 IO。
坏处：sort buffer 占用更大，如果 buffer 不够，就会疯狂用磁盘临时文件——反而更慢。
MySQL 怎么选这两种算法？看 sort buffer 能不能装下所有要取的字段。如果 max_length_for_sort_data（8.0 已废弃，由优化器自动判断）大于所有字段总长，就用单路，否则用双路。
记住：sort buffer 够大就用单路，不够就用双路。
实战案例
场景一：看看你的查询走了哪种排序
建一个测试表，看看 filesort 到底啥时候出现：
CREATE TABLE `orders` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`amount` decimal(10,2) NOT NULL,
`create_time` datetime NOT NULL,
`status` tinyint NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB;
-- 先插入测试数据（造几千行数据，效果才明显）
INSERT INTO orders (user_id, amount, create_time, status)
SELECT
FLOOR(RAND() * 1000) + 1,
ROUND(RAND() * 1000, 2),
NOW() - INTERVAL FLOOR(RAND() * 365) DAY,
FLOOR(RAND() * 4)
FROM information_schema.CHARACTER_SETS a
CROSS JOIN information_schema.CHARACTER_SETS b
LIMIT 5000;
-- 确认数据量
SELECT COUNT(*) FROM orders;
-- 增加覆盖索引，让 ORDER BY 可以走索引且不回表
ALTER TABLE orders ADD KEY idx_covering (create_time, id, amount);
-- 查询 A：走索引排序（Extra 显示 Using index）
EXPLAIN SELECT id, amount FROM orders
ORDER BY create_time LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: index
possible_keys: NULL
key: idx_covering
key_len: 14
ref: NULL
rows: 10
filtered: 100.00
Extra: Using index
-- 查询 B：走 filesort（Extra 出现 Using filesort）
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
ORDER BY create_time LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_user_id
key: idx_user_id
key_len: 4
ref: const
rows: 5
filtered: 100.00
Extra: Using index condition; Using filesort
对比很明显：查询 A 的 Extra 是 Using index，直接走索引顺序，不需要额外排序。查询 B 的 Extra 是 Using filesort——user_id = 100 走了 idx_user_id 索引过滤，但 ORDER BY 的 create_time 在另一个索引上，MySQL 没法同时用两个索引，只能 filesort。
场景二：Filesort 深度诊断（用优化器追踪）
想知道 MySQL 到底用的哪种 filesort 算法？EXPLAIN 看不出来，得看 optimizer trace：
-- 开启追踪
SET optimizer_trace='enabled=on';
-- 执行查询
SELECT user_id, amount, status FROM orders
WHERE user_id > 0 ORDER BY amount DESC;
-- 查看追踪结果
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
在输出里找到 filesort_summary 部分（下面是真实执行结果）：
"filesort_summary": {
"memory_available": 1048576,
"key_size": 5,
"row_size": 19,
"max_rows_per_buffer": 4428,
"num_rows_estimate": 4428,
"num_rows_found": 1681,
"num_initial_chunks_spilled_to_disk": 0,
"peak_memory_used": 49152,
"sort_algorithm": "std::stable_sort",
"unpacked_addon_fields": "skip_heuristic",
"sort_mode": "<fixed_sort_key, additional_fields>"
}
重点看这几个关键字段：
sort_mode：<fixed_sort_key, additional_fields>是单路排序（不回表），<sort_key, rowid>是双路排序（要回表）。这里是additional_fields，说明查询的字段少，sort buffer 足够了，直接一次性排完num_initial_chunks_spilled_to_disk：等于 0，说明纯内存排序，没有写磁盘临时文件。这代表排序性能很好peak_memory_used：只用了 48KB（49152 字节）就排完了 1681 行数据，sort buffer 完全够用sort_algorithm：std::stable_sort，MySQL 8.0 用的 C++ 标准库稳定排序
避坑指南
⚠️ 真实踩过的坑：
sort buffer 不是越大越好
- 遇到过新人把
sort_buffer_size设成 256M，结果并发 100 个连接就占 25G 内存 - 建议：线上设 2M-4M 就够，配合监控看到
number_of_tmp_files大于 0 再考虑调大
- 遇到过新人把
深分页 + ORDER BY 是性能杀手
ORDER BY create_time LIMIT 100000, 20这种写法，MySQL 得先排 10 万行再扔掉 99980 行- 建议：用游标分页代替 OFFSET，或者用延迟关联（先查主键再 JOIN）
SELECT * 会让 filesort 变慢很多
- SELECT * 会把所有字段都塞进 sort buffer，一个 buffer 只能装更少的行
- 建议：只 SELECT 需要的字段，别偷懒写 *
思考题
🤔 互动时间：
ORDER BY a ASC, b DESC在联合索引(a, b)上能走索引排序吗？为什么？- 如果 sort buffer 里的数据排不下，MySQL 会用哪些排序算法？（提示：quicksort + merge sort）
- 假设你的表有 100 万行，
ORDER BY id LIMIT 10和ORDER BY id LIMIT 999990, 10，性能差几十倍，你能想到原因吗？
总结
🎯 面试考点
- MySQL ORDER BY 的两种实现：索引排序（Using index）和 filesort（Using filesort）
- Filesort 两种算法：单路排序（不回表，省 IO）和 双路排序（省内存）
- 通过
EXPLAIN看有没有Using filesort，通过optimizer_trace看具体的排序模式和临时文件数 - 深分页 + ORDER BY 是性能大坑，用游标分页或延迟关联优化
今天就试一下：打开你的慢查询日志，找一个出现 Using filesort 的 SQL，用今天学的方法分析一下——它是单路还是双路？有没有 number_of_tmp_files > 0？试试能不能通过加索引消灭 filesort？
下期预告：LIMIT 分页的性能优化 —— 面试必问的深分页怎么破？
有问题欢迎评论区交流，明天见！
