MySQL Filesort

mysql filesort文件排序

MySQL Filesort（文件排序）是 MySQL 在无法利用索引完成ORDER BY/GROUP BY时，在内存或磁盘上执行的额外排序过程。执行计划Extra列出现Using filesort即代表触发。

一、核心原理

• 不是一定写磁盘：数据量小时在内存（sort_buffer）完成；量大则用磁盘临时文件 + 归并排序。
• 两种排序算法：
  • 单路排序（优先）
    • 一次性读取所有查询字段到sort_buffer排序
    • 优点：一次 I/O、效率高
    • 触发：行总长度 ≤max_length_for_sort_data（默认 1024 字节）
  • 双路排序（回表排序）
    • 先读排序字段 + 主键 ID排序；再按 ID 回表取完整数据
    • 优点：省内存；缺点：二次随机 I/O（HDD 性能差）
    • 触发：行总长度 >max_length_for_sort_data

二、触发 Filesort 的常见场景

• 无索引、或索引不匹配ORDER BY字段
• 联合索引未遵循最左前缀（如索引(a,b)，ORDER BY b,a）
• 排序字段有函数 / 运算 / 类型转换（ORDER BY YEAR(date)）
• ORDER BY混合ASC/DESC（复合索引）
• 查询字段过多、行太宽（触发双路 / 磁盘排序）

三、优化方案（优先级从高到低）

1. 用索引消除 Filesort（最有效）

• 单列排序：给排序列建索引

  -- 原SQL SELECT name,age FROM user ORDER BY age; -- 优化：覆盖索引 ALTER TABLE user ADD INDEX idx_age_name(age, name);

• WHERE + ORDER BY：复合索引（过滤列在前，排序列在后）

  -- WHERE status=? ORDER BY create_time ALTER TABLE order ADD INDEX idx_status_create(status, create_time);

• 避免：排序列函数、表达式、隐式转换

2. 调优参数（无法加索引时）

• sort_buffer_size（每连接排序内存）
  • 默认：256KB
  • 建议：2MB~4MB（并发高别太大，防 OOM）
• max_length_for_sort_data
  • 增大可优先用单路排序（减少回表）
• tmp_table_size / max_heap_table_size
  • 增大减少磁盘临时表

3. SQL 写法优化

• 只查需要的字段，禁止SELECT *（减少行宽、多用单路排序）
• WHERE 精准过滤，减少排序行数
• 分页 + 排序：用覆盖索引避免大表全排序

四、排查与验证

-- 查看执行计划 EXPLAIN SELECT ... ORDER BY ...; -- 看排序状态 SHOW STATUS LIKE 'Sort%'; -- Sort_merge_passes：归并次数（高则内存不足） -- Sort_scan/Sort_range：触发filesort次数

上面说的临时文件是临时表吗

不是一回事，完全两个东西，我用最简单、最清晰的话给你讲明白：

1. Using filesort 里的「临时文件」

=排序用的临时文件

• 只存排序数据
• 格式是 MySQL 内部排序块，不是表结构
• 作用：排序放不下内存时，写到磁盘分段归并
• 对应状态：Sort_merge_passes

一句话：这是排序用的临时块文件，不是表。

2. Using temporary 里的「临时表」

=真正的临时表

• 结构像普通表，有字段、有行
• 用于GROUP BY / DISTINCT / UNION等
• 先存中间结果，再计算
• 可能在内存，也可能落盘成#sql_xxx.MYD

一句话：这是存中间结果的临时表。

3. 最关键区别（必记）

• Using filesort→ 排序溢出 →临时排序文件
• Using temporary→ 分组 / 去重 →临时表

它们完全独立，一个管排序，一个管分组。

4. 最简单记忆法

• 看到filesort→ 排序用的临时文件
• 看到temporary→ 真正的临时表

他们是在内存里还是要写入磁盘文件

都可以先在内存，不够大就落磁盘。但触发条件不一样。

我分开讲得清清楚楚：

一、Using filesort（排序）

1. 内存阶段

数据量小 → 放sort_buffer内存排序→ 不写磁盘，很快

2. 磁盘阶段

sort_buffer 放不下 → 拆成多个临时磁盘文件，归并排序→ 写磁盘，慢

判断：Sort_merge_passes越大 → 越频繁写磁盘

二、Using temporary（临时表）

1. 内存阶段

结果集小 → 内存临时表（MEMORY 引擎）→ 不写磁盘

2. 磁盘阶段

超过tmp_table_size或max_heap_table_size→ 自动转成磁盘临时表（InnoDB/MyISAM）→ 写磁盘，极慢

三、最关键区别

• filesort 临时文件：排序溢出 → 磁盘块文件
• temporary 临时表：分组结果太大 → 真实磁盘表

它们都：先内存，不够就落盘。

四、最简单总结

• 内存够：都在内存，快
• 内存不够：都写磁盘，慢
• filesort 写的是排序临时文件
• temporary 写的是真实临时表