当前位置：首页 > news >正文

【运维篇第三弹】《万字带图详解分库分表》从概念到Mycat中间件使用再到Mycat分片规则，详解分库分表，有使用案例

news 2025/8/31 6:25:51

分库分表

1.分库分表介绍

核心：将单台数据库服务器的数据分散（垂直拆分、水平拆分）存储在多台数据库中

2.Mycat概述

Mycat是一个开源的分库分表的中间件

3.Mycat入门

入门程序

4.Mycat配置

Schema.xml、rule.xml、server.xml

5.Mycat分片

分片规则：范围、取模、枚举、一致性hash、固定分片hash算法、字符串hash解析算法、按天分片、按自然月分片

6.Mycat管理及监控

原理，管理，监控工具

一、介绍

(1)概念

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。
CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

在这里插入图片描述

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的

(2)拆分方式

在这里插入图片描述

1.垂直拆分

在这里插入图片描述

①垂直分库：以表为依据，根据业务将不同表拆分到不同库中

特点：

每个库的表结构都不同。
每个库的数据也不一样。
所有库的并集式全量数据。

②垂直分库：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：

每个表的结构都不一样
每个表的数据也不一样，一般通过一列（主键/外键）关联
所有表的并集是全量数据

2.水平拆分

在这里插入图片描述

①水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中

特点：

每个库的表结构都一样
每个库的数据都不一样
所有库的并集是全量数据

在这里插入图片描述

①水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个库中

特点：

每个库的表结构都一样
每个库的数据都不一样
所有库的并集是全量数据

(3)实现技术

在这里插入图片描述

shardingJDBC：基于AOP原理，在应用程序中队本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
MyCat：数据库分库分表中间件，不需要调整代码即可实现分库分表，支持多种语言，性能不及前者。

应用程序访问多个数据库，既要负责处理核心业务功能，又要处理数据路由的逻辑，编写难度是很高的，耦合高、扩展难、维护成本大；

这时候就有技术来处理该问题；

二、Mycat概述与安装

(1)介绍

1.概述

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可用像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在。

优势：

性能可靠稳定
强大的技术团队
体系完善
社区活跃

2.分库分表架构

在这里插入图片描述

整体流程是：应用访问逻辑库的逻辑表，中间件依据分片规则，将操作路由到对应的分片节点，最终落地到节点主机的物理库执行，以此实现大规模数据的分布式存储与访问。

mycat不存储具体的数据，只是在逻辑上对数据进行分片处理

(2)安装

Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，以下为Mycat的Linux中的环境搭建：。

如果需要做演示，我们需要准备四台服务器或虚拟机等。

我们需要在服务器中安装如下软件：

MySQL
JDK
Mycat

按照如下安装

服务器	安装软件	说明
服务器1	JDK、Mycat	MyCat中间件服务器
服务器2	MySQL	分片服务器
服务器3	MySQL	分片服务器
服务器4	MySQL	分片服务器

Mycat的安装就是解压安装包到目的路径即可

tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local

(3)目录结构

bin：存放可执行文件，用于启动停止mycatconf：存放mycat配置文件lib：存放mycat的项目依赖包（jar）logs：存放mycat的日志文件

(4)更换新版jar包

进入lib文档

在这里插入图片描述

更换更高版本的

mysql-connector-java-5.1.35.jar#删除低版本jar包
rm  -rf mysql-connector-java-5.1.35.jar #导入新版jar包
mysql-connector-java-8.0.22.jar#对新版本jar包赋予权限chmod 777 mysql-connector-java-8.0.22.jar

在这里插入图片描述

三、Mycat入门

知道mycat能够做什么就行

水平分表做演示

(1)需求

由于tb_order表中数据量很大，磁盘IO及容量都到达了瓶颈，下载需要对tb_order表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上，具体的结构，参考下图。
在这里插入图片描述

物理结构如下：

在这里插入图片描述

分片配置文件的对应关系如下：

在这里插入图片描述

接下来正式开始操作步骤：

(2)配置schema.xml文件

