近期听闻寒潮来临,作为一个热爱学习的人,勇于面对疾风,在第二次寒潮来临时,与某大厂面试官鏖战了近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,肯定要加锁,然后顺序申请。
- 假设事务 A 申请到了 id=1,2,3, 事务 B 申请到 id=4,那么这时候表的自增值是 5,之后继续执行。
- 事务 B 正确提交了,但事务 A 出现了回滚
- 如果允许事务 A 把自增 id 回退,也就是把表 的当前自增值改回 2,那么就会出现这样的情况:表里面已经有 id=4的行,而当前的自增 id 值是 2。
- 接下来,继续执行的其他事务就会申请到 id=2,然后再申请到 id=3,最后到ID=4。这时,就会出现插入语句报错“主键冲突”。
那么如果要解决这个问题,有2种可行方案:
-
每次申请 id 之前,先判断表里面是否已经存在这个 id。如果存在,就跳过这个 id。但是,这个方法的成本很高。因为,本来申请 id 是一个很快的操作,现在还要再去主键索引树上判断 id 是否存在。
-
把自增 id 的锁范围扩大,必须等到一个事务执行完成并提交,下一个事务才能再申请自增 id。这个方法的问题,就是锁的粒度太大,系统并发能力大大下降。
可见,这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首,就是我们假设的这个“允许自增 id 回退”的前提导致的。因此,InnoDB 放弃了这个设计,语句执行失败也不回退自增 id。也正是因为这样,所以才只保证了自增 id 是递增的,但不保证是连续的。
所以你会看到当事务A执行完,但未提交时,事务B 的show create table user2
的 AUTO_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
现在这般,面试官漏出了满意的笑容。
网友评论