MySQL InnoDB AUTO_INCREMENT ( 下 )

撸了今年阿里、腾讯和美团的面试,我有一个重要发现…….

出处:https://www.twle.cn


前面两章节,我们介绍了 MySQL Innodb AUTO_INCREMENT 锁的三种模式,分别为 「 传统模式 」 、 「 连续模式 」 、「 交错模式 」 ,这三种模式我们可以用同学聚会定餐馆来形象的描述下

  1. 「 传统模式 」 – 老板,你家的店我包了,等到我同学聚会完了你再招待其它客人啊….其它客人:坑啊! 你们快点吃啊,慢吞吞的…
  2. 「 连续模式 」 – 老爸,我同学总共 50 个人,先给我来 50 个位置,有些同学可能没来,那就空着。剩下的位置,你可以招待其它的同学。有时候,如果不知道有多少同学 ( 健忘症犯了… ),就只能再回到传统模式了。
  3. 「 交错模式 」 – 老板,我也不知道会来几个同学,反正,来了一个就坐一个位置,其它客人来了也可以坐 ( 假设老板家座位足够 )

这三种锁模式的优缺点是啥 ?

  1. 「 传统模式 」 能保证所有的自增值是连续的,且不会浪费 ( 也就是会一直 123456789…自增下去,不会断掉 ),但其它的事务或语句要等到当前语句执行完才会继续执行
  2. 「 交错模式 」解决了要 「 传统模式 」中要等待的问题,但也会引入新问题,就是可能造成自增值出现断层,比如 12345 缺了 4 这样。同时,在指定插入数据条数不确定的情况下,会回到 「 传统模式 」
  3. 「 交错模式 」 解决了自增值断层的问题,但引入了自增值顺序混乱的问题,可能会导致自增值如下 13245687

交错模式在日常的 SELECT 语句中是不会出啥问题的,因为会按照自增值排序,出问题就处恢复数据或主从过程中的二进制日志回放,可能导致从库或者恢复的数据的自增值和源数据不一致。

好了,MySQL Innodb AUTO_INCREMENT 锁的三种模式我们就介绍到此了,接下来的部分我们讨论讨论下这三种模式在 MySQL Innodb 中的使用说明

MySQL Innodb AUTO_INCREMENT 锁的使用说明

主从复制中的 AUTO_INCREMENT

主从复制时,如果你使用的是基于 SQL 语句的复制,需要将 innodb_autoinc_lock_mode 配置项的值设置为 0 或 1 ,而且主服务器和从服务器上的值必须相同。

如果设置 innodb_autoinc_lock_mode = 2,也就是使用 「 交错锁 」 模式,或者主从服务器上的值不相同,则不能确保从服务器上相同行的自增值和主服务器相同

如果你使用的是基于  ( row ) 或混合模式的主从复制,那么使用任何一个 AUTO_INCREMENT 锁模式都是安全的,因为基于行的复制对 SQL 语句的执行顺序不敏感。

在 交错锁 模式下,主从复制来说,任何基于语句的复制都是不安全的

注意: 混合模式使用的是基于行的复制方式。

「 丢失 」 ( lost ) 自增值和序列间隙

无论你使用的是哪种锁模式 ( 0 , 1 或 2 ),如果生成自增值的事务回滚,则这些自增量值将 「 丢失 」。

一旦为 AUTO_INCREMENT 列生成一个自增值后,无论 「 INSERT-like 」 语句是否完成,以及包含事务是否回滚,都无法回滚该自增值。

这些丢失的自增值不会被重复使用。因此,表的 AUTO_INCREMENT 列中的值可能存在间隙

为 AUTO_INCREMENT 列指定 NULL 或 0 时候

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 ),如果用户在INSERT 语句中为 AUTO_INCREMENT列指定的值为 NULL 或 0,那么 Innodb 将会忽略这些值并为该列生成一个新的自增值。

也就是说,不管是否为 AUTO_INCREMENT 列指定了值为 NULL ,还是指定了值为 0 ,或是未指定,Innodb 都会自动生成一个自增值作为该列的值。

为 AUTO_INCREMENT 列指定一个负值 ( < 0 )

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 ),如果用户在INSERT 语句中为 AUTO_INCREMENT列指定的值为负数,那么 Innodb 为 AUTO_INCREMENT 生成的值就是不确定的 ( is not defined )

