DBLE分庫分表實戰

環境： DBLE 2.19.03.0

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

IP：? 192.168.20.10/24

MySQL版本： MySQL-社區版-5.7.26

添加2個賬號授權：

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? ?修改后的如下：

<?xml?version="1.0"?>
<!DOCTYPE?dble:schema?SYSTEM?"schema.dtd">
<dble:schema?xmlns:dble="http://dble.cloud/"?version="2.19.03.0">

????<schema?name="testdb">
????????<!--?全局表?-->
????????<table?name="company"?primaryKey="id"?type="global"?dataNode="dn1,dn2,dn3"/>

????????<!--?range分區2?-->
????????<table?name="travelrecord"?primaryKey="id"?dataNode="dn1,dn2,dn3"?rule="sharding-by-range_t"/>

????????<!--?hash?mod?3?分區?-->
????????<table?name="hotnews"?primaryKey="id"?dataNode="dn1,dn2,dn3"?rule="id-sharding-by-mod3"/>

????????<!--?hashStringmod3?分區?-->
????????<table?name="user_auth"?primaryKey="open_id"?dataNode="dn1,dn2,dn3"?rule="user-auth-sharding-by-open_id"?/>

????????<!--?ER?分區?-->
????????<table?name="order1"?dataNode="dn1,dn2,dn3"?rule="id-sharding-by-mod3">?
????????????<childTable?name="order_detail"?primaryKey="id"?joinKey="order_id"?parentKey="id"?/>?
????????</table>
????</schema>
????
????<dataNode?name="dn1"?dataHost="192.168.20.10"?database="db1"/>
????<dataNode?name="dn2"?dataHost="192.168.20.10"?database="db2"/>
????<dataNode?name="dn3"?dataHost="192.168.20.10"?database="db3"/>
????
????<dataHost?name="192.168.20.10"?maxCon="500"?minCon="10"?balance="0"?switchType="-1"?slaveThreshold="100">
????????<heartbeat>select?user()</heartbeat>
????????<writeHost?host="hostM"?url="192.168.20.10:3306"?user="rw"?password="rw123456">
????????????<readHost?host="hostS"?url="192.168.20.10:3306"?user="rd"?password="rd123456"/>
????????</writeHost>
????</dataHost>
????
</dble:schema>

vim?rule.xml 修改后的內容如下：

????<tableRule?name="sharding-by-range_t">
????????<rule>
????????????<columns>id</columns>
????????????<algorithm>rangeLong2</algorithm>
????????</rule>
????</tableRule>

????<tableRule?name="id-sharding-by-mod3">
????????<rule>
????????????<columns>id</columns>
????????????<algorithm>hashmod3</algorithm>
????????</rule>
????</tableRule>

????<tableRule?name="user-auth-sharding-by-open_id">
????????<rule>??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????????????<columns>open_id</columns>
????????????<algorithm>hashStringmod3</algorithm>
????????</rule>
????</tableRule>

????<function?name="rangeLong2"?class="NumberRange">
????????<property?name="mapFile">autopartition-long_t.txt</property>
????????<property?name="defaultNode">0</property><!--?不符合條件的插入到第一個分區去?-->
????</function>
????
????<function?name="hashmod3"?class="Hash">
????????<property?name="partitionCount">3</property>
????????<property?name="partitionLength">1</property>
????</function>
????
????<function?name="hashStringmod3"?class="StringHash">
????????<property?name="partitionCount">3</property>
????????<property?name="partitionLength">1</property>
????????<property?name="hashSlice">0:20</property>??<!--?表示取前20位進行hash取模后再決定數據落在那個分片上?-->
????</function>

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

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

vim server.xml 內容如下：

修改user部分為如下：?
????<user?name="man1">
????????<property?name="password">654321</property>
????????<property?name="manager">true</property>
????????<!--?manager?user?can't?set?schema-->
????</user>
????<user?name="op">
????????<property?name="password">123456</property>
????????<property?name="schemas">testdb</property>
????</user>
????????<!--?table's?DML?privileges??INSERT/UPDATE/SELECT/DELETE?-->
<!--
????????<privileges?check="true">
????????????<schema?name="testdb"?dml="0110"?>
????????????????<table?name="employee"?dml="1111"></table>
????????????</schema>
????????</privileges>
-->
????<user?name="rw">
????????<property?name="password">rw123456</property>
????????<property?name="schemas">testdb</property>
????</user>
????<user?name="rd">
????????<property?name="password">rd123456</property>
????????<property?name="schemas">testdb</property>
????????<property?name="readOnly">true</property>
????</user>

然后， reload 下 dble ， 進行測試

ddl專用：

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

去創建符合上面的要求的幾個表,并寫入數據測試：

##?測試range分區
(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)

多執行幾次，你會看到三個分片上都插入了3條數據，因為company定義為全局表。

