美文网首页
面试官居然问我自增主键是否连续

面试官居然问我自增主键是否连续

作者: 丿灬尘埃 | 来源:发表于2023-01-28 17:40 被阅读0次

    近期听闻寒潮来临,作为一个热爱学习的人,勇于面对疾风,在第二次寒潮来临时,与某大厂面试官鏖战了近1个多小时后,本以为大获全胜,谁料大佬随口给来一记重拳:自增主键连续吗?

    我们都知道,创建表时一般都会写上id int(11) NOT NULL AUTO_INCREMENT代表主键的连续性,那么当然要毫不客气的告诉他,连续!必须连续!
    如果这么说了,那么恭喜你,可能就挂了啊,兄dei.
    听我娓娓道来
    首先你要这么告诉他

    1.主键连续性的意义

    自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,索引更紧凑。所以一般都会设置为主键自增。

    但并不是所有情况下自增主键都会连续。下面我会举几个例子来说明下,什么情况下,主键自增但不是连续的。
    在这之前呢,我大大方方的告诉面试官了2个事情,自增值保存在哪里,自增值是如何修改的。

    2.自增值保存在哪里

    先来构建一张表

    CREATE TABLE `user2` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '姓名',
      `age` int(6) NOT NULL DEFAULT '0',
      `phone` varchar(24) NOT NULL DEFAULT '',
      `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户信息'
    

    AUTO_INCREMENT=0 证明下一个插入的值ID应该从1开始
    其实,这个输出结果容易引起这样的误解:自增值是保存在表结构定义里的。实际上,表的结构定义存放在后缀名为.frm 的文件中,但是并不会保存自增值。

    那么到底放在哪里呢?
    不同的引擎对于自增值的保存策略不同。

    • MyISAM 引擎的自增值保存在数据文件中。
    • InnoDB 引擎的自增值,保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”,具体情况是:
      • 在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表时,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。举例来说,如果一个表当前数据行里最大的 id 是 10,AUTO_INCREMENT=11。这时候,我们删除 id=10 的行,AUTO_INCREMENT 还是 11。但如果马上重启实例,重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说,MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
      • 在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。

    3.自增值怎么修改(what, how, when )

    3.1如何修改

    当定义主键为AUTO_INCREMENT时,插入一条数据时,会做如下行为
    1.当插入数据时,ID指定为0 或者 null时,就把这个表当前的AUTO_INCREMENT填写到自增字段ID上
    2.如果ID指定了值,就用指定的值插入。

    3.2 什么时候修改

    1.首先插入insert into user2(id,name, age, phone) values (null, '主键', 12, '32432432');,因为没有指定id,先获取表的自增值当做主键(假设此时为1)
    2.将传入的值改为insert into user2(id,name, age, phone) values (1,'主键', 12, '32432432');
    3.将自增值改为2
    4.继续执行操作

    可以看到,这个表的自增值改成 2,是在真正执行插入数据的操作之前。

    4.自增主键不连续的情况

    4.1 事务回滚

    假设主键连续的情况下,t5时刻和t3 时刻的主键都应该是1,因为事务A 发生了回滚,假如没有发生回滚,那么事物B的ID应该是4.
    看效果:


    结果却是主键不是1而是4,为什么会是这样呢?
    为什么在出现回滚的时候,MySQL 没有把表 的自增值改回去呢?如果把表 的当前自增值从 4 改回 1,再插入新数据的时候,不就可以生成 id=2 的一行数据了吗?其实,MySQL 这么设计是为了提升性能。接下来,分析一下这个设计思路,看看自增值为什么不能回退。

    假设有两个并行执行的事务,在申请自增值的时候,为了避免两个事务申请到相同的自增 id,肯定要加锁,然后顺序申请。

    1. 假设事务 A 申请到了 id=1,2,3, 事务 B 申请到 id=4,那么这时候表的自增值是 5,之后继续执行。
    2. 事务 B 正确提交了,但事务 A 出现了回滚
    3. 如果允许事务 A 把自增 id 回退,也就是把表 的当前自增值改回 2,那么就会出现这样的情况:表里面已经有 id=4的行,而当前的自增 id 值是 2。
    4. 接下来,继续执行的其他事务就会申请到 id=2,然后再申请到 id=3,最后到ID=4。这时,就会出现插入语句报错“主键冲突”。

    那么如果要解决这个问题,有2种可行方案:

    1. 每次申请 id 之前,先判断表里面是否已经存在这个 id。如果存在,就跳过这个 id。但是,这个方法的成本很高。因为,本来申请 id 是一个很快的操作,现在还要再去主键索引树上判断 id 是否存在。

    2. 把自增 id 的锁范围扩大,必须等到一个事务执行完成并提交,下一个事务才能再申请自增 id。这个方法的问题,就是锁的粒度太大,系统并发能力大大下降。

    可见,这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首,就是我们假设的这个“允许自增 id 回退”的前提导致的。因此,InnoDB 放弃了这个设计,语句执行失败也不回退自增 id。也正是因为这样,所以才只保证了自增 id 是递增的,但不保证是连续的。

    所以你会看到当事务A执行完,但未提交时,事务B 的show create table user2AUTO_INCREMENT=4也就证实了这个说法。

    4.2 唯一健冲突

    其实当唯一健冲突时,也同样发生了回滚,所以具体情况和4.1所说的事务回滚一样。

    4.3自增主键的批量申请

    上SQL

    insert into user2 (id,name,age)values(null, 1,1);
    insert into user2 (id,name,age) values(null, 2,2);
    insert into user2 (id,name,age) values(null, 3,3);
    insert into user2 (id,name,age) values(null, 4,4);
    create table user3 like user2;
    insert into user3(name,age) select name,age from user2;
    show create  table user3;
    insert into user3 (id,name,age) values(null, 5,5);
    show create  table user3;
    

    结果:


    可以看到此时主键发生了不连续性。
    这是什么情况呢。那么一起来看下主键锁吧。

    5.自增锁

    5.1自增锁的设计历史

    在 MySQL 5.0 版本的时候,自增锁的范围是语句级别。也就是说,如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放。显然,这样设计会影响并发度。

    MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode,默认值是 1。

    • 这个参数的值被设置为 0 时,表示采用之前 MySQL 5.0 版本的策略,即语句执行结束后才释放锁;
    • 这个参数的值被设置为 1 时:普通 insert 语句,自增锁在申请之后就马上释放;类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
    • 这个参数的值被设置为 2 时,所有的申请自增主键的动作都是申请后就释放锁。

    当我说完这个的时候,隐约像是给自己挖了一个大坑,面试官抓住机会问到:为什么默认设置下,insert … select 要使用语句级的锁?为什么这个参数的默认值不是 2?

    大大方方的告诉他,这么设计是为了数据一致性
    回想下4.3的SQL


    在这个例子里,我往表 user2中插入了 4 行数据,然后创建了一个相同结构的表 user3,然后两个 session 同时执行向表 user3 中插入数据的操作。

    你可以设想一下,如果 session B 是申请了自增值以后马上就释放自增锁,那么就可能出现这样的情况

    • session B 先插入了两个记录,(1,1,1)、(2,2,2);
    • 然后,session A 来申请自增 id 得到 id=3,插入了(3,5,5);
    • 之后,session B 继续执行,插入两条记录 (4,3,3)、 (5,4,4)。

    你可能会说,这也没关系吧,毕竟 session B 的语义本身就没有要求表 t2 的所有行的数据都跟 session A 相同。是的,从数据逻辑上看是对的。但是,如果我们现在的 binlog_format=statement,你可以设想下,binlog 会怎么记录呢?

    binlog 拿去从库执行,或者用来恢复临时实例,备库和临时实例里面,session B 这个语句执行出来,生成的结果里面,id 都是连续的。这时,这个库就发生了数据不一致。

    其实,这是因为原库 session B 的 insert 语句,生成的 id 不连续。这个不连续的 id,用 statement 格式的 binlog 来串行执行,是执行不出来的。

    所以解决办法如下2种:
    1.让原库的批量插入数据语句,固定生成连续的 id 值。所以,自增锁直到语句执行结束才释放,就是为了达到这个目的。

    2.在 binlog 里面把插入数据的操作都如实记录进来,到备库执行的时候,不再依赖于自增主键去生成。这种情况,其实就是 innodb_autoinc_lock_mode 设置为 2,同时 binlog_format 设置为 row。

    因此,在生产上,尤其是有 insert … select 这种批量插入数据的场景时,从并发插入数据性能的角度考虑,建议设置:innodb_autoinc_lock_mode=2 ,并且 binlog_format=row. 这样做,既能提升并发性,又不会出现数据一致性问题。(到这里不信面试官不给你竖个大拇指。)

    但是,在普通的 insert 语句里面包含多个 value 值的情况下,即使 innodb_autoinc_lock_mode 设置为 1,也不会等语句执行完成才释放锁。因为这类语句在申请自增 id 的时候,是可以精确计算出需要多少个 id 的,然后一次性申请,申请完成后锁就可以释放了。也就是说,批量插入数据的语句,之所以需要这么设置,是因为“不知道要预先申请多少个 id”。

    既然预先不知道要申请多少个自增 id,那么一种直接的想法就是需要一个时申请一个。但如果一个 select … insert 语句要插入 10 万行数据,按照这个逻辑的话就要申请 10 万次。显然,这种申请自增 id 的策略,在大批量插入数据的情况下,不但速度慢,还会影响并发插入的性能。

    因此,对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:

    1.语句执行过程中,第一次申请自增 id,会分配 1 个;
    2.1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;
    2.2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个;
    3.依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。
    所以你看4.3的结果图自增主键已经到了8

    现在这般,面试官漏出了满意的笑容。

    相关文章

      网友评论

          本文标题:面试官居然问我自增主键是否连续

          本文链接:https://www.haomeiwen.com/subject/atayhdtx.html