面试题:MySQL 自增主键一定是连续的吗?

文章目录

  • 测试环境:
  • 一、自增值的属性特征:
    • 1. 自增主键值是存储在哪的?
    • 2. 自增主键值的修改机制?
  • 二、新增语句自增主键是如何变化的:
  • 三、自增主键值不连续情况:(唯一主键冲突)
  • 四、自增主键值不连续情况:(事务回滚)
  • 五、自增主键值不连续情况:(批量插入)
  • 六、自增主键值的优化
    • 1.什么是自增锁
    • 2.自增锁有哪些优化
  • 七、MySQL8.0做了哪些优化


测试环境:

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); 

流程图如下所示

图片

流程步骤:

  • AUTO_INCREMENT=1 (表示下一次插入数据时,如果需要自动生成自增值,会生成 id=1。)
  • insert into t values(null, 1, 1) (执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,1,1))
  • get AUTO_INCREMENT=1 (InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 1 )
  • AUTO_INCREMENT=2 insert into t values(1, 1, 1) (将传入的行的值改成 (1,1,1),并把自增值改为2)
  • insert (1,1,1) 执行插入操作,至此流程结束

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

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

当我执行以下SQL语句时

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

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

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

图片

例如:

  • AUTO_INCREMENT=2 (表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。)
  • insert into t values(null, 1, 1) (执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,1,1))
  • get AUTO_INCREMENT=2 (InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 2 )
  • AUTO_INCREMENT=3 insert into t values(2, 1, 1) (将传入的行的值改成 (2,1,1),并把自增值改为3)
  • 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格式,主从复制就会出现不一致。

七、MySQL8.0做了哪些优化

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

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/277090.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

git 常用基本命令, reset 回退撤销commit,解决gitignore无效,忽略记录或未记录远程仓库的文件,删除远程仓库文件

git 基本命令 reset 撤销commit https://blog.csdn.net/a704397849/article/details/135220091 idea 中 rest 撤销commit过程如下&#xff1a; Git -> Rest Head… 在To Commit中的HEAD后面加上^&#xff0c;点击Reset即可撤回最近一次的尚未push的commit Reset Type 有三…

maven阿里源找不到指定jar包解决方案

到这里去找&#xff1a; https://mvnrepository.com/ 例如你要找&#xff1a;spring-boot-starter-web 复制坐标即可。IDEA会去坐标自带的网址寻找资源&#xff0c;可能会慢一点。

轻量应用服务器2核2G3M带宽腾讯云和阿里云价格1元之争?

轻量应用服务器2核2G3M带宽配置阿里云和腾讯云都降价了&#xff0c;阿里云63元一年&#xff0c;腾讯云62元一年&#xff0c;到底选哪家比较好&#xff1f;这个价都需要新用户资格&#xff0c;第一次购买云服务器的用户&#xff0c;所以&#xff0c;如果你的账号是新用户&#x…

pda手持终端定制_基于联发科|紫光展锐平台的手持终端解决方案

pda手持终端的高性能正在重新定义便携式设备的工作效率。pda手持终端解决方案采用了8核心2.0G主频处理器及Android 10系统&#xff0c;可选配2G16G/4G64G内存&#xff0c;这款手持终端确保了更快的运行速度和更低的功耗。它支持各种功能选配&#xff0c;包括条码扫描、NFC、红外…

Linux管理LVM逻辑卷

目录 一、LVM逻辑卷介绍 1. 概述 2. LVM基本术语 2.1 PV&#xff08;Physical Volume&#xff0c;物理卷&#xff09; 2.2 VG (Volume Group&#xff0c;卷组&#xff09; 2.3 LV (Logical Volume&#xff0c;逻辑卷&#xff09; 3. 常用的磁盘命令 4. 查看系统信息的命…

分享好用的chatgpt

1.在vscode中&#xff0c;点击这个&#xff1a; 2.搜索&#xff1a;ChatGPT - 中文版&#xff0c;个人觉得这个更好用&#xff1a; 3.下载完成之后&#xff0c;左侧会多出来这个&#xff1a; 点击这个图标就能进入chatgpt界面了 4.如果想使用tizi访问国外的chatgpt&#xf…

Vue实现导出Excel表格,提示“文件已损坏,无法打开”的解决方法

一、vue实现导出excel 1、前端实现 xlsx是一个用于读取、解析和写入Excel文件的JavaScript库。它提供了一系列的API来处理Excel文件。使用该库&#xff0c;你可以将数据转换为Excel文件并下载到本地。这种方法适用于在前端直接生成Excel文件的场景。 安装xlsx依赖 npm inst…

CSS 丝带形状效果

CSS 丝带形状效果如图&#xff1a; 通过CSS创建折叠丝带形状 这里代码应该比较清晰易懂&#xff0c;clip-path 的值应该也容易理解。要注意的是&#xff0c;我们使用了 color-mix() 函数&#xff0c;这个属性允许创建主颜色的深色版本。现在如果我们将元素旋转相反的方向&#…

