发布时间:2026-09-28 12:30 更新时间:2026-09-28 12:30 阅读量:10
磁盘告警突然响了,翻到 /var/lib/mysql 下面一堆 #innodb_temp 或者 #sql_xxx_0.MAD 文件,很多站长第一反应是「谁在建临时表」。MySQL 8 的临时表有两套存储引擎:内存临时表默认走 TempTable,溢出后落到 InnoDB 的临时表空间;另一种是老的 MEMORY 引擎,超限后转成磁盘上的 MyISAM 表。搞清楚 internal_tmp_mem_storage_engine 决定了落盘文件长什么样,也决定了你接下来该看哪个参数。
MySQL 8.0 起,internal_tmp_mem_storage_engine 默认是 TempTable,它可以在一张「内存临时表」里存放变长列,比老的 MEMORY 引擎更省空间。当内存用量超过 temptable_max_ram 后,TempTable 会尝试使用 temptable_max_mmap 指定的内存映射文件(本质还是在 tmpdir 下的文件,但走 mmap 不算磁盘 IO 意义上的落盘),两者都撑不住才真正写入 InnoDB 临时表空间。
如果你把该参数显式改成 MEMORY,那内存临时表就是 MEMORY 引擎,列类型受限(不支持 BLOB/TEXT),超过 tmp_table_size 与 max_heap_table_size 中较小的那个值,就会转成磁盘上的 MyISAM 临时表——这也是老版本里最常见的「磁盘临时表爆满」来源。
mysql> SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';
mysql> SHOW VARIABLES LIKE 'tmp_table_size';
mysql> SHOW VARIABLES LIKE 'max_heap_table_size';
mysql> SHOW VARIABLES LIKE 'temptable_max_ram';
mysql> SHOW VARIABLES LIKE 'temptable_max_mmap';
注意 tmp_table_size 只对 MEMORY 引擎的内存临时表生效,对 TempTable 不起作用;TempTable 的限制由 temptable_max_ram 控制。很多人把 tmp_table_size 调到 256M 甚至 1G 却毫无改善,原因就在这里。具体默认值与上限以你所装版本的官方文档为准。
两个关键计数器是 Created_tmp_tables(总共创建了多少张内部临时表)和 Created_tmp_disk_tables(其中有多少张最终落到了磁盘)。只看后者没意义,要看比值:
mysql> SHOW GLOBAL STATUS WHERE Variable_name IN (
'Created_tmp_tables',
'Created_tmp_disk_tables',
'Created_tmp_files',
'Sort_merge_passes',
'Sort_scan',
'Sort_range');
判断思路是这样的:如果 Created_tmp_disk_tables 随 Created_tmp_tables 同比例上涨,说明大量内部临时表都落盘了,通常是分组或去重(GROUP BY、DISTINCT)在撑爆内存表,或者 SELECT 里带了 BLOB/TEXT 列导致 TempTable 无法在内存中保存。反过来,如果磁盘临时表数量不多,但 Sort_merge_passes 持续增长、Sort_scan 和 Sort_range 很高,那瓶颈更可能在排序阶段,属于 sort_buffer_size 不够导致的多轮归并,而不是临时表本身的问题。
配合 performance_schema 能进一步定位到具体 SQL:
mysql> SELECT DIGEST_TEXT,
SUM_CREATED_TMP_TABLES,
SUM_CREATED_TMP_DISK_TABLES,
SUM_SORT_MERGE_PASSES,
SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;
这张表按 SQL 指纹聚合,能看到某条语句创建了多少张磁盘临时表、是否走了全表扫描。如果某个 GROUP BY 语句的 SUM_CREATED_TMP_DISK_TABLES 远高于其他语句,那磁盘临时表爆满的元凶基本就锁定了。
临时表落盘的本质是「中间结果集太大」。能通过索引消除分组临时表的,优先加索引或改写 SQL,例如把 GROUP BY 的列建成联合索引,让优化器走索引有序读取而不是建临时表;能用覆盖索引避免回表的,就不要 SELECT 一堆不必要的大字段。LONGTEXT、JSON 大字段参与 GROUP BY 或 DISTINCT 时,TempTable 无法把行存在内存里,这种情况加参数也救不了,只能减少参与分组的数据量。
参数层面,如果确实需要放宽内存临时表容量,可以这样调整(以实际内存余量为准,不要一次性给太大):
# my.cnf 中调整,改完需重启生效
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
temptable_max_ram = 256M
temptable_max_mmap = 1G
需要提醒的是,tmp_table_size 与 max_heap_table_size 取较小值作为单个内存临时表的上限,两个要一起调。这两个参数是按会话并发叠加的,100 个并发连接同时建 64M 临时表,理论峰值就是 6G 以上,所以不要只看单条 SQL 的效果。TempTable 的 temptable_max_ram 是全局共享的内存池,超过后先走 mmap,temptable_max_mmap 设成 0 可以禁止 mmap、直接走 InnoDB 临时表空间,是否这么做取决于你的磁盘与监控策略。
另外记得检查 tmpdir 指向的目录空间。InnoDB 临时表空间(ibtmp1)默认会自动扩展,极端情况下可能把 innodb_temp_data_file_path 所在的盘写满,重启后 ibtmp1 会被重建并收缩,这是缓解手段但不是根治。具体文件命名与增长行为以官方文档为准。
小结一下排查顺序:先用 SHOW VARIABLES 确认 internal_tmp_mem_storage_engine 是 TempTable 还是 MEMORY,再用 Created_tmp_disk_tables 与 Created_tmp_tables 的比值判断落盘比例,用 Sort_merge_passes 区分排序还是分组,最后到 events_statements_summary_by_digest 里揪出具体 SQL 去改写。参数调整只是兜底,索引和 SQL 才是压缩临时表体积的主战场。
| 📑 | 📅 |
|---|---|
| Nginx 缓存代理写满磁盘:max_size 与缓存目录清理实战 | 2026-09-28 |
| Linux TCP backlog 溢出排查:ss -lnt 的 Send-Q 怎么看 | 2026-09-28 |
| Docker 容器内 cron 与宿主 crontab 取舍:重复执行与时区错乱排查 | 2026-09-28 |
| MySQL隐形变慢排查:buffer pool命中率与磁盘临时表 | 2026-09-28 |
| Linux僵尸进程排查:ps状态Z到父进程不回收定位 | 2026-09-28 |
| Redis内存碎片率异常:used_memory与RSS差在哪 | 2026-09-28 |
| Docker容器DNS解析异常:127.0.0.11与自定义网络排查 | 2026-09-28 |
| Linux SYN_RECV 堆积排查:半连接队列与 tcp_syncookies | 2026-09-29 |
| Nginx 长连接被中间设备掐断排查:从抓包 RST 到 keepalive_timeout 调优 | 2026-09-29 |
| Docker 容器内 curl 通但宿主不通:端口映射与 iptables 排查 | 2026-09-29 |