在 MySQL 的日常使用和面试中，排序是一个绕不开的核心话题。无论是 ORDER BY 还是 GROUP BY，都可能触发排序操作。而 MySQL 处理排序的方式主要分为两种：索引排序和 filesort。理解它们的原理和性能差异，不仅能帮助你在面试中脱颖而出，更能指导你写出真正高效的 SQL。
一、什么是索引排序？
索引排序，顾名思义，就是利用 B+ 树索引本身的有序性来避免额外的排序操作。在 InnoDB 中，索引的叶子节点是按照索引列的顺序排列的，因此如果查询的 ORDER BY 子句能够与索引的顺序匹配，MySQL 就可以直接按顺序读取索引，而无需再对结果集进行排序。
例如，假设有一张表 t1，包含列 id（主键）、a、b、c，并且在 (a, b) 上有一个联合索引。
SELECT * FROM t1 ORDER BY a, b;
这条查询可以直接利用联合索引 (a, b) 的有序性，按顺序读取数据，无需额外排序。此时 EXPLAIN 的 Extra 列不会出现 Using filesort。
索引排序的优势非常明显：
- 零额外排序开销：不需要在内存或磁盘中对结果集进行排序。
- 天然有序：数据按索引顺序返回，效率极高。
- 适合 LIMIT：如果配合
LIMIT，可以只读取前 N 条记录，避免全表扫描。
但索引排序也有严格的限制条件：
ORDER BY的列顺序必须与索引列的顺序完全一致（最左前缀原则）。- 所有排序列的排序方向（ASC/DESC）必须与索引定义一致（MySQL 8.0 之前不支持降序索引，8.0+ 支持）。
- 如果
WHERE条件中使用了索引的前缀列，排序仍可利用索引；但如果WHERE条件导致索引失效，排序也无法利用索引。 - 如果查询涉及多个表的连接，索引排序通常只能用于驱动表。
二、什么是 filesort？
当无法利用索引完成排序时，MySQL 就会使用 filesort（文件排序）。注意，filesort 并不一定意味着使用磁盘文件，它只是一种排序算法的名称，可能在内存中完成，也可能借助临时文件。
filesort 主要分为两种算法：
1. 双路排序（Two-Pass Sort）
这是较老的算法。它首先根据 WHERE 条件取出需要的行，然后对每一行提取排序键和行指针（rowid），在内存中对这些键值对进行排序。排序完成后，再根据 rowid 回表读取完整的行数据。
双路排序的缺点是需要回表两次（一次取排序键，一次取完整数据），导致较多的随机 I/O。在 MySQL 4.1 之前，这是唯一的 filesort 算法。
2. 单路排序（Single-Pass Sort）
从 MySQL 4.1 开始，引入了单路排序。它一次性将查询需要的所有列（包括排序键和 SELECT 的列）都读入内存，然后在内存中直接排序，最后返回结果。这样只需要一次回表（如果用的是覆盖索引则无需回表），减少了 I/O 次数。
单路排序的效率通常高于双路排序，但它需要更多的内存。MySQL 通过系统变量 sort_buffer_size 控制排序缓冲区的大小。如果排序的数据量超过了 sort_buffer_size，MySQL 会将数据分成多个临时文件，进行外部归并排序，这会导致性能急剧下降。
那么，MySQL 如何选择使用哪种算法？实际上，MySQL 优化器会根据查询的列、行大小、max_length_for_sort_data 等参数来决定。如果查询的列总长度小于 max_length_for_sort_data，则使用单路排序；否则使用双路排序。
三、性能差异对比
| 维度 | 索引排序 | filesort |
|---|---|---|
| 排序开销 | 无额外排序 | 需要内存/磁盘排序 |
| I/O 次数 | 顺序读取，I/O 少 | 可能涉及随机 I/O 和外部排序 |
| 内存消耗 | 低 | 高（依赖 sort_buffer_size） |
| 适用场景 | ORDER BY 与索引顺序匹配 | 无法利用索引排序 |
| LIMIT 优化 | 天然支持，效率极高 | 需要先排序全部数据再取前 N 条 |
| 稳定性 | 高 | 数据量大时性能波动大 |
从表中可以看出，索引排序在绝大多数情况下都优于 filesort。但在实际业务中，我们无法为每一种排序组合都建立索引，因此 filesort 仍然广泛存在。
四、如何优化 filesort？
既然 filesort 无法完全避免，我们可以通过以下手段来优化：
- 增大
sort_buffer_size：让更多排序在内存中完成，避免外部归并排序。但不宜过大，否则会浪费内存。 - 调整
max_length_for_sort_data：适当增大可以让 MySQL 选择单路排序，减少回表次数。 - 使用覆盖索引：如果查询的列都能从索引中获取，filesort 就不需要回表，效率会大幅提升。
- 优化 SQL：尽量避免
SELECT *，只取需要的列，减少排序数据量。 - 合理设计索引：让
ORDER BY和WHERE都能利用索引，是根本的解决之道。
五、面试常见追问
Q1：Using filesort 一定很慢吗？
不一定。如果排序的数据量很小，filesort 在内存中完成，速度也很快。但如果数据量大且无法利用索引，性能就会明显下降。
Q2：为什么 ORDER BY 有时能用索引，有时不能用？
关键看 ORDER BY 的列是否满足索引的最左前缀原则，以及排序方向是否一致。此外，如果 WHERE 条件使用了范围查询，也可能导致索引排序失效。
Q3：MySQL 8.0 对排序有哪些改进？
MySQL 8.0 支持降序索引，使得 ORDER BY a ASC, b DESC 这样的混合排序也能利用索引。此外，8.0 对 filesort 算法也做了优化，例如引入了更高效的排序算法。
六、总结
索引排序和 filesort 是 MySQL 排序的两大核心机制。索引排序利用 B+ 树的有序性，几乎零成本；而 filesort 则需要在内存或磁盘中进行额外排序，性能受数据量和缓冲区大小影响较大。在实际开发中，我们应优先考虑通过合理的索引设计来避免 filesort，同时在无法避免时，通过调整参数和优化 SQL 来减轻其影响。
理解这两者的差异，不仅是面试中的高频考点，更是写出高性能 SQL 的必备技能。希望本文能帮助你彻底搞懂 MySQL 排序的底层逻辑。
未经允许不得转载：任鹏个人博客 » MySQL 排序算法：filesort 与索引排序的性能差异
