MyCat 垂直分片和水平拆分的实现

2025-04-01 11:44 数据库作者：快乐的小三菊

一、垂直分片

1.1 场景

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

MyCat 垂直分片和水平拆分的实现

现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下：

MyCat 垂直分片和水平拆分的实现

1.2 准备

准备三台服务器，并分别在上面创建数据库 shopping，如下：

MyCat 垂直分片和水平拆分的实现

1.3 配置

首先配置 schema.XML，内容如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
编程客栈		<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />

		<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
		<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
		<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />

		<table name="tb_user" dataNode="dn3" primaryKey="id" />
		<table name="tb_user_address" dataNode="dn3" primaryKey="id" />
		<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
		<table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
		<table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>

	</schema>
	
	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
	<dataNode name="dn3编程客栈" dataHost="dhost3" database="shopping" />
	
	<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.229.158:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
	</dataHost>
	
	<dataHost name="dhost2" 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.229.162:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
	</dataHost>
	
	<dataHost name="dhost3" 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.229.163:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
	</dataHost>
</mycat:schema>

然后配置 server.xml ，内容如下：

	<user name="root" defaultAccount="true">
		<property name="password">1234</property>
		<property name="schemas">SHOPPING</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">SHOPPING</property>
		<property name="readOnly">true</property>
	</user>

1.4 测试

1、上传测试 sql 脚本到服务器的根目录下，地址在这，提取码为 42qh，如下：

MyCat 垂直分片和水平拆分的实现

2、重新启动 mycat 后，在 mycat 的命令行中，通过 source 指令导入表结构，以及对应的数据，查看数据分布情况。

source /shopping-table.sql
source /shopping-insert.sql

MyCat 垂直分片和水平拆分的实现

将表结构及对应的测试数据导入之后，可以检查一下各个数据库服务器中的表结构分布情况。检查是否和我们准备工作中规划的服务器一致。

MyCat 垂直分片和水平拆分的实现

3、查询用户的收件人及收件人地址信息(包含省、市、区)。

select ua.user_id, ua.contact, p.province, c.city, r.area , ua.address from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid ;

在 MyCat 的命令行中，当我们执行以下多表联查的 sql 语句时，可以正常查询出数据。

MyCat 垂直分片和水平拆分的实现

4、查询每一笔订单及订单的收件地址信息(包含省、市、区)。实现该需求对应的 sql 语句如下：

SELECT order_id , payment ,receiver, province , city , area FROM tb_order_master o , tb_areas_provinces p , tb_areas_city c , tb_areas_region r WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid ;

但是现在存在一个问题，订单相关的表结构是在 192.168.229.162 数据库服务器中，而省市区的数据库表是在 192.168.229.163 数据库服务器中。那么在 MyCat 中执行是否可以成功呢？

MyCat 垂直分片和水平拆分的实现

经过测试，我们看到，sql 语句执行报错。原因就是因为 MyCat 在执行该 sql 语句时，需编程客栈要往具体的数据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成 sql 语句失败，报错。

对于上述的这种现象，我们如何来解决呢？下面我们介绍的全局表，就可以轻松解决这个问题。

1.5 全局表

对于省、市、区/县表 tb_areas_provinces、tb_areas_city、tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。

修改 schema.xml 中的逻辑表的配置，修改 tb_areas_provinces、tb_areas_city、 tb_areas_region 三个逻辑表，增加 type 属性，配置为 global，就代表该表是全局表，就会在所涉及到的 dataNode 中创建给表。对于当前配置来说，也就意味着所有的节点中都有该表了。

<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>

MyCat 垂直分片和水平拆分的实现

1、配置完毕后，重新启动 MyCat。

MyCat 垂直分片和水平拆分的实现

2、删除原来每一个数据库服务器中的所有表结构

3、通过 source 指令，重新导入表及数据

source /shopping-table.sql
source /shopping-insert.sql

4、检查每一个数据库编程服务器中的表及数据分布，看到三个节点中都有这三张全局表

MyCat 垂直分片和水平拆分的实现

5、然后再次执行上面的多表联查的 sql 语句，是可以正常执行成功的。

MyCat 垂直分片和水平拆分的实现

6、当在 MyCat 中更新全局表的时候，我们可以看到，所有分片节点中的数据都发生了变化，每个节点的全局表数据时刻保持一致。

二、水平拆分

2.1 场景

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

MyCat 垂直分片和水平拆分的实现

2.2 准备

准备三台服务器，并分别在上面创建数据库 itcast，如下：

MyCat 垂直分片和水平拆分的实现

2.3 配置

