MySQL 临时表

MySQL提供临时表功能，在保存一些临时数据是非常有用的。临时表只在当前连接可见，当连接关闭时，MySQL会自动删除表并释放空间。当然也可以手动删除临时表

MySQL数据库的临时表分两种：内部临时表和外部临时表。

外部临时表

外部临时表指的是通过 CREATE TEMPORARY TABLE 语法显示创建的临时表。这种临时表只对当前会话可见，其他会话是看不到这个临时表的。当前会话关闭时，临时表会自动删除。外部临时表可以与非临时表同名，当创建的临时表与非临时表同名时，非临时表对当前会话不可见，知道临时表被删除。

内部临时表

内部临时表是一种特殊轻量级的临时表，主要用来性能优化。这种临时表是由MySQL自动创建并管理，用来存储并操作某些中间结果，这种操作可能包括在优化阶段和执行阶段。这种内部临时表对用户是不可见的，但是可以通过执行计划EXPLAIN 或 SHOW STATUS可以查看MySQL是否使用内部临时表来帮助完成某个操作。MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

内部临时表有两种类型：一种是HEAP临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。另一种是OnDisk临时表，这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE，HEAP临时表将会被自动转换成OnDisk临时表。OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。

外部临时表用法

外部临时表是通过CREATE TEMPORARY TABLE及DROP TABLE来操作的，但是SHOW TABLES命令显示数据表列表时，你将无法看到自己创建的临时表的。并且在退出当前会话后，临时表就会被自动删除。也可以手动（DROP TABLE）删除。

外部临时表使用需要注意的几点：

1、所用的数据库账号要有建立临时表的权限

2、在同一条sql中，不能多次关联同一张临时表。否则会报错：

mysql> select * from temp_t, temp_t as t;
ERROR 1137 (HY000): Can't reopen table: 'temp_t'

3、临时表在建立连接时可见，关闭时会清除空间，删除临时表

4、show tables 不会列出临时表

5、不能使用rename重命名临时表。但是，你可以alter table代替：只能使用alter table old_tp_table_name rename new_tp_table_name;

6、影响使用replication功能

7、如果你为一个表声明了别名，当你指向这个表的时候，就必须使用这个别名

内部临时表用法

 在书写SQL语句的时候能够尽量少的使用内部临时表进行查询优化，将有效的提高查询执行的效率。以下几种情况，MySQL可能会用到内部临时表：

1、UNION查询

2、用到TEMPTABLE算法或者是UNION查询中的视图；

3、ORDER BY和GROUP BY的子句不一样时；

4、表连接中，ORDER BY的列不是驱动表中的；

5、DISTINCT查询并且加上ORDER BY时；

6、SQL中用到SQL_SMALL_RESULT选项时；

7、FROM中的子查询；

8、子查询或者semi-join时创建的表；

EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using temporary 就表示会用到临时表。

当然了，如果临时表中需要存储的数据量超过了上限（ tmp-table-size或 max-heap-table-size中取其大者），这时候就需要生成基于磁盘的临时表了。

在以下几种情况下，会创建磁盘临时表：

1、数据表中包含BLOB/TEXT列；

2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）；

3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；、

4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。

从5.7.5开始，新增一个系统选项 internao_tmp_disk_storage_engine可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了。