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

12次阅读

共计 4558 个字符,预计需要花费 12 分钟才能阅读完成。

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…

正文完
 0