1500字范文 > mysql 分库分表实战_DBLE分库分表实战

mysql 分库分表实战_DBLE分库分表实战

时间：2023-08-25 18:26:52

环境： 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 修改后的内容如下：

rangeLong2

hashmod3

open_id

hashStringmod3

autopartition-long_t.txt

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;

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

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

|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

(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;

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

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

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

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';

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

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

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

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;

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

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

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

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"需要加新的分片了"

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关联，配置为ＥＲ分片

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

在rule.xml里面设定分片规则：

hashmod3

然后， 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 ;

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

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

| 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;

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

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

| 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 ;

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

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

| 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查询的时候，路由走的是所有分片节点！！！！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。