MySQL InnoDB中的AUTO_INCREMENT处理

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_INCREMENT
table 的 列是 t1
：

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

innodb_autoinc_lock_mode
 配置参数 有三种可能的设置 。对于“ 传统 ”，“ 连续 ”或 “ 交错 ”锁定模式，设置分别为0、1或2 。

•innodb_autoinc_lock_mode = 0
 （“ 传统 ”锁定模式）传统的锁定模式提供与innodb_autoinc_lock_mode
 在MySQL 5.1中引入配置参数之前相同的行为 。由于语义可能存在差异，提供了传统的锁定模式选项是为了向后兼容，性能测试以及解决“混合模式插入”问题。在这种锁定模式下，所有“ 类似于INSERT ”的语句都将获得特殊的表级AUTO-INC
 锁定，以将其插入具有 AUTO_INCREMENT
列的表中。此锁通常保持在语句的末尾（而不是事务的末尾），以确保为给定的INSERT
 语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增值任何给定语句分配的都是连续的。对于基于语句的复制，这意味着在副本服务器上复制SQL语句时，自动增量列使用与源服务器相同的值。执行多个INSERT
语句的结果 是确定性的，并且副本将复制与源上相同的数据。如果对由多个INSERT
语句生成的自动增量值进行交织，则两个并发INSERT
语句的结果 将是不确定的，并且无法使用基于语句的复制可靠地传播到副本服务器。为了清楚起见，请考虑使用该表的示例：

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

InnoDB
无法预先判断从Tx1 SELECT
的INSERT
语句中检索了多少行 ，并且随着 语句的进行，它每次一次分配一个自动增量值。使用表级锁（保持在该语句的末尾），一次只能执行一个 INSERT
引用表的语句t1
，并且不会交错使用不同的语句生成自动递增编号。Tx1 INSERT ... SELECT
语句生成的自动增量值是连续的，并且Tx1 语句使用的（单个）自动增量值是连续的。 INSERT
 Tx2中的语句小于或大于用于Tx1的所有语句，具体取决于哪个语句首先执行。从二进制日志重播时，只要SQL语句以相同的顺序执行（使用基于语句的复制或在恢复方案中），结果与Tx1和Tx2首次运行时的结果相同。因此，在语句结束之前保持的表级锁使INSERT
使用自动增量的语句可以 安全地用于基于语句的复制。但是，当多个事务同时执行insert语句时，这些表级锁会限制并发性和可伸缩性。In the preceding example, if there were no table-level lock, the value of the auto-increment column used for the INSERT
 in Tx2 depends on precisely when the statement executes. If the INSERT
 of Tx2 executes while the INSERT
 of Tx1 is running (rather than before it starts or after it completes), the specific auto-increment values assigned by the two INSERT
 statements are nondeterministic, and may vary from run to run.在 连续 锁定模式下，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_INCREMENT
table 的 列 t1
，并且最近自动生成的序列号为100。

现在，考虑以下“ 混合模式插入 ” 语句：

随着 innodb_autoinc_lock_mode
 设置为0（“ 传统 ”），这四个新的行是：

下一个可用的自动递增值是103，因为自动递增值一次分配一次，而不是在语句执行开始时一次分配一次。无论是否存在 （任何类型的）“ INSERT
-like ”语句，该结果都是正确的 。随着 innodb_autoinc_lock_mode
 设置为1（“ 连续 ”），这四个新行也：

但是，在这种情况下，下一个可用的自动递增值是105，而不是103，因为在处理语句时分配了四个自动递增值，但只使用了两个。无论是否存在 （任何类型的）“ INSERT
-like ”语句，该结果都是正确的 。与 innodb_autoinc_lock_mode
 设定为模式2（“ 交织 ”），四个新的行是：

的值x
和 y
是独一无二的，比任何先前产生的行大。然而，具体的数值 x
，并 y
依赖于并行执行语句生成自动递增值的数量。最后，考虑以下语句，该语句是在最近生成的序列号为100时发出的：

使用任何 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
操作，则未指定未使用的自动增量值的后续操作可能会遇到“ 重复输入 ”错误。在下面的示例中演示了此行为。

InnoDB AUTO_INCREMENT计数器初始化

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

如果AUTO_INCREMENT
为InnoDB
表指定列，则InnoDB
数据字典中的表句柄 包含一个称为自动增量计数器的特殊计数器，该计数器用于为该列分配新值。该计数器仅存储在主存储器中，而不存储在磁盘上。

要在服务器重新启动后初始化自动增量计数器，请在 InnoDB
包含AUTO_INCREMENT
列的表的第一次插入中执行以下语句的等效项。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB
递增该语句检索的值，并将其分配给该列以及该表的自动递增计数器。默认情况下，该值增加1。此默认值可被auto_increment_increment
 配置设置覆盖 。

如果表为空，则InnoDB
使用值 1
。此默认设置可以被auto_increment_offset
 配置设置覆盖 。

如果一条SHOW TABLE STATUS
语句在初始化自动递增计数器之前检查了表，则InnoDB
初始化但不递增该值。存储该值以供以后的插入使用。该初始化使用对表的常规互斥锁定读取，并且该锁定持续到事务结束。InnoDB
遵循与为新创建的表初始化自动增量计数器相同的过程。

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

InnoDB
只要服务器运行，就使用内存中的自动增量计数器。如上文所述，当服务器停止并重新启动服务器后，请InnoDB
为每个表的第一个表重新初始化计数器 INSERT
。

服务器重新启动也会取消and 语句中 table选项的影响 ，您可以将它们与表一起使用以设置初始计数器值或更改当前计数器值。 AUTO_INCREMENT = *
N*
CREATE TABLE
ALTER TABLE
InnoDB

注意

•当AUTO_INCREMENT
整数列的值用完时，后续INSERT
 操作将返回重复键错误。这是一般的MySQL行为。•当您重新启动MySQL服务器时，InnoDB
 可能会重复使用为AUTO_INCREMENT
列生成但从未存储的旧值 （即，在回滚的旧事务期间生成的值）。