InnoDB中的AUTO_INCREMENT处理

网站和app并发性能取决于访问链路的每个环节，包括前端、流量路由、后台业务逻辑代码、中间件和数据库等。大部分环节都可以通过横向扩展来提高并发性能，数据库作为链路的末端要保持数据一致性等特点不像其他环节容易横向扩展，所以数据库性能尤为重要，特别是插入性能。

数据库设计通常会用一列与业务无关的自增长id作为主键（互联网业务数据库设计一般不会完全遵循数据库范式，如果有其他列值是随时间递增，也可以用该列做主键），提高写入效率和方便数据复制。而自增长id生成模式影响着数据插入性能。

InnoDB提供了一种可配置的锁定机制，可以显着提高SQL语句的可伸缩性和性能，从而为具有AUTO_INCREMENT列的表添加行。要在InnoDB表使用AUTO_INCREMENT机制， AUTO_INCREMENT必须将列定义为索引的一部分，以便可以在表上执行等效的索引查找SELECT MAX(ai_col)以获取最大列值。通常，这是通过使列成为某些表索引的第一列来实现的。

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

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

“INSERT-like” 语句：在表中生成新的行中的所有语句，包括 INSERT， INSERT … SELECT，REPLACE， REPLACE … SELECT和LOAD DATA。包括“简单插入”，“批量插入”和“混合模式 ”插入。
“简单插入”：可以预先确定要插入的行数的语句（最初处理语句时）。这包括没有嵌套子查询的单行和多行INSERT以及REPLACE语句，但不包括INSERT … ON DUPLICATE KEY UPDATE。
“批量插入”：预先不知道要插入的行数（以及所需的自动增量值的数量）的语句。这包括 INSERT … SELECT， REPLACE … SELECT和LOAD DATA声明，但不是简单的 INSERT。在处理每一行时，InnoDB为AUTO_INCREMENT列分配一个新值。
“混合模式插入”：指定一些（但不是全部）新增行的自动增量值的“简单插入”语句。举个例子 INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 其中c1是 ti表的AUTO_INCREMENT表。
另一种类型的“混合模式插入”是INSERT … ON DUPLICATE KEY UPDATE，在最坏的情况下实际上是INSERT 后跟了一个UPDATE操作，其中AUTO_INCREMENT列在UPDATE阶段期间可能使用或不使用分配值。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语句的执行顺序不敏感。

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

传统的锁定模式提供了与在MySQL 5.1中引入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语句：

1
2
3

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ... 

Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB事先无法判断从Tx1 SELECT中的INSERT语句中检索了多少行，并且随着语句的进行，它会一次分配一个自动增量值。使用保持在语句末尾的表级锁定，一次只能执行一个 INSERT引用表t1的语句，并且不会交错生成不同语句的自动增量数。由Tx1 INSERT … SELECT语句生成的自动递增值是连续的，并且用于Tx2中的INSERT 语句的（单个）自动递增值要小于或大于用于Tx1的语句，具体取决于首先执行的语句。

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

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

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

如果不使用二进制日志作为恢复或复制的一部分重放SQL语句，则交错锁定模式可用于消除表级AUTO-INC锁的所有使用，从而实现更高的并发性和性能，但代价是允许自动存在空白 – 由语句分配的增量编号，可能具有由并发执行的语句交错分配的编号。

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锁，则“简单插入”等待AUTO-INC锁定，就像它是“批量插入”。

此锁定模式确保在存在未提前知道行数的INSERT语句（以及在语句进行时指定自动增量编号）的情况下，由任何“INSERT-like” 语句指定的所有自动增量值都是连续，并且操作对于基于语句的复制是安全的。

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

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

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

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

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

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

总结

MySQL 插入语句分三类：简单插入（Simple inserts）、批量插入（Bulk inserts）和混合插入（Mixed-mode inserts），这三种插入在innodb_autoinc_lock_mode取不同值时，生成的自增长值和插入性能会不一样。