读mysql45讲-自增主键不连续

最新推荐文章于 2024-07-20 13:05:46 发布

西埃斯迪恩-霖

最新推荐文章于 2024-07-20 13:05:46 发布

阅读量1k

点赞数

分类专栏：待删除文章标签： mysql database

本文链接：https://blog.csdn.net/ZRL1996/article/details/124073786

版权

待删除专栏收录该内容

82 篇文章 0 订阅

订阅专栏

自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。但实际上自增主键不能保证连续递增。

创建一个表：

CREATE TABLE `t` ( `id` int(11) NOTNULLAUTO_INCREMENT, 
`c` int(11) DEFAULTNULL, 
`d` int(11) DEFAULTNULL, 
PRIMARY KEY (`id`), 
UNIQUE KEY `c` (`c`) ) ENGINE=InnoDB;

然后插入一条记录insert into t values(null, 1, 1);再执行sho create table t 查看表信息：

在这里插入图片描述

自增值保存策略

可以看到，表定义里面出现了一个AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成id=2。这里的AUTO_INCREMENT的值并不会保存在表结构中，表结构的定义是存放在.frm文件中的，表结构定义不会保存AUTO_INCREMENT的值。
不同的引擎对于自增值的保存策略不同。

MyISAM引擎的自增值保存在数据文件中。
InnoDB引擎中在MYSQL5.7及之前的版本，自增值都是都保存在内存当中的，并没有持久化。所以当数据库重启之后，都是先去表中查询max(id)的最大值，然后在最大值的基础上加上1作为下次的自增值。如果插入一条记录，分配的id是10，那么这个时候的自增值就是11；然后删除id=10的记录，再重启一下，自增值又会变为了10。也就是重启可能会改变一个表的AUTO_INCREMENT值
InnoDB引擎在MYSQL8中将自增值的变更记录在了redo log中，重启的时候依据redo log的日志来恢复重启的值，其实InnoDB都是讲自增值保存在内存中的。

自增值修改机制

在MySQL里面，如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的
行为如下：

如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段；
如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是X，当前的自增值是Y。

如果X<Y，那么这个表的自增值不变；
如果X≥Y，就需要把当前自增值修改为新的自增值。

auto_increment_offset 和 auto_increment_increment是两个系统参数，分别用来表示自增的初始值和步长，默认值都是1。

现在表里已经有了(1,1,1)这条记录了，然后再执行一条sql：

insert into t values(null, 1, 1);

因为c字段有唯一性约束，所以这条语句会执行失败，但是再看下AUTO_INCREMENT的值会发现变为了3；

在这里插入图片描述

那就说明插入发生了错误但是并没有将AUTO_INCREMENT给回滚掉，唯一键冲突是导致自增主键id不连续的第一种原因。

同样地，事务回滚也会产生类似的现象，这就是第二种原因。

insert into t values(null,1,1); 
begin; 
insert into t values(null,2,2); 
rollback; 
insert into t values(null,2,2); 
//插入的行是(3,2,2)

在这里插入图片描述
预期是当前的AUTO_INCREMENT=3，并且(null,2,2)记录被回滚掉，应该插入的数据是(3,3,3)但是事务回滚也没有跟更新AUTO_INCREMENT的值，导致自增主键没有连续。

自增主键为什么不能回退

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯定要加锁，然后顺序申请。

事务A申请到自增值为1，事务B申请到的自增值为2，那么这个时候自增值应该就是3.
事务B执行成功，事务B执行失败进行回滚。如果这个时候要更新自增值，那么自增值就会变为2，但是表里已经有了一个id=2的记录；所以如果继续执行的话就会发生主键冲突。

为了解决这个主键冲突，有两种方法：

每次申请自增值的时候，先去判断表里有没有存在这个值，如果已经有了那就跳过。
把申请自增的锁范围扩大，只有当一个申请自增值的事务都完成之后才可以继续申请自增值。

可见，这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首，就是假设的这个“允许自增id回退”的前提导致的。因此，InnoDB放弃了这个设计，语句执行失败也不回退自增id。也正是因为这样，所以才只保证了自增id是递增的，但不保证是连续的。

自增锁的优化

在mysql5.0版本，自增锁的范围是语句级别，也就是一条sql语句中申请了自增值，只有这条sql语句执行完才可以继续申请，这个很明显会影响并发。

MySQL 5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是1。

这个参数的值被设置为0时，表示采用之前MySQL 5.0版本的策略，即语句执行结束后才释放锁；
这个参数的值被设置为1时：
- 普通insert语句，自增锁在申请之后就马上释放；
- 类似insert…select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
这个参数的值被设置为2时，所有的申请自增主键的动作都是申请后就释放锁。

至于【insert…select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；】这个是因为在主备同步数据那块会导致数据不一致的问题，我不想记录了所以就不复制了。

在普通的insert语句中，哪怕是insert后面有多个values的情况，因为是可以精确计算出需要多少个自增id的，所以会一次性申请，在申请完之后就会释放锁。

也就是说，批量插入数据的语句，之所以需要这么设置，是因为“不知道要预先申请多少个id”。
既然预先不知道要申请多少个自增id，那么一种直接的想法就是需要一个时申请一个。但如果一个select …insert语句要插入10万行数据，按照这个逻辑的话就要申请10万次。显然，这种申请自增id的策略，在大批量插入数据的情况下，不但速度慢，还会影响并发插入的性能。因此，对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

语句执行过程中，第一次申请自增id，会分配1个；
1个用完以后，这个语句第二次申请自增id，会分配2个；
2个用完以后，还是这个语句，第三次申请自增id，会分配4个；
依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。

把之前建立的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 t2 like t; 
insert into t2(c,d) select c,d from t; 
insert into t2 values(null, 5,5);

看下t2表的主键情况：

在这里插入图片描述

在执行【insert into t2(c,d) select c,d from t;】这条语句的时候一共申请了三次，第一次id=1，第二次id=2到id=3，第三次是id=4到id=7，但是只有到了4，所以在执行【nsert into t2 values(null, 5,5);】这条sql的时候，自增id的值就变为了9，这是自增主键不连续的第三个原因。