MySQL主键自增值为什么有“空洞”?

ywz888

发布于 2022-8-26 15:34

浏览

0收藏

• 1.场景准备
• 2.开始测试
• 3.问题分析
• 4.问题拓展
本文在测试 insert、insert ignore、replace into 三种数据插入方式的时候，发现插入数据的时候在表内存在带有“唯一特性”的值重复的情况下三种语句的处理方式。最终发现了MySQL主键自增值“空洞”了

1.场景准备
测试场景为MySQL 8.0：

• 主键重复场景
• 唯一键重复场景
1、建表，包含主键及唯一约束

 CREATE TABLE t1(
  id int(11) NOT NULL auto_increment,
  c1 varchar(64) DEFAULT NULL,
  c2 int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_c1 (c1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;1.
2.
3.
4.
5.
6.
7.

2、写入初始测试数据

insert into t1 (c1,c2) values ('a',1),('b',2),('c',3);

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
+----+------+------+
3 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

2.开始测试
insert into

# 测试主键重复
mysql> insert into t1 values (1,'aaa', 111);
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'

# 测试唯一键重复
mysql> insert into t1 (c1,c2) values('a', 4);
ERROR 1062 (23000): Duplicate entry 'a' for key 't1.uk_c1'1.
2.
3.
4.
5.
6.
7.

insert ignore into
insert方式插入数据在处理过程中发生主键传统等错误时候，语句会被终止，并告知错误的原因。而使用insert ignore的方式进行数据插入，则会忽略插入错误的行继续插入没有问题的行记录，最终以warning进行提示。

# 测试主键重复
mysql> insert ignore into t1 values (1,'aaa', 111);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't1.PRIMARY' |
+---------+------+------------------------------------------+
1 row in set (0.01 sec)

# 测试唯一键重复
mysql> insert ignore into t1 (c1,c2) values('a', 4);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1062 | Duplicate entry 'a' for key 't1.uk_c1' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

在测试过程中惊奇的发现测试表中的主键自增列发生了改变，经过之前的操作已经变成了7：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
# 可是表的行数据并没有增加
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
+----+------+------+
3 rows in set (0.00 sec)
# 新写入一条数据后，自增 id 变成 7
mysql> insert into t1 (c1,c2) values('d', 4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
|  7 | d    |    4 |
+----+------+------+
4 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.

replace into
最后，replace into的方式导致如果插入数据是原值的情况，然后主键冲突，就对该主键的内容进行替换，如果唯一键冲突，唯一值所在行就会删除，重新插入新的行，如果都不冲突则正常插入数据。

# 测试主键重复
mysql> replace into t1 values (1,'aaa', 111);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aaa  |  111 |
|  2 | b    |    2 |
|  3 | c    |    3 |
|  7 | d    |    4 |
+----+------+------+
4 rows in set (0.00 sec)

# 测试唯一键重复
mysql> replace into t1 (c1,c2) values('b', 4);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aaa  |  111 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
+----+------+------+
4 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

上文测试了三种插入数据的方式，可是测试过程中发现插入失败的时候，自增列的自增值居然变大了。

3.问题分析
为了更好地理解，首先让我们具体认识一下AUTO_INCREMENT属性在不同的存储引擎当中，其自增值的保存策略有所不同：

• MyISAM引擎的自增值是保存在数据文件中的。
• InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，将自增值的变更记录在了redo log中，当MySQL发生重启的时候依靠redo log恢复重启之前的自增值。在此前，现在该表的自增值是7，重启后又变成4了。
可是理解了这个并不能马上理解现在的这个问题，我们知道当数据进行数据插入的时候，如果插入的数据中自增列不指定其值的时候，该列就会以当前自增值作为其值，如果指定其值就会插入指定的值，当然也有满足唯一的原则，同时插入指定值大于自增值时，自增值也会随之改变。而自增值使用的算法是以auto_increment_offset参数决定开始，以auto_increment_increment决定步长来实现的,默认情况都是1：

mysql> select @@auto_increment_offset;
+-------------------------+
| @@auto_increment_offset |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

那么，为什么会出现插入数据未成功，自增值却变大了的情况呢？原因很简单，用插入数据的流程来进行分析：

MySQL主键自增值为什么有“空洞”?-鸿蒙开发者社区

因为自增值的保存是在插入数据真正执行前完成的，因此就会出现这种问题了。

这个时候有人就会想了，可以把AUTO_INCREMENT值改回去吗？简单测试一下：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
+----+------+------+
4 rows in set (0.00 sec)
# 自增值修改为15
mysql> alter table t1 auto_increment = 15;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 修改成功
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
# 未插入任何值，修改回去，修改成功
mysql> alter table t1 auto_increment = 9;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
# 修改回自增中间的值
mysql> alter table t1 auto_increment = 5;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 不会报错但无法修改
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.

显然，如果自增值往大的方向修改是没有问题的，但如果往小的修改就要看目前数据库插入的值是否会将修改后的自增值“卡”在中间，如果出现这种情况是没办法改回去的，原因显而易见，自增属性与主键配套使用，如果现在表里id=4和id=6之间差了个5的值，将自增值改回5，当插入数据时，自增值就会插入5的值并且把自增值加1，问题就出现了，此时自增值再进行插入就违背了唯一的原则了

4.问题拓展
在生产环境中还存在很多类似的问题，如：

# 目前的插入值为8，自增值为9
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
+----+------+------+
4 rows in set (0.00 sec)
# 插入数据相当于(9,'t1', 1)
mysql> insert into t1 values (null,'t1', 1);
Query OK, 1 row affected (0.00 sec)
# 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 插入数据相当于(10,'t2', 2)
mysql> insert into t1 values (null,'t2', 2);
Query OK, 1 row affected (0.00 sec)
# 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
# 插入数据相当于(11,'t3', 3)
mysql> insert into t1 values (null,'t3', 3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
|  9 | t1   |    1 |
| 11 | t3   |    3 |
+----+------+------+
6 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.

在插入过程中，开启了一个事务，在插入的时候发生了事务的回滚，当回滚后再次插入数据，发现自增值又从出现了“空洞”，那么问题又来了，为什么在插入数据的时候发生了回滚，数据回滚了，自增值却没有回滚呢？为了更直观，继续测试，假设有两个事务。

测试前数据：

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
|  9 | t1   |    1 |
| 11 | t3   |    3 |
| 13 | t4   |    3 |
+----+------+------+
7 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

进行测试：

MySQL主键自增值为什么有“空洞”?-鸿蒙开发者社区

测试后数据：

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
|  9 | t1   |    1 |
| 11 | t3   |    3 |
| 13 | t4   |    3 |
| 15 | s2   |    2 |
+----+------+------+
8 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

发现还是“空洞”了，而且此时答案也十分清楚了，在不同事务在进行写入操作的时候申请自增值，为了避免两个事务申请到相同的自增值，所以需要对其加锁，按照一定顺序进行申请自增值。根据前面的例子：

• 首先两个session都开启了事务，session1前的是id=14的自增值，session2则申请到id=15的自增值
• 接着当session2插入成功后提交了事务，而此时，session1插入成功或出现插入失败时进行了事务回滚
此时就出现了前面说到的问题了，没办法回滚，回滚就会出现自增值“卡”在中间的情况了，以后有机会再继续聊聊自增锁的问题。

本文转载自公共号GreatSQL社区。

分类

其他

标签

标签写个mysql

已于2022-8-26 15:35:29修改

51CTO

51CTO博客

51CTO学堂

MySQL主键自增值为什么有“空洞”?

订阅鸿蒙技术特刊，精选内容抢先看