本文将介绍两个问题:
- MySQL数据库为什么需要自增主键?
- 自增主键为什么是递增不连续的?
下面分别看下这两个问题
一、MySQL数据库为什么需要自增主键
MySQL常用的InnoDB存储引擎是使用B+Tree来维护索引的,通过B+Tree来维护索引的有序性,在插入新值时需要做必要的维护,以下图为例
如果新增ID列为700,则只需在R5后插入即可。如果新增加的是400,就相对麻烦了,需要逻辑上挪动后面的数据。有更糟糕的情况,如果R5所在的数据页已经满了,根据B+Tree的算法,这时需要申请一个新的数据页,然后挪动部分数据。这个过程叫做页分裂,在这种情况下,性能会受到影响。除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页,整体空间利用率降低大约50%。
当然有分裂就有合并,当相邻两个页由于删除数据,利用率很低之后,会将数据页做合并。合并过程,可以认为是分裂的逆向过程,所以一般情况下都是逻辑删除。
自增主键每次都是追加记录,不涉及挪动数据,也不会触发页分裂。这是自增主键的好处之一。
除了考虑性能,还需要从存储空间的角度来看。假如用其他比较长的字符串做主键,普通索引的叶子节点是主键索引,那势必会占用较大的空间,而如果用自增主键的话,占用的空间相对小很多。这也是自增主键的好处之一。
好了到这里你应该清楚了为什么使用自增主键了吧,关于索引的分类欢迎留言讨论。下面来看下一个问题,为什么自增主键能保证自增,但是不保证连续?
二、自增主键为自增但是非连续
以下表为例
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(11) DEFAULT NULL,
`id_card` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在这个表里执行insert语句插入数据后,在看表结构会有哪些变化
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(11) DEFAULT NULL,
`id_card` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`id_card`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
可以看到,表定义里面出现了一个 AUTO_INCREMENT=2,表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。
这个输出结果容易引起这样的误解:自增值是保存在表结构定义里的。实际上,表的结构定义存放在后缀名为.frm 的文件中,但是并不会保存自增值。
不同的引擎对于自增值的保存策略不同。
- MyISAM 引擎的自增值保存在数据文件中。
- InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”,具体情况是:
- 在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。
- 在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。
自增值的修改机制
如果主键id被定义为AUTO_INCREMENT,在插入一行数据时,会有如下行为:
- 如果插入数据 id字段指定为0、null或未指定值,那么就把这个表当前的AUTO_INCREMENT值填到自增字段;
- 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。
新的自增值生成算法是:从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值。
自增值的修改时机
insert语句的执行流程如下
- 执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,'张三', 1);
- InnoDB 发现用户没有指定自增 id 的值,获取表 user 当前的自增值 2;
- 将传入的行的值改成 (2,'李四', 1);
- 将表的自增值改成 3;
- 继续执行插入数据操作,由于已经存在 id_card=1 的记录,所以报 Duplicate key error,语句返回。
可以看到,这个表的自增值改成 3,是在真正执行插入数据的操作之前。这个语句真正执行的时候,因为碰到唯一键 id_card冲突,所以 id=2 这一行并没有插入成功,但也没有将自增值再改回去。
这种情况就出现了自增值不连续的情况。唯一键会引起自增值的不连续。
同样的,事务回滚也会引起自增值的不连续,这很好理解,事务回滚后自增值不会跟着回滚,所以事务回滚也是导致自增值不连续的原因,但是是递增的。
为什么在出现唯一键冲突或者回滚的时候,MySQL没有回滚自增主键呢?其实,MySQL这么设计是为了提升性能。
假设有两个并行执行的事务,在申请自增值的时候,为了避免两个事务申请到相同的自增 id,肯定要加锁,然后顺序申请。
- 假设事务 A 申请到了 id=2, 事务 B 申请到 id=3,那么这时候表 t 的自增值是 4,之后继续执行。
- 事务 B 正确提交了,但事务 A 出现了唯一键冲突。
- 如果允许事务 A 把自增 id 回退,也就是把表 t 的当前自增值改回 2,那么就会出现这样的情况:表里面已经有 id=3 的行,而当前的自增 id 值是 2。
- 接下来,继续执行的其他事务就会申请到 id=2,然后再申请到 id=3。这时,就会出现插入语句报错“主键冲突”
而为了解决这个主键冲突,有两种方法:
- 每次申请 id 之前,先判断表里面是否已经存在这个 id。如果存在,就跳过这个 id。但是,这个方法的成本很高。因为,本来申请 id 是一个很快的操作,现在还要再去主键索引树上判断 id 是否存在。
- 把自增 id 的锁范围扩大,必须等到一个事务执行完成并提交,下一个事务才能再申请自增 id。这个方法的问题,就是锁的粒度太大,系统并发能力大大下降。
可见,这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首,就是我们假设的这个“允许自增 id 回退”的前提导致的。
因此,InnoDB 放弃了这个设计,语句执行失败也不回退自增 id。也正是因为这样,所以才只保证了自增 id 是递增的,但不保证是连续的。
往期精彩内容推荐:
MySQL数据更新流程原来这么复杂-CSDN博客