mysql执行排序操作时，如果无法利用索引完成排序，就会在内存或磁盘中生成排序文件，对数据进行排序处理。当排序数据量超过内存限制时，就会生成磁盘排序文件，这类操作会显著提升查询的响应时间。
mysql排序文件的生成机制
mysql的排序过程分为两种模式，不同的模式对应不同的排序文件使用方式：
- 内存排序：当排序数据量小于
sort_buffer_size参数设置的值时，排序操作完全在内存的排序缓冲区中完成，不会生成磁盘排序文件。 - 磁盘排序：如果排序数据量超过
sort_buffer_size，mysql会将排序数据分块写入磁盘的临时文件，完成排序后再合并结果，这类磁盘文件就是排序文件，会占用磁盘IO资源。
另外如果查询使用了filesort方式排序，且需要回表查询额外字段，还可能触发Using temporary临时表，进一步增加排序文件的使用。
排序文件性能低下的常见原因
1. 未使用索引排序
如果排序字段没有对应的索引，mysql无法利用索引的有序性直接返回排序结果，只能进行filesort操作，大概率会生成排序文件。
2. 排序缓冲区设置过小
sort_buffer_size参数默认值通常较小，当排序数据量稍大时就会触发磁盘排序，生成排序文件。
3. 查询返回多余字段
如果查询使用SELECT *返回所有字段，排序时需要缓存的字段数据量变大，更容易超过排序缓冲区大小，触发磁盘排序。
4. 排序数据量过大
没有对查询结果做分页限制，一次性排序大量数据，必然会导致排序文件生成，性能严重下降。
mysql排序文件优化方案
1. 优化索引设计，避免filesort
为排序字段创建合适的索引，让mysql可以直接利用索引的有序性完成排序，从根源上避免生成排序文件。如果查询同时有过滤条件和排序条件，可以创建联合索引，遵循最左前缀原则。
例如有查询语句：
-- 原查询，无合适索引时会生成排序文件 SELECT id, name, create_time FROM user WHERE status = 1 ORDER BY create_time DESC;
可以创建联合索引(status, create_time)，这样过滤和排序都可以利用索引完成，不会生成排序文件。验证索引是否生效可以用EXPLAIN查看执行计划，没有出现Using filesort即为优化成功。
-- 查看执行计划 EXPLAIN SELECT id, name, create_time FROM user WHERE status = 1 ORDER BY create_time DESC;
2. 调整排序相关参数
合理调整mysql的排序相关参数，减少磁盘排序文件的生成：
sort_buffer_size：适当增大该参数值，让更多排序操作可以在内存中完成，注意不要设置过大，避免占用过多内存影响其他连接。max_length_for_sort_data：如果查询返回的字段长度总和小于该值，mysql会采用单次排序模式，减少排序文件的生成，可根据实际查询字段长度调整。tmp_table_size和max_heap_table_size：如果有临时表生成，调整这两个参数可以让临时表尽量在内存中存放，减少临时表对应的排序文件。
查看当前参数值的语句如下：
-- 查看排序相关参数 SHOW VARIABLES LIKE 'sort_buffer_size'; SHOW VARIABLES LIKE 'max_length_for_sort_data'; SHOW VARIABLES LIKE 'tmp_table_size'; SHOW VARIABLES LIKE 'max_heap_table_size';
3. 优化查询语句
改写查询语句，减少排序的数据量和字段量：
- 避免使用
SELECT *，只返回需要的字段，减少排序时需要缓存的数据量。 - 增加合理的分页限制，避免一次性排序大量数据，例如使用
LIMIT子句限制返回行数。 - 如果排序字段有固定范围，可以在
WHERE子句中增加范围过滤，减少参与排序的数据量。
优化后的查询示例：
-- 优化后查询，只返回必要字段，增加分页限制 SELECT id, name, create_time FROM user WHERE status = 1 ORDER BY create_time DESC LIMIT 0, 20;
4. 定期维护表和索引
如果表的数据碎片较多，索引的有序性会下降，可能导致原本可以使用索引排序的查询无法命中索引，进而生成排序文件。可以定期执行OPTIMIZE TABLE或者ALTER TABLE table_name ENGINE=INNODB来整理表和索引的碎片，提升索引使用效率。
优化效果验证
优化完成后，可以通过EXPLAIN执行计划查看是否还有Using filesort标识，同时可以对比优化前后的查询响应时间，确认排序文件的性能损耗是否降低。如果业务中有慢查询日志，也可以查看对应排序查询的执行时间变化，验证优化效果。