MySQL 5.7 臨時表空間優化深度解析：獨立表空間、資訊表與效能提升

臨時表分類

MySQL 5.7 兩大關鍵優化

1. InnoDB 臨時表空間獨立化

• **5.6 及之前**：臨時表資料與 Undo Log 共存於共享表空間 `ibdata1`，導致 `ibdata1` 單調增長、無法收縮、爭用熱點
• **5.7 新增參數**：`innodb_temp_data_file_path` (預設 `ibtmp1:12M:autoextend`)
• **效果**：臨時表資料、未壓縮表的 Undo Log 寫入獨立表空間，**不再膨脹 ibdata1**，支援獨立管理、監控、清理
• **限制**：路徑繼承 `innodb_data_home_dir`，無法直接指定其他磁碟 (可用符號鏈接繞過)

2. 臨時表資訊暴露於 `information_schema.innodb_temp_table_info`

• 5.7 前無標準方式檢視內部臨時表元資料
• 新視圖欄位：`TABLE_ID`、`NAME` (如 `#sql1705_2_9`)、`N_COLS`、`SPACE` (表空間 ID)、`PER_TABLE_TABLESPACE`、`IS_COMPRESSED`
• 用途：監控臨時表數量、識別大臨時表、關聯 `innodb_tablespaces` 分析空間佔用

實戰驗證

配置獨立表空間至 tmpfs (記憶體盤)

-- Linux 符號鏈接
ln -s /run/shm/ /var/lib/mysql/tmp_space2

-- my.cnf
[mysqld]
innodb_temp_data_file_path=tmp_space2/ibtmp2:200M:autoextend

-- 重啟驗證
SELECT @@innodb_temp_data_file_path;
-- 結果: tmp_space2/ibtmp2:200M:autoextend

批量建立臨時表並查詢元資料

-- 建立預存程序建立 10 個臨時表
DELIMITER $$
CREATE PROCEDURE sp_create_temp_tables(IN cnt INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= cnt DO
        SET @sql = CONCAT('CREATE TEMPORARY TABLE tmp', i, ' (id INT, desc VARCHAR(60))');
        PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

CALL sp_create_temp_tables(10);

-- 查詢臨時表資訊
SELECT * FROM information_schema.innodb_temp_table_info;
-- 結果: 10 列，NAME 為 #sql{thread_id}_{seq}、SPACE 指向臨時表空間

效能影響與最佳實踐

• **減少 ibdata1 爭用**：長跑報表、大排序、派生表物化不再衝擊核心表空間
• **tmpfs 加速**：高頻臨時表場景 (複雜報表、ETL 中間結果) 獲益顯著
• **監控告警**：定期查詢 `innodb_temp_table_info` 計數、關聯 `innodb_tablespaces.FILE_SIZE` 告警臨時表空間暴增
• **參數調優**：`tmp_table_size`、`max_heap_table_size` 控制記憶體臨時表上限，超限轉磁碟 (MyISAM/InnoDB 臨時表)

版本演進補充

• MySQL 8.0：臨時表預設使用 InnoDB (而非 MyISAM)、支援 `INSTANT DDL`、臨時表空間進一步優化
• MariaDB 10.2+：類似獨立臨時表空間實作

MySQL 5.7 將臨時表空間從 `ibdata1` 剝離並暴露元資料，是穩定性與可觀測性的重要里程碑，配合 tmpfs 可大幅提升高併發複雜查詢吞吐。