概述
在MySQL运行过程中，会产生各种各样的临时表和临时文件，有外部临时文件和内部临时文件。今天，主要讲述的是内部临时表是如何产生，并且哪些语句会产生的。
本篇测试是在mysql版本5.7下完成，8.0优化了很多临时表使用的细节，这个以后会单独开一篇文章讲述。
介绍
MySQL中的内部临时表分为两种，一种为创建并存在于内存中的临时表，另一种是创建并存在于硬盘上的临时表。先来介绍第一种：
内存临时表
MySQL服务器会在以下条件下建立临时表：
- UNION语句
mysql> explain select id from t1 union select id from t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | NULL |
| 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 32 | 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.21 sec)
mysql>
复制上面的执行计划中，<union1,2>就是一张内存临时表，在Extra中也明确说明了“Using temporary”。
2. 使用TEMPTABLE算法建立的视图
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.26 sec)
mysql> create ALGORITHM=TEMPTABLE view t3 as select * from t1;
Query OK, 0 rows affected (0.36 sec)
mysql> explain select * from t3;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | NULL |
| 2 | DERIVED | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.39 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.24 sec)
mysql>
复制通过show status和explain发现，确实创建了内存临时表。
3. derived tables
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.31 sec)
mysql> explain select id1,name1 from (select id as id1 , name as name1 from t1) as a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.23 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.26 sec)
复制虽然在explain执行计划中并没有体现，但是在show status中产生了一个临时表。这里为什么产生了临时表呢。是因为select的列名不同，所以导致产生了一个临时表。
4. 子查询或半连接所创建的临时表
在使用in或not in语句时，优化器会根据成本来选择一种最优的算法，其中，Materialization算法会创建一张临时表。
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.20 sec)
mysql> explain select * from t1 where id in (select id from t2 where name like '%f%');
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | test.t1.id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 32 | 11.11 | Using where |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------+------+----------+-------------+
3 rows in set, 1 warning (0.22 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.22 sec)
mysql>
复制- order by或group by语句
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.31 sec)
mysql> explain select * from t1 where id > 1 group by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 33.33 | Using where; Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.15 sec)
mysql> select * from t1 where id > 1 group by name order by name;
+------+------+
| id | name |
+------+------+
| 3 | eee |
| 2 | fff |
+------+------+
2 rows in set (0.20 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.22 sec)
复制- DISTINCT和order by一起使用时
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.27 sec)
mysql> explain select distinct name from t1 order by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.18 sec)
mysql> select distinct name from t1 order by id;
+------+
| name |
+------+
| wef |
| fff |
| eee |
+------+
3 rows in set (0.17 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.20 sec)
复制- SQL_SMALL_RESULT与group by或distinct结合使用
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.17 sec)
mysql> explain select SQL_SMALL_RESULT name from t1 group by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.21 sec)
mysql> select SQL_SMALL_RESULT name from t1 group by name;
+------+
| name |
+------+
| wef |
| fff |
| eee |
+------+
3 rows in set (0.05 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
复制SQL_SMALL_RESULT修饰符的意思是：告诉优化器取得的结果集非常小，可以直接使用临时表来缓存结果集。
8. insert into语句到同一张表里，MySQL会创建一张临时表来保存中间结果
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.15 sec)
mysql> insert into t1 select * from t1;
Query OK, 11 rows affected (0.27 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.20 sec)
复制- update多表
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.22 sec)
mysql> update t1 a , t2 b set a.id = b.id where b.name like '%w%';
Query OK, 22 rows affected (0.31 sec)
Rows matched: 22 Changed: 22 Warnings: 0
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.23 sec)
复制- group_concat和count(distinct)语句
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.14 sec)
mysql> select group_concat(name) name from t1;
+-----------------------------------------------------------------------------------------+
| name |
+-----------------------------------------------------------------------------------------+
| aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa,aaa |
+-----------------------------------------------------------------------------------------+
1 row in set (0.20 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.19 sec)
---
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.26 sec)
mysql> select count(distinct name) from t1;
+----------------------+
| count(distinct name) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.22 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.23 sec)
复制有很多时候，临时表会在explain中会有体现，但是还有些个别的情况下，explain并不会显示使用临时表，这种情况一般为derived table 或 materialized算法产生的临时表。
硬盘临时表
相较于产生的内存临时表而言，硬盘临时表会耗费非常多的资源和性能去创建，所以，尽可能地避免硬盘临时表，是优化的一大思路。
那么，MySQL会如何产生硬盘临时表呢，具体如下：
- 表中有BLOB和TEXT的列(name列为text类型)。
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.01 sec)
mysql> explain select name from t2 union select name from t1;
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| 1 | PRIMARY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 268693 | 100.00 | NULL |
| 2 | UNION | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 273720 | 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.09 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
复制- UNION或UNION ALL语句中有长度超过512字节的列
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.01 sec)
mysql> explain select last_date_time from t1 union select last_date_time from t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 273720 | 100.00 | NULL |
| 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 268693 | 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.00 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
复制- desc或show columns语句
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.34 sec)
mysql> desc t2;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | text | YES | | NULL | |
| last_date_time | varchar(700) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.35 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.23 sec)
复制写在最后
硬盘临时表的代价会比内存临时表高出数倍，所以，在写SQL一定要注意这点，尽量避免硬盘临时表的产生。
最后修改时间：2020-09-01 13:36:09
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
