MySQL 臨時表加速查詢實戰：從全表掃描到索引命中的性能躍升

核心場景

大表頻繁執行複雜查詢，涉及多表 JOIN、篩選、排序，執行計劃顯示 Using temporary; Using filesort，響應時間秒級甚至超時。

臨時表優化原理

• 預計算結果集：將高成本操作（JOIN、聚合、排序）結果物化到臨時表
• 縮小掃描範圍：後續查詢針對小得多的臨時表，而非原大表
• 消除重複排序：臨時表建立時已按需排序，省去 filesort
• 簡化優化器決策：減少統計信息收集、索引合並、成本評估開銷

實戰案例：客戶應收賬款查詢

原始慢查詢

SELECT c.name, r.balance, ...other columns
FROM customers c
JOIN receivables r ON c.customer_id = r.customer_id
WHERE r.balance > 0
  AND c.postcode > '98000'
ORDER BY c.name;

執行計劃痛點：

• customers 全表掃描（無 postcode 索引）
• receivables 全表掃描（無 balance 索引）
• JOIN 後大量行排序 ORDER BY c.name → Using filesort
• 臨時表創建於磁盤（超過 tmp_table_size） → Using temporary

臨時表優化版

-- 步驟 1：創建臨時表，預先 JOIN、篩選、排序
CREATE TEMPORARY TABLE tmp_cust_balance AS
SELECT c.customer_id, c.name, c.postcode, r.balance, ...other columns
FROM customers c
JOIN receivables r ON c.customer_id = r.customer_id
WHERE r.balance > 0
ORDER BY c.name;  -- 關鍵：建表時完成排序

-- 步驟 2：創建針對性索引（臨時表支持索引！）
ALTER TABLE tmp_cust_balance ADD INDEX idx_postcode (postcode);

-- 步驟 3：輕量級查詢臨時表
SELECT * FROM tmp_cust_balance WHERE postcode > '98000';

-- 步驟 4：會話結束自動清理，或顯式 DROP
DROP TEMPORARY TABLE IF EXISTS tmp_cust_balance;

性能對比

進階技巧：臨時表全生命周期管理

1. 引擎選擇：Memory vs InnoDB vs MyISAM

-- 顯式指定引擎（默認 Memory，數據大時自動轉 MyISAM）
CREATE TEMPORARY TABLE tmp_xxx ENGINE=InnoDB SELECT ...;
-- InnoDB 臨時表：支持事務、行鎖、崩潰安全，適合大數據量
-- Memory 引擎：全內存，極快，但受 max_heap_table_size 限制，不支持 BLOB/TEXT

2. 索引策略

• 臨時表創建後可 ALTER TABLE ... ADD INDEX，**查詢模式決定索引**
• 複合索引覆蓋 WHERE + ORDER BY + GROUP BY 列
• 會話結束自動釋放，無需擔心索引維護成本

3. 避免「臨時表爆炸」

-- 監控臨時表大小
SHOW STATUS LIKE 'Created_tmp_disk_tables';  -- 磁盤臨時表計數
SHOW STATUS LIKE 'Created_tmp_tables';      -- 總臨時表計數

-- 控制參數
tmp_table_size = 64M
max_heap_table_size = 64M
-- 超過此大小自動轉 MyISAM 磁盤表，性能驟降

適用與不適用場景判斷

現代替代方案：CTE (Common Table Expressions)

MySQL 8.0+ 支持 WITH 子句，語義等價但更優雅：

WITH cust_balance AS (
  SELECT c.customer_id, c.name, c.postcode, r.balance
  FROM customers c
  JOIN receivables r ON c.customer_id = r.customer_id
  WHERE r.balance > 0
)
SELECT * FROM cust_balance WHERE postcode > '98000';

• 優化器可選擇**物化**或**內聯**執行，自動決策最優路徑
• 無需手動 DROP，語句級作用域，無洩漏風險
• 支持遞歸查詢、多層嵌套，可維護性強

總結

臨時表是**「以空間換時間、以預計算換即時計算」**的經典優化手法。關鍵在於：

1. 識別「重複、昂貴、可縮小」的查詢模式
2. 在臨時表上建立**針對性索引**（這往往被忽略）
3. 控制生命周期，避免連接洩漏導致 Created_tmp_tables 暴增
4. MySQL 8.0+ 優先考慮 WITH CTE，兼具性能與可讀性

合理使用，能將複雜報表查詢從「秒級」推向「毫秒級」，是 DBA 與開發必備的利器。