MySQL 臨時表實戰：大表查詢優化的「分而治之」利器

面對億級大表的複雜查詢，直接操作往往導致鎖爭用、緩衝區汙染、執行計畫不穩。MySQL 臨時表提供 **Session 隔離、自動清理、可選記憶體引擎** 的中間層，是大數據量分步驟處理的經典優化手段。本文深入實戰場景、引擎選擇與效能驗證方法。

核心適用場景

• 大表 (億級) 需多次不同維度分析 → **一次篩選到臨時表，多次查詢臨時表**
• 複雜多表 JOIN 產生巨大中間結果 → **分步驟落地臨時表，避免單一巨大執行計畫**
• 報表系統：抽取核心指標到臨時表，前端高頻查詢臨時表
• ETL 中間階段暫存：轉換、清洗、驗證後再寫入目標表

建立語法與引擎選擇

標準磁碟臨時表 (預設 MyISAM/InnoDB)

CREATE TEMPORARY TABLE tmp_user_stats (
    user_id INT PRIMARY KEY,
    order_count INT,
    total_amount DECIMAL(12,2),
    last_order_date DATE
) ENGINE=InnoDB; -- 5.7+ 支援 InnoDB 臨時表，支援事務

記憶體臨時表 (MEMORY/HEAP) - 極速場景

CREATE TEMPORARY TABLE tmp_hot_data (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    score INT
) ENGINE=MEMORY;  -- 固定長度行、無 BLOB/TEXT、HASH 索引預設

關鍵限制與參數控制

限制項目	MEMORY 引擎	解決方案
最大表大小	max_heap_table_size (預設 16MB)	調大：SET SESSION max_heap_table_size=268435456 (256MB)
實際上限	min(max_heap_table_size, tmp_table_size)	同步調整兩參數
資料型別	不支援 BLOB/TEXT、VARCHAR 實際存定長	改用磁碟臨時表或壓縮欄位
索引類型	預設 HASH (等值查詢快、範圍查詢慢)	显式指定 USING BTREE
並發可見性	僅當前 Session	需跨 Session 共享改用永久表

實戰範例：億級訂單表多維度報表

-- 1. 核心篩選：從 1 億訂單抽取近 30 天已完成 (預估 200 萬行)
CREATE TEMPORARY TABLE tmp_recent_orders ENGINE=InnoDB
SELECT 
    o.id, o.user_id, o.amount, o.created_at,
    u.vip_level, u.city_id
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND o.id > 0; -- 強制使用主鍵索引

-- 2. 建立加速索引 (臨時表上建索極快)
CREATE INDEX idx_user ON tmp_recent_orders (user_id);
CREATE INDEX idx_city ON tmp_recent_orders (city_id);
CREATE INDEX idx_date ON tmp_recent_orders (created_at);

-- 3. 多個報表查詢複用 (毫秒級響應)
-- 日銷售趨勢
SELECT DATE(created_at) AS dt, COUNT(*), SUM(amount) 
FROM tmp_recent_orders GROUP BY DATE(created_at);

-- 城市銷售排名
SELECT c.name, COUNT(*), SUM(amount)
FROM tmp_recent_orders t
JOIN cities c ON t.city_id = c.id
GROUP BY c.id ORDER BY SUM(amount) DESC LIMIT 20;

-- VIP 用戶貢獻
SELECT vip_level, COUNT(DISTINCT user_id), SUM(amount)
FROM tmp_recent_orders GROUP BY vip_level;

-- 4. Session 結束自動清理，或显式
DROP TEMPORARY TABLE tmp_recent_orders;

效能測試方法論 (文中建議驗證)

-- 對比基準：直接查詢大表
SELECT DATE(created_at), COUNT(*), SUM(amount)
FROM orders
WHERE status='completed' AND created_at>=...
GROUP BY DATE(created_at);

-- 關鍵指標對比
-- 1. 執行時間 (秒)
-- 2. 掃描行數 (Handler_read_key / Handler_read_rnd_next)
-- 3. 臨時表建立時間 + 索引建立時間 + 查詢時間 總和
-- 4. 併發下的鎖等待 (SHOW ENGINE INNODB STATUS)

-- 監控臨時表使用情況
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables: 記憶體臨時表數
-- Created_tmp_disk_tables: 磁碟臨時表數 (超過限制轉磁碟)
-- Created_tmp_files: 臨時檔案數

避坑指南

• ❌ **盲目使用**：小表/有索引查詢直接跑更快，臨時表增加開銷
• ❌ **MEMORY 表存大資料**：超限自動轉磁碟 (MyISAM)，效能崩塌，監控 Created_tmp_disk_tables
• ❌ **連線池不清理**：應用層連線複用導致臨時表殘留佔用記憶體，**邏輯結束必須显式 DROP**
• ❌ **複寫環境**：Statement 格式不複寫臨時表操作，Slave 重啟後不一致 → 用 ROW 格式或永久表
• ❌ **存儲程序忘記清理**：程序結束不自動清理臨時表

進階：MySQL 8.0 CTE (Common Table Expressions) 替代方案

WITH recent_orders AS (
    SELECT o.id, o.user_id, o.amount, o.created_at, u.vip_level, u.city_id
    FROM orders o JOIN users u ON o.user_id = u.id
    WHERE o.status='completed' AND o.created_at>=DATE_SUB(NOW(), INTERVAL 30 DAY)
)
SELECT DATE(created_at), COUNT(*), SUM(amount) FROM recent_orders GROUP BY DATE(created_at);
-- 優點：語法清晰、優化器可整體優化、無顯式建立/清理
-- 缺點：複雜 CTE 可能產生巨大執行計畫、不支援中間結果索引、重複引用需物化

總結

MySQL 臨時表是 **大表分步驟處理的經典優化工具**：
✅ 核心價值：將大表掃描一次，後續多次查詢走小表/索引
✅ 引擎選擇：**InnoDB 穩健通用，MEMORY 極速但有限制**
✅ 關鍵動作：**建立後立即建索引、用完显式 DROP、監控 Created_tmp_disk_tables**
✅ 現代替代：**CTE 適用簡單邏輯，複雜多步驟仍推薦臨時表**