MySQL里藏着一个会自己长大的文件，90%的DBA都忽略了

那是一个再普通不过的周三下午，阳光透过落地窗在工位上投下斑驳的光影。我正慢悠悠的等下班——突然，手机“叮”地一声炸响，一条严重告警：“磁盘使用率超98%告警”。

1. 【紧急告警】测试服务器磁盘使用率 98%！

整个办公室瞬间安静了一秒，接着炸开了锅。 “不是上周才清理过日志吗？怎么又满了？” “是不是谁又跑了个全量导出？” “快查查是不是有大文件！”

DBA同事手忙脚乱地登录服务器，开始 du -sh * 扫描目录。我也悄悄 SSH 进了 MySQL 的数据目录，想看看有没有异常。

然后，我看到了它——192G的文件

-rw-r----- 1 mysql mysql 192G Aug 12 16:20 ibtmp1
192GB！

一个名字平平无奇、却如黑洞般吞噬磁盘空间的文件——ibtmp1。

2. 这个“幽灵文件”到底是什么？

在 MySQL 5.7 及之后版本中，ibtmp1 是 InnoDB 引擎用于存储临时表的独立表空间文件。默认配置是：

innodb_temp_data_file_path = ibtmp1:12M:autoextend

意思是：初始大小12MB，自动扩展，理论上可以无限增长（只要磁盘还有空间），而问题就出在这“无限”二字上。

而本次问题的起因也是因为测试环境没人关注性能问题（只关注数据库是否可用），一旦有低效 SQL 频繁创建临时表，ibtmp1 就会像吹气球一样膨胀——直到撑爆整个磁盘。

3. 如何“驱魔”？三步搞定

第一步：优雅停服，清空幽灵

临时表空间只在 MySQL 运行时存在。只要重启实例，ibtmp1 就会自动清零。但为了数据安全，建议先设置：

SET GLOBAL innodb_fast_shutdown = 0;
SHUTDOWN;

这样能确保 InnoDB 做一次完整的 checkpoint，避免意外。

注：MySQL 5.7+ 支持直接在 SQL 中执行 SHUTDOWN。

关机后，那个 192G 的“幽灵”果然消失了。

第二步：设上限，防复发

光清空不够，得治本。我们在 my.cnf 中加上限制：

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:20G

现在，哪怕再有疯狂 SQL，最多也只能吃掉 20GB —— 超了就报错，反而能暴露问题。

重启 MySQL 后验证：

SHOW VARIABLES LIKE 'innodb_temp_data_file_path';
-- 输出：ibtmp1:12M:autoextend:max:20G 

第三步：揪出“真凶”——那些制造临时表的 SQL

为什么会有这么大临时表？得看哪些查询触发了 Using Temporary。

通过查询慢SQL，并查看执行计划，我们发现以下几种情况最容易“召唤幽灵”：

• GROUP BY 用到了无索引字段

EXPLAIN SELECT * FROM test_tmp1 GROUP BY col2;
-- Extra: Using temporary; Using filesort

• GROUP BY 和 ORDER BY 字段不一致

即使都有索引，也会用临时表：

EXPLAIN SELECT name FROM test_tmp1 GROUP BY name ORDER BY id DESC;
-- Extra: Using index for group-by; Using temporary; Using filesort

• DISTINCT + ORDER BY 字段不匹配

EXPLAIN SELECT DISTINCT col2 FROM test_tmp1 ORDER BY name;
-- Extra: Using temporary; Using filesort

• UNION（注意：UNION ALL 已优化，不使用临时表）

EXPLAIN SELECT name FROM t1 UNION SELECT name FROM t1 WHERE id < 10;
-- 出现 UNION RESULT 行，Extra: Using temporary

• INSERT INTO ... SELECT ...

尤其是大表自复制：

INSERT INTO test_tmp3 SELECT * FROM test_tmp3;
-- Extra: Using temporary

💡 小结： 几乎所有用到临时表的场景，都是慢查询的高危信号。 ibtmp1 暴涨，其实是数据库在向你求救：“快优化这些烂 SQL！”

4. 模拟复现：亲眼见证 ibtmp1 膨胀

4.1 模拟使用临时表空间的场景

为了验证，我们做了个小实验：

• 创建一张 1500 万行的大表 test_tmp3（无主键、无索引）；
• 设置 tmp_table_size = 16M，ibtmp1:12M:autoextend:max:2G， 逼 MySQL 使用磁盘临时表；
• 执行 INSERT INTO test_tmp3 SELECT * FROM test_tmp3;

结果：

• 初始 ibtmp1：12MB
• 执行后：2.8GB
• 再执行一次（上限设为 2G）：报错！

ERROR 1114 (HY000): The table '/tmp/#sql_xxx' is full

日志里也记了一笔：

[ERROR] The table '/tmp/#sql_xxx' is full

这正是我们想要的“熔断机制”——宁可让 SQL 失败，也不能让服务器宕机。

4.2 相关参数

除了 innodb_temp_data_file_path，还有几个参数会影响临时表行为：

• tmp_table_size：内存临时表最大值
• max_heap_table_size：用户定义 MEMORY 表的上限
• default_tmp_storage_engine：临时表默认引擎（5.7+ 默认 InnoDB）
• internal_tmp_disk_storage_engine：磁盘临时表引擎（8.0+ 引入）

它们相互影响，共同决定临时表是走内存还是磁盘。

5 尾声：幽灵退散，系统重生

那天晚上，我们不仅清掉了 192G 的“幽灵”，还顺藤摸瓜干掉了三个低效查询。从此，测试环境再没因 ibtmp1 报警。而我也明白了一个道理：数据库不会无缘无故变慢，也不会无缘无故吃光磁盘。它每一次“异常”，都是在用沉默的方式，讲述一个又一个被忽视的故事。

下次当你看到 ibtmp1 异常膨胀，请别急着删文件——先问问：是谁，在背后写下了那行“可怕”的 SQL？

大家平时还遇到哪些问题，也欢迎留言交流。