目录
1、自增id是主键
1.1 简述
如果数据库有主键,切主键设置为自增。以mysql为例,一般自增主键对应的存储类型为整形int,即对应的占用 4 个字节,作为有符号位的话范围就是 [-2^31,2^31-1],也就是 [-2147483648,2147483647];无符号位的话最大值就是 2^32-1,也就是 4294967295。
下面得表为例:
CREATE TABLE IF NOT EXISTS `t`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`url` VARCHAR(64) NOT NULL,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入一个 id 为最大值 2147483647 的值,如下图所示:
如果此时继续下面的插入语句:
INSERT INTO t (url) VALUES('www.baidu.com');
结果就会造成主键冲突:
这种情况怎么处理?
1.2 处理方式
出现上述问题后如何处理?
这里可以分2中场景来分析。
1.2.1 空洞数据场景的处理
该表中历史数据已经不存在了,比如说现在数据库中保存的id是由于我们测试或其他原因造成本应该写入的id,结果写入了空洞数据,如下:
如果此时我们想讲错误数据删除后,继续从1、2、3、4的顺序写入,但是你测试发现数据库会从1004开始写起。
innodb引擎:Innodb表把自增主键的最大ID记录到内存中,重启数据库后,都会导致最大自增ID重置。
(据说8.0 会保存自增的最大ID到表里,待验证)
第一种情况:
删除的空洞数据后面没有数据,重启后,AUTO_INCREMENT会查询最大ID并进行重置,重置后和重启前的值不同。
第二种情况:
删除的空洞数据后面有数据,重启后,AUTO_INCREMENT会查询最大ID并进行重置,重置后和重启前的值相同。
MyISam引擎:MyISAM表会把自增主键的最大ID记录到数据文件里,重启MySQL自增主键的最大ID也不会丢失。
1.2.2 如果确实是由于写到最大了,怎么处理?
这种情况,上面说的处理方式肯定不行,而且一般重启数据库的成本也太高了,不适合实际生产采用。
分析上面原因,是由于int只有4 个字节,造成长度不够,所以无法写入,那我们修改数据库的存储类型由int 改为bigint ,也就是 8 个字节。这样能存储的最大数据量就是 2^64-1,这个数量级应该满足业务需要。
这个时候继续添加:
业务就可以正常写入了。
2、自增id不是主键
自增id但不是主键,mysql是不存在这种情况的。但有网友介绍的如下场景:
2.1 盗用网友的解释如下:
另一种情况就是建表时没设置主键。这种情况,InnoDB 会自动帮你创建一个不可见的、长度为 6 字节的 row_id,默认是无符号的,所以最大长度是 2^48-1。
实际上 InnoDB 维护了一个全局的 dictsys.row_id,所以未定义主键的表都共享该 row_id,并不是单表独享。每次插入一条数据,都把全局 row_id 当成主键 id,然后全局 row_id 加 1。
这种情况的数据库自增 ID 用完会发生什么呢?
1、创建一张无显示设置主键的表 t:
CREATE TABLE IF NOT EXISTS `t`(
`age` int(4) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、通过 ps -ef|grep mysql
命令获取 mysql 的进程 ID,然后执行命令,通过 gdb 先把 row_id 修改为 1。PS:没有 gdb 的,百度安装下
sudo gdb -p 16111 -ex 'p dict_sys->row_id=1' -batch
出现下图就是没错的:
3、插入三条数据:
insert into t(age) values(1);
insert into t(age) values(2);
insert into t(age) values(3);
此时的数据库数据:
4、gdb 把 row_id 修改为最大值:281474976710656
sudo gdb -p 16111 -ex 'p dict_sys->row_id=281474976710656' -batch
5、再插入三条数据:
insert into t(age) values(4);
insert into t(age) values(5);
insert into t(age) values(6);
此事的数据库数据:
分析:
- 刚开始设置 row_id 为 1,插入三条数据 1、2、3 的 row_id 也理应是 1、2、3;这是没问题的。
- 接着设置 row_id 为最大值,紧跟着插入三条数据。这时的数据库结果是:4、5、6、3;你会发现 1、2 被覆盖了。
- row_id 达到后最大值后插入的值 4、5、6 的 row_id 分别是 0、1、2;由于 row_id 为 1、2 的值已存在,所以后者的值 5、6 会覆盖掉 row_id 为 1、2 的值。
结论:row_id 达到最大值后会从 0 重新开始算;前面插入的数据就会被后插入的数据覆盖,且不会报错。
3、分库分表和自增id的处理
当业务增量,迫于并发或者数据量的问题,我们需要进行分库分表时,原来还作为主键的自增id该如何处理?
原来单库单表的构造在分表分库的模式下肯定不行。不可能每张表都从1开始,主键冲突该如何解决?
大家很容易就想到,让个表增加一个固定步长生成id,似乎可以解决这个问题。比如现在要分为5张表,那所有表都按照5这个步长开始生成,如下:
表名 | 开始值 | 步长 | 生产id的值列表 |
tab_1 | 1 | 5 | 1,6,11,16,21 ... |
tab_2 | 2 | 5 | 2,7,12,17,22... |
tab_3 | 3 | 5 | 3,8,13,18,23... |
tab_4 | 4 | 5 | 4,9,14,19,24,... |
tab_5 | 5 | 5 | 5,10,15,20,25... |
一般设置特殊的自增值和自增步长是为了:当数据出现错乱时,我们需要将备份的数据和线上的数据进行比对时使用。)
我们可以通过:
SHOW VARIABLES LIKE 'auto_inc%';
查看当前数据库的自增长设置。
auto_increment_increment 这是自增长的步长。
auto_increment_offset 这是自增长开始的值。
假如有一张表,我们要给它增加一个自增列(id列)。
SET @@auto_increment_increment=5; -- 将自增长步长设置为5
SET @@auto_increment_offset=1; -- 将自增长开始值设置为1
我们先看下设置的自增步长和初始值是否改变了:
再次执行 SHOW VARIABLES LIKE 'auto_inc%';
通过查询结果,我们可以看到,自增步长和初始值已经被改变,那么接下来我们看一下id值会不会按照我们的期望进行设置。写入数据后如下:
但是上述方式会造成数据库中所有表的自增都是这个固定步长。所以如果数据库分表时几张不同业务表,此方法就不行。还有同一业务的分表大家的起始值都一样,不满足我们上面方案的需求,这个就需要我们手动插入各表第1条数据,让分表的起始di不同,之后按照固定步长计算后就不会出现重复。
总体看,虽然在一定程度上解决了自增id的问题,但是如果我们的分表个数后续增加或者减少时,这个方案就不行了。
那该怎么办? 下面分布式ID就可以解决。
4、分布式ID
4.1 由来
但随着数据日渐增长,主从同步也扛不住了,就需要对数据库进行分库分表,但分库分表后需要有一个唯一ID来标识一条数据,数据库的自增ID显然不能满足需求;特别一点的如订单、优惠券也都需要有唯一ID
做标识。此时一个能够生成全局唯一ID
的系统是非常必要的。那么这个全局唯一ID
就叫分布式ID
。
4.2、优秀的分布式ID需要具备的特性
- 全局唯一:必须保证ID是全局性唯一的,基本要求
- 高性能:高可用低延时,ID生成响应要块,否则反倒会成为业务瓶颈
- 高可用:100%的可用性是骗人的,但是也要无限接近于100%的可用性
- 好接入:要秉着拿来即用的设计原则,在系统设计和实现上要尽可能的简单
- 趋势递增:最好趋势递增,这个要求就得看具体业务场景了,一般不严格要求
4.3常见的分布式ID
- UUID
- 数据库自增ID
- 数据库多主模式
- 号段模式
- Redis
- 雪花算法(SnowFlake)
- 滴滴出品(TinyID)
- 百度 (Uidgenerator)
- 美团(Leaf)
具体这里就不展开介绍,大家可以根据自己的业务选择。
详细介绍大家可以去看:https://zhuanlan.zhihu.com/p/107939861
参考:https://zhuanlan.zhihu.com/p/390264098
https://blog.csdn.net/a646705816/article/details/101161076
https://zhuanlan.zhihu.com/p/107939861