MySQL 內部臨時表完整解析：建立時機、記憶體與磁碟切換、優化策略

MySQL 內部臨時表深度解析：觸發條件、儲存引擎選擇與優化指南

一、什麼是內部臨時表

MySQL 在查詢處理過程中，為了完成某些操作（排序、分組、去重等），會自動建立內部臨時表。使用者無法直接控制其建立時機與引擎選擇。

• 優先使用 MEMORY 引擎（記憶體）
• 超過大小限制或包含不支援欄位時轉為 MyISAM（5.7.5+ 可用 InnoDB）

二、必定建立臨時表的場景

1. UNION 查詢
2. 使用 TEMPTABLE 演算法的視圖 / UNION 中的視圖
3. JOIN 中 ORDER BY 或 GROUP BY 使用非驅動表欄位
4. DISTINCT + ORDER BY
5. 查詢含 SQL_SMALL_RESULT 提示
6. FROM 子查詢（衍生表）
7. 半連結（semi-join）優化產生的表
8. 多表 UPDATE
9. GROUP_CONCAT()、COUNT(DISTINCT ...) 評估

三、強制使用磁碟臨時表的條件

• 表包含 TEXT / BLOB 欄位
• GROUP BY / DISTINCT 欄位長度 > 512 字元（或 512 位元組二進位）
• SELECT/UNION 輸出欄位最大長度 > 512
• SHOW COLUMNS、DESCRIBE 等輸出含 BLOB

四、關鍵配置參數

實際記憶體臨時表上限 = MIN(tmp_table_size, max_heap_table_size)

五、5.6/5.7 新增參數

• default_tmp_storage_engine（5.6.3+）：CREATE TEMPORARY TABLE 使用者建立的臨時表引擎，預設 MEMORY
• internal_tmp_disk_storage_engine（5.7.5+）：內部磁碟臨時表引擎，可選 InnoDB/MyISAM，預設 MyISAM

切勿混淆兩者：前者控制使用者建立的臨時表，後者控制伺服器內部產生的磁碟臨時表。

六、優化建議

1. 在 ORDER BY / GROUP BY 欄位建立索引，避免 filesort 與臨時表
2. 大欄位（TEXT/BLOB）拆分到獨立表，主表僅保留常用欄位
3. 適度調大 tmp_table_size / max_heap_table_size，但避免 OOM
4. 5.7+ 設定 internal_tmp_disk_storage_engine=InnoDB 獲得事務保護與更好並發
5. 避免不必要的 DISTINCT、ORDER BY NULL 抑制隱含排序

七、實測驗證

SET default_tmp_storage_engine = MyISAM;
CREATE TEMPORARY TABLE t1 (id INT);
SHOW CREATE TABLE t1\G  -- Engine=MyISAM

SET default_tmp_storage_engine = MEMORY;
CREATE TEMPORARY TABLE t2 (id INT);
SHOW CREATE TABLE t2\G  -- Engine=MEMORY