【mysql】er关系分片

戒掉贪嗔痴(薛双奇)

于 2024-08-21 14:28:46 发布

阅读量62

点赞数 1

分类专栏：数据库运维-MySQL 文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/141392820

版权

数据库运维-MySQL 专栏收录该内容

161 篇文章 7 订阅

订阅专栏

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.总结
主表和子表均在指定的节点上均匀分布。其他未指定的节点无数据。