InnoDB中的AUTO_INCREMENT处理

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

本节描述AUTO_INCREMENT锁定模式的行为，不同AUTO_INCREMENT锁定模式设置的用法含义以及如何InnoDB初始化AUTO_INCREMENT计数器。

InnoDB AUTO_INCREMENT锁定模式
InnoDB AUTO_INCREMENT锁定模式的用法含义
InnoDB AUTO_INCREMENT计数器初始化
注意

InnoDB AUTO_INCREMENT锁定模式

本节描述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。
“混合模式的插件”
这些是“简单插入”语句，用于指定部分（但不是全部）新行的自动增量值。下面c1是一个示例，其中AUTO_INCREMENTtable 的列是t1：
```
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
另一种类型的“混合模式插入”是INSERT ... ON DUPLICATE KEY UPDATE，在最坏的情况下实际上是INSERT紧跟着a UPDATE，其中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语句的执行顺序不敏感。

innodb_autoinc_lock_mode = 0（“传统”锁定模式）
传统的锁定模式提供与innodb_autoinc_lock_mode在MySQL 5.1中引入配置参数之前相同的行为。由于语义可能存在差异，因此提供了传统的锁定模式选项以实现向后兼容性，性能测试以及解决“混合模式插入”问题。
在这种锁定模式下，所有“类似于INSERT ”的语句都将获得特殊的表级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无法预先判断从Tx1 SELECT的INSERT语句中检索了多少行，并且它在语句进行时一次分配一个自动递增值。使用表级锁（保持在该语句的末尾），一次只能执行一个INSERT引用表的语句t1，并且不会交错使用不同的语句生成自动递增编号。Tx1 INSERT ... SELECT语句生成的自动增量值是连续的，并且Tx1 语句使用的（单个）自动增量值是连续的。INSERT Tx2中的语句小于或大于用于Tx1的所有语句，具体取决于哪个语句首先执行。
只要从二进制日志中重放SQL语句时执行顺序相同（使用基于语句的复制时，或在恢复方案中），结果与Tx1和Tx2首次运行时的结果相同。因此，在语句结束之前保持的表级锁定使INSERT使用自动增量的语句可以安全地用于基于语句的复制。但是，当多个事务同时执行insert语句时，这些表级锁会限制并发性和可伸缩性。
在前面的示例中，如果没有表级锁，则INSERTTx2中用于in 的auto-increment列的值完全取决于语句执行的时间。如果在运行Tx1的INSERT同时INSERT（而不是在启动之前或完成之后）执行Tx2的，则由两个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 ”语句中单调递增。但是，由于多个语句可以同时生成数字（也就是说，在语句之间交错分配数字），因此为任何给定语句插入的行生成的值可能不是连续的。
如果仅执行的语句是“简单插入”，其中要提前知道要插入的行数，则为单个语句生成的数字中没有空格，除了“混合模式插入”。但是，执行“批量插入”时，任何给定语句分配的自动增量值可能存在间隙。

InnoDB AUTO_INCREMENT锁定模式的用法含义

在复制中使用自动增量
如果使用基于语句的复制，请将其设置innodb_autoinc_lock_mode为0或1，并在主服务器及其从属服务器上使用相同的值。如果您使用innodb_autoinc_lock_mode= 2（“ interleaved ”）或主机和从机不使用相同锁定模式的配置，则不能确保从机上的自动增量值与主机上的自动增量值相同。
如果使用的是基于行的复制或混合格式的复制，则所有自动增量锁定模式都是安全的，因为基于行的复制对SQL语句的执行顺序不敏感（混合格式使用基于行的复制复制对于基于语句的复制不安全的任何语句）。
“丢失”的自动增量值和序列间隔
在所有锁定模式（0、1和2）下，如果生成自动增量值的事务回滚，则这些自动增量值将“丢失”。一旦为自动增量列生成了一个值，就无法回滚该值，无论是否完成“INSERT-like ”语句，也不能回滚包含的事务。这种丢失的值不会重复使用。因此，存储在AUTO_INCREMENT表的列中的值中可能存在间隙。
为AUTO_INCREMENT列指定NULL或0
在所有锁定模式（0、1和2）中，如果用户为中的AUTO_INCREMENT列指定NULL或0 INSERT，InnoDB则将行视为未指定值，并为其生成新值。
为该AUTO_INCREMENT列分配一个负值
在所有锁定模式（0、1和2）下，如果您为AUTO_INCREMENT列分配负值，则不会定义自动递增机制的行为。
如果该AUTO_INCREMENT值变得大于指定整数类型的最大整数
在所有锁定模式（0、1和2）中，如果该值变得大于可以以指定整数类型存储的最大整数，则不会定义自动递增机制的行为。
“批量插入”的自动增量值的缺口
与innodb_autoinc_lock_mode设定为0（“传统”）或1（“连续”），通过任何给定语句生成的自动递增的值是连续的，没有间隙，这是因为表级AUTO-INC锁一直保持到该语句的末尾，只有一个这样的语句可以一次执行。
与innodb_autoinc_lock_mode设置为2（“交织”），有可能是在所产生的自动递增值间隙“批量插入，”但只有当有同时执行的“INSERT样”的语句。
对于锁定模式1或2，在连续的语句之间可能会出现间隙，因为对于批量插入，可能不知道每个语句所需的自动递增值的确切数量，并且可能会高估。
由“混合模式插入”分配的自动增量值
考虑一个“混合模式插入”，其中“简单插入”指定一些（但不是全部）结果行的自动增量值。这样的语句在锁定模式0、1和2下的行为不同。例如，假定c1为AUTO_INCREMENTtable的一列t1，并且最近自动生成的序列号为100。
```
mysql> CREATE TABLE t1 (
-> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
-> c2 CHAR(1)
-> ) ENGINE = INNODB;
```
现在，考虑以下“混合模式插入”语句：
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
```
随着innodb_autoinc_lock_mode设置为0（“传统”），这四个新的行是：
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----	+------	+
| c1  	| c2   	|
+-----	+------	+
|   1 	| a    	|
| 101 	| b    	|
|   5 	| c    	|
| 102 	| d    	|
+-----	+------	+
```
下一个可用的自动递增值是103，因为自动递增值一次分配一次，而不是在语句执行开始时一次分配一次。无论是否存在（任何类型的）“INSERT-like ”语句，该结果都是正确的。
随着innodb_autoinc_lock_mode设置为1（“连续”），这四个新行也：
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----	+------	+
| c1  	| c2   	|
+-----	+------	+
|   1 	| a    	|
| 101 	| b    	|
|   5 	| c    	|
| 102 	| d    	|
+-----	+------	+
```
但是，在这种情况下，下一个可用的自动递增值是105，而不是103，因为在处理语句时分配了四个自动递增值，但只使用了两个。无论是否存在（任何类型的）“INSERT-like ”语句，该结果都是正确的。
与innodb_autoinc_lock_mode设定为模式2（“交织”），四个新的行是：
```
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----	+------	+
| c1  	| c2   	|
+-----	+------	+
|   1 	| a    	|

|   x| b    	|
|   5 	| c    	|

|   y| d    	|
+-----	+------	+
```
值x和y是独一无二的，比任何先前产生的行大。然而，具体的数值x，并y依赖于并行执行语句生成自动递增值的数量。
最后，考虑以下语句，该语句是在最近生成的序列号为100时发出的：
```
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
```
使用任何innodb_autoinc_lock_mode设置，此语句都会生成重复键错误23000（Can't write; duplicate key in table），因为为该行分配了101，并且该行的(NULL,'b')插入(101,'c')失败。
AUTO_INCREMENT在INSERT语句序列的中间修改列值
在MySQL 5.7和更早版本中，AUTO_INCREMENT在INSERT语句序列的中间修改列值可能会导致“重复条目”错误。例如，如果您执行了UPDATE将AUTO_INCREMENT列值更改为大于当前最大自动增量值的值的INSERT操作，则未指定未使用的自动增量值的后续操作可能会遇到“重复输入”错误。在MySQL 8.0和更高版本中，如果您修改了AUTO_INCREMENT将列值设置为大于当前最大自动增量值的值，将保留新值，随后的INSERT操作将从新的较大值开始分配自动增量值。在下面的示例中演示了此行为。
```
mysql> CREATE TABLE t1 (
-> c1 INT NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (c1)
->  ) ENGINE = InnoDB;

mysql> INSERT INTO t1 VALUES(0), (0), (3);

mysql> SELECT c1 FROM t1;
+----	+
| c1 	|
+----	+
|  1 	|
|  2 	|
|  3 	|
+----	+

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

mysql> SELECT c1 FROM t1;
+----	+
| c1 	|
+----	+
|  2 	|
|  3 	|
|  4 	|
+----	+

mysql> INSERT INTO t1 VALUES(0);

mysql> SELECT c1 FROM t1;
+----	+
| c1 	|
+----	+
|  2 	|
|  3 	|
|  4 	|
|  5 	|
+----	+
```

