it编程 > 数据库 > Mysql

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

10人参与 • 2025-03-06 • Mysql

一、垂直分片

1.1 场景

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

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

1.2 准备

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

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，如下：

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

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

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

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 语句时，可以正常查询出数据。 

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 中执行是否可以成功呢？ 

经过测试，我们看到，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"/>

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

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

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

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

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

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

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

二、水平拆分

2.1 场景

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

2.2 准备

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

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)values('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 垂直分片和水平拆分内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)
打赏 微信扫一扫 微信扫一扫

您想发表意见！！点此发布评论

推荐阅读

MySQL读写分离的项目时间实践

MySQL读写分离的项目时间实践

03-06
MySQL中如何开启二进制日志(Binlog)

MySQL中如何开启二进制日志(Binlog)

03-06
MySQL修改密码的四种方式详解

MySQL修改密码的四种方式详解

03-06
mysqlbinlog查看日志[ERROR]unknown&nbsp;variable&nbsp;‘default-character-set=utf8’

mysqlbinlog查看日志[ERROR]unknown variable ‘default-character-set=utf8’

03-08
两个windows服务器使用canal实现mysql实时同步

两个windows服务器使用canal实现mysql实时同步

03-08

MySQL对字符串使用STR_TO_DATE()函数详解

03-09

猜你喜欢

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论