(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?;???命令也可以看到隨機分發到3個節點的。

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

然后，?我們寫個腳本，批量插入些數據，看看情況：

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個分片上看下數據量，大致如下，還是比較均勻的：
(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分區
CREATE?TABLE?`user_auth`?(
??`id`?bigint?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵id',
??`open_id`?varchar(100)?NOT?NULL?DEFAULT?''?COMMENT?'第三方授權id',
??`union_id`?varchar(100)?NOT?NULL?DEFAULT?''?COMMENT?'授權的關聯id',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COMMENT='用戶AUTH信息表'?;

####?注意：實際生產環境的主鍵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)

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

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

date類型分區的實驗：

先去后端的db上創建物理的庫：

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 類似如下：

<?xml?version="1.0"?>
<!DOCTYPE?dble:schema?SYSTEM?"schema.dtd">

<dble:schema?xmlns:dble="http://dble.cloud/"?version="2.19.03.0">

????<schema?name="testdb">
????????<!--?按月分片?-->
????????<table?name="user"?dataNode="user_dn$1-13"?rule="sharding-by-month-user"/>
????</schema>
????
????<dataNode?name="user_dn1"?dataHost="192.168.20.10"?database="userdb1"/>
????<dataNode?name="user_dn2"?dataHost="192.168.20.10"?database="userdb2"/>
????<dataNode?name="user_dn3"?dataHost="192.168.20.10"?database="userdb3"/>
????<dataNode?name="user_dn4"?dataHost="192.168.20.10"?database="userdb4"/>
????<dataNode?name="user_dn5"?dataHost="192.168.20.10"?database="userdb5"/>
????<dataNode?name="user_dn6"?dataHost="192.168.20.10"?database="userdb6"/>
????<dataNode?name="user_dn7"?dataHost="192.168.20.10"?database="userdb7"/>
????<dataNode?name="user_dn8"?dataHost="192.168.20.10"?database="userdb8"/>
????<dataNode?name="user_dn9"?dataHost="192.168.20.10"?database="userdb9"/>
????<dataNode?name="user_dn10"?dataHost="192.168.20.10"?database="userdb10"/>
????<dataNode?name="user_dn11"?dataHost="192.168.20.10"?database="userdb11"/>
????<dataNode?name="user_dn12"?dataHost="192.168.20.10"?database="userdb12"/>
????<dataNode?name="user_dn13"?dataHost="192.168.20.10"?database="userdb13"/>
????
????<dataHost?name="192.168.20.10"?maxCon="500"?minCon="10"?balance="0"?switchType="-1"?slaveThreshold="100">
????????<heartbeat>select?user()</heartbeat>
????????<writeHost?host="hostM"?url="192.168.20.10:3306"?user="rw"?password="rw123456">
????????????<readHost?host="hostS"?url="192.168.20.10:3306"?user="rd"?password="rd123456"/>
????????</writeHost>
????</dataHost>
????
</dble:schema>

然后，到 rule.xml中添加規則：

<tableRule?name="sharding-by-month-user">
????<rule>
????????<columns>addData</columns>
????????<algorithm>partbymonth-user</algorithm>
????</rule>
</tableRule>

<!--?加的基于月份的分片規則，?注意如果數量超了?會插入報錯?-->
????<function?name="partbymonth-user"?class="Date">
????????<property?name="dateFormat">yyyy-MM-dd</property>
????????????<property?name="sBeginDate">2018-01-01</property>
?????????<!--??<property?name="sEndDate">2019-02-31</property>?-->
????????<property?name="sPartionDay">30</property>??<!--?默認是每10天一個分片。我這里改成每30天一個分片，另外注意并不按照固定的月來寫入?-->
????????<property?name="defaultNode">0</property><!--?默認小于?2018-01-01?的數據插入到dn1去?-->
????</function>

(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


因此，我們需要提前人工把分片加好?并做好可用分區的監控，不然會造成無法寫入數據的事故出現。


(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類型的可用分區的監控（腳本的原理同樣適用于其他類型的分區）：

簡單的做法就是定期執行一個explain的insert插入測試， 如果有ERROR關鍵字就告警出來

一個簡單的腳本如下：
#?提前60天預警

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?"當前可用分片數量處于安全狀態"
else
????echo?"需要加新的分片了"
fi

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

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

<?xml?version="1.0"?>
<!DOCTYPE?dble:schema?SYSTEM?"schema.dtd">
<dble:schema?xmlns:dble="http://dble.cloud/"?version="2.19.03.0">

????<schema?name="testdb">	
????????<!--?按月分片?-->
????????<table?name="user"?dataNode="user_dn$1-23"?rule="sharding-by-month-user"/>
????</schema>

????<dataNode?name="user_dn1"?dataHost="192.168.20.10"?database="userdb1"/>
????<dataNode?name="user_dn2"?dataHost="192.168.20.10"?database="userdb2"/>
????<dataNode?name="user_dn3"?dataHost="192.168.20.10"?database="userdb3"/>
????<dataNode?name="user_dn4"?dataHost="192.168.20.10"?database="userdb4"/>
????<dataNode?name="user_dn5"?dataHost="192.168.20.10"?database="userdb5"/>
????<dataNode?name="user_dn6"?dataHost="192.168.20.10"?database="userdb6"/>
????<dataNode?name="user_dn7"?dataHost="192.168.20.10"?database="userdb7"/>
????<dataNode?name="user_dn8"?dataHost="192.168.20.10"?database="userdb8"/>
????<dataNode?name="user_dn9"?dataHost="192.168.20.10"?database="userdb9"/>
????<dataNode?name="user_dn10"?dataHost="192.168.20.10"?database="userdb10"/>
????<dataNode?name="user_dn11"?dataHost="192.168.20.10"?database="userdb11"/>
????<dataNode?name="user_dn12"?dataHost="192.168.20.10"?database="userdb12"/>
????<dataNode?name="user_dn13"?dataHost="192.168.20.10"?database="userdb13"/>
????<dataNode?name="user_dn14"?dataHost="192.168.20.10"?database="userdb14"/>
????<dataNode?name="user_dn15"?dataHost="192.168.20.10"?database="userdb15"/>
????<dataNode?name="user_dn16"?dataHost="192.168.20.10"?database="userdb16"/>
????<dataNode?name="user_dn17"?dataHost="192.168.20.10"?database="userdb17"/>
????<dataNode?name="user_dn18"?dataHost="192.168.20.10"?database="userdb18"/>
????<dataNode?name="user_dn19"?dataHost="192.168.20.10"?database="userdb19"/>
????<dataNode?name="user_dn20"?dataHost="192.168.20.10"?database="userdb20"/>
????<dataNode?name="user_dn21"?dataHost="192.168.20.10"?database="userdb21"/>
????<dataNode?name="user_dn22"?dataHost="192.168.20.10"?database="userdb22"/>
????<dataNode?name="user_dn23"?dataHost="192.168.20.10"?database="userdb23"/>

????<dataHost?name="192.168.20.10"?maxCon="500"?minCon="10"?balance="0"?switchType="-1"?slaveThreshold="100">
????????<heartbeat>select?user()</heartbeat>
????????<writeHost?host="hostM"?url="192.168.20.10:3306"?user="rw"?password="rw123456">
????????????<readHost?host="hostS"?url="192.168.20.10:3306"?user="rd"?password="rd123456"/>
????????</writeHost>
????</dataHost>
</dble:schema>


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


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

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


5、插入數據測試
(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 表 （互聯網場景下用多表JOIN的不多，因此ER分片規則不太常用到，但是需要大致的了解）：

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

1：ER分片關系簡介

有一類業務，例如訂單（ORDER）跟訂單明細表（ORDER_DETAIL）,明細表會依賴訂單單，就是該會存在表的主從關系，

這類似業務的切分可以抽象出合適的切分規則，比如根據用戶ID切分,其它相關的表都依賴于用戶ID，再或者根據訂單ID進行切分，

總之部分業務總會可以抽象出父子關系的表。這類表適用于ER分片表，子表的記錄與所關聯的父表記錄存放在同一個數據分片上，

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

迕行數據切分，保證相同order_id的數據分到同一個分片上，在進行數據插入操作時，Mycat會獲取order所在的分片，

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

2：父表按照主鍵ID分片，字表的分片字段與主表ID關聯，配置為ＥＲ分片

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

<!-- ER 分區 -->

<table name="order1" dataNode="dn1,dn2,dn3" rule="id-sharding-by-mod3">?

?<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="id" />?

</table>

在rule.xml里面設定分片規則：

? ? <tableRule name="id-sharding-by-mod3">

? ? ? ? <rule>? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? <columns>id</columns>

? ? ? ? ? ? <algorithm>hashmod3</algorithm>

? ? ? ? </rule>

? ? </tableRule>

? ? <!-- mod 3 -->

? ? <function name="hashmod3" class="Hash">? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

? ? ? ? <property name="partitionCount">3</property>

? ? ? ? <property name="partitionLength">1</property>

? ? </function>

然后， reload 下 dble?

2.2 先建表， order 和 order_detail 表，有主外鍵關系

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 錄入數據：

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)

錄入數據，一組組錄入，涉及到外鍵關系：?

第一組北京的訂單

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設置成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，查看下數據寫入的情況：

(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，看下數據情況 (db2 和 db3 上面的數據查看，使用同樣的方法）；

((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，模擬下業務的查詢：

(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 總結：當子表與父表的關聯字段正好是父表的分片字段時，子表直接根據父表規則進行分片，在數據錄入的時候子表直接放在父表的分片上面，在進行關聯查詢join的時候，走的是父表的路由。

【重要】其它的總結：

當子表與父表的關聯字段不是父表的分片字段時，必須通過查找對應的父表記錄來確認子表所在分片，如果找不到則會拋出錯誤，在join查詢的時候，路由走的是所有分片節點！！！！