This action will force synchronization from SnailClimb/JavaGuide, which will overwrite any changes that you have made since you forked the repository, and can not be recovered!!!
Synchronous operation will process in the background and will refresh the page when finishing processing. Please be patient.
title | category | tag | ||
---|---|---|---|---|
MySQL自增主键一定是连续的吗 |
数据库 |
|
作者:飞天小牛肉
众所周知,自增主键可以让聚集索引尽量地保持递增顺序插入,避免了随机查询,从而提高了查询效率。
但实际上,MySQL 的自增主键并不能保证一定是连续递增的。
下面举个例子来看下,如下所示创建一张表:
使用 insert into test_pk values(null, 1, 1)
插入一行数据,再执行 show create table
命令来看一下表的结构定义:
上述表的结构定义存放在后缀名为 .frm
的本地文件中,在 MySQL 安装目录下的 data 文件夹下可以找到这个 .frm
文件:
从上述表结构可以看到,表定义里面出现了一个 AUTO_INCREMENT=2
,表示下一次插入数据时,如果需要自动生成自增值,会生成 id = 2。
但需要注意的是,自增值并不会保存在这个表结构也就是 .frm
文件中,不同的引擎对于自增值的保存策略不同:
1)MyISAM 引擎的自增值保存在数据文件中
2)InnoDB 引擎的自增值,其实是保存在了内存里,并没有持久化。第一次打开表的时候,都会去找自增值的最大值 max(id)
,然后将 max(id)+1
作为这个表当前的自增值。
举个例子:我们现在表里当前数据行里最大的 id 是 1,AUTO_INCREMENT=2,对吧。这时候,我们删除 id=1 的行,AUTO_INCREMENT 还是 2。
但如果马上重启 MySQL 实例,重启后这个表的 AUTO_INCREMENT 就会变成 1。 也就是说,MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
以上,是在我本地 MySQL 5.x 版本的实验,实际上,到了 MySQL 8.0 版本后,自增值的变更记录被放在了 redo log 中,提供了自增值持久化的能力 ,也就是实现了“如果发生重启,表的自增值可以根据 redo log 恢复为 MySQL 重启前的值”
也就是说对于上面这个例子来说,重启实例后这个表的 AUTO_INCREMENT 仍然是 2。
理解了 MySQL 自增值到底保存在哪里以后,我们再来看看自增值的修改机制,并以此引出第一种自增值不连续的场景。
在 MySQL 里面,如果字段 id 被定义为 AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下:
根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设某次要插入的值是 insert_num
,当前的自增值是 autoIncrement_num
:
insert_num < autoIncrement_num
,那么这个表的自增值不变insert_num >= autoIncrement_num
,就需要把当前自增值修改为新的自增值也就是说,如果插入的 id 是 100,当前的自增值是 90,insert_num >= autoIncrement_num
,那么自增值就会被修改为新的自增值即 101
一定是这样吗?
非也~
了解过分布式 id 的小伙伴一定知道,为了避免两个库生成的主键发生冲突,我们可以让一个库的自增 id 都是奇数,另一个库的自增 id 都是偶数
这个奇数偶数其实是通过 auto_increment_offset
和 auto_increment_increment
这两个参数来决定的,这俩分别用来表示自增的初始值和步长,默认值都是 1。
所以,上面的例子中生成新的自增值的步骤实际是这样的:从 auto_increment_offset
开始,以 auto_increment_increment
为步长,持续叠加,直到找到第一个大于 100 的值,作为新的自增值。
所以,这种情况下,自增值可能会是 102,103 等等之类的,就会导致不连续的主键 id。
更遗憾的是,即使在自增初始值和步长这两个参数都设置为 1 的时候,自增主键 id 也不一定能保证主键是连续的
举个例子,我们现在往表里插入一条 (null,1,1) 的记录,生成的主键是 1,AUTO_INCREMENT= 2,对吧
这时我再执行一条插入 (null,1,1)
的命令,很显然会报错 Duplicate entry
,因为我们设置了一个唯一索引字段 a
:
但是,你会惊奇的发现,虽然插入失败了,但自增值仍然从 2 增加到了 3!
这是为啥?
我们来分析下这个 insert 语句的执行流程:
test_pk
当前的自增值 2;可以看到,自增值修改的这个操作,是在真正执行插入数据的操作之前。
这个语句真正执行的时候,因为碰到唯一键 a 冲突,所以 id = 2 这一行并没有插入成功,但也没有将自增值再改回去。所以,在这之后,再插入新的数据行时,拿到的自增 id 就是 3。也就是说,出现了自增主键不连续的情况。
至此,我们已经罗列了两种自增主键不连续的情况:
除此之外,事务回滚也会导致这种情况
我们现在表里有一行 (1,1,1)
的记录,AUTO_INCREMENT = 3:
我们先插入一行数据 (null, 2, 2)
,也就是 (3, 2, 2) 嘛,并且 AUTO_INCREMENT 变为 4:
再去执行这样一段 SQL:
虽然我们插入了一条 (null, 3, 3) 记录,但是使用 rollback 进行回滚了,所以数据库中是没有这条记录的:
在这种事务回滚的情况下,自增值并没有同样发生回滚!如下图所示,自增值仍然固执地从 4 增加到了 5:
所以这时候我们再去插入一条数据(null, 3, 3)的时候,主键 id 就会被自动赋为 5
了:
那么,为什么在出现唯一键冲突或者回滚的时候,MySQL 没有把表的自增值改回去呢?回退回去的话不就不会发生自增 id 不连续了吗?
事实上,这么做的主要原因是为了提高性能。
我们直接用反证法来验证:假设 MySQL 在事务回滚的时候会把自增值改回去,会发生什么?
现在有两个并行执行的事务 A 和 B,在申请自增值的时候,为了避免两个事务申请到相同的自增 id,肯定要加锁,然后顺序申请,对吧。
而为了解决这个主键冲突,有两种方法:
很显然,上述两个方法的成本都比较高,会导致性能问题。而究其原因呢,是我们假设的这个 “允许自增 id 回退”。
因此,InnoDB 放弃了这个设计,语句执行失败也不回退自增 id。也正是因为这样,所以才只保证了自增 id 是递增的,但不保证是连续的。
综上,已经分析了三种自增值不连续的场景,还有第四种场景:批量插入数据。
对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:
注意,这里说的批量插入数据,不是在普通的 insert 语句里面包含多个 value 值!!!,因为这类语句在申请自增 id 的时候,是可以精确计算出需要多少个 id 的,然后一次性申请,申请完成后锁就可以释放了。
而对于 insert … select
、replace …… select 和 load data 这种类型的语句来说,MySQL 并不知道到底需要申请多少 id,所以就采用了这种批量申请的策略,毕竟一个一个申请的话实在太慢了。
举个例子,假设我们现在这个表有下面这些数据:
我们创建一个和当前表 test_pk
有相同结构定义的表 test_pk2
:
然后使用 insert...select
往 teset_pk2
表中批量插入数据:
可以看到,成功导入了数据。
再来看下 test_pk2
的自增值是多少:
如上分析,是 8 而不是 6
具体来说,insert……select 实际上往表中插入了 5 行数据 (1 1)(2 2)(3 3)(4 4)(5 5)。但是,这五行数据是分三次申请的自增 id,结合批量申请策略,每次申请到的自增 id 个数都是上一次的两倍,所以:
由于这条语句实际只用上了 5 个 id,所以 id=6 和 id=7 就被浪费掉了。之后,再执行 insert into test_pk2 values(null,6,6)
,实际上插入的数据就是(8,6,6):
本文总结下自增值不连续的 4 个场景:
insert...select
语句)此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。