MySQL 自增主键

以下仅考虑 InnoDB 存储引擎。

自增主键有两个性质需要考虑：

• 单调性
  每次插入一条数据，其 ID 都是比上一条插入的数据的 ID 大，就算上一条数据被删除。
• 连续性
  插入成功时，其数据的 ID 和前一次插入成功时数据的 ID 相邻。

自增主键的单调性

为何会有单调性的问题？

这主要跟自增主键最大值的获取方式，以及存放位置有关系。

如果最大值是通过计算获取的，并且在某些情况下需要重新获取时，会因为最新的数据被删除而减小。

自增主键最大值怎么取的？存放到哪里？

MySQL 5.7 及之前的版本，自增主键最大值会在启动（重启）后从数据库中取出放到内存：

SELECT MAX(ai_col) FROM table_name FOR UPDATE; 

这样获取是通过计算的，并且由于存放在内存而容易丢失。

如果删除最新一条数据（假设 ID 为 10），因故障或者其他必要重启后再插入一条数据时会使用之前的 ID （即 ID 为 10）。

问题在于如果有其他表依赖了该 ID，则其他表的数据关联到的数据就符合要求了。除非设置了外键。

比如我要向最大一个 ID 的账号充了 100 万。但是在充值之前，该账号被删除，然后服务器故障重启，重启后有人新注册了一个账号。结果我的 100 万充到了他的新账号上。注册新账号的人以为是新手福利，笑嘻嘻。

如何解决单调性的问题？

从 MySQL 8.0 开始，自增主键最大值会在每次修改后写入到 redo log，并且在每个检查点写入引擎私有的系统表。

• 如果是正常重启，则读取系统表里的值。
• 如果是故障重启，则先读取系统表里的值放到内存。接着扫描 redo log 里存储的值。如果扫描到的值大于内存的值，则将该值覆盖到内存。

但由于数据库可能在 redo log 刷入磁盘前就故障了，所以可能会用到之前申请的 ID。

注：如果 redo log 都没刷入，就更不用说将数据插入数据表了。

InnoDB AUTO_INCREMENT Counter Initialization
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization

自增主键插入时的连续性

这里不考虑由于删除导致的连续性问题

为何会有连续性问题？

这主要是跟插入事务回滚有关系。

对于两个插入事务，事务 A 先执行插入语句，之后事务 B 执行插入语句。在这之后，事务 A 回滚，导致 A 执行插入语句时占用的 ID 被抛弃。

之所以事务 A 没提交的情况下，事务 B 就能执行插入语句，跟 InnoDB 的自增长锁（AUTO-INC Locking）相关。该锁是一种特殊的表锁（table-level lock），但会在插入语句执行后立即释放，不会等到事务结束。

如何解决连续性问题？

使用最高隔离级别 SERIALIZABLE （串行）。

由于性能上的考虑，通常不这样做。

多事务批量插入的连续性

事务 A 和事务 B 都在执行 不确定数量 的批量插入（INSERT ... SELECT）：

• 保证事务 A 的数据的 ID 连续： innodb_autoinc_lock_mode = 0 （AUTO-INC Locking）
  必须等待语句执行结束才释放锁。
• 保证事务 A 的数据的 ID 连续： innodb_autoinc_lock_mode = 1 （AUTO-INC Locking）
  和上面的区别在于，当执行 确定数量 的批量插入时，使用轻量级互斥量（mutex）而不是特殊表锁（AUTO-INC Locking），从而提前向内存的计数器申请相应数量的 ID。之后立即释放，不用等语句执行结束。
  会因为回滚而使得全局 ID 不连续。
• 不保证事务 A 的数据的 ID 连续： innodb_autoinc_lock_mode = 2 （mutex）

三种插入定义：

• 简单插入
  能够提前知道插入的行数
• 批量插入
  不能提前知道插入的行数
• 混合插入
  批量插入中的一部分的 ID 是指定的（非 0 且非 NULL），另一部分未指定，使用数据库生成的自增 ID。

其他

如果主动指定 ID 为 0 或者 NULL 插入，则会使用数据库生成的自增 ID。

参考文档

为什么 MySQL 的自增主键不单调也不连续
https://database.51cto.com/art/202004/614923.htm
《MySQL技术内幕——InnoDB存储引擎》 第 6 章：锁

MySQL 自增主键

原文：https://www.cnblogs.com/schaepher/p/12798203.html