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里面设定分片规则
id mod-long 然后重启mycat或者重新加载mycat
本blog源地址：blog.csdn.net/mchdba/article/details/50655304，不经过原作者mchdba(黄杉)允许，谢绝转载
2.2 先建表， ORDER 和 ORDER_DETAIL 表，有主外键关系
2.2 先建表， ORDER 和 ORDER_DETAIL 表，有主外键关系 CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME); 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_ORDID11 FOREIGN KEY (ORDER_ID) REFERENCES ORDER1 (ID)); mysql> CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME); Query OK, 0 rows affected (0.06 sec) mysql> 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.02 sec) mysql> 3.3 录入数据：
3.3 录入数据： mysql> mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW()); ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME) VALUES ('BJ0001', NOW()) mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW()); ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME) VALUES ('BJ0001', NOW()) mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW()); Query OK, 1 row affected (0.03 sec) mysql> 录入数据，一组组录入，涉及到外键关系：
第一组北京的订单
mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW()); Query OK, 1 row affected (0.03 sec) mysql> 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()); Query OK, 1 row affected (0.00 sec) mysql> 第二组上海的订单：
mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW()); Query OK, 1 row affected (0.01 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()); mysql> 第三组广州的订单：
mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(4,'GZH004',NOW()); Query OK, 1 row affected (0.01 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()); mysql> 第四组 武汉的订单，这里故意将order_id设置成4，看看效果，是否随id为4的广州的那组分片：
mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(5,'WUHAN005',NOW()); ERROR 1064 (HY000): Index: 2, Size: 2 mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(6,'WUHAN006',NOW()); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data of ORDER1(ID=6,WUHAN005) ',NOW());2.4 验证子表是否随父表分片，果然验证如下，大家看到订单详细表ORDER_DETAIL的数据的分片都是随ORDER_ID所在外键的父表的分片：
2.4 验证子表是否随父表分片，果然验证如下，大家看到订单详细表ORDER_DETAIL的数据的分片都是随ORDER_ID所在外键的父表的分片： [root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3317 --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL"; Enter password: +--------+ | @@port | +--------+ | 3327 | +--------+ +----+--------+---------------------+ | ID | SN | CREATE_TIME | +----+--------+---------------------+ | 1 | BJ0001 | 2016-02-11 22:54:26 | | 4 | GZH004 | 2016-02-11 22:57:49 | +----+--------+---------------------+ +----+----------+------------+--------------------------------------+---------------------+ | ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME | +----+----------+------------+--------------------------------------+---------------------+ | 1 | 1 | 1 | test data of ORDER1(ID=1,BJ001) | 2016-02-11 23:07:05 | | 4 | 4 | 1 | test data of ORDER1(ID=4,GZH004) | 2016-02-11 23:09:48 | | 6 | 4 | 1 | test data of ORDER1(ID=6,WUHAN005) | 2016-02-11 23:10:18 | +----+----------+------------+--------------------------------------+---------------------+ [root@test_1_11logs]# [root@test_1_11logs]# [root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3327 --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL"; Enter password: +--------+ | @@port | +--------+ | 3317 | +--------+ +----+----------+---------------------+ | ID | SN | CREATE_TIME | +----+----------+---------------------+ | 3 | SHH001 | 2016-02-11 22:57:26 | | 6 | WUHAN006 | 2016-02-11 23:04:39 | +----+----------+---------------------+ +----+----------+------------+------------------------------------+---------------------+ | ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME | +----+----------+------------+------------------------------------+---------------------+ | 3 | 3 | 1 | test data of ORDER1(ID=3,SHH001) | 2016-02-11 23:09:02 | +----+----------+------------+------------------------------------+---------------------+ [root@test_1_11logs]# 2.5 有日志为列，看到SHH001这个订单的数据录入都在端口为3317的分片mysql实例上，如下验证：
2.5 有日志为列，看到SHH001这个订单的数据录入都在端口为3317的分片mysql实例上，如下验证：[root@test_1_11logs]# more mycat.log|grep SHH001 02/11 22:56:58.477 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) 02/11 22:56:58.478 WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2 02/11 22:57:22.476 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) 02/11 22:57:22.477 WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2 02/11 22:57:26.988 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW()) 02/11 22:57:26.989 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW()), route={ 1 -> dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())} 02/11 22:57:26.999 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=4, lastTime=1455202646984, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=91, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true] 02/11 23:09:02.690 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,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()) VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001) ', NOW()) 02/11 23:09:02.692 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,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={ VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001) ', NOW())} VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001) ', NOW())}, respHandler=SingleNodeHandler [node=dn21{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=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true] [root@test_1_11logs]# 【】看到直接22:57:26.988时间点处有数据录入，22:57:26.989处有路由分配 route={dn21了，直接走父表的路由了**
2.6 查询路由
2.6 查询路由mycat命令行里面执行：
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 | +-----------+-----------------------------------------------------------------------------------------------------------+ | dn21 | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID | | dn22 | 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 rows in set (0.00 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-02-12 17:51:24 | 1 | 1 | 1 | test data of ORDER1(ID=1,BJ001) | 2016-02-12 17:51:30 | +----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+ 1 row in set (0.01 sec) mysql> 【】mycat.log后台日志debug信息，看到走的route路由是dn21和dn22，所有的节点路由都走了：
02/12 18:22:51.444 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=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 02/12 18:22:51.444 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key: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 02/12 18:22:51.448 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key: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 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 -> dn21{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 -> dn22{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID} } 02/12 18:23:01.214 DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=18, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=18, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 02/12 18:23:01.215 DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=22, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=21, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
图片1.jpg"ORDER_ID" name="ORDER_DETAIL2" parentkey="ID" primarykey="ID">
在rule.xml里面设定分片规则
PROVINCE province-str-split 1 partition-hash-str-pro.txt 0 新添加partition-hash-str-pro.txt
[root@test_1_11conf]# more partition-hash-str-pro.txt beijing=0 shanghai=1 tianjing=2 [root@test_1_11conf]# 然后重启mycat或者重新加载mycat
3.2 建表
3.2 建表 CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME); 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)); mysql> CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME); Query OK, 0 rows affected (0.05 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.02 sec) mysql> 3.2 录入数据，一组组录入，涉及到外键关系：
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()); mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()); Query OK, 1 row affected (0.21 sec) mysql> 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()); mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW()); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,2SHH001) ',NOW()); Query OK, 1 row affected (0.41 sec) mysql> 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()); mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data of ORDER1(ID=4,2GZH004) ',NOW()); Query OK, 1 row affected (0.21 sec) mysql> 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()); mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW()); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW()); Query OK, 1 row affected (0.22 sec) mysql> 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()) mysql> 3.3 数据验证
3.3 数据验证 [root@test_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2"; Enter password: +--------+ | @@port | +--------+ | 3327 | +--------+ +----+----------+-----------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | +----+----------+-----------+---------------------+ | 3 | shanghai | 2SHH001 | 2016-02-12 17:24:00 | | 6 | shanghai | 2WUHAN006 | 2016-02-12 17:24:14 | +----+----------+-----------+---------------------+ +----+----------+------------+-------------------------------------+---------------------+ | ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME | +----+----------+------------+-------------------------------------+---------------------+ | 3 | 3 | 1 | test data of ORDER1(ID=3,2SHH001) | 2016-02-12 17:24:04 | +----+----------+------------+-------------------------------------+---------------------+ [root@test_1_11~]# [root@test_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2"; Enter password: +--------+ | @@port | +--------+ | 3317 | +--------+ +----+----------+---------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | +----+----------+---------+---------------------+ | 1 | beijing | 2BJ0001 | 2016-02-12 17:23:46 | | 4 | beijing | 2GZH004 | 2016-02-12 17:24:07 | +----+----------+---------+---------------------+ +----+----------+------------+---------------------------------------+---------------------+ | ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME | +----+----------+------------+---------------------------------------+---------------------+ | 1 | 1 | 1 | test data of ORDER1(ID=1,2BJ0001) | 2016-02-12 17:23:50 | | 4 | 4 | 1 | test data of ORDER1(ID=4,2GZH004) | 2016-02-12 17:24:11 | | 6 | 4 | 1 | test data of ORDER1(ID=6,2WUHAN006) | 2016-02-12 17:24:17 | +----+----------+------------+---------------------------------------+---------------------+ [root@test_1_11~]# 3.4 mycat.log中debug日志显示，通过关键字2BJ0001来检索父子数据记录的录入情况
3.4 mycat.log中debug日志显示，通过关键字2BJ0001来检索父子数据记录的录入情况vim mycat.log
02/12 17:23:50.530 DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=1 02/12 17:23:50.531 DEBUG [BusinessExecutor3] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=1 02/12 17:23:50.531 DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=1 02/12 17:23:50.531 DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21 02/12 17:23:50.531 DEBUG [BusinessExecutor3] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1 02/12 17:23:50.532 DEBUG [$_NIOREACTOR-0-RW] (FetchStoreNodeOfChildTableHandler.java:154) -received rowResponse response,1 from MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=dn21, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChildTableHandler@dde0e41, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 02/12 17:23:50.533 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 02/12 17:23:50.732 DEBUG [BusinessExecutor4] (RouterUtil.java:1213) -found partion node for child table to insert dn21 sql :INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()) 02/12 17:23:50.733 DEBUG [BusinessExecutor4] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()), route={ 1 -> dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())} } rrs 02/12 17:23:50.735 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true] 02/12 17:23:50.735 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]【】可以看到在17:23:50.532处，看到find root parent’s node sql select order2.id from order2 where order2.id=1这样的日志记录，就是通过外键锁涉及的父表的关联字段id去找所在分片记录，然后在17:23:50.732看到已经路由出来了分片地址route={dn21。
3.5 如果录入不存在的父表数据的时候路由情况
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()) mysql> 查看日志mycat.log情况：
02/12 17:54:43.351 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,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()) 02/12 17:54:43.352 DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=9 02/12 17:54:43.353 DEBUG [BusinessExecutor7] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=9 02/12 17:54:43.353 DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=9 02/12 17:54:43.353 DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21 02/12 17:54:43.353 DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1 02/12 17:54:43.354 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455270883346, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 02/12 17:54:43.554 DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22 02/12 17:54:43.554 DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2 02/12 17:54:43.554 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=15, lastTime=1455270883547, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 02/12 17:54:43.805 WARN [BusinessExecutor3] (RouterUtil.java:1206) -org.opencloudb.server.NonBlockingSession@634984c8INSERT 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())看到，遍历了所有节点dn21和dn22之后，select order2.id from order2 where order2.id=9执行后没有找到记录，就报错了 can’t find (root) parent sharding node for sql:
3.6 join关联查询涉及到的路由，是全节点遍历查询
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 | +-----------+--------------------------------------------------------------------------------------+ | dn21 | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 | | dn22 | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 | +-----------+--------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6; +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+ | ID | PROVINCE | SN | CREATE_TIME | ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME | +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+ | 1 | beijing | 2BJ0001 | 2016-02-12 17:23:46 | 6 | 4 | 1 | test data of ORDER1(ID=6,2WUHAN006) | 2016-02-12 17:24:17 | | 4 | beijing | 2GZH004 | 2016-02-12 17:24:07 | 6 | 4 | 1 | test data of ORDER1(ID=6,2WUHAN006) | 2016-02-12 17:24:17 | +----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+ 2 rows in set (0.01 sec) mysql> 02/12 18:02:23.370 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 02/12 18:02:23.371 DEBUG [$_NIOREACTOR-2-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 02/12 18:02:23.373 DEBUG [$_NIOREACTOR-2-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 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6} 2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6} } 02/12 18:02:23.373 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,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 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6} 2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6} } rrs 3.7 【】总结：当子表与父表的关联字段不是父表的分片字段时，必须通过查找对应的父表记录来确认子表所在分片，如果找不到则会抛出错误，在join查询的时候，路由走的是所有分片节点。
3.7 【】总结：当子表与父表的关联字段不是父表的分片字段时，必须通过查找对应的父表记录来确认子表所在分片，如果找不到则会抛出错误，在join查询的时候，路由走的是所有分片节点。4，报错处理总结
4，报错处理总结4.1 报错1：
4.1 报错1：mysql> INSERT INTO ORDER2(ID,SN,CREATE_TIME) VALUES(1,'2BJ0001',NOW()); ERROR 1064 (HY000): For input string: "2BJ0001"mysql> 报错是因为分片字段是str字符串，所以需要修改分片规则1中type从0改成1，0是数字型分片，1是字符串分片。
4.2 报错2：
4.2 报错2： mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',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 (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()) mysql> 解决方案1：此类ER表癿揑入操作丌能做为一个亊务迕行数捤提交，如果父子表在一个亊务中迕行提交，显然在亊务没有提交前子表是无法查刡父表癿数捤癿，因此就无法确定sharding node。如果是ER关系癿表在揑入数捤时丌能在同一个亊务中提交数捤，叧能分开提交。4.3 报错3
4.3 报错3解决方案2：上面的方案2个事务搞不定，所以查看后台mycat.log，日志
02/12 14:32:09.378 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()) 02/12 14:32:09.506 DEBUG [$_NIOREACTOR-3-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=1 02/12 14:32:09.513 DEBUG [BusinessExecutor5] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=1 02/12 14:32:09.513 DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=1 02/12 14:32:09.514 DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21 02/12 14:32:09.514 DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1 02/12 14:32:09.535 WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist 02/12 14:32:09.535 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=3, lastTime=1455258729497, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=290, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 02/12 14:32:09.715 DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22 02/12 14:32:09.715 DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2 02/12 14:32:09.716 WARN [$_NIOREACTOR-1-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist后台执行sql确实找不到
mysql> select order2.id from order2 where order2.id=1; ERROR 1105 (HY000): Table 'db3.order2' doesn't exist mysql> 原因是2个datanode是互为主从的关系，dn21和dn22是mm结构，然后停止mm结构，2个都是单独的mysql，然后重建ORDER2和ORDER_DETAIL2表，再执行insert操作，ok，问题解决了。
4.4 报错4
4.4 报错4解决方案3：表名字大小写的问题导致
mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',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 (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()) mysql> 后台报错：
02/12 17:04:24.583 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=5, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()) 02/12 17:04:24.584 DEBUG [$_NIOREACTOR-0-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=1 02/12 17:04:24.584 DEBUG [BusinessExecutor6] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=1 02/12 17:04:24.585 DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=1 02/12 17:04:24.585 DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21 02/12 17:04:24.585 DEBUG [BusinessExecutor6] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1 02/12 17:04:24.585 WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist mysql> select * from db3.order2; ERROR 1146 (42S02): Table 'db3.order2' doesn't exist mysql> select * from db3.ORDER2; Empty set (0.01 sec) mysql> mysql> [mysqld] lower_case_table_names = 1然后重启mysql已经mycat服务，再次进行check。