MySQL InnoDB AUTO_INCREMENT (1)

想必你已经很熟悉 MySQL Innodb 中的 AUTO_INCREMENT，它是一个约束条件，如果某个字段添加了这个约束条件，插入数据的时候，如果没有给该字段指定一个值，那么它就会自动插入一个自增长的值。

本章节我们不讲 AUTO_INCREMENT 如何使用，而是讲讲 Innodb 是怎么处理它的，有关 AUTO_INCREMENT 的用法，你可以查阅我们的 MySQL 基础教程：MySQL 自增序列 AUTO_INCREMENT

AUTO_INCREMENT 约束

AUTO_INCREMENT 是 Innodb 提供的一种可配置的锁定机制，如果某个表的某一列具有 AUTO_INCREMENT 约束，那么向该表添加数据的时候可以很明显的提高 SQL 语句的性能和可伸缩性。

为了充分使用 Innodb 表的 AUTO_INCREMENT 机制， 必须 将 AUTO_INCREMENT 字段 ( 或列，下面提到的 「 列 」 和字段可以等价 ) 定义为 「 索引 」 的一部分，这样就可以在表上使用索引执行下面的语句来查找最大的列值

    SELECT MAX (ai_col ) FROM tablename;

ai_col 为定义了 AUTO_INCREMENT 约束的列。

通常情况下，为了最大化性能，添加了 AUTO_INCREMENT 约束的列要么独自成一个索引 ( 主索引 )，那么是组合索引中的第一列

需要注意的是： 虽然我们日常使用中会把 AUTO_INCREMENT 添加为主键，但它其实也可以不是主键的。甚至可以不是唯一索引。

AUTO_INCREMENT 不仅仅是一个字段约束条件，它还是一个 「 锁 」，也就是那个很少见到的 「 AUTO_INCREMENT 锁」。

本章节接下来的部分，我们就来介绍介绍这个 「 AUTO_INCREMENT 锁」的模式和行为，包括不同的 「 AUTO_INCREMENT 锁」模式的使用意义，以及 Innodb 如何初始化 「 AUTO_INCREMENT 计数器 」

Innodb AUTO_INCREMENT 锁的模式

使用了 AUTO_INCREMENT 那么多次，我们已经知道它的主要作用就是产生一个不重复的 「 自增值 」。

我们知道，插入多条数据有两种插入方法，一种是一条一条的执行 INSERT INTO，另一种是 INSERT INTO VALUES(...),(...) 多条一起插入

这两种插入方法都能正确的自增 AUTO_INCREMENT 列，它们是如何做的呢 ？

这就仰赖了 AUTO_INCREMENT 锁 ，为了适应这两种插入方法，它同时也具有多种模式。

术语

在我们继续讲解之前，为了方便大家理解一些术语或概念，我们先罗列在此

1. 「 insert like 」 语句

   所有可以在表中添加新行的语句，我们称之为 「 insert like 」 语句，例如

   1. INSERT, INSERT ... SELECT
   2. REPLACE
   3. REPLACE ... SELECT
   4. LOAD DATA
   5. 其它的还有 「 simple-inserts 」、「 bulk-inserts 」和 「 mixed-mode 」 三种插入语句

2. 「 simple-inserts 」 语句

   「 simple-inserts 」 是可以预先确定要插入的行数的语句 ( 最初处理语句时 )。包括不带子查询的 单行 和 多行 INSERT 和REPLACE 语句，但不包括 INSERT ... ON DUPLICATE KEY UPDATE 语句

3. 「 Bulk inserts 」 批量插入

   「 Bulk inserts 」是预先不知道要插入的行数（以及所需的自动增量值的数量）的语句。

   包括 INSERT ... SELECT，REPLACE ... SELECT 和 LOAD DATA 语句，但不包括普通的 INSERT

   在处理每一行时，InnoDB 都会重新为 AUTO_INCREMENT 列分配一个新值

4. 「 Mixed-mode inserts 」 混合模式插入

   「 Mixed-mode inserts 」 是指「 simple-inserts 」 语句中，有些指定了 AUTO_INCREMENT 列的值，而另一些则没有。

   例如下面的 SQL 语句，其中 c1 是表 t1 的 AUTO_INCREMENT 列

        INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种类型的 「 Mixed-mode inserts 」 是 `INSERT ... ON DUPLICATE KEY UPDATE` ，这种语句最坏的情况下实际上是 `INSERT` 后跟 `UPDATE`，其中在更新阶段，可能会也可能不会为 `AUTO_INCREMENT` 列的分配值

innodb_autoinc_lock_mode

我们先不讨论有几种模式，我们先来看看它是如何配置的。

「 AUTO_INCREMENT 锁」模式的配置变量为 innodb_autoinc_lock_mode ，我们可以通过下面的语句查看当前的模式是什么

    show variables like 'innodb_autoinc_lock_mode';

