一次MySQL排序内存溢出的排查与解决实录

我昨天刚踩完这个坑，顺手把排查过程整理了一下，希望能帮你省点时间。

事情是这样的

昨天下午，我正美滋滋地写着代码，突然监控告警响了。点开一看，一个跑了半年的MySQL服务挂了，日志里躺着一行刺眼的红字：

1	Out of sort memory, consider increasing server sort buffer size

说实话，看到这个报错我第一反应是——哪个憨憨写了没索引的大排序？后来一查，不是代码的问题，是数据量涨上来了，原来默认的256KB真扛不住了。

所以今天我就把整个排查和解决过程记录下来，希望对遇到同样问题的你有点帮助。

先说说这个报错是啥意思

MySQL在执行ORDER BY或者GROUP BY的时候，如果数据量太大，内存里放不下，就会把数据临时写到磁盘上。但是呢，MySQL有个参数叫sort_buffer_size，它决定了每个连接能用来做排序的内存有多大。

默认值只有256KB。

你品品，256KB能干啥？一条数据稍微大点，几百条就把内存撑爆了。一旦撑爆，MySQL就给你甩脸色——Out of sort memory。

常见触发场景就这几类：

排序的数据量大（几十万行起步）
排序字段里有TEXT、BLOB、JSON这种大字段
排序字段没索引，强制走内存排序

我这次属于数据量涨了 + 没索引双重暴击。

排查过程

第一步：确认当前配置

先进容器看一眼：

1	docker exec -it mysql-container mysql -uroot -p

进去之后执行：

1	SHOW VARIABLES LIKE 'sort_buffer_size';

结果返回 262144，果然是256KB，实锤了。

再看了一眼Sort_merge_passes这个状态值：

1	SHOW GLOBAL STATUS LIKE 'Sort%';

Sort_merge_passes 那个数字高得离谱，说明大量排序操作都走了磁盘临时文件。这个值理想情况下应该是0。

第二步：确定调整方向

其实解决方案就三条路：

方案	优点	缺点
调大sort_buffer_size	简单粗暴，立即生效	设置太大会吃掉大量内存，高并发下危险
给排序字段建索引	治本，性能提升最大	需要改表结构，DBA审批麻烦
优化SQL，减少排序数据量	长远来看最优	改代码，测试，上线周期长

我这边是Docker跑的服务，先临时调大参数救火，然后顺手把索引加上，彻底解决问题。

解决方案（Docker环境）

方案一：直接在docker-compose里加参数（我用这个）

因为我不喜欢额外挂配置文件，感觉太啰嗦，所以直接在docker-compose.yml的command里怼了一堆参数：

services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: xxxxxxx
    ports:
      - "3306:3306"
    volumes:
      - ./data:/var/lib/mysql
    command:
      - --sort_buffer_size=4M
      - --innodb_buffer_pool_size=4G
      - --max_connections=500
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
      # 爱加多少加多少，全写这儿

改完之后：

1	docker-compose down && docker-compose up -d

重启完再查一遍SHOW VARIABLES，4MB，稳了。

注意：command里写的参数会覆盖配置文件里的同名配置，优先级最高。

方案二：挂载配置文件（更规范的做法）

如果你跟我一样，command那行快写成诗了，可以考虑挂配置文件：

先在宿主机搞一个my.cnf：

[mysqld]
sort_buffer_size = 4M
innodb_buffer_pool_size = 4G
max_connections = 500

然后挂到容器里：

1 2	volumes: - ./my.cnf:/etc/mysql/conf.d/my.cnf

两种方式都行，看个人习惯。

服务器8G内存，参数到底给多少？

AI给了一个参考

参数	建议值	说明
innodb_buffer_pool_size	4G - 5.6G	给50%-70%的内存
sort_buffer_size	2M - 4M	给太大高并发下会爆内存
join_buffer_size	1M - 2M	JOIN没索引时的缓冲区
read_buffer_size	512K	全表扫描用的
tmp_table_size	64M	内存临时表上限

sort_buffer_size不能给太大

因为这个参数是每个连接独享的。假设你设了64MB，同时有100个连接在排序，那就是6.4GB内存没了——8GB的机器直接原地爆炸。

效果验证

配置生效后，再跑一遍原来的查询，报错没了。再瞅一眼Sort_merge_passes：

1	SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

数值不再蹭蹭往上涨了，心里一块石头落地。

注意事项

别只调大sort_buffer_size就完事了，给排序字段建索引才是正道。调参治标，索引治本。
8GB内存的话，innodb_buffer_pool_size一定要给足，这才是MySQL性能的命根子，sort_buffer_size只是配角。
改完配置记得验证，进容器执行SHOW VARIABLES看一眼，别改了等于没改。
如果你用的是MySQL 8.0.20以上版本，排序机制有变化，涉及JSON字段的排序更容易触发这个报错，这个参数就更值得关注了。

BLCL的博客小馆