InnoDB
提供了一种可配置的锁定机制,可以显着提高将行添加到具有
AUTO_INCREMENT
列的表的 SQL 语句的可伸缩性和性能。要在
表中
使用该AUTO_INCREMENT
机制,
必须将列定义为某个索引的第一列或唯一列,以便可以对表执行相当于索引查找的操作以获得最大列值。索引不需要是or
,但为了避免
列中的重复值,建议使用这些索引类型。
InnoDB
AUTO_INCREMENT
SELECT
MAX(
ai_col
)PRIMARY KEY
UNIQUE
AUTO_INCREMENT
本节介绍AUTO_INCREMENT
锁定模式、不同锁定模式设置的使用含义
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_INCREMENT
列t1
:INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另一种类型的“混合模式插入”是
INSERT ... ON DUPLICATE KEY UPDATE
,在最坏的情况下实际上是 anINSERT
后跟 a ,其中在更新阶段可能会或可能不会使用列UPDATE
的分配值 。AUTO_INCREMENT
innodb_autoinc_lock_mode
该变量
有三种可能的设置
。设置为 0、1 或 2,分别表示
“传统”、“连续”或
“交错”锁定模式。
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
是连续的,并且使用的(单个)自增值INSERT
Tx2 中的语句小于或大于用于 Tx1 的所有语句,具体取决于首先执行的语句。只要 SQL 语句在从二进制日志重播时(使用基于语句的复制时,或在恢复场景中)以相同的顺序执行,结果就与 Tx1 和 Tx2 首次运行时相同。因此,表级锁一直保持到语句结束,使
INSERT
使用自动增量的语句可以安全地用于基于语句的复制。但是,当多个事务同时执行插入语句时,那些表级锁会限制并发性和可伸缩性。在前面的示例中,如果没有表级锁,则用于
INSERT
in Tx2 的自增列的值恰好取决于语句执行的时间。如果INSERT
of Tx2 在INSERT
of Tx1 运行时执行(而不是在它开始之前或它完成之后),则这两个语句分配的特定自动增量值INSERT
是不确定的,并且可能因运行而异。在 连续 锁模式下,
InnoDB
可以避免对行数预先已知的 “简单插入”AUTO-INC
语句使用表级锁 ,并且仍然为基于语句的复制保留确定性执行和安全性。如果您不使用二进制日志来重放 SQL 语句作为恢复或复制的一部分, 则可以使用交错 锁定模式来消除对表级
AUTO-INC
锁的所有使用,以实现更高的并发性和性能,但代价是允许 auto 中的间隙-递增语句分配的数字,并可能交错并发执行的语句分配的数字。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_autoinc_lock_mode
为 0 或 1 并在源及其副本上使用相同的值。innodb_autoinc_lock_mode
如果您使用= 2(“交错”)或源和副本不使用相同锁定模式的配置, 则不能确保副本上的自动增量值与源上 的值相同。如果您使用基于行或混合格式的复制,所有的自增锁模式都是安全的,因为基于行的复制对 SQL 语句的执行顺序不敏感(并且混合格式使用基于行的对基于语句的复制不安全的任何语句进行复制)。
“丢失”自动增量值和序列间隙
在所有锁模式(0、1、2)下,如果一个产生自增值的事务回滚,那些自增值就会“丢失”。自增列一旦产生值,就不能回滚,不管 “
INSERT
-like ” 语句是否完成,包含的事务是否回滚。这些丢失的值不会被重用。AUTO_INCREMENT
因此,存储在表的列 中的值可能存在间隙 。AUTO_INCREMENT
为列 指定 NULL 或 0在所有锁定模式(0、1 和 2)中,如果用户为 中的
AUTO_INCREMENT
列 指定 NULL 或 0INSERT
, 则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( “ interleaved ”)时, “批量插入”生成的自动增量值可能存在间隙,但前提是同时执行 “INSERT
-like ” 语句。对于锁定模式 1 或 2,连续语句之间可能会出现间隙,因为对于批量插入,可能不知道每个语句所需的自动增量值的确切数量,并且可能会高估。
由“混合模式插入” 分配的自动增量值
考虑一个“混合模式插入”,其中 “简单插入”指定一些(但不是全部)结果行的自动增量值。这样的语句在锁定模式 0、1 和 2 中的行为不同。例如,假设
c1
是 table 的AUTO_INCREMENT
列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
语句 的中间 修改列值在所有锁定模式(0、1 和 2)中,修改
AUTO_INCREMENT
一系列语句中间的列值INSERT
可能会导致“重复输入” 错误。例如,如果您执行将 列值UPDATE
更改为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); ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
本节介绍如何InnoDB
初始化
AUTO_INCREMENT
计数器。
如果AUTO_INCREMENT
为表指定列InnoDB
,则数据字典中的表句柄
InnoDB
包含一个称为自动递增计数器的特殊计数器,用于为该列分配新值。该计数器仅存储在主内存中,而不存储在磁盘上。
要在服务器重新启动后初始化自动递增计数器,
InnoDB
请在第一次插入包含AUTO_INCREMENT
列的表时执行与以下语句等效的语句。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
InnoDB
increments the value retrieved by the
statement and assigns it to the column and to the auto-increment
counter for the table. By default, the value is incremented by
1. This default can be overridden by the
auto_increment_increment
configuration setting.
If the table is empty, InnoDB
uses the value
1
. This default can be overridden by the
auto_increment_offset
configuration setting.
如果SHOW TABLE STATUS
语句在自动递增计数器初始化之前检查表,则InnoDB
初始化但不递增该值。存储该值供以后插入使用。此初始化在表上使用正常的独占锁定读取,锁定持续到事务结束。InnoDB
遵循为新创建的表初始化自动递增计数器的相同过程。
After the auto-increment counter has been initialized, if you do
not explicitly specify a value for an
AUTO_INCREMENT
column,
InnoDB
increments the counter and assigns the
new value to the column. If you insert a row that explicitly
specifies the column value, and the value is greater than the
current counter value, the counter is set to the specified
column value.
InnoDB
uses the in-memory auto-increment
counter as long as the server runs. When the server is stopped
and restarted, InnoDB
reinitializes the
counter for each table for the first
INSERT
to the table, as described
earlier.
服务器重新启动还会取消
和
语句
中表选项的影响,您可以将其与表一起使用以设置初始计数器值或更改当前计数器值。
AUTO_INCREMENT =
N
CREATE TABLE
ALTER TABLE
InnoDB