MyCat 垂直分片和水平拆分的實(shí)現(xiàn)

更新時(shí)間：2025年03月06日 11:26:12 作者：快樂的小三菊

本文主要介紹了MyCat 垂直分片和水平拆分的實(shí)現(xiàn),垂直分片將不同類型的表拆分到不同的數(shù)據(jù)庫服務(wù)器上,水平拆分則將大表拆分到多個(gè)服務(wù)器上,下面就來具體介紹一下,感興趣的可以了解一下

一、垂直分片

1.1 場景

在業(yè)務(wù)系統(tǒng)中，涉及以下表結(jié)構(gòu)，但是由于用戶與訂單每天都會(huì)產(chǎn)生大量的數(shù)據(jù)，單臺(tái)服務(wù)器的數(shù)據(jù)存儲(chǔ)及處理能力是有限的，可以對(duì)數(shù)據(jù)庫表進(jìn)行拆分，原有的數(shù)據(jù)庫表如下。

現(xiàn)在考慮將其進(jìn)行垂直分庫操作，將商品相關(guān)的表拆分到一個(gè)數(shù)據(jù)庫服務(wù)器，訂單表拆分的一個(gè)數(shù)據(jù)庫服務(wù)器，用戶及省市區(qū)表拆分到一個(gè)服務(wù)器。最終結(jié)構(gòu)如下：

1.2 準(zhǔn)備

準(zhǔn)備三臺(tái)服務(wù)器，并分別在上面創(chuàng)建數(shù)據(jù)庫 shopping，如下：

1.3 配置

首先配置 schema.xml，內(nèi)容如下：

<?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 ，內(nèi)容如下：

	<user name="root" defaultAccount="true">
		<property name="password">1234</property>
		<property name="schemas">SHOPPING</property>
		
		<!-- 表級(jí) DML 權(quán)限設(shè)置 -->
		<!-- 		
		<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 腳本到服務(wù)器的根目錄下，地址在這，提取碼為 42qh，如下：

2、重新啟動(dòng) mycat 后，在 mycat 的命令行中，通過 source 指令導(dǎo)入表結(jié)構(gòu)，以及對(duì)應(yīng)的數(shù)據(jù)，查看數(shù)據(jù)分布情況。

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

將表結(jié)構(gòu)及對(duì)應(yīng)的測試數(shù)據(jù)導(dǎo)入之后，可以檢查一下各個(gè)數(shù)據(jù)庫服務(wù)器中的表結(jié)構(gòu)分布情況。檢查是否和我們準(zhǔn)備工作中規(guī)劃的服務(wù)器一致。

3、查詢用戶的收件人及收件人地址信息(包含省、市、區(qū))。

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 的命令行中，當(dāng)我們執(zhí)行以下多表聯(lián)查的 sql 語句時(shí)，可以正常查詢出數(shù)據(jù)。

4、查詢每一筆訂單及訂單的收件地址信息(包含省、市、區(qū))。實(shí)現(xiàn)該需求對(duì)應(yīng)的 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 ;

但是現(xiàn)在存在一個(gè)問題，訂單相關(guān)的表結(jié)構(gòu)是在 192.168.229.162 數(shù)據(jù)庫服務(wù)器中，而省市區(qū)的數(shù)據(jù)庫表是在 192.168.229.163 數(shù)據(jù)庫服務(wù)器中。那么在 MyCat 中執(zhí)行是否可以成功呢？

經(jīng)過測試，我們看到，sql 語句執(zhí)行報(bào)錯(cuò)。原因就是因?yàn)?nbsp;MyCat 在執(zhí)行該 sql 語句時(shí)，需要往具體的數(shù)據(jù)庫服務(wù)器中路由，而當(dāng)前沒有一個(gè)數(shù)據(jù)庫服務(wù)器完全包含了訂單以及省市區(qū)的表結(jié)構(gòu)，造成 sql 語句失敗，報(bào)錯(cuò)。

對(duì)于上述的這種現(xiàn)象，我們?nèi)绾蝸斫鉀Q呢？下面我們介紹的全局表，就可以輕松解決這個(gè)問題。

