众所周知，自增主键可以让聚簇索引尽可能保持递增顺序插入，避免随机查询，从而提高查询效率。但实际上，MySQL的自增主键并不能保证一定要不断自增。我们举个例子看看，建表如下：自增值保存在哪里？使用insertintotest_pkvalues(null,1,1)插入一行数据，然后执行showcreatetable命令可以看到表的结构定义：上表的结构定义保存在本地文件中后缀为.frm，在MySQL安装目录下的data文件夹下可以找到这个.frm文件：从上面的表结构可以看到表定义中有一个AUTO_INCREMENT=2，意思是插入时下一次数据，如果需要自动生成自增值，则生成id=2。但需要注意的是，自增值不会保存在这个表结构中，即.frm文件中。不同的引擎对自增值的存储策略不同：1）MyISAM引擎的自增值保存在数据文件中2）InnoDB引擎的自增值实际保存在内存中，不持久化。第一次打开表时，会找到自增max(id)的最大值，然后将max(id)+1作为表的当前自增值。比如：我们表中当前数据行中最大的id是1，AUTO_INCREMENT=2对不对？这时候我们删除id=1的行，AUTO_INCREMENT还是2。但是如果马上重启MySQL实例，重启后这张表的AUTO_INCREMENT会变成1。也就是说，MySQL重启可能会修改表的AUTO_INCREMENT值。以上是我本地MySQL5.x版本的实验。其实在MySQL8.0版本之后，将自增的变化记录放在了redolog中，提供了自增持久化的能力，即实现了“如果发生重启，表的自增值可以根据redolog恢复到MySQL重启前的值。”也就是说，对于上面的例子，重启实例后表的AUTO_INCREMENT还是2。了解了MySQL自增存储在哪里，我们再来看一下自增的修改机制，并以此来介绍第一种非连续自增的场景。自增不连续场景1在MySQL中，如果字段id定义为AUTO_INCREMENT，插入一行数据时，自增的行为如下：如果id字段指定为0、null或未指定值时插入数据，则将本表当前的AUTO_INCREMENT值填入自增字段；如果插入数据时id字段指定了具体的值，则直接使用语句中指定的值。根据要插入的值与当前自增值的大小关系，自增值的变化结果也会不同。假设待插入的值为insert_num，当前自增值为autoIncrement_num：如果insert_num=autoIncrement_num，则需要将当前自增值改为新的自增值即插入的id为100，则当前自增值为90，insert_num>=autoIncrement_num，那么自增值就会被修改为新的自增值，即101，有这个必要吗？没有，学过分布式id的朋友一定知道，为了避免两个库生成的主键冲突，我们可以让一个库的自增id全部为奇数，而另一个库的自增id其他图书馆都是偶数。偶数其实是由auto_increment_offset和auto_increment_increment这两个参数决定的，分别用来表示自增的初始值和步长，默认值为1。因此，生成新自增的步长上面例子中的自增值其实是这样的：从auto_increment_offset开始，以auto_increment_increment为步长，不断叠加，直到找到第一个大于100的值作为新的自增值。所以，这种情况下，自增值可能是102、103等，会导致主键id不连续。比较遗憾的是，即使自增初始值和步长两个参数都设置为1，自增主键id也不一定保证主键连续。自增是不连续的。场景2比如我们现在向表中插入一条(null,1,1)记录，生成的主键是1，AUTO_INCREMENT=2对不对？这时候我再执行一条命令插入(null,1,1)，显然会报错Duplicateentry，因为我们设置了一个唯一的索引字段a:然而，你会惊讶的发现，虽然插入失败了，自增值还是从2增加到3！为什么？我们来分析一下这条插入语句的执行过程：执行者调用InnoDB引擎接口插入一行记录(null,1,1)；InnoDB发现用户没有指定自增id的值，则获取表test_pk的当前自增值2；将传入记录改为(2,1,1)；将表的自增值改为3；继续插入数据，因为已经有a=1的记录，所以报Duplicatekey错误，从语句返回可以看出，自增修改操作在实际插入数据操作之前。真正执行这条语句的时候，因为和唯一键a冲突，id=2行没有插入成功，但是自增值也没有改回来。因此，之后插入新的数据行时，得到的自增id为3，也就是说出现了自增主键不连续的情况。至此，我们列出了自增主键不连续的两种情况：自增初始值和自增步长未设置为1；唯一键冲突。另外，事务回滚也会造成这种情况。自增值不连续。场景3我们现在表中有一行(1,1,1)条记录，AUTO_INCREMENT=3：我们先插入一行数据(null,2,2)，也就是(3,2,2)，然后AUTO_INCREMENT变成4：再执行这么一段SQL：虽然我们插入了一条(null,3,3)记录，但是使用rollback回滚了，所以数据库中没有这条记录：事务回滚时，self-增量没有相同的回滚！如下图，自增值还是从4顽固地增加到5：所以此时我们插入一条数据(null,3,3)时，主键id会自动赋值为5:那么，为什么在唯一键冲突或者回滚的时候，MySQL没有把表的自增值改回来呢？如果再往回走，自增的id不会不连续吗？其实这样做的主要原因是为了提高性能。我们直接用反证的方法来验证一下：假设MySQL在事务回滚时将自增值改回来，会发生什么？现在有两个并行执行的事务A和B。申请自增的时候，为了防止两个事务申请同一个自增id，一定要加锁，然后顺序申请，对吧？假设事务A申请了id=1，事务B申请了id=2，那么此时表t的自增值为3，继续往下执行。事务B提交正确，但是事务A存在唯一键冲突，即id=1的行插入失败，那么如果允许事务A回滚自增id，即更改表当前自增值回1，那么就会出现这样的情况：表中已经有id=2的行，当前自增id值为1。继续执行会申请id=2。这时候insert语句会报错“主键冲突”。为了解决这种主键冲突，有两种方法：每次申请一个id之前，先判断表中是否已经存在该id，如果存在则跳过该id，扩大自增id的锁范围，并且必须等到一个事务执行完成并提交后，下一个事务才能申请自增id。显然，以上两种方式的成本都比较高，会造成性能问题。这样做的原因是我们假设“允许自增idfallback”。因此，InnoDB放弃了这种设计，如果语句执行失败，不回退到自增id。也正是因为如此，只有自增的id才能保证自增，而不是连续的。综上分析了三种不连续自增的场景，还有第四种场景：批量插入数据。自增不连续场景4对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：在语句执行过程中，第一个申请自增id的会分配一个；一个用完后，语句第二次申请自增id会分配2个；2用完后还是一样的语句，第三次申请自增id分配4；以此类推，同一条语句申请自增id，每次申请的自增id数量都是上一次的两倍。注意，这里说的批量插入数据，不是普通插入语句中的多值值！！！，因为这种语句在申请自增id的时候可以准确的计算出需要多少个id，然后一次性去申请。申请完成后，就可以释放锁了。对于insert...select,replace...select和loaddata等语句，MySQL不知道需要申请多少个ID，所以采用了这种批量申请的策略。毕竟一一申请太慢了。.例如，假设我们当前表有如下数据：我们创建一个与当前表test_pk具有相同结构定义的表test_pk2：然后使用insert...select将数据批量插入到teset_pk2表中：如你所愿看到，数据导入成功。我们看一下test_pk2的自增值：如上分析，是8，而不是6。具体来说，insert...select实际上是向表(11)(22)(3)中插入5行数据3)(44)(55)。但是这5行数据是申请了3次的自增id。结合批量申请策略，每次申请的自增id数量是上一次申请的两倍，所以：第一次申请一个id：id=1第二次分配了两个id：id=2andid=3和第三次分配了四个id：id=4,id=5,id=6,id=7由于这个语句实际上只使用了5个id，所以id=6和id=7就浪费了.之后执行insertintotest_pk2values(null,6,6)，实际插入的数据为(8,6,6)：总结总结一下不连续自增的四种场景：自增初始值和自增stepThelongsettingisnot1uniquekeyconflicttransactionrollbackbatchinsert(如insert...select语句)
