2023-03-20
原文作者:简单教程 原文地址:https://www.twle.cn/c/yufei/innodb/innodb-basic-auto_increment.html

想必你已经很熟悉 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 」 是可以预先确定要插入的行数的语句 ( 最初处理语句时 )。包括不带子查询的 单行多行 INSERTREPLACE 语句,但不包括 INSERT ... ON DUPLICATE KEY UPDATE 语句

  3. 「 Bulk inserts 」 批量插入

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

    包括 INSERT ... SELECTREPLACE ... SELECTLOAD DATA 语句,但不包括普通的 INSERT

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

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

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

    例如下面的 SQL 语句,其中 c1 是表 t1AUTO_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 有三个可选的值,分别是 012 ,分别代表着 「 传统 」,「 连续 」 或 「 交错 」 三种锁模式

在不同的版本下,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 锁的使用,以获得更高的并发性和性能,但代价语句分配的自增值数字可能不是连续的,而且可能因为并发的执行而存在重复的数字

后记

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

阅读全文