MySQL 內部臨時表機制詳解（重構版）：觸發條件、記憶體磁碟切換、5.7 新參數與優化策略

MySQL 內部臨時表完整機制與優化指南（深度重構版）

一、官方定義

MySQL 在查詢處理中自動建立的內部臨時表，優先用 MEMORY（記憶體），超限或不支援時轉 MyISAM（5.7.5+ 可選 InnoDB）。使用者無法直接控制。

二、必定觸發內部臨時表的 SQL 模式

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

三、強制落磁碟（MyISAM/InnoDB）的條件

• 含 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 關鍵新參數（切勿混淆）

六、優化實戰建議

1. 索引消除臨時表：ORDER BY/GROUP BY 欄位加索引
2. 大欄位拆表：TEXT/BLOB 移至擴展表，主表只留常用欄位
3. 適度調大記憶體上限：監控 Created_tmp_disk_tables / Created_tmp_tables 比率
4. 5.7+ 推薦：internal_tmp_disk_storage_engine=InnoDB 獲得事務保護
5. 抑制隱含排序：ORDER BY NULL 避免不必要的排序臨時表
6. 避免 SELECT *：只查需要欄位，減少臨時表行大小

七、驗證 default_tmp_storage_engine 行為

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

八、監控指標

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables: 總計
-- Created_tmp_disk_tables: 落磁碟數
-- 比率 > 10% 需優化