MySQL 臨時表使用指南：創建、類型選擇與性能優化

臨時表核心特性

• 生命周期：綁定會話，斷開自動刪除，不影響其他連接
• 命名隔離：同名永久表被隱藏，會話內優先訪問臨時表
• 權限：需 CREATE TEMPORARY TABLES 權限

基本創建語法

-- 磁盤臨時表 (默認 MyISAM/InnoDB)
CREATE TEMPORARY TABLE tmp_sales (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    sale_date DATE
) ENGINE=InnoDB;

-- 內存臨時表 (HEAP/MEMORY 引擎)
CREATE TEMPORARY TABLE tmp_fast_lookup (
    code CHAR(10) PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

MEMORY vs 磁盤臨時表選擇

實戰優化案例

場景：大表 orders (5000 萬行) 需按多維度統計，直接查詢耗時 30s+。

優化：

-- 1. 抽取近 30 天熱數據到內存臨時表
CREATE TEMPORARY TABLE tmp_recent_orders ENGINE=MEMORY
SELECT order_id, user_id, amount, create_time
FROM orders
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND status = 'paid';

-- 2. 創建索引加速後續 JOIN
ALTER TABLE tmp_recent_orders ADD PRIMARY KEY (order_id);
ALTER TABLE tmp_recent_orders ADD INDEX idx_user (user_id);

-- 3. 多維度統計全部在臨時表上完成
SELECT user_id, COUNT(*), SUM(amount) FROM tmp_recent_orders GROUP BY user_id;
SELECT DATE(create_time) AS dt, COUNT(*) FROM tmp_recent_orders GROUP BY dt;
-- ... 其他報表查詢

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

效果：報表查詢從 30s 降至 0.5s 以內。

避坑指南

• tmp_table_size / max_heap_table_size 太小導致 MEMORY 表自動轉磁盤，性能驟降
• MEMORY 表不支持 TEXT/BLOB，強制轉磁盤
• 事務中使用臨時表：InnoDB 臨時表支持事務，MEMORY 不支持
• 複製表結構：CREATE TEMPORARY TABLE tmp LIKE original; (不複製數據)

驗證臨時表引擎

SHOW CREATE TABLE tmp_sales;
-- 關注 ENGINE=... 行