MySQL临时表（内部和外部）

我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时，可以把中间结果或小的子集放到一个临时表里，再对这些表进行查询，以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。

一般情况下，临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。

外部临时表

外部临时表也可称为会话临时表，这种临时表只对当前用户可见，它的数据和表结构都存储在内存中。当前会话中断或结束后，数据表数据就会丢失，MySQL 会自动删除表并释放其所占空间。

1）创建临时表

创建临时表很容易，在 CREATE TABLE 语句上添加 TEMPORARY 关键字，如下所示：

CREATE TEMPORARY TABLE <表名>…

临时表的命名可以和非临时表同名，但是同名后非临时表将对当前会话不可见，直到临时表被删除。

2）查询临时表

创建了临时表之后，运行 SHOW TABLES 命令不会列出临时表，以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息，这不是 Bug，而是设计就是如此。

我们可以使用以下命令来查看临时表：

SHOW CREATE TABLE <表名>; 

3）删除临时表

当然，我们也可以在当前会话中手动销毁临时表，SQL 语句如下：

DROP TABLE <表名>;

例 1

下面我们创建 tmp_table 表，并对它进行操作。

mysql> CREATE TEMPORARY TABLE tmp_table (
    -> id INT NOT NULL,
    -> name VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_test    |
+-------------------+
| product           |
| product_price     |
| score             |
| student           |
| student_comment   |
| tb_student        |
| tb_student_course |
| tb_students_info  |
| tb_students_score |
| tb_usertest       |
+-------------------+
10 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE tmp_table /G
*************************** 1. row ***************************
       Table: tmp_table
Create Table: CREATE TEMPORARY TABLE `tmp_table` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> DROP TABLE tmp_table;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tmp_table /G
ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist

如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话，再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表，会发现数据库中没有该表的存在，因为在退出会话时该临时表已经被销毁了。

外部临时表也有一些限制，使用时需注意以下几点：

• 所用数据库账号需要有建立和使用临时表的权限
• 在同一条 SQL 语句中，不能关联2次相同的临时表
• 不能用 RENAME 来重命名一个临时表，可以用 ALTER TABLE 来代替。

内部临时表

内部临时表是一种特殊轻量级的临时表，不同于手工创建的临时表，它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果，该过程由 MySQL 自动完成，用户无法手工干预，且这种内部表对用户来说是不可见的。

我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。

内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色，MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。

以下是生成内部临时表的几种可能：

• 使用 ORDER BY/GROUP BY 的列并非全来自于表连接的第一个表
• Distinct 和 ORDER BY 联合使用
• 多表连接需要保存中间结果集

内部临时表有两种类型，一种是 HEAP 临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要 IO 操作；另一种是 OnDisk 临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。

以下情况可能阻碍 MySQL 使用 HEAP 临时表，改用 OnDisk 临时表：

• 数据表中含有 BLOB 类型或 TEXT 类型字段列；
• 在 GROUP BY 或 DISTINCT 任何条件中含有超过 512 字节的列；
• 如果使用了 UNION 或 UNION ALL，而且 SELECT 列中含有任何超过 512 字节的列；
• 如果 HEAP 临时表的大小大于 MAX_HEAP_TABLE_SIZE 系统变量的值，HEAP 临时表将会被自动转换成 OnDisk 临时表。

MySQL 5.7 版本中，OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。
Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。