自增主键

自增主键：

能够保证新数据能够按照主键递增的顺序进行插入。

自增值保存在哪？

每次执行insert语句之后，id+=1，那么这个id的值保存在哪？

• MyISAM引擎的表自增值保存在数据文件中。

• InnoDB引擎中的id自增值保存在内存中。根据MySQL版本号存在如下区别：

  • MySQL5.7及之前的版本：保存在内存中，并且不会进行持久化，每次插入新的数据都会加1。如果遇上重启则内存的最大自增值会丢失，开机后会扫描表获取当前所有行记录id的最大值max(id)，并把max(id)+1作为下一个记录的自增值。 重启可以刷新最准确的max(id)值，填补空洞(但为了保证有序性，中间的id空洞并不能解决)。

• MySQL8.0版本：将自增值的变更记录在redolog当中，如果发生重启就直接依靠redolog进行恢复。

自增值修改机制

auto_increment_offset：自增的初始值

auto_increment_increment：自增的步长

假设的当前表的自增值为AUTO_INCREMENT，执行insert语句时，自增字段的值根据以下规则决定：

• insert(null,4)插入时自增字段为0，null或未指定值，那么就把当前行的自增字段值设置为AUTO_INCREMENT，然后AUTO_INCREMENT自增。
• insert(X,3)插入行记录时指定自增字段的值X：
  • X>=AUTO_INCREMENT：则将当前自增值AUTO_INCREMENT更新为X+1，当前行的自增字段值为X
  • X<AUTO_INCREMENT：则只需要将当前行的自增字段值为X

自增值空洞

假设表t主键id字段自增，c字段作为唯一索引。以下几种情况可能会导致出现自增值不连续的情况：

场景一、唯一性约束导致自增值插入失败

此时数据库中的表t已经存在一行记录(1,1,1)

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

1. 执行器调用InnoDB引擎写入(0，1，1)这行新的数据
2. InnoDB发现自增的字段为0、则获取当前表t的自增字段AUTO_INCREMENT=2
3. 传入的数据变成(2,1,1)
4. AUTO_INCREMENT字段自增
5. 执行(2,1,1)数据插入操作。此时才发现表中c字段值1已经出现，因此报Duplicate key错误，返回

可以发现上面的过程中id=2这行数据并没有插入，因此存在“空洞”，主键值不连续。

而造成空洞的主要原因在于，先进行表自增值自增，然后才会执行语句判断唯一性约束。

场景二、事务回滚

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);

1. 第二个insert语句插入(2,2,2)后，AUTO_INCREMENT=3
2. 回滚后，表中会删除(2,2,2)这条数据，但是AUTO_INCREMENT自增值并不会回滚。
3. 第三个insert语句插入(3,2,2)

而这里MySQL之所以不将自增值回滚，主要是为了提升性能。这里如果将“允许自增值回滚”这一条件带入以下多并发线程的场景，分析如下：

1. A线程插入id=2,B线程插入id=3，当前AUTO_INCREMENT=4
2. B线程成功提交，而A线程违反唯一键约束，事务回滚，此时根据上面的条件AUTO_INCREMENT会回滚变成2
3. 接下来，C线程连续插入两条insert语句，分别插入id=2，id=3，就会导致出现主键冲突。
4. 而要解决上述主键冲突，要么就是每次生成id时判断表中是否已经存在，要么就是按照事务提交顺序释放自增值的锁。无论是哪一种无疑都非常影响MySQL的性能。

综上，如果MySQL允许自增值随着redolog进行回滚，那么在多并发情况下，就不能够保证AUTO_INCREMENT一定大于当前表中的max(id)。

场景三、批量数据插入

MySQL5.1.22引入一个自增锁的策略，通过设置innodb_autoinc_lock_mode参数值，采用不同的自增锁：

• 设置为0：表示语句执行结束后才会释放锁
• 设置为1：
  • 如果是普通insert语句：申请完毕后直接释放
  • 如果是insert ... select这样的批量插入语句，自增锁会等待语句执行完毕后才被释放
• 设置为2：所有自增值申请完毕后立即释放

inert into (),()

insert插入多个value的情况下，因为MySQL可以计算出需要多少个id，因此一次性申请完后多个id后，直接释放锁。

insert ... select批量插入

insert ... select语句执行时，如果是申请完一个id就释放锁，那么在多并发情况下这条批量语句生成的id并不一定连续，因为可能会受到其它session的影响。而binlog为statement在恢复时可以保证连续，这样就会造成主备不一致。

解决方案有两种：

✨将innodb_autoinc_lock_mode设置为1，让自增锁执行完语句再释放。

✨在生产上，从并发角度考虑更多会采取这种做法：innodb_autoinc_lock_mode设置为2，同时binlog设置为row。这样保证备库应用binlog时直接利用主库记录的id值插入数据(允许乱序插入id+主备一致)。

MySQL批量申请自增id策略

批量申请自增id策略：每一次申请到的id个数是上一次申请的两倍，当前申请的id用完后才会进行下一轮的申请。

insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

假设当前t表只有四行数据：那么执行过程如下：

1. 首先执行批量插入语句：
   • 第一行数据，第一次申请i一个id，id=1;并将id=1分配给第一行数据
   • 第二行数据，由于第一次申请的id已经用完，因此第二次申请两个id，id=2,3。并将id=2分配给第二行数据
   • 第三行数据，第二次申请的id还没用完，不申请新的id。将id=3分配给第三行
   • 第四行数据，由于第二次申请的id用完了，因此第三次申请四个id，id=4,5,6,7。并将id=4分配给第四行数据
   • 剩余id=5,6,7在insert...select批量语句已经用不到了，因此直接丢弃
2. 执行当行插入insert，此时申请到的id就是id=8。导致出现自增值空洞。

自增值的主备同步问题

场景：多并发情况下线程A先执行insert(null)，后写binlog；线程B后执行insert(null)，先写binlog。那么binlog恢复时，先恢复执行B线程的插入记录，那么这个自增id会不会与主库不一致？

每个insert语句执行之前，会固定有一句SET INSERT_ID=2,提前设置好当前的自增值，而这个设置自增ID的语句也会同时记录进binlog。

因此binlog恢复时，每个insert语句前都会紧跟着一个记录使用到的自增id的设置语句。