乐趣区

关于java:面试官MySQL-自增主键一定是连续的吗大部分人都会答错

测试环境:

MySQL 版本:8.0

数据库表:T(主键 id,惟一索引 c,一般字段 d)

如果你的业务设计依赖于自增主键的连续性,这个设计假如自增主键是间断的。但实际上,这样的假如是错的,因为自增主键不能保障间断递增。

举荐一个开源收费的 Spring Boot 实战我的项目:

https://github.com/javastacks/spring-boot-best-practice

一、自增值的属性特色:

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=2binlog_format=row.。这样更无利与咱们在 insert … select 这种批量插入数据的场景时,既能晋升并发性,又不会呈现数据一致性问题。

版权申明:本文为 CSDN 博主「又 欠」的原创文章,遵循 CC 4.0 BY-SA 版权协定,转载请附上原文出处链接及本申明。原文链接:https://blog.csdn.net/qq_48157004/article/details/128356734

近期热文举荐:

1.1,000+ 道 Java 面试题及答案整顿(2022 最新版)

2. 劲爆!Java 协程要来了。。。

3.Spring Boot 2.x 教程,太全了!

4. 别再写满屏的爆爆爆炸类了,试试装璜器模式,这才是优雅的形式!!

5.《Java 开发手册(嵩山版)》最新公布,速速下载!

感觉不错,别忘了顺手点赞 + 转发哦!

退出移动版