【建议收藏】MySQL中的自增id超出上限的问题

码农编程进阶笔记

共 2822字,需浏览 6分钟

 ·

2022-05-28 04:24

在mysql中有多种自增id,除了我们日常开发中经常使用的自增主键外,还有一些其他的自增id,主要是mysql内部为了辅助其正常运行而使用的。


这些自增id,都是定义了初始值,然后不停的累加步长。对于每一种自增id,在mysql中都会定义其数据类型,以及这个数据类型所占用的字节长度,也就是说每个自增id,都是有上限的,只不过上限的大小不尽相同而已,既然自增id有上限,那么就有可能被用完,那问题来了,如果自增id用完了,会发生什么呢?


在mysql中,对于不同的自增id值达到上限后,对应的处理方式是不同的。下面我们就对mysql中,几个比较重要的自增id进行分析一下。


为了方便下文描述,我们建立如下的表结构:

#来源公众号:【码农编程进阶笔记】CREATE TABLE `increment_id_test` (
 
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  PRIMARY 
KEY (`id`USING BTREE
ENGINE=InnoDB;

一、自定义自增主键

自定义自增主键,是工作中最经常使用到的一种自增id,对于自增主键的一些细节问题可以参考你真的懂自增主键。


在表 increment_id_test 中,字段id是自增的,而且被定义成主键。id的数据类型为int,可表示的最大数值是2^32-1,也就是4294967295。为了能够让id字段快速达到这个上限,这里有两种方法:来源公众号:【码农编程进阶笔记】

1.直接插入一个id=4294967295 的数据行。

insert into increment_id_test values(4294967295);


2.设置autoincrement的值。

alter table increment_id_test auto_increment=4294967295;


当然也可以在建表的时候,就指定auto_increment的值为 4294967295。


两种方式的差异在于,设置auto_increment的方式,表示下次插入的数据行的id是 4294967295。


这里我们使用第一种方式做实验,相比第二种方式,可以减少一次数据插入,就可以让id达到上限。实验步骤如下:


1.插入一条id为 4294967295 的数据行


#来源公众号:【码农编程进阶笔记】

insert into increment_id_test values(4294967295);


2.在插入一条id为null的数据行,当自增主键为null时,插入的主键id值auto_increment


insert values(null);


返回信息如下:

Duplicate entry '4294967295' for key 'increment_id_test.PRIMARY'


这里产生唯一键冲突的错误,说明执行第二条插入语句时,表increment_id_test的auto_increment的值和表中已有的主键id值 4294967295相同,也即表明:当auto_incement达到上限后,再次申请下一个id时,得到的值保持不变。


当把主键id的数据类型设置为int时,我们需要考虑表未来的数据量大小,毕竟 4294967295 并不是一个很大的值,对于一个每秒插入100行的业务,不到500天,就可以达到主键id上限。


其实在建表时,无论主键id是否设置为可自增,当id值大小超过这个上限后,都是会报错的。主键自增的情况下,报错信息为:唯一键冲突:


Duplicate entry '4294967295' for key 'increment_id_test.PRIMARY'


对于普通字段的情况,报错信息为:插入数据超出数据类型范围:

Data truncation: Out of range value for column 'id' at row 1


所以在建表时,通常我们都会将主键id设置为8字节的bigint unsigned,bigint的数据长度为 2^64次方,一个很大很大的数值。理论上,在并发够大,时间够长的情况下,还是有可能达到其上限的,但是这个也仅仅是理论上,因为到目前为止,还没有一个mysql实例超过这个上限。


其实对于bigint 类型id超出上限的问题,我们可以换个角度想一下,如果表的自增主键达到了这个上限,那么表中的数据量也是一个天文数据了,那么在这个表的业务操作,也基本上操作不动了,绝大部分业务,都会在表数据量达到这个上限前,进行表的切分。因此说,达到这个上限,只可能在理论上,实际的业务场景,基本上不存在的。


二、row_id

我们都知道,使用InnoDB存储引擎时,如果数据表没有设置主键,那么Innodb会给该表设置一个不可见,长度为6字节的默认主键 row_id。Innodb维护了一个全局的dict_sys.row_id值,这个值,被所有无主键的数据表共同使用,每个无主键的数据表,插入一行数据,都会是当前的dict_sys.row_id的值增加1.来源公众号:【码农编程进阶笔记】


其实row_id的值在存储时,数据长度为8字节,只不过Innodb只使用后6个字节。那么row_id的值,写到数据表中时就有一下两个特点


1.row_id写入表中的值范围,是从0-2^48-1。


2.当row_id的值为2^48时,再进行数据插入,那么row_id的后6个字节的值,就全部为0了。


也就是说,当row_id的值到了2^48次方-1后,再次插入数据,下一个值就是0,然后开始循环。不过和自定义主键不同的是,row_id标识的主键,没有唯一性约束,当插入数据的row_id值,在表中已经存在的话,那么写入的数据会"悄无声息"覆盖已存在的数据。来源公众号:【码农编程进阶笔记】


因为row_id是Innodb维护的,Innodb没有暴露出修改该值的接口和命令,要想验证上述情况,只能通过调试的方式修改变量的值,因为涉及到gdb调试的相关内容,这里不再深究。


总结

从上面 Innodb对row_id重复情况下的处理机制来看,在设计表时,最好还是使用自定义主键,而不要使用Innodb的默认主键,至少在自定义主键的场景下,当自增id达到上限时,插入数据,系统会提示报错信息,而不是覆盖数据,因为数据覆盖意味着数据丢失,影响的是数据可靠性,而插入失败产生的报错,影响是可用性。在数据业务中,可靠性通常是优先于可用性的。

往期精选文章

【收藏并分享】详解mysql的for update

MySQL的B+树如何存储主键和数据?

为什么MySQL不建议使用NULL作为列默认值?

100G内存下,MySQL查询200G大表会OOM么?

MySQL常见面试题:什么是主从延时?如何降低主从延时?

浏览 38
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报