必须同时设置 tmp_table_size 和 max_heap_table_size 且值完全一致,否则以较小值为准;sort_buffer_size 是每个连接独占、按需分配的缓冲区,调高需结合并发量与内存总量评估。

tmp_table_size 和 max_heap_table_size 必须设成一样
MySQL 用 tmp_table_size 控制内存临时表上限,但真正起作用的是 max_heap_table_size ——两者取较小值。如果只改一个,等于白调。
常见错误是只配了 tmp_table_size = 256M,却漏掉 max_heap_table_size,结果实际生效的还是默认的 16MB(或系统更低值),Created_tmp_disk_tables 指标照涨不误。
- 必须在
[mysqld]段下**同时设置两个参数**,且值完全一致 - 若使用 InnoDB 表做 GROUP BY / DISTINCT,临时表大小受二者共同限制;MyISAM 临时表则只看
tmp_table_size - 超过该值的临时表会自动落地为磁盘表(
MYD/MYI或ibtmp1),触发Created_tmp_disk_tables计数
sort_buffer_size 不是越大越好,而且它不共享
sort_buffer_size 是每个连接独占的缓冲区,不是全局共用的。设成 8MB 看似不多,但 200 个活跃连接就吃掉 1.6GB 内存,还没算其他 per-connection 缓冲区(如 join_buffer_size、read_rnd_buffer_size)。
它只在需要排序时分配(比如 ORDER BY 无索引、GROUP BY 无索引、文件排序),查询结束就释放,不会常驻。所以调高它不会“一直占着”,但并发一高,瞬间峰值很吓人。
- OLTP 场景默认
256K足够,别盲目拉到 4M - 真有万行以上排序需求,先确认
EXPLAIN输出里是否出现Using filesort,再结合SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'看是否频繁归并排序 - MySQL 8.0.22+ 不允许
SET GLOBAL sort_buffer_size,只能改配置文件 + 重启 - 某些 ORM(如 Django 的
connection.cursor())或代理(如 ProxySQL)会在建连后主动重置会话变量,导致你配了也无效
怎么验证配置到底有没有生效
改完 my.cnf,重启 MySQL 后,不能只信配置文件内容。得进库查实时值:
SHOW VARIABLES LIKE 'tmp_table_size';SHOW VARIABLES LIKE 'max_heap_table_size';SHOW VARIABLES LIKE 'sort_buffer_size';
注意:这些是**会话级变量**,新连接才继承全局值。已有连接仍用旧值,得重连或手动执行 SET SESSION sort_buffer_size = 1048576; 才能生效。
- 检查是否被多处定义覆盖:比如
[mysqld]和[server]段都写了同一参数,后者会被忽略 - 确认没有被启动命令行参数覆盖:比如
mysqld --sort-buffer-size=1M会优先于配置文件 - 用
SHOW GLOBAL STATUS查监控指标:Created_tmp_disk_tables下降、Sort_merge_passes趋稳,才是真有效
容易被忽略的关联影响
调大 tmp_table_size 和 sort_buffer_size 不是孤立动作。它们和 innodb_buffer_pool_size、max_connections 共同决定总内存水位。估算公式是:
总内存 ≈ innodb_buffer_pool_size + (tmp_table_size + sort_buffer_size + join_buffer_size + ... ) × max_connections
比如 innodb_buffer_pool_size=4G,max_connections=300,每连接缓存加起来按 10MB 算,光连接相关就额外吃掉 3GB —— 这还没算 OS 缓存、其他进程。
- 不要只盯着单个参数调优,先看
free -h和mysqladmin ext -i1 | grep -E "Threads_connected|Created_tmp_disk"做基线对比 -
tmp_table_size和sort_buffer_size都是“按需分配”,但高并发下分配/释放频繁,可能引发内存碎片,尤其在老版本 MySQL 中 - 如果应用大量使用
UNION、子查询或窗口函数,临时表和排序压力会远超预期,这时光调参数不够,得配合 SQL 改写