1.5 全局表

對(duì)于省、市、區(qū)/縣表 tb_areas_provinces、tb_areas_city、tb_areas_region，是屬于數(shù)據(jù)字典表，在多個(gè)業(yè)務(wù)模塊中都可能會(huì)遇到，可以將其設(shè)置為全局表，利于業(yè)務(wù)操作。

修改 schema.xml 中的邏輯表的配置，修改 tb_areas_provinces、tb_areas_city、 tb_areas_region 三個(gè)邏輯表，增加 type 屬性，配置為 global，就代表該表是全局表，就會(huì)在所涉及到的 dataNode 中創(chuàng)建給表。對(duì)于當(dāng)前配置來說，也就意味著所有的節(jié)點(diǎn)中都有該表了。

<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、配置完畢后，重新啟動(dòng) MyCat。

2、刪除原來每一個(gè)數(shù)據(jù)庫服務(wù)器中的所有表結(jié)構(gòu)

3、通過 source 指令，重新導(dǎo)入表及數(shù)據(jù)

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

4、檢查每一個(gè)數(shù)據(jù)庫服務(wù)器中的表及數(shù)據(jù)分布，看到三個(gè)節(jié)點(diǎn)中都有這三張全局表

5、然后再次執(zhí)行上面的多表聯(lián)查的 sql 語句，是可以正常執(zhí)行成功的。

6、當(dāng)在 MyCat 中更新全局表的時(shí)候，我們可以看到，所有分片節(jié)點(diǎn)中的數(shù)據(jù)都發(fā)生了變化，每個(gè)節(jié)點(diǎn)的全局表數(shù)據(jù)時(shí)刻保持一致。

二、水平拆分

2.1 場景

在業(yè)務(wù)系統(tǒng)中, 有一張日志表，業(yè)務(wù)系統(tǒng)每天都會(huì)產(chǎn)生大量的日志數(shù)據(jù)，單臺(tái)服務(wù)器的數(shù)據(jù)存儲(chǔ)及處理能力是有限的，需要對(duì)數(shù)據(jù)庫表進(jìn)行拆分。即水平拆分表。

2.2 準(zhǔn)備

準(zhǔn)備三臺(tái)服務(wù)器，并分別在上面創(chuàng)建數(shù)據(jù)庫 itcast，如下：

2.3 配置

首先配置 schema.xml，內(nèi)容如下：

	<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 個(gè)節(jié)點(diǎn)中，分別是 dn4、dn5、dn6 ，而具體的數(shù)據(jù)分別存儲(chǔ)在 dhost1、 dhost2、dhost3 的 itcast 數(shù)據(jù)庫中。

然后配置 server.xml ，內(nèi)容如下，配置 root 用戶既可以訪問 SHOPPING 邏輯庫，又可以訪問 ITCAST 邏輯庫。

	<user name="root" defaultAccount="true">
		<property name="password">1234</property>
		<property name="schemas">SHOPPING,ITCAST</property>
		
		<!-- 表級(jí) DML 權(quán)限設(shè)置 -->
		<!-- 		
		<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 測試

配置完畢后，重新啟動(dòng) MyCat，然后在 mycat 的命令行中，執(zhí)行如下 sql 創(chuàng)建表、并插入數(shù)據(jù)，查看數(shù)據(jù)分布情況。

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 '操作時(shí)間',
	param_and_value varchar(500) DEFAULT NULL COMMENT '請求參數(shù)名及參數(shù)值',
	operate_class varchar(200) DEFAULT NULL COMMENT '操作類',
	operate_method varchar(200) DEFAULT NULL COMMENT '操作方法',
	cost_time bigint(20) DEFAULT NULL COMMENT '執(zhí)行方法耗時(shí), 單位 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);

可以看到，數(shù)據(jù)被分別存儲(chǔ)到了不同的表里面，因?yàn)槲覀儾扇〉姆制?guī)則為 rule="mod-long"，即取模分片，后續(xù)會(huì)詳細(xì)介紹。