在我的 5.7.22 的版本的 MySQL 中，输出结果为

    mysql> show variables like 'innodb_autoinc_lock_mode';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_autoinc_lock_mode | 1     |
    +--------------------------+-------+

配置参数 innodb_autoinc_lock_mode 有三个可选的值，分别是 0、1 和 2 ，分别代表着 「 传统 」，「 连续 」 或 「 交错 」 三种锁模式

在不同的版本下，innodb_autoinc_lock_mode 的默认值是不一样的，在 mysql >= 8.0.3 版本中是 2，也就是 「 交错 」 模式，而 mysql <= 8.0.2 版本中是 1，也就是 「 连续 」 模式

对于 8.0.3 版本中的这种变更，也反应了 Innodb 的默认 「 复制模式 」 已经从基于 SQL 语句 变更为基于 行 ( row )

基于 SQL 语句的复制需要 「 连续 」 模式的 「 AUTO_INCREMENT 锁」，以确保为给定的 SQL 语句序列以可预测和可重复的顺序分配自动增量值，而基于行的复制对 SQL 语句的执行顺序不敏感

innodb_autoinc_lock_mode = 0 传统锁模式

传统锁模式是在 MySQL 5.1 中引入 innodb_autoinc_lock_mode 配置参数之前的默认模式。现在，传统锁模式存在的意义，仅仅是用于向后兼容，性能测试以及解决 「 混合模式插入 」 问题，因为语义方面可能存在差异。

在这种锁模式下，为了向具有 AUTO_INCREMENT 列的表中插入数据，所有的 「 insert like 」 语句都会获得一个特殊的 表级 AUTO-INC 锁，这种锁会自动添加到 SQL 语句的末尾 ( 不是事务的末尾 )，以确保以可预测且可重复的顺序为给定的 INSERT 语句序列分配自增值，并确保为任何给定语句分配的自增值都是连续的。

在基于 SQL 语句的 ( 主从 ) 复制环境中，在从服务器上运行复制 SQL 语句时，自增量列的值和主服务器的值相同，这样执行多个 INSERT 语句的结果是确定性的，并且从服务器的数据和主服务器的数据一摸一样。

如果多个 INSERT 语句生成的自增值是交错的，那么两个并发 INSERT 语句的结果将是不确定的，这样就无法使用基于 SQL 语句的复制模式将数据可靠地复制到从服务器

讲解的有点拗口，我们看看一些示例，假设存在一张表 t1，它的建表语句如下

    CREATE TABLE t1 (
      c1 INT(11) NOT NULL AUTO_INCREMENT,
      c2 VARCHAR(10) DEFAULT NULL,
      PRIMARY KEY (c1)
    ) ENGINE=InnoDB;

把表建完后，我们假设有两个事务在运行，两个事务都是往 t1 表中插入数据，第一个事务使用一个事务使用 INSERT ... SELECT 语句插入 1000 行的，另一个事务使用简单的 INSERT 语句插入一行数据

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

第一个事务 Tx1 ，因为 InnoDB 无法预先判断在 INSERT 语句从 SELECT 中检索了多少行，所以它会在每插入一条数据的时候分配一个自增值。这种情况下，会使用 表锁 ，会在 SQL 语句的末尾自动添加一个表锁，一次只能在表 t1 执行一条 INSERT 语句，这样就能保证每条 INSERT 语句的自增值是连续的且不会交错。

这样由 Tx1 INSERT ... SELECT 语句生成的自增值是连续的，并且 Tx2 事务中 INSERT 语句使用的 ( 单个 ) 自增值要小于或大于 Tx1 的所有自增值，结果具体取决于哪个语句先执行

这时候在主从复制或数据恢复时，只要以二进制日志重放 SQL 语句时 ( 使用基于语句的复制时或恢复方案中）以相同的顺序执行，那么重放的结果与 Tx1 和 Tx2 首次运行时的结果相同

如果前面的示例没有使用 「 表锁 」 ，那么 Tx2 中 INSERT 的自增列的值取决于语句执行的时间。如果 Tx2 的 INSERT 在 Tx1 的 INSERT 运行时 ( 而不是在它开始之前或完成之后 ) 执行，则两个 INSERT 语句分配的特定自增值是不确定的，并且可能因运行而异。

在 「 连续锁 」模式下，InnoDB 可以避免将表级 AUTO-INC 锁用于 「 insert like 」 语句，因为行数已预先知道，而且还可以确保基于语句的复制的确定性执行和安全性

在恢复或复制数据的重放 SQL 语句时如果不使用二进制日志，那么可以使用 「 交错锁 」模式用来消除表级 AUTO-INC 锁的使用，以获得更高的并发性和性能，但代价语句分配的自增值数字可能不是连续的，而且可能因为并发的执行而存在重复的数字

后记

知识量好大... 单单一个 「 传统 」模式就这么复杂...