mysql 主键自增语句_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 都没刷入，就更不用说将数据插入数据表了。

自增主键插入时的连续性

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

为何会有连续性问题？

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

对于两个插入事务，事务 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 章：锁