1.修改schema.xml
--my_test4表和my_test4_child是父子关系,保存在1号到3号库中。
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100">
<table name="my_test2" dataNode="dnmycatdb$1-9" rule="sharding-by-intfile-mycatdb-my_test2"></table>
<table name="my_test3" primaryKey="jobid" type="global" dataNode="dnmycatdb$1-9" ></table>
<table name="my_test4" dataNode="dnmycatdb$1-3" rule="mod-long" >
<childTable name="my_test4_child" primaryKey="orderdetail_id" joinKey="order_id" parentKey="order_id"/>
</table>
</schema>
2.规则设置
[root@mysql1 conf]# cat rule.xml
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="sharding-by-intfile-mycatdb-my_test2">
<rule>
<columns>region</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>order_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int-mycatdb-my_test2.txt</property>
<property name="type">1</property>
<property name="defaultNode">0</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
</mycat:rule>
--总共3个分片 ,分布在1,2,3号节点。
3.准备数据
[root@mysql1 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
CREATE TABLE my_test4(
order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
number VARCHAR(64),
createtime DATETIME);
CREATE TABLE my_test4_child(
orderdetail_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
order_status CHAR(1),
addrs VARCHAR(128),
createtime DATETIME,
CONSTRAINT fk_iorder01 FOREIGN KEY (order_id) REFERENCES my_test4(order_id));
4.录入数据。
录入9组数据,一组组录入,涉及到外键关系。
INSERT INTO my_test4(order_id,number,createtime)VALUES(1,'0000001',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (1,1,'1','0000001-1',NOW());
INSERT INTO my_test4(order_id,number,createtime)VALUES(2,'0000002',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (2,2,'1','0000002-1',NOW());
INSERT INTO my_test4(order_id,number,createtime)VALUES(3,'0000003',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (3,3,'1','0000003-1',NOW());
INSERT INTO my_test4(order_id,number,createtime)VALUES(4,'0000004',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (4,4,'1','0000004-1',NOW());
INSERT INTO my_test4(order_id,number,createtime)VALUES(5,'0000005',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (5,5,'1','0000005-1',NOW());
INSERT INTO my_test4(order_id,number,createtime)VALUES(6,'0000006',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (6,6,'1','0000006-1',NOW());
INSERT INTO my_test4(order_id,number,createtime) VALUES(7,'0000007',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (7,7,'1','0000007-1',NOW());
INSERT INTO my_test4(order_id,number,createtime) VALUES(8,'0000008',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (8,8,'1','0000008-1',NOW());
INSERT INTO my_test4(order_id,number,createtime) VALUES(9,'0000009',NOW());
INSERT INTO my_test4_child(orderdetail_id,order_id,order_status,addrs,createtime)
VALUES (9,9,'1','0000009-1',NOW());
commit;
"sspu@192.168.1.11">show tables;
+-------------------+
| Tables in mycatdb |
+-------------------+
| my_test2 |
| my_test3 |
| my_test4 |
| my_test4_child |
+-------------------+
4 rows in set (0.01 sec)
"sspu@192.168.1.11">select * from my_test4;
+----------+---------+---------------------+
| order_id | number | createtime |
+----------+---------+---------------------+
| 1 | 0000001 | 2024-08-21 14:16:06 |
| 4 | 0000004 | 2024-08-21 14:16:06 |
| 7 | 0000007 | 2024-08-21 14:16:06 |
| 3 | 0000003 | 2024-08-21 14:16:06 |
| 6 | 0000006 | 2024-08-21 14:16:06 |
| 9 | 0000009 | 2024-08-21 14:16:06 |
| 2 | 0000002 | 2024-08-21 14:16:06 |
| 5 | 0000005 | 2024-08-21 14:16:06 |
| 8 | 0000008 | 2024-08-21 14:16:06 |
+----------+---------+---------------------+
9 rows in set (0.07 sec)
"sspu@192.168.1.11">select * from my_test4_child;
+----------------+----------+--------------+-----------+---------------------+
| orderdetail_id | order_id | order_status | addrs | createtime |
+----------------+----------+--------------+-----------+---------------------+
| 1 | 1 | 1 | 0000001-1 | 2024-08-21 14:16:06 |
| 4 | 4 | 1 | 0000004-1 | 2024-08-21 14:16:06 |
| 7 | 7 | 1 | 0000007-1 | 2024-08-21 14:16:06 |
| 3 | 3 | 1 | 0000003-1 | 2024-08-21 14:16:06 |
| 6 | 6 | 1 | 0000006-1 | 2024-08-21 14:16:06 |
| 9 | 9 | 1 | 0000009-1 | 2024-08-21 14:16:06 |
| 2 | 2 | 1 | 0000002-1 | 2024-08-21 14:16:06 |
| 5 | 5 | 1 | 0000005-1 | 2024-08-21 14:16:06 |
| 8 | 8 | 1 | 0000008-1 | 2024-08-21 14:16:06 |
+----------------+----------+--------------+-----------+---------------------+
9 rows in set (0.01 sec)
5.查询
--查看执行计划。
"sspu@192.168.1.11">explain select * from my_test4 t1,my_test4_child t2 where t2.order_status='1'
-> and t2.orderdetail_id=2 and t1.order_id=t2.order_id;
+------------+---------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+------------+---------------------------------------------------------------------------------------------------------------------------+
| dnmycatdb1 | select * from my_test4 t1,my_test4_child t2 where t2.order_status='1' and t2.orderdetail_id=2 and t1.order_id=t2.order_id |
| dnmycatdb2 | select * from my_test4 t1,my_test4_child t2 where t2.order_status='1' and t2.orderdetail_id=2 and t1.order_id=t2.order_id |
| dnmycatdb3 | select * from my_test4 t1,my_test4_child t2 where t2.order_status='1' and t2.orderdetail_id=2 and t1.order_id=t2.order_id |
+------------+---------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.04 sec)
数据在 dnmycatdb1,dnmycatdb2,dnmycatdb3 三个节点下。其他节点无数据。
可以根据需求,随意设计表的数据应该保存在哪个节点。
6.检查其他节点
[root@mysql2 data]# ll sspudb4
total 220
-rw-r----- 1 mysql mysql 59 Aug 21 10:26 db.opt
-rw-r----- 1 mysql mysql 8620 Aug 21 11:27 my_test2.frm
-rw-r----- 1 mysql mysql 98304 Aug 21 11:48 my_test2.ibd
-rw-r----- 1 mysql mysql 8710 Aug 21 13:48 my_test3.frm
-rw-r----- 1 mysql mysql 98304 Aug 21 13:52 my_test3.ibd
[root@mysql2 data]# ll sspudb5
total 220
-rw-r----- 1 mysql mysql 59 Aug 21 10:26 db.opt
-rw-r----- 1 mysql mysql 8620 Aug 21 11:27 my_test2.frm
-rw-r----- 1 mysql mysql 98304 Aug 21 11:48 my_test2.ibd
-rw-r----- 1 mysql mysql 8710 Aug 21 13:48 my_test3.frm
-rw-r----- 1 mysql mysql 98304 Aug 21 13:52 my_test3.ibd
[root@mysql2 data]# ll sspudb6
total 220
-rw-r----- 1 mysql mysql 59 Aug 21 10:26 db.opt
-rw-r----- 1 mysql mysql 8620 Aug 21 11:27 my_test2.frm
-rw-r----- 1 mysql mysql 98304 Aug 21 11:48 my_test2.ibd
-rw-r----- 1 mysql mysql 8710 Aug 21 13:48 my_test3.frm
-rw-r----- 1 mysql mysql 98304 Aug 21 13:52 my_test3.ibd
[root@mysql2 data]#
并无 my_test4 表和 my_test4_child;
7.总结
主表和子表均在指定的节点上均匀分布。其他未指定的节点无数据。