什么情況下會用到臨時表 · 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)