《MySQL 進階篇》十九：事務日誌

ACatSmiling發表於2024-09-22
MySql

Author: ACatSmiling

Since: 2024-09-21

事務有 4 種特性：原子性、一致性、隔離性和永續性。那麼事務的 4 種特性到底是基於什麼機制實現呢？

  • 事務的隔離性由鎖機制實現。
  • 而事務的原子性、一致性和永續性由事務的redo log和undo log來保證。
    • redo log 稱為重做日誌，提供再寫入操作，恢復提交事務修改的頁操作，用來保證事務的永續性。
    • undo log 稱為回滾日誌，回滾行記錄到某個特定版本，用來保證事務的原子性、一致性。

有的 DBA 或許會認為 undo log 是 redo log 的逆過程，其實不然。redo log 和 undo log 都可以視為是一種恢復操作。但是：

  • redo log：是儲存引擎層（InnoDB）生成的日誌，記錄的是物理級別上的頁修改操作，比如頁號 xxx、偏移量 yyy 寫入了 zzz 資料。主要為了保證資料的可靠性。
  • undo log：是儲存引擎層（InnoDB）生成的日誌，記錄的是邏輯操作日誌，比如對某一行資料進行了 INSERT 語句操作，那麼 undo log 就記錄一條與之相反的 DELETE 操作。主要用於事務的回滾（undo log 記錄的是每個修改操作的逆操作）和一致性非鎖定讀（undo log 回滾行記錄到某種特定的版本 ---> MVCC，即多版本併發控制）。

redo log

InnoDB 儲存引擎是以頁為單位來管理儲存空間的。在真正訪問頁面之前，需要把在磁碟上的頁快取到記憶體中的Buffer Pool之後才可以訪問。所有的變更都必須先更新緩衝池中的資料，然後緩衝池中的髒頁會以一定的頻率被刷入磁碟（checkpoint 機制），透過緩衝池來最佳化 CPU 和磁碟之間的鴻溝，這樣就可以保證整體的效能不會下降太快。

為什麼需要 redo log

一方面，緩衝池可以幫助我們消除 CPU 和磁碟之間的鴻溝，checkpoint 機制可以保證資料的最終落盤，然而由於 checkpoint 並不是每次變更的時候就觸發的，而是 master 執行緒隔一段時間去處理的。所以最壞的情況就是事務提交後，剛寫完緩衝池，資料庫當機了，那麼這段資料就是丟失的，無法恢復。

另一方面，事務包含永續性的特性，就是說對於一個已經提交的事務，在事務提交後即使系統發生了崩潰，這個事務對資料庫中所做的更改也不能丟失。

那麼如何保證這個永續性呢？一個簡單的做法：在事務提交完成之前把該事務所修改的所有頁面都重新整理到磁碟。但是這個簡單粗暴的做法有些問題：

  • 修改量與重新整理磁碟工作量嚴重不成比例
    • 有時候僅僅修改了某個頁面中的一個位元組，但是我們知道在 InnoDB 中是以頁為單位來進行磁碟 I/O 的，也就是說在該事務提交時不得不將一個完整的頁面從記憶體中重新整理到慈盤，我們又知道一個頁面預設是 16 KB 大小，只修改一個位元組就要重新整理 16 KB 的資料到磁碟上，顯然是太小題大做了。
  • 隨機 I/O 重新整理較慢
    • 一個事務可能包含很多語句，即使是一條語句也可能修改許多頁面，假如該事務修改的這些頁面可能並不相鄰，這就意味著在將某個事務修改的 Buffer Pool 中的頁面重新整理到磁碟時，需要進行很多的隨機 I/O，隨機 I/O 比順序 I/O 要慢，尤其對於傳統的機械硬碟來說。

另一個解決的思路：我們只是想讓已經提交了的事務對資料庫中資料所做的修改永久生效，即使後來系統崩潰，在重啟後也能把這種修改恢復出來。所以，其實沒有必要在每次事務提交時就把該事務在記憶體中修改過的全部頁面重新整理到磁碟，只需要把修改了哪些東西記錄一下就好。比如，某個事務將 0 號系統表空間中第 10 號頁面中偏移量為 100 處的值 1 改成 2，我們只需記錄一下："將第 0 號表空間的第 10 號頁面中偏移量為 100 處的值更新為 2"。

InnoDB 引擎的事務採用了WAL 技術（Write-Ahead Logging），這種技術的思想就是先寫日誌，再寫磁碟，只有日誌寫入成功，才算事務提交成功，這裡的日誌就是 redo log。當發生當機且資料未刷到磁碟的時候，可以透過 redo log 來恢復，保證 ACID 中的 D，這就是 redo log 的作用。

image-20231110193214624

redo log 的好處和特點

