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 之前的古老方案，但场景合适时仍然在用。

流程：

1. 取排序字段 + 行指针（row ID）到 sort buffer
2. sort buffer 满了就 quicksort 排一次，写入临时文件
3. 所有数据读完，对临时文件做归并排序
4. 最后拿着排好的 row ID 回表取完整数据

问题很明显：排了两趟。第一趟排序，第二趟回表取数据。回表是随机 IO，数据量大时非常慢。

单路排序（Single-pass / Modified）

MySQL 4.1 之后引入，核心优化是：少回一次表。

流程：

1. 取所有需要的字段（不光是排序字段）到 sort buffer
2. 在 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++ 标准库稳定排序

---

避坑指南

⚠️ 真实踩过的坑：

1. sort buffer 不是越大越好

   • 遇到过新人把 sort_buffer_size 设成 256M，结果并发 100 个连接就占 25G 内存
   • 建议：线上设 2M-4M 就够，配合监控看到 number_of_tmp_files 大于 0 再考虑调大

2. 深分页 + ORDER BY 是性能杀手

   • ORDER BY create_time LIMIT 100000, 20 这种写法，MySQL 得先排 10 万行再扔掉 99980 行
   • 建议：用游标分页代替 OFFSET，或者用延迟关联（先查主键再 JOIN）

3. SELECT * 会让 filesort 变慢很多

   • SELECT * 会把所有字段都塞进 sort buffer，一个 buffer 只能装更少的行
   • 建议：只 SELECT 需要的字段，别偷懒写 *

---

思考题

🤔 互动时间：

1. ORDER BY a ASC, b DESC 在联合索引 (a, b) 上能走索引排序吗？为什么？
2. 如果 sort buffer 里的数据排不下，MySQL 会用哪些排序算法？（提示：quicksort + merge sort）
3. 假设你的表有 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 分页的性能优化 —— 面试必问的深分页怎么破？

---

有问题欢迎评论区交流，明天见！