MySQL Internal Temporary

什么是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 |  | 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     |  | 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