1. 首页
  2. mysql面试题

04-四、MySQL 面试题 InnoDB AUTO_INCREMENT ( 下 )

前面两章节,我们介绍了 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 配置项的值设置为 01 ,而且主服务器和从服务器上的值必须相同。

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

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

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

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

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

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

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

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

为 AUTO_INCREMENT 列指定 NULL0 时候

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 ),如果用户在INSERT 语句中为 AUTO_INCREMENT 列指定的值为 NULL0,那么 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 列是表 t1AUTO_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 | +-----+------+

xy 的值是唯一的,并且比任何先前生成的行的都大。但是,xy 的具体值取决于同时执行语句生成的自增值的数量。如果没有其它 「 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 | +----+

结束语

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

希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

看完两件小事

如果你觉得这篇文章对你挺有启发,我想请你帮我两个小忙:

  1. 关注我们的 GitHub 博客,让我们成为长期关系
  2. 把这篇文章分享给你的朋友 / 交流群,让更多的人看到,一起进步,一起成长!
  3. 关注公众号 「方志朋」,公众号后台回复「666」 免费领取我精心整理的进阶资源教程
  4. JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区,是给开发者用的 Hacker News,技术文章由为你筛选出最优质的干货,其中包括:Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者,你每天都可以在这里找到技术世界的头条内容。

    本文著作权归作者所有,如若转载,请注明出处

    转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com

    标题:04-四、MySQL 面试题 InnoDB AUTO_INCREMENT ( 下 )

    链接:https://www.javajike.com/article/1608.html

« 05-五、MySQL 面试题 InnoDB AUTO_INCREMENT ( 末 )
03-三、MySQL 面试题 InnoDB AUTO_INCREMENT ( 中 )»

相关推荐

QR code