排序溢出磁盘源于sort_buffer_size设置不当，需通过sort_merge_passes等状态变量确认；优化应优先建覆盖索引、限制结果集，再适度调参。
当SQL排序操作数据量过大，超出内存缓冲区容量时，MySQL会将部分排序数据写入磁盘临时文件，导致性能急剧下降——这就是“排序溢出磁盘”问题。核心在于 _sort_buffer_size（实际为 sort_buffer_size）参数设置不合理，而非“_sort_buffer”这个不存在的参数名。
确认是否真因 sort_buffer_size 不足触发磁盘排序
仅看慢查询或执行计划不够，需结合状态变量验证：
- 执行
SHOW STATUS LIKE 'Sort_merge_passes';—— 该值持续增长，说明频繁发生多路归并，极可能已溢出磁盘 - 对比
Sort_scan和Sort_range：若两者数值高，且Sort_merge_passes也高，基本可判定排序负载重 - 开启
optimizer_trace或使用EXPLAIN FORMAT=JSON查看using_filesort是否伴随using_temporary，进一步定位是否因内存不足退化为磁盘排序
合理设置 sort_buffer_size 参数
该参数是**每个需要排序的连接独占**的内存缓冲区，不是全局共享池，盲目调大会加剧内存压力：
- 默认值通常为 256KB（MySQL 8.0+），对简单 ORDER BY 可能足够；但含多列、长字符串或大结果集时易不足
- 建议按典型查询最大排序行数 × 平均每行排序字段长度粗略估算，例如：10万行 × 20字节 ≈ 2MB → 可设为
2M（注意单位支持 K/M/G） - 线上环境不建议超过 4M–8M/连接，尤其并发连接数高的实例；可通过
show variables like 'max_connections';评估总内存占用上限 - 修改方式：
SET SESSION sort_buffer_size = 4194304;（当前会话）或写入配置文件[mysqld]段后重启（全局生效）
比调参更有效的根本优化方向
单纯增大 sort_buffer_size 是治标。真正高效的做法是减少甚至消除排序开销：
-
加复合索引覆盖排序需求：如
ORDER BY status, created_at DESC，建立INDEX(status, created_at)可避免 filesort -
限制结果集大小：明确业务是否真需要全量排序？
LIMIT 100能极大降低排序数据量 - 避免 SELECT *：只查必要字段，减小单行体积，间接提升 buffer 利用率
-
拆分复杂排序逻辑到应用层：例如分页场景，用游标（cursor-based pagination）替代
OFFSET，避免每次重排全量数据
sort_buffer_size 是把双刃剑——设太小会磁盘溢出，设太大则浪费内存、增加锁竞争。优先从索引和查询逻辑入手，再辅以适度调优，才是稳定高效的解法。
