MyCat 垂直分片和水平拆分的實(shí)現(xiàn)
一、垂直分片
1.1 場(chǎng)景
在業(yè)務(wù)系統(tǒng)中,涉及以下表結(jié)構(gòu),但是由于用戶(hù)與訂單每天都會(huì)產(chǎn)生大量的數(shù)據(jù),單臺(tái)服務(wù)器的數(shù)據(jù)存儲(chǔ)及處理能力是有限的,可以對(duì)數(shù)據(jù)庫(kù)表進(jìn)行拆分,原有的數(shù)據(jù)庫(kù)表如下。
現(xiàn)在考慮將其進(jìn)行垂直分庫(kù)操作,將商品相關(guān)的表拆分到一個(gè)數(shù)據(jù)庫(kù)服務(wù)器,訂單表拆分的一個(gè)數(shù)據(jù)庫(kù)服務(wù)器,用戶(hù)及省市區(qū)表拆分到一個(gè)服務(wù)器。最終結(jié)構(gòu)如下:
1.2 準(zhǔn)備
準(zhǔn)備三臺(tái)服務(wù)器,并分別在上面創(chuàng)建數(shù)據(jù)庫(kù) 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&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 ,內(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 測(cè)試
1、上傳測(cè)試 sql 腳本到服務(wù)器的根目錄下,地址在這,提取碼為 42qh,如下:
2、重新啟動(dòng) mycat 后,在 mycat 的命令行中,通過(guò) 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)的測(cè)試數(shù)據(jù)導(dǎo)入之后,可以檢查一下各個(gè)數(shù)據(jù)庫(kù)服務(wù)器中的表結(jié)構(gòu)分布情況。 檢查是否和我們準(zhǔn)備工作中規(guī)劃的服務(wù)器一致。
3、查詢(xún)用戶(hù)的收件人及收件人地址信息(包含省、市、區(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 語(yǔ)句時(shí),可以正常查詢(xún)出數(shù)據(jù)。
4、查詢(xún)每一筆訂單及訂單的收件地址信息(包含省、市、區(qū))。實(shí)現(xiàn)該需求對(duì)應(yīng)的 sql 語(yǔ)句如下:
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è)問(wèn)題,訂單相關(guān)的表結(jié)構(gòu)是在 192.168.229.162 數(shù)據(jù)庫(kù)服務(wù)器中,而省市區(qū)的數(shù)據(jù)庫(kù)表是在 192.168.229.163 數(shù)據(jù)庫(kù)服務(wù)器中。那么在 MyCat 中執(zhí)行是否可以成功呢?
經(jīng)過(guò)測(cè)試,我們看到,sql 語(yǔ)句執(zhí)行報(bào)錯(cuò)。原因就是因?yàn)?nbsp;MyCat 在執(zhí)行該 sql 語(yǔ)句時(shí),需要往具體的數(shù)據(jù)庫(kù)服務(wù)器中路由,而當(dāng)前沒(méi)有一個(gè)數(shù)據(jù)庫(kù)服務(wù)器完全包含了訂單以及省市區(qū)的表結(jié)構(gòu),造成 sql 語(yǔ)句失敗,報(bào)錯(cuò)。
對(duì)于上述的這種現(xiàn)象,我們?nèi)绾蝸?lái)解決呢? 下面我們介紹的全局表,就可以輕松解決這個(gè)問(wèn)題。
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)前配置來(lái)說(shuō),也就意味著所有的節(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、刪除原來(lái)每一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中的所有表結(jié)構(gòu)
3、通過(guò) source 指令,重新導(dǎo)入表及數(shù)據(jù)
source /shopping-table.sql source /shopping-insert.sql
4、檢查每一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中的表及數(shù)據(jù)分布,看到三個(gè)節(jié)點(diǎn)中都有這三張全局表
5、然后再次執(zhí)行上面的多表聯(lián)查的 sql 語(yǔ)句,是可以正常執(zhí)行成功的。
6、當(dāng)在 MyCat 中更新全局表的時(shí)候,我們可以看到,所有分片節(jié)點(diǎn)中的數(shù)據(jù)都發(fā)生了變化,每個(gè)節(jié)點(diǎn)的全局表數(shù)據(jù)時(shí)刻保持一致。
二、水平拆分
2.1 場(chǎng)景
在業(yè)務(wù)系統(tǒng)中, 有一張日志表,業(yè)務(wù)系統(tǒng)每天都會(huì)產(chǎn)生大量的日志數(shù)據(jù),單臺(tái)服務(wù)器的數(shù)據(jù)存 儲(chǔ)及處理能力是有限的,需要對(duì)數(shù)據(jù)庫(kù)表進(jìn)行拆分。即水平拆分表。
2.2 準(zhǔn)備
準(zhǔn)備三臺(tái)服務(wù)器,并分別在上面創(chuàng)建數(shù)據(jù)庫(kù) 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ù)庫(kù)中。
然后配置 server.xml ,內(nèi)容如下,配置 root 用戶(hù)既可以訪問(wèn) SHOPPING 邏輯庫(kù),又可以訪問(wèn) ITCAST 邏輯庫(kù)。
<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 測(cè)試
配置完畢后,重新啟動(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 '返回值類(lèi)型', operate_user varchar(20) DEFAULT NULL COMMENT '操作用戶(hù)', operate_time varchar(20) DEFAULT NULL COMMENT '操作時(shí)間', param_and_value varchar(500) DEFAULT NULL COMMENT '請(qǐng)求參數(shù)名及參數(shù)值', operate_class varchar(200) DEFAULT NULL COMMENT '操作類(lèi)', operate_method varchar(200) DEFAULT NULL COMMENT '操作方法', cost_time bigint(20) DEFAULT NULL COMMENT '執(zhí)行方法耗時(shí), 單位 ms', source int(1) DEFAULT NULL COMMENT '來(lái)源 : 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ì)介紹。
到此這篇關(guān)于MyCat 垂直分片和水平拆分的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MyCat 垂直分片和水平拆分內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- mysql mycat 中間件安裝與使用
- mycat在windows環(huán)境下的安裝和啟動(dòng)
- 數(shù)據(jù)庫(kù)中間件MyCat的介紹
- 簡(jiǎn)單了解mysql mycat 中間件
- SpringBoot整合MyCat實(shí)現(xiàn)讀寫(xiě)分離的方法
- MyCat環(huán)境搭建詳細(xì)教程
- MyCAT上新增一個(gè)庫(kù)及MyCAT報(bào)錯(cuò)1184的問(wèn)題及解決
- 高效數(shù)據(jù)流轉(zhuǎn):Mycat分庫(kù)分表與GreatSQL實(shí)時(shí)同步
- 使用Mycat-eye管理Mycat數(shù)據(jù)庫(kù)服務(wù)的操作
- Mycat搭建分庫(kù)分表方式
相關(guān)文章
mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制實(shí)例詳解
這篇文章主要介紹了mysql視圖之創(chuàng)建視圖(CREATE VIEW)和使用限制,結(jié)合實(shí)例形式詳細(xì)分析了mysql視圖創(chuàng)建于使用相關(guān)原理與操作注意事項(xiàng),需要的朋友可以參考下2019-12-12如何修改MYSQL5.7.17數(shù)據(jù)庫(kù)存儲(chǔ)文件的路徑
在搭建華為云服務(wù)器的時(shí)候遇到點(diǎn)問(wèn)題,查看了網(wǎng)上好多的帖子都沒(méi)能解決,不知道有沒(méi)有跟我遇到一樣問(wèn)題的老鐵,我就把我的解決辦法分享給大家,希望能夠幫助各位老鐵2023-05-05mysql如何將數(shù)據(jù)庫(kù)中的所有表結(jié)構(gòu)和數(shù)據(jù)導(dǎo)入到另一個(gè)庫(kù)
介紹了如何使用mysqldump命令備份和導(dǎo)入數(shù)據(jù)庫(kù),以及創(chuàng)建目標(biāo)數(shù)據(jù)庫(kù)的步驟,首先使用mysqldump備份源數(shù)據(jù)庫(kù),然后在目標(biāo)數(shù)據(jù)庫(kù)中創(chuàng)建數(shù)據(jù)庫(kù),并將備份文件導(dǎo)入到目標(biāo)數(shù)據(jù)庫(kù),確保數(shù)據(jù)結(jié)構(gòu)和內(nèi)容完整復(fù)制,提到了DataGrip、Navicat在導(dǎo)入導(dǎo)出過(guò)程中可能出現(xiàn)的問(wèn)題2024-10-10MYSQL優(yōu)化之?dāng)?shù)據(jù)表碎片整理詳解
在日常的數(shù)據(jù)庫(kù)維護(hù)中,在給客戶(hù)制定維護(hù)方案時(shí),都會(huì)有降低高水位此類(lèi)的建議 建議每年年終定期做數(shù)據(jù)整理,下面這篇文章主要給大家介紹了關(guān)于MYSQL優(yōu)化之?dāng)?shù)據(jù)表碎片整理的相關(guān)資料,需要的朋友可以參考下2022-04-04MySQL查詢(xún)本周、上周、本月、上個(gè)月份數(shù)據(jù)的sql代碼
MySQL查詢(xún)的方式很多,下面為您介紹的MySQL查詢(xún)實(shí)現(xiàn)的是查詢(xún)本周、上周、本月、上個(gè)月份的數(shù)據(jù),如果您對(duì)MySQL查詢(xún)方面感興趣的話(huà),不妨一看2012-11-11MySQL OOM 系列三 擺脫MySQL被Kill的厄運(yùn)
這篇文章主要介紹了MySQL OOM 系列三 擺脫MySQL被Kill的厄運(yùn) ,需要的朋友可以參考下2016-07-07教你使用VS?Code的MySQL擴(kuò)展管理數(shù)據(jù)庫(kù)的方法
這篇文章主要介紹了使用VS?Code的MySQL擴(kuò)展管理數(shù)據(jù)庫(kù),在本文告訴你如何用VS?Code的擴(kuò)展程序管理MySQL數(shù)據(jù)庫(kù),包括連接到MySQL、新建數(shù)據(jù)庫(kù)和表、修改字段定義、簡(jiǎn)單的查詢(xún)方法以及導(dǎo)入導(dǎo)出,需要的朋友可以參考下2022-01-01使用mysqldump實(shí)現(xiàn)mysql備份
mysqldump客戶(hù)端可用來(lái)轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)或搜集數(shù)據(jù)庫(kù)進(jìn)行備份或?qū)?shù)據(jù)轉(zhuǎn)移到另一個(gè)SQL服務(wù)器(不一定是一個(gè)MySQL服務(wù)器)。今天我們就來(lái)詳細(xì)探討下mysqldump的使用方法2016-11-11