自增主键是MySQL提供给我们的一种选择,使用自增主键,可以保持主键递增顺序插入,但是为什么在实际开发中,我们会选择诸如雪花算法这样的外部工具类作为主键呢
想必是因为主键自增其自身的局限性罢了
首先说,主键自增是不能保证连续递增的只能保证递增这一个单一概念,这一篇大抵都是在讲,为什么会不保证主键可以连续递增
首先,我们拿一个表来说事,一个表上有一个主键索引和一个唯一索引,那么
| CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c` (`c`) ) ENGINE=InnoDB; |
自增值的保存问题
首先,我们执行了insert into t values(null,1,1),然后执行show create table命令,查看如下的结果
看出来,AUTO_INCREMENT=2 表示下一次插入主键,会生成自增值,id=2
这个看起来是保存在了表结构定义当中,而不其实不是,而是保存在了.frm文件中
不同的引擎对自增值的保存策略不同
MyISAM 引擎自增值保存在了数据文件中
InnoDB引擎,保存在了内存中,而且在MySQL 8.0之后才有了自增值持久化的功能,在此之前,都是发生了重启,去打开表,找自增值的最大值,然后将最大值_+1作为这个表的自增值
也就是说,一个表的最大id是10,AUTO_INCREMENT=11,这时候 删除id=10的行,那么这个AUTO_INCREMENT还是11,但是重启后,就变为了10
在8.0后,自增值的变动被记录在了redo log中,重启时候靠redo log恢复之前的值
接下来讲解自增值的修改时机和修改策略
一个字段被定义为了AUTO_INCREMENT,插入一行数据的会后,如果id为0 null 或者不填,那么就会把表当前的AUIO_INCREMENT的值填到自增字段
如果指定了值,就直接使用语句中指定的值
如果要插入的值为X,当前值为Y
X<Y 自增值不变,X>=Y 将当前自增值改为新的自增值
这个新的自增值取决于 auto_increment_offset开始和auto_increment_increment为步长,持续叠加,知道找到一个大于X的值
说完上面的了,可以说主键为什么不会保证完全的顺序自增
1.一个举例
假设已经有了(1,1,1)的记录,再插入一条
insert into t values(null, 1, 1);
主键会被分配为一个2,主键被设置为了3
但是因为c是唯一索引,导致插入错误
主键仍然是3
从而下一个主键分配的要为5,导致了主键不是连续的情况
于是,唯一键冲突导致了主键自增不连续
2.事务的回滚
这样的执行,导致了主键自增了,但是事务回滚了
这样可能有一个问题,为什么出现唯一键冲突或者回滚的时候,为什么不把主键也回滚了,
这样是因为MySQL要提高性能,主要原因为如下的
1.如果不同事务在需要并行的时候,为了避免申请到相同的自增id,必然要加锁,然后顺序申请
如果事务A申请到了2,B申请到了3
那么B事务提交了,A事务回滚了,导致自增id变为了2,然后再插入新的值,就会发现主键id重复
如果非要可以回滚,那么可以按照如下的方式去解决
1.申请id之前,先判断是否已经存在了这个id是否已经存在了,这样会导致每次申请id都需要判断,太麻烦了
2.在自增id锁范围扩大,必须等着一个事务执行完成后提交,下一个事务才能继续申请id,但是这样会让并发能力下降
故主键不能进行回滚
但,自增锁可以进行优化
为了避免申请主键id带了的性能损耗,MySQL做了一些微不足道的工作
MySQL引入了一个策略,innodb_autoinc_lock_mode,默认为1
这个值为0,表示是原本的策略,执行语句结束后才释放锁 为1,insert语句,可以确定有多少个的时候,释放锁
像是insert…select 的批量插入数据,需要等待语句结束在释放
设置为2,所有的申请主键的动作都是申请后就释放
为什么批量插入的时候,不申请完就释放锁呢?
假设如下:
在这个例子中,先插入了4个数据,在创建了一个表2
然后同时往表2里面插入 (null,5,5)和t的数据
如果主键是申请之后立刻释放,这个可能会出现:
session B 先插入了两个记录，(1,1,1)、(2,2,2)；然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；之后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4)。
说实话,为了避免出现这个情况,有两种思路
1.批量插入数据语句,生成固定连续的id,等待语句结束
2.设置binlog为row,保证不会依赖自增主键去执行备库语句
所以,当设置为1时候,会出现两种情况
如果是设置申请完自增id时候,可以算出多少个id的,可以直接释放
如果是select … insert这样的语句,不确定申请了多少个id的,就只能等待语句结束
当然对于select … insert这样的批量插入的语句,有了一种优化
就是会递增式的增加给予的id,比如在语句执行过程中
第一次申请,分配1个,第二次申请的时候,分配了2个,然后再去申请,就会分配4个,依次类推,每次都是上一次的2倍
这也会导致主键自增id不连续
总结:
主键不能自增的三大原因为
1.唯一键冲突
2.事务的回滚
3.MySQL对于批量插入的优化