早上查看磁盘的时候发现磁盘空间100%

排查文件，原来是www/server/data/ibtmp1，已经21G多了。此文件是用于数据库临时表空间

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文件大小

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';

解决办法

1.需在配置文件：/etc/my.cnf中加入限制

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

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

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

参考：

一、临时表空间

MySQL 5.7在临时表空间上做了改进，已经实现将临时表空间从 ibdata（共享表空间文件）中分离。并且可以重启重置大小，避免出现像以前 ibdata 过大难以释放的问题。

1、表现

MySQL启动时 datadir 下会创建一个 ibtmp1 文件，默认配置为 ibtmp1:12M:autoextend，即初始大小为 12M，默认值下会无限扩展。

通常来说，查询导致的临时表（如group by）如果超出 tmp_table_size、max_heap_table_size 大小限制则创建 innodb 磁盘临时表（MySQL5.7默认临时表引擎为 innodb），存放在共享临时表空间。

如果某个操作创建了一个大小为100M的临时表，则临时表空间数据文件会扩展到100M大小以满足临时表的需要。当删除临时表时，释放的空间可以重新用于新的临时表，但 ibtmp1 文件保持扩展大小。

2、临时表空间与 tmpdir 对比

共享临时表空间用于存储非压缩InnoDB临时表(non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment)等数据；

tmpdir 用于存放指定临时文件(temporary files)和临时表(temporary tables)，与共享临时表空间不同的是，tmpdir存储的是压缩的InnoDB临时表。

二、什么情况下会用到临时表？

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象，当 explain 查看执行计划结果的 extra 列中，如果包含 Using Temporary 就表示会用到临时表，例如如下几种常见的情况通常就会用到：

1、insert into table1  select ...  from table2 。

2、group by 无索引字段或 group by 和 order by 的字段不一样。

3、distinct 的值和 group by 的值不一样，无法利用稀疏索引。

三、怎么解决 ibtmp1 文件空间占用的问题？

1、万能的重启大法，找个合适的时间，切换数据库，重启老的主库。

因为临时表空间使用动态的表空间ID，因此每次重启时都会变化（每次重启时，都会重新初始化临时表空间文件）。

2、通过配置 innodb_temp_data_file_path 控制 ibtmp1 文件的最大值，避免表空间大小无限增加。

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

12M是文件的初始大小，10G是文件的最大值，超过最大值则系统会提示报错

ERROR 1114 (HY000): The table '/data/msb_5_7_31/tmp#sql_xxxxx_0' is full

来源：https://www.modb.pro/db/621529