MySQL 臨時表加速重複查詢實戰：避免多次排序與掃描

核心思想

將「大表連接 + 過濾 + 排序」的昂貴操作**物質化**為一張**已排序的小臨時表**，後續高頻查詢直接掃描臨時表，極大減少 I/O 與 CPU。

場景還原

原始查詢：查詢余額大於 0 且郵編 > '98000' 的客戶，按名稱排序，需反覆執行。

SELECT c.name, r.balance, ...
FROM customer c, receivable r
WHERE c.customer_id = r.customer_id
  AND r.balance > 0
  AND c.postcode > '98000'
ORDER BY c.name;

優化步驟

1. 建立臨時表，預先完成連接、過濾、排序

   CREATE TEMPORARY TABLE cust_with_balance AS
   SELECT c.name, r.balance, c.postcode, ...
   FROM customer c, receivable r
   WHERE c.customer_id = r.customer_id
     AND r.balance > 0
   ORDER BY c.name;

2. 後續查詢直接掃描臨時表

   SELECT * FROM cust_with_balance WHERE postcode > '98000';

效果分析

• 臨時表行數 ≪ 原表行數（已過濾）。
• 物理順序 = 排序順序，無 filesort，順序 I/O 極快。
• 避免重複連接、過濾、排序開銷。

關鍵限制與注意事項

進階用法

• CREATE TEMPORARY TABLE ... ENGINE=MEMORY：極速讀取，但重啟丟失、大小受 max_heap_table_size 限制。
• 定時任務 REPLACE INTO tmp SELECT ... 刷新準實時數據。
• 結合 INDEX(postcode) 於臨時表進一步加速篩選。