MYSQL存储引擎InnoDB（十八）：AUTO_INCREMENT 处理（上）

科技 06-30 来源：泡泡研究笔记

InnoDB提供了一种可配置的锁定机制，可以显著提高向具有 AUTO_INCREMENT列的表添加行的 SQL 语句的可伸缩性和性能。要将 AUTO_INCREMENT机制用于 InnoDB表，必须将AUTO_INCREMENT列定义为某个索引的第一列或唯一列，以便可以对表执行等效SELECT MAX(ai_col)的索引查找以获得最大列值。索引不需要是PRIMARY KEY或UNIQUE ，但为了避免AUTO_INCREMENT列中的重复值，建议使用这些索引类型。

InnoDB AUTO_INCREMENT 锁定模式术语

本节介绍AUTO_INCREMENT 用于生成自增值的锁定模式，以及每种锁定模式如何影响复制。自动增量锁定模式在启动时使用innodb_autoinc_lock_mode 变量进行配置。

以下术语用于描述 innodb_autoinc_lock_mode 设置：

1、“ INSERT-like ” 语句

在表中生成新行的所有语句，包括 INSERT、 INSERT ... SELECT、REPLACE、 REPLACE ... SELECT和LOAD DATA。包括“ simple-inserts ”、 “ bulk-inserts ”和“ mixed-mode ” 插入。

2、simple-inserts

可以预先确定要插入的行数的语句（最初处理语句时）。这包括单行和多行 INSERT以及没有嵌套子查询的REPLACE，但不包括 INSERT ... ON DUPLICATE KEY UPDATE语句。

3、bulk-inserts

预先不知道要插入的行数（以及所需的自动增量值的数量）的语句。这包括 INSERT ... SELECT, REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。在处理每一行时，InnoDB一次为一列AUTO_INCREMENT分配新值。

4、mixed-mode

这些是“简单插入”语句，为一些新行指定自动增量值。下面是一个示例，其中 c1是 table t1的 AUTO_INCREMENT列：

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

另一种类型的“混合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE。在最坏的情况下实际上是 INSERT 后跟 UPDATE，其中为 AUTO_INCREMENT列分配的值可能会也，可能不会在更新阶段使用。

InnoDB AUTO_INCREMENT 锁定模式

innodb_autoinc_lock_mode 变量有三种可能的设置。“传统”、“连续”或 “交错”锁定模式的设置分别为 0、1 或 2 。从 MySQL 8.0 开始，交错锁定模式 ( innodb_autoinc_lock_mode=2) 是默认设置。在 MySQL 8.0 之前，连续锁定模式是默认的 ( innodb_autoinc_lock_mode=1)。

MySQL 8.0 中交错锁模式的默认设置反映了从基于语句的复制到基于行的复制作为默认复制类型的变化。基于语句的复制需要连续的自增锁模式，以确保给定的SQL语句序列以可预测和可重复的顺序分配自增值，而基于行的复制对SQL语句的执行顺序不敏感.

1、innodb_autoinc_lock_mode = 0 （“传统”锁定模式）

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

在这种锁定模式下，所有“ INSERT-like ”语句都会获得一个特殊的表级AUTO-INC 锁定，用于插入到具有 AUTO_INCREMENT列的表中。此锁通常保持到语句的末尾（而不是事务的末尾），以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增值任何给定语句分配的都是连续的。

在基于语句的复制的情况下，这意味着当在副本服务器上复制 SQL 语句时，自动增量列使用与源服务器上相同的值。多个 INSERT语句的执行结果是确定性的，副本复制与源上相同的数据。如果多个语句生成的自动增量值INSERT交错，则两个并发 INSERT语句的结果将是不确定的，并且无法使用基于语句的复制可靠地传播到副本服务器。

为了清楚起见，考虑一个使用此表的示例：

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

假设有两个事务正在运行，每个事务都将行插入到具有一 AUTO_INCREMENT列的表中。一个事务使用 INSERT ... SELECT插入 1000 行的语句，另一个事务使用插入一行的简单 INSERT语句：

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB无法预先知道从 Tx1SELECT中的 INSERT语句中检索了多少行，并且随着语句的进行，它一次分配一个自动增量值。使用表级锁，保持到语句末尾，一次只能执行一个 INSERT引用表的语句t1，并且不同语句生成自增数不会交错。Tx1 语句生成的自增值 INSERT ... SELECT是连续的，使用的（单个）自增值 INSERTTx2 中的语句小于或大于用于 Tx1 的所有语句，具体取决于哪个语句首先执行。