InnoDB AUTO_INCREMENT计数器初始化

本节介绍如何InnoDB初始化AUTO_INCREMENT计数器。

如果AUTO_INCREMENT为InnoDB表指定列，则内存表对象包含一个称为自动增量计数器的特殊计数器，该计数器在为该列分配新值时使用。

在MySQL 5.7和更早的版本中，自动递增计数器仅存储在主存储器中，而不存储在磁盘上。要在服务器重启后初始化自动增量计数器，InnoDB将在包含AUTO_INCREMENT列的表中的第一次插入时执行以下语句的等效项。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

在MySQL 8.0中，此行为已更改。每次更改时，当前最大自动增量计数器值都会写入重做日志，并保存到每个检查点的引擎专用系统表中。这些更改使当前的最大自动增量计数器值在服务器重新启动后保持不变。

在正常关闭后重新启动服务器时，InnoDB使用存储在数据字典系统表中的当前最大自动增量值初始化内存中自动增量计数器。

在崩溃恢复期间重新启动服务器时，InnoDB使用存储在数据字典系统表中的当前最大自动增量值初始化内存中自动增量计数器，并在重做日志中扫描自上一个检查点以来写入的自动增量计数器值。如果重做日志记录的值大于内存中计数器的值，则应用重做日志记录的值。但是，在服务器崩溃的情况下，不能保证重用先前分配的自动增量值。每次由于INSERT或改变当前的最大自动增量值UPDATE操作时，会将新值写入重做日志，但是如果崩溃发生在重做日志刷新到磁盘之前，则在重新启动服务器后初始化自动增量计数器时，可以重新使用先前分配的值。

