Differences between MySQL AUTO_INCREMENT 5.7 VS 8.0 (二)

by

几年前整理过一篇<Differences between MySQL AUTO_INCREMENT 5.7 VS 8.0>，记录了两个版本对于auto_increment列存储的差异，提到mysql 5.7是auto_increment存在内存，而8.0后是增加了持久化到基表，这里再记录一些2者在重启后的的差异及影响。

一、重启后AUTO_INCREMENT

重启后mysql 5.7的形为是人从表里现有的最大ID+1 赋值AUTO_INCREMENT,而mysql 8是从字典基表加载。那就存在一个问题，如下

insert into t ()   --auto_inc:1
insert into t()  -- auto_inc2:
delete from t where id=2;

restart mysql

这种情况重启mysql后，2个版本auto_increment的值就不再相同。

二、参数innodb_autoinc_lock_mode

-- mysql 5.7

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

-- mysql 8
mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 2     |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

Consecutive 模式：自增值在语句结束后才分配，保证自增序列连续，但并发性能较差

Interleaved 模式：自增值提前分配，支持高并发，但可能导致自增序列出现间隙

三、检查当前auto_increment值

-- mysql 5.7

mysql> CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (name) VALUES ('row4');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
|  1 | row4 |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT table_schema,AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME='t1';
+--------------+----------------+
| table_schema | AUTO_INCREMENT |
+--------------+----------------+
| anbob        |              2 |
+--------------+----------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (name) VALUES ('row5');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT table_schema,AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME='t1';
+--------------+----------------+
| table_schema | AUTO_INCREMENT |
+--------------+----------------+
| anbob        |              3 |
+--------------+----------------+
1 row in set (0.00 sec)

-- mysql 8.0

mysql> CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (name) VALUES ('row4');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT table_schema,AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME='t1';
+--------------+----------------+
| TABLE_SCHEMA | AUTO_INCREMENT |
+--------------+----------------+
| anbob        |           NULL |
| mysql        |              2 |
+--------------+----------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO t1 (name) VALUES ('row5');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT table_schema,AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME='t1';
+--------------+----------------+
| TABLE_SCHEMA | AUTO_INCREMENT |
+--------------+----------------+
| anbob        |           NULL |
| mysql        |              2 |
+--------------+----------------+

mysql> CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 (name) VALUES ('row4');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT table_schema,AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME='t1';
+--------------+----------------+
| TABLE_SCHEMA | AUTO_INCREMENT |
+--------------+----------------+
| anbob        |           NULL |
| mysql        |              2 |
+--------------+----------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO t1 (name) VALUES ('row5');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT table_schema,AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME='t1';
+--------------+----------------+
| TABLE_SCHEMA | AUTO_INCREMENT |
+--------------+----------------+
| anbob        |           NULL |
| mysql        |              2 |
+--------------+----------------+

mysql> update t1 set id=100 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+-----+------+
| id  | name |
+-----+------+
|   1 | row4 |
| 100 | row5 |
+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT table_schema,AUTO_INCREMENT FROM information_schema.TABLES WHERE  TABLE_NAME='t1';
+--------------+----------------+
| TABLE_SCHEMA | AUTO_INCREMENT |
+--------------+----------------+
| anbob        |           NULL |
| mysql        |              2 |
+--------------+----------------+
2 rows in set (0.00 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Note: MYSQL 8的information_schema.TABLES.AUTO_INCREMENT的值刷新并不即时，可能是来自disk， 数据更新在memory， 刷新有延迟，但不确认更新时间, 可以使用show create table查看。

结论

维度MySQL 5.7MySQL 8.0
重启后 Auto_inc2（MAX+1=2）3（持久化恢复）
重启后新插入 id2（复用空洞）3（不复用）
计数器存储位置仅内存内存 + 系统表持久化
innodb_autoinc_persist不支持支持（默认 ON）
GAP复用策略重启后复用持久化序列优先，不复用
重启恢复来源扫描表数据持久化系统表
information_schema 实时性实时更新存在延迟
lock_mode 默认值1（consecutive）2（interleaved）

重启后获取auto_increment方式的影响

方面MySQL 5.7MySQL 8.0
重启恢复速度慢（需扫描表）快（从系统表读取）
大数据表重启可能很慢仍然很快
表数据损坏时可能丢失自增值可以恢复
并发性能较差（consecutive 模式）好（interleaved 模式）
自增序列连续性较好（consecutive 模式）可能出现间隙（interleaved 模式）

Leave a Comment