面试官:MySQL主键为什么不是连续递增的?

浏览:
字体:
发布时间:2023-01-09 16:12:01
来源:今日头条

设计MySQL表时,我们一般会设置一个自增主键,从而让主键索引尽可能的保持递增的趋势,这样可以避免页分裂,让MySQL顺序写入,大大提高MySQL的性能。

但是,自增主键只能保持大致递增,无法保证顺序递增。

当我们创建完一个表后,通过show create table命令,可以看到MySQL定义了AUTO_INCREMENT来指定主键的递增值。

在MySQL5.7之前,这个递增值是直接保存在内存里面的,当服务器重启后,MySQL会读取表里面的最大主键id,然后将最大值+1作为下次递增的值。

在MySQL8.0时,将其优化为了保存在redo log中,从而实现了递增值的持久化。

那都有哪些情况可能导致主键不能连续递增呢?

首先我们要知道的是,MySQL对于主键递增值得使用是一次性的,即每次获取完递增值之后,不管接下来的语句是否能真正执行成功,这个递增值都不会再回收利用了。

1、唯一索引冲突导致的主键不连续

有时为了满足业务的需要,我们有时会对表中的字段设置唯一索引。但是当唯一索引冲突时,会产生什么问题呢?

以上面的user表为例,我们对name设置唯一索引。

我们执行两次以下语句:

INSERT into user values (null,'张三','123456');

不难猜到,第二次的执行结果肯定会报错:

我们在上面已经提到,MySQL对于递增值的使用是一次性的,那么第二次执行插入时,不管语句成功还是失败,那么这个递增值就会浪费掉。

这时,我们再执行一条正常的不冲突的插入语句,会发现主键id产生了间隔。

2、事务回滚会造成主键不连续

与唯一索引冲突类似,当我们在一个事务中执行插入语句时,那么必然会向MySQL申请一个递增值作为主键id,如果最后事务没有提交,而是回滚,那么这个递增值自然也就浪费掉了。

3、批量插入会造成主键不连续

为了保证主键id的唯一性,在申请自增id时,MySQL会对申请操作加锁。一般情况下,这个申请动作会很快。

对于一般的批量插入,比如insert into ... values(xxx),由于插入的Value个数可以提前计算得出,MySQL会一次性的申请足够数量的id,以保证性能。

但是对于insert into ... select 这种语句就有点麻烦了,由于无法确定到底需要申请多个主键id,如果插入一条申请一个的话,假设要插入100万条记录,那就得申请100万次,可想而知性能会有多么差劲。

所以对于这种批量插入的语句,MySQL采用了一种翻倍申请的优化策略:

语句执行时,第一次申请一个自增id,第二次申请2个自增id,第三次申请4个自增id...

即每次申请的数量都比上次多一倍,这样虽然会浪费一些自增id,但是可以保证插入的效率,从性能角度来看,是可以接受的。

自增id为什么不回退复用

大家可能会有点疑问,为什么自增id是一次性使用的?

其实原因也很简单,大家稍微一想就明白了。

假设有两个事务在同时执行,为了保证自增id的唯一性,MySQL会对申请动作加锁,然后两个事务各获得一个自增id。比如事务1申请到了自增id100,事务2申请到了自增id101。

当事务2成功提交,事务1因为某些原因回滚了。

如果我们要回退复用事务1的id,将AUTO_INCREMENT又设置成了100+1,那么下一个事务来申请自增id时,就会拿到101,而这时101已经被事务2用掉了,就会造成主键冲突。

当然我们也可以每次都让MySQL检查一下主键是否冲突,如果冲突就跳过这个id,但是这样一来,本来申请自增id这个很轻的动作就会变得很重,对性能的影响就会很大。

所以,从性能角度考虑,InnoDB只保证了主键id是大致递增的,而不保证是顺序递增的。

>更多相关文章
24小时热门资讯
24小时回复排行
资讯 | QQ | 安全 | 编程 | 数据库 | 系统 | 网络 | 考试 | 站长 | 关于东联 | 安全雇佣 | 搞笑视频大全 | 微信学院 | 视频课程 |
关于我们 | 联系我们 | 广告服务 | 免责申明 | 作品发布 | 网站地图 | 官方微博 | 技术培训
Copyright © 2007 - 2024 Vm888.Com. All Rights Reserved
粤公网安备 44060402001498号 粤ICP备19097316号 请遵循相关法律法规
');})();