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

MySQL 臨時表完整指南：建立、記憶體引擎、生命週期與效能權衡

編輯：MySQL 資料庫相關知識

前言：何時需要臨時表？

面對超大表（億級行）的複雜多次查詢，反覆全表掃描極其昂貴。臨時表允許將小量結果集物質化到記憶體或磁碟，後續查詢僅掃描這個微型表，可獲數量級效能提升。

一、基本建立語法

-- 磁碟臨時表（預設 MyISAM/InnoDB）
CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INT NOT NULL
);

-- 記憶體臨時表（HEAP/MEMORY 引擎）
CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INT NOT NULL
) ENGINE = MEMORY;  -- MySQL 5.0+ 建議用 MEMORY，HEAP 為別名

二、生命週期與可見性

連線專屬： 僅對建立該表的當前連線可見，其他連線完全不可見（甚至可同名共存）。
自動清理： 連線斷開時，MySQL 自動 DROP 臨時表並釋放空間。
手動釋放： 連線存活期間可隨時 DROP TEMPORARY TABLE tmp_table; 釋放資源。
遮蔽同名永久表： 若永久表同名存在，臨時表優先可見，永久表被隱藏至連線結束。

三、MEMORY (HEAP) 引擎特性與限制

特性	說明
儲存位置	純記憶體，讀寫極快（微秒級）
索引類型	預設 HASH 索引（=、<=> 最優），可指定 BTREE（範圍查詢）
資料型別限制	不支援 BLOB/TEXT，VARCHAR 實際按 CHAR 固定長度儲存
大小限制	受 `max_heap_table_size`（預設 16MB）與 `tmp_table_size` 共同限制，超限自動轉 MyISAM 磁碟表
並發可見性	僅當前連線可見，不支援事務隔離級別
持久性	重啟/斷電資料全失，不適合關鍵業務資料

四、效能權衡：臨時表 vs 直接查詢

臨時表較優場景：

同一結果集需多次複雜 JOIN/聚合/排序。
大表篩選後結果集極小（< 1%），物質化成本遠低於重複掃描大表。
中間結果需跨多個存儲程序/觸發器共享（同連線）。

直接查詢較優場景：

大表有高選擇性索引，單次查詢即高效。
結果集較大，物質化記憶體壓力大、甚至落磁碟反而更慢。
查詢一次性，無重複利用價值。

五、實戰範例：多表 JOIN 優化

-- 原查詢：三表關聯，重複掃描 tablename2/3
SELECT t1.a, t2.a, t3.a
FROM t1, t2, t3
WHERE t1.a = t2.a AND t2.a = t3.a AND t2.c != 0;

-- 優化：先將 t2/t3 關聯結果物質化到記憶體臨時表
CREATE TEMPORARY TABLE tmp_t23 ENGINE=MEMORY
SELECT t2.a AS a2, t3.a AS a3
FROM t2, t3
WHERE t2.a = t3.a AND t2.c = 0;

-- 後續查詢僅掃描微型臨時表
SELECT t1.a, tmp.a3
FROM t1, tmp_t23 tmp
WHERE t1.a = tmp.a2;

-- 同一連線下可重複利用 tmp_t23 執行多種變體查詢