MySQL 5.7 临时表空间文件ibtmp1暴增原因及解决方法

1. MySQL临时表空间介绍

  关于临时表、临时表空间的概念参考官方解释，临时表一种是用户通过create temporary table创建的显式临时表，另一种是复杂SQL执行时临时创建的隐式辅助表，当临时表需要存储的数据超过了tmp_table_size或max-heap-table-size中的较大值，那么临时表数据将会存储到磁盘，在MySQL 5.7中就是基于ibtmp1文件的临时表空间中。显式临时表数据和undo存于ibtmp1时，用户断开连接虽然释放了临时表，但实际的使用空间并不会释放，只有重启数据库才能真正释放这部分空间（这部分的功能bug在MySQL 8.0中得到解决）。另外，关于临时表的具体占用情况可以通过INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO查看。
   MySQL 5.7中临时表空间通过innodb_temp_data_file_path参数控制，可以在配置文件my.cnf中根据需要设置临时表空间的路径、名称、大小，默认是ibtmp1:12M:autoextend，默认初始大小为12M，自增无上限，每次数据库重启之后会被删除重建。

mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.01 sec)

2. 临时表空间文件ibtmp1暴增原因及解决方法

   运维同事反应zabbix监测到数据库服务器/home使用率突然暴增到80%，通过查询发现是暴增源头是ibtmp1文件。其实在发现这个警告之前一个小时在这台机上做查询操作的同事反应查询卡死，原因为复杂查询与索引造成的死锁问题。后续排查定位问题是几个低效的SQL造成的，需要优化处理。

[root@mysqlplus2 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/cl-root   50G  4.5G   46G   9% /
devtmpfs              63G     0   63G   0% /dev
tmpfs                 63G     0   63G   0% /dev/shm
tmpfs                 63G  386M   63G   1% /run
tmpfs                 63G     0   63G   0% /sys/fs/cgroup
/dev/sda1           1014M  141M  874M  14% /boot
`/dev/mapper/cl-home  1.1T  1.1T   26G  98% /home`
tmpfs                 13G     0   13G   0% /run/user/0

[root@mysqlplus2 ~]# ll -h /home/mysql/data/
total 840G
-rw-r----- 1 mysql mysql 332M Oct 11 14:21 ibdata1
-rw-r----- 1 mysql mysql  48M Oct 11 14:21 ib_logfile0
-rw-r----- 1 mysql mysql  48M Oct 11 03:00 ib_logfile1