分库分表

# 80.分库分表

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：　　

系统被切分成了，用户，订单交易，支付几个模块。

‍

# 如何分库

一个问题：在两台主机上的两个数据库中的表，能否关联查询？

答案：不可以关联查询。

分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。举例：

--客户表 rows:20万
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
);

-- 订单表 rows:600万
    CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)
);


-- 订单详细表 rows:600万
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);


-- 订单状态字典表 rows:20
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

以上四个表如何分库？客户表分在一个数据库，另外三张都需要关联查询，分在另外一个数据库

‍

# 如何分表

1、选择要拆分的表

MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。

例如：例子中的 orders、orders_detail 都已经达到 600 万行数据，需要进行分表优化。

‍

2、分表字段

以 orders 表为例，可以根据不同自字段进行分表：

编号	分表字段	效果
1	id（主键、或创建时间）	查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。
2	customer_id（客户 id）	根据客户 id 去分，两个节点访问平均，一个客户的所有订单都在同一个节点

‍

# 环境准备

为了方便，我们不需要启动这么多MySQL服务器，这里只保留2个即可，也就是192.168.56.101/103

为此，我们可以修改下集群的配置vim /usr/local/mycat/conf/clusters/prototype.cluster.json，修改第11行和第17行，只保留一个Master和slave：

{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetryCount":3,
		"minSwitchTimeInterval":300,
		"showLog":false,
		"slaveThreshold":0.0
	},
	"masters":[
		"rwSepw"
	],
	"maxCon":2000,
	"name":"prototype",
	"readBalanceType":"BALANCE_ALL",
	"replicas":[
		"rwSepr"
	],
	"switchType":"SWITCH"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

‍

同理，数据源的配置，我们也去掉（可以通过修改文件名的方式）：

cd /usr/local/mycat/conf/datasources/
mv rwSepr2.datasource.json rwSepr2.datasource.json_backup
mv rwSepw2.datasource.json rwSepw2.datasource.json_backup

1
2
3

‍

然后重启MyCat：

/usr/local/mycat/bin
./mycat restart

1
2

‍

查看数据源，可以看到只有一个Master：

/*+ mycat:showClusters{} */;

‍

至此，104和105两台MySQL可以关闭了。

‍

# MyCat实现分库分表

Mycat2 一大优势就是可以在终端直接创建数据源、集群、库表，并在创建时指定分库、分表。与 1.6 版本比大大简化了分库分表的操作

# 1、添加数据源

/*+ mycat:createDataSource{
    "name":"dw0",
    "url":"jdbc:mysql://192.168.56.101:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;

/*+ mycat:createDataSource{
    "name":"dr0",
    "url":"jdbc:mysql://192.168.56.101:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;

/*+ mycat:createDataSource{
    "name":"dw1",
    "url":"jdbc:mysql://192.168.56.103:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;

/*+ mycat:createDataSource{
    "name":"dr1",
    "url":"jdbc:mysql://192.168.56.103:3306",
    "user":"root",
    "password":"Mycat0520@"
} */;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

‍

通过注释命名添加数据源后，在对应目录会生成相关配置文件

ll /usr/local/mycat/conf/datasources
-rw-r--r--  1 root root 518 6月  26 20:24 dr0.datasource.json
-rw-r--r--  1 root root 518 6月  26 20:24 dr1.datasource.json
-rw-r--r--  1 root root 518 6月  26 20:24 dw0.datasource.json
-rw-r--r--  1 root root 518 6月  26 20:24 dw1.datasource.json

1
2
3
4
5

‍

# 2、添加集群配置

把新添加的数据源配置成集群

/*!mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]}*/;
/*!mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]}*/;

1
2

‍

可以查看集群配置信息，c0主要是192.168.56.101，c1主要是192.168.56.103

ll /usr/local/mycat/conf/clusters
总用量 12
-rw-r--r-- 1 root root 312 6月  26 20:43 c0.cluster.json
-rw-r--r-- 1 root root 312 6月  26 20:43 c1.cluster.json

1
2
3
4

其实这4个数据源应该是在不同的服务器上的，这里是为了简化才只使用了2个数据库

‍

# 3、创建全局表

在本文一开的案例中，有个字典表：dict_order_type，这个表是每个数据库都要用到的，在MyCat中，这叫全局表（也叫广播表）。

我们来创建一个全局表，在MyCat中执行：

--添加数据库db1
CREATE DATABASE db1;

--在建表语句中加上关键字 BROADCAST（广播，即为全局表）
CREATE TABLE db1.`travelrecord` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `user_id` varchar(100) DEFAULT NULL,
    `traveldate` date DEFAULT NULL,
    `fee` decimal(10,0) DEFAULT NULL,
    `days` int DEFAULT NULL,
    `blob` longblob,
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

