设计MySQL表时,我们一般会设置一个自增主键,从而让主键索引尽可能地保持递增的趋势,这样可以避免页分裂,让MySQL顺序写入,大大提高MySQL的性能。
但是,自增主键只能保持大致递增,无法保证顺序递增。
当我们创建完一个表后,通过show create table命令,可以看到MySQL定义了AUTO_INCREMENT来指定主键的递增值。
在MySQL5.7之前,这个递增值是直接保存在内存里面的,当服务器重启后,MySQL会读取表里面的最大主键id,然后将最大值+1作为下次递增的值。
在MySQL8.0时,将其优化为了保存在redo log中,从而实现了递增值的持久化。
那都有哪些情况可能导致主键不能连续递增呢?
首先我们要知道的是,MySQL对于主键递增值得使用是一次性的,即每次获取完递增值之后,不管接下来的语句是否能真正执行成功,这个递增值都不会再回收利用了。
有时为了满足业务的需要,我们有时会对表中的字段设置唯一索引。但是当唯一索引冲突时,会产生什么问题呢?
以上面的user表为例,我们对name设置唯一索引。
我们执行两次以下语句:
INSERT into user values (null,'张三','123456');
不难猜到,第二次的执行结果肯定会报错:
我们在上面已经提到,MySQL对于递增值的使用是一次性的,那么第二次执行插入时,不管语句成功还是失败,那么这个递增值就会浪费掉。
这时,我们再执行一条正常的不冲突的插入语句,会发现主键id产生了间隔。
与唯一索引冲突类似,当我们在一个事务中执行插入语句时,那么必然会向MySQL申请一个递增值作为主键id,如果最后事务没有提交,而是回滚,那么这个递增值自然也就浪费掉了。
为了保证主键id的唯一性,在申请自增id时,MySQL会对申请操作加锁。一般情况下,这个申请动作会很快。
对于一般的批量插入,比如insert into ... values(xxx),由于插入的Value个数可以提前计算得出,MySQL会一次性的申请足够数量的id,以保证性能。
但是对于insert into ... select 这种语句就有点麻烦了,由于无法确定到底需要申请多个主键id,如果插入一条申请一个的话,假设要插入100万条记录,那就得申请100万次,可想而知性能会有多么差劲。
所以对于这种批量插入的语句,MySQL采用了一种翻倍申请的优化策略:
语句执行时,第一次申请一个自增id,第二次申请2个自增id,第三次申请4个自增id...
即每次申请的数量都比上次多一倍,这样虽然会浪费一些自增id,但是可以保证插入的效率,从性能角度来看,是可以接受的。
大家可能会有点疑问,为什么自增id是一次性使用的?
其实原因也很简单,大家稍微一想就明白了。
假设有两个事务在同时执行,为了保证自增id的唯一性,MySQL会对申请动作加锁,然后两个事务各获得一个自增id。比如事务1申请到了自增id100,事务2申请到了自增id101。
当事务2成功提交,事务1因为某些原因回滚了。
如果我们要回退复用事务1的id,将AUTO_INCREMENT又设置成了100+1,那么下一个事务来申请自增id时,就会拿到101,而这时101已经被事务2用掉了,就会造成主键冲突。
当然我们也可以每次都让MySQL检查一下主键是否冲突,如果冲突就跳过这个id,但是这样一来,本来申请自增id这个很轻的动作就会变得很重,对性能的影响就会很大。
所以,从性能角度考虑,InnoDB只保证了主键id是大致递增的,而不保证是顺序递增的。
我是@程序员拾山,欢迎关注我,期待与大家一起学习成长,也感谢您的点赞和关注。