真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

DBLE分庫分表實(shí)戰(zhàn)

環(huán)境：DBLE 2.19.03.0

創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供海曙網(wǎng)站建設(shè)、海曙做網(wǎng)站、海曙網(wǎng)站設(shè)計(jì)、海曙網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、海曙企業(yè)網(wǎng)站模板建站服務(wù)，十余年海曙做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

OS版本：CentOS Linux release 7.6.1810 (Core)?

IP：?192.168.20.10/24

MySQL版本：MySQL-社區(qū)版-5.7.26




添加2個(gè)賬號授權(quán)：

create user 'rw'@'%' identified by 'rw123456';

create user 'rd'@'%' identified by 'rd123456';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,REFERENCES,CREATE TEMPORARY TABLES,INDEX ON *.* TO? rw@'%' ;

GRANT SELECT ON *.* TO 'rd'@'%' ;

連接方式：

讀寫：

mysql -urw -prw123456 --port 8066 -h 192.168.20.10 testdb?

只讀：

mysql -urd -prd123456 --port 8066 -h 192.168.20.10 testdb?

ddl專用：

mysql -uop -p123456 --port 8066 -h 192.168.20.10 testdb?

管理賬號：

mysql -uman1 -p654321 --port 9066 -h 192.168.20.10?

解壓DBLE：

tar xf dble-2.19.03.tar.gz? /usr/local/

cd /usr/local

ln -s?dble-2.19.03 dble

cd conf/

vim schema.xml? ?修改后的如下：





????
????????
????????

????????
????????

????????
????????

????????
????????

????????
?????????
?????????????
????????
????
????
????
????
????
????
????
????????select?user()
????????
????????????
????????
????
????

vim?rule.xml 修改后的內(nèi)容如下：

????
????????
????????????id
????????????rangeLong2
????????
????

????
????????
????????????id
????????????hashmod3
????????
????

????
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????????????open_id
????????????hashStringmod3
????????
????

????
????????autopartition-long_t.txt
????????0
????
????
????
????????3
????????1
????
????
????
????????3
????????1
????????0:20??
????

[root@centos7 /usr/local/dble/conf ]#? vim autopartition-long_t.txt? # 增加一個(gè)路由規(guī)則文件

#?range?start-end?,data?node?index
#?K=1000,M=10000.
#?范圍：前開后閉?(開區(qū)間，閉區(qū)間]
0-1M=0
1M-2M=1
2M-3M=2

vim server.xml 內(nèi)容如下：

修改user部分為如下：?
????
????????654321
????????true
????????
????
????
????????123456
????????testdb
????
????????

????
????????rw123456
????????testdb
????
????
????????rd123456
????????testdb
????????true
????

然后， reload 下 dble ， 進(jìn)行測試


ddl專用：

????mysql -uop -p123456 --port 8066 -h 192.168.20.10 testdb?

去創(chuàng)建符合上面的要求的幾個(gè)表,并寫入數(shù)據(jù)測試：

##?測試range分區(qū)
(testdb)?>?create?table?travelrecord?(
id?bigint?not?null?primary?key,
user_id?varchar(100),
traveldate?DATE,?
fee?decimal(10,2),
days?int
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

(testdb)?>?insert?into?travelrecord?(id,user_id,traveldate,fee,days)?values(10,'wang','2014-01-05',510,3);
(testdb)?>?insert?into?travelrecord?(id,user_id,traveldate,fee,days)?values(13000,'lee','2011-01-05',26.5,3);
(testdb)?>?insert?into?travelrecord?(id,user_id,traveldate,fee,days)?values(29800,'zhang','2018-01-05',23.3,3);

(testdb)?>?select?*?from?travelrecord?;
+-------+---------+------------+--------+------+
|?id????|?user_id?|?traveldate?|?fee????|?days?|
+-------+---------+------------+--------+------+
|????10?|?wang????|?2014-01-05?|?510.00?|????3?|
|?13000?|?lee?????|?2011-01-05?|??26.50?|????3?|
|?29800?|?zhang???|?2018-01-05?|??23.30?|????3?|
+-------+---------+------------+--------+------+

##?測試全局表
(testdb)?>?create?table?company(id?int?not?null?primary?key,name?varchar(100));?

(testdb)?>?insert?into?company(id,name)?values(1,'hp');
(testdb)?>?insert?into?company(id,name)?values(2,'ibm');
(testdb)?>?insert?into?company(id,name)?values(3,'oracle');

(testdb)?>?select?*?from?company?;
+----+--------+
|?id?|?name???|
+----+--------+
|??1?|?hp?????|
|??2?|?ibm????|
|??3?|?oracle?|
+----+--------+
3?rows?in?set?(0.01?sec)

多執(zhí)行幾次，你會看到三個(gè)分片上都插入了3條數(shù)據(jù)，因?yàn)閏ompany定義為全局表。