好處：

  • redo log 降低了刷盤頻率。
  • redo log 佔用的空間非常小。redo log 儲存表空間 ID、頁號、偏移量以及需要更新的值，所需的儲存空間是很小的，刷盤快。

特點：

  • redo log 是順序寫入磁碟的。
    • 在執行事務的過程中，每執行一條語句，就可能產生若干條 redo log，這些日誌是按照產生的順序寫入磁碟的，也就是使用順序 I/O，效率比隨機 I/O 快。
  • 事務執行過程中，redo log 不斷記錄。
    • redo log 跟 bin log 的區別，redo log 是儲存引擎層產生的，而 bin log 是資料庫層產生的。假設一個事務，對錶做 10 萬行的記錄插入，在這個過程中，一直不斷的往 redo log 順序記錄，而 bin log 不會記錄，直到這個事務提交，才會一次寫入到 bin log 檔案中（bin log 是記錄主從複製的檔案）。

redo log 的組成

redo log 可以簡單分為以下兩個部分：

  • 重做日誌緩衝（redo log buffer） ，儲存在記憶體中，是易失的。
  • 重做日誌檔案（redo log file），儲存在硬碟中，是持久的。

redo log buffer

在伺服器啟動時，就向作業系統申請了一大片稱之為redo log buffer的連續記憶體空間，翻譯成中文就是重做日誌緩衝區。這片記憶體空間被劃分成若干個連續的redo log block。一個 redo log block 佔用 512 位元組大小。

image-20231110194328602

引數設定：innodb_log_buffer_size。

  • redo log buffer 的大小，預設 16 MB，最大值是 4096 MB，最小值為 1 MB。

    mysql> SHOW VARIABLES LIKE '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

redo log file

redo log file 如圖所示，其中的ib_logfile0和ib_logfile1即為 redo log：

image-20231110194915124

redo log 的整體流程

以一個更新事務為例，redo log 的流轉過程，如下圖所示：

image-20231110195130060

  • 第 1 步：先將原始資料從磁碟中讀入記憶體中來，修改資料的記憶體複製。
  • 第 2 步：生成一條重做日誌，並寫入 redo log buffer，記錄的是資料被修改後的值。
  • 第 3 步：當事務 COMMIT 時，將 redo log buffer 中的內容重新整理到 redo log file，對 redo log file 採用追加寫的方式。
  • 第 4 步：定期將記憶體中修改的資料重新整理到磁碟中。

Write-Ahead Log：預先日誌持久化，在持久化一個資料頁之前，先將記憶體中相應的日誌頁持久化。

redo log 的刷盤策略

redo log 的寫入並不是直接寫入磁碟的，InnoDB 引擎會在寫 redo log 的時候先寫 redo log buffer，之後以一定的頻率刷到真正的 redo log file 中。這裡的一定頻率怎麼看待呢？這就是我們要說的刷盤策略。

image-20231110195627970

注意，redo log buffer 刷盤到 redo log file 的過程，並不是真正的刷到磁碟中去，只是刷入到檔案系統快取（page cache）中去（OS buffer，這是現代作業系統為了提高檔案寫入效率做的一個最佳化），真正的寫入會交給系統自己來決定（比如 page cache 足夠大了）。那麼對於 InnoDB 來說就存在一個問題，如果交給系統來同步，同樣如果系統當機，那麼資料也丟失了（雖然整個系統當機的機率還是比較小的）。

針對這種情況，InnoDB 給出innodb_flush_log_at_trx_commit 引數，該引數控制 COMMIT 提交事務時，如何將 redo log buffer 中的日誌重新整理到 redo log file 中。它支援三種策略：

  • 設定為 0：每次提交事務時，不會將 redo log buffer 中的日誌寫入 page cache，而是透過一個單獨的執行緒，每秒寫入 page cache 並呼叫系統的 fsync() 函式寫入磁碟的 redo log file。這種方式不是實時寫磁碟的， 而是每隔 1 秒寫一次日誌，如果系統崩潰，可能會丟失 1 秒的資料。

  • 設定為 1：預設值，每次提交事務時，都會將 redo log buffer 中的日誌寫入 page cache 中，並且會呼叫 fsync() 函式將日誌寫入 redo log file 中。這種方式雖然不會再崩潰時丟失資料，但是效能比較差。

    mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

  • 設定為 2：每次提交事務時，都只將 redo log buffer 中的日誌寫入 page cache 中，之後每隔 1 秒，透過 fsync() 函式將 page cache 中的資料寫入 redo log file 中。

write：刷盤，指的是 MySQL 從 buffer pool 中將內容寫到系統的 page cache 中，並沒有持久化到系統磁碟上。這個速度其實是很快的。

fsync：持久化到磁碟，指的是從系統的 page cache 中，將資料持久化到系統磁碟上。這個速度可以認為比較慢，而且也是 IOPS 升高的真正原因。

