如何排查MySQL由于内存溢出导致的事务自动回滚_增加Sort_buffer

mysql事务自动回滚并非内存溢出直接表现，而是out of sort memory等错误触发后应用主动回滚，或oom killer杀进程导致未提交事务丢失；根源在排序缓冲不足，需查错误日志确认，并优先优化索引与执行计划。

如何排查mysql由于内存溢出导致的事务自动回滚_增加sort_buffer_size

MySQL事务自动回滚本身不是内存溢出的直接表现——它更可能是Out of sort memory这类错误触发后，上层应用（如Java Spring）捕获异常并主动回滚事务；或者因OOM Killer杀掉mysqld进程，导致未提交事务全部丢失。真正要盯的是“排序内存不足”这个具体瓶颈，而不是泛泛查“事务回滚”。

确认是不是`Out of sort memory`在作祟

先看错误日志里有没有明确报错。Spring、MyBatis或客户端日志中若出现：

java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size

或 MySQL 错误日志（/var/log/mysql/error.log 或 mysqld.err）里有：

Out of sort memory; increase sort_buffer_size

那就锁定了问题类型：是单条查询排序阶段内存不够，不是全局OOM。此时事务回滚只是副作用，根源在排序缓冲。

用下面命令快速验证当前设置：

SHOW VARIABLES LIKE 'sort_buffer_size';

注意：sort_buffer_size 是每个连接独占的，不是全局共享。设成 4M，100个活跃连接就可能吃掉 400MB，但问题往往出在单条查询上——比如没索引的 ORDER BY 强制全表扫描后排序。

别急着调大`sort_buffer_size`，先看执行计划

盲目增大 sort_buffer_size 可能掩盖真问题，还加剧高并发下的内存压力。优先做这三件事：

对报错SQL执行 EXPLAIN，重点看 Extra 列是否含 Using filesort —— 出现就说明没走索引排序，必须优化
检查排序字段是否有有效索引：比如 ORDER BY updated_time DESC, id DESC，应建联合索引 INDEX(updated_time, id)，且注意字段顺序和方向匹配
确认WHERE条件是否足够过滤数据量：如果 WHERE del = 0 AND tenant_id = 123 返回几十万行再排序，再大的 sort_buffer_size 也扛不住，得加覆盖索引或提前分页

临时调参只能救急，不能替代索引设计。一个没索引的 ORDER BY，把 sort_buffer_size 从 256K 拉到 8M，只是让“崩溃点”延后一点，反而更容易在并发时触碰系统总内存上限。

什么时候才该调`sort_buffer_size`？怎么调才安全

只有满足以下全部条件时，才考虑调大：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

已确认SQL走了索引（EXPLAIN 中 type 是 ref/range，且无 Using filesort）
排序结果集仍较大（比如 rows 显示扫描 5–10 万行），但业务又无法改成分页游标式查询
服务器剩余内存充足，且最大连接数可控（max_connections ≤ 100）

调法分两步：

临时生效（当前会话/全局，重启失效）：

SET SESSION sort_buffer_size = 4*1024*1024;  -- 当前连接用 4MB

或

SET GLOBAL sort_buffer_size = 4*1024*1024;  -- 所有新连接生效

永久生效：在 my.cnf 的 [mysqld] 段下加：

sort_buffer_size = 4M

注意：不要设超过 8M。实测超过这个值，性能提升极小，但内存碎片和分配失败概率明显上升。MySQL 官方文档也建议保持在 2–4M 区间。

事务回滚不是终点，要防`sp_head::main_mem_root`暗坑

如果你的排序操作藏在存储过程中（比如循环 FETCH 游标后排序），那问题可能更隐蔽：sort_buffer_size 调再大也没用。因为游标内部使用的内存来自 sp_head::main_mem_root，它不走缓冲池管理，也不受 sort_buffer_size 控制，而且——只要过程没正常退出，这块内存就不会释放。

排查方法：

SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/sql/sp_head%';

如果返回值动辄几个 GB，说明游标或过程逻辑正在持续累积内存。此时必须检查：