Mysql 系列 | 自增 ID

自增主键在每张表中都会存在，即使没有定义也会自动生成。

自增 ID 除了我们常说的表 ID 外，还有 row_id、thread_id、table_id 等。

下面只考虑每张表的 ID。

存在哪里

• MyISAM 引擎，存在数据文件中

• InnoDB 引擎，

  • Mysql5.7 前存在内存中，没有持久化。每次重启后，第一次打开表先找主键最大值，加一后作为当前自增值。会导致修改了重启前的 AUTO_INCREMENT 值

  • Mysql8.0 记录在 redo log 中，重启后，从 redolog 中恢复

自增机制

• 插入一行数据时，如果 id 是 0、null 或未指定，则把表当前的 AUTO_INCREMENT 值填到自增字段。如果语句中指定了具体值，则直接用给定的值

• 比较插入的 id 和当前自增值，如果 id 小于自增值则自增值不变；否则修改自增值为最新

• 新自增值的生成算法，从 auto_increment_offset（自增初始值） 开始，以 auto_increment_increment（步长） 为步长，持续叠加，直到找到第一个大于 id 的值，作为新的自增值

  - 自增初始值和步长默认都是 1
  

• 补充好要插入的数据，然后执行插入，

  • 这个过程如果语句出错，比如加了唯一索引的字段违反字段唯一原则时，插入失败

  • 修改自增值是在真正插入数据之前

  • 插入失败后，自增值的变更不会回滚，导致 ID 出现空洞

  • 当操作回滚后，自增值不会退回去，同理也会导致 ID 不连续

批量自增 ID

• 对于批量插入数据，Mysql 中有批量申请自增 ID 的策略

• 第一次分配 1 个，用完后再分配 2 个，再用完后第三次分配 4 个，以此类推。

• 比如批量插入 5 条数据，前两次申请的 ID 用完后，第三次分配了 4 个，但实际只用了 2 个。这个时候，分配的 ID 不会收回去，因此也会导致 ID 不连续。

自增主键不连续

• 从上面的分析可以看出，不连续有三个原因

  • 插入数据失败后，自增值不回滚

  • 事务回滚后，自增值不回退

  • 批量插入数据时，会批量提供 ID，用不完不会回收

---

MySQL 5.1.22 开始引入的参数 innodb_autoinc_lock_mode，控制了自增值申请时的锁范围。从并发性能的角度考虑，建议设置为 2，同时将 binlog_format 设置为 row。