InnoDB交易記錄（redo log 和 undo log）詳解

InnoDB交易記錄（redo log 和 undo log）詳解

為了最大程度避免資料寫入時io瓶頸帶來的效能問題，MySQL採用了這樣一種緩衝機制：當query修改資料庫內資料時，InnoDB先將該資料從磁碟讀取到記憶體中，修改記憶體中的資料拷貝，並將該修改行為持久化到磁碟上的交易記錄（先寫redo log buffer，再定期批量寫入），而不是每次都直接將修改過的資料記錄到硬碟內，等交易記錄持久化完成之後，記憶體中的髒資料可以慢慢刷回磁碟，稱之為Write-Ahead Logging。交易記錄採用的是追加寫入，順序io會帶來更好的效能優勢。

為了避免髒資料刷回磁碟過程中，掉電或系統故障帶來的資料丟失問題，InnoDB採用交易記錄（redo log）來解決該問題。

【redo log】

用於在執行個體故障恢複時，繼續那些已經commit但資料尚未完全回寫到磁碟的事務。

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. 

通常會初始化2個或更多的 ib_logfile 儲存 redo log，由參數 innodb_log_files_in_group 確定個數，命名從 ib_logfile0 開始，依次寫滿 ib_logfile 並順序重用（in a circular fashion）。如果最後1個 ib_logfile 被寫滿，而第一個ib_logfile 中所有記錄的事務對資料的變更已經被持久化到磁碟中，將清空並重用之。

在寫入量比較高的情況下，redo log 的大小將顯著地影響寫入效能。

innodb_log_file_size 用來控制 ib_logfile 的大小。5.5版本及以前：預設5MB，最大4G。

所有交易記錄累加大小不超過4G，交易記錄過大，checkpoint會減少，在節省磁碟io的同時，大的交易記錄也意味著資料庫crash後，恢複起來較慢。

官方文檔建議innodb_log_file_size的設定可參考 show engine innodb status \G

Log sequence number -Last checkpoint < (innodb_log_files_in_group * innodb_log_file_size ) * 0.75

【undo log】

記錄了資料修改的前鏡像。存放於ibdata中。（提一句，DDL操作會修改資料字典，該資訊也存放在ibdata中）

用於在執行個體故障恢複時，藉助undo log將尚未commit的事務，復原到事務開始前的狀態。

An undo log is a collection of undo log records associated with a single transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from the undo log records. Undo logs exist within undo log segments, which are contained within rollback segments. Rollback segments are physically part of the system tablespace. For related information, see Section 14.6, “InnoDB Multi-Versioning”.

The InnoDB crash recovery process consists of several steps:（指的是MySQL server crash，不包括hardware failure 或者 server system error）

Redo log application
Redo log application is the first step and is performed during initialization, before accepting any connections. If all changes are flushed from the buffer pool to the tablespaces (ibdata* and *.ibd files) at the time of the shutdown or crash, redo log application is skipped. InnoDB also skips redo log application if redo log files are missing at startup.

Removing redo logs to speed up recovery is not recommended, even if some data loss is acceptable. Removing redo logs should only be considered after a clean shutdown, with innodb_fast_shutdown set to 0 or 1.

Roll back of incomplete transactions
Incomplete transactions are any transactions that were active at the time of crash or fast shutdown. The time it takes to roll back an incomplete transaction can be three or four times the amount of time a transaction is active before it is interrupted, depending on server load.

You cannot cancel transactions that are being rolled back. In extreme cases, when rolling back transactions is expected to take an exceptionally long time, it may be faster to start InnoDB with an innodb_force_recovery setting of 3 or greater. See Section 14.23.2, “Forcing InnoDB Recovery”.

Change buffer merge
Applying changes from the change buffer (part of the system tablespace) to leaf pages of secondary indexes, as the index pages are read to the buffer pool.

Purge
Deleting delete-marked records that are no longer visible to active transactions.

The steps that follow redo log application do not depend on the redo log (other than for logging the writes) and are performed in parallel with normal processing. Of these, only rollback of incomplete transactions is special to crash recovery. The insert buffer merge and the purge are performed during normal processing.

After redo log application, InnoDB attempts to accept connections as early as possible, to reduce downtime. As part of crash recovery, InnoDB rolls back transactions that were not committed or in XA PREPARE state when the server crashed. The rollback is performed by a background thread, executed in parallel with transactions from new connections. Until the rollback operation is completed, new connections may encounter locking conflicts with recovered transactions.