mycat ER分片的场景详细分析学习

mycat高可用 ER分片的场景详细分析

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添加如下配置配置文件修改

<table name="ORDER1" dataNode="dn21,dn22" rule="mod-long"> 
 <childTable name="ORDER_DETAIL" primaryKey="ID" joinKey="ORDER_ID" parentKey="ID" /> 
</table>

在rule.xml里面设定分片规则：
    <tableRule name="mod-long">
         <rule>
              <columns>id</columns>
              <algorithm>mod-long</algorithm>
         </rule>
    </tableRule>
然后重启mycat或者重新加载mycat

2.2 先建表， ORDER 和 ORDER_DETAIL 表，有主外键关系
mysql> explain CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);
+-----------+-----------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                 |
+-----------+-----------------------------------------------------------------------------------------------------+
| dn1       | CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn2       | CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn3       | CREATE TABLE ORDER1(ID INT 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);
Query OK, 0 rows affected (0.35 sec)

mysql> explain 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));
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                                                                                                       |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1       | 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)) |
| dn2       | 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)) |
| dn3       | 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)) |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 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));
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)


查看mycat.log：
03/01 23:19:30.046  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,SHH001)',NOW())
03/01 23:19:30.047  DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:650) -found partion node (using parent partion rule directly) for child table to insert  dn1 sql :INSERT INTO ORDER_DETAIL (ID, ORDER_ID, ORD_STATUS, ADDRESS, CREATE_TIME)
VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001)', NOW())
03/01 23:19:30.047  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,SHH001)',NOW()), route={
   1 -> dn1{INSERT INTO ORDER_DETAIL (ID, ORDER_ID, ORD_STATUS, ADDRESS, CREATE_TIME)
VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001)', NOW())}
} rrs 
03/01 23:19:30.075  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=5, lastTime=1456845570046, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=62, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{INSERT INTO ORDER_DETAIL (ID, ORDER_ID, ORD_STATUS, ADDRESS, CREATE_TIME)
VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001)', NOW())}, respHandler=SingleNodeHandler [node=dn1{INSERT INTO ORDER_DETAIL (ID, ORDER_ID, ORD_STATUS, ADDRESS, CREATE_TIME)
VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001)', NOW())}, packetId=0], host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
03/01 23:19:30.076  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=5, lastTime=1456845570046, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=62, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
看到直接23:19:30.047时间点处有数据录入，23:19:30.047处有路由分配 route={dn1了，直接走父表的路由了

2.4 验证子表是否随父表分片，果然验证如下，大家看到订单详细表ORDER_DETAIL的数据的分片都是随ORDER_ID所在外键的父表的分片：
mysql -uroot -poracle --socket=/tmp/mysql.sock -e "select * from db1.ORDER1;select * from db1.ORDER_DETAIL";
+----+----------+---------------------+
| ID | SN       | CREATE_TIME         |
+----+----------+---------------------+
|  3 | SHH001   | 2016-03-01 23:19:20 |
|  6 | WUHAN006 | 2016-03-02 01:03:09 |
+----+----------+---------------------+
+----+----------+------------+----------------------------------+---------------------+
| ID | ORDER_ID | ORD_STATUS | ADDRESS                          | CREATE_TIME         |
+----+----------+------------+----------------------------------+---------------------+
|  3 |        3 | 1          | test data of ORDER1(ID=3,SHH001) | 2016-03-01 23:19:33 |
+----+----------+------------+----------------------------------+---------------------+

mysql -uroot -poracle --socket=/tmp/mysql.sock -e "select * from db2.ORDER1;select * from db2.ORDER_DETAIL";
+----+--------+---------------------+
| ID | SN     | CREATE_TIME         |
+----+--------+---------------------+
|  1 | BJ0001 | 2016-03-01 23:04:10 |
|  4 | GZH004 | 2016-03-01 23:35:01 |
+----+--------+---------------------+
+----+----------+------------+--------------------------------------+---------------------+
| ID | ORDER_ID | ORD_STATUS | ADDRESS                              | CREATE_TIME         |
+----+----------+------------+--------------------------------------+---------------------+
|  1 |        1 | 1          | test data  of ORDER1(ID=1,BJ001)     | 2016-03-01 23:04:48 |
|  4 |        4 | 1          | test data  of ORDER1(ID=4,GZH004)    | 2016-03-01 23:35:20 |
|  6 |        4 | 1          | test data  of ORDER1(ID=6,WUHAN006)  | 2016-03-02 01:03:46 |
+----+----------+------------+--------------------------------------+---------------------+

2.6：查询路由
mysql> 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 | SQL                                                                                                       |
+-----------+-----------------------------------------------------------------------------------------------------------+
| dn1       | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID |
| dn2       | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID |
| dn3       | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID |
+-----------+-----------------------------------------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> 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 | 2016-03-01 23:04:10 |  1 |        1 | 1          | test data  of ORDER1(ID=1,BJ001)  | 2016-03-01 23:04:48 |
+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+
1 row in set (0.02 sec)

mycat.log后台日志debug信息，看到走的route路由是dn1、dn2、dn3，所有的节点路由都走了：
03/02 22:07:37.183  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=5, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=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
03/02 22:07:37.184  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDBselect t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID
03/02 22:07:37.185  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID value:select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID, route={
   1 -> dn1{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
   2 -> dn2{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
   3 -> dn3{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
}
03/02 22:07:37.185  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=5, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=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, route={
   1 -> dn1{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
   2 -> dn2{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
   3 -> dn3{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}
} rrs 
03/02 22:07:37.185  DEBUG [$_NIOREACTOR-2-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID
03/02 22:07:37.185  DEBUG [$_NIOREACTOR-2-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/02 22:07:37.186  DEBUG [$_NIOREACTOR-2-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/02 22:07:37.186   INFO [$_NIOREACTOR-2-RW] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for mysql1 of schema db2
03/02 22:07:37.186  DEBUG [$_NIOREACTOR-2-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/02 22:07:37.186   INFO [$_NIOREACTOR-2-RW] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for mysql1 of schema db3
03/02 22:07:37.189  DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=11, lastTime=1456927657177, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@753ec5b2, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/02 22:07:37.192  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=11, lastTime=1456927657177, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@753ec5b2, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

2.7 总结：当子表与父表的关联字段正好是父表的分片字段时，子表直接根据父表规则进行分片，在数据录入的时候子表直接放在父表的分片上面，在进行关联查询join的时候，走的是父表的路由。

3：父表的分片字段为其他字段，以PROVINCE字段分片，字表的分片字段与主表ID关联，配置为ＥＲ分片

3.1：各种配置

在schema.xml里面添加表配置
<table name="ORDER2" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="province-str"> 
 <childTable name="ORDER_DETAIL2" primaryKey="ID" joinKey="ORDER_ID" parentKey="ID" /> 
</table>

在rule.xml里面设定分片规则：
        <tableRule name="province-str">
                <rule>
                       <columns>PROVINCE</columns>
                       <algorithm>province-str-split</algorithm>
                </rule>
         </tableRule>
        <function name="province-str-split" class="org.opencloudb.route.function.PartitionByFileMap">
                <property name="type">1</property>
                <property name="mapFile">partition-hash-str-pro.txt</property>
                <property name="defaultNode">0</property>
        </function>

新添加partition-hash-str-pro.txt：
more partition-hash-str-pro.txt
beijing=0
shanghai=1
tianjing=2
然后重启mycat或者重新加载mycat

3.2 建表
mysql>  explain CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
+-----------+--------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                      |
+-----------+--------------------------------------------------------------------------------------------------------------------------+
| dn1       | CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn2       | CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn3       | CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
+-----------+--------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> explain CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID));
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                                                                                                          |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1       | CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID)) |
| dn2       | CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID)) |
| dn3       | CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID)) |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.03 sec)
 
mysql> CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
Query OK, 0 rows affected (0.38 sec)

mysql> CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID));
Query OK, 0 rows affected (0.35 sec)

