MySQL数据文件中ibtmp1文件太大，异常空间占用问题处理

今天收到一条数据库磁盘占用告警的邮件，正常来说应该不会占用那么多，登录服务器查看哪个文件或目录占用最大。

从这里可以看到ibtmp1文件用了105GB。ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在支持大文件的系统这个文件大小是可以无限增长的。

临时表释放后，空间会释放，但是磁盘空间不会释放，空闲空间可以被复用。释放磁盘空间只能重启。

登录MySQL， 查看ibtmp1文件大小。

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE/1024/1024 AS TotalSize_MB, DATA_FREE/1024/1024 as FreeSize_MB, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary';

临时表空间占用很大，查看配置

默认配置，没有最大容量限制。

现在我们在这里加上一个上线，比如5GB。修改my.cnf配置，增加如下配置：

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

最后重启MySQL。

总结：可能导致ibtmp1文件暴涨的情况：

1、用到临时表，当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary就表示会用到临时表。
2、GROUP BY无索引字段或GROUP BY + ORDER BY的子句字段不一样时。
3、order by与distinct共用，其中distinct与order by里的字段不一致（主键字段除外）。
4、insert into table1 select xxx from table2。

解决办法:

1、限制 ibtmp1 文件大小：innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
2、优化 SQL，避免使用临时表。
3、重启 mysql 实例释放 ibtmp1 文件