另外，InnoDB 儲存引擎有一個後臺執行緒，每隔 1 秒，就會把 redo log buffer 中的內容寫到檔案系統快取（page cache），然後呼叫刷盤操作。

image-20231110204145340

也就是說，一個沒有提交事務的 redo log 記錄，也可能會刷盤。因為在事務執行過程 redo log 記錄是會寫入 redo log buffer 中，這些 redo log 記錄會被後臺執行緒刷盤。

image-20231110204328011

除了後臺執行緒每秒 1 次的輪詢操作，還有一種情況，當 redo log buffer 佔用的空間即將達到 innodb_log_buffer_size 的一半的時候，後臺執行緒會主動刷盤。

不同刷盤策略演示

刷盤策略分析

1. innodb_flush_log_at_trx_commit = 1

image-20231110204709173

  • innodb_flush_log_at_trx_commit = 1 時，只要事務提交成功，都會主動同步刷盤，這個速度是很快的。最終 redo log 記錄就一定在硬碟裡，不會有任何資料丟失。
  • 如果事務執行期間 MySQL 掛了或當機，這部分日誌丟了，但是事務並沒有提交，所以日誌丟了也不會有損失。可以保證 ACID 的 D，資料絕對不會丟失，但是這種效率是最差的。
  • 建議使用預設值，雖然作業系統當機的機率理論小於資料庫當機的機率，但是一般既然使用了事務，那麼資料的安全相對來說更重要些。

2. innodb_flush_log_at_trx_commit = 2

image-20231110211552048

  • innodb_flush_log_at_trx_commit = 2 時，只要事務提交成功，redo log buffer 中的內容就會寫入檔案系統快取（page cache）。
  • 如果只是 MySQL 掛了不會有任何資料丟失，但是作業系統當機可能會有 1 秒資料的丟失，這種情況下無法滿足 ACID 中的 D。
  • 數值 2 是一種折中的做法，它的 I/O 效率理論是高於 1，低於 0 的。當進行調優時，為了降低 CPU 的使用率，可以從 1 降成 2，因為 O/S 出現故障的機率很小。

3. innodb_flush_log_at_trx_commit = 0

image-20231110212019323

  • innodb_flush_log_at_trx_commit = 0 時，master thread 中每 1 秒進行一次重做日誌的 fsync 操作，因此例項 crash，最多丟失 1 秒鐘內的事務。（master thread 是負責將緩衝池中的資料非同步重新整理到磁碟，保證資料的一致性）
  • 數值 0，是一種效率最高的做法，這種策略有丟失資料的風險，也無法保證 D。

一句話，0：延遲寫，延遲刷，1：實時寫，實時刷，2：實時寫，延遲刷。

示例

比較 innodb_flush_log_at_trx_commit 對事務的影響。準備資料：

mysql> USE atguigudb2;
Database changed

