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区间，用完再取，使用乐观锁机制控制并发。