mysql 临时表

1.情景展示

在实际开发过程中，当现有手段无法满足我们想要的数据时，我们就可以通过创建临时表，保存一些临时数据的方式，来用作数据的过渡。

2.具体分析

临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间；

使用其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表（当然也可以手动删除）。

在mysql中，临时表可分为：外表临时表和内部临时表

3.外部临时表（推荐使用）

通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。

这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。

引擎类型：memory（heap）、myisam、merge、innodb ，不支持mysql cluster(簇)；

注意：

自己所用的数据库账号要有建立临时表的权限；

在同一条sql中，不能关联2次相同的临时表；

临时表在建立连接时可见（准确地来说是可用），关闭时会清除空间，删除临时表（也可以通过DROP TABLE手动删表）；

show tables 不会列出临时表（对外不可见）；

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

影响使用replication功能。

简单来说，就是：临时表的用法和普通表一样。

举例：

通常情况下，我喜欢在存储过程当中，使用临时表。

4.内部临时表（尽量规避）

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。

这种临时表会被MySQL自动创建并用来存储某些操作的中间结果，这些操作可能包括在优化阶段或者执行阶段。

这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。

内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。

但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

（如果用户在书写SQL语句的时候能够尽量少的使用内部临时表进行查询优化，将有效的提高查询执行的效率。）

内部临时表有两种类型：

一种是HEAP临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作；

另一种是OnDisk临时表，顾名思义，这种临时表会将数据存储在磁盘上。

OnDisk临时表用来处理中间结果比较大的操作：如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE（详情请参考MySQL手册中系统变量部分），HEAP临时表将会被自动转换成OnDisk临时表。

OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。

如何知道SQL查询语句到底有没有使用内部临时表？

需要通过expalin来完成，具体表现形式有两种：见文末推荐。

mysql会使用内部临时表的情况汇总：

情形1：在SQL语句中使用SQL_BUFFER_RESULT

SQL_BUFFER_RESULT主要用来让MySQL尽早的释放表上的锁。

因为如果数据量很大的话，需要较长时间将数据发送到客户端，通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。

情形2：使用派生表（DERIVED_TABLE）

类似这种，但是，这个没有用内部临时表，等有合适案例了，再补上。

可通过SELECT @@optimizer_switch;语句来查看derived_merge当前的状态（on/off）;

可使用：set optimizer_switch='derived_merge=off'来禁止derived table合并到外层的Query中。

mysql 5.1中开始引入optimizer_switch, 控制mysql优化器行为。他有一些结果集，通过on和off控制开启和关闭优化器行为。使用有效期全局和会话两个级别；

在mysql优化语句过程中，可通过设置optimizer_switch控制优化行为。

情形3：查询系统表

如果我们查询系统表的话，系统表的数据将被存储到内部临时表中。

当我们使用EXPLAIN来查看是否读取系统表数据需要利用到内部临时表时，得到的结果为：否；

但是，这并不意味着查询系统表没有用到内部临时表；

查询系统表前，临时表数量为10；查询后，临时表数量为18。

情形4：使用distinct或者group by且字段没有设置索引

town_village_mapping表没有任何索引

我们可以看到，无论是：group by 还是distinct，都是用了内部临时表；

town_village_mapping_old表的county_code字段有普通索引

我们可以看到：此时走的就是索引，并且没有使用内部临时表。

说明：这里，没有考虑索引失效的情况，具体问题具体分析。

情形5：关联查询+order by

MySQL如何执行关联查询？

MySQL对任何关联都执行嵌套循环（Block Nested Loop）关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到一个表中寻找匹配的行，依次下去直到找到的有匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。

我们可以看到：两表关联，在没有排序的情况下，是不会使用内部临时表的；

但是，当我们加上排序字段后，将会使用内部临时表。

在关联查询的时候如果需要排序（有ORDER BY子句），MySQL都会将关联的结果存放在一个临时表中，然后在所有的关联都结束后，再进行文件排序。

这种情况下Extra字段可以看到Using temporary;Using filesort；

如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。

MySQL的执行计划是一棵左侧深度优先的树。

不过，如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”。实际上，当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式。

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果需要排序的数据量小于排序缓冲区，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序的块进行合并，最后返回排序结果。

情形6：表连接+group by

没加group by之前

加group by之后

情形6：group by 与 order by 不同列

我们可以看到，当group by与order by使用相同列时，不会使用内部临时表；

但是，当group by与order by使用的列名不同时，就会触发内部临时表。

情形7：distinct 与 order by 不同列

由情形4，我们知道：

当distinct后面的列使用索引的话，将不会使用内部临时表；

我们可以再次看到，当distinct走索引，且排序字段与distinct相同时，mysql是不会用内部临时表的；

但是，当order by后面的字段与distinct 字段不相同时，将会使用内部临时表。

情形8：使用union去重

先看union all（不去重）

不去重的情况下，不会使用内部临时表；

但是，如果去重的话，将会使用内部临时表。

.....(未完待续)

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

作者：Marydon​