也就是说,如果指定了一个负数,那么当前插入行的 AUTO_INCREMENT 列的值就是不确定的 ( is not defined )

如果 AUTO_INCREMENT 列的值大于指定列数据类型 ( 一般是整数类型 ) 的最大整数

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 ),如果 AUTO_INCREMENT 列的值变得大于可以存储在指定整数类型中的最大整数,则最终的结果也是不确定的 ( is not defined )

「 批量插入 」 中的自增值的间隙

在 innodb_autoinc_lock_mode 配置项的值设置为 0 ( 传统锁模式 ) 或 1 ( 连续锁模式 ) 时,任何给定语句生成的自增值都是连续的,没有间隙。

因为在这两个模式下,「 批量插入 」 会使用表级别的 AUTO-INC 锁,且会一直持有直到所有语句运行结束。在表级别的 AUTO-INC 锁控制下,一次只能执行一个这样的语句

在 innodb_autoinc_lock_mode 配置项的值设置为 2 ( 交错锁模式 ) 时,「 批量插入 」 生成的自增值可能存在间隙,仅有的出现前提是同时执行 「 INSERT-like 」 语句

对于锁模式 1 和 2 ,连续的语句间也可能出现间隙,因为批量插入,可能并不知道每个语句所需的确切数量的自增值,可能会存在高估,如果一旦高估了,那么高估的自增值将会被抛弃且永远也不会使用到。

「 混合模式插入 」 中的自增值

「 混合模式插入 」 中,那些 「 简单插入 」中的某些语句 ( 但不是全部 ) 可能显式的为 AUTO_INCREMENT列指定了值。

如果真的发生了这种情况,三种锁模式下的结果是各不相同的。

例如,假设 c1 列是表 t1 的 AUTO_INCREMENT 列,且假设最新自动生成的序列号为 100

 CREATE TABLE t1 (
    c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 CHAR(1)
) ENGINE = INNODB;

现在,我们来看看下面这条混合模式插入语句

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

在 innodb_autoinc_lock_mode 设置为 0 ( 传统锁模式下 ),刚刚插入的 4 条数据如下

 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 ,也就是 「 连续锁模式 」,那么刚刚插入的 4 条数据如下

 mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

虽然最终数据看起来一样。但是,行为结果还是不同的。

在这种情况下,下一个可用的自增值是 105,而不是 103,因为在处理语句时分配了四个自增值,但只使用了两个,也就是 103 和 104 永远的被丢失了。这时候无论是否同时并发的执行其它 「 INSERT-like 」 语句 ( 任何类型 ),此结果都是正确的

但如果设置 innodb_autoinc_lock_mode 的值为 2 ,也就是 「 交错锁模式 」,那么刚刚插入的 4 条数据如下

 mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

x 和 y 的值是唯一的,并且比任何先前生成的行的都大。但是,x 和 y 的具体值取决于同时执行语句生成的自增值的数量。如果没有其它 「 INSERT-like 」语句并发执行,结果和 「 传统锁 」 模式是一样的。

最后,我们来看看下面这个 SQL 语句,假设最新自动生成的序列号为 100

 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') 再使用 101 就会失败,当然了,前提是 AUTO_INCREMENT 列设置为主键或唯一索引

在一序列 INSERT 语句的中间修改 AUTO_INCREMENT 列值

在 MySQL 5.7 及更早版本中,在一序列 INSERT 语句的中间修改 AUTO_INCREMENT 列值可能会导致 「 重复条目 」 ( Duplicate entry ) 错误。例如,如果执行了将 AUTO_INCREMENT 列值更改为大于当前最大自增值的 UPDATE 操作,那些后续的未指定也未使用自增值的 INSERT 操作可能会遇到 「 重复条目 」 错误

在MySQL 8.0 及更高版本中,如果将 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);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
+----+

结束语

知识量好多有没有 ? 这次的文字简单明了,我就不做过多解释了…看完了你有啥感觉?

赞(0) 打赏

如未加特殊说明,此网站文章均为原创,转载必须注明出处。Java 技术驿站 » MySQL InnoDB AUTO_INCREMENT ( 下 )
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

关注【Java 技术驿站】公众号,每天早上 8:10 为你推送一篇技术文章

扫描二维码关注我!


关注【Java 技术驿站】公众号 回复 “VIP”,获取 VIP 地址永久关闭弹出窗口

免费获取资源

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