问题的形式解答：

一、MySQL在什么情况下会创建临时表(Internal Temporary Table Use in MySQL)？

我列举3个

1. UNION查询;

2. insert into select ...from ...

3. ORDER BY和GROUP BY的子句不一样时;

4.数据表中包含blob/text列

等等，其实还有好多。具体参考 https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

二、怎么知道mysql用了临时表呢？

这个问题很简单，EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表。举个例子，有个感性认识。

创建测试表t22 :create table t22 as select * from information_schema.tables;

mysql>desct22;+-----------------+---------------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------------+---------------------+------+-----+---------+-------+|TABLE_CATALOG|varchar(512)|NO|||||TABLE_SCHEMA|varchar(64)|NO|||||TABLE_NAME|varchar(64)|NO|||||TABLE_TYPE|varchar(64)|NO|||||ENGINE|varchar(64)|YES||NULL|||VERSION|bigint(21)unsigned|YES||NULL|||ROW_FORMAT|varchar(10)|YES||NULL|||TABLE_ROWS|bigint(21)unsigned|YES||NULL|||AVG_ROW_LENGTH|bigint(21)unsigned|YES||NULL|||DATA_LENGTH|bigint(21)unsigned|YES||NULL|||MAX_DATA_LENGTH|bigint(21)unsigned|YES||NULL|||INDEX_LENGTH|bigint(21)unsigned|YES||NULL|||DATA_FREE|bigint(21)unsigned|YES||NULL|||AUTO_INCREMENT|bigint(21)unsigned|YES||NULL|||CREATE_TIME|datetime|YES||NULL|||UPDATE_TIME|datetime|YES||NULL|||CHECK_TIME|datetime|YES||NULL|||TABLE_COLLATION|varchar(32)|YES||NULL|||CHECKSUM|bigint(21)unsigned|YES||NULL|||CREATE_OPTIONS|varchar(255)|YES||NULL|||TABLE_COMMENT|varchar(2048)|NO||||+-----------------+---------------------+------+-----+---------+-------+21rowsinset(0.02sec)mysql>explain->selecttable_schema,table_name,create_timefromt22wheretable_schemalike'test%'->union->selecttable_schema,table_name,create_timefromt22wheretable_schemalike'information%'->;+----+--------------+------------+------------+------+---------------+------+---------+------+----------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+------+---------------+------+---------+------+----------+----------+-----------------+|1|PRIMARY|t22|NULL|ALL|NULL|NULL|NULL|NULL|12522369|11.11|Usingwhere||2|UNION|t22|NULL|ALL|NULL|NULL|NULL|NULL|12522369|11.11|Usingwhere||NULL|UNIONRESULT|<union1,2>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+------+---------------+------+---------+------+----------+----------+-----------------+3rowsinset,1warning(0.02sec)

三、临时表有关的参数有哪些？

innodb_temp_data_file_path = ibtmp1:12M:autoextend
tmp_table_size = 16777216
max_heap_table_size =16777216
default_tmp_storage_engine=InnoDB

internal_tmp_disk_storage_engine=InnoDB

四、mysql临时表配置参数是tmp_table_size，当临时表空间不够用的时候怎么办？

如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），这时候就需要生成基于磁盘的临时表了。也就是放在innodb_temp_data_file_path指定的临时表空间中。

如果你对这句话有疑问，那我举个例子来看下：反复执行语句： insert into t22 select * from t22; 同时查看表空间ibtmp1的大小变化。反复执行insert 语句，插入表中的数量指数级增长。

看下例子：

五、看图说话，做了上个实验，不知道你是否会有如下想法：既然内部临时表(Internal Temporary Table)用于排序，分组，当需要的存储空间超过tmp-table-size上限的时候，使用临时表空间。临时表空间是磁盘，速度比不上内存，那是不是可以加大tmp_table_size来优化需要使用临时表的SQL语句？

当然可以呀，tmp_table_size最大值是18446744073709551615，如果建议256M。

六、mysql中是如何监控临时表和临时表空间使用情况的？

mysql>showstatuslike'%tmp%';+-------------------------+-------+|Variable_name|Value|+-------------------------+-------+|Created_tmp_disk_tables|1||Created_tmp_files|7||Created_tmp_tables|18|+-------------------------+-------+

建议Created_tmp_disk_tables/Created_tmp_tables不要超过25%。如果Created_tmp_disk_tables数量很大，查看是否有很多慢sql，是否有很多使用临时表的语句。加大tmp_table_size的值。

七、mysql的临时表空间文件暴增，可以达到几百G，你认为形成的原因是什么？

第四个问题做的例子，如果你不停的反复的实验，你会发现ibtmp1增长的速度惊人。有个项目，曾经ibtmp1暴增到300G。一看慢sql日志，有大量慢sql，而且有很多语句需要排序。所以给ibtmp1加上限制最大值。innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G，mysql会反复利用。

参考：老叶茶馆

https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=207355450&idx=3&sn=3e3a2c0a7497a8cd099ddc5c33a9932d&scene=21#wechat_redirect