MySQL 中的兩種臨時表

外部臨時表

通過CREATE TEMPORARY TABLE 建立的臨時表，這種臨時表稱為外部臨時表。這種臨時表只對當前使用者可見，當前會話結束的時候，該臨時表會自動關閉。這種臨時表的命名與非臨時表可以同名（同名後非臨時表將對當前會話不可見，直到臨時表被刪除）。

內部臨時表

內部臨時表是一種特殊輕量級的臨時表，用來進行效能優化。這種臨時表會被MySQL自動建立並用來儲存某些操作的中間結果。這些操作可能包括在優化階段或者執行階段。這種內部表對使用者來說是不可見的，但是通過EXPLAIN或者SHOW STATUS可以檢視MYSQL是否使用了內部臨時表用來幫助完成某個操作。內部臨時表在SQL語句的優化過程中扮演著非常重要的角色， MySQL中的很多操作都要依賴於內部臨時表來進行優化。但是使用內部臨時表需要建立表以及中間資料的存取代價，所以使用者在寫SQL語句的時候應該儘量的去避免使用臨時表。

內部臨時表有兩種型別：一種是HEAP臨時表，這種臨時表的所有資料都會存在記憶體中，對於這種表的操作不需要IO操作。另一種是OnDisk臨時表，顧名思義，這種臨時表會將資料儲存在磁碟上。OnDisk臨時表用來處理中間結果比較大的操作。如果HEAP臨時表儲存的資料大於MAX_HEAP_TABLE_SIZE（詳情請參考MySQL手冊中系統變數部分），HEAP臨時表將會被自動轉換成OnDisk臨時表。OnDisk臨時表在5.7中可以通過INTERNAL_TMP_DISK_STORAGE_ENGINE系統變數選擇使用MyISAM引擎或者InnoDB引擎。

本篇文章主要介紹哪些操作可能會利用到內部臨時表。如果使用者在書寫SQL語句的時候能夠儘量少的使用內部臨時表進行查詢優化，將有效的提高查詢執行的效率。

首先我們定義一個表t1，
CREATE TABLE t1( a int, b int); INSERT INTO t1 VALUES(1,2),(3,4);

下面所有的操作都是基於表t1進行舉例的。

• 在SQL語句中使用SQL_BUFFER_RESULT hint

SQL_BUFFER_RESULT主要用來讓MySQL儘早的釋放表上的鎖。因為如果資料量很大的話，需要較長時間將資料傳送到客戶端，通過將資料緩衝到臨時表中可以有效的減少讀鎖對錶的佔用時間。
例如：

• 如果SQL語句中包含了DERIVED_TABLE。

在5.7中，由於採用了新的優化方式，我們需要使用 set optimizer_switch=’derived_merge=off’來禁止derived table合併到外層的Query中。
例如：

• 如果我們查詢系統表的話，系統表的資料將被儲存到內部臨時表中。

我們當前不能使用EXPLAIN來檢視是否讀取系統表資料需要利用到內部臨時表，但是可以通過SHOW STATUS來檢視是否利用到了內部臨時表。
例如：

• 如果DISTINCT語句沒有被優化掉，即DISTINCT語句被優化轉換為GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT, 內部臨時表將會被使用。

• 如果查詢帶有ORDER BY語句，並且不能被優化掉。下面幾種情況會利用到內部臨時表快取中間資料，然後對中間資料進行排序。

1）如果連線表使用BNL（Batched Nestloop）/BKA(Batched Key Access)
例如：

1))BNL預設是開啟的

2))關掉BNL後，ORDER BY將直接使用filesort。

2）ORDER BY的列不屬於執行計劃中第一個連線表的列。
例如：

3）如果ORDER BY的表示式是個複雜表示式。

那麼什麼樣的ORDER BY表示式，MySQL認為是複雜表示式呢？

1))如果排序表示式是SP或者UDF。
例如：

2))ORDER BY的列包含聚集函式

為了簡化執行計劃，我們利用INDEX來優化GROUP BY語句。
例如：

3))ORDER BY的列中包含有SCALAR SUBQUERY,當然該SCALAR SUBQUERY沒有被優化掉。
例如：

4) 如果查詢既帶有ORDER BY同時也有GROUP BY語句，但是兩個語句使用的列不相同。

注意: 如果是5.7，我們需要將sql_mode設定為非only_full_group_by模式，否則會報錯。

同樣為了簡化執行計劃，我們利用INDEX來優化GROUP BY語句。
例如：

• 如果查詢帶有GROUP BY語句，並且不能被優化掉。下面幾種情況會利用到內部臨時表快取中間資料，然後對中間資料進行GROUP BY。

1）如果連線表使用BNL（Batched Nestloop）/BKA(Batched Key Access)。
例如：

2) 如果GROUP BY的列不屬於執行計劃中的第一個連線表。
例如：

3) 如果GROUP BY語句使用的列與ORDER BY語句使用的列不同。
例如：

4) 如果GROUP BY帶有ROLLUP並且是基於多表外連線。
例如：

5) 如果GROUP BY語句使用的列來自於SCALAR SUBQUERY，並且沒有被優化掉。
例如：

• IN表示式轉換為semi-join進行優化
  1) 如果semi-join執行方式為Materialization
  例如：

2) 如果semi-join執行方式為Duplicate Weedout
例如：

• 如果查詢語句帶有UNION，MySQL將利用內部臨時表幫助UNION操作消除重複。
  例如：

• 如果查詢語句使用多表更新。
  這裡Explain不能看到內部臨時表被利用，所以需要檢視status。
  例如：

• 如果聚集函式中包含如下函式，內部臨時表也會被利用。

總之，上面列出了10種情況，MySQL將利用內部臨時表進行中間結果快取，如果資料量比較大的話，內部臨時表將會把資料儲存在磁碟上，這樣顯然會對效能有所影響。為了儘可能的減少效能損失，我們需要儘量避免上述情況的出現。