讲师中心 微信公众号

工具推荐

视频效率加速
updream

一键生成高质量推广成片

剧云
剧云

中文短剧剧本制作平台

Seko
Seko

AI短剧一键生成成片

Laper
Laper

剧本策划到成片协作

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

胖雪姑娘_7444

胖雪姑娘_7444

发布时间:2026-11-16 09:52:40

|

878人浏览过

|

来源于php中文网

原创

避免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 线程只做顺序回放,这才是根治之道。

热门AI工具

更多
DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

SkildArt
SkildArt Hot

SkildArt是一款AI文本写作工具,一站式 AI 视觉创作平台。

Atoms
Atoms Hot

Atoms是一款AI智能体工具,第一支自动构建真实业务的 AI 团队。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

超级简历WonderCV

一款AI办公效率工具,主要用于免费求职简历模版下载制作,应届生职场人必备简历制作神器,适合需要提升相关任务效率的用户。

讯飞智作

讯飞智作是一款AI视频创作工具,AI文本配音工具,数字人课程、营销视频制作。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

SoundView
SoundView Hot

SoundView是一款面向跨境电商的视频翻译、配音和本地化 AI 创作平台。

墨刀AI
墨刀AI Hot

一款AI图像与设计工具,主要用于产品经理的专属智能体,适合需要提升相关任务效率的用户。

相关专题

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

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

5043

2023.10.12

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

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

1031

2023.10.27

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

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

1329

2024.02.23

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

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

7101

2024.03.06

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

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

3743

2024.03.06

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

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

7100

2024.04.07

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

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

9941

2024.04.29

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

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

1350

2024.04.29

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

120

2026.11.12

热门下载

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

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn