MySQL自增主键为什么不连续
CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
自增主键好处？
自增主键可以让主键索引尽量的保持递增顺序插入，避免页分裂，索引更加紧凑。
自增主键保存在何处？
insert into t values(null, 1, 1);
show create table t\G
可以看到表定义中出现了AUTO_INCREMENT=2，表示下一次插入数据时如果需要自动生成自增值，那么id便是2。
不同的引擎对于自增值的保存策略不同：
- MyISAM引擎的自增值保存在数据文件中
- InnoDB引擎的自增值保存在内存里，但是在MySQL8.0以后，该自增值才可以被持久化：MySQL5.7以前，自增值没有持久化每次重启后第一次打开表的时候，会找自增值的最大值max(id)，然后将最大值加1作为这个表的自增值；MySQL8.0版本会将自增值的变更记录在redo log中，重启时依靠redo log恢复。
自增值的修改机制
自增值的修改行为如下：
- 如果插入数据时id字段指定为0、null或者未指定值，那么就把该表的AUTO_INCREMENT值填到自增字段
- 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值
自增值的变更情况如下，假设某次要插入的值是X，当前的自增值是Y：
- 如果X<Y，表的自增值不变
- 如果X>=Y，需要把当前自增值修改为新的自增值
新的自增值生成算法
从auto_increment_offset（默认值是1）开始，以auto_increment_increment（默认值是1）为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值
唯一键冲突导致自增主键不连续
insert into t values(null, 1, 1);
- 执行器调用InnoDB引擎写入一行，传入的这一行的值是(0,1,1)
- InnoDB发现用户没有指定id，获取表t当前的自增值2
- 将传入的行改为(2,1,1)
- 将表的自增值改为1
- 继续执行插入数据操作，但是由于c=1的记录已经存在，所以会返回Duplicat key error，语句返回
上述执行过程可以看出，自增值的修改是在真正插入数据的操作之前，如果数据真正插入的时候异常，也不会将自增值改回去。
事务回滚导致自增主键不连续
set autocommit=0;
begin;
insert into t values(null, 2, 2);
rollback;
show create table t\G
可以看到自增主键已经从3变成4，但是并没有因为事务的回滚回退。
事务回滚为什么自增值不能回退
两个并行的事务在申请自增值的时候，为了避免两个事务申请到相同的自增id，需要加锁按照顺序申请，如果自增值可以回退需要做一些特殊处理：
- 每次申请id之前，判断表里此id是否存在（去主键索引树上判断该id是否存在）
- 把自增id的锁范围扩大，必须等到一个事务提交后才，下一个事务才可以申请id，锁粒度太大，系统并发能力极大下降
为了避免上述的性能消耗，InnoDB即使语句执行失败也不回退自增id。
批量插入导致自增值不连续
自增值锁不是一个事务锁，每次申请完就释放，方便其他事务获取自增值。
参数innodb_autoinc_lock_mode的不同会影响锁的释放时机：
- 该参数如果为0，语句执行结束后释放锁
- 设置为1：普通insert语句，自增锁在申请后马上释放；insert...select这样的批量插入语句等语句结束后才释放
- 设置为2：申请后就释放锁
show global variables like 'innodb_autoinc_lock_mode';
如果innodb_autoinc_lock_mode值设置为1，对于insert...select这种无法提前知道申请多少个id，那就在需要的时候申请1个，如果有10w行数据那就需要申请10万次，对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：
- 语句执行过程中，第一次申请自增id，分配1个
- 1个用完以后，第二次申请，会分配2个
- 2个用完以后，第三次申请，会分配4个
- 依此类推，每次申请都是上一次的两倍（最后一次申请不一定全部使用）
在innodb_autoinc_lock_mode参数为1的情况下，如果大批量插入数据也会造成自增id的不连续。