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/OHDD 性能差触发行总长度 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并发高别太大防 OOMmax_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 写的是真实临时表