什么是Internal Temporary？

临时表分为两种，一种是当执行一些SQL的时候MySQL会自动创建的一些中间结果集，称为internal temporary，这些中间结果集可能放在memory中，也有可能放在disk上；

还有一种是手动执行create temporary table语法生成的外部临时表，这种临时表存储在memory上，数据库shutdown，就会自动删除；

本篇讲的临时表都是指内部临时表，测试使用的MySQL版本是8.0.13；

怎么判断有没有使用内部临时表？

执行计划explain或explain format=json 中出现using temporary;

show status中Created_tmp_disk_tables或Created_tmp_tables数值增加；

什么情况下产生Internaltemporary table?

(1)除了后面提到的特殊情况，所有使用union的SQL，但是使用union all没有使用临时表

(2)用到TEMPTABLE算法或者是UNION查询中的视图

mysql>descselect*fromt_orderunionselect*fromt_group;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|PRIMARY|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|UNION|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||NULL|UNIONRESULT|<union1,2>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+3rowsinset,1warning(0.01sec)但是使用unionall没有使用临时表mysql>descselect*fromt_orderunionallselect*fromt_group;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+|1|PRIMARY|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|UNION|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+2rowsinset,1warning(0.00sec)

(3)使用衍生表

(4)子查询和semi-join

mysql>descselect/*+set_var(optimizer_switch='derived_merge=off')*/*from(select*fromt_order)t;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|DERIVED|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+2rowsinset,1warning(0.01sec)mysql>descformat=jsonselect/*+set_var(optimizer_switch='derived_merge=off')*/*from(select*fromt_order)t;......"materialized_from_subquery":{"using_temporary_table":true,

(5)order by和group by的子句不一样时，或者表连接中order by或group by的列是被驱动表中的列；

order by和group by同时使用的时候：

mysql>descselectdept_nofromt_ordergroupbydept_noorderbydept_no;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|1|SIMPLE|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary;Usingfilesort|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+1rowinset,1warning(0.00sec)或者：mysql>setsessionsql_mode='';QueryOK,0rowsaffected(0.00sec)mysql>descselectdept_nofromt_ordergroupbydept_noorderbyemp_no;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|1|SIMPLE|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary;Usingfilesort|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+1rowinset,1warning(0.00sec)

order by和group by分别和join使用的时候：

mysql>descselect*fromt_groupt1joint_ordert2ont1.emp_no=t2.emp_noorderbyt2.emp_no;+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+---------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+---------------------------------+|1|SIMPLE|t1|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary;Usingfilesort||1|SIMPLE|t2|NULL|ref|ix_t1|ix_t1|5|employees.t1.emp_no|1|100.00|NULL|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+---------------------------------+2rowsinset,1warning(0.00sec)mysql>descselect*fromt_groupt1joint_ordert2ont1.emp_no=t2.emp_noorderbyt1.emp_no;+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+----------------+|1|SIMPLE|t1|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingfilesort||1|SIMPLE|t2|NULL|ref|ix_t1|ix_t1|5|employees.t1.emp_no|1|100.00|NULL|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+----------------+2rowsinset,1warning(0.00sec)mysql>descselectt1.dept_nofromt_groupt1joint_ordert2ont1.emp_no=t2.emp_nogroupbyt1.dept_no;+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+|1|SIMPLE|t1|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary||1|SIMPLE|t2|NULL|ref|ix_t1|ix_t1|5|employees.t1.emp_no|1|100.00|Usingindex|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+2rowsinset,1warning(0.00sec)mysql>descselectt2.dept_nofromt_groupt1joint_ordert2ont1.emp_no=t2.emp_nogroupbyt2.dept_no;+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+|1|SIMPLE|t1|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary||1|SIMPLE|t2|NULL|ref|ix_t1|ix_t1|5|employees.t1.emp_no|1|100.00|NULL|+----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+2rowsinset,1warning(0.00sec)

(6)使用distinct或者distinct集合ORDER BY时

mysql>descselectdistinct*fromt_order;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|SIMPLE|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+1rowinset,1warning(0.00sec)

(7)SQL中用到SQL_SMALL_RESULT选项时；

(8)INSERT ... SELECT针对同一个表操作的时候

mysql>descinsertintot_orderselect*fromt_order;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|INSERT|t_order|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|NULL||1|SIMPLE|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+2rowsinset,1warning(0.00sec)

(9)使用GROUP_CONCAT() or COUNT(DISTINCT)

使用group_concat()时产生临时表：

mysql>flushstatus;QueryOK,0rowsaffected(0.02sec)mysql>selectdept_no,group_concat(emp_no)fromt_ordergroupbydept_no;+---------+-------------------------+|dept_no|group_concat(emp_no)|+---------+-------------------------+|d002|31112||d004|10004||d005|24007,30970,40983,50449||d006|22744||d007|49667||d008|48317|+---------+-------------------------+6rowsinset(0.00sec)mysql>showstatuslike'%tmp%';+-------------------------+-------+|Variable_name|Value|+-------------------------+-------+|Created_tmp_disk_tables|0||Created_tmp_files|0||Created_tmp_tables|1|+-------------------------+-------+3rowsinset(0.00sec)

使用count(distinct)时产生临时表：

mysql>flushstatus;QueryOK,0rowsaffected(0.02sec)mysql>descselectcount(distinctdept_no)fromt_order;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+|1|SIMPLE|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+1rowinset,1warning(0.00sec)mysql>showstatuslike'%tmp%';+-------------------------+-------+|Variable_name|Value|+-------------------------+-------+|Created_tmp_disk_tables|0||Created_tmp_files|0||Created_tmp_tables|1|+-------------------------+-------+3rowsinset(0.01sec)

什么情况下产生的内部临时表不是在内存中，而是在磁盘上？

(1)表存在blob或text字段；

(2)在SELECT UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；

(3)使用show columns和describe命令在存在blob列的表上；

内部临时表使用什么存储引擎？

MySQL8.0.2开始支持internal_tmp_mem_storage_engine参数；

(1)当internal_tmp_mem_storage_engine=TempTable时，

TempTable存储引擎为varchar和varbinary数据类型提供高效的存储，temptable_max_ram=1G定义临时表最大可以使用的内存空间，但是如果参数temptable_use_mma=on，则表示可以继续使用内存存储临时表，如果off，则临时表超过阈值，只能使用磁盘存储；

(2)当internal_tmp_mem_storage_engine=memory时：

内部临时表大小超过参数tmp_table_size和max_heap_table_size时候，会自动从内存中转移到磁盘上，内部临时表在磁盘上默认使用的是innodb存储引擎，由参数internal_tmp_disk_storage_engine决定.

参考链接

Internal Temporary Table Use in MySQL