3.2:录入数据,涉及到外键关系
INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW());
INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data  of ORDER1(ID=1,2BJ0001) ',NOW());

INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW());
INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW());

INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW());
INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data  of ORDER1(ID=4,2GZH004) ',NOW());

INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW());
INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
     
3.3：数据验证
mysql -uroot -poracle --socket=/tmp/mysql.sock -e "select * from db1.ORDER2;select * from db1.ORDER_DETAIL2";
+----+----------+---------+---------------------+
| ID | PROVINCE | SN      | CREATE_TIME         |
+----+----------+---------+---------------------+
|  1 | beijing  | 2BJ0001 | 2016-03-03 18:05:57 |
|  4 | beijing  | 2GZH004 | 2016-03-03 18:10:18 |
+----+----------+---------+---------------------+
+----+----------+------------+---------------------------------------+---------------------+
| ID | ORDER_ID | ORD_STATUS | ADDRESS                               | CREATE_TIME         |
+----+----------+------------+---------------------------------------+---------------------+
|  1 |        1 | 1          | test data  of ORDER1(ID=1,2BJ0001)    | 2016-03-03 18:06:07 |
|  4 |        4 | 1          | test data  of ORDER1(ID=4,2GZH004)    | 2016-03-03 18:10:18 |
|  6 |        4 | 1          | test data  of ORDER1(ID=6,2WUHAN006)  | 2016-03-03 18:10:30 |
+----+----------+------------+---------------------------------------+---------------------+

查看mycat.log：
03/03 18:51:05.107  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=8, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW())
03/03 18:51:05.108  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=8, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW()), route={
   1 -> dn2{INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW())}
} rrs 
03/03 18:51:05.136  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=12, lastTime=1457002265108, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=68, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW())}, respHandler=SingleNodeHandler [node=dn2{INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW())}, packetId=0], host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
03/03 18:51:05.136  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=12, lastTime=1457002265108, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=68, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

