InnoDB的AUTO_INCREMENT处理

本文翻译自MySQL官方手册，对InnoDB的AUTO_INCREMENT处理作了介绍。

InnoDB提供了可配置的锁机制，可显著提升在有AUTO_INCREMENT列的表中插入行记录的SQL语句的可伸缩性和性能。

为了在InnoDB表中使用AUTO_INCREMENT机制，必须将AUTO_INCREMENT列定义为某个索引的第一列（联合索引）或唯一列，以便于在表上执行相当于索引列的SELECT MAX(ai_col)查找以获得列的最大值。

索引不需要是主键或唯一索引，但是为了避免AUTO_INCREMENT列的值重复，建议使用这些索引类型。

接下来介绍AUTO_INCREMENT锁模式，不同AUTO_INCREMENT锁模式设置的使用含义，以及InnoDB如何初始化AUTO_INCREMENT计数器。

InnoDB AUTO_INCREMENT锁模式

本节描述用于生成自动增量值的AUTO_INCREMENT锁模式，以及每种锁模式对复制的影响。自动增量（auto-increment）锁模式在服务启动时使用innodb_autoinc_lock_mode变量配置。

以下术语被用于介绍innodb_autoinc_lock_mode设置：

• “类插入（INSERT-like）”语句

    指所有在表中生成新行的语句，包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA语句。包括下面提到的“简单插入（simple-inserts）”、“批量插入（bulk-inserts）”、以及“混合模式（mixed-mode）”插入。

• “简单插入（simple-inserts）”

    指可以提前（在开始处理语句时）确定插入行数的语句。包括单行、多行的不包括嵌套子查询的INSERT和REPLACE语句，但是不包括 INSERT ... ON DUPLICATE KEY UPDATE。

• “批量插入（Bulk inserts）”

    指不能提前知道插入行数（以及所需自动增量值的数量）的语句。包括 INSERT ... SELECT 、REPLACE ... SELECT、以及 LOAD DATA语句，但不是普通的插入语句。InnoDB需要在处理每一行时为AUTO_INCREMENT列分配一个新值。

• “混合模式插入（Mixed-mode inserts）”

    包括为新行中的一部分（而非全部）指定了自动增量值的“简单插入（simple insert）”语句。下面是一个例子，c1是表t1的自增列：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

•   另一种类型的混合模式插入是INSERT ... ON DUPLICATE KEY UPDATE，在最坏的情况下实际上是INSERT，紧接着是UPDATE，在此期间，为自增列分配的值可能在更新阶段使用，也可能不用。

innodb_autoinc_lock_mode变量有三种可能的设置。分别是0（传统的），1（连续的），2（交错的）。从MySQL 8.0开始，默认设置是交错锁模式（innodb_autoinc_lock_mode=2）。在8.0之前，默认设置是连续锁模式（innodb_autoinc_lock_mode=1）。

MySQL 8.0的默认设置反映了其默认复制类型从基于语句的复制变为了基于行复制。基于语句的复制需要连续自增锁模式，用来保证在给定的SQL语句下，自增值按照可预测和可重复的顺序分配，但是基于行的复制则对SQL语句的执行顺序不敏感。

• innodb_autoinc_lock_mode = 0 （传统锁模式）

    传统锁模式提供了与引入innodb_autoinc_lock_mode变量之前相同的行为。传统锁模式选项是为了向后兼容、性能测试和解决“混合模式插入”问题而提供的，因为可能存在语义上的差异。

    在这种模式下，所有在表上有自增列的类插入（INSER