MySQL 二進制日誌：事務性與非事務性語句寫入規則差異詳解

MySQL Binlog 記錄所有資料變更，是主從複制與時間點恢復（PITR）的基石。事務性引擎與非事務性引擎的語句在 Binlog 中的寫入時機、緩衝機制截然不同。本文詳細解析三大寫入規則與實戰影響。

核心概念：事務緩衝

• 事務執行期間，修改先寫入事務緩衝（記憶體），不直接寫 Binlog。
• 事務 COMMIT 時，將緩衝內所有事件**一次性順序寫入** Binlog（保證原子性）。
• 並發事務交織執行，靠事務緩衝區隔事件序列。

非事務性語句寫入三大規則

關鍵場景：事務中操作非事務表（MyISAM/MEMORY）

START TRANSACTION;
INSERT INTO innodb_table ...;      -- 事務性，進緩衝
INSERT INTO myisam_table ...;      -- 非事務性，但緩衝非空 → 規則3，進緩衝
COMMIT;                            -- 一次性刷盤

⚠️ **風險**：若事務後續 ROLLBACK，非事務表的修改**無法回滾**，但 Binlog 已記錄該操作 → **主從數據不一致**！

最佳實踐

1. 避免同一事務混用事務/非事務表。拆分為獨立事務或自動提交。
2. 若必須混用，非事務表操作放在事務**最開始**（規則 2 直接寫 Binlog），事務失敗時至少 Binlog 順序與實際一致（但主庫數據仍不一致）。
3. 生產環境**全面遷移至 InnoDB**，杜絕非事務表。
4. 啟用 binlog_format=ROW，基於行變更記錄，減少語句級混合帶來的語意歧義。

驗證指令

SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'sync_binlog';  -- 1 = 每次提交刷盤，最安全
SHOW ENGINE INNODB STATUS\G  -- 觀察 Log buffer 與 Binlog 協同

總結

Binlog 寫入機制：事務性進緩衝隨提交刷盤，非事務性看緩衝狀態決定直寫或進緩衝。混合操作是主從不一致溫床，**架構層面統一 InnoDB + ROW 格式** 是根治之道。