为什么mysql自增主键不是连续的

一前言

提出这个问题，是因为在工作中发现 mysql 中的 user 表的 id 默认是自增的，但是数据库存储的结果却不是连续的。

user 表结构：

create table `user` ( 
	`id` bigint(20) unsigned not null auto_increment comment '递增id', 
	`name` varchar(20),
	`create_time` datetime default current_timestamp comment '创建时间', 
	`update_time` datetime default current_timestamp comment '更新时间', 
	primary key (`id`),unique key `idx_name` (`name`)) 
engine=innodb auto_increment=1 default charset=utf8mb4 comment='user表'

user 表存储：

二自增值存储说明

1.1 myisam 引擎的自增值保存在数据文件中。

1.2 innodb 引擎的自增值，其实是保存在了内存里，并且到了 mysql 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 mysql 重启前的值”，具体情况是：

在 mysql 5.7 及之前的版本，自增值保存在内存里。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id) + 1 作为这个表当前的自增值。
在 mysql 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

三自增值修改机制

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

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

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

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

新的自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 x 的值，作为新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。

四自增值修改时机

insert into user values(null, '张三');

1 当执行上述 sql 时，执行器调用 innodb 引擎接口写入一行，传入的这一行的值是 (0,”张三”)；

2 innodb 发现 sql 没有指定自增 id 的值，获取 user 表当前的自增值 2；

3 将传入的行的值改成 (2,”张三”);

4 将表的自增值改成 3；

5 继续执行插入数据操作。

五导致自增值不连续的原因

5.1 唯一键冲突

假设执行 sql 的时候 user 表 id = 10，此时在内存中的自增 id 为11，此时发生唯一键冲突写库失败，则 user 表没有 id = 10 这条记录，之后 id 从11开始写入，因此 id 是不连续的。

5.2 事务回滚

假设同时需要对 user、staff 表进行写库操作，执行 sql 的时候 user 表 id = 10，此时在内存中的自增 id 为11；staff 表 id = 20，此时内存中的自增 id 为21，一旦事务执行失败，事务回滚，写库失败，则 user 表没有 id = 10 这条记录，staff 表没有 id = 20 这条记录，user 表从11开始写入，staff 表从21开始写入，如此产生 id 不连续的现象。

5.3 批量写库操作

对于批量插入数据的语句，mysql 有一个批量申请自增 id 的策略：

1. 语句执行过程中，第一次申请自增 id，会分配 1 个；

2. 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；

3. 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；

依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

假设批量往 user 表中写入四条记录，则这四条记录将分为三次申请id，

第一次分配到 id = 1，第二次分配到 id = 2、3 ，第三次分配到 id = 4、5、6、7，当批量写入四条记录之后，id = 1、2、3、4将会入库，但是 id = 5、6、7就被废弃了，下一个 id 从8开始。

六参考文档

https://time.geekbang.org/column/intro/139

到此这篇关于为什么mysql自增主键不是连续的的文章就介绍到这了,更多相关mysql自增主键连续内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

为什么mysql自增主键不是连续的

目录

一 前言

二 自增值存储说明

三 自增值修改机制

四 自增值修改时机

五 导致自增值不连续的原因