Documentation Home
MySQL 8.0 参考手册  / 第 15 章 InnoDB 存储引擎  / 15.6 InnoDB 磁盘结构  / 15.6.1 表格  /  14.6.1.6 InnoDB 中的 AUTO_INCREMENT 处理

14.6.1.6 InnoDB 中的 AUTO_INCREMENT 处理

InnoDB提供了一种可配置的锁定机制,可以显着提高将行添加到具有 AUTO_INCREMENT列的表的 SQL 语句的可伸缩性和性能。要在 表中 使用该AUTO_INCREMENT机制, 必须将列定义为某个索引的第一列或唯一列,以便可以对表执行相当于索引查找的操作以获得最大列值。索引不需要是or ,但为了避免 列中的重复值,建议使用这些索引类型。 InnoDBAUTO_INCREMENTSELECT MAX(ai_col)PRIMARY KEYUNIQUEAUTO_INCREMENT

本节介绍AUTO_INCREMENT锁定模式、不同锁定模式设置的使用含义 AUTO_INCREMENT以及如何 InnoDB初始化 AUTO_INCREMENT计数器。

InnoDB AUTO_INCREMENT 锁定模式

本节介绍AUTO_INCREMENT 用于生成自动增量值的锁定模式,以及每种锁定模式如何影响复制。自增锁定模式是在启动时使用 innodb_autoinc_lock_mode 变量配置的。

以下术语用于描述 innodb_autoinc_lock_mode 设置:

  • INSERT-like 语句

    在表中生成新行的所有语句,包括 INSERTINSERT ... SELECTREPLACEREPLACE ... SELECTLOAD DATA。包括简单插入批量插入混合模式 插入。

  • 简单的插入

    可以预先确定要插入的行数的语句(当语句最初被处理时)。这包括单行和多行 INSERT以及 REPLACE没有嵌套子查询的语句,但不包括 INSERT ... ON DUPLICATE KEY UPDATE.

  • 批量插入

    事先不知道要插入的行数(以及所需的自动增量值的数量)的语句。这包括 INSERT ... SELECTREPLACE ... SELECTLOAD DATA语句,但不包括普通 INSERT的 。在处理每一行时,一次为列 InnoDB分配一个新值。AUTO_INCREMENT

  • 混合模式插入

    这些是简单插入语句,它们为一些(但不是全部)新行指定自动增量值。下面是一个示例,其中 c1是表的一 AUTO_INCREMENTt1

    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是连续的,并且使用的(单个)自增值 INSERTTx2 中的语句小于或大于用于 Tx1 的所有语句,具体取决于首先执行的语句。

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

    在前面的示例中,如果没有表级锁,则用于 INSERTin Tx2 的自增列的值恰好取决于语句执行的时间。如果 INSERTof Tx2 在INSERTof Tx1 运行时执行(而不是在它开始之前或它完成之后),则这两个语句分配的特定自动增量值 INSERT是不确定的,并且可能因运行而异。

    连续 锁模式下,InnoDB可以避免对行数预先已知的 简单插入AUTO-INC语句使用表级锁 ,并且仍然为基于语句的复制保留确定性执行和安全性。

    如果您不使用二进制日志来重放 SQL 语句作为恢复或复制的一部分, 则可以使用交错 锁定模式来消除对表级 AUTO-INC锁的所有使用,以实现更高的并发性和性能,但代价是允许 auto 中的间隙-递增语句分配的数字,并可能交错并发执行的语句分配的数字。

  • innodb_autoinc_lock_mode = 1连续锁定模式)

    这是默认的锁定模式。在这种模式下,批量插入使用特殊的AUTO-INC 表级锁并将其保持到语句结束。这适用于所有 INSERT ... SELECTREPLACE ... SELECTLOAD 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(交错)或源和副本不使用相同锁定模式的配置, 则不能确保副本上的自动增量值与源上 的值相同。

    如果您使用基于行或混合格式的复制,所有的自增锁模式都是安全的,因为基于行的复制对 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( “ interleaved )时, 批量插入生成的自动增量值可能存在间隙,但前提是同时执行 INSERT-like 语句。

    对于锁定模式 1 或 2,连续语句之间可能会出现间隙,因为对于批量插入,可能不知道每个语句所需的自动增量值的确切数量,并且可能会高估。

  • 由“混合模式插入 分配的自动增量值

    考虑一个混合模式插入,其中 简单插入指定一些(但不是全部)结果行的自动增量值。这样的语句在锁定模式 0、1 和 2 中的行为不同。例如,假设c1是 table 的 AUTO_INCREMENTt1,并且最近自动生成的序列号是 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 计数器初始化

本节介绍如何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 = NCREATE TABLEALTER TABLEInnoDB

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

  • 当您重新启动 MySQL 服务器时,InnoDB 可能会重用为 AUTO_INCREMENT列生成但从未存储的旧值(即,在回滚的旧事务期间生成的值)。