MySQL 8.0 新特性之自增变量持久化

MySQL 8.0 新特性之自增变量持久化

MySQL 8.0 中支持自增变量持久化，实际也是解决之前版本中存在的自增主键重启重置的 BUG 问题（ BUG #199：Innodb autoincrement stats los on restart） 。

MySQL 8.0 开始，当前最大的自增计数器每当发生变化，值会被写入 redo log 中，并在每个检查点时保存在 engine-private system table 中，对 AUTO_INCREMENT 值进行持久化，MySQL 重启后，该值也不会改变。

MySQL 5.7 vs 8.0 测试对比

MySQL 5.7

# MySQL 5.7
mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |   11 |   22 |
|  2 |    2 |   22 |   33 |
|  3 |    2 |   22 |   25 |
|  4 |    2 |   11 |   22 |
+----+------+------+------+
4 rows in set (0.01 sec)# 对应自增值（AUTO_INCREMENT）为 5
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,`c3` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)# 修改主键值
mysql> update t2 set id=5 where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |   11 |   22 |
|  2 |    2 |   22 |   33 |
|  3 |    2 |   22 |   25 |
|  5 |    2 |   11 |   22 |
+----+------+------+------+
4 rows in set (0.00 sec)# 自增值（AUTO_INCREMENT）未发生变化
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,`c3` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)# 插入数据，此时报错“主键冲突”
mysql> insert into t2 (c1,c2,c3) values (3,11,12);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'# 再次查看自增值（AUTO_INCREMENT）已变为 6
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,`c3` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)# 测试再次插入数据，此时正常
mysql> insert into t2 (c1,c2,c3) values (3,11,12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |   11 |   22 |
|  2 |    2 |   22 |   33 |
|  3 |    2 |   22 |   25 |
|  5 |    2 |   11 |   22 |
|  6 |    3 |   11 |   12 |
+----+------+------+------+
5 rows in set (0.00 sec)# 自增值（AUTO_INCREMENT）此时变为 7
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,`c3` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

MySQL 8.0

# MySQL 8.0
mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |   11 |   22 |
|  2 |    2 |   22 |   33 |
|  3 |    2 |   22 |   25 |
|  4 |    2 |   11 |   22 |
+----+------+------+------+
4 rows in set (0.01 sec)# 对应自增值（AUTO_INCREMENT）为 5
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int NOT NULL AUTO_INCREMENT,`c1` int DEFAULT NULL,`c2` int DEFAULT NULL,`c3` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)# 修改主键值
mysql> update t2 set id=5 where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |   11 |   22 |
|  2 |    2 |   22 |   33 |
|  3 |    2 |   22 |   25 |
|  5 |    2 |   11 |   22 |
+----+------+------+------+
4 rows in set (0.00 sec)# 查看自增值（AUTO_INCREMENT）已变为 6
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int NOT NULL AUTO_INCREMENT,`c1` int DEFAULT NULL,`c2` int DEFAULT NULL,`c3` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)# 再次修改主键值
mysql> update t2 set id=8 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |   11 |   22 |
|  2 |    2 |   22 |   33 |
|  3 |    2 |   22 |   25 |
|  8 |    2 |   11 |   22 |
+----+------+------+------+
4 rows in set (0.00 sec)# 查看自增值（AUTO_INCREMENT）已变为 9
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int NOT NULL AUTO_INCREMENT,`c1` int DEFAULT NULL,`c2` int DEFAULT NULL,`c3` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)# 插入数据正常
mysql> insert into t2 (c1,c2,c3) values (3,11,12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    1 |   11 |   22 |
|  2 |    2 |   22 |   33 |
|  3 |    2 |   22 |   25 |
|  8 |    2 |   11 |   22 |
|  9 |    3 |   11 |   12 |
+----+------+------+------+
5 rows in set (0.00 sec)# 查看自增值（AUTO_INCREMENT）正常变为 10
mysql> show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE `t2` (`id` int NOT NULL AUTO_INCREMENT,`c1` int DEFAULT NULL,`c2` int DEFAULT NULL,`c3` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

参考资料

【1】AUTO_INCREMENT Handling in InnoDB