MYSQL的ibtmp1文件太大怎么处理

1. 啥情况呀

测试环境机器磁盘空间不足的告警打破了下午的沉寂，一群人开始忙活着删数据。但是，不久前刚清理了一波数据，测试环境在没做压测的情况下不至于短短一个月不到就涨了200G数据，于是，我悄悄的进入数据目录下，发现一个不寻常的点，ibtmp1 文件有192G

ll-hibtmp1
-rw-r-----1mysqlmysql192GAug1216：20ibtmp1

2. 怎么处理

2.1 简单说明

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

2.2 解决办法

a) 找个空闲时间关闭数据

#设置innodb_fast_shutdown参数SETGLOBALinnodb_fast_shutdown=0;#此步骤可以省略
#关闭数据库实例shutdown;#因本实例为MySQL5.7可以直接在SQL命令行中shutdown关闭

关闭后ibtmp1 文件会自动清理

b) 修改my.cnf配置文件

为了避免ibtmp1 文件无止境的暴涨导致再次出现此情况，可以修改参数，限制其文件最大尺寸。

如果文件大小达到上限时，需要生成临时表的SQL无法被执行(一般这种SQL效率也比较低，可借此机会进行优化)

innodb_temp_data_file_path=ibtmp1：12M：autoextend：max：5G#12M代表文件初始大小，5G代表最大size

c) 启动mysql服务

启动数据库后可以查一下是否生效

mysql>showvariableslike'innodb_temp_data_file_path';+----------------------------+-------------------------------+|Variable_name|Value|+----------------------------+-------------------------------+|innodb_temp_data_file_path|ibtmp1：12M：autoextend：max：5G|+----------------------------+-------------------------------+1rowinset(0.01sec)

3. 什么情况下会用到临时表

当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表，例如如下几种常见的情况通常就会用到：

a) GROUP BY 无索引字段或GROUP BY+ ORDER BY 的子句字段不一样时

/**先看一下表结构*/mysql>showcreatetabletest_tmp1\G***************************1.row***************************
Table：test_tmp1CreateTable：CREATETABLE`test_tmp1`(
`id`int(11)NOTNULLAUTO_INCREMENT，
`name`varchar(50)DEFAULTNULL，
`col2`varchar(25)DEFAULTNULL，PRIMARYKEY(`id`)，KEY`name`(`name`)
)ENGINE=InnoDBAUTO_INCREMENT=16DEFAULTCHARSET=utf81rowinset(0.00sec)/**groupby无索引字段*/mysql>explainselect*fromtest_tmp1groupbycol2;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|1|SIMPLE|test_tmp1|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingtemporary;Usingfilesort|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+/**groupby与orderby字段不一致时，及时groupby和orderby字段有索引也会使用*/mysql>explainselectnamefromtest_tmp1groupbynameorderbyiddesc;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+|1|SIMPLE|test_tmp1|NULL|range|name|name|153|NULL|3|100.00|Usingindexforgroup-by;Usingtemporary;Usingfilesort|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+1rowinset，1warning(0.02sec)

b) order by 与distinct 共用，其中distinct与order by里的字段不一致(主键字段除外)

/**例子中有无索引时会存在，如果2个字段都有索引会如何*/mysql>altertabletest_tmp1addkeycol2(col2);
QueryOK，0rowsaffected(1.07sec)
Records：0Duplicates：0Warnings：0/**结果如下，其实该写法与groupby+orderby一样*/mysql>explainselectdistinctcol2fromtest_tmp1orderbyname;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+|1|SIMPLE|test_tmp1|NULL|index|col2|col2|78|NULL|8|100.00|Usingtemporary;Usingfilesort|+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+1rowinset，1warning(0.00sec)

c) UNION查询(MySQL5. 7 后union all已不使用临时表)

