写点什么

MySQL 自增 ID 以及其他唯一 ID 方式分析

用户头像
Bruce Duan
关注
发布于: 2020 年 05 月 05 日
MySQL自增ID以及其他唯一ID方式分析

今天来动手实践下MySQL自增ID的使用和探讨下其他唯一ID的方式吧!



在MySQL数据库中,如果一个表的自增ID用完了再插入数据会出现什么问题呢?如果不清楚的话那就自己动手实践下吧呜呜呜!



首先,创建一个最简单的表,只包含一个自增id并插入一条数据。

create table ceshi01 (
CESHI_ID int unsigned auto_increment comment '主键',
NAME varchar(64) comment '姓名',
primary key (CESHI_ID)
);
insert into ceshi01(NAME) values ('小王');



通过show命令show create table ceshi01;查看表情况:

CREATE TABLE `ceshi01` (
`CESHI_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`NAME` VARCHAR(64) DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`CESHI_ID`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

可见AUTO_INCREMENT已经自动变成了2,由于我们这里定义的是int unsigned可以算下自增ID最大可以达到2的32幂次方4294967295



当然我们也可以在创建表的时候直接声明AUTO_INCREMENT的初始值:

CREATE TABLE ceshi01 (
CESHI_ID INT UNSIGNED AUTO_INCREMENT COMMENT '主键',
NAME VARCHAR(64) COMMENT '姓名',
PRIMARY KEY (CESHI_ID)
) AUTO_INCREMENT=4294967295;
INSERT INTO ceshi01(NAME) VALUES ('小李');

同样的通过show命令查看一下ceshi01表的结构

CREATE TABLE `ceshi01` (
`CESHI_ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`NAME` varchar(64) DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`CESHI_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

可以发现AUTO_INCREMENT已经变成4294967295了,当我们再想插入一条数据的时候出现了下面的异常结果:

结果是当我们再次插入时使用的自增ID还是4294967295报主键冲突的错误。



4294967295,4294967295,4294967295重要的事情说三遍,大家看这个数字已经足够大了吧,这个数字已经可以应付我们日常大部分的业务场景了,但是你的业务如果经常性的增删数据的话,用完的风险还是很大的,那就再采用bigint unsigned,这个数字就更大了。



如果创建表的时候没有声明主键怎么办???



如果是这种情况,InnoDB会自动帮你创建一个不可见的长度为6字节的row_id,而且InnoDB维护了一个全局的dictsys.row_id,未定义主键的表都会共享该row_id,每次插入一条数据,全局主键row_id会被当成主键id自动加1。



该全局row_id使用的是bigint unsigned类型,但是实际上只给row_id留了6个字节,如果row_id一直增加一直增加直到2的48幂次方-1,如果再增加1,row_id的低48位都为0,结果在插入新一行数据时,拿到的row_id就为0存在主键冲突的可能性。



所以,千万记住我们设计表的时候每个表都需要设定一个主键哦!



好啦,我们接下来看一下其他的唯一ID生成方式吧!!!



高并发分布式系统中生成全局唯一Id汇总



Twitter 方案(Snowflake 算法):41位时间戳+10位机器标识(比如IP,服务器名称等)+12位序列号(本地计数器)

Flicker 方案:MySQL自增ID + "REPLACE INTO XXX:SELECT LAST_INSERT_ID();"

UUID:缺点,无序,字符串过长,占用空间,影响检索性能。

MongoDB 方案:利用 ObjectId。缺点:不能自增。



TDDL 在分布式下的SEQUENCE原理



在数据库中创建sequence 表,用于记录,当前已被占用的id最大值。

每台客户端主机取一个id区间(比如 1000~2000)缓存在本地,并更新 sequence 表中的id最大值记录。

客户端主机之间取不同的id区间,用完再取,使用乐观锁机制控制并发。



用户头像

Bruce Duan

关注

做最好版本的自己 2020.05.01 加入

主要分享Java服务端相关的技术,欢迎关注!啥也不说了上代码开始撸。

评论

发布
暂无评论
MySQL自增ID以及其他唯一ID方式分析