文件排序（filesort）避免
什么是 filesort
MySQL 的 filesort 并不是指”文件排序”（虽然字面意思如此），而是一种排序算法的名称。它可能在内存中完成，也可能使用磁盘临时文件。
当 SQL 包含 ORDER BY，且 MySQL 无法利用索引直接获取有序数据时，就需要执行 filesort。
EXPLAIN SELECT * FROM orders WHERE status = 'PAID' ORDER BY create_time;
-- Extra: Using filesort ← 这是一个优化信号
filesort 的两种实现
1. 内存排序（快速）
- 排序数据量小于
sort_buffer_size - 完全在内存中完成，性能影响较小
2. 磁盘排序（慢速）
- 排序数据量超过
sort_buffer_size - MySQL 需要分批排序后写入临时文件，最后合并
- 性能下降 10~100 倍
filesort vs. 索引排序
-- 不需要 filesort：ORDER BY 字段是索引的一部分且前面字段是等值条件
SELECT * FROM orders WHERE status = 'PAID' ORDER BY create_time;
-- 索引 (status, create_time) 利用索引直接排序
-- 需要 filesort：排序字段不在索引中
SELECT * FROM orders WHERE status = 'PAID' ORDER BY amount;
| 排序方式 | 原理 | 速度 |
|---|---|---|
| 索引排序 | 直接遍历索引 | 极快（无额外开销） |
| 内存 filesort | 内存中快速排序 | 快 |
| 磁盘 filesort | 写入临时文件再归并 | 慢 |
如何避免 filesort
方案一：索引覆盖 ORDER BY
让 ORDER BY 的字段包含在索引中：
-- 等值条件 + 排序，建立复合索引
CREATE INDEX idx_status_time ON orders(status, create_time);
-- 这个查询就不会 filesort 了
SELECT * FROM orders WHERE status = 'PAID' ORDER BY create_time;
关键点：索引的排序顺序必须与 ORDER BY 一致。
-- 索引 (a, b)
ORDER BY a, b -- ✅ 可以（索引排序）
ORDER BY a DESC, b DESC -- ✅ 可以（MySQL 8.0 支持反向扫描）
ORDER BY b, a -- ❌ 不可以（违反最左前缀）
ORDER BY a ASC, b DESC -- ❌ 不可以（排序方向不一致）
ORDER BY a -- ✅ 可以（部分前缀）
方案二：使用覆盖索引
如果 SELECT 的字段全部在索引中，MySQL 可以直接从索引返回数据，不需要回表：
-- 覆盖索引包含查询所有字段
CREATE INDEX idx_covering ON orders(status, create_time, id, amount);
-- 这样查询：只从索引获取数据，不需要 sort buffer
SELECT id, amount, create_time FROM orders
WHERE status = 'PAID' ORDER BY create_time;
-- Extra: Using where; Using index
方案三：使用索引降序（MySQL 8.0）
MySQL 8.0 支持降序索引：
-- 创建降序索引
CREATE INDEX idx_status_time_desc ON orders(status, create_time DESC);
-- 降序排序不再需要 filesort
SELECT * FROM orders WHERE status = 'PAID' ORDER BY create_time DESC;
方案四：限制排序数据量
如果必须 filesort，尽量减小排序数据量：
-- 限制结果集大小
SELECT * FROM orders WHERE status = 'PAID'
ORDER BY create_time LIMIT 20;
MySQL 针对 LIMIT 的 filesort 有优化：它会使用专门的”Top-N 堆排序”算法，只需要维护 N 个元素的堆，而不需要排序全部数据。
方案五：排序字段使用单列索引
如果复合索引不可行，确保 ORDER BY 字段有单列索引：
CREATE INDEX idx_create_time ON orders(create_time);
-- 对于只按时间排序的查询，走索引排序
SELECT * FROM orders ORDER BY create_time LIMIT 100;
-- 但如果 WHERE 条件的过滤性很差，MySQL 可能仍然选择 filesort
-- 因为优化器会权衡：全表扫描 + filesort vs 索引扫描 + 回表
如何判断 filesort 的性能影响
-- 关注 sort_* 状态变量
SHOW STATUS LIKE 'sort%';
| 变量 | 含义 |
|---|---|
| Sort_scan | 使用 filesort 的查询次数 |
| Sort_range | 范围查询使用 filesort 的次数 |
| Sort_rows | 排序的总行数 |
| Sort_merge_passes | 磁盘合并排序的次数（>0 说明用了磁盘） |
重点关注 Sort_merge_passes。如果这个值 > 0，说明 filesort 使用了磁盘，需要优化。
调优参数
[mysqld]
# 增大排序缓冲区（默认 256KB，可调到 2MB~8MB）
sort_buffer_size = 4M
# 最大排序数据量（默认 1024KB）
max_sort_length = 1024
注意：sort_buffer_size 是按连接分配的，过大容易导致内存溢出。
总结
避免 filesort 的最佳策略是让 ORDER BY 的字段成为索引的一部分，且与 WHERE 条件一起构成覆盖索引。如果实在无法避免 filesort，确保数据量尽量小（加 LIMIT）和排序尽量在内存中完成（增大 sort_buffer_size）。
EXPLAIN 中 Using filesort 是需要优化的信号，但不是所有的 filesort 都是性能杀手——关键看排序数据量和是否使用磁盘。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容