宝塔面板MySQL 5.7 导致磁盘IO暴增 临时表空间文件ibtmp1暴增原因及解决方法

1. MySQL临时表空间ibtmp1介绍

关于临时表、临时表空间ibtmp1的概念参考官方解释，临时表一种是用户通过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，自增无上限，每次数据库重启之后会被删除重建。

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

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

ibtmp1增长的原因

ibtmp1增长主要与SQL有关，尤其是大量的分组聚合，排序，join查询SQL.通常如下情况会造成iptmp1上涨：

1.查询语句会先查询temp_table_size(内存分配)的量，当临时存储的量超过这个参数限制时，就会在iptmp1中申请占用空间。

2.select order group by GROUP BY 无索引字段或group by + order by 的子句字段不一样时。

3.select (select) 子查询

4.insert into select … from … 表数据复制

5.select union select 联合语句

临时表空间相关的参数:tmp_table_size ；max_heap_table_size ；innodb_temp_data_file_path

#查看ibtmp1文件大小

解决办法

1.在 MYSQL配置文件限制ibtmp1文件大小：

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

2.优化SQL，避免使用临时表。

3.重启mysql实例释放ibtmp1文件