mysql innoDB 中的AUTO_INCREMENT 学习

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

InnoDB auto_increment 锁定模式

自动增量锁定模式采用innodb_autoinc_lock_mode参数配置。

innodb_autoinc_lock_mode 变量有3三种可设置的值，0、1、2，分别为 “traditional”, “consecutive”, or “interleaved”模式：  “传统”、 “连续”或  “交错”锁定模式。

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

传统的锁定模式提供与innodb_autoinc_lock_mode 引入变量之前存在的相同行为 。由于语义可能存在差异，提供传统锁定模式选项是为了向后兼容、性能测试和解决“混合模式插入”问题。

在这种锁定模式下，所有“类似 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;




Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB无法预先知道从Tx1 select中的INSERT语句中检索了多少行 ，并且随着 语句的进行，它一次分配一个自动增量值。使用表级锁，一直保持到语句的末尾，一次只能执行一条 INSERT引用 table 的语句t1，并且不同语句的自增数的生成不会交错。Tx1INSERT ... SELECT语句生成的自增值 是连续的，使用的（单个）自增值 INSERT Tx2 中的语句小于或大于所有用于 Tx1 的语句，具体取决于哪个语句先执行。

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

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），如果用户指定NULL或0用于AUTO_INCREMENT在列 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。