面试官:数据库自增 ID 用完了会怎么样?
看到这个问题,我想起当初玩魔兽世界的时候,25H 难度的脑残吼的血量已经超过了 21 亿,所以那时候副本的 BOSS 都设计成了转阶段、回血的模式,因为魔兽的血量是 int 型,不能超过 2^32 大小。
估计暴雪的设计师都没想到几个资料片下来血量都超过 int 上限了,以至于大家猜想才会有后来的属性压缩。
这些都是题外话,只是告诉你数据量大了是有可能达到上限的而已,回到 Mysql 自增 ID 上限的问题,可以分为两个方面来说。
1.有主键
如果设置了主键,并且一般会把主键设置成自增。
我们知道,Mysql 里 int 类型是 4 个字节,如果有符号位的话就是[-2^31,2^31-1],无符号位的话最大值就是 2^32-1,也就是 4294967295。
创建一张表试试:
然后执行插入
这样表里就有一条达到有符号位的最大值上限的数据。
如果再次执行插入语句:
就会看到错误提示:1062 - Duplicate entry '2147483647' for key 'PRIMARY', Time: 0.000000s
。
也就是说,如果设置了主键并且自增的话,达到自增主键上限就会报错重复的主键 key。
解决方案,mysql 主键改为 bigint,也就是 8 个字节。
设计的时候要考虑清楚值的上限是多少,如果业务频繁插入的话,21 亿的数字其实还是有可能达到的。
2.没有主键
如果没有设置主键的话,InnoDB 则会自动帮你创建一个 6 个字节的 rowid,由于 rowid 是无符号的,所以最大长度是 2^48-1。
同样创建一张表作为测试:
通过ps -ef|grep mysql
拿到 mysql 的进程 ID,然后执行命令,通过 gdb 先把 row_id 修改为 1
然后插入几条数据:
再次修改 row_id 为 2^48,也就是 281474976710656
再次插入数据
然后查询数据会发现 3 条数据是 4,5,6,3。
因为我们先设置 rowid=1 开始,所以 1,2,3 的 rowid 也是 1,2,3。
修改 rowid 为上限值之后,rowid 会从 0 重新开始计算,所以 4,5,6 的 row_id 就是 0,1,2。
由于 1,2 数据已经存在,数据则是会被覆盖。
总结
自增 ID 达到上限用完了之后,分为两种情况:
如果设置了主键,那么将会报错主键冲突。
如果没有设置主键,数据库则会帮我们自动生成一个全局的 row_id,新数据会覆盖老数据
解决方案:
表尽可能都要设置主键,主键尽量使用 bigint 类型,21 亿的上限还是有可能达到的,比如魔兽,虽然说 row_id 上限高达 281 万亿,但是覆盖数据显然是不可接受的。
版权声明: 本文为 InfoQ 作者【艾小仙】的原创文章。
原文链接:【http://xie.infoq.cn/article/7d3db45fd5da2727609d59b0c】。文章转载请联系作者。
评论