从 01 开始
2023-06-28
目录

分库分表

# 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{} */;
1

至此，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
1

‍ 我们打开 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
1

接下来我们造几个数：

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;
1

‍

# 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;
1

不出意外，是可以的。

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

/*+ mycat:showErGroup{}*/
1

‍ 查询结果：

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} */;
1
上次更新: 2025/5/17 12:26:09
双主双从复制方案
MyCat安全设置

← MyCat安全设置→

最近更新
01
语雀文档一键下载至本地教程
07-04
02
要成功，就不要低估环境对你的影响
07-03
03
血泪教训：电子设备要定期开机
07-02
更多文章>
Theme by Vdoing | Copyright © 2022-2025 | 粤 ICP 备 2022067627 号 -1 | 粤公网安备 44011302003646 号 | 点击查看十年之约