mysql> CREATE TABLE test_load(
    -> a INT,
    -> b CHAR(80)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE p_load(COUNT INT UNSIGNED)
    -> BEGIN
    -> DECLARE s INT UNSIGNED DEFAULT 1;
    -> DECLARE c CHAR(80)DEFAULT REPEAT('a',80);
    -> WHILE s<=COUNT DO
    -> INSERT INTO test_load SELECT NULL,c;
    -> COMMIT;
    -> SET s=s+1;
    -> END WHILE;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

innodb_flush_log_at_trx_commit = 1 時：

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> CALL p_load(30000);
Query OK, 0 rows affected (2 min 3.70 sec)

innodb_flush_log_at_trx_commit = 2 時：

mysql> TRUNCATE TABLE test_load;
Query OK, 0 rows affected (0.04 sec)

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> CALL p_load(30000);
Query OK, 0 rows affected (1 min 4.00 sec)

innodb_flush_log_at_trx_commit = 0 時：

mysql> TRUNCATE TABLE test_load;
Query OK, 0 rows affected (0.03 sec)

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> CALL p_load(30000);
Query OK, 0 rows affected (1 min 0.72 sec)

最終結果：

innodb_flush_logat_trx_commit 執行所用的時間
0 1 min 0.72 sec
1 2 min 3.70 sec
2 1 min 4.00 sec

針對上述儲存過程，為了提高事務的提交效能，應該在將 3 萬行記錄插入表後進行一次的 COMMIT 操作，而不是每插入一條記錄後就進行一次 COMMIT 操作，這樣做的好處是可以使事務方法在 ROLLBACK 時，回滾到事務最開始的確定狀態。

注意：雖然使用者可以透過設定引數 innodb_flush_log_at_trx_commit 為 0 或 2 來提高事務提交的效能，但需清楚，這種設定方法喪失了事務的 ACID 特性。

寫入 redo log buffer 過程

Mini-Transaction

MySQL 把對底層頁面中的一次原子訪問的過程，稱之為一個Mini-Transaction，簡稱 mtr。比如，向某個索引對應的 B+Tree 中，插入一條記錄的過程就是一個 Mini-Transaction。一個 mtr 可以包含一組 redo log，在進行崩潰恢復時這一組 redo log 作為一個不可分割的整體。

一個事務可以包含若干條語句，每一條語句其實是由若干個 mtr 組成，每一個 mtr 又可以包含若干條 redo log，畫個圖表示它們的關係就是這樣：

image-20231110213606186

  • 一個事務由多條 SQL 語句組成。
  • 一條 SQL 語句包含多個 mtr，因為一條 SQL 可能改變多條記錄。
  • 一個 mtr 對應多條 redo log，因為 redo log 存放的是物理級別的修改，當插入語句且頁分裂時，會出現大量比如 "A 頁 xxx、偏移量 yy 寫入了 zzz 資料"，"B 頁面 aaa、偏移量 bb 寫入了 ccc 資料" 這樣的記錄。

redo log 寫入 redo log buffer

向 redo log buffer 中寫入 redo log 的過程是順序的，也就是先往前邊的 block 中寫，當該 block 的空閒空間用完之後，再往下一個 block 中寫。當想往 redo log buffer 中寫入 redo log 時，第一個遇到的問題就是應該寫在哪個 block 的哪個偏移量處，所以 InnoDB 的設計者特意提供了一個稱之為buf_free的全域性變數，該變數指明後續寫入的 redo log 應該寫入到 redo log buffer 中的哪個位置，如圖所示：

image-20231110214215918

一個 mtr 執行過程中，可能產生若干條 redo log，這些 redo log 是一個不可分割的組，所以其實並不是每生成一條 redo log，就將其插入到 redo log buffer 中，而是每個 mtr 執行過程中產生的日誌先暫時存到一個地方，當該 mtr 結束的時候，將過程中產生的一組 redo log 再全部複製到 redo log buffer 中。假設有兩個名為 T1、T2 的事務，每個事務都包含 2 個 mtr，我們給這幾個 mtr 命名一下：

  • 事務 T1 的兩個 mtr 分別稱為 mtr_T1_1 和 mtr_T1_2。
  • 事務 T2 的兩個 mtr 分別稱為 mtr_T2_1 和 mtr_T2_2。

每個 mtr 都會產生一組 redo log，用示意圖來描述一下這些 mtr 產生的日誌情況：

image-20231110214607022

不同的事務可能是併發執行的，所以 T1、T2 之間的 mtr 可能是交替執行的。每當一個 mtr 執行完成時，伴隨該 mtr 生成的一組 redo log 就需要被複制到 redo log buffer 中，也就是說，不同事務的 mtr 可能是交替寫入 redo log buffer 的，我們畫個示意圖（為了美觀，把一個 mtr 中產生的所有的 redo log 當作一個整體來畫）：

image-20231110215030086

有的 mtr 產生的 redo log 量可能非常大，比如 mtr_t1_2 產生的 redo log 佔用空間比較大，佔用了 3 個 block 來儲存。

redo log block 的結構圖

一個 redo log block 是由日誌頭、日誌體、日誌尾組成。日誌頭佔用 12 位元組，日誌尾佔用 8 位元組，所以一個 block 真正能儲存的資料就是 512 - 12 - 8 = 492 位元組。

image-20231110215410008

真正的 redo log 都是儲存到佔用 496 位元組大小的 log block body 中，圖中的 log block header 和 log block trailer 儲存的是一些管理資訊。我們來看看這些所謂的管理資訊都有什麼。

image-20231110215536304

  • log block header的屬性分別如下：
    • LOG_BLOCK_HDR_NO ：log buffer 是由 log block 組成，在內部 log buffer 就好似一個陣列，因此 LOG_BLOCK_HDR_NO 用來標記這個陣列中的位置。其是遞增並且迴圈使用的，佔用 4 個位元組，但是由於第—位用來判新是否是 flush bit，所以最大的值為 2 GB。
    • LOG_BLOCK_HDR_DATA_LEN：表示 block 中已經使用了多少位元組，初始值為12，因為 log block body 從第 12 個位元組處開始。隨著往 block 中寫入的 redo log 越來越多，該屬性值也跟著增長。如果 log block body 已經被全部寫滿，那麼該屬性的值被設定為512。
    • LOG_BLOCK_FIRST_REC_GROUP ：一條 redo log 也可以稱之為一條 redo 日誌記錄（redo log record)，一個 mtr 會生產多條 redo 日誌記錄，這些 redo 日誌記錄被稱之為一個 redo 日誌記錄組（redo log record group）。LOG_BLOCK_FIRST_REC_GROUP 就代表該 block 中第一個 mtr 生成的 redo 日誌記錄組的偏移量（其實也就是這個 block 裡，第一個 mtr 生成的第一條 redo 日誌的偏移量）。如果該值的大小與 LOG_BLOCK_HDR_DATA_LEN 相同，則表示當前 log block 不包含新的日誌。
    • LOG_BLOCK_CHECKPOINT_NO：佔用 4 位元組，表示該 log block 最後被寫入時的checkpoint。
  • log block trailer的屬性如下：
    • LOG_BLOCK_CHECKSUN：表示 block 的校驗值，用於正確性校驗（其值和 LOG_BLOCK_HDR_NO 相同)，暫時不關心它。

