> ORDER BY 的实现原理：面试官最爱问的排序优化

---

大家好，我是一名拥有10年以上经验的DBA老兵。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第13天内容。

---

## 背景引入

> 💡 写 SQL 天天用 ORDER BY，但你有没有想过——MySQL 到底是**怎么排的**？

来个灵魂拷问：你写 `ORDER BY create_time DESC` 的时候，MySQL 是不是一定会做排序？

**答案是：不一定。**

如果命中了索引，MySQL 直接从 B+ 树里按顺序读数据，连排都不排。如果没命中，它就老老实实走 filesort——在内存甚至磁盘上排序。

**今天的目标**：搞懂 MySQL 排序的两种方式、它们的底层原理，以及面试官最爱的三个排序优化问题。

---

## 核心概念

### 排序方式一：利用索引排序（不费吹灰之力）

B+ 树索引天生就是有序的。叶子节点上的数据按索引列从小到大排好，就像图书馆的书架一样，书脊上的编号已经是顺序的了。

所以如果 MySQL 发现你的 ORDER BY 条件正好匹配了某个索引，它就懒得再排一遍了——直接从索引里按顺序取数据，一步到位。

```sql
-- 假设有个联合索引 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 到底啥时候出现：

```sql
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：

```sql
-- 开启追踪
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` 部分（下面是真实执行结果）：

```json
"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 分页的性能优化 —— 面试必问的深分页怎么破？

**全本合集**：[《每天一个MySQL知识点，百日打怪升级》](https://github.com/martianzhang/mysql_100_days)  


---

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


做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

【MySQL百日打怪升级第13天】ORDER BY 的实现原理：面试官最爱问的排序优化

本文详解MySQL中ORDER BY的两种实现原理：索引排序（免排序，高效）与filesort（内存/磁盘排序），剖析单路/双路算法差异，结合EXPLAIN与optimizer_trace实战诊断，并给出索引设计、sort_buffer调优及深分页避坑指南——直击面试高频考点。（239字）

人工智能

深入解析MySQL ORDER BY排序机制：索引排序与Filesort双路算法对比。掌握联合索引最左前缀原则，避免Using filesort性能损耗。优化深分页查询，提升SQL执行效率。

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

【MySQL百日打怪升级第13天】ORDER BY 的实现原理：面试官最爱问的排序优化-腾讯云开发者社区-腾讯云

【MySQL百日打怪升级第13天】ORDER BY 的实现原理：面试官最爱问的排序优化

【MySQL百日打怪升级第13天】ORDER BY 的实现原理：面试官最爱问的排序优化

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