MySQL主键自增值为什么出现"空洞"的场景

  • 发表于：
  • 分类： 网络资源
  • 浏览：浏览(44)

技术社群的这篇文章《MySQL主键自增值为什么有“空洞”?》，对MySQL主键自增值"空洞"的场景进行了介绍，可以了解下。

本文在测试 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;

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)

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'

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)

在测试过程中惊奇的发现测试表中的主键自增列发生了改变，经过之前的操作已经变成了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)

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)

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

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)

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

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

这个时候有人就会想了，可以把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)

显然，如果自增值往大的方向修改是没有问题的，但如果往小的修改就要看目前数据库插入的值是否会将修改后的自增值“卡”在中间，如果出现这种情况是没办法改回去的，原因显而易见，自增属性与主键配套使用，如果现在表里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)

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

测试前数据，

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)

进行测试，

session1session2
begin;
insert into t1 values (null,'s1', 1);begin;

insert into t1 values (null,'s2', 2);

commit;
rollback;

测试后数据，

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)

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

  • 首先两个session都开启了事务，session1前的是id=14的自增值，session2则申请到id=15的自增值
  • 接着当session2插入成功后提交了事务，而此时，session1插入成功或出现插入失败时进行了事务回滚

此时就出现了前面说到的问题了，没办法回滚，回滚就会出现自增值“卡”在中间的情况了，以后有机会再继续聊聊自增锁的问题。


如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，




近期更新的文章：
《show effective grants》
《你知道台风的名称是怎么定义的？》
《搭建知识体系的借鉴和了解》
《MySQL全新版本模型》
《MySQL中drop和delete删用户场景》
《Gdevops北京站参会思考》

近期的热文：
《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》

文章分类和索引：
《公众号1200篇文章分类和索引》


免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论