环境: 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 -sdble-2.19.03 dble
cd conf/
vim schema.xml 修改后的如下:<?xml version="1.0"?>
dble:schemaSYSTEM"schema.dtd">
selectuser()
vimrule.xml 修改后的内容如下:
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 # 增加一个路由规则文件#rangestart-end,datanodeindex
#K=1000,M=10000.
#范围:前开后闭(开区间,闭区间]
0-1M=0
1M-2M=1
2M-3M=2
vim server.xml 内容如下:修改user部分为如下:
654321
true
123456
testdb
rw123456
testdb
rd123456
testdb
true
然后, reload 下 dble , 进行测试
ddl专用:
mysql -uop -p123456 --port 8066 -h 192.168.20.10 testdb
去创建符合上面的要求的几个表,并写入数据测试:
##测试range分区
(testdb)>createtabletravelrecord(
idbigintnotnullprimarykey,
user_idvarchar(100),
traveldateDATE,
feedecimal(10,2),
daysint
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
(testdb)>insertintotravelrecord(id,user_id,traveldate,fee,days)values(10,'wang','-01-05',510,3);
(testdb)>insertintotravelrecord(id,user_id,traveldate,fee,days)values(13000,'lee','-01-05',26.5,3);
(testdb)>insertintotravelrecord(id,user_id,traveldate,fee,days)values(29800,'zhang','-01-05',23.3,3);
(testdb)>select*fromtravelrecord;
+-------+---------+------------+--------+------+
|id|user_id|traveldate|fee|days|
+-------+---------+------------+--------+------+
|10|wang|-01-05|510.00|3|
|13000|lee|-01-05|26.50|3|
|29800|zhang|-01-05|23.30|3|
+-------+---------+------------+--------+------+
##测试全局表
(testdb)>createtablecompany(idintnotnullprimarykey,namevarchar(100));
(testdb)>insertintocompany(id,name)values(1,'hp');
(testdb)>insertintocompany(id,name)values(2,'ibm');
(testdb)>insertintocompany(id,name)values(3,'oracle');
(testdb)>select*fromcompany;
+----+--------+
|id|name|
+----+--------+
|1|hp|
|2|ibm|
|3|oracle|
+----+--------+
3rowsinset(0.01sec)
多执行几次,你会看到三个分片上都插入了3条数据,因为company定义为全局表。
(testdb)>explaininsertintocompany(id,name)values(1,'hp');
+-----------+----------+---------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------+----------+---------------------------------------------+
|dn1|BASESQL|insertintocompany(id,name)values(1,'hp')|
|dn2|BASESQL|insertintocompany(id,name)values(1,'hp')|
|dn3|BASESQL|insertintocompany(id,name)values(1,'hp')|
+-----------+----------+---------------------------------------------+
3rowsinset(0.00sec)
使用explainselect*fromcompany;命令也可以看到随机分发到3个节点的。
##测试hashmod分区
createtablehotnews(idbigintunsignednotnullprimarykey,titlevarchar(400),created_timedatetime)ENGINE=InnoDBDEFAULTCHARSET=utf8;
然后,我们写个脚本,批量插入些数据,看看情况:
foriin{1..1000};do
mysql-uop-p123456--port8066-h192.168.20.10testdb-e"insertintohotnews(id,title,created_time)values($i,'one',now());"
done
然后,到后端的3个分片上看下数据量,大致如下,还是比较均匀的:
(db1)>selectcount(*)fromdb1.hotnews;
+----------+
|count(*)|
+----------+
|333|
+----------+
1rowinset(0.00sec)
(db1)>selectcount(*)fromdb2.hotnews;
+----------+
|count(*)|
+----------+
|334|
+----------+
1rowinset(0.00sec)
(db1)>selectcount(*)fromdb3.hotnews;
+----------+
|count(*)|
+----------+
|333|
+----------+
1rowinset(0.00sec)
##hashStringmod分区
CREATETABLE`user_auth`(
`id`bigintunsignedNOTNULLAUTO_INCREMENTCOMMENT'主键id',
`open_id`varchar(100)NOTNULLDEFAULT''COMMENT'第三方授权id',
`union_id`varchar(100)NOTNULLDEFAULT''COMMENT'授权的关联id',
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='用户AUTH信息表';
####注意:实际生产环境的主键id需要由程序去保证唯一性(例如使用雪花算法)
(testdb)>insertintouser_auth(id,open_id,union_id)values(1,'331116828422393856','oy0IAj9mdPUr7bLMl879Jp37eV3Y');
(testdb)>insertintouser_auth(id,open_id,union_id)values(2,'341170994247204864','oy0IA3Yj9mdPUr7bLMl879Jp37eV');
(testdb)>insertintouser_auth(id,open_id,union_id)values(3,'330414325695332352','oy0IAj9mdPU3Yr7bLMl879Jp37eV');
(testdb)>insertintouser_auth(id,open_id,union_id)values(4,'328588424011591680','oy0IAj9mdPUr7bLMl8Jp37e79V');
(testdb)>insertintouser_auth(id,open_id,union_id)values(5,'330414325695332352','oy0IA3Yj9mdPUr7p37ebLMl879JV3Y');
(testdb)>insertintouser_auth(id,open_id,union_id)values(6,'341172222247211111','oy0IAj9bLMl879Jp37eV3YmdPUr7');
(testdb)>insertintouser_auth(id,open_id,union_id)values(7,'341173334247755464','Jp37eoy0IAj9mdPUr73YbLMl879V');
(testdb)>selectid,open_id,union_idfromuser_authorderbyidasc;
+----+--------------------+--------------------------------+
|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|
+----+--------------------+--------------------------------+
7rowsinset(0.00sec)
(testdb)>explainselectid,open_id,union_idfromuser_authwhereopen_id='341173334247755464';
+-----------+----------+--------------------------------------------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------+----------+--------------------------------------------------------------------------------+
|dn2|BASESQL|selectid,open_id,union_idfromuser_authwhereopen_id='341173334247755464'|
+-----------+----------+--------------------------------------------------------------------------------+
1rowinset(0.00sec)
(testdb)>explainselectid,open_id,union_idfromuser_authwhereopen_id='331116828422393856';
+-----------+----------+--------------------------------------------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------+----------+--------------------------------------------------------------------------------+
|dn1|BASESQL|selectid,open_id,union_idfromuser_authwhereopen_id='331116828422393856'|
+-----------+----------+--------------------------------------------------------------------------------+
1rowinset(0.00sec)
(testdb)>explainselectid,open_id,union_idfromuser_authwhereopen_id='328588424011591680';
+-----------+----------+--------------------------------------------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------+----------+--------------------------------------------------------------------------------+
|dn3|BASESQL|selectid,open_id,union_idfromuser_authwhereopen_id='328588424011591680'|
+-----------+----------+--------------------------------------------------------------------------------+
1rowinset(0.00sec)
############################################################################
上面就是几种常用的分区了, 另外还有种 date类型按时间分区的可能在日志表的场景下也常用些。
date类型分区的实验:
先去后端的db上创建物理的库:createdatabaseuserdb1;
createdatabaseuserdb2;
createdatabaseuserdb3;
createdatabaseuserdb4;
createdatabaseuserdb5;
createdatabaseuserdb6;
createdatabaseuserdb7;
createdatabaseuserdb8;
createdatabaseuserdb9;
createdatabaseuserdb10;
createdatabaseuserdb11;
createdatabaseuserdb12;
createdatabaseuserdb13;
修改后的 schema.xml 类似如下:<?xml version="1.0"?>
dble:schemaSYSTEM"schema.dtd">
selectuser()
然后,到 rule.xml中添加规则:
addData
partbymonth-user
yyyy-MM-dd
-01-01
30
0
(testdb)>createtableifnotexistsuser(addDatadate,dbnamevarchar(32),usernamevarchar(32),provincevarchar(16),ageint(3));
(testdb)>insertintouser(addData,dbname,username,age)values('-01-01',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-02-01',database(),'user1',12);
(testdb)>explaininsertintouser(addData,dbname,username,age)values('-03-01',database(),'user1',12);
+-----------+----------+--------------------------------------------------------------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------+----------+--------------------------------------------------------------------------------------------------+
|user_dn1|BASESQL|INSERTINTOuser(addData,dbname,username,age)VALUES('-03-01',DATABASE(),'user1',12)|
+-----------+----------+--------------------------------------------------------------------------------------------------+
(testdb)>insertintouser(addData,dbname,username,age)values('-03-01',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-04-01',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-04-11',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-04-21',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-04-25',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-04-30',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-05-01',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-05-03',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-05-05',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-06-21',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-07-30',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-01-01',database(),'user1',12);
(testdb)>insertintouser(addData,dbname,username,age)values('-06-01',database(),'user1',12);
ERROR1064(HY000):can'tfindanyvaliddatanode:user->ADDDATA->-06-01
因此,我们需要提前人工把分片加好并做好可用分区的监控,不然会造成无法写入数据的事故出现。
(testdb)>select*fromuserorderbyaddDataasc;
+------------+----------+----------+----------+------+
|addData|dbname|username|province|age|
+------------+----------+----------+----------+------+
|-01-01|userdb1|user1|NULL|12|
|-02-01|userdb1|user1|NULL|12|
|-03-01|userdb1|user1|NULL|12|
|-04-01|userdb4|user1|NULL|12|
|-04-11|userdb4|user1|NULL|12|
|-04-21|userdb4|user1|NULL|12|
|-04-25|userdb4|user1|NULL|12|
|-04-30|userdb4|user1|NULL|12|
|-05-01|userdb5|user1|NULL|12|
|-05-03|userdb5|user1|NULL|12|
|-05-05|userdb5|user1|NULL|12|
|-06-21|userdb6|user1|NULL|12|
|-07-30|userdb8|user1|NULL|12|
|-01-01|userdb13|user1|NULL|12|
+------------+----------+----------+----------+------+
14rowsinset(0.02sec)
查询测试:
(testdb)>explainselect*fromuserwhereaddDatabetween'-04-01'and'-04-30';
+-----------+----------+------------------------------------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------+----------+------------------------------------------------------------------------+
|user_dn4|BASESQL|select*fromuserwhereaddDatabetween'-04-01'and'-04-30'|
+-----------+----------+------------------------------------------------------------------------+
1rowinset(0.00sec)
(testdb)>select*fromuserwhereaddDatabetween'-04-01'and'-04-30';
+------------+---------+----------+----------+------+
|addData|dbname|username|province|age|
+------------+---------+----------+----------+------+
|-04-01|userdb4|user1|NULL|12|
|-04-11|userdb4|user1|NULL|12|
|-04-21|userdb4|user1|NULL|12|
|-04-25|userdb4|user1|NULL|12|
|-04-30|userdb4|user1|NULL|12|
+------------+---------+----------+----------+------+
5rowsinset(0.01sec)
(testdb)>explainselect*fromuserwhereaddDatabetween'-04-01'and'-05-30'orderbyaddDataasc;
+-----------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|user_dn4_0|BASESQL|select`user`.`addData`,`user`.`dbname`,`user`.`username`,`user`.`province`,`user`.`age`from`user`whereaddDataBETWEEN'-04-01'AND'-05-30'ORDERBY`user`.`addData`ASC|
|user_dn5_0|BASESQL|select`user`.`addData`,`user`.`dbname`,`user`.`username`,`user`.`province`,`user`.`age`from`user`whereaddDataBETWEEN'-04-01'AND'-05-30'ORDERBY`user`.`addData`ASC|
|merge_1|MERGE|user_dn4_0;user_dn5_0|
|shuffle_field_1|SHUFFLE_FIELD|merge_1|
+-----------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4rowsinset(0.00sec)
(testdb)>select*fromuserwhereaddDatabetween'-04-01'and'-05-30'orderbyaddDataasc;
+------------+---------+----------+----------+------+
|addData|dbname|username|province|age|
+------------+---------+----------+----------+------+
|-04-01|userdb4|user1|NULL|12|
|-04-11|userdb4|user1|NULL|12|
|-04-21|userdb4|user1|NULL|12|
|-04-25|userdb4|user1|NULL|12|
|-04-30|userdb4|user1|NULL|12|
|-05-01|userdb5|user1|NULL|12|
|-05-03|userdb5|user1|NULL|12|
|-05-05|userdb5|user1|NULL|12|
+------------+---------+----------+----------+------+
8rowsinset(0.01sec)
date类型的可用分区的监控(脚本的原理同样适用于其他类型的分区):
简单的做法就是定期执行一个explain的insert插入测试, 如果有ERROR关键字就告警出来一个简单的脚本如下:
#提前60天预警
DAYS=$(date-d60days+%F)
echo$DAYS
ifmysql-urw-prw123456--port8066-h192.168.20.10testdb2>/dev/null-e"explaininsertintouser(addData,dbname,username,age)values(\"$DAYS\",database(),'user1',12);";then
echo"当前可用分片数量处于安全状态"
else
echo"需要加新的分片了"
fi
date类型加新的分片的方法:1、修改schema.xml加上新的分片的配置信息,修改后大致这样:
dble:schemaSYSTEM"schema.dtd">
selectuser()
2、重载配置文件
reload@@config_all;
3、去后端创建对应的物理库
createdatabaseuserdb14;
.....这里省略其它的建库语句.......
createdatabaseuserdb23;
4、通过dble再次下发下建表命令
createtableifnotexistsuser(addDatadate,dbnamevarchar(32),usernamevarchar(32),provincevarchar(16),ageint(3));
5、插入数据测试
(testdb)>explaininsertintouser(addData,dbname,username,age)values('-11-01',database(),'user1',12);
+-----------+----------+--------------------------------------------------------------------------------------------------+
|DATA_NODE|TYPE|SQL/REF|
+-----------+----------+--------------------------------------------------------------------------------------------------+
|user_dn23|BASESQL|INSERTINTOuser(addData,dbname,username,age)VALUES('-11-01',DATABASE(),'user1',12)|
+-----------+----------+--------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)
(testdb)>explaininsertintouser(addData,dbname,username,age)values('-12-01',database(),'user1',12);
ERROR1064(HY000):can'tfindanyvaliddatanode:user->ADDDATA->-12-01
######################################################################################################
ER 表 (互联网场景下用多表JOIN的不多,因此ER分片规则不太常用到,但是需要大致的了解):
下面的内容大篇幅参考: /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关联,配置为ER分片
2.1:在schema.xml添加如下配置配置文件修改
在rule.xml里面设定分片规则:
id
hashmod3
3
1
然后, 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 | -08-31 23:05:36 |
| 4 | GZH004 | -08-31 23:06:57 |
| 3 | SHH001 | -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) | -08-31 23:06:17 |
| 4 | 4 | 1| test data of ORDER1(ID=4,GZH004) | -08-31 23:07:01 |
| 6 | 4 | 1| test data of ORDER1(ID=6,WUHAN006) | -08-31 23:07:23 |
| 3 | 3 | 1| test data of ORDER1(ID=3,SHH001) | -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 | -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) | -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 | -08-31 23:05:36 | 1 | 1 | 1| test data of ORDER1(ID=1,BJ001) | -08-31 23:06:17 |
+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
1 row in set (0.00 sec)
2.7 总结:当子表与父表的关联字段正好是父表的分片字段时,子表直接根据父表规则进行分片,在数据录入的时候子表直接放在父表的分片上面,在进行关联查询join的时候,走的是父表的路由。
【重要】其它的总结:
当子表与父表的关联字段不是父表的分片字段时,必须通过查找对应的父表记录来确认子表所在分片,如果找不到则会抛出错误,在join查询的时候,路由走的是所有分片节点!!!!
如果觉得《mysql 分库分表实战_DBLE分库分表实战》对你有帮助,请点赞、收藏,并留下你的观点哦!