mysql如何实现逻辑自增_2021-01-05：mysql的自增id的实现逻辑是什么样子的？

福哥答案2021-01-05：

答案来自这个链接：每日一面 - mysql 的自增 id 的实现逻辑是什么样子的？

Key TakeAways

1.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 更新计数器获取。

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

3.AutoIncrement 的 id 可以让新数据聚集在一起，利于大部分 OLTP 业务(访问频率在最近一天，一周，或者几个月内比较活跃，而超过一段时间内的数据很少访问)。如果是这类业务推荐使用自增主键，将业务主键(UUID)作为二级的唯一索引使用。

4.如果考虑分布式性能以及避免 AutoIncrement 带来的锁性能问题，可以考虑使用 ID 生成器生成：全局趋势增长的主键。

为何主键要 Auto Increment 而不是 UUID

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

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

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

AutoIncrement 原理

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

AutoIncrement 最大值

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

AutoIncrement 锁模式

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

1.innodb_autoinc_lock_mode=0(traditional lock mode)

传统的auto_increment机制，这种模式下所有针对auto_increment列的插入操作都会加表级别的AUTO-INC锁，在语句执行结束则会释放，分配的值也是一个个分配，是连续的，正常情况下也不会有间隙(当然如果事务rollback了这个auto_increment值就会浪费掉，从而造成间隙)。

2.innodb_autoinc_lock_mode=1(consecutive lock mode)

这种情况下，针对未知数量批量插入(例如INSERT ... SELECT, REPLACE ... SELECT和LOAD DATA)才会采用AUTO-INC锁这种方式，而针对已知数量的普通插入，则采用了一种新的轻量级的互斥锁来分配auto_increment列的值。这种锁，只会持续到获取一定数量的 id，不会等待语句执行结束在释放。也就是拿轻量级锁提前分配好所需数量的 id 之后释放锁，再执行语句。当然，如果其他事务已经持有了AUTO-INC锁，则simple inserts需要等待。当然，这种情况下，可能产生的间隙更多。

3.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 之后就不会了。