共計 1991 個字符，預計需要花費 5 分鐘才能閱讀完成。
如何理解 MySQL 中 binlog 和 innodb_flush_log_at_trx_commit，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
innodb_flush_log_at_trx_commit 和 sync_binlog 兩個參數是控制 MySQL 磁盤寫入策略以及數據安全性的關鍵參數。本文從參數含義，性能，安全角度闡述兩個參數為不同的值時對 db 性能, 數據的影響.
一 參數意義
innodb_flush_log_at_trx_commit
如果 innodb_flush_log_at_trx_commit 設置為 0，log buffer 將每秒一次地寫入 log file 中，并且 log file 的 flush(刷到磁盤) 操作同時進行. 該模式下，在事務提交的時候，不會主動觸發寫入磁盤的操作。
如果 innodb_flush_log_at_trx_commit 設置為 1，每次事務提交時 MySQL 都會把 log buffer 的數據寫入 log file，并且 flush(刷到磁盤) 中去.
如果 innodb_flush_log_at_trx_commit 設置為 2，每次事務提交時 MySQL 都會把 log buffer 的數據寫入 log file. 但是 flush(刷到磁盤) 操作并不會同時進行。該模式下,MySQL 會每秒執行一次 flush(刷到磁盤) 操作。
注意：
由于進程調度策略問題, 這個“每秒執行一次 flush(刷到磁盤) 操作”并不是保證 100% 的“每秒”。
sync_binlog
sync_binlog 的默認值是 0，像操作系統刷其他文件的機制一樣，MySQL 不會同步到磁盤中去而是依賴操作系統來刷新 binary log。
當 sync_binlog =N (N 0)，MySQL 在每寫 N 次 二進制日志 binary log 時，會使用 fdatasync() 函數將它的寫二進制日志 binary log 同步到磁盤中去。
注:
如果啟用了 autocommit，那么每一個語句 statement 就會有一次寫操作；否則每個事務對應一個寫操作。
根據上述描述，我做了一張圖，可以方便大家查看。
二 性能
兩個參數在不同值時對 db 的純寫入的影響表現如下：
測試場景 1
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
測試場景 2
innodb_flush_log_at_trx_commit=1
sync_binlog=1000
測試場景 3
innodb_flush_log_at_trx_commit=1
sync_binlog=1
測試場景 4
innodb_flush_log_at_trx_commit=1
sync_binlog=1000
測試場景 5
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
場景 TPS 場景 1 41000 場景 2 33000 場景 3 26000 場景 4 33000
由此可見，當兩個參數設置為雙 1 的時候，寫入性能最差，sync_binlog=N (N 1) innodb_flush_log_at_trx_commit=2 時，(在當前模式下)MySQL 的寫操作才能達到最高性能。
三 安全
當 innodb_flush_log_at_trx_commit 和 sync_binlog 都為 1 時是最安全的，在 mysqld 服務崩潰或者服務器主機 crash 的情況下，binary log 只有可能丟失最多一個語句或者一個事務。但是魚與熊掌不可兼得，雙 11 會導致頻繁的 io 操作，因此該模式也是最慢的一種方式。
當 innodb_flush_log_at_trx_commit 設置為 0，mysqld 進程的崩潰會導致上一秒鐘所有事務數據的丟失。
當 innodb_flush_log_at_trx_commit 設置為 2，只有在操作系統崩潰或者系統掉電的情況下，上一秒鐘所有事務數據才可能丟失。
雙 1 適合數據安全性要求非常高，而且磁盤 IO 寫能力足夠支持業務，比如訂單, 交易, 充值, 支付消費系統。雙 1 模式下，當磁盤 IO 無法滿足業務需求時 比如 11.11 活動的壓力。推薦的做法是 innodb_flush_log_at_trx_commit=2，sync_binlog=N (N 為 500 或 1000) 且使用帶蓄電池后備電源的緩存 cache，防止系統斷電異常。
系統性能和數據安全是業務系統高可用穩定的必要因素。我們對系統的優化需要尋找一個平衡點，合適的才是最好的，根據不同的業務場景需求，可以將兩個參數做組合調整，以便是 db 系統的性能達到最優化。
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。