redo log file

相關引數設定

  • innodb_log_group_home_dir ：指定 redo log 檔案組所在的路徑，預設值為./，表示在資料庫的資料目錄下。MySQL 的預設資料目錄（var/lib/mysql ）下預設有兩個名為ib_logfile0 和ib_logfile1 的檔案，log buffer 中的日誌，預設情況下就是重新整理到這兩個磁碟檔案中。此 redo log 檔案組位置可以修改。

    mysql> SHOW VARIABLES LIKE 'innodb_log_group_home_dir';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_group_home_dir | ./    |
+---------------------------+-------+
1 row in set (0.00 sec)

  • innodb_log_files_in_group：指明 redo log file 的個數，命名方式如 ib_logfile0，iblogfile1，…，iblogfilen。預設 2 個，最大 100 個。

    mysql> SHOW VARIABLES LIKE 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
1 row in set (0.00 sec)

  • innodb_flush_log_at_trx_commit：控制 redo log 重新整理到磁碟的策略，預設為1。

    mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.01 sec)

  • innodb_log_file_size：單個 redo log file 設定大小，預設值為48 MB 。最大值為 512 GB，注意最大值指的是整個 redo log 系列檔案之和，即（innodb_log_files_in_group * innodb_log_file_size）不能大於最大值 512 GB。

    mysql> SHOW VARIABLES LIKE 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.00 sec

    • 根據業務修改其大小，以便容納較大的事務。編輯 my.cnf 檔案並重啟資料庫生效，如下所示：

      [root@centos7-mysql-1 mysql]#vim /etc/my.cnf
innodb_log_file_size=200M

在資料庫例項更新比較頻繁的情況下，可以適當加大 redo log 組數和大小。但也不推薦 redo log 設定過大，在 MySQL 崩潰恢復時，會重新執行 redo log 中的記錄。

日誌檔案組

從上邊的描述中可以看到，磁碟上的 redo log file 不止一個，而是以一個日誌檔案組的形式出現的。這些檔案以ib_logfile[數字]（數字可以是0、1、2…）的形式進行命名，每個 redo log file 大小都是一樣的。

在將 redo log 寫入日誌檔案組時，是從ib_logfile0開始寫，如果ib_logfile0寫滿了，就接著ib_logfile1寫。同理，ib_logf1le1寫滿了就去寫ib_logfile2，依次類準。如果寫到最後一個檔案該咋辦？那就重新轉到 ib_logfile0 繼續寫，所以整個過程如下圖所示：

image-20231111095945175

總共的 redo log file 大小其實就是：innodb_log_file_size * innodb_log_files_in_group。

採用迴圈使用的方式向 redo log 檔案組裡寫資料的話，會導致後寫入的 redo log 覆蓋掉前邊寫的 redo log，基於此，InnoDB 的設計者提出了 checkpoint 的概念。

checkpoint

在整個日誌檔案組中還有兩個重要的屬性，分別是write pos、checkpoint。

  • write pos 是當前記錄的位置，一邊寫一邊後移。
  • checkpoint 是當前要擦除的位置，也是往後推移。

每次刷盤 redo log 記錄到日誌檔案組中，write pos 位置就會後移更新。每次 MySQL 載入日誌檔案組恢復資料時，會清空載入過的 redo log 記錄，並把 checkpoint 後移更新。write pos 和 checkpoint 之間的還空著的部分，可以用來寫入新的 redo log 記錄。

image-20231111102851434

如果 write pos 追上 checkpoint ，表示日誌檔案組滿了，這時候不能再寫入新的 redo log 記錄，MySQL 得停下來，清空一些記錄，把 checkpoint 推進一下。

image-20231111102951774

小結

InnoDB 的更新操作，採用的是 Write Ahead Log（預先日誌持久化）策略，即先寫日誌，再寫入磁碟：

image-20231111103139939

undo log

redo log 是事務永續性的保證，undo log 是事務原子性的保證。在事務中更新資料的前置操作，其實是要先寫入一個 undo log。

如何理解 undo log

