mysql mma 原理_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-joinmysql> 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 | Ex