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

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

设计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是大致递增的,而不保证是顺序递增的。​

责任编辑:姜华 来源: 今日头条
相关推荐

2021-02-19 10:02:57

HTTPSJava安全

2022-07-06 13:48:24

RedisSentinel机制

2023-12-06 09:10:28

JWT微服务

2021-01-21 07:53:29

面试官Promis打印e

2020-10-24 15:50:54

Java值传递代码

2021-12-20 10:30:33

forforEach前端

2024-10-24 09:22:30

2023-12-20 14:35:37

Java虚拟线程

2023-11-30 08:16:19

SpringjarTomcat

2020-05-06 15:02:58

MySQL数据库技术

2022-12-22 14:32:37

JavaScript编程语言

2023-06-05 07:57:53

Kafka消息事务消息

2023-07-05 08:17:38

JDK动态代理接口

2020-04-21 15:59:50

MySQL自增主键数据库

2021-09-28 17:48:20

MySQL主键索引

2024-03-13 07:53:57

弱引用线程工具

2021-09-07 10:44:33

Java 注解开发

2020-12-23 13:29:15

微服务架构面试官

2020-08-24 10:55:41

数据库双写代码

2024-01-11 08:12:20

重量级监视器
点赞
收藏

51CTO技术栈公众号