MySQL 臨時表監控：Created_tmp_tables、Created_tmp_disk_tables 與 tmp_table_size 調優

核心狀態變數

mysql> SHOW GLOBAL STATUS LIKE 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197   |
| Created_tmp_files       | 58      |
| Created_tmp_tables      | 1771587 |
+-------------------------+---------+

變數含義

• Created_tmp_tables：總共建立的內部臨時表數（含記憶體與磁碟）。
• Created_tmp_disk_tables：因大小超過限制而轉為磁碟臨時表的數量。
• Created_tmp_files：MySQL 建立的臨時檔案數（如排序、日誌）。

關鍵指標：磁碟臨時表比率

Created_tmp_disk_tables / Created_tmp_tables * 100%
-- 範例：21197 / 1771587 ≈ 1.2%  (優秀，建議 < 10%)

控制參數

mysql> SHOW VARIABLES WHERE Variable_name IN ('tmp_table_size','max_heap_table_size');
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 268435456 |  -- 256MB
| tmp_table_size      | 536870912 |  -- 512MB
+---------------------+-----------+

• 臨時表引擎為 MEMORY 時，**實際上限取兩者較小值**（此例 256MB）。
• 超過上限 → 自動轉換為 MyISAM 磁碟臨時表（效能大幅下降）。

調優建議

1. 監控磁碟臨時表比率：持續 > 10% 需調大參數或優化查詢。
2. 適度調大 tmp_table_size / max_heap_table_size：視記憶體容量，建議不超過總記憶體 1~2%。
3. 優化查詢避免臨時表：
   • 為 `GROUP BY`、`ORDER BY`、`DISTINCT` 欄位建立索引。
   • 避免 `SELECT *`，減少行寬度。
   • 將大型臨時表查詢拆分為多步驟（應用層或暫存表）。
4. MySQL 8.0+：內部臨時表預設改用 InnoDB，`tmp_table_size` 仍控制記憶體上限，超過則用 InnoDB 磁碟表。