MySQL在以下几种情况会创建临时表

jopen 9年前

 

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开始,新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB,而在这以前,只能使用 MyISAM。而在5.6.3以后新增的系统选项  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引擎的临时表