什么情况下会用到临时表 · MySQL FAQ系列整理

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](http://imysql.com/2015/06/14/mysql-faq-what-important-information-in-explain.shtml) 就表示会用到临时表。当然了，如果临时表中需要存储的数据量超过了上限（ [tmp-table-size](https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tmp_table_size) 或[max-heap-table-size](https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_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开始，新增一个系统选项[internal_tmp_disk_storage_engine](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_internal_tmp_disk_storage_engine) 可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。而在5.6.3以后新增的系统选项 [default_tmp_storage_engine](https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_default_tmp_storage_engine) 是控制 CREATE TEMPORARY TABLE创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了。见下例： ~~~ mysql> set default_tmp_storage_engine = "InnoDB"; -rw-rw---- 1 mysql mysql 8558 Jul 7 15:22 #sql4b0e_10_0.frm -- InnoDB引擎的临时表 -rw-rw---- 1 mysql mysql 98304 Jul 7 15:22 #sql4b0e_10_0.ibd -rw-rw---- 1 mysql mysql 8558 Jul 7 15:25 #sql4b0e_10_2.frm mysql> set default_tmp_storage_engine = "MyISAM"; -rw-rw---- 1 mysql mysql 0 Jul 7 15:25 #sql4b0e_10_2.MYD -- MyISAM引擎的临时表 -rw-rw---- 1 mysql mysql 1024 Jul 7 15:25 #sql4b0e_10_2.MYI mysql> set default_tmp_storage_engine = "MEMORY"; -rw-rw---- 1 mysql mysql 8558 Jul 7 15:26 #sql4b0e_10_3.frm -- MEMORY引擎的临时表 ~~~ 延伸阅读： [[MySQL优化案例]系列 — 频繁创建临时表](http://imysql.com/2009_02_17_mysql_optimize_to_many_temp_table) [无需过分关注Created_tmp_disk_tables](http://www.imysql.com/2009/07/01/donot_worry_about_tmp_table_on_disk) [8.4.4 How MySQL Uses Internal Temporary Tables](https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html)