事務需要保證原子性，也就是事務中的操作要麼全部完成，要麼什麼也不做。但有時候事務執行到一半會出現一些情況，比如：

  • 情況一：事務執行過程中可能遇到各種錯誤，比如伺服器本身的錯誤，作業系統錯誤 ，甚至是突然斷電導致的錯誤。
  • 情況二：程式設計師可以在事務執行過程中，手動輸入ROLLBACK 語句結束當前事務的執行。

以上情況出現，需要把資料改回原先的樣子，這個過程稱之為回滾 ，這樣就可以造成一個假象：這個事務看起來什麼都沒做，所以符合原子性要求。

每當我們要對一條記錄做改動時（這裡的改動可以指INSERT、DELETE、UPDATE ），都需要 "留一手"---> 把回滾時所需的東西記下來。比如：

  • 插入一條記錄時，至少要把這條記錄的主鍵值記下來，之後回滾的時候，只需要把這個主鍵值對應的記錄刪除就好了（對於每個 INSERT，InnoDB 儲存引擎會新增一個 DELETE）。

  • 刪除一條記錄時，至少要把這條記錄中的內容都記下來，之後回滾的時候，再把由這些內容組成的記錄插入到表中就好了（對於每個 DELETE，InnoDB 儲存引擎會新增一個 INSERT）。

  • 修改一條記錄時，至少要把修改這條記錄前的舊值都記錄下來，之後回滾的時候，再把這條記錄更新為舊值就好了（對於每個 UPDATE，InnoDB 儲存引擎會執行一個相反的 UPDATE，將修改前的行放回去）。

MySQL 把這些為了回滾而記錄的內容，稱之為撤銷日誌或者回滾日誌，即 undo log。

說明：

  • 由於查詢操作（SELECT）並不會修改任何使用者記錄，所以在查詢操作執行時，不需要記錄相應的 undo log。
  • undo log 會產生 redo log，也就是 undo log 的產生會伴隨著 redo log 的產生，這是因為 undo log 也需要永續性的保護。

undo log 的作用

回滾資料

使用者對 undo log 可能有誤解的認為：undo log 用於將資料庫物理地恢復到執行語句或事務之前的樣子。但事實並非如此，undo log 是邏輯日誌，只是將資料庫邏輯地恢復到原來的樣子。所有修改都被邏輯地取消了，但是資料結構和頁本身在回滾之後可能大不相同，比如新增的頁不會邏輯的進行刪除。

這是因為在多使用者併發系統中，可能會有數十、數百甚至數千個併發事務，資料庫的主要任務就是協調對資料記錄的併發訪問。比如，一個事務在修改當前一個頁中某幾條記錄，同時還有別的事務在對同一個頁中另幾條記錄進行修改。因此，不能將一個頁回滾到事務開始的樣子，因為這樣會影響其他事務正在進行的工作。

MVCC

undo log 的另一個作用是MVCC，即在 InnoDB 儲存引擎中，MVCC 的實現是透過 undo log 來完成的。當使用者讀取一行記錄時，若該記錄已經被其他事務佔用，當前事務可以透過 undo log 讀取之前的行版本資訊，以此實現非鎖定讀取。

undo log 的儲存結構

回滾段與 undo 頁

InnoDB 對 undo log 的管理採用段的方式，也就是 回滾段（rollback segment）。每個回滾段記錄了1024 個 undo log segment ，而在每個 undo log segment 中進行undo 頁的申請。

  • 在 InnoDB 1.1 版本之前（不包括 1.1 版本），只有一個 rollback segment ，因此，支援同時線上的事務限制為1024個，實際上這對絕大多數的應用來說都已經夠用。
  • 從 1.1 版本開始，InnoDB 支援最大 128 個rollback segment ，故其支援同時線上的事務限制，提高到了128 * 1024個。

雖然 InnoDB 1.1 版本支援了 128 個 rollback segment，但是這些 rollback segment 都儲存於共享表空間ibdata中。從 InnoDB 1.2 版本開始，可透過引數對 rollback segment 做進一步的設定。這些引數包括：

  • innodb_undo_directory：設定 rollback segment 檔案所在的路徑。這意味著，rollback segment 可以存放在共享表空間以外的位置，即可以設定為獨立表空間。該引數的預設值為 "./"，表示當前 InnoDB 儲存引擎的目錄。
  • innodb_undo_logs：設定 rollback segment 的個數，預設值為128。在 InnoDB 1.2 版本中，該引數用來替換之前版本的引數 innodb_rollback_segments。
  • innodb_undo_tablespaces：設定構成 rollback segment 檔案的數目，預設值為 2，這樣 rollback segment 可以較為平均地分佈在多個檔案中。設定該引數後，會在路徑 innodb_undo_directory 看到 undo 為字首的檔案，該檔案就代表 rollback segment 檔案。