03/03 18:51:06.232  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=8, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW())
03/03 18:51:06.233  DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=3
03/03 18:51:06.234  DEBUG [BusinessExecutor4] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=3
03/03 18:51:06.234  DEBUG [BusinessExecutor4] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=3
03/03 18:51:06.234  DEBUG [BusinessExecutor4] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn1
03/03 18:51:06.234  DEBUG [BusinessExecutor4] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 18:51:06.235  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1457002266214, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 18:51:06.436  DEBUG [BusinessExecutor4] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn2
03/03 18:51:06.436  DEBUG [BusinessExecutor4] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 18:51:06.436  DEBUG [BusinessExecutor4] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands schema change:true con:MySQLConnection [id=11, lastTime=1457002266436, user=root, schema=db2, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChildTableHandler@2c2f4d91, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 18:51:06.458  DEBUG [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:154) -received rowResponse response,3 from  MySQLConnection [id=11, lastTime=1457002266457, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChildTableHandler@2c2f4d91, host=192.168.10.22, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@558d8140, writeQueue=0, modifiedSQLExecuted=false]
03/03 18:51:06.458  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1457002266457, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 18:51:06.660  DEBUG [BusinessExecutor0] (RouterUtil.java:1213) -found partion node for child table to insert dn2 sql :INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW())
03/03 18:51:06.660  DEBUG [BusinessExecutor0] (NonBlockingSession.java:113) -ServerConnection [id=8, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW()), route={
   1 -> dn2{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW())}
} rrs 
03/03 18:51:06.699  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=12, lastTime=1457002266645, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=68, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW())}, respHandler=SingleNodeHandler [node=dn2{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data  of ORDER1(ID=3,2SHH001) ',NOW())}, packetId=0], host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
03/03 18:51:06.699  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=12, lastTime=1457002266645, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=68, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
可以看到在18:51:06.233处，看到find root parent's node sql select order2.id from order2 where  order2.id=3这样的日志记录，
就是通过外键锁涉及的父表的关联字段id去找所在分片记录，然后在18:51:06.660看到已经路由出来了分片地址route={dn2。

3.5：如果录入不存在的父表数据的时候路由情况
数据录入：
mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW());
ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())

查看mycat.log：       
03/03 19:08:13.550  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=8, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())
03/03 19:08:13.551  DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where  order2.id=9
03/03 19:08:13.552  DEBUG [BusinessExecutor5] (EnchachePool.java:76) -ER_SQL2PARENTID  miss cache ,key:TESTDB:select order2.id from order2 where  order2.id=9
03/03 19:08:13.552  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where  order2.id=9
03/03 19:08:13.552  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn1
03/03 19:08:13.552  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 19:08:13.553  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1457003293546, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:08:13.754  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn2
03/03 19:08:13.754  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 19:08:13.755  DEBUG [BusinessExecutor5] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands schema change:true con:MySQLConnection [id=11, lastTime=1457003293754, user=root, schema=db2, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChildTableHandler@375ce70f, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:08:13.756  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1457003293746, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:08:13.957  DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn3
03/03 19:08:13.957  DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 19:08:13.957  DEBUG [BusinessExecutor5] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands schema change:true con:MySQLConnection [id=11, lastTime=1457003293957, user=root, schema=db3, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChildTableHandler@375ce70f, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:08:13.958  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1457003293946, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:08:14.251   WARN [BusinessExecutor6] (RouterUtil.java:1206) -org.opencloudb.server.NonBlockingSession@48a130d1INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW()) err:can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data  of ORDER1(ID=6,2WUHAN006) ',NOW())
03/03 19:08:15.957  DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=11, lastTime=1457003295957, user=root, schema=db1, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
看到，遍历了所有节点dn1、dn2、dn3之后，select order2.id from order2 where order2.id=9执行后没有找到记录，就报错了 err:can't find (root) parent sharding node for sql


3.6：join关联查询涉及到的路由，是全节点遍历查询
mysql> explain select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6;
+-----------+--------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                  |
+-----------+--------------------------------------------------------------------------------------+
| dn1       | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |
| dn2       | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |
| dn3       | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |
+-----------+--------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

03/03 19:18:56.138  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=19, lastTime=1457003936118, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=73, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:19:02.604  DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=8, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6
03/03 19:19:02.604  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6
03/03 19:19:02.605  DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 value:select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={
   1 -> dn1{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
   2 -> dn2{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
   3 -> dn3{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
}
03/03 19:19:02.605  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=8, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={
   1 -> dn1{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
   2 -> dn2{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
   3 -> dn3{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}
} rrs 
03/03 19:19:02.605  DEBUG [$_NIOREACTOR-1-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6
03/03 19:19:02.605  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 19:19:02.606  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 19:19:02.606   INFO [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for mysql1 of schema db2
03/03 19:19:02.606  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
03/03 19:19:02.606   INFO [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for mysql1 of schema db3
03/03 19:19:02.607  DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=11, lastTime=1457003942580, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@77d0933e, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:19:02.630  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=11, lastTime=1457003942580, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@77d0933e, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/03 19:19:02.630  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1457003942580, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=74, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

总结：当子表与父表的关联字段不是父表的分片字段时，必须通过查找对应的父表记录来确认子表所在分片，如果找不到则会抛出错误，在join查询的时候，路由走的是所有分片节点。