文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL:MySQL的自增主键是连续自增吗?

2024-11-30 03:16

关注

我们在使用自增主键统计数据库的数据量时,也会经常使用id的最大值与最小值之间的差值作为数据库当前已有数据的条数,但是这种统计方式是否正确?是否存在误差?

笔者先给出本文结论:自增主键可以保持主键递增顺序插入,避免页分裂,索引更为紧凑,但是自增主键并不能保证连续递增,即出现空洞

但是问题再次出现,为什么明明是自增主键,为什么不能保证连续递增?为什么会出现空洞?

在本文中,我们使用如下的数据库配置:

1 自增值的存储

在如上的空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据,再执行 show create table 命令,就可以看到如下图所示的结果:

表定义里面出现了一个 AUTO_INCREMENT=2,表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。

不同的引擎对于自增至的保存策略不同:

2 自增值修改机制

在 MySQL 里面,如果字段 id 被定义为 AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下:

根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设,某次要插入的值是 X,当前的自增值是 Y。

新的自增值生成算法是:从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值。其中,auto_increment_offset 和 auto_increment_increment 是两个系统参数,分别用来表示自增的初始值和步长,默认值都是 1。

但是在一些场景下,使用的就不全是默认值。比如,双 M 的主备结构里要求双写的时候,我们就可能会设置成 auto_increment_increment=2,让一个库的自增 id 都是奇数,另一个库的自增 id 都是偶数,避免两个库生成的主键发生冲突。

当 auto_increment_offset 和 auto_increment_increment 都是 1 的时候,新的自增值生成逻辑很简单,就是:

3 自增值修改时机

3.1 唯一键冲突

假设表t有了存在(1,1,1)这条记录,再次执行一次数据命令:

insert into t values(null, 1, 1);

这个语句的执行流程就是:

可以看到,这个表的自增值修改为3之后也不会再回退,之后再插入拿到的自增id就是3,自增主键不再连续。

3.2 事务回滚

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

如上语句就会出现不连续自增id的情况。MySQL不允许做回退,看如下的假设:假设有两个并行执行的事务,在申请自增值时,为了避免两个事务申请到相同自增id,肯定加锁,然后顺序申请。

为了解决这个主键冲突,有两种方法:

出于性能考虑,如果设计为必须连续,那就需要每次都去检查当前申请的ID是否已存在,浪费性能;或者提升锁粒度,会导致申请ID退化为串行申请 

3.3 批量申请自增id策略

对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:

4 自增锁优化

自增id锁并不是一个事务锁,而是每次申请完就马上释放,以便允许别的事务再申请。

在MySQL 5.0版本的时候,自增锁的范围是语句级别。也就是说,如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放。显然,这样设计会影响并发度。

MySQL 5.1.22版本引入了一个新策略,新增参数innodb_autoinc_lock_mode,默认值是1。

普通 insert 语句,自增锁在申请之后就马上释放;

类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;

你一定有两个疑问:为什么默认设置下,insert … select 要使用语句级的锁?为什么这个参数的默认值不是 2?原因就是为了保证数据的一致性。

在生产上,尤其是有 insert … select 这种批量插入数据的场景时,从并发插入数据性能的角度考虑,我建议你这样设置:innodb_autoinc_lock_mode=2 ,并且 binlog_format=row. 这样做,既能提升并发性,又不会出现数据一致性问题。

需要注意的是,我这里说的批量插入数据,包含的语句类型是 insert … select、replace … select 和 load data 语句。

但是,在普通的 insert 语句里面包含多个 value 值的情况下,即使 innodb_autoinc_lock_mode 设置为 1,也不会等语句执行完成才释放锁。因为这类语句在申请自增 id 的时候,是可以精确计算出需要多少个 id 的,然后一次性申请,申请完成后锁就可以释放了。

也就是说,批量插入数据的语句,之所以需要这么设置,是因为“不知道要预先申请多少个 id”。

既然预先不知道要申请多少个自增 id,那么一种直接的想法就是需要一个时申请一个。但如果一个 select … insert 语句要插入 10 万行数据,按照这个逻辑的话就要申请 10 万次。显然,这种申请自增 id 的策略,在大批量插入数据的情况下,不但速度慢,还会影响并发插入的性能。

因此,对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:

来源:陆队长内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