怎样优化MySQL8.0的tmp_table_size避免临时表落盘-mysql教程-PHP中文网

mysql 8.0 中 tmp_table_size 参数已被彻底移除，执行 show variables like 'tmp_table_size' 会返回空或报错；真正生效的是 max_heap_table_size 和 internal_tmp_mem_storage_engine，临时表是否落盘取决于二者共同约束的内存上限，且需同步配置并监控 created_tmp_disk_tables / created_tmp_tables 比值（超5%需优化），text/blob 字段会强制走磁盘临时表，使内存参数完全失效。

怎样优化mysql8.0的tmp_table_size避免临时表落盘

MySQL 8.0 里根本不存在 tmp_table_size

直接告诉你：执行 SHOW VARIABLES LIKE 'tmp_table_size' 会返回空或报错 Unknown system variable 'tmp_table_size'，这不是你操作错了，是 MySQL 8.0 确实移除了这个参数。如果你从 5.7 升级上来，配置文件里还留着 tmp_table_size，MySQL 启动时会静默忽略——不警告、不报错、也不生效。

真正起作用的是两个参数：max_heap_table_size 和 internal_tmp_mem_storage_engine。临时表是否落盘，取决于查询中间结果大小是否超过二者共同约束的“内存上限”：

max_heap_table_size 控制 MEMORY 表和内存临时表的最大尺寸
internal_tmp_mem_storage_engine 决定用哪种引擎建内存临时表（8.0 默认是 TempTable，不是 MEMORY）
实际内存上限 = min(max_heap_table_size, internal_tmp_mem_storage_engine 的隐式上限) —— 但后者通常不显式设限，所以关键就是把 max_heap_table_size 设合理

必须同步调大 max_heap_table_size 并确认引擎

只改一个参数等于没改。常见错误是把 max_heap_table_size 设成 256M，却忘了 internal_tmp_mem_storage_engine 还是默认的 MEMORY（尤其在低版本升级后未清理旧配置），而 MEMORY 引擎不支持 TEXT/BLOB、行格式固定、超限就直接报错或强制落盘。

正确做法：

查当前设置：SELECT @@max_heap_table_size, @@internal_tmp_mem_storage_engine;
永久生效，在 [mysqld] 段统一加两行：
max_heap_table_size = 134217728（即 128MB）
internal_tmp_mem_storage_engine = TempTable
重启 MySQL（SET GLOBAL 对已有连接无效，且部分变量不支持动态修改）
验证：新连接中执行 SELECT @@max_heap_table_size, @@internal_tmp_mem_storage_engine;，确保值已更新且引擎为 TempTable

比调参更关键的是监控 Created_tmp_disk_tables 比值

调大参数不是目的，避免落盘才是。但光看 Created_tmp_disk_tables 绝对值没意义——它可能只是某个慢查询偶尔触发。真正要盯的是比值：

MySQL

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

下载

Created_tmp_disk_tables / Created_tmp_tables

这个比值超过 5% 就该介入，超过 15% 已有明显瓶颈。注意：

该比值只对新建立的连接实时更新；老连接沿用启动时的参数设置，所以调参后需等 10–30 分钟再采样
执行 SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 获取两个数值
如果比值居高不下，即使参数已调大，说明 SQL 本身结构导致无法避免落盘（比如含 TEXT 字段、无索引的 GROUP BY）

TEXT/BLOB 字段会让所有内存参数失效

这是最容易被忽略的硬限制：TempTable 引擎虽支持 TEXT/BLOB，但会以 off-page 方式管理，仍可能触发磁盘临时表；而一旦字段类型是 TEXT 或 BLOB，MySQL 会直接跳过内存临时表阶段，强制走磁盘（InnoDB 临时表）。此时无论 max_heap_table_size 设多大，都完全无效。

排查方法：