注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL自增列问题

MySQL 彭东稳 2164次浏览 已收录 0个评论

MySQL 8.0修复了一个MySQL十年之久的自增列Bug,在2003年由Percona的CEO(当时应该还没Percona吧)提出的bug#199。先重现一下这个BUG。

这里我们关闭mysqld,再启动mysqld,然后再插入一条数据。

我们看到AUTO_INCREMENT为2,同时插入了(2,2),而如果我没有重启,插入同样数据我们得到的应该是(4,2)。 上面的测试反映了mysqld重启后,InnoDB存储引擎的表自增id可能出现重复利用的情况。

这在很多场景下可能导致问题,包括但不限于:主备切换、历史数据迁移等场景。在bug#199下面一大堆的回复里,可以看到大量的同行抱怨。如,假设t1有个历史表t1_history用来存t1表的历史数据,那么mysqld重启前,ti_history表中可能已经有了(2,2)这条数据,而重启后我们又插入了(2,2),当新插入的(2,2)迁移到历史表时,会违反主键约束。这类问题是否在数据迁移中会出现呢,我们也需要注意一下。比如我们使用mysqldump导出数据,然后导入到另外一个环境。mysqldump导出数据里面是指定了自增长值的方式,而非空。

建表时可以指定AUTO_INCREMENT值,不指定时默认为1,这个值表示当前自增列的起始值大小,如果新插入的数据没有指定自增列的值,那么自增列的值即为这个起始值。对于InnoDB表,这个值没有持久到文件中。而是存在内存中(dict_table_struct.autoinc)。那么又问,既然这个值没有持久下来,为什么我们每次插入新的值后,show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的(ha_innobase::update_create_info)。

知道了AUTO_INCREMENT是实时存储内存中的。那么,mysqld重启后,从哪里得到AUTO_INCREMENT呢?内存值肯定是丢失了,实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT,而这种方法就是造成自增id重复的原因。

另外,当MySQL开启一个事务后,有类INSERT操作,自增值就会增加;但是当事务回滚后,自增至并不会减小。也就是说自增值会有空洞。

我们再来看一个自增长问题,如下测试:

我们可以看到第一种带NULL值的写法,自增长值会多增加一个值;第二种insert .. select,自增长也会多增加一个值。这个会带来什么问题呢?你会发现从库自增长值却是正常的(当复制格式为ROW时),这个时候其实也就是主从数据不一致了,但影响不大,除非出现记录ID大于自增长ID,那样插入数据重复会报错。

究其原因,和insert语句的定位也有关系,目前有这几类insert语句。

1、simple insert,如insert into t(name) values(‘test’)

2、bulk insert,如load data | insert into … select …. from ….

3、mixed insert,如insert into t(id,name) values(1,’a’),(null,’b’),(5,’c’);

这个和参数innodb_autoinc_lock_mode有很大的关系,默认参数值为1。innodb_autoinc_lock_mode这个参数控制着在向有auto_increment列的表插入数据时,相关锁的行为,有三个取值:

  • 0:这个表示tradition(传统)

它提供了一个向后兼容的能力,在这一模式下,所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁。注意呀,这里说的是语句级而不是事务级的,一个事务可能包涵有一个或多个语句。

它能保证值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入。

  • 1:这个表示consecutive(连续)

这一模式下对simple insert做了优化,由于simple insert一次性插入值的个数可以立马得到确定,所以MySQL可以一次生成几个连续的值,用于这个insert语句;总的来说这个对复制也是安全的(它保证了基于语句复制的安全)。由于现在MySQL已经推荐把二进制的格式设置成ROW格式,所以没有复制安全问题了。

这一模式也是MySQL的默认模式,这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁。

  • 2:这个表示interleaved(交错)

由于这个模式下已经没有了auto_inc锁,所以这个模式下的性能是最好的;但是它也有一个问题,就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

<延伸>

MySQL “replace into” 的坑

MySQL自增列主从不一致的测试

MySQL自增列导致主键重复问题分析

MySQL · 8.0.0新特性 · 持久化自增列值


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)or分享 (0)
关于作者:

您必须 登录 才能发表评论!