您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 臨時表實戰指南：建立、記憶體引擎、生命週期與效能優化建議

編輯：MySQL 資料庫相關知識

處理超大表複雜查詢時，將中間結果集物化到臨時表可大幅降低重複掃描成本。本文說明 CREATE TEMPORARY TABLE 語法、HEAP/MEMORY 引擎加速、生命週期管理與效能評估要點。

基本建立語法

-- 磁碟臨時表 (預設 MyISAM/InnoDB)
CREATE TEMPORARY TABLE tmp_stats (
  id INT PRIMARY KEY,
  cnt BIGINT
) ENGINE=InnoDB;

-- 記憶體臨時表 (極速，重啟消失，資料量受限於 tmp_table_size/max_heap_table_size)
CREATE TEMPORARY TABLE tmp_fast (
  id INT PRIMARY KEY,
  val VARCHAR(20)
) ENGINE=MEMORY;

關鍵特性

特性	說明
可見性	僅當前 Session 可見，其他連線不可存取
生命週期	Session 斷開自動刪除，或手動 `DROP TEMPORARY TABLE`
同名遮蔽	若永久表同名，臨時表優先被引用
權限	需 `CREATE TEMPORARY TABLES` 權限

效能優化建議

評估資料量：結果集 < 10 萬列且欄位定長 → ENGINE=MEMORY；超過或含 BLOB/TEXT → 磁碟表。
索引策略：臨時表同樣受益於索引，依查詢模式加 PRIMARY KEY 或 INDEX。
避免過度使用：若原表已有適當索引，直接查詢可能更快；臨時表建立與填充本身有開銷。
參數調校：tmp_table_size、max_heap_table_size 設為 64M~256M，超過自動轉磁碟。

實戰範例：分頁優化

-- 原查詢：深分頁 OFFSET 100000 LIMIT 20 極慢
-- 優化：先取 ID 存入臨時表，再 JOIN 回主表
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO tmp_ids SELECT id FROM huge_table WHERE cond ORDER BY id LIMIT 100020;
-- 取最後 20 筆 ID
SELECT h.* FROM huge_table h
JOIN (SELECT id FROM tmp_ids ORDER BY id DESC LIMIT 20) t USING (id);
DROP TEMPORARY TABLE tmp_ids;

臨時表是「以空間換時間」的戰術工具，善用可化解疑難慢查，濫用則適得其反。