MySQL 臨時表是 Session 級別的私有資料表,自動清理、不佔用永久空間,是複雜查詢中間結果暫存、大表分步驟處理的利器。本文系統整理建立語法、生命週期、引擎選擇與效能權衡。
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INT NOT NULL
);
| 特性 | 說明 |
|---|---|
| 可見範圍 | 僅建立該表的 當前 Session (連線) 可見 |
| 命名衝突 | 同名永久表被遮蔽 (隱藏),Session 內操作臨時表;其他 Session 不受影響 |
| 自動清理 | Session 斷開 (正常/異常) 時,**自動 DROP 並釋放空間** |
| 手動清理 | DROP TEMPORARY TABLE tmp_table (建議显式清理) |
| 權限需求 | 需 CREATE TEMPORARY TABLES 權限 |
| 複寫行為 | 預設不複寫至 Slave (ROW 格式除外) |
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INT NOT NULL
) ENGINE = MEMORY; -- 5.5+ 用 ENGINE=MEMORY,舊版 TYPE=HEAP
| 特性 | MEMORY (HEAP) | 磁碟引擎 |
|---|---|---|
| 儲存位置 | 記憶體 (RAM) | 磁碟 |
| 讀寫速度 | 極快 (微秒級) | 毫秒級 |
| 最大大小 | 受限 max_heap_table_size (預設 16MB) | 受限磁碟空間 |
| 支援資料型別 | 固定長度 (無 BLOB/TEXT/VARCHAR 超過限制) | 全支援 |
| 索引類型 | HASH (預設) / BTREE | BTREE |
| 重啟後資料 | 遺失 | 保留 |
SET GLOBAL max_heap_table_size = 67108864; -- 64MB
SET SESSION max_heap_table_size = 67108864; -- 當前 Session
-- 實際上限取 min(max_heap_table_size, tmp_table_size)
官方文件建議:**實測比較** 臨時表 vs 直接查詢大表:
-- 1. 從億級訂單表抽取近 30 天已完成訂單到臨時表
CREATE TEMPORARY TABLE tmp_recent_orders ENGINE=MEMORY
SELECT order_id, user_id, amount, created_at
FROM orders
WHERE status = 'completed'
AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND id > 0; -- 強制使用索引
-- 2. 建立索引加速後續多次 JOIN
CREATE INDEX idx_user ON tmp_recent_orders (user_id);
-- 3. 多個報表查詢複用臨時表
SELECT u.name, COUNT(*) AS order_count, SUM(t.amount) AS total
FROM tmp_recent_orders t
JOIN users u ON t.user_id = u.id
GROUP BY u.id;
SELECT DATE(created_at) AS dt, SUM(amount)
FROM tmp_recent_orders
GROUP BY DATE(created_at);
-- 4. 結束 Session 自動清理,或显式
DROP TEMPORARY TABLE tmp_recent_orders;
DROP TEMPORARY TABLEmax_heap_table_size 自動轉為 MyISAM,效能驟降。監控 Created_tmp_disk_tables 狀態變數MySQL 臨時表是 **Session 隔離、自動清理** 的輕量級中間結果容器。
✅ 善用 ENGINE=MEMORY 加速小熱資料集
✅ 显式 DROP TEMPORARY TABLE 避免連線池殘留
✅ 實測驗證優於直接查詢大表再決定使用
✅ 注意複寫環境下的行為差異