作者:cndz 围观群众:631 更新于 标签:mysqlmysql自增主键
MySQL版本:8.0
数据库表:T (主键id,唯一索引c,普通字段d)
如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不能保证连续递增。
MySQL5.7版本
在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。
MySQL8.0之后版本
在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。
可以通过看表详情查看当前自增值,以及查看表参数详情AUTO_INCREMENT值(AUTO_INCREMENT就是当前数据表的自增值)
在表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);
流程图如下所示
流程步骤:
大家可以发现,在这个流程当中是先进行自增值的+1,在进行新增语句的执行的。大家可以发现这个操作并没有进行原子操作,如果SQL语句执行失败,那么自增是不是就不会连续了呢?
当我执行以下SQL语句时
insert into t values(null, 1, 1);
第一次我们可以进行新增成功,根据自增值的修改机制。如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;
当我们第二次在执行以下SQL语句时,就会出现错误。因为我们表中c字段是唯一索引,会出现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),出现了自增主键不连续的情况。
自增锁是一种比拟非凡的表级锁。并且在事务向蕴含了 AUTO_INCREMENT 列的表中新增数据时就会去持有自增锁,假如事务 A 正在做这个操作,如果另一个事务 B 尝试执行 INSERT语句,事务 B 会被阻塞住,直到事务 A 开释自增锁。
这个参数的值被设置为 0 时,表示采用之前 MySQL 5.0 版本的策略,即语句执行结束后才释放锁;
传统模式他可以保证数据一致性,但是如果有多个事务并发的执行 INSERT 操作,AUTO-INC的存在会使得 MySQL 的性能略有降落,因为同时只能执行一条 INSERT 语句。
这个参数的值被设置为 1 时:普通 insert 语句,自增锁在申请之后就马上释放;类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
间断模式他可以保证数据一致性,但是如果有多个事务并发的执行 INSERT 批量操作时,就会进行锁等待状态。如果我们业务插入数据量很大时,这个时候MySQL的性能就会大大下降。
这个参数的值被设置为 2 时,所有的申请自增主键的动作都是申请后就释放锁。
穿插模式他没有进行任何的上锁设置。在一定情况下是保证了MySQL的性能,但是他无法保证数据的一致性。如果我们在穿插模式下进行主从复制时,如果你的binlog格式不是row格式,主从复制就会出现不一致。具体可以了解一下我的这篇文章MySQL是如何保证主从一致
在MySQL8.0之后版本,已经默认设置为 innodb_autoinc_lock_mode=2 , binlog_format=row.。 这样更有利与我们在 insert … select 这种批量插入数据的场景时,既能提升并发性,又不会出现数据一致性问题。
原文链接:https://blog.csdn.net/qq_48157004/article/details/128356734