在设计MySQL表的时候，我们一般会设置一个自增的主键，让主键索引尽可能的保持递增的趋势，这样可以避免分页，让MySQL顺序写入，大大提高MySQL性能。但是自增主键只能粗略的增加，不能保证增加的顺序。我们建表后，通过showcreatetable命令可以看到MySQL定义了AUTO_INCREMENT来指定主键的增量值。在MySQL5.7之前，这个增量值是直接存储在内存中的。当服务器重启时，MySQL会读取表中的最大主键id，然后将最大值+1作为下一次递增的值。在MySQL8.0中，优化为存储在redolog中，从而实现增量值的持久化。那么有哪些情况可能会导致主键不连续增加呢？首先我们要知道，MySQL对主键增量的使用是一次性的，即每次获取增量值后，无论下一条语句能否执行成功，增量值都不会被回收。1、唯一索引冲突导致的主键不连续有时为了满足业务的需要，我们有时会为表中的字段设置唯一索引。但是当唯一索引发生冲突时会发生什么？以上面的user表为例，我们在name上设置了唯一索引。我们执行两次如下语句：INSERTintouservalues(null,'张三','123456');不难猜到第二次执行结果肯定会报错：上面我们已经提到，MySQL对于增量值的使用是一次性的，所以在第二次插入的时候，无论语句成功还是失败，增量值将被浪费。这时候如果我们执行正常的无冲突insert语句，就会发现主键id出现了空隙。2、事务回滚会导致主键不连续。类似于唯一索引冲突，当我们在事务中执行insert语句时，必然会向MySQL申请一个增量值作为主键id。如果最后一个事务没有提交，就会回滚，那么这个增量值自然就浪费了。3.批量插入会导致主键不连续。为了保证主键id的唯一性，mysql在申请自增id的时候会对申请操作加锁。一般情况下，这个应用动作会很快。对于一般的批量插入，比如insertinto...values(xxx)，由于可以提前计算出插入的Values个数，MySQL会一次性申请足够数量的ids来保证性能。但是对于insertinto...select语句，就有点麻烦了，因为无法判断是否需要申请多个主键id。如果插入一个申请一个，假设你要插入100万条记录，你要申请100万次。可想而知性能会有多差。因此，对于这种批量插入语句，MySQL采用了加倍申请的优化策略：语句执行时，第一次申请一个自增id，第二次申请两个自增id，而4次是第三次申请Auto-incrementid...也就是每次申请的数量都是上次的两倍。虽然会浪费一些自增id，但是可以保证插入的效率。从性能的角度来看，这是可以接受的。为什么自增id不恢复重用？你可能会有一些疑问。为什么自增id是一次性使用的？其实道理很简单，大家想一想就明白了。假设有两个事务同时执行，为了保证自增id的唯一性，MySQL会对应用action进行加锁，然后两个事务各自获得一个自增id。比如事务1申请了自增id100，事务2申请了自增id101。当事务2提交成功后，事务1因故回滚。如果我们想恢复重用事务1的id，设置AUTO_INCREMENT为100+1，那么下一个事务申请自增id时，会得到101，此时101已经被事务2使用了，导致主键冲突。当然我们也可以让MySQL每次都检查主键是否冲突，如果冲突就跳过id，但是这样一来申请自增id这个轻动作就会变得很重，影响在性能上会很大。因此，从性能上看，InnoDB只保证主键id是粗略递增，而不是顺序递增。