(testdb)?>?explain?insert?into?company(id,name)?values(1,'hp');
+-----------+----------+---------------------------------------------+
|?DATA_NODE?|?TYPE?????|?SQL/REF????????????????????|
+-----------+----------+---------------------------------------------+
|?dn1???????|?BASE?SQL?|?insert?into?company(id,name)?values(1,'hp')?|
|?dn2???????|?BASE?SQL?|?insert?into?company(id,name)?values(1,'hp')?|
|?dn3???????|?BASE?SQL?|?insert?into?company(id,name)?values(1,'hp')?|
+-----------+----------+---------------------------------------------+
3?rows?in?set?(0.00?sec)

使用?explain?select?*?from?company?;???命令也可以看到隨機(jī)分發(fā)到3個(gè)節(jié)點(diǎn)的。

##?測試hashmod分區(qū)
create?table?hotnews?(id?bigint?unsigned?not?null?primary?key?,title?varchar(400)?,created_time?datetime)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

然后，?我們寫個(gè)腳本，批量插入些數(shù)據(jù)，看看情況：

for?i?in?{1..1000};?do?
??mysql?-uop?-p123456?--port?8066?-h?192.168.20.10?testdb??-e?"insert?into?hotnews(id,title,created_time)?values($i,'one',now());"
done

然后，到后端的3個(gè)分片上看下數(shù)據(jù)量，大致如下，還是比較均勻的：
(db1)?>?select?count(*)??from?db1.hotnews;
+----------+
|?count(*)?|
+----------+
|??????333?|
+----------+
1?row?in?set?(0.00?sec)

(db1)?>?select?count(*)??from?db2.hotnews;
+----------+
|?count(*)?|
+----------+
|??????334?|
+----------+
1?row?in?set?(0.00?sec)

(db1)?>?select?count(*)??from?db3.hotnews;
+----------+
|?count(*)?|
+----------+
|??????333?|
+----------+
1?row?in?set?(0.00?sec)

