我们不能失去信仰

我们在这个世界上不停地奔跑...

0%

MySQL自增主键id不连续的原因

发表于 更新于 分类于

前言

以下内容均在 MySQL 的 InnoDB 存储引擎下完成

主键自增取值策略

  • 如果插入数据时 Id 字段指定为 0、Null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填充到自增字段中。

tips：AUTO_INCREMENT 的值表示下一个插入行的自增值。

  • 如果插入数据时 Id 字段指定了具体的值，就直接使用语句里指定的值。根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是X，当前的自增值是Y：

  1. 如果 X < Y, 那么这个表的自增值不变。

  2. 如果 X >= Y，就需要把当前自增值修改为新的自增值。新的自增值生成算法是： 从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值。

    补充： auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认都是1。

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

导致自增主键不连续的原因

  1. 唯一键 冲突，插入失败后，自增主键值 已经更新，不会回退
  2. 同样的，事务回滚也会产生类似的现象，事务回滚不糊回退 自增主键值
  3. 自增锁优化的存在

MySQL 没有把表的自增值改回去的原因

其实 MySQL 这么设计是为了提高性能。在并发事务时，可能会因为回退操作而导致事务执行失败。

如果通过其他复杂的方法来回退以及保证重新生成自增key的正确性，可能需要对自增 Id加锁，并且需要等待上一个事务执行完成后，才能确定当前事物的自增 Id 值，会大大降低并发度。

因此，InnoDB 放弃了这个设计，语句执行失败也不会回退自增 Id。所以自增 Id 是递增的，但不保证是连续的。

自增 Id 锁并不是一个事务锁（事务锁需要等到事务结束后才能释放），而是每次申请完就马上释放，以便允许别的事务再申请。

自增锁的优化

MySQL 5.0 版本的时候，自增锁的范围是语句界别。

也就是说，如果一个语句申请了一个表的自增锁，这个锁会等语句执行结束后才释放，显然，这样设计会影响并发度。

MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode ， 默认值为1。

  1. 这个参数设为 0 时，表示采用 MySQL 5.0 之前版本的策略，即语句执行结束后才释放锁。
  2. 这个参数的值设置为 1 时。普通 insert 语句，自增锁在申请之后就马上释放。在类似 insert … select 这样的批量插入数据的语句，由于 MySQL 无法精确算出需要多少个自增 Id ，所以自增锁还是需要等到语句执行完成后才被释放。
  3. 这个参数设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁，但是这样会有一个问题就是 binlog 格式需要设置成 row 格式，否则会出现 binlog 数据不一致的问题。

  • 默认设置为 1 的情况下，为什么 insert … select 要使用语句级别的锁？为什么这个参数的默认值不是2？

    这么设计还是为了数据的一致性， 在原库执行的时候可能存在事物间交叉执行，导致自增 Id 不连续，如果 binlog 的格式是 statement 格式，会造成恢复出来的数据和主库的不一致。

    所以这里建议为了提高并发度，将 innodb_autoinc_lock_mode 设置为2 并且 binlog 的格式改为 row 格式。

  • 自增锁的优化：

    在执行批量插入的时候，在普通的 insert 语句里面包含多个 value 值的情况下，即使 innodb_autoinc_lock_mode 设置为1，也不会等语句执行完成才释放锁。因为这类语句在申请自增 Id 的时候，是可以精确计算出需要多少个 Id 的，然后一次性申请，申请完成后锁就可以释放了。

    也就是说，批量插入数据的语句，之所以需要这么设置，是因为”不知道预先申请多少个 Id“。

    既然预先不知道要申请多少个自增 Id，如果是需要一个一个申请的话，按照这个逻辑就需要重复 n 次。显然，这种申请自增 Id 的策略，在大批量数据插入的情况下，不但速度慢，还会影响并发插入的性能，这是一个复杂度是 O(n) 的策略。

    因此，对于批量插入数据的语句，MySQL 有一个批量申请自增 Id 的策略，以空间换时间的做法，复杂度为O(logn) 。

    1. 语句执行过程中，第一次申请自增 Id，会分配1个
    2. 1个用完后，这个语句第二次申请，会分配2个
    3. 2个用完后，还是这个语句，第三次申请自增 Id，会分配4个
    4. 依次类推，同一个语句去申请自增 Id，每次申请到的自增 Id 个数都是上一次的两倍。

由于自增锁的优化，这个也是出现自增主键 Id 不连续的原因之一。