臨時表核心特性

• 會話隔離：同名臨時表在不同連接中互不干擾，會話結束自動清理。
• 屏蔽同名永久表：創建臨時表時，同名永久表不可見（直到臨時表 DROP）。
• 權限簡化：無需 CREATE TABLE 權限，只要有 CREATE TEMPORARY TABLES 權限即可。

創建語法與引擎選擇

1. 磁盤臨時表（默認 InnoDB/MyISAM）

CREATE TEMPORARY TABLE tmp_user_stats (
  user_id BIGINT NOT NULL,
  login_cnt INT NOT NULL DEFAULT 0,
  last_login DATETIME,
  PRIMARY KEY (user_id)
) ENGINE=InnoDB;  -- 支持事務、行鎖、外鍵

2. 內存臨時表（HEAP / MEMORY 引擎）

CREATE TEMPORARY TABLE tmp_lookup (
  code CHAR(10) NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE=MEMORY;  -- 數據全在內存，哈希索引默認，極快

HEAP 限制：

• 不支持 BLOB/TEXT、VARCHAR 超過定長（實際存固定長度）。
• 不支持 AUTO_INCREMENT（5.6+ 已支持）。
• 最大大小受 max_heap_table_size、tmp_table_size 限制（取較小者，默認 16MB），超限自動轉磁盤 MyISAM。
• 重啟/崩潰數據全丟，僅適用於可重算的查找表/中間結果。

典型應用場景

場景一：大表多次複用的中間結果集

-- 原查詢：多次 JOIN 同一大表
SELECT ... FROM big_table JOIN dim1 ...;
SELECT ... FROM big_table JOIN dim2 ...;

-- 優化：先篩選核心鍵值到臨時表
CREATE TEMPORARY TABLE tmp_core_keys AS
SELECT id FROM big_table WHERE create_time > '2024-01-01' AND status=1;

-- 後續多次 JOIN tmp_core_keys（極小、有索引、內存命中）
SELECT ... FROM tmp_core_keys t JOIN dim1 ON t.id = dim1.id;
SELECT ... FROM tmp_core_keys t JOIN dim2 ON t.id = dim2.id;

場景二：複雜報表的分步計算

CREATE TEMPORARY TABLE tmp_step1 AS
SELECT user_id, COUNT(*) AS cnt FROM events WHERE dt >= '2024-01-01' GROUP BY user_id;

CREATE TEMPORARY TABLE tmp_step2 AS
SELECT u.*, s.cnt FROM users u JOIN tmp_step1 s ON u.id = s.user_id WHERE s.cnt > 10;

-- 最終報表查詢 tmp_step2，邏輯清晰、易調試、避免巨大單句 SQL

場景三：應用層分頁緩存（配合 MEMORY 引擎）

-- 應用層首次查詢寫入臨時表
CREATE TEMPORARY TABLE tmp_page_cache ENGINE=MEMORY AS
SELECT id, title, summary FROM articles WHERE category='tech' ORDER BY id DESC;

-- 後續分頁直接讀臨時表
SELECT * FROM tmp_page_cache LIMIT 20 OFFSET 40;

性能優化關鍵點

1. 選對引擎：小表/查找表 → MEMORY；大表/含大字段/需事務 → InnoDB。
2. 調大臨時表限制：SET SESSION tmp_table_size = 256M; SET SESSION max_heap_table_size = 256M; 避免隱式轉磁盤。
3. 顯式索引：臨時表同樣受益於索引，CREATE INDEX idx_uid ON tmp_table(user_id);。
4. 避免重複創建：連接池環境下，檢查是否存在再創建（IF NOT EXISTS 不支持臨時表，需查 INFORMATION_SCHEMA.TABLES）。
5. 及時 DROP：長連接中處理完立即 DROP TEMPORARY TABLE，釋放元數據鎖與空間。

監控與排查

-- 查看當前會話臨時表
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;  -- 5.7+

-- 查看臨時表空間使用
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'tmp_%';

-- 發現 Created_tmp_disk_tables 增長快 → 調大 tmp_table_size / 優化查詢避免隱式磁盤臨時表

臨時表是「查詢重構的瑞士軍刀」：將複雜邏輯拆解為多步、物化中間結果、利用索引加速後續關聯。但濫用會導致 tempdb/系統表爭用、內存壓力。原則：小結果集、多次複用、邏輯解耦 時使用，用完即銷毀。