SQL数据库执行查询时，当ORDER BY子句无法命中已有索引，就会启用filesort机制完成排序操作。filesort并非字面意义上的文件排序，而是数据库内部实现的排序流程，会根据实际场景选择不同的排序策略来平衡内存使用和排序效率。
filesort的触发条件
当查询满足以下任意一种情况时，就会触发filesort：
- ORDER BY字段没有建立对应的索引，或者索引无法被有效利用
- 查询使用了不同的排序方向，比如ORDER BY col1 ASC, col2 DESC，而索引是统一方向的
- ORDER BY的字段来自不同的表，涉及关联查询时排序字段跨表
- 查询条件中使用了范围查询，导致索引的部分字段无法用于排序
filesort的核心执行步骤
1. 数据读取阶段
数据库首先会执行WHERE条件的过滤，读取符合条件的行数据。此时会根据需要排序的字段和查询需要返回的字段，决定读取的数据内容：
- 如果查询需要的字段较少，会只读取排序字段和需要返回的字段，减少数据量
- 如果查询需要返回大量字段，会读取排序字段和行的引用信息，后续再回表获取完整数据
2. 排序策略选择阶段
filesort会根据内存排序缓冲区sort_buffer_size的配置和待排序数据的大小，选择两种排序策略：
| 排序策略 | 适用场景 | 特点 |
|---|---|---|
| 内存排序 | 待排序数据小于sort_buffer_size | 直接在内存中完成排序，不需要临时文件，效率最高 |
| 外部排序 | 待排序数据大于sort_buffer_size | 将数据分块排序后写入临时文件，最后进行归并排序，性能相对较低 |
3. 排序执行阶段
如果选择内存排序，会直接调用数据库内部的排序算法对数据进行排序，常用的排序算法包括快速排序、归并排序等，会根据数据特征选择最优算法。
如果选择外部排序，执行流程如下：
- 将待排序数据分成多个小块，每个小块的大小不超过
sort_buffer_size - 对每个小块在内存中排序后，写入临时文件
- 对所有临时文件进行多路归并排序，得到最终的有序结果
4. 结果返回阶段
排序完成后，如果是直接读取字段的排序方式，直接返回排序后的结果；如果是读取行引用的排序方式，会根据行引用回表查询完整的字段内容，再返回给客户端。
filesort的优化方向
可以通过调整相关配置和查询语句来优化filesort的性能：
- 适当增大
sort_buffer_size，让更多排序可以在内存中完成，但要注意不要设置过大避免内存浪费 - 尽量让ORDER BY子句命中已有索引，避免触发filesort
- 查询时只返回需要的字段，减少排序时需要处理的数据量
- 如果排序数据量非常大，可以考虑增加
tmpdir对应的磁盘性能，提升临时文件的读写速度
简单示例说明
以下是一个触发filesort的简单查询示例：
-- 假设user表没有在age字段上建立索引 SELECT id, name, age FROM user ORDER BY age DESC;
这个查询会触发filesort，数据库会读取所有符合条件的行的id、name、age字段，然后按照age降序排序，最后返回结果。
如果需要查看filesort的相关信息，可以在查询前执行EXPLAIN命令，查看执行计划中的Extra列是否出现Using filesort字样，示例如下：
EXPLAIN SELECT id, name, age FROM user ORDER BY age DESC;
执行后如果Extra列显示Using filesort，就说明该查询触发了filesort机制。