详细介绍在第四章，此处只做入门演示，完整的一个mycat使用流程

1.简化文件

schema.xml文件分为三个部分：

1.逻辑库和逻辑表

2.数据节点

3.节点主机

(1)删除注释

删除注释后，我们可以看到下面这个表的演示：

在这里插入图片描述

(2)保留关键部分

我们再次精简可以得到如下：

在这里插入图片描述

每个大块留下一个例子用来修改即可。

2.配置文件

在这里插入图片描述

1.首先配置逻辑库，库名设置为DB01，逻辑库中配置了一张逻辑表，逻辑表名为TB_ORDER；这张逻辑表分为三片，三片分别为dn1，dn2，dn3；

2.这三个数据节点关联了三个节点主机dhost1，dhost2，dhost3，每一个数据节点都是关联的目标主机的db01数据库；

3.这三个节点主机就需要我们设置dbDriver为jdbc，每一个节点关联的mysql的url，用户名，密码。

（总结下来我们只需要配置三个大模块，逻辑库和逻辑表，使得能够对于数据节点，数据节点对应目标主机）

<!-- 1. 配置逻辑库和逻辑表，名字以及数据节点配置好 --><schema name="DB01" checkSQLschema="true" sqlMaxLimit="100"><table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /></schema><!-- 2. 配置数据节点对应的主机，以及操作的数据库 --><dataNode name="dn1" dataHost="dhost1" database="db01" /><dataNode name="dn2" dataHost="dhost2" database="db01" /><dataNode name="dn3" dataHost="dhost3" database="db01" /><!-- 3. 配置主机操作的数据库管理系统类型，以及驱动；然后配置主机地址以及用户名和密码 --><dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="master" url="jdbc:mysql://192.168.44.129:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" /></dataHost>

(3)配置server.xml文件

分片配置：配置mycat的用户及用户的权限信息

在这里插入图片描述

默认允许两个用户访问，一个root一个user，我们配置好用户名及密码，设置好访问的逻辑库；

用户默认开启只读权限；

(4)测试

1.启动mycat服务端

启动mycat，占用端口8066

#在mycat文件下面输入
bin/mycat start#查看是否启动成功
tail -50 logs/wrapper.log#停止mycat
bin/mycat stop

在这里插入图片描述

2.连接mycat客户端

连接到mycat客户端

mysql -h 192.168.44.128 -P 8066 -uroot -p

在这里插入图片描述

我们会发现，当我们第一次进入mycat的时候就存在一个数据库DB01和一张表tb_order；但是我们在DataGrip（数据库连接中却看不到这张tb_order的表。

这是因为这张表是mycat在schema.xml文件中定义的逻辑表，在物理主机上是不存在的表；

3.使用mycat

在mycat客户端使用