##?hashStringmod分區(qū)
CREATE?TABLE?`user_auth`?(
??`id`?bigint?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵id',
??`open_id`?varchar(100)?NOT?NULL?DEFAULT?''?COMMENT?'第三方授權(quán)id',
??`union_id`?varchar(100)?NOT?NULL?DEFAULT?''?COMMENT?'授權(quán)的關(guān)聯(lián)id',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COMMENT='用戶AUTH信息表'?;

####?注意：實(shí)際生產(chǎn)環(huán)境的主鍵id需要由程序去保證唯一性（例如使用雪花算法）

(testdb)?>?insert?into?user_auth?(id,open_id,union_id)?values(1,'331116828422393856','oy0IAj9mdPUr7bLMl879Jp37eV3Y');
(testdb)?>?insert?into?user_auth?(id,open_id,union_id)?values(2,'341170994247204864','oy0IA3Yj9mdPUr7bLMl879Jp37eV');
(testdb)?>?insert?into?user_auth?(id,open_id,union_id)?values(3,'330414325695332352','oy0IAj9mdPU3Yr7bLMl879Jp37eV');
(testdb)?>?insert?into?user_auth?(id,open_id,union_id)?values(4,'328588424011591680','oy0IAj9mdPUr7bLMl8Jp37e79V');
(testdb)?>?insert?into?user_auth?(id,open_id,union_id)?values(5,'330414325695332352','oy0IA3Yj9mdPUr7p37ebLMl879JV3Y');
(testdb)?>?insert?into?user_auth?(id,open_id,union_id)?values(6,'341172222247211111','oy0IAj9bLMl879Jp37eV3YmdPUr7');
(testdb)?>?insert?into?user_auth?(id,open_id,union_id)?values(7,'341173334247755464','Jp37eoy0IAj9mdPUr73YbLMl879V');

(testdb)?>?select?id,open_id,union_id?from?user_auth?order?by?id?asc?;
+----+--------------------+--------------------------------+
|?id?|?open_id????????????|?union_id???????????????????????|
+----+--------------------+--------------------------------+
|??1?|?331116828422393856?|?oy0IAj9mdPUr7bLMl879Jp37eV3Y???|
|??2?|?341170994247204864?|?oy0IA3Yj9mdPUr7bLMl879Jp37eV???|
|??3?|?330414325695332352?|?oy0IAj9mdPU3Yr7bLMl879Jp37eV???|
|??4?|?328588424011591680?|?oy0IAj9mdPUr7bLMl8Jp37e79V?????|
|??5?|?330414325695332352?|?oy0IA3Yj9mdPUr7p37ebLMl879JV3Y?|
|??6?|?341172222247211111?|?oy0IAj9bLMl879Jp37eV3YmdPUr7???|
|??7?|?341173334247755464?|?Jp37eoy0IAj9mdPUr73YbLMl879V???|
+----+--------------------+--------------------------------+
7?rows?in?set?(0.00?sec)

(testdb)?>?explain?select?id,open_id,union_id?from?user_auth?where?open_id?=?'341173334247755464'?;
+-----------+----------+--------------------------------------------------------------------------------+
|?DATA_NODE?|?TYPE?????|?SQL/REF????????????????????????????????????????????????????????????????????????|
+-----------+----------+--------------------------------------------------------------------------------+
|?dn2???????|?BASE?SQL?|?select?id,open_id,union_id?from?user_auth?where?open_id?=?'341173334247755464'?|
+-----------+----------+--------------------------------------------------------------------------------+
1?row?in?set?(0.00?sec)

(testdb)?>?explain?select?id,open_id,union_id?from?user_auth?where?open_id?=?'331116828422393856'?;
+-----------+----------+--------------------------------------------------------------------------------+
|?DATA_NODE?|?TYPE?????|?SQL/REF????????????????????????????????????????????????????????????????????????|
+-----------+----------+--------------------------------------------------------------------------------+
|?dn1???????|?BASE?SQL?|?select?id,open_id,union_id?from?user_auth?where?open_id?=?'331116828422393856'?|
+-----------+----------+--------------------------------------------------------------------------------+
1?row?in?set?(0.00?sec)

(testdb)?>?explain?select?id,open_id,union_id?from?user_auth?where?open_id?=?'328588424011591680'?;
+-----------+----------+--------------------------------------------------------------------------------+
|?DATA_NODE?|?TYPE?????|?SQL/REF????????????????????????????????????????????????????????????????????????|
+-----------+----------+--------------------------------------------------------------------------------+
|?dn3???????|?BASE?SQL?|?select?id,open_id,union_id?from?user_auth?where?open_id?=?'328588424011591680'?|
+-----------+----------+--------------------------------------------------------------------------------+
1?row?in?set?(0.00?sec)

############################################################################

上面就是幾種常用的分區(qū)了， 另外還有種 date類型按時(shí)間分區(qū)的可能在日志表的場景下也常用些。

date類型分區(qū)的實(shí)驗(yàn)：

先去后端的db上創(chuàng)建物理的庫：

create?database?userdb1?;
create?database?userdb2?;
create?database?userdb3?;
create?database?userdb4?;
create?database?userdb5?;
create?database?userdb6?;
create?database?userdb7?;
create?database?userdb8?;
create?database?userdb9?;
create?database?userdb10?;
create?database?userdb11?;
create?database?userdb12?;
create?database?userdb13?;

修改后的 schema.xml 類似如下：






????
????????
????????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????????select?user()
????????
????????????
????????
????
????

然后，到 rule.xml中添加規(guī)則：


????
????????addData
????????partbymonth-user
????



????
????????yyyy-MM-dd
????????????2018-01-01
?????????
????????30??
????????0
????

(testdb)?>?create?table?if?not?exists?user?(addData?date,?dbname?varchar(32),username?varchar(32),province?varchar(16),age?int(3));

(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2015-01-01',database(),'user1',12);

(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2016-02-01',database(),'user1',12);

(testdb)?>?explain??insert?into?user?(addData,dbname,username,age)?values?('2017-03-01',database(),'user1',12);
+-----------+----------+--------------------------------------------------------------------------------------------------+
|?DATA_NODE?|?TYPE?????|?SQL/REF??????????????????????????????????????????????????????????????????????????????????????????|
+-----------+----------+--------------------------------------------------------------------------------------------------+
|?user_dn1??|?BASE?SQL?|?INSERT?INTO?user?(addData,?dbname,?username,?age)?VALUES?('2017-03-01',?DATABASE(),?'user1',?12)?|
+-----------+----------+--------------------------------------------------------------------------------------------------+
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2017-03-01',database(),'user1',12);


(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-04-01',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-04-11',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-04-21',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-04-25',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-04-30',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-05-01',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-05-03',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-05-05',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-06-21',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2018-07-30',database(),'user1',12);
(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2019-01-01',database(),'user1',12);

(testdb)?>?insert?into?user?(addData,dbname,username,age)?values?('2019-06-01',database(),'user1',12);
ERROR?1064?(HY000):?can't?find?any?valid?data?node?:user?->?ADDDATA?->?2019-06-01


因此，我們需要提前人工把分片加好?并做好可用分區(qū)的監(jiān)控，不然會造成無法寫入數(shù)據(jù)的事故出現(xiàn)。


(testdb)?>?select?*?from?user?order?by?addData?asc?;
+------------+----------+----------+----------+------+
|?addData????|?dbname???|?username?|?province?|?age??|
+------------+----------+----------+----------+------+
|?2015-01-01?|?userdb1??|?user1????|?NULL?????|???12?|
|?2016-02-01?|?userdb1??|?user1????|?NULL?????|???12?|
|?2017-03-01?|?userdb1??|?user1????|?NULL?????|???12?|
|?2018-04-01?|?userdb4??|?user1????|?NULL?????|???12?|
|?2018-04-11?|?userdb4??|?user1????|?NULL?????|???12?|
|?2018-04-21?|?userdb4??|?user1????|?NULL?????|???12?|
|?2018-04-25?|?userdb4??|?user1????|?NULL?????|???12?|
|?2018-04-30?|?userdb4??|?user1????|?NULL?????|???12?|
|?2018-05-01?|?userdb5??|?user1????|?NULL?????|???12?|
|?2018-05-03?|?userdb5??|?user1????|?NULL?????|???12?|
|?2018-05-05?|?userdb5??|?user1????|?NULL?????|???12?|
|?2018-06-21?|?userdb6??|?user1????|?NULL?????|???12?|
|?2018-07-30?|?userdb8??|?user1????|?NULL?????|???12?|
|?2019-01-01?|?userdb13?|?user1????|?NULL?????|???12?|
+------------+----------+----------+----------+------+
14?rows?in?set?(0.02?sec)


查詢測試：
(testdb)?>?explain?select?*?from?user?where?addData?between?'2018-04-01'?and?'2018-04-30'?;
+-----------+----------+------------------------------------------------------------------------+
|?DATA_NODE?|?TYPE?????|?SQL/REF????????????????????????????????????????????????????????????????|
+-----------+----------+------------------------------------------------------------------------+
|?user_dn4??|?BASE?SQL?|?select?*?from?user?where?addData?between?'2018-04-01'?and?'2018-04-30'?|
+-----------+----------+------------------------------------------------------------------------+
1?row?in?set?(0.00?sec)


(testdb)?>?select?*?from?user?where?addData?between?'2018-04-01'?and?'2018-04-30'?;
+------------+---------+----------+----------+------+
|?addData????|?dbname??|?username?|?province?|?age??|
+------------+---------+----------+----------+------+
|?2018-04-01?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-11?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-21?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-25?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-30?|?userdb4?|?user1????|?NULL?????|???12?|
+------------+---------+----------+----------+------+
5?rows?in?set?(0.01?sec)


(testdb)?>?explain?select?*?from?user?where?addData?between?'2018-04-01'?and?'2018-05-30'?order?by?addData?asc?;
+-----------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|?DATA_NODE???????|?TYPE??????????|?SQL/REF?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|
+-----------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|?user_dn4_0??????|?BASE?SQL??????|?select?`user`.`addData`,`user`.`dbname`,`user`.`username`,`user`.`province`,`user`.`age`?from??`user`?where?addData?BETWEEN?'2018-04-01'?AND?'2018-05-30'?ORDER?BY?`user`.`addData`?ASC?|
|?user_dn5_0??????|?BASE?SQL??????|?select?`user`.`addData`,`user`.`dbname`,`user`.`username`,`user`.`province`,`user`.`age`?from??`user`?where?addData?BETWEEN?'2018-04-01'?AND?'2018-05-30'?ORDER?BY?`user`.`addData`?ASC?|
|?merge_1?????????|?MERGE?????????|?user_dn4_0;?user_dn5_0??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|
|?shuffle_field_1?|?SHUFFLE_FIELD?|?merge_1?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|
+-----------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4?rows?in?set?(0.00?sec)


(testdb)?>?select?*?from?user?where?addData?between?'2018-04-01'?and?'2018-05-30'?order?by?addData?asc?;
+------------+---------+----------+----------+------+
|?addData????|?dbname??|?username?|?province?|?age??|
+------------+---------+----------+----------+------+
|?2018-04-01?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-11?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-21?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-25?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-04-30?|?userdb4?|?user1????|?NULL?????|???12?|
|?2018-05-01?|?userdb5?|?user1????|?NULL?????|???12?|
|?2018-05-03?|?userdb5?|?user1????|?NULL?????|???12?|
|?2018-05-05?|?userdb5?|?user1????|?NULL?????|???12?|
+------------+---------+----------+----------+------+
8?rows?in?set?(0.01?sec)

date類型的可用分區(qū)的監(jiān)控（腳本的原理同樣適用于其他類型的分區(qū)）：

簡單的做法就是定期執(zhí)行一個(gè)explain的insert插入測試， 如果有ERROR關(guān)鍵字就告警出來

一個(gè)簡單的腳本如下：
#?提前60天預(yù)警

DAYS=$(date?-d?60days??+%F)
echo?$DAYS

if?mysql?-urw?-prw123456?--port?8066?-h?192.168.20.10?testdb?2>/dev/null?-e?"explain?insert?into?user?(addData,dbname,username,age)?values?(\"$DAYS\",database(),'user1',12);"?;?then?
????echo?"當(dāng)前可用分片數(shù)量處于安全狀態(tài)"
else
????echo?"需要加新的分片了"
fi

date類型加新的分片的方法：?

1、修改schema.xml?加上新的分片的配置信息，修改后大致這樣：





????	
????????
????????
????

????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????
????

????
????????select?user()
????????
????????????
????????
????



2、重載配置文件
reload?@@config_all?;


3、去后端創(chuàng)建對應(yīng)的物理庫??
create?database?userdb14;
.....這里省略其它的建庫語句.......
create?database?userdb23;

4、通過dble再次下發(fā)下建表命令
create?table?if?not?exists?user?(addData?date,?dbname?varchar(32),username?varchar(32),province?varchar(16),age?int(3));


5、插入數(shù)據(jù)測試
(testdb)?>?explain?insert?into?user?(addData,dbname,username,age)?values?('2019-11-01',database(),'user1',12);
+-----------+----------+--------------------------------------------------------------------------------------------------+
|?DATA_NODE?|?TYPE?????|?SQL/REF??????????????????????????????????????????????????????????????????????????????????????????|
+-----------+----------+--------------------------------------------------------------------------------------------------+
|?user_dn23?|?BASE?SQL?|?INSERT?INTO?user?(addData,?dbname,?username,?age)?VALUES?('2019-11-01',?DATABASE(),?'user1',?12)?|
+-----------+----------+--------------------------------------------------------------------------------------------------+
1?row?in?set?(0.00?sec)

(testdb)?>?explain?insert?into?user?(addData,dbname,username,age)?values?('2019-12-01',database(),'user1',12);
ERROR?1064?(HY000):?can't?find?any?valid?data?node?:user?->?ADDDATA?->?2019-12-01

######################################################################################################

ER 表 （互聯(lián)網(wǎng)場景下用多表JOIN的不多，因此ER分片規(guī)則不太常用到，但是需要大致的了解）：

下面的內(nèi)容大篇幅參考： https://blog.csdn.net/zhanglei_16/article/details/50779929

1：ER分片關(guān)系簡介

有一類業(yè)務(wù)，例如訂單（ORDER）跟訂單明細(xì)表（ORDER_DETAIL）,明細(xì)表會依賴訂單單，就是該會存在表的主從關(guān)系，

這類似業(yè)務(wù)的切分可以抽象出合適的切分規(guī)則，比如根據(jù)用戶ID切分,其它相關(guān)的表都依賴于用戶ID，再或者根據(jù)訂單ID進(jìn)行切分，

總之部分業(yè)務(wù)總會可以抽象出父子關(guān)系的表。這類表適用于ER分片表，子表的記錄與所關(guān)聯(lián)的父表記錄存放在同一個(gè)數(shù)據(jù)分片上，

避免數(shù)據(jù)Join跨庫操作，以order與order_detail例子為例，schema.xml中定義合適的分片配置,order,order_detail 根據(jù)order_id

迕行數(shù)據(jù)切分，保證相同order_id的數(shù)據(jù)分到同一個(gè)分片上，在進(jìn)行數(shù)據(jù)插入操作時(shí)，Mycat會獲取order所在的分片，

然后將order_detail也插入到order所在的分片

2：父表按照主鍵ID分片，字表的分片字段與主表ID關(guān)聯(lián)，配置為ＥＲ分片

2.1：在schema.xml添加如下配置配置文件修改

?

??

在rule.xml里面設(shè)定分片規(guī)則：

? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? id

? ? ? ? ? ? hashmod3

? ? ? ?

? ?

? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

? ? ? ? 3

? ? ? ? 1

? ?

然后， reload 下 dble?

2.2 先建表， order 和 order_detail 表，有主外鍵關(guān)系

mysql> explain CREATE TABLE order1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,sn VARCHAR(64),create_time DATETIME) ENGINE=InnoDB DEFAULT CHARSET=utf8;

+-----------+-----------------------------------------------------------------------------------------------------+

| DATA_NODE | SQL? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|

+-----------+-----------------------------------------------------------------------------------------------------+

| dn1? ? ? ?| CREATE TABLE order1(id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,sn VARCHAR(64),create_time DATETIME) |

| dn2? ? ? ?| CREATE TABLE order1(id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,sn VARCHAR(64),create_time DATETIME) |

| dn3? ? ? ?| CREATE TABLE order1(id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,sn VARCHAR(64),create_time DATETIME) |

+-----------+-----------------------------------------------------------------------------------------------------+

3 rows in set (0.02 sec)

mysql> CREATE TABLE order1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,sn VARCHAR(64),create_time DATETIME) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.35 sec)

mysql> CREATE TABLE order_detail(id INT AUTO_INCREMENT PRIMARY KEY, order_id INT,ord_status CHAR(1),address VARCHAR(128),create_time DATETIME,CONSTRAINT FK_ORDid FOREIGN KEY (order_id) REFERENCES order1 (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.44 sec)

3.3 錄入數(shù)據(jù)：

mysql> explain INSERT INTO order1(id,sn,create_time) VALUES(1,'BJ0001',NOW());

+-----------+----------------------------------------------------------------+

| DATA_NODE | SQL? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-----------+----------------------------------------------------------------+

| dn2? ? ? ?| INSERT INTO order1(id,sn,create_time) VALUES(1,'BJ0001',NOW()) |

+-----------+----------------------------------------------------------------+

1 row in set (0.03 sec)

錄入數(shù)據(jù)，一組組錄入，涉及到外鍵關(guān)系：?

第一組北京的訂單

mysql> INSERT INTO order1(id,sn,create_time) VALUES(1,'BJ0001',NOW());

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO ORDER_DETAIL(id,order_id,ord_status,address,create_time) VALUES (1,1,'1','test data? of order1(id=1,BJ001) ',NOW());

第二組上海的訂單：

mysql> explain INSERT INTO order1(id,sn,create_time) VALUES(3,'SHH001',NOW());

+-----------+----------------------------------------------------------------+

| DATA_NODE | SQL? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-----------+----------------------------------------------------------------+

| dn1? ? ? ?| INSERT INTO order1(id,sn,create_time) VALUES(3,'SHH001',NOW()) |

+-----------+----------------------------------------------------------------+

1 row in set (0.02 sec)

mysql> INSERT INTO order1(id,sn,create_time) VALUES(3,'SHH001',NOW());

Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO ORDER_DETAIL(id,order_id,ord_status,address,create_time) VALUES (3,3,'1','test data of order1(id=3,SHH001)',NOW());

Query OK, 1 row affected (0.06 sec)

第三組廣州的訂單：

mysql> explain INSERT INTO order1(id,sn,create_time) VALUES(4,'GZH004',NOW());

+-----------+----------------------------------------------------------------+

| DATA_NODE | SQL? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-----------+----------------------------------------------------------------+

| dn2? ? ? ?| INSERT INTO order1(id,sn,create_time) VALUES(4,'GZH004',NOW()) |

+-----------+----------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> INSERT INTO order1(id,sn,create_time) VALUES(4,'GZH004',NOW());

Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO ORDER_DETAIL(id,order_id,ord_status,address,create_time) VALUES (4,4,'1','test data? of order1(id=4,GZH004) ',NOW());

Query OK, 1 row affected (0.05 sec)

第四組 武漢的訂單，這里故意將order_id設(shè)置成4，看看效果，是否隨id為4的廣州的那組分片：

mysql> explain INSERT INTO order1(id,sn,create_time) VALUES(5,'WUHAN005',NOW());

+-----------+------------------------------------------------------------------+

| DATA_NODE | SQL? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-----------+------------------------------------------------------------------+

| dn3? ? ? ?| INSERT INTO order1(id,sn,create_time) VALUES(5,'WUHAN005',NOW()) |

+-----------+------------------------------------------------------------------+

1 row in set (0.01 sec)

? ??

mysql> explain INSERT INTO order1(id,sn,create_time) VALUES(6,'WUHAN006',NOW());

Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO ORDER_DETAIL(id,order_id,ord_status,address,create_time) VALUES (6,4,'1','test data? of order1(id=6,WUHAN006) ',NOW());

Query OK, 1 row affected (0.05 sec)

通過DBLE，查看下數(shù)據(jù)寫入的情況：

(testdb) > select * from order1;

+----+--------+---------------------+

| id | sn? ? ?| create_time? ? ? ? ?|

+----+--------+---------------------+

|? 1 | BJ0001 | 2019-08-31 23:05:36 |

|? 4 | GZH004 | 2019-08-31 23:06:57 |

|? 3 | SHH001 | 2019-08-31 23:06:43 |

+----+--------+---------------------+

3 rows in set (0.01 sec)

(testdb) > select * from order_detail ;

+----+----------+------------+--------------------------------------+---------------------+

| id | order_id | ord_status | address? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | create_time? ? ? ? ?|

+----+----------+------------+--------------------------------------+---------------------+

|? 1 |? ? ? ? 1 | 1? ? ? ? ? | test data? of ORDER1(ID=1,BJ001)? ? ?| 2019-08-31 23:06:17 |

|? 4 |? ? ? ? 4 | 1? ? ? ? ? | test data? of ORDER1(ID=4,GZH004)? ? | 2019-08-31 23:07:01 |

|? 6 |? ? ? ? 4 | 1? ? ? ? ? | test data? of ORDER1(ID=6,WUHAN006)? | 2019-08-31 23:07:23 |

|? 3 |? ? ? ? 3 | 1? ? ? ? ? | test data of ORDER1(ID=3,SHH001)? ? ?| 2019-08-31 23:06:47 |

+----+----------+------------+--------------------------------------+---------------------+

4 rows in set (0.01 sec)

直連后端的db1，看下數(shù)據(jù)情況 (db2 和 db3 上面的數(shù)據(jù)查看，使用同樣的方法）；

((none)) > select * from db1.order1;

+----+--------+---------------------+

| id | sn? ? ?| create_time? ? ? ? ?|

+----+--------+---------------------+

|? 3 | SHH001 | 2019-08-31 23:06:43 |

+----+--------+---------------------+

1 row in set (0.00 sec)

((none)) > select * from db1.order_detail;

+----+----------+------------+----------------------------------+---------------------+

| id | order_id | ord_status | address? ? ? ? ? ? ? ? ? ? ? ? ? | create_time? ? ? ? ?|

+----+----------+------------+----------------------------------+---------------------+

|? 3 |? ? ? ? 3 | 1? ? ? ? ? | test data of ORDER1(ID=3,SHH001) | 2019-08-31 23:06:47 |

+----+----------+------------+----------------------------------+---------------------+

1 row in set (0.00 sec)

2.6 走DBLE，模擬下業(yè)務(wù)的查詢：

(testdb) > explain select t1.*,t2.* from order1 t1,order_detail t2 where t2.ord_status='1' and t2.id=1 and t1.id=t2.order_id;

+-----------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| DATA_NODE? ? ? ?| TYPE? ? ? ? ? | SQL/REF? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-----------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| dn1_0? ? ? ? ? ?| BASE SQL? ? ? | select `t2`.`id`,`t2`.`order_id`,`t2`.`ord_status`,`t2`.`address`,`t2`.`create_time`,`t1`.`id`,`t1`.`sn`,`t1`.`create_time` from? `order1` `t1` join? `order_detail` `t2` on `t1`.`id` = `t2`.`order_id` where (`t2`.`ord_status` = '1') AND (`t2`.`id` = 1) |

| dn2_0? ? ? ? ? ?| BASE SQL? ? ? | select `t2`.`id`,`t2`.`order_id`,`t2`.`ord_status`,`t2`.`address`,`t2`.`create_time`,`t1`.`id`,`t1`.`sn`,`t1`.`create_time` from? `order1` `t1` join? `order_detail` `t2` on `t1`.`id` = `t2`.`order_id` where (`t2`.`ord_status` = '1') AND (`t2`.`id` = 1) |

| dn3_0? ? ? ? ? ?| BASE SQL? ? ? | select `t2`.`id`,`t2`.`order_id`,`t2`.`ord_status`,`t2`.`address`,`t2`.`create_time`,`t1`.`id`,`t1`.`sn`,`t1`.`create_time` from? `order1` `t1` join? `order_detail` `t2` on `t1`.`id` = `t2`.`order_id` where (`t2`.`ord_status` = '1') AND (`t2`.`id` = 1) |

| merge_1? ? ? ? ?| MERGE? ? ? ? ?| dn1_0; dn2_0; dn3_0? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| shuffle_field_1 | SHUFFLE_FIELD | merge_1? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+-----------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5 rows in set (0.00 sec)

(testdb) > SELECT

? t1.*,

? t2.*

FROM

? order1 t1,

? order_detail t2

WHERE t2.ord_status = '1'

? AND t2.id = 1

? AND t1.id = t2.order_id ;

+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+

| id | sn? ? ?| create_time? ? ? ? ?| id | order_id | ord_status | address? ? ? ? ? ? ? ? ? ? ? ? ? ?| create_time? ? ? ? ?|

+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+

|? 1 | BJ0001 | 2019-08-31 23:05:36 |? 1 |? ? ? ? 1 | 1? ? ? ? ? | test data? of ORDER1(ID=1,BJ001)? | 2019-08-31 23:06:17 |

+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+

1 row in set (0.00 sec)

2.7 總結(jié)：當(dāng)子表與父表的關(guān)聯(lián)字段正好是父表的分片字段時(shí)，子表直接根據(jù)父表規(guī)則進(jìn)行分片，在數(shù)據(jù)錄入的時(shí)候子表直接放在父表的分片上面，在進(jìn)行關(guān)聯(lián)查詢join的時(shí)候，走的是父表的路由。

【重要】其它的總結(jié)：

當(dāng)子表與父表的關(guān)聯(lián)字段不是父表的分片字段時(shí)，必須通過查找對應(yīng)的父表記錄來確認(rèn)子表所在分片，如果找不到則會拋出錯(cuò)誤，在join查詢的時(shí)候，路由走的是所有分片節(jié)點(diǎn)?。。?！


網(wǎng)頁標(biāo)題：DBLE分庫分表實(shí)戰(zhàn)
網(wǎng)站URL：http://weahome.cn/article/pjpgjh.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部