undo log 相關引數一般很少改動。

undo 頁的重用

當開啟一個事務需要寫 undo log 的時候，就得先去 undo log segment 中找到一個空閒的位置，當有空位的時候，就去申請 undo 頁，在這個申請到的 undo 頁中進行 undo log 的寫入。我們知道 MySQL 預設一頁的大小是 16 KB。

為每一個事務分配一個頁，是非常浪費的（除非你的事務非常長)，假設你的應用的 TPS（每秒處理的事務數目）為 1000，那麼 1 秒就需要 1000 個頁，大概需要 16 MB 的儲存，1 分鐘大概需要 1 GB 的儲存。如果照這樣下去除非 MySQL 清理的非常勤快，否則隨著時間的推移，磁碟空間會增長的非常快，而且很多空間都是浪費的。

於是，undo 頁就被設計的可以重用了，當事務提交時，不會立刻刪除 undo 頁。因為重用，所以這個 undo 頁可能混雜著其他事務的 undo log。undo log 在 commit 後，會被放到一個連結串列中，然後判斷 undo 頁的使用空間是否小於 3 /4，如果小於 3/4的話，則表示當前的 undo 頁可以被重用，那麼它就不會被回收，其他事務的 undo log 可以記錄在當前 undo 頁的後面。由於 undo log 是離散的，所以清理對應的磁碟空間時，效率不高。

因為每一個事務分配一個頁，造成極大的浪費，所以要重用 ---> 因為重用，所以當前日誌的 undo 頁，可能會有其他事務的 undo log ---> 所以當前事務提交後，不能立即刪除 undo 頁，而是 undo log 放到連結串列中，嘗試重用 undo 頁。

回滾段與事務

  1. 每個事務只會使用一個回滾段，一個回滾段在同一時刻可能會服務於多個事務。

  2. 當一個事務開始的時候，會指定一個回滾段，在事務進行的過程中，當資料被修改時，原始的資料會被複制到回滾段。

  3. 在回滾段中，事務會不斷填充盤區，直到事務結束或所有的空間被用完。如果當前的盤區不夠用，事務會在段中請求擴充套件下一個盤區，如果所有已分配的盤區都被用完，事務會覆蓋最初的盤區，或者在回滾段允許的情況下擴充套件新的盤區來使用。

  4. 回滾段存在於 undo 表空間中，在資料庫中可以存在多個 undo 表空間，但同一時刻只能使用一個 undo 表空間。

    mysql> SHOW VARIABLES LIKE 'innodb_undo_tablespaces';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 2     |
+-------------------------+-------+
1 row in set (0.00 sec)

  5. 當事務提交時，InnoDB 儲存引擎會做以下兩件事情：

    • 將 undo log 放入列表中，以供之後的 purge 操作。
    • 判斷 undo log 所在的頁是否可以重用，若可以分配給下個事務使用。

回滾段中的資料分類

  • 未提交的回滾資料（uncommitted undo information）：該資料所關聯的事務並未提交，用於實現讀一致性，所以該資料不能被其他事務的資料覆蓋。

  • 已經提交但未過期的回滾資料（committed undo information）：該資料關聯的事務已經提交，但是仍受到 "undo retention" 引數的保持時間的影響。

  • 事務已經提交併過期的資料（expired undo information）：該資料關聯的事務已經提交，而且資料儲存時間已經超過 "undo retention" 引數指定的時間，屬於已經過期的資料。當回滾段滿了之後，會優先覆蓋 "事務已經提交併過期的資料"。

事務提交後並不能馬上刪除 undo log 及 undo log 所在的頁，這是因為可能還有其他事務需要透過 undo log 來得到行記錄之前的版本。因此，事務提交時將 undo log 放入一個連結串列中，是否可以最終刪除 undo log 及 undo log 所在頁，由purge執行緒來判斷。

undo log 的型別

在 InnoDB 儲存引擎中，undo log 分為：

  • insert undo log
    • insert undo log 是指在INSERT操作中產生的 undo log。因為 INSERT 操作的記錄，只對事務本身可見，對其他事務不可見（這是事務隔離性的要求），故該 undo log 可以在事務提交後直接刪除，不需要進行 purge 操作。
  • update undo log
    • update undo log 記錄的是對UPDATE和DELETE操作產生的 undo log，該 undo log 可能需要提供MVCC機制，因此不能在事務提交時就進行刪除。提交時放入 undo log 連結串列，等待 purge 執行緒進行最後的刪除。

undo log 的生命週期

簡要生成過程

以下是 undo + redo 事務的簡化過程。

假設有 2 個數值，分別為 A = 1 和 B = 2，然後將 A 修改為 3，B 修改為 4：

