MySQL自增变量的持久化（附带实例）

在 MySQL 8.0 之前，自增主键 AUTO_INCREMENT 的值如果大于 max(primary key)+1，在 MySQL 重启后，会重置 AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。在 MySQL 9.0 中，对自增主键 AUTO_INCREMENT 进行了持久化。

下面通过案例来对比不同版本中自增变量是否持久化。

在 MySQL 5.7 版本中，测试步骤如下：
1) 创建数据表，其中包含自增主键的id字段，SQL语句如下：

mysql> CREATE TABLE test1(id int auto_increment primary key);
Query OK, 0 rows affected (0.08 sec)

2) 插入 4 个空值，SQL 语句如下：

mysql> INSERT INTO test1 values(0),(0),(0),(0);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

3) 查询数据表 test1 中的数据，结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

4) 删除 id 为 4 的记录，SQL 语句如下：

mysql> DELETE FROM test1 where id=4;
Query OK, 1 row affected (0.04 sec)

5) 再次插入一个空值，SQL 语句如下：

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

6) 查询此时数据表 test1 中的数据，结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
+----+

从结果中可以看出，虽然删除了 id 为 4 的记录，但是再次插入空值时，并没有重用被删除的 4，而是分配了 5。

7) 删除 id 为 5 的记录，结果如下：

mysql> DELETE FROM test1 where id=5;
Query OK, 1 row affected (0.04 sec)

8) 重 class="mysql"启数据库，重新插入一个空值，SQL 语句如下：

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

9) 再次查询数据表 test1 中的数据，结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

从结果中可以看出，新插入的 0 值分配的是 4，但是按照重启前的操作逻辑，此处应该分配 6。出现上述结果的主要原因是自增主键没有持久化。

在 MySQL 5.7 中，对于自增主键的分配规则是由InnoDB数据字典内部的一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会通过下面这种方式初始化。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

在 MySQL 9.0 版本中，执行与 MySQL 5.7 版本中相同的步骤，第 9 步的结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  6 |
+----+

从结果中可以看出，自增变量已经持久化了。

MySQL 9.0 将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB 会根据重做日志中的信息来初始化计数器的内存值。为了尽量减小对系统性能的影响，计数器写入重做日志时，并不会马上刷新数据库系统。