Redian新闻
>
美团:MySQL 自增主键一定是连续的吗?

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

公众号新闻

点击上方“芋道源码”,选择“设为星标

管她前浪,还是后浪?

能浪的浪,才是好浪!

每天 10:33 更新文章,每天掉亿点点头发...

源码精品专栏

 
来源:飞天小牛肉

美团问数据库比较多,分享一位读者面试美团遇到的关于 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 自增值到底保存在哪里以后,我们再来看看自增值的修改机制,并以此引出第一种自增值不连续的场景。

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

  • 项目地址:https://github.com/YunaiV/ruoyi-vue-pro
  • 视频教程:https://doc.iocoder.cn/video/

自增值不连续场景 1

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

  • 如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;
  • 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设某次要插入的值是 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_offsetauto_increment_increment 这两个参数来决定的,这俩分别用来表示自增的初始值和步长,默认值都是 1。

所以,上面的例子中生成新的自增值的步骤实际是这样的:从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 100 的值,作为新的自增值。

所以,这种情况下,自增值可能会是 102,103 等等之类的,就会导致不连续的主键 id。

更遗憾的是,即使在自增初始值和步长这两个参数都设置为 1 的时候,自增主键 id 也不一定能保证主键是连续的

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

  • 项目地址:https://github.com/YunaiV/yudao-cloud
  • 视频教程:https://doc.iocoder.cn/video/

自增值不连续场景 2

举个例子,我们现在往表里插入一条 (null,1,1) 的记录,生成的主键是 1,AUTO_INCREMENT= 2,对吧

这时我再执行一条插入 (null,1,1) 的命令,很显然会报错 Duplicate entry,因为我们设置了一个唯一索引字段 a

但是,你会惊奇的发现,虽然插入失败了,但自增值仍然从 2 增加到了 3!

这是为啥?

我们来分析下这个 insert 语句的执行流程:

  1. 执行器调用 InnoDB 引擎接口准备插入一行记录 (null,1,1);
  2. InnoDB 发现用户没有指定自增 id 的值,则获取表 test_pk 当前的自增值 2;
  3. 将传入的记录改成 (2,1,1);
  4. 将表的自增值改成 3;
  5. 继续执行插入数据操作,由于已经存在 a=1 的记录,所以报 Duplicate key error,语句返回

可以看到,自增值修改的这个操作,是在真正执行插入数据的操作之前。

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

至此,我们已经罗列了两种自增主键不连续的情况:

  1. 自增初始值和自增步长设置不为 1
  2. 唯一键冲突

除此之外,事务回滚也会导致这种情况

自增值不连续场景 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,肯定要加锁,然后顺序申请,对吧。

  1. 假设事务 A 申请到了 id = 1, 事务 B 申请到 id=2,那么这时候表 t 的自增值是 3,之后继续执行。
  2. 事务 B 正确提交了,但事务 A 出现了唯一键冲突,也就是 id = 1 的那行记录插入失败了,那如果允许事务 A 把自增 id 回退,也就是把表的当前自增值改回 1,那么就会出现这样的情况:表里面已经有 id = 2 的行,而当前的自增 id 值是 1。
  3. 接下来,继续执行的其他事务就会申请到 id=2。这时,就会出现插入语句报错“主键冲突”。

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

  1. 每次申请 id 之前,先判断表里面是否已经存在这个 id,如果存在,就跳过这个 id
  2. 把自增 id 的锁范围扩大,必须等到一个事务执行完成并提交,下一个事务才能再申请自增 id

很显然,上述两个方法的成本都比较高,会导致性能问题。而究其原因呢,是我们假设的这个 “允许自增 id 回退”。

因此,InnoDB 放弃了这个设计,语句执行失败也不回退自增 id。也正是因为这样,所以才只保证了自增 id 是递增的,但不保证是连续的。

综上,已经分析了三种自增值不连续的场景,还有第四种场景:批量插入数据。

自增值不连续场景 4

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

  1. 语句执行过程中,第一次申请自增 id,会分配 1 个;
  2. 1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;
  3. 2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个;
  4. 依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。

