MySQL--MySQL小知识点：自增主键为什么不是连续的？自增id用完怎么办？--其乐融融的IT技术小站

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

今天我们就来说说这个问题，看看什么情况下自增主键会出现 “空洞”?

为了便于说明，我们创建一个表t，其中id是自增主键字段、c是唯一索引。

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;

自增值保存在哪儿?

在这个空表t里面执行insert into t values(null, 1, 1);插入一行数据，再执行show create table命令，就可以看到如下图所示的结果：

图1 自动生成的AUTO_INCREMENT值

可以看到，表定义里面出现了一个AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成id=2。

其实，这个输出结果容易引起这样的误解：自增值是保存在表结构定义里的。实际上，表的结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值。

不同的引擎对于自增值的保存策略不同。

MyISAM引擎的自增值保存在数据文件中。
InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。

举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。

也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。

在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。理解了MySQL对自增值的保存策略以后，我们再看看自增值修改机制。

自增值修改机制

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

1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段;

2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是X，当前的自增值是Y。

1. 如果X

2. 如果X≥Y，就需要把当前自增值修改为新的自增值。

自增值的修改时机

假设，表t里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

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

这个语句的执行流程就是：

1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);

2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2;

3. 将传入的行的值改成(2,1,1);

4. 将表的自增值改成3;

5. 继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate keyerror，语句返回。

对应的执行流程图如下：

图2 insert(null, 1,1)唯一键冲突

可以看到，这个表的自增值改成3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键c冲突，所以id=2这一行并没有插入成功，但也没有将自增值再改回去。所以，在这之后，

唯一键冲突是导致自增主键id不连续的第一种原因。同样地，事务回滚也会产生类似的现象，这就是第二种原因。

自增id用完怎么办?

MySQL里有很多自增的id，每个自增id都是定义了初始值，然后不停地往上加步长。虽然自然数是没有上限的，但是在计算机里，只要定义了表示这个数的字节长度，那它就有上限。比如，无符号整型(unsigned int)是4个字节，上限就是232 -1。

既然自增id有上限，就有可能被用完。但是，自增id用完了会怎么样呢?

表定义自增值id

表定义的自增值达到上限后的逻辑是：再申请下一个id时，得到的值保持不变。我们可以通过下面这个语句序列验证一下：

create table t(id int unsigned auto_increment primary key) 
auto_increment=4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOTNULLAUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/
insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'

可以看到，第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变(还是4294967295)，就导致了第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主

键冲突错误。

4294967295不是一个特别大的数，对于一个频繁插入删除数据的表来说，是可能会被用完的。因此在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成8个字节的bigint unsigned。

InnoDB系统自增row_id

如果你创建的InnoDB表没有指定主键，那么InnoDB会给你创建一个不可见的，长度为6个字节的row_id。InnoDB维护了一个全局的dict_sys.row_id值，所有无主键的InnoDB表，每插入一行数据，都将当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值加1。

实际上，在代码实现时row_id是一个长度为8字节的无符号长整型(bigint unsigned)。但是，InnoDB在设计时，给row_id留的只是6个字节的长度，这样写到数据表中时只放了最后6个字节，所以row_id能写到数据表中的值，就有两个特征：

1. row_id写入表中的值范围，是从0到248 -1;

2. 当dict_sys.row_id=248 时，如果再有插入数据的行为要来申请row_id，拿到以后再取最后6个

字节的话就是0。

也就是说，写入表的row_id是从0开始到248 -1。达到上限后，下一个值就是0，然后继续循环。当然，这个值本身已经很大了，但是如果一个MySQL实例跑得足够久的话，还是可能达到这个上限的。在InnoDB逻辑里，申请到row_id=N后，就将这行数据写入表中;如果表中已经存在row_id=N的行，新写入的行就会覆盖原有的行。