MySQL Binlog 三種格式深度解析：STATEMENT vs ROW vs MIXED 選型指南

MySQL Binlog 三種格式深度解析：STATEMENT vs ROW vs MIXED 選型指南

Binlog 是 MySQL 複寫與時間點恢復的核心。格式選擇直接影響主從一致性、磁碟佔用、網路傳輸、複寫效能。本文從演進歷史、核心差異、適用場景、特殊物件處理、參數調優五個維度系統闡述。

一、核心配置與推薦

# 動態修改（會話/全局）
SET SESSION binlog_format = 'MIXED';
SET GLOBAL binlog_format = 'MIXED';

# 永久生效寫入 my.cnf
[mysqld]
binlog_format = MIXED

推薦：MIXED（5.1.8+ 預設）。Statement 為主，遇不安全語句自動切 Row，兼顧效能與一致性。

二、Binlog 基礎屬性

• 記錄：INSERT/UPDATE/DELETE/DDL 等修改操作；不記錄 SELECT。
• 三種模式：STATEMENT (SBR)、ROW (RBR)、MIXED (MBR)。
• 核心功能：複寫、時間點恢復 (PITR)、審計。
• ROW vs STATEMENT 核心權衡：主庫負載 vs 複寫一致性。

三、複寫演進歷程

四、複寫基本流程（經典單線程）

1. Slave IO Thread 連接 Master，請求指定 Binlog 位置後的事件。
2. Master Binlog Dump Thread 讀取並發送事件 + 當前 Binlog 文件名/位置。
3. Slave IO Thread 寫入本地 Relay Log，更新 master.info 記錄 Master 位置。
4. Slave SQL Thread 讀取 Relay Log，解析並重放事件。

五、Redo Log vs Undo Log（複習）

• Undo Log：原子性 + MVCC。操作前備份舊值，回滾用。存系統表空間或獨立 Undo 表空間。
• Redo Log：持久性 (WAL)。操作後記錄新值，崩潰恢復重放用。循環寫入 ib_logfile*。
• 崩潰恢復兩階段：Redo 前滾（重放已提交事務）→ Undo 回滾（撤銷未提交事務）。

六、ROW 格式 (RBR) 深度剖析

優點

1. 絕對安全：記錄每行具體變更（前後值），無上下文依賴。存儲過程、函數、觸發器、UDF、非確定性函數均能正確複寫。
2. 主鍵查詢加速：從庫有主鍵時，ROW 更新/刪除定位極快，鎖更少。
3. 特定語句鎖更少：INSERT ... SELECT、AUTO_INCREMENT 插入、無條件/小範圍 UPDATE/DELETE。

缺點

1. Binlog 膨脹：大批量更新/全表 ALTER 產生海量行事件。
   例：UPDATE t SET a=1 WHERE b=1 影響 100 萬行 → 100 萬行事件 vs 1 條 Statement。
2. ALTER TABLE 重建表 → 全表行事件，Binlog 暴增。
3. 併發寫入壓力：大事務大量行事件頻繁 fsync。
4. 大 BLOB/TEXT：UDF 產生大對象導致複寫延遲。

七、STATEMENT 格式 (SBR) 深度剖析

優點

1. Binlog 精簡：只記 SQL 文本，IO/磁碟/網路開銷極低。
2. 跨版本複寫友好：從庫版本可高於主庫。

缺點（不安全場景多）

1. 非確定性函數：UUID()、USER()、FOUND_ROWS()、SYSDATE()（除非 --sysdate-is-now）、RAND()、SLEEP()、LAST_INSERT_ID() 在存儲過程中等。
2. UDF：從庫必須同樣安裝且行為一致。
3. INSERT ... SELECT：全表掃描產生更多行鎖。
4. AUTO_INCREMENT：InnoDB 表插入鎖競爭。
5. 複雜語句從庫重放資源消耗大：優化器可能選擇不同執行計劃。
6. 存儲函數隱式 NOW()：每次調用執行 NOW()，行為差異。
7. 表結構必須幾乎一致：列順序、類型差異導致錯誤。
8. 僅支援 REPEATABLE READ 及以上隔離級別。

[mysqld]
binlog_row_image = MINIMAL  # 默認 FULL

是否有非確定性函數/UDF/觸發器/存儲函數？
  ├─ 是 → 強烈建議 ROW 或 MIXED
  └─ 否
      ├─ 大批量更新/ALTER TABLE 頻繁？
      │   ├─ 是 → STATEMENT 或 MIXED（避免 Binlog 爆炸）
      │   └─ 否
      │       ├─ 跨版本複寫/異構從庫？
      │       │   ├─ 是 → STATEMENT
      │       │   └─ 否
      │       │       ├─ 對一致性要求極高（金融/核心交易）？
      │       │       │   ├─ 是 → ROW（配合 binlog_row_image=MINIMAL）
      │       │       │   └─ 否 → MIXED（推薦預設）