关于mysql:MySQL-8-和-MySQL-57-在自增计数上的区别

MySQL 8 和 MySQL 5.7 在自增计数上的区别

作者：Arunjith Aravindan

本文起源：Percona 博客，爱可生开源社区翻译。

本文约 900 字，预计浏览须要 2 分钟。

Auto-Increment

自增（Auto-Increment）计数性能能够为主键列生成惟一值，这是数据库的一种设计。与 MySQL 5.7 相比，MySQL 8 为自增性能做了一项重要的降级。这个降级能够确保自增计数器的最大值在服务器重启后放弃不变，从而为数据一致性和可靠性提供了更好的保障。在本文中，咱们将比照 MySQL 5.7 和 MySQL 8 的不同之处，并提供理论示例来展现两者的区别。

MySQL 5.7 的自增

在 MySQL 5.7 中，主动增计数器的工作机制如下：当向蕴含自增列的表中插入新的一行数据时，计数器会主动加 1，生成的数值会作为插入行的主键应用。这个计数器值仅保留在内存中，在服务器重启后无奈长久化。因而，如果服务器解体或重启，计数器可能会重置为一个较低的值。

MySQL 8 的自增长久化

随着 MySQL 8 的公布，自增计数器机制有了显著改良。在 MySQL 8 中，自增计数器的最大值当初能够在服务器重启后长久化。这意味着，即便服务器重启，自增计数器也会从上次完结的中央复原，以确保自增主键的值放弃间断。

示例比照

让咱们用一个简略的例子来阐明 MySQL 5.7 和 MySQL 8 在长久自增计数器方面的区别。咱们将创立一个名为 users 的表，用于存储用户信息。

在 MySQL 5.7 中建表。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.42-46 |
+-----------+
 
mysql> CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
Query OK, 0 rows affected (0.02 sec)

在表中插入三条数据，能够查看到。

mysql> INSERT INTO users (username) VALUES ('user1');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO users (username) VALUES ('user2');
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO users (username) VALUES ('user3');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  3 | user3    |
+----+----------+
3 rows in set (0.00 sec)

咱们持续删除一条记录并插入一条新记录。

mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)
 
mysql> INSERT INTO users (username) VALUES ('user4');
Query OK, 1 row affected (0.01 sec)

删除 ID 为 3 的记录和插入新记录后，与预期统一，咱们察看到新记录的 ID 为 4。

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  4 | user4    |
+----+----------+
3 rows in set (0.00 sec)

当初，咱们从 users 表中删除最初一条记录（ID=4），重启服务器，并检查表内容。

mysql> delete from users where id=4;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)
 
service mysql restart
 
mysql> select * from users;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: db1
 
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.01 sec)

表中只剩下两条记录。咱们插入第五条记录，判断它是否采纳 ID 5，还是回退为 ID 3。

mysql> INSERT INTO users (username) VALUES ('user5');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  3 | user5    |
+----+----------+
3 rows in set (0.00 sec)

因而，在 MySQL 5.7 中，重启会导致主动增长计数器重置为较低的值，从而使新的记录插入时采纳 ID 3。

MySQL 8 的解决方案

MySQL 8 解决了在服务器重启时 InnoDB 存储引擎呈现的自增计数器失落的问题。这项加强能够确保自增计数器的值在服务器重启后长久化，从而保障主键生成的一致性。

在 MySQL 8 中建表。

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.33-0ubuntu0.22.04.2 |
+-------------------------+
1 row in set (0.00 sec)
 
mysql> CREATE TABLE users (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     username VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

在表中插入三条数据，能够查看到。

mysql> INSERT INTO users (username) VALUES ('user1');
Query OK, 1 row affected (0.07 sec)
 
mysql> INSERT INTO users (username) VALUES ('user2');
Query OK, 1 row affected (0.02 sec)
 
mysql> INSERT INTO users (username) VALUES ('user3');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  3 | user3    |
+----+----------+
3 rows in set (0.00 sec)

接下来，删除一条并插入一条。

mysql> delete from users where id=3;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)
 
mysql> INSERT INTO users (username) VALUES ('user4');
Query OK, 1 row affected (0.01 sec)

删除 ID 为 3 的记录和插入新记录采纳 ID 为 4。

mysql>  select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  4 | user4    |
+----+----------+
3 rows in set (0.00 sec)

删除最初一条记录（ID=4）后，重启服务器并查看表。

mysql> delete from users where id=4;
Query OK, 1 row affected (0.01 sec)
 
mysql>  select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.00 sec)
 
service mysql restart
 
mysql> select * from users;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: db1
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
+----+----------+
2 rows in set (0.02 sec)

重启后，users 表中只保留两条记录。在 MySQL 8 中，插入新记录时，如预期那样采纳 ID=5。

mysql> INSERT INTO users (username) VALUES ('user5');
Query OK, 1 row affected (0.01 sec)
 
mysql>  select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  5 | user5    |
+----+----------+
3 rows in set (0.00 sec)

总结

MySQL 8 之前版本中的 InnoDB 存储引擎报告的自增计数器问题可能会导致困惑和数据不统一，特地是在服务器重启期间。计数器的值可能失落，导致主动生成的主键值不匹配。MySQL 8 通过保障自增计数器在服务器重启之间长久化来解决这个问题。

通过降级到 MySQL 8，开发者能够利用这个性能创立更加松软的应用程序，能够治理不同的故障状况而不影响数据完整性。

https://www.percona.com/blog/auto-increment-counter-persisten…