MySQL臨時表機制詳解：創建、類型選擇、生命周期與性能權衡

核心價值：大表子集高頻查詢加速器

當需對超大表 (億級行) 反覆查詢小數據子集 (如近 7 天活躍用戶) 時，將子集提取至臨時表，後續查詢僅掃描臨時表，**避免重複全表掃描、索引深度遍歷、鎖競爭**。

創建語法與類型選擇

磁盤臨時表 (默認 MyISAM/InnoDB)

CREATE TEMPORARY TABLE tmp_active_users (
    user_id BIGINT PRIMARY KEY,
    last_login DATETIME,
    login_count INT
) ENGINE=InnoDB;  -- 顯式指定引擎，支持事務/外鍵

• 持久化磁盤，數據量無內存限制
• 支持索引、外鍵、事務 (InnoDB)
• 斷開連接自動 DROP，釋放空間

內存臨時表 (HEAP/MEMORY)

CREATE TEMPORARY TABLE tmp_lookup (
    code CHAR(10) PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;  -- 或 ENGINE=HEAP (別名)

• 全內存存儲，**極速讀寫** (無 I/O)
• 固定長度行格式 (VARCHAR 實際按 CHAR 存儲)
• 不支持 BLOB/TEXT、AUTO_INCREMENT、可變長列
• 重啟/斷開連接**數據全丟失**，僅適合緩存/查找表

命名衝突與可見性規則

• 同名永久表被**隱藏** (遮蔽)，臨時表優先可見
• 同一會話不可創建同名臨時表 (報錯 Table already exists)
• 不同會話可創建同名臨時表，**完全隔離**，互不干擾

生命周期管理

-- 顯式刪除 (推薦及時釋放)
DROP TEMPORARY TABLE IF EXISTS tmp_active_users;

-- 會話結束自動刪除 (正常斷開/異常斷開/KILL 線程)
-- 事務提交/回滾**不影響**臨時表生命周期

性能權衡決策矩陣

優化建議與陷阱

1. 先測試再決定：有時優化器配合良好索引，直接查大表比建臨時表+查詢更快
2. 索引臨時表：創建後立即 CREATE INDEX idx_col ON tmp_tbl(col);，後續查詢受益
3. 避免嵌套臨時表：子查詢物化、UNION、派生表已隱式創建內部臨時表，外層再建臨時表疊加開銷
4. 監控落盤比率：Created_tmp_disk_tables / Created_tmp_tables > 10% 內存不足或含大字段，調大 tmp_table_size/max_heap_table_size 或改磁盤表
5. 複製表捷徑：CREATE TEMPORARY TABLE tmp AS SELECT * FROM big_table WHERE condition; 一鍵建表導數據

實戰模板：日報表加速

-- 1. 提取近 7 天訂單子集 (約 50 萬行)
CREATE TEMPORARY TABLE tmp_recent_orders ENGINE=InnoDB AS
SELECT order_id, user_id, amount, create_time
FROM orders
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 2. 建立關鍵索引
CREATE INDEX idx_user_time ON tmp_recent_orders (user_id, create_time);

-- 3. 多維分析查詢 (秒級響應)
SELECT user_id, COUNT(*), SUM(amount) 
FROM tmp_recent_orders 
GROUP BY user_id 
HAVING SUM(amount) > 1000;

-- 4. 會話結束自動清理，無需手動 DROP