首先配置 schema.xml，内容如下：

	<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
		<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
	</schema>
	
	<dataNode name="dn4" dataHost="dhost1" database="itcast" />
	<dataNode name="dn5" dataHost="dhost2" database="itcast" />
	<dataNode name="dn6" dataHost="dhost3" database="itcast" />

tb_log 表最终落在 3 个节点中，分别是 dn4、dn5、dn6 ，而具体的数据分别存储在 dhost1、 dhost2、dhost3 的 itcast 数据库中。

然后配置 server.xml ，内容如下，配置 root 用户既可以访问 SHOPPING 逻辑库，又可以访问 ITCAST 逻辑库。

	<user name="root" defaultAccount="true">
		<property name="password">1234</property>
		<property name="schemas">SHOPPING,ITCAST</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

2.4 测试

配置完毕后，重新启动 MyCat，然后在 mycat 的命令行中，执行如下 sql 创建表、并插入数据，查看数据分布情况。

CREATE TABLE tb_log (
	id bigint(20) NOT NULL COMMENT 'ID',
	model_name varchar(200) DEFAULT NULL COMMENT '模块名',
	model_value varchar(200) DEFAULT NULL COMMENT '模块值',
	return_value varchar(200) DEFAULT NULL COMMENT '返回值',
	return_class varchar(200) DEFAULT NULL COMMENT '返回值类型',
	operate_user varchar(20) DEFAULT NULL COMMENT '操作用户',
	operate_time varchar(20) DEFAULT NULL COMMENT '操作时间',
	param_and_value varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值',
	operate_class varchar(200) DEFAULT NULL COMMENT '操作类',
	operate_method varchar(200) DEFAULT NULL COMMENT '操作方法',
	cost_time bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
	source int(1) DEFAULT NULL COMMENT '来源 : 1 PC , 2 android , 3 IOS',
	PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)VALUES('1','user','insert','success','Java.lang.String','10001','2022-01-06 18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)VALUES('2','user','insert','success','java.lang.String','10001','2022-01-06 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)VALUES('3','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)VAjavascriptLUES('4','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)VALUES('5','user','insert','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','insert','29',3);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class,operate_user, operate_time, param_and_value, operate_class, operate_method,cost_time，source)VALUES('6','user','find','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);

可以看到，数据被分别存储到了不同的表里面，因为我们采取的分片规则为 rule="mod-long"，即取模分片，后续会详细介绍。

MyCat 垂直分片和水平拆分的实现

到此这篇关于MyCat 垂直分片和水平拆分的实现的文章就介绍到这了,更多相关MyCat 垂直分片和水平拆分内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

继续阅读：MyCat 垂直分片 MyCat水平拆分

MyCat 垂直分片和水平拆分的实现

目录

一、垂直分片

1.1 场景

1.2 准备

1.3 配置

1.4 测试

1.5 全局表

二、水平拆分

2.1 场景

2.2 准备

2.3 配置

2.4 测试

更多精彩内容

精彩评论

最新数据库

MySQL重复数据处理的七种高效方法

Windows 上如果忘记了 MySQL 密码重置密码的两种方法

MySQL数据类型Numeric详解

mysql存储过程遇到ERROR 1060 (42S21):Duplicate column问题及解决方案

Django配置多个环境的MySQL数据库的完整指南

数据库排行榜

Hadoop Key Management Server (KMS)配置及测试

spark报错ERROR ObjectStore: Version information found in metastore differs 2.1.0 from expected schema version 1.2.0. Schema verififcation is disabled hive.metastore.schema.verification so setting version.

解决Navicat远程连接MySQL出现 10060 unknow error的方法

redis-cluster集群调优之cluster-require-full-coverage参数

Navicat连接Oracle数据库的详细步骤与注意事项

目录

一、垂直分片

1.1 场景

1.2 准备

1.3 配置

1.4 测试

1.5 全局表

二、水平拆分

2.1 场景

2.2 准备

2.3 配置

2.4 测试

更多精彩内容

精彩评论

最新数据库

MySQL重复数据处理的七种高效方法

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

MySQL数据类型Numeric详解

mysql存储过程遇到ERROR 1060 (42S21):Duplicate column问题及解决方案

Django配置多个环境的MySQL数据库的完整指南

数据库排行榜

Hadoop Key Management Server (KMS)配置及测试

spark报错ERROR ObjectStore: Version information found in metastore differs 2.1.0 from expected schema version 1.2.0. Schema verififcation is disabled hive.metastore.schema.verification so setting version.

解决Navicat远程连接MySQL出现 10060 unknow error的方法

redis-cluster集群调优之cluster-require-full-coverage参数

Navicat连接Oracle数据库的详细步骤与注意事项

Windows 上如果忘记了 MySQL 密码重置密码的两种方法