/**先测一下unionall的情况*/mysql>explainselectnamefromtest_tmp1unionallselectnamefromtest_tmp1whereid<10;+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|1|PRIMARY|test_tmp1|NULL|index|NULL|name|153|NULL|8|100.00|Usingindex||2|UNION|test_tmp1|NULL|range|PRIMARY|PRIMARY|4|NULL|8|100.00|Usingwhere|+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+2rowsinset，1warning(0.01sec)/**再看一下union作为对比，发现出现了使用临时表的情况*/mysql>explainselectnamefromtest_tmp1unionselectnamefromtest_tmp1whereid<10;+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|1|PRIMARY|test_tmp1|NULL|index|NULL|name|153|NULL|8|100.00|Usingindex||2|UNION|test_tmp1|NULL|range|PRIMARY|PRIMARY|4|NULL|8|100.00|Usingwhere||NULL|UNIONRESULT|<union1，2>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+3rowsinset，1warning(0.00sec)

d) insert into select ...from ...

/**简单看一下本表的数据重复插入的情况*/mysql>explaininsertintotest_tmp1(name，col2)selectname，col2fromtest_tmp1;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|INSERT|test_tmp1|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|NULL||1|SIMPLE|test_tmp1|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingtemporary|+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+2rowsinset(0.00sec)

小结： 上面列举的是最常见的使用临时表的情况，其中基本都是引起慢查询的因素，因此，如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。

4. 和临时表空间相关的参数有哪些

各参数之间相互影响，其中直接影响临时表空间的参数如要有如下几个

innodb_temp_data_file_path
tmp_table_size
max_heap_table_size
default_tmp_storage_engine
internal_tmp_disk_storage_engine

5. 下面来模拟一个ibtmp1 文件快速膨胀的例子

5.1 调整参数值

上面列出了主要的参数，那么先调整一下参数，以便于模拟

tmp_table_size=16M
innodb_temp_data_file_path=ibtmp1：12M：autoextend：max：5G

调整后重启数据库

5.2 造一批数据

/**造一张表或者从其他表复制一批数据，为了方便模拟，可以不创建主键及索引*/mysql>createtabletest_tmp3select*fromdb1.tbname;
QueryOK，15948372rowsaffected(2min27.24sec)
Records：15948372Duplicates：0Warnings：0

此时查看一下ibtmp1 文件的大小

ll-hibtmp1
-rw-r-----1mysqlmysql12MAug1516：06ibtmp1/**此时是默认的初始大小*/

5.2 使用insert into ... select * from ...的方式插入

/**此方式将会使用临时表空间，且tmp_table_size参数已调小为16M，本表当前有2G多，所以会使用临时表空间*/mysql>insertintotest_tmp3select*fromtest_tmp3;
QueryOK，15948372rowsaffected(2min7.40sec)
Records：15948372Duplicates：0Warnings：0

此时 查看一下ibtmp1 文件的大小

ll-hibtmp1
-rw-r-----1mysqlmysql2.8GAug1516：17ibtmp1/**此时已使用了2.8G*/

此时该表的size如下

ll-hbak_db/test_tmp3*/**结果中已有5.8G*/-rw-r-----1mysqlmysql8.9KAug1516：04bak_db/test_tmp3.frm-rw-r-----1mysqlmysql5.8GAug1516：16bak_db/test_tmp3.ibd

5.3 继续测试，看看会发生什么

因为ibtmp1 当前设置的最大值为5G，继续复制一个5.8G的数据，会不会异常，如果异常有什么表现?

/**继续插入时因临时表空间大小有限制，超过5G后将异常，信息如下*/mysql>insertintotest_tmp3select*fromtest_tmp3;
ERROR1114(HY000)：Thetable'/app/data/mysql3306/tmp/#sql_32469_0'isfull

此时 查看一下ibtmp1 文件的大小

ll-hibtmp1
-rw-r-----1mysqlmysql5.0GAug1516：17ibtmp1/**此时已使用了5.0G，已达到上限*/

数据库日志里也会记录本次异常

2019-08-15T08：23：47.016495Z3[ERROR]/usr/local/mysql5.7/bin/mysqld：Thetable'/app/data/mysql3306/tmp/#sql_32469_0'isfull

以上测试实例因不同的版本可能会有差异，建议大家亲自测试一下。

本文转载自微信公众号【数据库干货铺】。

（举报）