只要 SQL 语句在从二进制日志重放时（使用基于语句的复制时，或在恢复场景中）以相同的顺序执行，结果与 Tx1 和 Tx2 首次运行时的结果相同。因此，表级锁一直保持到语句结束，使得 INSERT使用自动增量的语句可以安全地用于基于语句的复制。但是，当多个事务同时执行插入语句时，这些表级锁会限制并发性和可伸缩性。

在前面的示例中，如果没有表级锁，则用于Tx2 INSERT 的自动增量列的值取决于语句执行的确切时间。如果 Tx2 INSERT 在 Tx1 INSERT 运行时执行（而不是在它开始之前或完成之后），则两个 INSERT语句分配的特定自动增量值是不确定的，并且可能因运行而异。

在连续锁模式下，InnoDB可以避免对预先知道行数的 “简单插入”语句使用表级锁AUTO-INC，并且仍然为基于语句的复制保留确定性执行和安全性。

如果您不使用二进制日志作为恢复或复制的一部分来重放 SQL 语句，则可以使用交错锁模式来消除所有表级 AUTO-INC锁的使用，以获得更高的并发性和性能，但代价是允许自动中的间隙增加由语句分配的编号，并可能使同时执行的语句分配的编号交错。

2、innodb_autoinc_lock_mode = 1 （“连续”锁定模式）

在这种模式下，“批量插入”使用特殊的 AUTO-INC表级锁并持有它直到语句结束。这适用于所有 INSERT ... SELECT、 REPLACE ... SELECT和LOAD DATA语句。一次只能执行一个持有 AUTO-INC锁的语句。如果批量插入操作的源表与目标表不同，则AUTO-INC在从源表中选择的第一行获取共享锁之后，再获取目标表的锁。如果批量插入操作的源和目标是同一个表，则AUTO-INC在所有选定行上获取共享锁后获取锁。

“简单插入”（预先知道要插入的行数）通过在互斥锁（一种轻量级锁）的控制下获得所需数量的自增值来避免表级锁在分配过程中保留，直到语句完成。除非 AUTO-INC锁被另一个事务持有，否则不使用表级AUTO-INC锁。如果另一个事务持有一个 AUTO-INC锁，一个“简单插入”等待AUTO-INC 锁，就好像它是一个“批量插入”。

这种锁定模式确保，在存在 INSERT预先不知道行数的语句（以及随着语句进行而分配自动递增编号的情况）的情况下，任何 “ INSERT-like ” 语句分配的所有自动递增值都是连续的，并且操作对于基于语句的复制是安全的。

简而言之，这种锁定模式显着提高了可伸缩性，同时可以安全地用于基于语句的复制。此外，与“传统” 锁定模式一样，任何给定语句分配的自动递增数字都是连续的。对于任何使用自动增量的语句，与“传统”模式相比，语义 没有变化，但有一个重要例外。

例外情况是“混合模式插入”AUTO_INCREMENT ，其中用户为多行“简单插入”中的某些（但不是全部）行提供列的显式值。对于此类插入，InnoDB分配比要插入的行数更多的自动增量值。但是，所有自动分配的值都是连续生成的（因此高于）最近执行的前一条语句生成的自动增量值。“多余”的数字会丢失。

3、innodb_autoinc_lock_mode = 2 （“交错”锁定模式）

在这种锁模式下，没有 “ INSERT-like ” 语句使用表级AUTO-INC 锁，可以同时执行多条语句。这是最快且最具可扩展性的锁定模式，但在从二进制日志重放 SQL 语句时使用基于语句的复制或恢复场景时，它 并不安全。

在这种锁定模式下，自动递增值保证在所有并发执行的“ INSERT-like ” 语句中是唯一的并且单调递增。但是，由于多个语句可以同时生成数字（即，数字的分配在语句之间交错），为任何给定语句插入的行生成的值可能不是连续的。

如果执行的唯一语句是“简单插入”，其中要插入的行数是提前知道的，那么除了 “混合模式插入”之外，为单个语句生成的数字中没有间隙。但是，当执行“批量插入”时，任何给定语句分配的自动增量值可能存在间隙。