深入谈谈MySQL中的自增主键

MySQL的主键可以是自增的，那么如果在断电重启后新增的值还会延续断电前的自增值吗？自增值默认为1，那么可不可以改变呢？下面就说一下 MySQL的自增值。

特点

保存策略

1、如果存储引擎是 MyISAM，那么这个本文来源gaodai$ma#com搞$代*码网2自增值是存储在数据文件中的；

2、如果是 InnoDB引擎，1）在 5.6之前是存储在内存中，没有持久化，在重启后会去找最大的键值，举个例子，如果一个表当前数据行里最大 id是10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10；

　　　　　　　　　 2）在 8.0开始，自增值就保存在 redo log中，重启后会从 redo log中读取之前保存的自增值。

自增值的确定

1、如果插入数据时 id字段指定为0、null或未指定，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段，并且会以auto_increment_offset作为初始值，auto_increment_increment为步长，找出第一个大于当前自增值的值作为新的自增值。

2、如果插入的数据的 id字段指定了具体的值，就直接使用语句里的值。

在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突。

自增值的修改

假设某次要输入的值是 X，当前的自增值是 Y。那么：

1、如果 X<Y，那么这个表的自增值不变；

2、如果X≥Y，那么就把当前自增值修改为新的自增值。

执行过程

假设有表t ，id是自增主键，在已有 (1,1,1)的情况下，插入一条 (null,1,1)，那么执行过程就如下：

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

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

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

4、将表的自增值改成 3；

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

带来的问题

由于上面说得这种特性，在一些场景中会出现主键不连续的现象。

场景1：添加数据时唯一索引重复

在 c列索引重复后，原本要分配的主键值 2就会被丢弃，而下次再次插入就从 2 开始计算，也就变成了 3。

场景2：事务回滚

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)

在第二条语句回滚后分配给其的主键 2也会被丢弃。

场景3：特殊批插入优化导致

这里说得特殊的批插入指的是insert … select、replace … select 和 load data 语句。为什么说这些语句可能会导致？这就要说到自增锁了。首先自增锁是为了避免多线程冲突，因为在多线程下，如果同时有多个线程来获取自增值，那么就可能会导致同一个自增值被分配给多条记录，导致逐渐冲突。所以需要自增锁，而为什么前面说得这些批插入语句会导致主键不连续，在下面自增锁部分会说到。

喜欢 (0)赏

[搞代码]

分享 (0)