MySQL 實例 IO 使用率持續 100% 是常見且棘手的性能瓶頸。本文從 **Binlog 刷盤策略** 與 **隱式臨時表導致磁碟溢寫** 兩大核心維度，深度剖析 IO 飆升根因，提供參數調優與 SQL 重寫的實戰優化方案。

一、 Binlog 同步策略對 IO 的影響

Binlog 是 MySQL 最關鍵的日志，直接影響持久性與主從復制。其刷盤頻率由兩個核心參數控制：

1. sync_binlog：控制 Binlog 刷盤頻率

• sync_binlog = 0：完全依賴 OS 緩存刷盤，**性能最好，風險最高** (OS Crash 丟失數據)。
• sync_binlog = 1：每次事務提交強制 fsync()，**最安全，IO 開銷最大**。
• sync_binlog = N (N>1)：每 N 次事務提交 fsync() 一次，**折中方案**。

線上調整 (無需重啟)：

SET GLOBAL sync_binlog = 500;  -- 每 500 次提交刷盤一次，大幅降低 IOPS

2. innodb_flush_log_at_trx_commit：控制 Redo Log 刷盤

• = 1 (預設)：每次提交 fsync() Redo Log，**ACID 完全保障，IO 壓力大**。
• = 2：提交時只寫入 OS Page Cache，每秒背景線程 fsync() 一次。**性能大幅提升，僅 OS Crash 可能丟失 1 秒數據**。
• = 0：完全交給背景線程每秒 fsync()，**性能最好，MySQL Crash 也可能丟數據**。

線上調整：

SET GLOBAL innodb_flush_log_at_trx_commit = 2;  -- 推薦生產環境折中值

> ⚠️ **持久化配置**：上述 SET GLOBAL 重啟失效，必須同步寫入 my.cnf。

3. 組合建議

場景	sync_binlog	innodb_flush_log_at_trx_commit	特點
金融核心/強一致性	1	1	最高安全，IO 壓力最大，需高性能存儲 (NVMe/RAID BBU)
一般業務/高併發	100-1000	2	**推薦平衡點**，性能提升 3-10 倍，僅 OS Crash 丟數據
非核心/可重放日志	0	2	極致性能，接受極小概率數據丟失

二、 臨時表導致 IO 飆升：經典案例深度復盤

1. 故障現場

• SQL： 多表 LEFT JOIN + DISTINCT + ORDER BY + LIMIT 20，查詢大量列 (g.*)。
• 現象： 執行 7 秒，IO 100%，磁碟讀寫飆升。
• 執行計劃： Using temporary; Using filesort。

2. 根因分析

MySQL 執行該 SQL 時：

1. JOIN 產生大量中間結果集 (含 g.* 所有列)。
2. DISTINCT + ORDER BY 觸發**內部臨時表** + **Filesort**。
3. 臨時表超過 tmp_table_size/max_heap_table_size (預設 16MB)，**從內存 (Memory 引擎) 轉存至磁碟 (MyISAM/InnoDB 引擎)**。
4. 磁碟臨時表讀寫導致 IO 100%，成爲性能瓶頸。

3. 臨時表觸發場景速查

場景	說明
ORDER BY 與 GROUP BY 列不同	如 ORDER BY price GROUP BY name
JOIN 中 ORDER BY/GROUP BY 非驅動表列	如 SELECT * FROM a JOIN b ORDER BY b.name
DISTINCT + ORDER BY	需去重再排序
SQL_SMALL_RESULT	提示優化器用臨時表

4. 直接使用磁碟臨時表的場景 (無法用內存)

• 包含 TEXT/BLOB 列。
• GROUP BY/DISTINCT 列長度 > 512 字節。
• UNION 查詢選擇列 > 512 字節。

5. 相關配置參數

tmp_table_size = 64M          # 內存臨時表大小上限
max_heap_table_size = 64M     # 用戶建立 MEMORY 表上限
# 實際生效上限 = MIN(tmp_table_size, max_heap_table_size)

6. 優化方案：SQL 拆分 (核心思想：縮小臨時表寬度)

原 SQL (慢)：

SELECT DISTINCT g.*, cp.name, c.name, t.name
FROM gm_game g
LEFT JOIN gm_cp cp ON ...
LEFT JOIN gm_category c ON ...
LEFT JOIN gm_type t ON ...
WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20;

優化後 (快 10-100 倍)：

-- 步驟 1: 僅查主鍵 ID 進行排序分頁 (窄表，無臨時表或極小臨時表)
SELECT g.id
FROM gm_game g
LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0
LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20;

-- 步驟 2: 應用層拼接 ID 列表，查詢詳細信息 (走主鍵點查，極快)
SELECT g.*, cp.name, c.name, t.name
FROM gm_game g
LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0
LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
WHERE g.deleted = 0 AND g.id IN (id1, id2, ..., id20)
ORDER BY g.modify_time DESC;

7. 實測效果對比

環境	優化前	優化後 (步驟1+步驟2)	提升倍數	IO 變化
SATA 磁碟	~50 秒	0.3s + 0.1s = 0.4s	125x	100% → <1%
SSD 磁碟	~7 秒	0.3s + 0.1s = 0.4s	17x	100% → <1%

結論： 優化前磁碟 IO 是瓶頸 (SSD 快 7 倍)；優化後磁碟不再是瓶頸，SSD/SATA 性能持平，CPU 成為新瓶頸。

三、 表設計原則：從源頭杜絕磁碟臨時表

1. 大字段拆表： TEXT/BLOB/JSON/長 VARCHAR 移至擴展表 (垂直分表)，主表僅保留高頻訪問列。
2. 索引支撐排序： 在 ORDER BY/GROUP BY 列上建立索引，利用索引有序性避免 Filesort。
3. 避免 SELECT *： 只查必要列，減少中間結果集寬度。

四、 SQL 優化通用手冊

1. 拆分大 SQL： 將排序/分組與詳細數據查詢分離 (如上案例)。
2. 業務妥協： 非核心報表/導出場景，評估去除 ORDER BY/GROUP BY。
3. 調大臨時表閾值 (治標)：

   tmp_table_size = 256M
   max_heap_table_size = 256M

   慎用：可能導致內存壓力，OOM 風險。
4. 使用覆蓋索引： 讓查詢僅掃描索引不回表。

五、 總結

MySQL IO 過高通常由 **日志刷盤過於頻繁** 或 **糟糕 SQL 觸發磁碟臨時表** 引起。解決路徑：

1. 參數層面： 根據業務容忍度調整 sync_binlog (建議 500-1000) 和 innodb_flush_log_at_trx_commit=2，立竿見影降低 IOPS。
2. SQL 層面： 識別 Using temporary; Using filesort 的慢查詢，通過 **拆分 SQL 縮小臨時表寬度**、**垂直拆表移除大字段**、**建立支撐索引** 徹底解決。
3. 架構層面： 讀寫分離將讀壓力分流；分庫分表降低單表數據量；核心業務上 NVMe/RAID 卡 (帶 BBU) 硬抗 IO。

調優是系統工程，**先參數後 SQL，先治標後治本，監控驗證每一步收益**。