面對超大表複雜查詢,將中間結果物化到**臨時表**往往能將查詢時間從分鐘級降至秒級。MySQL 臨時表具有**會話級隔離、自動清理、支援 Memory/InnoDB/MyISAM 引擎**等特性。本文系統整理建立語法、引擎選擇策略、生命週期管理與效能調優要點。
-- 基礎語法
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INT NOT NULL
);
-- 指定儲存引擎 (MySQL 5.6+ 預設 InnoDB,舊版 MEMORY)
CREATE TEMPORARY TABLE tmp_table (
id INT PRIMARY KEY,
data VARCHAR(100)
) ENGINE = MEMORY; -- 或 InnoDB, MyISAM
| 特性 | MEMORY (HEAP) | InnoDB (預設 5.6+) | MyISAM |
|---|---|---|---|
| 儲存位置 | 純記憶體 | 磁碟 (含 Buffer Pool) | 磁碟 |
| 最大容量 | 受限 max_heap_table_size / tmp_table_size (較小者) | 受限磁碟空間 | 受限磁碟空間 |
| 支援索引類型 | HASH (預設), BTREE | BTREE (聚集+二級) | BTREE |
| 支援 BLOB/TEXT | **不支援** | 支援 | 支援 |
| 支援變長欄位 | **不支援** (VARCHAR 實際存定長) | 支援 | 支援 |
| 交易支援 | 無 | **完整 ACID** | 無 |
| 鎖粒度 | 表鎖 | 行鎖 (MVCC) | 表鎖 |
| 崩潰恢復 | 資料全失 | Redo Log 恢復 | 損壞風險高 |
| 適用場景 | 小結果集、極高頻讀寫、無持久需求 | **通用首選**、大結果集、需交易、含大字段 | 舊系統相容、全文索引 |
tmp_table,Session B **不可見**,可同名共存。DROP。DROP TEMPORARY TABLE IF EXISTS tmp_table; (加 TEMPORARY 關鍵字避免誤刪同名永久表)。USING HASH) O(1) 查找;範圍查詢需 **BTREE**。MEMORY 表超過 max_heap_table_size (預設 16MB) 或含 BLOB/TEXT/VARCHAR 實際長度過大 → **自動轉換為 MyISAM 磁碟表**,效能驟降。對策:
tmp_table_size / max_heap_table_size (Session 級 SET SESSION tmp_table_size = 67108864; 64MB)。-- 單次 INSERT 多值,減少語句解析與提交開銷
INSERT INTO tmp_table (name, value) VALUES ('a',1), ('b',2), ...; -- 批次 1000-5000 行
-- 或 LOAD DATA INFILE (需 FILE 權限,極快)
LOAD DATA INFILE '/tmp/data.csv' INTO TABLE tmp_table FIELDS TERMINATED BY ',';
臨時表**無持久統計資訊**,優化器依賴採樣估計。大表查詢前執行 ANALYZE TABLE tmp_table; 更新統計,避免嚴重低估導致錯誤計畫。
| 陷阱 | 現象 | 解決 |
|---|---|---|
| 連線池復用導致臨時表殘留 | SP 結束未 DROP,下次復用連線發現同名表報錯或資料髒讀 | SP 開頭/結尾強制 DROP TEMPORARY TABLE IF EXISTS ... |
| MEMORY 表 VARCHAR 實際存定長 | 定義 VARCHAR(1000) 只存 "a" 仍佔 1000 bytes 記憶體 | 改用 InnoDB 或精確定義長度 |
| 隱式轉 MyISAM 導致表鎖爭用 | 高併發寫入臨時表出現 Lock_wait_timeout | 強制 ENGINE=InnoDB 或調大 tmp_table_size |
| 主從複寫中臨時表處理 | ROW 格式複寫會複製臨時表操作;STATEMENT 格式可能不同步 | 啟用 replicate-wild-ignore-table=%.tmp_% 或改用永久表+定時清理 |
MySQL 臨時表是**會話級資料物化**的利器。選對引擎 (現代版本首選 InnoDB)、控制記憶體參數、建立必要索引、批量載入、主動 ANALYZE、連線池環境下嚴格清理,即能將複雜查詢效能提升數量級。記住:**臨時表不是銀彈,良好的索引設計與查詢改寫永遠是第一優先序。**