(7)Mycat 的分片 “join”

Orders 订单表已经进行分表操作了，和它关联的 orders_detail 订单详情表如何进行 join 查询。

我们要对 orders_detail 也要进行分片操作。Join 的原理如下图：

 

1.修改 Mycat 的配置文件 /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
		<table name="customer" dataNode="dn2"></table>
		<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
			<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
		</table>
	</schema>

	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />
	
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="hostM1" url="172.16.94.131:3306" user="root"
				   password="Zan_123456">
		</writeHost>
		
	</dataHost>
	<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
       <heartbeat>select user()</heartbeat>       
	   <writeHost host="hostM2" url="172.16.94.133:3306" user="root"
				   password="Zan_123456">
		</writeHost>
	</dataHost>
</mycat:schema>

 

2.在 dn2 创建 orders_detail 表

#订单详细表 rows:600万
CREATE TABLE orders_detail(
 id INT AUTO_INCREMENT,
 detail VARCHAR(2000),
 order_id INT,
 PRIMARY KEY(id)
);

 

3.重启 Mycat，让配置生效

 

[root@shuidi01 bin]# ./mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
[root@shuidi01 bin]# ./mycat start
Starting Mycat-server...

 

4.访问 Mycat 向 orders_detail 表插入数据

INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

 

5.在mycat、dn1、dn2中运行两个表join语句

Select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;