排序是 SQL 中极常见的操作，对应 ORDER BY 子句。数据库执行排序有两种路径：利用索引天然有序性直接返回，或者将数据取出来在内存或磁盘上单独排序。后者通常被称为“文件排序”（filesort），但名字有误导性——它不一定真的写磁盘，只是在内存不足时才会借助临时文件。关键点在于：能利用索引避免 filesort 就应尽量利用，当无法利用时再考虑如何减小 filesort 的开销。
13.5.1 文件排序的内部机制
当查询无法利用索引直接按序返回结果时，MySQL 会启动 filesort。其基本原理是：
- 根据 WHERE 条件筛选出需要参与排序的记录。
- 将每条记录需要排序的字段以及必要的回表字段“复制”到一个排序缓冲区（
sort_buffer_size，默认 256KB）中。 - 在缓冲区内进行快速排序。
- 如果排序数据量超出
sort_buffer_size，则将数据分块排序，每块排完后写入临时文件，最后将多个有序块归并合并，得到全局有序结果。 - 排序完成后，如果需要返回的列没有完全包含在排序缓冲区中，则会根据主键回表获取其余列。这个过程会触发大量随机 I/O，是 filesort 昂贵的主要原因。
MySQL 有两种 filesort 模式（由 max_length_for_sort_data 参数控制，8.0 已简化为一种行为）：
- 全字段排序（单路排序）：将查询所需的所有列一次性放入排序缓冲区，排序后直接返回。优点是不需要二次回表；缺点是单条记录占用空间大，缓冲区能装的记录数少，容易超出内存强制写临时文件。
- 仅排序字段+主键排序（双路排序）：只将排序列和主键值放入缓冲区，排序完成后根据主键回到原表查询其他列。优点是缓冲区可容纳更多记录；缺点是生成结果前需要回表，造成大量随机 I/O。
在 MySQL 8.0 中，对于 InnoDB 表，默认使用全字段排序，因为一般建议把 sort_buffer_size 设置合理，避免二次回表。理解这些内部行为，只是为了让你感知：一旦出现 filesort 且数据量大，排序就会成为性能杀手，尤其是在涉及 TEXT/BLOB 类大字段时。
13.5.2 利用索引天然有序性避免 filesort
索引中所有行本就按照索引键的顺序排列。如果 SQL 的排序要求与某个索引的键顺序完全一致，优化器就可以直接沿着该索引顺序扫描，天然得到有序结果，不需要任何额外排序。这通常对应 EXPLAIN 的 Extra 字段中没有 “Using filesort” 的情况。
满足索引排序需要两个关键条件：
- 排序列必须是某个索引的最左前缀连续列，且排序方向一致（全 ASC 或全 DESC，MySQL 8.0 开始支持部分混合方向，但前提是索引定义字段与排序字段顺序相同且方向可对应）。
- 不能有跨越索引顺序的过滤条件，比如
WHERE a = 1 ORDER BY c使用索引(a,b,c)虽然 a 是等值，但跳过了 b 直接按 c 排序，一般情况下无法利用索引排序（8.0 有条件下推后仍可能排序）。更安全的写法是让排序列紧接在等值条件之后。
经典例子：表 orders 有索引 idx_user_time(user_id, create_time)。
-- 可以走索引排序，Extra 无 Using filesort
SELECT * FROM orders
WHERE user_id = 100
ORDER BY create_time DESC;这个查询中，user_id 是等值，之后就紧接 create_time 排序。InnoDB 会直接在索引 idx_user_time 上定位到 user_id=100 的第一条记录，然后向左或向右扫描叶子节点，天然获得按 create_time 降序的结果。
但如果写成：
SELECT * FROM orders
WHERE user_id = 100
ORDER BY amount DESC;amount 不在索引中，优化器只能先根据 user_id 取出所有订单，再对 amount 进行 filesort，Extra 中必然出现 Using filesort。
13.5.3 联合索引设计对排序的支撑
联合索引对排序的支撑非常强大，但需要遵循“最左前缀 + 中间不间断”的原则。如果排序字段能构成某个索引的前缀（可以包含等值条件占用的列），就可以避免 filesort。
假设有如下索引：INDEX(a, b, c)。
查询能否利用索引排序的判断如下：
ORDER BY a✔（利用索引排序）ORDER BY a, b✔ORDER BY a DESC, b DESC✔（方向一致且顺序匹配）WHERE a = 1 ORDER BY b✔（a 被等值消耗后，b 处于索引第二轮有序）WHERE a = 1 AND b = 2 ORDER BY c✔WHERE a = 1 ORDER BY c✘（跳过了 b，通常 filesort）WHERE a > 1 ORDER BY b✘（范围条件后，索引对 b 不再保证有序）ORDER BY b, c✘（不满足最左前缀）ORDER BY b✘
所以，如果你频繁需要对某组字段排序，且这些字段上的过滤条件基本都是等值，那么把它们设计成一个联合索引，将排序列放在最后，往往能一举两得：既加速筛选，又避免排序。
13.5.4 使用覆盖索引进一步优化带排序的查询
如果排序列的索引本身包含查询所需的所有字段（即覆盖索引），那么不但能避免 filesort，还能避免回表，实现最优性能。EXPLAIN 中会同时出现 Using index（覆盖索引）且无 Using filesort。
典型场景：分页查询中经常需要对时间排序并取少量列。
SELECT id, create_time, title
FROM articles
WHERE category = 5
ORDER BY create_time DESC
LIMIT 20;建立索引 idx_cat_time(category, create_time, title) 后，这个查询从索引上即可取出全部所需列，按顺序扫描索引尾部 20 行，零回表、零排序，效率极高。
13.5.5 排序优化的常见误区与额外建议
误区1：以为只要有索引，ORDER BY 就不用排序。
许多开发者给 order_status 加了个单列索引，却发现 WHERE order_status = 1 ORDER BY create_time 仍然 filesort。因为排序与筛选用的是不同索引，优化器选择了 order_status 索引过滤，但排序仍需额外进行。这种场景应该直接建立 (order_status, create_time) 联合索引。
误区2：对 WHERE IN (...) ORDER BY col 的排序索引过度乐观。
当条件中有 IN 多值时，索引内对应多个区段，MySQL 可能无法利用索引天然的全局有序，可能会退化为 filesort。如果 IN 的列表很短，有时优化器仍能完成一次排序归并，但需要谨慎。
*误区3：忽视 SELECT 带来的排序缓冲区压力。**
如果排序不可避免，SELECT * 会将所有列拉入排序缓冲区，导致缓冲区很快占满并写磁盘。实际查询中只选择需要的列，能显著减少排序开销。
合理利用 sort_buffer_size：对于确实需要排序的会话级大量数据，可以在会话中临时增大 sort_buffer_size（SET SESSION sort_buffer_size = 1610241024），但要避免全局调得过大引起内存压力。
使用索引排序时注意分析 EXPLAIN：如果你在 Extra 中同时看到 Using where; Using index 且无 Using filesort，那是典型的索引提供筛选和排序，且覆盖索引的最优状态。若看到 Using filesort 就应检查是否有优化空间。
总之，排序优化的核心思路是：尽量通过索引的设计来满足业务排序需求，而不是依赖数据库事后排序。这要求你在设计阶段就了解业务查询模式，把常用的排序字段整合进联合索引的尾部。当 filesort 无法避免时，减小排序数据量、避免 SELECT *、以及恰当的缓冲区大小就是最后的防线。