每日一面 - mysql 的自增 id 的实现逻辑是什么样子的？

本问题参考自： https://www.zhihu.com/question/437916819/answer/1661679374， 解答为个人原创

Key TakeAways

• InnoDB 引擎中 有三种 AutoIncrement 锁模式：
  • innodb_autoinc_lock_mode=0（traditional lock mode）：获取表锁，语句执行结束后释放
  • innodb_autoinc_lock_mode=1（consecutive lock mode，MySQL 8.0 之前默认 ）：对于不确定插入数量的语句（例如INSERT ... SELECT, REPLACE ... SELECT和LOAD DATA）和 innodb_autoinc_lock_mode=0 一样，其他的确定数量的语句在执行前先批量获取 id，之后再执行语句。
  • innodb_autoinc_lock_mode=2（interleaved lock mode，MySQL 8.0+ 默认 ）：采用乐观锁， CAS 更新计数器获取。
• AutoIncrement 计数器在 MySQL 8.0 之前，存储在内存中，在 MySQL 8.0 之后，持久化存储到磁盘。通过每次更新写入 Redo Log，并在检查点刷入 innodb 引擎表中记录下来。
• AutoIncrement 的 id 可以让新数据聚集在一起，利于大部分 OLTP 业务（访问频率在最近一天，一周，或者几个月内比较活跃，而超过一段时间内的数据很少访问）。如果是这类业务推荐使用自增主键，将业务主键（UUID）作为二级的唯一索引使用。
• 如果考虑分布式性能以及避免 AutoIncrement 带来的锁性能问题，可以考虑使用 ID 生成器生成：全局趋势增长的主键

为何主键要 Auto Increment 而不是 UUID

MySQL InnoDB 引擎默认主键索引是 B+ 树索引，也是聚集索引，为何叫聚集索引呢？

以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。

存储中，聚集索引的数据，会根据索引的值，对应的数据也会聚集存储在一起：

MySQL 读取磁盘上的数据是一页一页读取的，如果某条我们要处理的数据在某一页中，但是这一页其他数据我们都不关心，这样的请求多了，性能会急剧下降，类似于 CPU 的 false sharing：

按照 B+ 树的原理，AutoIncrement 的 ID 能保证最新的数据在一页中被读取，而且减少了 B+ 树分裂翻转。 UUID 由于无序，插入时，B+ 树会不断翻转，并且最新的数据可能不在同一页。很可能会出现，最新一条数据，和好几年前的数据在同一页。

在大部分 OLTP 类业务中，例如购物和支付交易的订单，节日促销的抽奖活动这类业务都有这样的使用场景，访问频率在最近一天，一周，或者几个月内比较活跃，而超过一段时间内的数据很少访问。如果是这类业务推荐使用自增主键，将业务主键（UUID）作为二级的唯一索引使用。 如果考虑分布式性能以及避免 AutoIncrement 带来的锁性能问题，可以考虑使用 ID 生成器生成全局趋势增长的主键，例如 Twitter 的 Snowflake 算法生成的前面是时间戳的主键id，或者是 类似于这种 “时间+业务+自增”(例如 20210105105811233ORD0000001) 字符串，作为主键id，这样其实也能近似保证热数据聚集存储在一起，也就是 MySQL 一页一页读取能命中更多要读取处理的数据

AutoIncrement 原理

我们这里只关心 InnoDB 引擎的。

AutoIncrement 最大值

AutoIncrement 最大值，和列类型相关。最大可以设置列类型为 UNSIGNED BIGINT，这样最大值就是 18446744073709551615。 超过这个值继续生成则还是 18446744073709551615。不会再增加。

AutoIncrement 锁模式

获取 AutoIncrement 最新值，需要涉及到锁。目前有三种锁模式，对应 innodb_autoinc_lock_mode 的值， 0 ，1，2. MySQL 8.0 之后，默认为 2， 在这之前，默认为 1

• innodb_autoinc_lock_mode=0（traditional lock mode） 传统的auto_increment机制，这种模式下所有针对auto_increment列的插入操作都会加表级别的AUTO-INC锁，在语句执行结束则会释放，分配的值也是一个个分配，是连续的，正常情况下也不会有间隙（当然如果事务rollback了这个auto_increment值就会浪费掉，从而造成间隙）。
• innodb_autoinc_lock_mode=1（consecutive lock mode） 这种情况下，针对未知数量批量插入（例如INSERT ... SELECT, REPLACE ... SELECT和LOAD DATA）才会采用AUTO-INC锁这种方式，而针对已知数量的普通插入，则采用了一种新的轻量级的互斥锁来分配auto_increment列的值。这种锁，只会持续到获取一定数量的 id，不会等待语句执行结束在释放。也就是拿轻量级锁提前分配好所需数量的 id 之后释放锁，再执行语句。当然，如果其他事务已经持有了AUTO-INC锁，则simple inserts需要等待。当然，这种情况下，可能产生的间隙更多。
• innodb_autoinc_lock_mode=2（interleaved lock mode） 这种模式下任何类型的inserts都不会采用AUTO-INC锁，性能最好，但是在同一条语句内部产生auto_increment值间隙。其实这个就是所有语句对于同一个值进行 Compare-And-Set 更新，类似于乐观锁。这个锁模式对statement-based replication的主从同步都有一定问题。因为同步传输的是语句，而不是行值，语句执行后的差异导致主从可能主键不一致。

AutoIncrement 存储

AutoIncrement 计数器在 MySQL 8.0 之前，存储在内存中，每次启动时通过以下语句初始化：

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

在 MySQL 8.0 之后，持久化存储到磁盘。通过每次更新写入 Redo Log，并在检查点刷入 innodb 引擎表中记录下来。

所以，在MySQL 8.0 之前，如果 rollback 导致某些值没有使用，重启后，这些值还是会使用。但是在 MySQL 8.0 之后就不会了。