#创建表
CREATE TABLE TB_ORDER (id BIGINT(20) NOT NULL,title VARCHAR(100) NOT NULL,PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
INSERT INTO TB_ORDER(id,title) VALUES(1000000,'goods1000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');

①创表后现象

可以看到在db01数据库中，原本是不存在表的，但是在mycat中创建表刷新后，所有的主机节点上都创建了表。

在这里插入图片描述

②插入数据后现象

我们会发现数据并不是平均分配到各个数据库中；

这是因为分配规则在定义逻辑表的时候选择的分片规则；

在这里插入图片描述

(5)分片规则rule.xml

在mycat的配置文件夹中，有一个叫rule.xml的文件,其中放置了各种分片规则。

我们可以知道，他是按照id分片，算法使用rang-long，然后往下继续翻可以找到该算法的实现：

在这里插入图片描述

算法实现：

在这里插入图片描述

该算法涉及了一个java类，映射文件关联了一个物理文件，我们可以在conf下找到

在这里插入图片描述

文件内容：

# range start-end,data node index
# K=1000,M=10000
0-500M=0
500M-1000M=1
1000M-1500M=2

说明了id从0到500万放在第一个节点，以此类推；

四、Mycat配置文件概述

(1)schema.xml

schema.xml 作为MyCat中最重要的配置文件之一，涵盖了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。

主要包含以下三组标签：

schema标签
datanode标签
datahost标签

1.schema标签

schema标签用于定义MyCat实例中的逻辑库，一个MyCat实例中，可以有多个逻辑库，可以通过schema标签来划分不同的逻辑库。

MyCat中的逻辑库的概念，等同于MySQL中的database概念，需要操作某个逻辑库下的表时，也需要切换逻辑库(use xxx)。

schema标签中的table标签定义了MyCat中逻辑库schema下的逻辑表，所有需要拆分的表都需要在table标签中定义。

(1)文件中格式：

<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100" ><table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>

(2)核心属性：

①标签

name：指定自定义的逻辑库库名
checkSQLschema：在SQL语句中操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询的记录数

②

标签

name：定义逻辑表表名，
dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
rule：分片规则名字，分片规则名字是在rule.xml中定义的
primaryKey：逻辑表对应真实表的主键
type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表配置为 global

(3)注意点

checkSQLschema执行sql语句会去除数据库名，

#当我们为（use 数据库）的时候，原本可以正确执行，但是开启自动去除后就会报错
select * from DB01.TB_ORDER

2.datanode标签

dataNode标签中定义了MyCat中的数据节点，也就是我们通常说的数据分片。

一个dataNode标签就是一个独立的数据分片。

(1)文件中格式：

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db02" />
<dataNode name="dn3" dataHost="dhost3" database="db03" />

（2）核心属性：

name：定义数据节点名称
dataHost：数据库实例主机名称，引用自dataHost标签中name属性
database：定义分片所属数据库

3.datahost标签

该标签在MyCat逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分离、心跳语句。

(1)文件中格式：

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="jdbc" ><heartbeat>select user()</heartbeat><writeHost host="master" url="jdbc:mysql://192.168.1.101:3306" user="root" password="123456"></writeHost></dataHost>

(2)核心属性：

name：唯一标识，供上层标签使用
maxCon/minCon：最大连接数/最小连接数
balance：负载均衡策略，取值0，1，2，3
writeType：写操作方式）
dbDriver：数据库驱动，支持native、jdbc

writeType： 0：写操作转发到第一个 writeHost，第一个挂了切换到第二个；1：写操作随机分发到配置的wirteHost

(2)rule.xml

rule.xml中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化。

主要包含两类标签

tableRule
Function

<tableRules> 标签：
用于定义具体的分片规则

<functions> 标签：
用于注册分片算法，每个 function 包含：

在这里插入图片描述

(3)server.xml

server.xml配置文件中包含了MyCat的系统配置信息

主要有两个重要的标签：

system
user

1.system标签

对应的系统配置及其含义。

(1)文件中格式：

<system><property name="nonePasswordLogin"></property><property name="nonePasswordLogin"></property><property name="nonePasswordLogin"></property>
</system>

2.user标签

在这里插入图片描述

五、Mycat分片

(1)垂直拆分

1.场景

在业务系统中，涉及以下表结构，但是由于用户与订单每天都会产生大量的数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分，原有的数据库表如下。

在这里插入图片描述

2.准备工作

在三台物理主机上创建为shopping的数据库来存放对应的表

3.配置mycat

(1)schema.xml文件中：

	<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100"><table name="tb_goods_base"  dataNode="dn4" primaryKey="id" /><table name="tb_goods_brand" dataNode="dn4" primaryKey="id" /><table name="tb_goods_cat"   dataNode="dn4" primaryKey="id" /><table name="tb_goods_desc"  dataNode="dn4" primaryKey="goods_id" /><table name="tb_goods_item"  dataNode="dn4" primaryKey="id" /><table name="tb_order_item"   dataNode="dn5" primaryKey="id" /><table name="tb_order_master"  dataNode="dn5" primaryKey="order_id" /><table name="tb_order_pay_log"  dataNode="dn5" primaryKey="out_trade_no" /><table name="tb_user"  dataNode="dn6" primaryKey="id" /><table name="tb_user_address" dataNode="dn6" primaryKey="id" /><table name="tb_areas_provinces"  dataNode="dn6" primaryKey="id" /><table name="tb_areas_city" dataNode="dn6" /><table name="tb_areas_region"  dataNode="dn6" primaryKey="id" /></schema><dataNode name="dn4" dataHost="dhost1" database="shopping" /><dataNode name="dn5" dataHost="dhost2" database="shopping" /><dataNode name="dn6" dataHost="dhost3" database="shopping" />

值得注意的是，之前第三章演示的例子是水平分表，但是这次是垂直分库，所以不需要分配规则rule，一张表因为所有的数据都存储在一个节点上；

(2)server.xml文件中

在这里插入图片描述

4.物理机生成对应表

导入sql脚本文件

在这里插入图片描述

在mycat中使用source指令

#建表
source /heima/YunWei/VerticalDatabase/shopping-table.sql
#插入数据
source /heima/YunWei/VerticalDatabase/shopping-insert.sql

5.全局表配置

1.当使用多表联查的时候，如果表在不同的节点，是不能够查到的，会报错；

为了解决这种情况，需要在schema.xml配置的时候，将需要跨节点联查的表设置成全局表；

2.全局表存在于每一个节点当中，当在mycat中修该全局表中数据时，其他表也会更改；

如图所示：

在这里插入图片描述

(2)水平拆分

和垂直拆分一样，设置好物理机上的数据库，然后配置schema.xml文件上的配置，以及server.xml文件。

数据分配到不同的节点，但是mycat可以从不同节点中拿到全部的数据；

1.场景

在业务中，有一张表（日志表），业务系统每天都会产生大量的日志数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分。

在这里插入图片描述

2.配置mycat

(1)schema.xml文件中：

<!-- 1. 配置逻辑库和逻辑表，名字以及数据节点配置好 --><schema name="DB01" checkSQLschema="true" sqlMaxLimit="100"><table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /></schema><!-- 2. 配置数据节点对应的主机，以及操作的数据库 --><dataNode name="dn1" dataHost="dhost1" database="db01" /><dataNode name="dn2" dataHost="dhost2" database="db01" /><dataNode name="dn3" dataHost="dhost3" database="db01" /><!-- 3. 配置主机操作的数据库管理系统类型，以及驱动；然后配置主机地址以及用户名和密码 --><dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100"><heartbeat>select user()</heartbeat><writeHost host="master" url="jdbc:mysql://192.168.44.129:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" /></dataHost>

(2)sever.xml文件中

在这里插入图片描述

3.引入分片规则概念

我们发现，水平分表分数据时，table标签中有一个rule属性；该属性就是指定分片规则。

该规则规定了数据应该放到哪一个数据节点中。

六、分片规则

分片规则都在rule.xml文件中；

当我们多个逻辑表想使用同一个分配规则的时候，会产生冲突，这时候我们完全可以新增规则：

1.复制需要规则

2.tableRule标签中修改规则名

3.function标签中按照修改对应配置

#下面介绍分片规则是为了了解各个规则的实现方式以及需要根据不同逻辑表自定义的地方#

范围，取模，枚举，固定分片hash 针对数字类型
一致性hash 解析uuid，字符串hash 解析字符串，应用指定 解析数字类型字符串
日期分片 针对日期

(1)范围分片

根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片。

# range start-end,data node index
# K=1000,M=10000
0-500M=0
500M-1000M=1
1000M-1500M=2

具体实现：

在这里插入图片描述

修改方式：

修改txt文件即可；

(2)取模分片

根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定该数据属于哪一个分片。

xxx%3 == 0:0
xxx%3 == 1:1
xxx%3 == 2:2

具体实现：

在这里插入图片描述

修改方式：

修改成正确的节点数量即可；

(3)一致性hash分片

murmur 规则是通过哈希算法实现数据均匀分片的方案，兼顾效率与分布均衡性，是 MyCat 中常用的分片策略之一；

所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置。

具体实现：

在这里插入图片描述

修改方式：

填写对应的节点数量

(4)枚举分片

通过在配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则适用于按照省份、性别、状态拆分数据等业务。

枚举值 1:0
枚举值 2:2
枚举值 3:2

具体实现：

在这里插入图片描述

修改方式：

修改txt文件即可

默认节点表示：当值不属于枚举值，默认存放节点，该属性为可选项。

不添加的时候出现错误值会报错

我们可以在txt中修改枚举值和对应节点；

（其中的这些按什么字段分，默认节点，对应文件及其路径都可以修改）

(5)应用指定分片

一个长id，会截取头部n个字符，根据这n个字符进行枚举分片

运行阶段由应用自主决定路由到那个分片，直接根据字符子串（必须是数字）计算分片号

具体实现：

在这里插入图片描述

<function name="sahrding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString" ><property name="startIndex">0</property>	  <!--1.开始索引 --><property name="size">2</property>			  <!--2.截取长度 --><property name="partitionCount">3</property>   <!--3.分片数量 --><property name="defaultPartiton">2</property>  <!--4.默认分片 -->
</function>

修改配置即可

(6)固定分片hash算法

该算法类似于十进制的求模运算，但是为二进制的操作。

例如，取id的二进制低10位与 1111111111进行位&运算。

特点：

如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。
可以均匀分配，也可以非均匀分配。
分片字段必须为数字类型

具体实现：

在这里插入图片描述

1.分片长度：2+1 = 3 个分片

2.分配范围：256，512表示2个分片是256，一个分片是512

（0-255第一个分片，256-511第二个分片，515-1023第三个分片）

所有分片范围之和最大值一定是1024；

因为取低10位的二进制最大值是1023

其实就相当于将低10位的值分成了3部分，前256放一个节点，后256放一个节点，剩下512放一个节点

(7)字符串hash解析分片

截取前两位字符，进行哈希运算，在与1023按位与；

根据最后得到的值分片

hash(字段值start:end)&(1024-1)

具体实现：

在这里插入图片描述

前面两个属性定义了数据节点以及对应空间大小；
最后一个属性决定了截取位置以及长度：比如 0 : 0 就是0-str.length，遵循左边右开；

(8)按日期分片-按天

按照天数分片

配置：

在这里插入图片描述

设置开始时间date1，结束时间date2，以及间隔p

在date1到date2期间，每隔p时间换一次节点。

比如2022-01-01 到 2022-12-31 一共有 365 天；

若10天为一个分片，就需要36.5个，但是分片只要满一天都要按一个分片算，也就是37个分片

(9)按日期分片-按自然月

按照月份分片

配置：

在这里插入图片描述

七、Mycat管理及监控

(1)mycat原理

在这里插入图片描述

当客户端发送一个sql语句的时候，mycat会对该语句进行一列的操作，比如sql解析，分片分析等，再分给各个需要操作的物理主机；

当需要返回结果的时候，对返回结果的合并操作，聚合操作，排序操作，分页操作等都是再mycat中进行的。

(2)mycat管理工具

Mycat默认开通两个端口，可以在server.xml中进行修改。

8066数据访问端口，即进行DML和DDL操作
9066数据库管理端口，即mycat服务管理控制功能，由于管理mycat的整个集群状态

mysql -h 192.168.44.128 -P 9066 -uroot -p123456

命令	含义
show @@help	查看Mycat管理工具帮助文档
show @@version	查看Mycat的版本
reload @@config	重新加载Mycat的配置文件
show @@datasource	查看Mycat的数据源信息
show @@datanode	查看Mycat现有的分片节点信息
show @@threadpool	查看Mycat现有的线程池信息
show @@sql	查看执行的SQL
show @@sql.sum	查看执行的SQL统计

show @@sql 无法监控sql语句：

   修改在conf文件夹中log4j2.xml文件<!-- 新增：专门记录 SQL 相关日志，级别设为 INFO --><AsyncLogger name="SQL" level="INFO" includeLocation="true" additivity="false"><AppenderRef ref="Console"/>  <!-- 输出到控制台 --><AppenderRef ref="RollingFile"/>  <!-- 同时记录到文件 --></AsyncLogger>