1. 啥情况呀
测试环境机器磁盘空间不足的告警打破了下午的沉寂，一群人开始忙活着删数据。但是，不久前刚清理了一波数据，测试环境在没做压测的情况下不至于短短一个月不到就涨了200G数据，于是，我悄悄的进入数据目录下，发现一个不寻常的点，ibtmp1 文件有192Gll -hibtmp1
-rw-r----- 1 mysql mysql 192G Aug 12 16：20 ibtmp1
2. 怎么处理
2.1 简单说明
ibtmp1 是非压缩的innodb临时表的独立表空间，通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1：12M：autoextend，也就是说在支持大文件的系统这个文件大小是可以无限增长的。
2.2 解决办法
a) 找个空闲时间关闭数据#设置innodb_fast_shutdown参数SET GLOBAL innodb_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> show variables like 'innodb_temp_data_file_path';+----------------------------+-------------------------------+| Variable_name | Value |+----------------------------+-------------------------------+| innodb_temp_data_file_path | ibtmp1：12M：autoextend：max：5G |+----------------------------+-------------------------------+1 row in set (0.01 sec)
3. 什么情况下会用到临时表
当EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表，例如如下几种常见的情况通常就会用到：
a) GROUP BY 无索引字段或GROUP BY+ ORDER BY 的子句字段不一样时/**先看一下表结构 */mysql> show create tabletest_tmp1\G*************************** 1. row ***************************
Table： test_tmp1Create Table： CREATE TABLE `test_tmp1` (
`id` int(11) NOT NULL AUTO_INCREMENT，
`name` varchar(50) DEFAULT NULL，
`col2` varchar(25) DEFAULT NULL，PRIMARY KEY (`id`)，KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf81 row in set (0.00 sec)/**groupby无索引字段*/mysql> explain select * from test_tmp1 group bycol2 ;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| 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 | Using temporary; Using filesort |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+/**group by 与order by字段不一致时，及时group by和order by字段有索引也会使用 */mysql> explain select name from test_tmp1 group by name order by id desc;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+| 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 | Using index for group-by; Using temporary; Using filesort |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+1 row in set， 1 warning (0.02 sec)
b) order by 与distinct 共用，其中distinct与order by里的字段不一致(主键字段除外)/**例子中有无索引时会存在，如果 2 个字段都有索引会如何*/mysql> alter table test_tmp1 add key col2(col2);
Query OK， 0 rows affected (1.07 sec)
Records： 0 Duplicates： 0 Warnings： 0/** 结果如下，其实该写法与group by +order by 一样*/mysql> explain select distinct col2 from test_tmp1 order byname;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+| 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 | Using temporary; Using filesort |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+1 row in set， 1 warning (0.00 sec)
c) UNION查询(MySQL5. 7 后union all已不使用临时表)/**先测一下union all的情况*/mysql> explain select name from test_tmp1 union all select name from test_tmp1 where id <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 | Using index || 2 | UNION | test_tmp1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8 | 100.00 | Using where |+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+2 rows in set， 1 warning (0.01 sec)/**再看一下union 作为对比，发现出现了使用临时表的情况*/mysql> explain select name from test_tmp1 union select name from test_tmp1 where id <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 | Using index || 2 | UNION | test_tmp1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 8 | 100.00 | Using where || NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+3 rows in set， 1 warning (0.00 sec)
d) insert into select ...from .../**简单看一下本表的数据重复插入的情况 */mysql> explain insert into test_tmp1(name，col2) select name，col2 from test_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 | Using temporary |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+2 rows in set (0.00 sec)
小结： 上面列举的是最常见的使用临时表的情况，其中基本都是引起慢查询的因素，因此，如果遇到临时表空间文件暴涨是需要查看一下是否有大量的慢查询。
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> create table test_tmp3 select * from db1.tbname;
Query OK， 15948372 rows affected (2 min 27.24 sec)
Records： 15948372 Duplicates： 0 Warnings： 0
此时查看一下ibtmp1 文件的大小ll -h ibtmp1
-rw-r----- 1 mysql mysql 12M Aug 15 16：06 ibtmp1 /**此时是默认的初始大小*/
5.2 使用insert into ... select * from ...的方式插入/**此方式将会使用临时表空间，且 tmp_table_size参数已调小为16M，本表当前有2G多，所以会使用临时表空间*/mysql> insert into test_tmp3 select * from test_tmp3 ;
Query OK， 15948372 rows affected (2 min 7.40 sec)
Records： 15948372 Duplicates： 0 Warnings： 0
此时 查看一下ibtmp1 文件的大小ll -h ibtmp1
-rw-r----- 1 mysql mysql 2.8G Aug 15 16：17 ibtmp1 /**此时已使用了2.8G*/
此时该表的size如下ll -h bak_db/test_tmp3* /** 结果中已有5.8G*/-rw-r----- 1 mysql mysql 8.9K Aug 15 16：04 bak_db/test_tmp3.frm-rw-r----- 1 mysql mysql 5.8G Aug 15 16：16 bak_db/test_tmp3.ibd
5.3 继续测试，看看会发生什么
因为ibtmp1 当前设置的最大值为5G，继续复制一个5.8G的数据，会不会异常，如果异常有什么表现?/** 继续插入时 因临时表空间大小有限制，超过5G后将异常，信息如下*/mysql> insert into test_tmp3 select * from test_tmp3;
ERROR 1114 (HY000)： The table '/app/data/mysql3306/tmp/#sql_32469_0' is full
此时 查看一下ibtmp1 文件的大小ll -h ibtmp1
-rw-r----- 1 mysql mysql 5.0G Aug 15 16：17 ibtmp1/**此时已使用了5.0G，已达到上限*/
数据库日志里也会记录本次异常2019-08-15T08：23：47.016495Z 3 [ERROR] /usr/local/mysql5.7/bin/mysqld： The table '/app/data/mysql3306/tmp/#sql_32469_0' is full
以上测试实例因不同的版本可能会有差异，建议大家亲自测试一下。
