MyCat 垂直分片和水平拆分的實現(xiàn)
一、垂直分片
1.1 場景
在業(yè)務系統(tǒng)中，涉及以下表結構，但是由于用戶與訂單每天都會產(chǎn)生大量的數(shù)據(jù)，單臺服務器的數(shù)據(jù)存儲及處理能力是有限的，可以對數(shù)據(jù)庫表進行拆分，原有的數(shù)據(jù)庫表如下。
現(xiàn)在考慮將其進行垂直分庫操作，將商品相關的表拆分到一個數(shù)據(jù)庫服務器，訂單表拆分的一個數(shù)據(jù)庫服務器，用戶及省市區(qū)表拆分到一個服務器。最終結構如下：
1.2 準備
準備三臺服務器，并分別在上面創(chuàng)建數(shù)據(jù)庫 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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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&serverTimezone=Asia/Shanghai&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 指令導入表結構，以及對應的數(shù)據(jù)，查看數(shù)據(jù)分布情況。
source /shopping-table.sql source /shopping-insert.sql
將表結構及對應的測試數(shù)據(jù)導入之后，可以檢查一下各個數(shù)據(jù)庫服務器中的表結構分布情況。 檢查是否和我們準備工作中規(guī)劃的服務器一致。
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 的命令行中，當我們執(zhí)行以下多表聯(lián)查的 sql 語句時，可以正常查詢出數(shù)據(jù)。
4、查詢每一筆訂單及訂單的收件地址信息(包含省、市、區(qū))。實現(xiàn)該需求對應的 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)在存在一個問題，訂單相關的表結構是在 192.168.229.162 數(shù)據(jù)庫服務器中，而省市區(qū)的數(shù)據(jù)庫表是在 192.168.229.163 數(shù)據(jù)庫服務器中。那么在 MyCat 中執(zhí)行是否可以成功呢？
經(jīng)過測試，我們看到，sql 語句執(zhí)行報錯。原因就是因為 MyCat 在執(zhí)行該 sql 語句時，需要往具體的數(shù)據(jù)庫服務器中路由，而當前沒有一個數(shù)據(jù)庫服務器完全包含了訂單以及省市區(qū)的表結構，造成 sql 語句失敗，報錯。
對于上述的這種現(xiàn)象，我們如何來解決呢？ 下面我們介紹的全局表，就可以輕松解決這個問題。
1.5 全局表
對于省、市、區(qū)/縣表 tb_areas_provinces、tb_areas_city、tb_areas_region，是屬于數(shù)據(jù)字典表，在多個業(yè)務模塊中都可能會遇到，可以將其設置為全局表，利于業(yè)務操作。
修改 schema.xml 中的邏輯表的配置，修改 tb_areas_provinces、tb_areas_city、 tb_areas_region 三個邏輯表，增加 type 屬性，配置為 global，就代表該表是全局表，就會在所涉及到的 dataNode 中創(chuàng)建給表。對于當前配置來說，也就意味著所有的節(jié)點中都有該表了。
<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、刪除原來每一個數(shù)據(jù)庫服務器中的所有表結構
3、通過 source 指令，重新導入表及數(shù)據(jù)
source /shopping-table.sql source /shopping-insert.sql
4、檢查每一個數(shù)據(jù)庫服務器中的表及數(shù)據(jù)分布，看到三個節(jié)點中都有這三張全局表
5、然后再次執(zhí)行上面的多表聯(lián)查的 sql 語句，是可以正常執(zhí)行成功的。
6、當在 MyCat 中更新全局表的時候，我們可以看到，所有分片節(jié)點中的數(shù)據(jù)都發(fā)生了變化，每個節(jié)點的全局表數(shù)據(jù)時刻保持一致。
二、水平拆分
2.1 場景
在業(yè)務系統(tǒng)中, 有一張日志表，業(yè)務系統(tǒng)每天都會產(chǎn)生大量的日志數(shù)據(jù)，單臺服務器的數(shù)據(jù)存 儲及處理能力是有限的，需要對數(shù)據(jù)庫表進行拆分。即水平拆分表。
2.2 準備
準備三臺服務器，并分別在上面創(chuàng)建數(shù)據(jù)庫 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 個節(jié)點中，分別是 dn4、dn5、dn6 ，而具體的數(shù)據(jù)分別存儲在 dhost1、 dhost2、dhost3 的 itcast 數(shù)據(jù)庫中。
然后配置 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 的命令行中，執(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 '操作時間',
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í)行方法耗時, 單位 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ù)被分別存儲到了不同的表里面，因為我們采取的分片規(guī)則為 rule="mod-long"，即取模分片，后續(xù)會詳細介紹。
到此這篇關于MyCat 垂直分片和水平拆分的實現(xiàn)的文章就介紹到這了,更多相關MyCat 垂直分片和水平拆分內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
mysql視圖之創(chuàng)建視圖（CREATE VIEW）和使用限制實例詳解
這篇文章主要介紹了mysql視圖之創(chuàng)建視圖（CREATE VIEW）和使用限制,結合實例形式詳細分析了mysql視圖創(chuàng)建于使用相關原理與操作注意事項,需要的朋友可以參考下2019-12-12
如何修改MYSQL5.7.17數(shù)據(jù)庫存儲文件的路徑
在搭建華為云服務器的時候遇到點問題，查看了網(wǎng)上好多的帖子都沒能解決，不知道有沒有跟我遇到一樣問題的老鐵，我就把我的解決辦法分享給大家，希望能夠幫助各位老鐵2023-05-05
mysql如何將數(shù)據(jù)庫中的所有表結構和數(shù)據(jù)導入到另一個庫
介紹了如何使用mysqldump命令備份和導入數(shù)據(jù)庫,以及創(chuàng)建目標數(shù)據(jù)庫的步驟,首先使用mysqldump備份源數(shù)據(jù)庫,然后在目標數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)庫,并將備份文件導入到目標數(shù)據(jù)庫,確保數(shù)據(jù)結構和內容完整復制,提到了DataGrip、Navicat在導入導出過程中可能出現(xiàn)的問題2024-10-10
這篇文章主要為大家詳細介紹了windows下mysql5.7安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-12-12
MYSQL優(yōu)化之數(shù)據(jù)表碎片整理詳解
在日常的數(shù)據(jù)庫維護中,在給客戶制定維護方案時,都會有降低高水位此類的建議 建議每年年終定期做數(shù)據(jù)整理,下面這篇文章主要給大家介紹了關于MYSQL優(yōu)化之數(shù)據(jù)表碎片整理的相關資料,需要的朋友可以參考下2022-04-04
MySQL查詢本周、上周、本月、上個月份數(shù)據(jù)的sql代碼
MySQL查詢的方式很多，下面為您介紹的MySQL查詢實現(xiàn)的是查詢本周、上周、本月、上個月份的數(shù)據(jù)，如果您對MySQL查詢方面感興趣的話，不妨一看2012-11-11
這篇文章主要介紹了MySQL OOM 系列三 擺脫MySQL被Kill的厄運 ,需要的朋友可以參考下2016-07-07
從MySQL5.5開始，MySQL以插件的形式支持半同步復制。如何理解半同步呢？今天我們就來詳細講解下，希望大家能夠喜歡。2017-03-03
教你使用VS?Code的MySQL擴展管理數(shù)據(jù)庫的方法
這篇文章主要介紹了使用VS?Code的MySQL擴展管理數(shù)據(jù)庫,在本文告訴你如何用VS?Code的擴展程序管理MySQL數(shù)據(jù)庫，包括連接到MySQL、新建數(shù)據(jù)庫和表、修改字段定義、簡單的查詢方法以及導入導出，需要的朋友可以參考下2022-01-01
mysqldump客戶端可用來轉儲數(shù)據(jù)庫或搜集數(shù)據(jù)庫進行備份或將數(shù)據(jù)轉移到另一個SQL服務器(不一定是一個MySQL服務器)。今天我們就來詳細探討下mysqldump的使用方法2016-11-11