14.6.1.6 InnoDB 中的 AUTO_INCREMENT 处理
InnoDB
提供了一种可配置的锁定机制,该机制可以显着改善将行添加到具有AUTO_INCREMENT
列的 table 中的 SQL 语句的可伸缩性和性能。要将AUTO_INCREMENT
机制与InnoDB
table 一起使用,必须将AUTO_INCREMENT
列定义为索引的一部分,以便可以等效于对 table 进行索引SELECT MAX(ai_col)
查找来获取最大列值。通常,这是通过使该列成为某个 table 索引的第一列来实现的。
本节介绍AUTO_INCREMENT
锁定模式的行为,不同AUTO_INCREMENT
锁定模式设置的使用含义以及InnoDB
如何初始化AUTO_INCREMENT
计数器。
InnoDB AUTO_INCREMENT 锁定模式
本节描述了用于生成自动增量值的AUTO_INCREMENT
锁定模式的行为,以及每种锁定模式如何影响复制。在启动时使用innodb_autoinc_lock_mode配置参数配置自动增量锁定模式。
以下术语用于描述innodb_autoinc_lock_mode设置:
- "INSERT-like" statements
在 table 中生成新行的所有语句,包括INSERT,插入...选择,REPLACE,更换...选择和LOAD DATA。包括“简单插入”,“批量插入”和“混合模式”插入。
- "Simple inserts"
可以预先确定要插入行数的语句(最初处理该语句时)。这包括单行和多行INSERT和REPLACE语句,这些语句没有嵌套的子查询,但没有插入...在重复的密钥更新上。
- "Bulk inserts"
事先不知道要插入行数(以及所需的自动增量值的数目)的语句。这包括插入...选择,更换...选择和LOAD DATA语句,但不包括普通INSERT
。在处理每一行时,InnoDB
一次为AUTO_INCREMENT
列分配一个新值。
- "Mixed-mode inserts"
这些是“简单插入”语句,用于指定某些(但不是全部)新行的自动增量值。下面是一个示例,其中c1
是 tablet1
的AUTO_INCREMENT
列:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另一种类型的“混合模式插入”是插入...在重复的密钥更新上,在最坏的情况下实际上是INSERT后面是UPDATE,其中在更新阶段可能会或可能不会使用AUTO_INCREMENT
列的分配值。
innodb_autoinc_lock_mode配置参数有三种可能的设置。对于“传统”,“连续”或“交错”锁定模式,设置分别为 0、1 或 2.
传统的锁定模式提供的行为与在 MySQL 5.1 中引入innodb_autoinc_lock_mode配置参数之前存在的行为相同。由于语义可能存在差异,提供了传统的锁定模式选项是为了实现向后兼容性,性能测试以及解决“混合模式插入”问题。
在这种锁定模式下,所有“类似于 INSERT”的语句都将获得特殊的 table 级AUTO-INC
锁定,以便插入具有AUTO_INCREMENT
列的 table 中。此锁通常保留在语句的末尾(而不是事务的末尾),以确保为给定的INSERT语句序列以可预测和可重复的 Sequences 分配自动递增值,并确保自动递增任何给定语句分配的值都是连续的。
对于基于语句的复制,这意味着在副本服务器上复制 SQL 语句时,自动增量列使用与源服务器相同的值。多个INSERT语句的执行结果是确定性的,并且副本将复制与源上相同的数据。如果交错由多个INSERT语句生成的自动增量值,则两个并发的INSERT语句的结果将是不确定的,并且不能使用基于语句的复制可靠地传播到副本服务器。
为了清楚起见,请考虑使用该 table 的示例:
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB;
假设有两个正在运行的事务,每个事务都将行插入具有AUTO_INCREMENT
列的 table 中。一种事务使用的是插入 1000 行的插入...选择语句,另一种事务使用的是插入一行的简单INSERT语句:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
InnoDB
无法预先判断从 Tx1 的INSERT语句中的SELECT检索了多少行,并且随着该语句的进行,它一次分配一个自动递增值。使用 table 级锁(保持在该语句的末尾),一次只能执行一个引用 tablet1
的INSERT语句,并且不会交错使用由不同的语句生成的自动递增编号。 Tx1 插入...选择语句生成的自动增量值是连续的,并且 Tx2 中INSERT语句使用的(单个)自动增量值小于或大于用于 Tx1 的所有增量,具体取决于哪个语句首先执行。
从二进制日志重播时,只要 SQL 语句以相同的 Sequences 执行(使用基于语句的复制或在恢复方案中),结果与 Tx1 和 Tx2 首次运行时的结果相同。因此,在语句结束之前保持的 table 级锁使使用自动增量的INSERT语句可以安全地用于基于语句的复制。但是,当多个事务同时执行 insert 语句时,这些 table 级锁会限制并发性和可伸缩性。
在前面的示例中,如果没有 table 级锁定,则 Tx2 中用于INSERT的 auto-increment 列的值完全取决于语句执行的时间。如果 Tx1 的INSERT在运行时(而不是在其启动之前或完成之后)执行 Tx2 的INSERT,则由两个INSERT语句分配的特定自动增量值是不确定的,并且可能因运行而异。
在consecutive锁定模式下,InnoDB
可以避免对预先知道行数的“简单插入”语句使用 table 级AUTO-INC
锁定,并且仍然保留确定性执行和基于语句的复制的安全性。
如果您不使用二进制日志重播 SQL 语句作为恢复或复制的一部分,则可以使用interleaved锁定模式来消除对 table 级AUTO-INC
锁定的所有使用,从而获得更大的并发性和性能,但代价是要允许空白。语句分配的自动递增数字,并可能使同时执行的语句分配的数字交错。
这是默认的锁定模式。在这种模式下,“批量插入”使用特殊的AUTO-INC
table 级锁并将其保持到语句结束。这适用于所有插入...选择,更换...选择和LOAD DATA语句。一次只能拥有AUTO-INC
锁的语句可以执行。如果批量插入操作的源 table 与目标 table 不同,则在从源 table 中选择的第一行获得共享锁之后,将获得目标 table 上的AUTO-INC
锁。如果批量插入操作的源和目标是同一 table,则在对所有选定行执行共享锁之后,将获得AUTO-INC
锁。
“简单插入”(预先知道要插入的行数)通过在互斥量(轻量级锁)的控制下获得所需的自动递增值数来避免 table 级AUTO-INC
锁。仅在分配过程中保持,直到语句完成时才保持。除非另一个事务持有AUTO-INC
锁,否则不使用 table 级AUTO-INC
锁。如果另一个事务持有AUTO-INC
锁,则“简单插入”将 awaitAUTO-INC
锁,就好像它是“批量插入”一样。
此锁定模式可确保在存在不预先知道行数的INSERT语句(并且随着语句的进行分配自动递增编号)的情况下,由任何“ INSERT -like”分配的所有自动递增值语句是连续的,并且操作对于基于语句的复制是安全的。
简而言之,此锁定模式可显着提高可伸缩性,同时可安全地用于基于语句的复制。此外,与“传统”锁定模式一样,任何给定语句分配的自动递增编号都是连续。与任何使用自动递增的语句的“传统”模式相比,语义没有“改变” *,但有一个重要的 exception。
“混合模式插入”exception,其中用户为多行“简单插入”中的某些(但不是全部)行的AUTO_INCREMENT
列提供显式值。对于此类插入,InnoDB
分配的自动增量值大于要插入的行数。但是,所有自动分配的值都是连续生成的(因此高于由最近执行的先前语句生成的自动增量值)。 “多余”数字丢失。
在这种锁定模式下,没有“ INSERTlike”语句使用 table 级AUTO-INC
锁定,并且可以同时执行多个语句。这是最快,最具扩展性的锁定模式,但是在使用基于语句的复制或恢复方案(从二进制日志中重放 SQL 语句)时,这是“不安全的”。
在这种锁定模式下,保证自动递增的值是唯一的,并且在所有同时执行的“ INSERTlike”语句中单调递增。但是,由于多个语句可以同时生成数字(也就是说,数字的分配在语句之间“交错”),因此为任何给定语句插入的行生成的值可能不是连续的。
如果仅执行的语句是“简单插入”,而要提前知道要插入的行数,则为“除混合模式插入”外,为单个语句生成的数字没有任何间隙。但是,执行“批量插入”时,任何给定语句分配的自动增量值可能存在间隙。
InnoDB AUTO_INCREMENT 锁定模式的用法含义
- 在复制中使用自动增量
如果使用的是基于语句的复制,请将innodb_autoinc_lock_mode设置为 0 或 1,并在源及其副本上使用相同的值。如果使用innodb_autoinc_lock_mode = 2(“交错”)或源和副本不使用相同锁定模式的配置,则不能确保副本上的自动增量值与源上的相同。
如果您使用的是基于行的复制或混合格式的复制,则所有自动增量锁定模式都是安全的,因为基于行的复制对 SQL 语句的执行 Sequences 不敏感(并且混合格式使用基于行的复制复制对于基于语句的复制不安全的任何语句)。
- “丢失”的自动增量值和序列间隔
在所有锁定模式(0、1 和 2)下,如果生成自动增量值的事务回滚,则这些自动增量值将“丢失”。一旦为自动增量列生成了一个值,就无法回滚该值,无论是否完成“ _1 样”语句以及是否回滚包含的事务。这种丢失的值不会重复使用。因此,存储在 table 的AUTO_INCREMENT
列中的值中可能会有间隙。
- 为
AUTO_INCREMENT
列指定 NULL 或 0
在所有锁定模式(0、1 和 2)中,如果用户为INSERT中的AUTO_INCREMENT
列指定 NULL 或 0,则InnoDB
会将行视为未指定值,并为其生成新值。
- 为
AUTO_INCREMENT
列分配负值
在所有锁定模式(0、1 和 2)中,如果您为AUTO_INCREMENT
列分配负值,则不会定义自动递增机制的行为。
- 如果
AUTO_INCREMENT
值大于指定整数类型的最大整数
在所有锁定模式(0、1 和 2)中,如果该值变得大于可以以指定整数类型存储的最大整数,则不会定义自动递增机制的行为。
- “批量插入”的自动增量值的间隙
在innodb_autoinc_lock_mode设置为 0(“传统”)或 1(“连续”)的情况下,任何给定语句生成的自动增量值都是连续的,没有间隙,因为 table 级AUTO-INC
锁定一直保持到语句结束,一次只能执行一个这样的语句。
在innodb_autoinc_lock_mode设置为 2(“交错”)的情况下,“批量插入”生成的自动增量值可能会有间隙,但前提是必须同时执行“ INSERT样”语句。
对于锁定模式 1 或 2,在连续的语句之间可能会出现间隙,因为对于批量插入,可能不知道每个语句所需的自动递增值的确切数量,并且可能会高估。
- 由“混合模式插入”分配的自动增量值
考虑一个“混合模式插入”,其中“简单插入”指定一些(但不是全部)结果行的自动增量值。这样的语句在锁定模式 0、1 和 2 下的行为不同。例如,假定c1
是 tablet1
的AUTO_INCREMENT
列,并且最近自动生成的序列号是 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
),因为为行(NULL, 'b')
分配了 101,并且行(101, 'c')
的插入失败。
- 在INSERT语句序列的中间修改
AUTO_INCREMENT
列值
在所有锁定模式(0、1 和 2)中,在INSERT语句序列的中间修改AUTO_INCREMENT
列值可能会导致“重复 Importing”错误。例如,如果执行UPDATE操作,将AUTO_INCREMENT
列的值更改为大于当前最大自动增量值的值,则后续的未指定未使用的自动增量值的INSERT操作可能会遇到“重复 Importing”错误。在下面的示例中演示了此行为。
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);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
InnoDB AUTO_INCREMENT 计数器初始化
本节介绍InnoDB
如何初始化AUTO_INCREMENT
计数器。
如果为InnoDB
table 指定AUTO_INCREMENT
列,则InnoDB
数据字典中的 table 句柄将包含一个称为自动增量计数器的特殊计数器,该计数器用于为该列分配新值。该计数器仅存储在主存储器中,而不存储在磁盘上。
要在服务器重新启动后初始化自动增量计数器,InnoDB
在包含AUTO_INCREMENT
列的 table 中的第一个插入处执行以下语句的等效项。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
InnoDB
递增该语句检索的值,并将其分配给 table 的列和 table 的自动递增计数器。默认情况下,该值增加 1.此默认值可以被auto_increment_increment配置设置覆盖。
如果 table 为空,则InnoDB
使用值1
。可以通过auto_increment_offset配置设置覆盖此默认设置。
如果显示 table 格状态语句在初始化自动递增计数器之前检查了 table,则InnoDB
会初始化但不会递增该值。存储该值以供以后的插入使用。该初始化使用对 table 的常规互斥锁定读取,并且该锁定持续到事务结束。 InnoDB
遵循与初始化新创建 table 的自动增量计数器相同的过程。
初始化自动增量计数器后,如果您没有为AUTO_INCREMENT
列明确指定值,则InnoDB
会递增计数器并将新值分配给该列。如果插入明确指定列值的行,并且该值大于当前计数器值,则计数器将设置为指定的列值。
只要服务器运行,InnoDB
就使用内存中自动增量计数器。如前所述,当服务器停止并重新启动服务器后,InnoDB
为 table 的第一个INSERT重新初始化每个 table 的计数器。
重新启动服务器还会取消CREATE TABLE和ALTER TABLE语句中AUTO_INCREMENT = N
table 选项的影响,您可以将它们与InnoDB
table 一起使用以设置初始计数器值或更改当前计数器值。
Notes
-
当
AUTO_INCREMENT
整数列的值用完时,随后的INSERT
操作将返回重复键错误。这是一般的 MySQL 行为。 -
当您重新启动 MySQL 服务器时,
InnoDB
可能会重用为AUTO_INCREMENT
列生成但从未存储的旧值(即,在回滚旧事务期间生成的值)。