MySQL临时表

MySQL在执行SQL查询时可能会用到临时表，一般情况下，用到临时表就意味着性能较低。

1.临时表

MySQL临时表分为“内存临时表”和“磁盘临时表”，并且任何临时表都是没有索引的。

其中内存临时表默认使用MySQL的MEMORY存储引擎，default_tmp_storage_engine 可以控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，默认是MEMORY。

磁盘临时表使用MySQL的MyISAM存储引擎，internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB。

一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表从内存导出到磁盘临时表。

从磁盘临时表与内存临时表的差异中大家可以看到，磁盘临时表只是内存临时的一个替代品。这就好像操作系的虚拟内存一样。当内存不够用时，可以在硬盘上的一个空间作为其替代品，将内存中的部分数据转移到虚拟内存中。这个磁盘临时表也是相同的道理。

可以通过 SHOW VARIABLES LIKE ‘tmpdir’; 来查看磁盘临时表的储存位置。

但是值得注意的是，硬盘的效率与内存的效率是不同的。在执行相同的一个作业时，内存的性能要高于硬盘的性能，一般会高上百倍，甚至上千倍。从这里就可以看出，为了提高数据库系统的性能，最好选择内存临时表，而放弃使用磁盘临时表。

2.什么时候会用到临时表

1、GROUP BY xx，并且xx不能利用索引排序时。
2、在关联查询中，ORDER BY或者GROUP BY使用了不是第一个表（驱动表）的列，例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name。此时，根据嵌套循环查询的特点可以知道，无论是否有索引，最后得到的结果中第二张表中的字段都是乱序的，因此MySQL只能先将关联的结果存放在临时表中，然后在所有的关联都结束后，再进行排序或分组。此时会在 EXPLAIN的结果中看到“Using temporary; Using filesort”。而如果所有的 ORDER BY 和 GROUP BY都在第一张表时，MySQL在关联处理第一张表时就能进行文件排序了，因此此时能够利用索引（如果有的话）省去文件排序和临时表了。
3、ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
4、SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序。SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用。一般情况下，没有必要使用这个选项，让MySQL服务器选择即可。
5、某些子查询会将子查询的结果放入临时表中，然后继续之后的查询。比如 WHERE 子句里的子查询。
6、UNION。

3.直接使用磁盘临时表的场景

1、表包含TEXT或者BLOB列；
2、GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
3、使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；

4.临时表相关配置

tmp_table_size：指定系统创建的内存临时表最大大小；
max_heap_table_size: 指定用户创建的内存表的最大大小；

注意：最终的系统创建的内存临时表大小是取上述两个配置值的最小值。当内存临时表大小超过配置项后，就会导出到磁盘。

5.表的设计原则

使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有：
1、创建索引：在ORDER BY或者GROUP BY的列上创建索引，并保证能利用到索引；
2、分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。

6.如何判断使用了临时表？

使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表。