‍

进入相关目录查看 schema 配置，可以看到自动生成的全局表配置信息

cat /usr/local/mycat/conf/schemas/db1.schema.json

‍

我们打开101和103的数据库，也能看到这个数据库和表。

‍

# 4、创建分片表（分库分表）

在 Mycat 里运行建表语句进行数据分片：

CREATE TABLE db1.orders(
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id),
    KEY `id` (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
dbpartition BY mod_hash(customer_id) tbpartition BY mod_hash(customer_id)
dbpartitions 2 tbpartitions 1;

1
2
3
4
5
6
7
8
9
10

关键是建表语句后面的几句，讲了数据库分片规则，表分片规则，以及各分多少片。mod_hash，就是一个hash算法，可以均匀的划分。

‍

同样可以查看生成的配置信息，进入相关目录查看 schema 配置

cat /usr/local/mycat/conf/schemas/db1.schema.json

‍

接下来我们造几个数：

INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

1
2
3
4
5
6
7
8

‍

我们连接MySQL，可以看到有多了数据库，并且数据库里只存储了部分数据：

另一个数据库同理，只存储了部分数据

‍

但我们如果通过MyCat查询数据，是可以看到有全部数据的：

SELECT * FROM orders;

‍

# 5、创建 ER 表

有时候，我们需要和分片表进行关联查询，这种表就叫ER表。

例如订单表只记录了订单基本信息，订单详情表则记录了具体订单的信息，那么订单详情表如何创建？举例：

CREATE TABLE orders_detail(
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8
dbpartition BY mod_hash(order_id) tbpartition BY mod_hash(order_id)
tbpartitions 1 dbpartitions 2;

1
2
3
4
5
6
7
8

‍

然后我们造数：

INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

1
2
3
4
5
6

‍

同理，某个数据库只存储了部分数据：

‍

然后我们试试能不能关联查询：

SELECT * FROM orders o INNER JOIN orders_detail od ON od.order_id=o.id;

不出意外，是可以的。

‍

我们在建表的时候，好像没有指定外键关系，为什么也能查询呢？这是因为 Mycat2 无需指定 ER 表，能自动识别，如果想要查看配置的表是否具有 ER 关系，使用

/*+ mycat:showErGroup{}*/

‍

查询结果：

groupId	schemaName	tableName
0.00	db1	orders
0.00	db1	orders_detail

group_id 表示相同的组，该组中的表具有相同的存储分布，查询的时候MyCat会自动合并结果，示意图：

‍

# 常用分片规则

# 1、分片算法简介

Mycat2 支持常用的（自动）HASH 型分片算法，也兼容 1.6 的内置的（cobar）分片算法.

HASH 型分片算法默认要求集群名字以 c 为前缀，数字为后缀，c0 就是分片表第一个节点，c1 就是第二个节点。该命名规则允许用户手动改变

‍

# 2、Mycat2 与 1.x 版本区别

Mycat2 Hash 型分片算法多数基于 MOD_HASH（MOD 对应 Java 的 % 运算），实际上是取余运算。

Mycat2 Hash 型分片算法对于值的处理，总是把分片值转换到列属性的数据类型再运算。

而 1.x 系列的分片算法统一转换到字符串类型再运算，且只能根据一个分片字段计算出存储节点下标。

Mycat2 Hash 型分片算法适用于等价条件查询。

而 1.x 系列由于含有用户经验的路由规则，1.x 系列的分片规则总是先转换成字符串再运算。

‍

# 3、分片规则与适用性

分片算法	描述	分库	分表	数值类型
MOD_HASH	取模哈希	是	是	数值，字符串
UNI_HASH	取模哈希	是	是	数值，字符串
RIGHT_SHIFT	右移哈希	是	是	数值
RANGE_HASH	两字段其一取模	是	是	数值，字符串
YYYYMM	按年月哈希	是	是	DATE，DATETIME
YYYYDD	按年日哈希	是	是	DATE，DATETIME
YYYYWEEK	按年周哈希	是	是	DATE，DATETIME
MM	按月哈希	否	是	DATE，DATETIME
DD	按日期哈希	否	是	DATE，DATETIME
MMDD	按月日哈希	是	是	DATE，DATETIME
WEEK	按周哈希	否	是	DATE，DATETIME
STR_HASH	字符串哈希	是	是	字符串

‍

# 4、常用分片规则简介

（1）MOD_HASH

【数据分片】HASH 型分片算法-MOD_HASH

如果分片值是字符串则先对字符串进行 hash 转换为数值类型

分库键和分表键是同键：

分表下标 = 分片值 % （分库数量 * 分表数量）
分库下标 = 分表下标 / 分表数量

分库键和分表键是不同键：

分表下标= 分片值 % 分表数量
分库下标= 分片值 % 分库数量

‍

（2）RIGHT_SHIFT

【数据分片】HASH 型分片算法 - RIGHT_SHIFT

RIGHT_SHIFT（字段名,位移数）

仅支持数值类型，分片值右移二进制位数，然后按分片数量取余

‍

（3）YYYYMM

【数据分片】HASH 型分片算法-YYYYMM

仅用于分库

(YYYY*12+MM) % 分库数.MM 是 1-12

‍

（4）MMDD

仅用于分表，仅 DATE/DATETIME，一年之中第几天%分表数，tbpartitions 不超过 366

‍

# 全局序列

Mycat2 在 1.x 版本上简化全局序列，自动默认使用雪花算法生成全局序列号，如不需要 Mycat 默认的全局序列，可以通过配置关闭自动全局序列

‍

# 1、建表语句方式关闭全局序列

如果不需要使用 mycat 的自增序列，而使用 mysql 本身的自增主键的功能，需要在配置中更改对应的建表 sql，不设置 AUTO_INCREMENT 关键字，这样MyCat就不认为这个表有自增主键的功能，就不会使用 mycat 的全局序列号。

这样,对应的插入 sql 在 mysql处理，由 mysql 的自增主键功能补全自增值.

雪花算法：引入了时间戳和 ID 保持自增的分布式 ID 生成算法：

‍

建表 sql 可以自动在原型库对应的逻辑表的物理表获取，如果逻辑表的建表 SQL 与物理表的建表 SQL 不对应，则需要在配置文件中配置建表 SQL。例如：

带 AUTO_INCREMENT 关键字使用默认全局序列

CREATE TABLE db1.`travelrecord` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `user_id` varchar(100) DEFAULT NULL,
    `traveldate` date DEFAULT NULL,
    `fee` decimal(10,0) DEFAULT NULL,
    `days` int DEFAULT NULL,
    `blob` longblob,
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;

1
2
3
4
5
6
7
8
9
10

‍

去掉关键字AUTO_INCREMENT，不使用默认全局序列

CREATE TABLE db1.`travelrecord` (
    `id` bigint NOT NULL,
    `user_id` varchar(100) DEFAULT NULL,
    `traveldate` date DEFAULT NULL,
    `fee` decimal(10,0) DEFAULT NULL,
    `days` int DEFAULT NULL,
    `blob` longblob,
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;

1
2
3
4
5
6
7
8
9
10

‍

# 2、设置 Mycat 数据库方式获取全局序列

1、在prototype服务器的db1库导入dbseq.sql文件

Mycat2已经为用户提供了相关sql脚本，需要在对应数据库下运行脚本，不能通过Mycat客户端执行

脚本所在目录：mycat/conf/dbseq.sql

脚本内容较长，这里不列出。其实就是创建了一个表，然后创建了一些规则

‍

2、添加全局序列配置文件

进入/mycat/conf/sequences目录，添加配置文件，{数据库名字}_{表名字}.sequence.json，配置内容：

{
    "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
    "name":"db1_travelrecord",
    "targetName": "prototype",
    "schemaName":"db1"//指定物理库名
}

1
2
3
4
5
6

可选参数targetName 更改序列号服务器

"targetName": "prototype" 是执行自增序列的节点,也是dbseq.sql导入的节点

dbseq.sql导入的当前库的库名与逻辑表的逻辑库名一致。导入后检查库下有没有mycat_sequence表。

其中increment是序列号自增的步伐，为1的时候严格按1递增，当1000的时候,mycat会每次批量递增1000取序列号，此时在多个mycat访问此序列号表的情况下，不能严格自增NAME列中的值是对应的库名_表名该值需要用户设置，即插入一条逻辑表相关的记录，用于记录序列号

‍

3、切换为数据库方式全局序列号

使用注释前要导入dbseq.sql，以及设置mycat_sequence表内的逻辑表记录。通过注释设置为数据库方式全局序列号

/*+ mycat:setSequence{
    "name":"db1_travelrecord",
    "clazz":"io.mycat.plug.sequence.SequenceMySQLGenerator",
    "name":"db1_travelrecord",
    "targetName": "prototype",
    "schemaName":"db2"
} */;

1
2
3
4
5
6
7

‍

4、如果想切换为雪花算法方式全局序列号：

/*+ mycat:setSequence{"name":"db1_travelrecord","time":true} */;

在GitHub上编辑此页

上次更新: 2023/6/28 11:11:51

← 双主双从复制方案 MyCat安全设置→