唯一InnoDB使用等效语句初始化自动增量计数器的情况是在导入没有元数据文件的表时。否则，从元数据文件中读取当前的最大自动递增计数器值（如果存在）。除了初始化计数器值外，当尝试使用计数器将计数器值设置为小于或等于持久计数器值的计数器值时，语句的等效项还用于确定表的当前最大自动递增计数器值。SELECT MAX(ai_col)FROM table_name FOR UPDATE.cfg.cfgSELECT MAX(ai_col)FROM table_nameALTER TABLE ... AUTO_INCREMENT =N FOR UPDATE声明。例如，您可以在删除某些记录后尝试将计数器值设置为较小的值。在这种情况下，必须搜索该表以确保新的计数器值不小于或等于实际的当前最大计数器值。

在MySQL 5.7和更早版本中，服务器重新启动会取消AUTO_INCREMENT = Ntable选项的效果，该选项可在CREATE TABLEor ALTER TABLE语句中用于分别设置初始计数器值或更改现有计数器值。在MySQL 8.0中，服务器重启不会取消AUTO_INCREMENT = Ntable选项的作用。如果将自动递增计数器初始化为特定值，或者将自动递增计数器值更改为较大的值，则新值将在服务器重新启动后保留。

注意
ALTER TABLE ... AUTO_INCREMENT = N只能将自动递增计数器的值更改为大于当前最大值的值。

在MySQL 5.7和更早版本中，服务器在ROLLBACK操作之后立即重新启动可能会导致重用以前分配给回滚事务的自动增量值，从而有效地回滚当前的最大自动增量值。在MySQL 8.0中，当前的最大自动增量值得以保留，从而防止了先前分配的值的重用。

如果一条SHOW TABLE STATUS语句在初始化自动递增计数器之前检查了一个表，则InnoDB打开该表并使用存储在数据字典系统表中的当前最大自动递增值来初始化计数器值。该值存储在内存中，供以后的插入或更新使用。计数器值的初始化使用对表的普通互斥锁定读取，该读取持续到事务结束。InnoDB为用户指定的自动递增值大于0的新创建的表初始化自动递增计数器时，遵循相同的过程。

初始化自动增量计数器后，如果在插入行时未显式指定自动增量值，InnoDB则将隐式递增计数器并将新值分配给该列。如果插入明确指定自动递增列值的行，并且该值大于当前最大计数器值，则该计数器将设置为指定值。

InnoDB只要服务器运行，就使用内存中自动增量计数器。InnoDB如前所述，当服务器停止并重新启动时，请重新初始化自动增量计数器。

auto_increment_offset配置选项确定为初始点AUTO_INCREMENT列值。默认设置为1。

auto_increment_increment配置选项控制连续列的值之间的间隔。默认设置为1。

注意

当AUTO_INCREMENT整数列的值用完时，后续INSERT操作将返回重复键错误。这是一般的MySQL行为。