MySQL多表统一主键策略

需求背景

有个三级目录(例如国家-省份-城市)的数据要以树的形式展示,由于每一级都是独立的实体,需要单独一张表存储,不同级别的id会重复,于是前端提意见要求整棵树不同级别的id都不能重复,不然他们的组件会出问题.于是只好研究一下多表统一主键的方案.

Snowflake雪花算法

虽然很自然就想到了分布式id中的雪花算法,不过我们对分布式场景和id严格自增没有要求,这个方案麻烦,没有必要.

UUID

从唯一性想到uuid也是很自然的. MySQL5.7不支持建表时使用函数,可以用触发器替代.

CREATE TABLE FOO (
  id CHAR(36) PRIMARY KEY
);



DELIMITER ;;
CREATE TRIGGER `foo_before_insert` 
BEFORE INSERT ON `foo` FOR EACH ROW 
BEGIN
  IF new.id IS NULL THEN
    SET new.id = uuid();
  END IF;
END;;
DELIMITER ;

不过我们很少让MySQL自己生成uuid,数据库的资源还是很宝贵的,这应该让应用自己生成. 此外UUID当主键的利弊也讨论很充分了,占用空间以及插入慢,适用于数据少的情况.

序列表

思路是另外专门用一张表存放自增的id,分表新建数据的时候来这里拿号.

## 创建序列表
DROP TABLE IF EXISTS `sequence`;
CREATE TABLE `sequence` (
  `tablename` varchar(64) NOT NULL,
  `nextid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`tablename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


## 创建主键策略函数
DROP FUNCTION IF EXISTS `auto_seq`;
CREATE DEFINER = `root`@`localhost` FUNCTION `auto_seq`(tb_purpose VARCHAR(64))
 RETURNS int(11)
BEGIN
DECLARE reid int; 
set reid = (select `nextid` from `sequence` where `tablename`=tb_purpose limit 1);
update `sequence` set `nextid`=reid + 1;
return reid;
end;

## 在分表中创建触发器，此触发器为插入前触发，调用主键策略函数返回的结果赋予id
DROP TRIGGER `auto_seq`;
 
CREATE TRIGGER `auto_seq` BEFORE INSERT ON `item`
FOR EACH ROW begin
set new.id = auto_seq('item');
end;

缺点是多张表同时写入的时候存在竞争.

间隔插入

上面的做法是插入数据时多张表排同一条队,其实没必要,只要提前约定每张表拿哪个id就可以保证不重复.比如有N张表,那么N个id为一个循环,第一个位置留给表A,第二个位置留给表B…. 这可以通过设置MySQL生成id的步进和id初始值即可.


## 设置步长为3
SET @@auto_increment_increment=3;

## 错开分表的初始id
CREATE TABLE `A` (ID INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT=1;
CREATE TABLE `B` (ID INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT=2;
CREATE TABLE `C` (ID INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT=3;

不过这个步长控制的粒度是库级别,会影响别的表.

分段插入

更进一步,直接划分多个大的区段就可以避免设置步进带来的问题.

## 错开分表的初始id
CREATE TABLE `A` (ID INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT=1;
CREATE TABLE `B` (ID INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT=100000;
CREATE TABLE `C` (ID INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT=10000000;

表A的id范围是1~99999,表B是100000~9999999,表C是10000000~MAX,只要能保证数据量不是非常大以至于耗尽约定的范围就行,这个方法最简单,我们的各级目录的数量又是很有限的,所以就用了这个方法. 另外用这个方案时要记得写清楚注释,不然别人手抖改了AUTO_INCREMENT就出问题了.