mysql er分片_mycat ER分片的场景详细分析学习

最新推荐文章于 2023-07-25 14:44:45 发布

Jade QZ

最新推荐文章于 2023-07-25 14:44:45 发布

阅读量161

点赞数

本文链接：https://blog.csdn.net/weixin_32392583/article/details/113907054

版权

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

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

mod-long

然后重启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 |

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

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

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

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

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

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

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

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

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

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

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

writeQueue=0, modifiedSQLExecuted=false]

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

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

3.1：各种配置

在schema.xml里面添加表配置

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

PROVINCE

province-str-split

partition-hash-str-pro.txt

新添加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 |

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

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

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

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

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,

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

Jade QZ

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql er分片_mycat ER分片的场景详细分析学习

mycat高可用 ER分片的场景详细分析1：ER分片关系简介有一类业务，例如订单(ORDER)跟订单明细表(ORDER_DETAIL),明细表会依赖二订单，就是该会存在表的主从关系，这类似业务的切分可以抽象出合适的切分规则，比如根据用户ID切分,其它相关的表都依赖于用户ID，再或者根据订单ID进行切分，总之部分业务总会可以抽象出父子关系的表。这类表适用于ER分片表，子表的记录与所关联的父表记录存放...
复制链接

扫一扫