如何在MySQL中避免filesort文件排序带来的磁盘I/O

胖雪姑娘_7444

胖雪姑娘_7444

2026-11-16

878人浏览

原创

避免Using filesort的核心是让MySQL直接利用索引物理顺序完成排序或在内存中完成,而非触发磁盘文件排序;常见原因包括ORDER BY字段不在索引最左前缀、ASC/DESC方向不匹配、使用函数排序、非驱动表字段排序及未覆盖索引。

如何在mysql中避免filesort文件排序带来的磁盘i/o

避免 Using filesort 的核心不是禁用它,而是让排序在内存完成、或根本不需要排序。 MySQL 一旦触发磁盘 filesort(即 Created_tmp_disk_tables 或 Sort_merge_passes 飙升),I/O 就会从顺序写退化为大量随机读——尤其对机械盘是致命打击。SSD 虽能扛住吞吐,但延迟抖动和寿命损耗依然真实存在。

为什么 ORDER BY 会强制走 filesort?

MySQL 只有在能**直接利用索引的物理顺序**满足 ORDER BY 时,才跳过 filesort。常见失效场景包括:

  • WHERE a = ? AND b > ? ORDER BY c:索引 (a,b) 覆盖了 WHERE,但 c 不在索引中,无法复用顺序
  • ORDER BY a ASC, b DESC:索引 (a,b) 是 ASC,ASC,方向不匹配
  • WHERE a LIKE 'x%' 后接 ORDER BY b:范围扫描后,b 列已无序
  • SELECT * + ORDER BY indexed_col:若 indexed_col 索引不包含所有 SELECT 列,MySQL 可能放弃索引排序,改用 filesort 回表取全量数据(取决于 max_length_for_sort_data 设置)

如何用覆盖索引彻底消除 filesort?

覆盖索引(Covering Index)能让 MySQL 仅靠索引树就拿到全部所需数据,既免回表,又天然有序。关键是要把 WHERE 条件列、ORDER BY 列、以及 SELECT 中所有非主键列,按顺序打包进一个复合索引。

例如查询:SELECT id, name, status FROM users WHERE dept_id = 123 ORDER BY created_at DESC LIMIT 20

最优索引应为:INDEX (dept_id, created_at DESC, name, status)(id 是主键,自动包含)

注意点:

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载
  • 不要把 created_at DESC 写成 created_at ASC —— MySQL 8.0+ 支持降序索引,必须显式声明方向才能匹配 ORDER BY ... DESC
  • 避免在索引中包含大字段(如 TEXT、长 VARCHAR),否则索引体积膨胀,反而拖慢范围扫描
  • 用 EXPLAIN FORMAT=JSON 查看 key 是否命中该索引,且 Extra 中不再出现 Using filesort

怎么控制 filesort 不落盘?

即使无法避免 filesort,也要确保它在内存里跑完。MySQL 用两个参数联合控制:

  • sort_buffer_size:每个连接独占的排序内存,默认 256KB。太小 → 频繁分段排序 → 多次磁盘合并;太大 → 单个大查询吃光内存,引发 OOM 或系统 swap
  • max_length_for_sort_data:决定 MySQL 用“单次扫描”还是“两次扫描”策略。值设得太低(如默认 1024),会让 MySQL 放弃读全字段进 sort_buffer,转而只存排序字段 + 主键,后续再回表——这反而增加 I/O 次数

实操建议:

  • 查当前值:SHOW VARIABLES LIKE 'sort_buffer_size';、SHOW VARIABLES LIKE 'max_length_for_sort_data';
  • 线上环境可设 sort_buffer_size = 4M(非全局,按需在会话级 SET),max_length_for_sort_data = 4096~8192,平衡内存与 I/O
  • 监控指标:SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'; 持续 > 0 表示频繁落盘;Created_tmp_disk_tables 上涨也说明临时结果溢出

哪些 SQL 写法会悄悄触发 filesort?

有些写法看似合理,实则暗藏陷阱:

  • ORDER BY RAND():必然 filesort,且无法优化。替代方案:先用 SELECT id FROM table ORDER BY id LIMIT 1 OFFSET FLOOR(RAND() * N) 取随机 ID,再查详情
  • GROUP BY 后跟 ORDER BY:若未用索引覆盖,MySQL 会先分组生成临时表,再对结果排序——双重开销。优先用 GROUP BY ... ORDER BY NULL 显式禁止无意义排序
  • 子查询中的 ORDER BY:除非配合 LIMIT,否则会被优化器忽略,还可能干扰外层执行计划
  • 使用函数包裹排序字段:ORDER BY UPPER(name) 无法走索引,必走 filesort

真正难处理的,往往是那些在主库跑得尚可、但从库回放时因缓冲池小或并发高而突然落盘的 filesort。这时候光调 sort_buffer_size 不够,得回到主库源头——用覆盖索引把排序逻辑压进存储引擎层,让 SQL 线程只做顺序回放,这才是根治之道。

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5043

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1329

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7101

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3743

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7100

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

9941

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1350

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1172

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程