阿里二面差点败在这道题MySQL自增主键为何不是连续的呢?
面试官 :" MySQL主键你一般怎样用 ?"
心想:"嘿嘿,面试官肯定想问InnoDB引擎索引特性相关知识!"
小龙 :"平时主键我一般用自增主键!因为自增ID有序,会按顺序往最后插入,而UUID无序,随机生成,随机插入,会造成 频繁页分裂 ,内存碎片化 ,大量随机IO,巴拉巴拉…"
心想:"这波稳啦!"
然后,面试官又来一套组合拳
面试官 :"en!好,那你知道 自增主键是否严格递增 呢?"
心想:"这还不简单,肯定递增啊!不对,仔细一想,有一些情况下自增主键是断开的,于是"
小龙 :"‘肯定不是递增的啊!"
以为到这里结束啦,只是考考我是否实际真正用过,没想到还是天真啦!
面试官 :"那你知道为啥不是严格递增的吗?换句话来说, 为何不是连续的 ?"
小龙 :"裂开!tm还真不知道,不会真要考那么底层吧!我去"
于是骂骂咧咧结束了这个话题!
但是由于其他问题回答的也可以,还是过啦!
不过,既然遇到啦这个问题,下来查阅了资料,还是想分享给大家!
1、前文
众所周知,由于自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,大量的随机IO。自增主键不连续。
这是大家已经熟知的知识点,但是,可能也有大部分朋友和之前的小龙一样不知道 为何自增主键不是严格递增的?
今天这篇文章,我们就来说说这个问题,看看什么情况下自增主键会出现 "断层"?
为了更加形象,这里创建一个表 xl_tb ,其中id是自增主键字段、a是唯一索引 ,然后插入一条数据,我们再查看它的表结构。 CREATE TABLE `NewTable` ( `id` int(11) NOT NULL AUTO_INCREMENT , `a` int(11) NULL , `b` int(11) NULL , PRIMARY KEY (`id`), UNIQUE INDEX `a` (`a`) USING BTREE );insert into xl_tb values(null, 1, 1)mysql> show create table xl_tbG; *************************** 1. row *************************** Table: xl_tb Create Table: CREATE TABLE `xl_tb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.04 sec)
可以看到,表定义里面出现了一个 AUTO_INCREMENT=4 ,表示下一次插入数据时,如果需要自动生成自增值,会生成id=4。
大家也可以自己手动试试!
但是,看到这里,可能有朋友以为自增值存在表结构里吧!哈哈,你这样想就有错啦! 2、自增主键为何不连续2.1、自增主键存储策略
接下来,我们一起看看自增值保存在哪里吧!
其实,不同的存储引擎,自增值保存策略不一样的。 MyISAM 引擎的自增值保存在数据文件中。 InnoDB 引擎的自增值,其实是保存在了内存里,并且到了MySQL 8.0版本后,才有了"自增值持久化 "的能力,也就是才实现了"如果发生重启,表的自增值可以恢复为MySQL重启前的值",具体情况是: 在MySQL 5.7及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值max(id),然后将max(id)+1作为这个表当前的自增值。 举例来说,如果一个表当前数据行里最大的id是10,AUTO_INCREMENT=11。这时候,我们删除id=10的行,AUTO_INCREMENT还是11。但如果马上重启实例,重启后这个表的AUTO_INCREMENT就会变成10。 也就是说,MySQL重启可能会修改一个表的AUTO_INCREMENT的值。 在MySQL 8.0版本,将自增值的变更记录在了redo log中,重启的时候依靠 redo log 恢复重启之前的值。
理解了MySQL对自增值的保存策略以后,我们再看看自增值修改机制。 2.2、自增值修改机制如果插入数据时id字段指定为0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT值填到自增字段; 如果插入数据时id字段指定了具体的值,就直接使用语句里指定的值。 2.3、自增值新增机制如果准备插入的值>=当前自增值,新的自增值就是"准备插入的值+1"; 否则,自增值不变。 2.4、自增值的修改时机
eg:
假设,表xl_tb里面已经有了(1,1,1)这条记录,这时我再执行一条插入数据命令: insert into t values(null, 1, 1); (自增id,唯一键a,普通字段b)
这个语句的 执行流程 就是: 执行器调用InnoDB引擎接口写入一行,传入的这一行的值是(0,1,1); InnoDB发现用户没有指定自增id的值,获取表xl_tb当前的自增值4; 将传入的行的值改成(4,1,1); 将表的自增值改成5; 继续执行插入数据操作,由于已经存在a=1的记录,所以报Duplicate key error,语句返回。
这个表的自增值改成5,是在真正执行插入数据的操作之前。这个语句真正执行的时候,因为 碰到唯一键a冲突 ,所以id=2这一行并没有插入成功,但也没有将自增值再改回去。
所以,在这之后,再插入新的数据行时,拿到的自增id就是5。也就是说,出现了自增主键不连续的情况。因此, 唯一键冲突是导致自增主键id不连续的第一种原因 。
同样地,事务 回滚也会产生类似的现象,这就是第二种原因 。
这时,你可能会想,为什么在出现唯一键冲突或者回滚的时候,MySQL没有把表xl_tb的自增值改回去呢?如果把表xl_tb的当前自增值从5改回4,再插入新数据的时候,不就可以生成id=2的一行数据了吗?
那么,接下来跟着我的思路来看看,为何不让自增主键后退吧!
首先,我们假设有两个并行执行的事务A、B,在申请自增值的时候,为了避免两个事务申请到相同的自增id,肯定要加锁,然后顺序申请。
首先,事务A申请到 id=2,此时当前自增值为3,由于加锁顺序申请,事务B申请到 id=3(当前自增值),此时,当前自增值变为 3+1=4 然后,事务 A、B都插入,假设事务B先插入然后成功插入,然后事务A插入发生了 唯一键冲突 如果假设允许自增值后退,自增值就变为2啦,假如事务A继续插入,申请到 id=2,成功插入,申请到 id=3,插入,由于之前事务B已经插入 id=3的数据,此时发生 主键冲突
那怎样解决呢? 每次申请id之前,先判断表里面是否已经存在这个id。 扩大锁范围,必须等事务执行完,才能申请下一个
虽然这两种方法可以解决,但是无疑性能极低。
于是,便让自增值不能回退,而避免造成主键冲突等问题。(也许还有其他问题我没有想到)
3、总结
为什么自增主键不连续? 在MySQL 5.7及之前的版本,自增值保存在内存里,并没有持久化 事务 回滚 (自增值不能回退,因为并发插入数据时,回退自增ID可能造成主键冲突) 唯一键冲突(由于表的自增值已变,但是主键发生冲突没插进去,下一次插入主键=现在变了的子增值+1,所以不连续)
6月底,华为将发布这门认证华为认证HCIPDatacomWANPlanningandDeploymentV1。0(中文版)预计将于2021年6月30日正式对外发布。为了帮助您做好学习培训和考试计划,现进行预
HCIEWLAN备考知识点都在这华为认证HCIEWLANV1。0已发布,HCIEWLANV1。0定位于培训与认证在园区WLAN领域具备大中型复杂网络规划设计部署运维及优化能力的网络技术专家。通过HCIEWLANV
I技术E企玩云南省首届HCIE沙龙活动在昆明成功举办3月27日,I技术E企玩的2021年华为云南HCIE沙龙在昆明成功举办,近50位云南省HCIE精英代表齐聚一堂,共享HCIE荣耀。HCIE(华为认证ICT专家)是华为认证体系中最高
HCIAVideoConferenceV3。0正式发布华为认证HCIAVideoConferenceV3。0(中文版)自2021年3月31日起,正式中国区发布。发布概述基于平台生态战略,围绕云管端协同的新ICT技术架构,华为公司打造了
华为杨文池聚合有能有为人,构筑生态立方体过去十年,是中国的数字经济迎来了指数级增长的十年。按照中国信通院发布的中国数字经济发展白皮书显示,2020年我国数字经济规模达到39。2万亿元,占GDP比重为38。6,同时2020
HCIEWLANV1。0正式发布华为认证无线局域网工程师HCIEWLANV1。0(中文版)自2021年4月26日起,正式中国区发布。发布概述基于平台生态战略,围绕云管端协同的新ICT技术架构,华为公司打造了覆盖I
华为徐直军共成长同发展5月17日,以因聚而生有能有为为主题的华为中国生态大会2021在深圳会展中心拉开帷幕。华为将与生态伙伴一起,构建适应数字化发展要求的伙伴体系,携手跨越新数字鸿沟。华为轮值董事长徐直
小米12最新曝光,首发骁龙898处理器,100W快充加5000mAh大电池近日,有关小米12最新消息那是层出不穷的,据有关博主爆料,小米12将在今年12月发布,而且有很大概率会全球首发高通骁龙898旗舰芯片。高通骁龙898旗舰芯片将采用全新的X2超大核架
这门HCIPDatacom认证将发布华为认证HCIPDatacomEnterpriseNetworkSolutionDesignV1。0(中文版)预计将于2021年6月30日正式对外发布。为了帮助您做好学习培训和考试
经过3次面试,我终于考过HCIE行文至此,落笔为终。我与HCIE的故事始于2019年金秋,终于2021年初春。一年多的HCIE备考之旅终于落下帷幕,细数过往,历历在目。首先我要感谢老师的视频,点点滴滴都详尽务实,
周杰伦的第一首歌你还记得吗?近日,在微博上出现了一个关于周杰伦的话题,关于他的创作有多强,网友们一直在争论,尤其是周杰伦的粉丝,是最有发言权的。近日,周董也在社交平台上发表声明,庆祝自己出道20年,感谢一直支