深入理解 C# 中的字符串比较:String.CompareTo vs String.Equals

深入理解 C# 中的字符串比较&#xff1a;String.CompareTo vs String.Equals 在处理字符串时&#xff0c;了解如何正确比较它们对于编写清晰、有效和可靠的 C# 程序至关重要。本文将深入探讨 C# 中的两个常用字符串比较方法&#xff1a;String.CompareTo 和 String.Equals&…

SAP MD04界面中增加功能按钮

通常在查看物料需求时,PMC用的最多的就是MD04的界面,在MD04界面中有很多的功能按钮,同时我们还可以新增功能按键的跳转。 1、我们先设置一下系统标准的在MD04界面中增加跳转的按钮 如下图:自有收藏夹—导航参数文件—维护 然后在MD04的界面中就可以看到我们刚才加的MM0…

QT应用篇 二、QML用Image组件实现Progress Bar 的效果

QT应用篇 一、QT上位机串口编程 二、QML用Image组件实现Progress Bar 的效果 三、QML自定义显示SpinBox的加减按键图片及显示值效果 文章目录 QT应用篇前言一、qml需求二、使用组件1.Image组件2.Image中fillMode的使用例子 总结 前言 记录自己学习QML的一些小技巧方便日后查找…

存算分离降本增效,StarRocks 助力聚水潭 SaaS 业务服务化升级

作者&#xff1a;聚水潭数据研发负责人 溪竹 聚水潭是中国领先的 SaaS 软件服务商&#xff0c;核心产品是电商 ERP&#xff0c;协同350余家电商平台&#xff0c;为商家提供综合的信息化、数字化解决方案。公司是偏线下商家侧的 toB 服务商&#xff0c;员工人数超过3500&#xf…

利用格式工厂,做视频的剪辑

接到一个工作&#xff0c;一段视频中&#xff0c;需要抠除其中某一段 其实 剪映、苹果手机的视频编辑功能&#xff0c;都可以轻松搞定 只是清晰度会有损伤 而且对于太大的视频&#xff0c;苹果手机就没法处理了。 很多软件在导出高清视频时&#xff0c;需要会员收费&#xff0…

**Python**综合案例

Python综合案例 一、系统需求分析 1、需求分析 使用面向对象编程思想完成学员管理系统的开发,具体如下: ① 系统要求:学员数据存储在文件中 ② 系统功能:添加学员、删除学员、修改学员信息、查询学员信息、显示所有学员信息、保存学员信息及退出系统等功能。 2、角色…

闲来无事互相翻包,来看看我们的2023通勤EDC

点击文末“阅读原文”即可参与节目互动 剪辑、音频 / 卷圈 运营 / SandLiu 卷圈 监制 / 姝琦 封面 / 姝琦Midjourney 产品统筹 / bobo 场地支持 / 声湃轩北京录音间 EDC&#xff0c;Every Day Carry&#xff0c;顾名思义就是每天包里装的东西。 什么&#xff1f;难道除了…

零代码也能玩出花:Mugeda在H5设计中的魔法力量

文章目录 一、Mugeda零代码可视化H5设计工具简介二、Mugeda零代码可视化H5设计实战案例1. 注册并登录Mugeda账号2. 选择模板3. 编辑页面内容4. 添加动画效果5. 预览和发布 三、Mugeda零代码可视化H5设计的优势《Mugeda零代码可视化H5设计实战》内容简介作者简介目录前言/序言 随…

Python能做大项目(8) - Need for Speed! 高效编码之一

1. AI赋能的代码编写 传统上&#xff0c;IDE的重要功能之一&#xff0c;就是代码自动完成、语法高亮、文档提示、错误诊断等等。随着人类进入深度学习时代&#xff0c;AI辅助编码则让程序员如虎添翼。 我们首先介绍几个AI辅助编码的工具&#xff0c;然后再介绍常规的语法高亮…

[HUBUCTF 2022 新生赛]checkin

[HUBUCTF 2022 新生赛]checkin wp 进入页面&#xff0c;代码如下&#xff1a; <?php show_source(__FILE__); $username "this_is_secret"; $password "this_is_not_known_to_you"; include("flag.php");//here I changed those two…

基于Java+SpringBoot+vue实现图书借阅管理系统

基于JavaSpringBootvue实现图书借阅和销售商城一体化系统 &#x1f345; 作者主页 程序设计 &#x1f345; 欢迎点赞 &#x1f44d; 收藏 ⭐留言 &#x1f4dd; &#x1f345; 文末获取源码联系方式 &#x1f4dd; 文章目录 基于JavaSpringBootvue实现图书借阅和销售商城一体化…

TikTok年度回顾:2023年的亮点时刻

2023年&#xff0c;TikTok再次成为全球关注的焦点&#xff0c;不仅延续了其独特的社交媒体魅力&#xff0c;还在创新、文化影响力等方面取得了一系列令人瞩目的亮点时刻。本文将深入探讨TikTok在2023年的重要事件、创新举措以及对社会的深远影响。 创新功能引领社交潮流 TikTok…