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

举报
lxw1844912514 发表于 2022/05/25 00:03:59 2022/05/25
【摘要】 在mysql中有多种自增id,除了我们日常开发中经常使用的自增主键外,还有一些其他的自增id,主要是mysql内部为了辅助其正常运行而使用的。 这些自增id,都是定义了初始值,然后不停的累加步长。对于每一种自增id,在mysql中都会定义其数据类型,以及这个数据类型所占用的字节长度,也就是说每个自增id,都是有上限的,只不过...

c140cf88d0538d96f75ba56032ab0d1f.png

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

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

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

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


   
  1. #来源公众号:【码农编程进阶笔记】CREATE TABLE `increment_id_test` (
  2.  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  3.   PRIMARY KEY (`id`) USING BTREE
  4. ) 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 的数据行


   
  1. #来源公众号:【码农编程进阶笔记】
  2. 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常见面试题:什么是主从延时?如何降低主从延时?

文章来源: blog.csdn.net,作者:lxw1844912514,版权归原作者所有,如需转载,请联系作者。

原文链接:blog.csdn.net/lxw1844912514/article/details/124938385

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。