c) UNION查询（MySQL5.7后union all已不使用临时表）
/**先测一下union all的情况*/mysql> explain select name from test_tmp1 union allselect 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 unionselect 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 | <union1,2> | 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_sizemax_heap_table_sizedefault_tmp_storage_engineinternal_tmp_disk_storage_engine5. 下面来模拟一个ibtmp1文件快速膨胀的例子
5.1 调整参数值
上面列出了主要的参数，那么先调整一下参数，以便于模拟
tmp_table_size = 16Minnodb_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: 15948372Duplicates: 0Warnings: 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 intotest_tmp3 select* from test_tmp3 ;Query OK, 15948372 rows affected (2 min 7.40 sec)Records: 15948372Duplicates: 0Warnings: 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.ibd5.3 继续测试,看看会发生什么
因为ibtmp1当前设置的最大值为5G，继续复制一个5.8G的数据，会不会异常，如果异常有什么表现？
/**继续插入时 因临时表空间大小有限制，超过5G后将异常，信息如下*/mysql> insert intotest_tmp3 select* from test_tmp3;ERROR 1114 (HY000): The table '/App/data/mysql3306/tmp/#sql_32469_0' is full