您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL自增主键一定是连续的吗?

时间:2024-03-10 15:25:36  来源:dbaplus社群  作者:

测试环境:

MySQL版本:8.0

数据库表:T (主键id,唯一索引c,普通字段d)

如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不能保证连续递增。

一、自增值的属性特征

1. 自增主键值是存储在哪的?

MySQL5.7版本

在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。

MySQL8.0之后版本

在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。

可以通过看表详情查看当前自增值,以及查看表参数详情AUTO_INCREMENT值(AUTO_INCREMENT就是当前数据表的自增值)

2. 自增主键值的修改机制?

在表t中,我定义了主键id为自增值,在插入一行数据的时候,自增值的行为如下:

1)如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;

2)如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。

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

1)如果 X<Y,那么这个表的自增值不变;

2)如果 X≥Y,就需要把当前自增值修改为新的自增值。

二、新增语句自增主键是如何变化的

我们执行以下SQL语句,来观察自增主键是如何进行变化的

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

流程图如下所示:

流程步骤:

1、AUTO_INCREMENT=1(表示下一次插入数据时,如果需要自动生成自增值,会生成 id=1。)

2、insert into t values(null, 1, 1)(执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,1,1))

3、get AUTO_INCREMENT=1(InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 1 )

4、AUTO_INCREMENT=2 insert into t values(1, 1, 1)(将传入的行的值改成 (1,1,1),并把自增值改为2)

5、insert (1,1,1) 执行插入操作,至此流程结束

大家可以发现,在这个流程当中是先进行自增值的+1,在进行新增语句的执行的。大家可以发现这个操作并没有进行原子操作,如果SQL语句执行失败,那么自增是不是就不会连续了呢?

三、自增主键值不连续情况:(唯一主键冲突)

当我执行以下SQL语句时

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

第一次我们可以进行新增成功,根据自增值的修改机制。如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;

当我们第二次在执行以下SQL语句时,就会出现错误。因为我们表中c字段是唯一索引,会出现Duplicate key error错误导致新增失败。

例如:

1、AUTO_INCREMENT=2(表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。)

2、insert into t values(null, 1, 1)(执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,1,1))

3、get AUTO_INCREMENT=2(InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 2 )

4、AUTO_INCREMENT=3 insert into t values(2, 1, 1)(将传入的行的值改成 (2,1,1),并把自增值改为3)

5、insert (2,1,1) 执行插入操作,由于已经存在 c=1 的记录,所以报 Duplicate key error,语句返回。

可以看到,这个表的自增值改成 3,是在真正执行插入数据的操作之前。这个语句真正执行的时候,因为碰到唯一键 c 冲突,所以 id=2 这一行并没有插入成功,但也没有将自增值再改回去。所以,在这之后,再插入新的数据行时,拿到的自增 id 就是 3。也就是说,出现了自增主键不连续的情况。

四、自增主键值不连续情况:(事务回滚)

其实事务回滚原理也和上面一样,都是因为异常导致新增失败,但是自增值没有进行回退。

五、自增主键值不连续情况:(批量插入)

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

1、语句执行过程中,第一次申请自增 id,会分配 1 个;

2、1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;

3、2 个用完以后,还是这个语句, 第三次申请自增 id,会分配 4 个;

4、依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。

执行以下SQL语句(在表t中先新增了4条数据,在创建表tt把表t数据进行批量新增)

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

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

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

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

create table tt like t;

insert into tt(c,d) select c,d from t;

insert into tt values(null, 5,5);

第一次申请到了 id=1,第二次被分配了 id=2 和 id=3, 第三次被分配到 id=4 到 id=7。当我们再执行 insert into t2 values(null, 5,5),实际上插入的数据就是(8,5,5),出现了自增主键不连续的情况。

六、自增主键值的优化

1.什么是自增锁

自增锁是一种比拟非凡的表级锁。并且在事务向蕴含了 AUTO_INCREMENT 列的表中新增数据时就会去持有自增锁,假如事务 A 正在做这个操作,如果另一个事务 B 尝试执行 INSERT语句,事务 B 会被阻塞住,直到事务 A 开释自增锁。

2.自增锁有哪些优化

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

传统模式(Traditional)

这个参数的值被设置为 0 时,表示采用之前 MySQL 5.0 版本的策略,即语句执行结束后才释放锁;

传统模式他可以保证数据一致性,但是如果有多个事务并发的执行 INSERT 操作,AUTO-INC的存在会使得 MySQL 的性能略有降落,因为同时只能执行一条 INSERT 语句。

间断模式(Consecutive)

这个参数的值被设置为 1 时:普通 insert 语句,自增锁在申请之后就马上释放;类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;

间断模式他可以保证数据一致性,但是如果有多个事务并发的执行 INSERT 批量操作时,就会进行锁等待状态。如果我们业务插入数据量很大时,这个时候MySQL的性能就会大大下降。

穿插模式(Interleaved)

这个参数的值被设置为 2 时,所有的申请自增主键的动作都是申请后就释放锁。

穿插模式他没有进行任何的上锁设置。在一定情况下是保证了MySQL的性能,但是他无法保证数据的一致性。如果我们在穿插模式下进行主从复制时,如果你的binlog格式不是row格式,主从复制就会出现不一致。具体可以了解一下我的这篇文章MySQL是如何保证主从一致

七、MySQL8.0做了哪些优化

在MySQL8.0之后版本,已经默认设置为 innodb_autoinc_lock_mode=2 , binlog_format=row.。这样更有利与我们在 insert … select 这种批量插入数据的场景时,既能提升并发性,又不会出现数据一致性问题。

作者丨又 欠

来源丨https://blog.csdn.NET/qq_48157004/article/detAIls/128356734



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: MySQL  点击:(5)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: MySQL  点击:(10)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: MySQL  点击:(8)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: MySQL  点击:(23)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: MySQL  点击:(5)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: MySQL  点击:(26)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  Search: MySQL  点击:(41)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  Search: MySQL  点击:(50)  评论:(0)  加入收藏
为什么高性能场景选用Postgres SQL 而不是 MySQL
一、 数据库简介 TLDR;1.1 MySQL MySQL声称自己是最流行的开源数据库,它属于最流行的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。LAMP...【详细内容】
2024-02-19  Search: MySQL  点击:(36)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  Search: MySQL  点击:(53)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(5)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(10)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(8)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(23)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(5)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(26)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(41)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(50)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(53)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(82)  评论:(0)  加入收藏
站内最新
站内热门
站内头条