1. start transaction;
2．記錄A = 1到undo log;
3. update A = 3;
4. 記錄A = 3到redo log;
5．記錄B = 2到undo log;
6. update B = 4;
7. 記錄B = 4到redo log;
8．將redo log重新整理到磁碟;
9. commit;
  • 在 1 ~ 8 步驟的任意一步，系統當機，事務未提交，該事務就不會對磁碟上的資料做任何影響。
  • 如果在 8 ~ 9 之間當機，恢復之後可以選擇回滾，也可以選擇繼續完成事務提交，因為此時 redo log 已經持久化。
  • 若在 9 之後系統當機，記憶體對映中變更的資料還來不及刷回磁碟，那麼系統恢復之後，可以根據 redo log 把資料刷回磁碟。

只有 Buffer Pool 的流程：

image-20231111181508120

有了 redo log 和 undo log 之後：

image-20231111181600020

在更新 Buffer Pool 中的資料之前，需要先將該資料事務開始之前的狀態寫入 undo log 中。假設更新到一半出錯了，就可以透過 undo log 來回滾到事務開始前。

詳細生成過程

對於 InnoDB 引擎來說，每個行記錄除了記錄本身的資料之外，還有幾個隱藏的列：

image-20231111183414685

  • DB_ROW_ID：如果沒有為表顯式的定義主鍵，並且表中也沒有定義唯一索引，那麼 InnoDB 會自動為表新增一個 row_id 的隱藏列作為主鍵。
  • DB_TRX_ID：每個事務都會分配一個事務 ID，當對某條記錄發生變更時，就會將這個事務的事務 ID 寫入 trx_id 中。
  • DB_ROLL_PTR：回滾指標，本質上就是指句 undo log 的指標。

當執行 INSERT 時：

BEGIN;

INSERT INTO user (name) VALUES ("tom");

插入的資料都會生成一條insert undo log，並且資料的回滾指標會指向它。undo log 會記錄 undo log 的序號、插入主鍵的列和值。那麼在進行 rollback 的時候，透過主鍵直接把對應的資料刪除即可。

image-20231111183619474

當執行 UPDATE 時：

對於更新的操作會產生update undo log，並且會分更新主鍵的和不更新主鍵的。

假設現在執行：

UPDATE user SET name = "Sun" WHERE id = 1;

image-20231111183825711

這時會把老的記錄寫入新的 undo log，讓回滾指標指向新的 undo log，它的 undo no 是 1，並且新的 undo log 會指向老的 undo log，它的 undo no 是 0。

假設現在執行：

UPDATE user SET id = 2 WHERE id = 1;

image-20231111184027834

對於更新主鍵的操作，會先把原來的資料 deletemark 標識開啟，這時並沒有真正的刪除資料，真正的刪除會交給清理執行緒去判斷，然後在後面插入一條新的資料，新的資料也會產生 undo log，並且 undo log 的序號會遞增。

可以發現每次對資料的變更都會產生一個 undo log，當一條記錄被變更多次時，那麼就會產生多條 undo log，undo log 記錄的是變更前的日誌，並且每個 undo log 的序號是遞增的，那麼當要回滾的時候，按照序號依次向前推，就可以找到原始資料。

undo log 是如何回滾的

以上面的例子來說，假設執行 rollback，那麼對應的流程應該是這樣：

  1. 透過 undo no = 3 的日誌，把 id = 2 的資料刪除。
  2. 透過 undo no = 2 的日誌，把 id = 1 的資料的 deletemark 還原成 0。
  3. 透過 undo no = 1 的日誌，把 id = 1 的資料的 name 還原成 Tom。
  4. 透過 undo no = 0 的日誌，把 id = 1 的資料刪除。

undo log 的刪除

對於insert undo log：

  • 因為 INSERT 操作的記錄，只對事務本身可見，對其他事務不可見。故該 undo log 可以在事務提交後直接刪除，不需要進行 purge 操作。

對於update undo log：

  • 該 undo log 可能需要提供 MVCC 機制，因此不能在事務提交時就進行刪除。提交時放入 undo log 連結串列，等待 purge 執行緒進行最後的刪除。

purge 執行緒兩個主要作用是：清理 undo 頁和清除 page 裡面帶有 Delete_Bit 標識的資料行。在 InnoDB 中，事務中的 DELETE 操作，實際上並不是真正的刪除掉資料行，而是一種Delete Mark操作，在記錄上標識Delete_Bit，而不刪除記錄。這是一種 "假刪除"，只是做了個標記，真正的刪除工作需要後臺 purge 執行緒去完成。

小結

image-20231111215246241

  • redo log 是物理日誌，記錄的是資料頁的物理變化。
  • undo log 是邏輯日誌，對事務回滾時，只是將資料庫邏輯地恢復到原來的樣子。
  • undo log 不是 redo log 的逆過程。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql-advanced.md

相關文章