MySQL 8.0特性-自增變數的持久化

kahnyao發表於2024-11-13
MySql變數持久化

MySQL 8.0特性-自增變數的持久化

在MySQL 8.0之前，自增主鍵AUTO_INCREMENT的值如果大於max(primary key)+1，在MySQL重啟後，會重置AUTO_INCREMENT=max(primary key)+1，這種現象在某些情況下會導致業務主鍵衝突或者其他難以發現的問題。 下面透過案例來對比不同的版本中自增變數是否持久化。

MySQL5.7測試

在MySQL 5.7版本中，測試步驟如下： 建立的資料表中包含自增主鍵的id欄位，語句如下：

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);
-- 插入4個空值，執行如下：
INSERT INTO test1
VALUES(0),(0),(0),(0);

-- 查詢資料表test1中的資料，結果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

-- 刪除id為4的記錄，語句如下：
DELETE FROM test1 WHERE id = 4;

-- 再次插入一個空值，語句如下：
INSERT INTO test1 VALUES(0);

-- 查詢此時資料表test1中的資料，結果如下：
mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0.00 sec)
-- 從結果可以看出，雖然刪除了id為4的記錄，但是再次插入空值時，並沒有重用被刪除的4，而是分配了5。
-- 刪除id為5的記錄
DELETE FROM test1 where id=5;

重啟資料庫

service mysql stop
service mysql start

繼續插入空值，然後再次查詢資料表test1中的資料，結果如下：

mysql> INSERT INTO test1 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

從結果可以看出，新插入的0值分配的是4，按照重啟前的操作邏輯，此處應該分配6。出現上述結果的主要原因是自增主鍵沒有持久化。 在MySQL 5.7系統中，對於自增主鍵的分配規則，是由InnoDB資料字典內部一個 計數器 來決定的，而該計數器只在 記憶體中維護 ，並不會持久化到磁碟中。當資料庫重啟時，該計數器會被初始化。

MySQL 8.0測試

上述測試步驟最後一步的結果如下：

mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+
4 rows in set (0.00 sec)

從結果可以看出，自增變數已經持久化了。

MySQL 8.0將自增主鍵的計數器持久化到 重做日誌中。每次計數器發生改變，都會將其寫入重做日誌中。如果資料庫重啟，InnoDB會根據重做日誌中的資訊來初始化計數器的記憶體值。

相關文章