乐趣区

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

jiezi

测试环境：

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=2，binlog_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 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

退出移动版