从 01 开始 从 01 开始
首页
  • 📚 计算机基础

    • 计算机简史
    • 数字电路
    • 计算机组成原理
    • 操作系统
    • Linux
    • 计算机网络
    • 数据库
    • 编程工具
    • 装机
  • 🎨 前端

    • Node
  • JavaSE
  • Java 高级
  • JavaEE

    • 构建、依赖管理
    • Ant
    • Maven
    • 日志框架
    • Junit
    • JDBC
    • XML-JSON
  • JavaWeb

    • 服务器软件
    • 环境管理和配置管理-科普篇
    • Servlet
  • Spring

    • Spring基础
  • 主流框架

    • Redis
    • Mybatis
    • Lucene
    • Elasticsearch
    • RabbitMQ
    • MyCat
    • Lombok
  • SpringMVC

    • SpringMVC 基础
  • SpringBoot

    • SpringBoot 基础
  • Windows 使用技巧
  • 手机相关技巧
  • 最全面的输入法教程
  • 最全面的浏览器教程
  • Office
  • 图片类工具
  • 效率类工具
  • 最全面的 RSS 教程
  • 码字工具
  • 各大平台
  • 校招
  • 五险一金
  • 职场规划
  • 关于离职
  • 杂谈
  • 自媒体
  • 📖 读书

    • 读书工具
    • 走进科学
  • 🌍 英语

    • 从零开始学英语
    • 英语兔的相关视频
    • Larry 想做技术大佬的相关视频
  • 🏛️ 政治

    • 反腐
    • GFW
    • 404 内容
    • 审查与自我审查
    • 互联网
    • 战争
    • 读书笔记
  • 💰 经济

    • 关于税
    • 理财
  • 💪 健身

    • 睡眠
    • 皮肤
    • 口腔健康
    • 学会呼吸
    • 健身日志
  • 🏠 其他

    • 驾驶技能
    • 租房与买房
    • 厨艺
  • 电影

    • 电影推荐
  • 电视剧
  • 漫画

    • 漫画软件
    • 漫画推荐
  • 游戏

    • Steam
    • 三国杀
    • 求生之路
  • 小说
  • 关于本站
  • 关于博主
  • 打赏
  • 网站动态
  • 友人帐
  • 从零开始搭建博客
  • 搭建邮件服务器
  • 本站分享
  • 🌈 生活

    • 2022
    • 2023
    • 2024
    • 2025
  • 📇 文章索引

    • 文章分类
    • 文章归档

晓林

程序猿,自由职业者,博主,英语爱好者,健身达人
首页
  • 📚 计算机基础

    • 计算机简史
    • 数字电路
    • 计算机组成原理
    • 操作系统
    • Linux
    • 计算机网络
    • 数据库
    • 编程工具
    • 装机
  • 🎨 前端

    • Node
  • JavaSE
  • Java 高级
  • JavaEE

    • 构建、依赖管理
    • Ant
    • Maven
    • 日志框架
    • Junit
    • JDBC
    • XML-JSON
  • JavaWeb

    • 服务器软件
    • 环境管理和配置管理-科普篇
    • Servlet
  • Spring

    • Spring基础
  • 主流框架

    • Redis
    • Mybatis
    • Lucene
    • Elasticsearch
    • RabbitMQ
    • MyCat
    • Lombok
  • SpringMVC

    • SpringMVC 基础
  • SpringBoot

    • SpringBoot 基础
  • Windows 使用技巧
  • 手机相关技巧
  • 最全面的输入法教程
  • 最全面的浏览器教程
  • Office
  • 图片类工具
  • 效率类工具
  • 最全面的 RSS 教程
  • 码字工具
  • 各大平台
  • 校招
  • 五险一金
  • 职场规划
  • 关于离职
  • 杂谈
  • 自媒体
  • 📖 读书

    • 读书工具
    • 走进科学
  • 🌍 英语

    • 从零开始学英语
    • 英语兔的相关视频
    • Larry 想做技术大佬的相关视频
  • 🏛️ 政治

    • 反腐
    • GFW
    • 404 内容
    • 审查与自我审查
    • 互联网
    • 战争
    • 读书笔记
  • 💰 经济

    • 关于税
    • 理财
  • 💪 健身

    • 睡眠
    • 皮肤
    • 口腔健康
    • 学会呼吸
    • 健身日志
  • 🏠 其他

    • 驾驶技能
    • 租房与买房
    • 厨艺
  • 电影

    • 电影推荐
  • 电视剧
  • 漫画

    • 漫画软件
    • 漫画推荐
  • 游戏

    • Steam
    • 三国杀
    • 求生之路
  • 小说
  • 关于本站
  • 关于博主
  • 打赏
  • 网站动态
  • 友人帐
  • 从零开始搭建博客
  • 搭建邮件服务器
  • 本站分享
  • 🌈 生活

    • 2022
    • 2023
    • 2024
    • 2025
  • 📇 文章索引

    • 文章分类
    • 文章归档
  • JavaSE

  • JavaSenior

  • JavaEE

  • JavaWeb

  • Spring

  • 主流框架

    • Redis

    • Mybatis

    • Lucene

    • Elasticsearch

    • MQ

    • MyCat

      • MyCat2-尚硅谷
      • MyCat 是什么,为什么要用
      • 搭建环境
      • MyCat 概念
      • MyCat 的配置文件
      • 一主一从复制
      • 一主一从读写分离
      • 双主双从复制方案
      • 分库分表
        • 如何分库
        • 如何分表
        • 环境准备
        • MyCat 实现分库分表
        • 常用分片规则
        • 全局序列
      • MyCat安全设置
      • Mycat2-UI
    • Lombok

  • SpringMVC

  • SpringBoot

  • Java
  • 主流框架
  • MyCat
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 号 | 点击查看十年之约
  • 跟随系统
  • 浅色模式
  • 深色模式
  • 阅读模式