注意,这里说的批量插入数据,不是在普通的 insert 语句里面包含多个 value 值!!!,因为这类语句在申请自增 id 的时候,是可以精确计算出需要多少个 id 的,然后一次性申请,申请完成后锁就可以释放了。

而对于 insert … select、replace … select 和 load data 这种类型的语句来说,MySQL 并不知道到底需要申请多少 id,所以就采用了这种批量申请的策略,毕竟一个一个申请的话实在太慢了。

举个例子,假设我们现在这个表有下面这些数据:

我们创建一个和当前表 test_pk 有相同结构定义的表 test_pk2

然后使用 insert...selectteset_pk2 表中批量插入数据:

可以看到,成功导入了数据。

再来看下 test_pk2 的自增值是多少:

如上分析,是 8 而不是 6

具体来说,insert…select 实际上往表中插入了 5 行数据 (1 1)(2 2)(3 3)(4 4)(5 5)。但是,这五行数据是分三次申请的自增 id,结合批量申请策略,每次申请到的自增 id 个数都是上一次的两倍,所以:

  • 第一次申请到了一个 id:id=1
  • 第二次被分配了两个 id:id=2 和 id=3
  • 第三次被分配到了 4 个 id:id=4、id = 5、id = 6、id=7

由于这条语句实际只用上了 5 个 id,所以 id=6 和 id=7 就被浪费掉了。之后,再执行 insert into test_pk2 values(null,6,6),实际上插入的数据就是(8,6,6):

小结

总结下自增值不连续的四个场景:

  1. 自增初始值和自增步长设置不为 1
  2. 唯一键冲突
  3. 事务回滚
  4. 批量插入(如 insert...select 语句)


欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢

已在知识星球更新源码解析如下:

最近更新《芋道 SpringBoot 2.X 入门》系列,已经 101 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。

提供近 3W 行代码的 SpringBoot 示例,以及超 4W 行代码的电商微服务项目。

获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。

文章有帮助的话,在看,转发吧。

谢谢支持哟 (*^__^*)

微信扫码关注该文公众号作者

戳这里提交新闻线索和高质量文章给我们。
相关阅读
MySQL 批量操作,一次插入多少行数据效率最高?美团:为什么 MySQL 不推荐使用 join?翻过三座大山:MatrixOne从 NewSQL 到 HTAP 分布式架构演进MySQL 处理大数据表的 3 种方案,写的太好了,建议收藏!!MySQL 数据同步到 Redis 缓存, so Easy !SQLAlchemy 2.0.0发布首个RC,Python ORM框架SpringBoot + Druid,完美监控 MySQL 性能MySQL 字符串指南 | Linux 中国MySQL适合运行在Docker中吗?一文掌握 MyBatis 的动态 SQL 使用与原理MySQL 索引 15 连问,抗住!抖音卷美团:流量掘金,意在广告Fluent Mybatis、原生Mybatis,、Mybatis Plus 大对比,哪个更好用?5大主流方案对比:MySQL千亿级数据线上平滑扩容实战问了几人,MySQL changebuffer 这点都没答对MySQL索引15连问,抗住!美团四面:如何保障 MySQL 和 Redis 的数据一致性?阴阳平衡、和而不同隐语开源首个工业级多方安全数据分析系统SCQL:像写SQL一样「易用」隐私计算MySQL主从复制原理剖析与应用实践美团:2023年新餐饮双主场行业报告(120页)SpringBoot 实现 MySQL 百万级数据量导出并避免 OOM 的解决方案想起我爸妈妈能否熬过这一关MySQL 最朴素的监控方式!用完爱不释手如何高效实现MySQL与elasticsearch的数据同步MySQL 常用脚本MySQL性能优化浅析及线上案例SQL能完成哪方面的计算?一文详解关系代数和SQL语法MySQL 底层之 MVCC、回滚段、一致性读、锁定读美团:这个 SQL 语句加了哪些锁?邓小平在起落中如何使用汪东兴了解那些“奇葩”SQL写法,快速写出高效率SQL如何判断此番是否“境外势力”作祟的办法:老大用自己做“诱饵”美团:SPI 的原理是什么?
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。