1. 啥情况呀
测试环境机器磁盘空间不足的告警打破了下午的沉寂，一群人开始忙活着删数据 。但是，不久前刚清理了一波数据，测试环境在没做压测的情况下不至于短短一个月不到就涨了200G数据，于是，我悄悄的进入数据目录下，发现一个不寻常的点，ibtmp1文件有192G
ll -hibtmp1 -rw-r----- 1 MySQL mysql 192G Aug 12 16:20 ibtmp12. 怎么处理
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代表最大sizec） 启动mysql服务
启动数据库后可以查一下是否生效
mysql> showvariables 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> showcreate tabletest_tmp1G*************************** 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 byname 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 tabletest_tmp1 add key col2(col2);Query OK, 0 rows affected (1.07 sec)Records: 0Duplicates: 0Warnings: 0/**结果如下，其实该写法与group by +order by 一样*/mysql> explain select distinct col2from test_tmp1 orderbyname;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+| 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)