什么是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数值增加；
什么情况下产生Internal temporary table?
(1)除了后面提到的特殊情况，所有使用union的SQL，但是使用union all没有使用临时表
(2)用到TEMPTABLE算法或者是UNION查询中的视图
mysql> desc select * from t_order union select * from t_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 | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.01 sec) 但是使用union all没有使用临时表 mysql> desc select * from t_order union all select * from t_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 | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
(3)使用衍生表
(4)子查询和semi-join
mysql> desc select /*+ set_var(optimizer_switch='derived_merge=off') */ * from (select * from t_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 |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
mysql> desc format=json select /*+ set_var(optimizer_switch='derived_merge=off') */ * from (select * from t_order)t;
......
"materialized_from_subquery": {
"using_temporary_table": true,(5)order by和group by的子句不一样时，或者表连接中order by或group by的列是被驱动表中的列；
order by和group by 同时使用的时候：
mysql> desc select dept_no from t_order group by dept_no order by dept_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 | Using temporary; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec) 或者： mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> desc select dept_no from t_order group by dept_no order by emp_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 | Using temporary; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
order by 和group by 分别和join使用的时候：
mysql> desc select * from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no order by t2.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 | Using temporary; Using filesort | | 1 | SIMPLE | t2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+---------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> desc select * from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no order by t1.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 | Using filesort | | 1 | SIMPLE | t2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) mysql> desc select t1.dept_no from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no group by t1.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 | Using temporary | | 1 | SIMPLE | t2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+ 2 rows in set, 1 warning (0.00 sec) mysql> desc select t2.dept_no from t_group t1 join t_order t2 on t1.emp_no=t2.emp_no group by t2.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 | Using temporary | | 1 | SIMPLE | t2 | NULL | ref | ix_t1 | ix_t1 | 5 | employees.t1.emp_no | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------+---------+---------------------+------+----------+-----------------+ 2 rows in set, 1 warning (0.00 sec)
(6)使用distinct或者distinct集合ORDER BY时
mysql> desc select distinct * from t_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 | Using temporary | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)
(7)SQL中用到SQL_SMALL_RESULT选项时；
(8)INSERT ... SELECT针对同一个表操作的时候
mysql> desc insert into t_order select * from t_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 | Using temporary | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 2 rows in set, 1 warning (0.00 sec)
(9)使用GROUP_CONCAT() or COUNT(DISTINCT)
使用group_concat()时产生临时表：
mysql> flush status; Query OK, 0 rows affected (0.02 sec) mysql> select dept_no,group_concat(emp_no) from t_order group by dept_no; +---------+-------------------------+ | dept_no | group_concat(emp_no) | +---------+-------------------------+ | d002 | 31112 | | d004 | 10004 | | d005 | 24007,30970,40983,50449 | | d006 | 22744 | | d007 | 49667 | | d008 | 48317 | +---------+-------------------------+ 6 rows in set (0.00 sec) mysql> show status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+ 3 rows in set (0.00 sec)
使用count(distinct)时产生临时表：
mysql> flush status; Query OK, 0 rows affected (0.02 sec) mysql> desc select count(distinct dept_no) from t_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 | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+ 3 rows in set (0.01 sec)
什么情况下产生的内部临时表不是在内存中，而是在磁盘上？
(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
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
