Mycat–实践–14–ER表的配置
1、介绍
ER表就是主子表
2、ER 关系的数据分片策略
- 子表的数据与父表在同一个数据节点上,即子表依赖于父表,通过表分组(Table Group)保证数据 Join 不会跨库操作。
- ER表中在schema.xml中使用< childTable >标签定义
3、案例
步骤1:修改 schema.xml配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库 TESTDB-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<!--
逻辑表 sys_user,存在3个数据节点中,分别是dn1,dn2,dn3
sharding-by-intfile 是 枚举分片
-->
<!-- 枚举 分片测试 -->
<table name="sys_user" dataNode="dn1,dn2,dn3" rule="sharding-by-intfile" />
<!-- 取模 分片测试 -->
<table name="sys_dept" dataNode="dn1,dn2,dn3" rule="mod-long" />
<!-- 范围约定 分片测试 -->
<table name="sys_test1" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<!-- 固定分片hash算法 分片测试 -->
<table name="sys_test2" dataNode="dn1,dn2,dn3" rule="rule2" />
<!-- jump Consistent hash 分片测试 -->
<table name="sys_test3" dataNode="dn1,dn2,dn3" rule="jch" />
<!-- 自然月 分片测试 -->
<table name="sys_test4" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" rule="sharding-by-month" />
<!-- 全局表 -->
<table name="sys_test5" dataNode="dn1,dn2,dn3" primaryKey="ID" type="global" />
<!-- 非全局表 -->
<table name="sys_test6" dataNode="dn1,dn2,dn3" primaryKey="ID" />
<!-- ER表-->
<!-- 范围约定 分片测试 -->
<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="auto-sharding-long">
<!-- customer_id 是 orders的字段,和customer表的id关联-->
<childTable name="orders" primaryKey="ID" joinKey="customer_id" parentKey="id">
<!-- order_id 是 order_items 的字段,和orders表的id关联-->
<childTable name="order_items" joinKey="order_id" parentKey="id" />
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id" parentKey="id" />
</table>
</schema>
<!--
数据节点,因为dataHost都是localhost1,所有dataHost主机有3个库,分别db1,db2,db3
-->
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataNode name="dn4" dataHost="localhost1" database="db4" />
<dataNode name="dn5" dataHost="localhost1" database="db5" />
<dataNode name="dn6" dataHost="localhost1" database="db6" />
<dataNode name="dn7" dataHost="localhost1" database="db7" />
<dataNode name="dn8" dataHost="localhost1" database="db8" />
<dataNode name="dn9" dataHost="localhost1" database="db9" />
<dataNode name="dn10" dataHost="localhost1" database="db10" />
<dataNode name="dn11" dataHost="localhost1" database="db11" />
<dataNode name="dn12" dataHost="localhost1" database="db12" />
<!--
数据库最大连接是1000,最小连接是10,
balance="0" 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上
writeType="0" :所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost
switchType="1":主从切换策略,自动切换
-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 心跳检测语句 -->
<heartbeat>select user()</heartbeat>
<!-- M1主节点 -->
<writeHost host="M1" url="192.168.187.130:3307" user="root" password="root">
<!-- 从节点 -->
<readHost host="M1S1" url="192.168.187.130:3308" user="root" password="root" />
<readHost host="M1S2" url="192.168.187.130:3309" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
步骤2、重启mycat
/usr/local/mycat/bin/mycat restart
步骤3、创建真实表:sys_test5
CREATE TABLE customer (
id int(11) NOT NULL COMMENT '主键id',
name varchar(20) NOT NULL COMMENT '客户名称',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户表';
CREATE TABLE orders (
id int(11) NOT NULL COMMENT '主键id',
customer_id int(11) NOT NULL COMMENT '客户id',
name varchar(20) NOT NULL COMMENT '订单名称',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';
CREATE TABLE order_items (
id int(11) NOT NULL COMMENT '主键id',
order_id int(11) NOT NULL COMMENT '订单id',
name varchar(20) NOT NULL COMMENT '订单商品名称',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单商品表';
CREATE TABLE customer_addr (
id int(11) NOT NULL COMMENT '主键id',
customer_id int(11) NOT NULL COMMENT '客户id',
address varchar(20) NOT NULL COMMENT '客户地址',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户地址表';
步骤4、查看分片规则
cat /usr/local/mycat/conf/autopartition-long.tx
步骤5、插入数据测试
-- 数据都在dn1
INSERT INTO customer(id,name) VALUES(1,'小明1');
INSERT INTO orders(id,customer_id,name) VALUES(1,1,'零食');
INSERT INTO order_items(id,order_id,name) VALUES(1,1,'辣条1');
INSERT INTO order_items(id,order_id,name) VALUES(2,1,'辣条2');
INSERT INTO customer_addr(id,customer_id,address) VALUES(1,1,'通信街1号');
-- 数据都在dn2
INSERT INTO customer(id,name) VALUES(11,'小明2');
INSERT INTO orders(id,customer_id,name) VALUES(2,11,'零食');
INSERT INTO order_items(id,order_id,name) VALUES(3,2,'辣条3');
INSERT INTO order_items(id,order_id,name) VALUES(4,2,'辣条4');
INSERT INTO customer_addr(id,customer_id,address) VALUES(2,11,'通信街2号');
-- 数据都在dn3
INSERT INTO customer(id,name) VALUES(21,'小明3');
INSERT INTO orders(id,customer_id,name) VALUES(3,21,'零食');
INSERT INTO order_items(id,order_id,name) VALUES(5,3,'辣条5');
INSERT INTO order_items(id,order_id,name) VALUES(6,3,'辣条6');
INSERT INTO customer_addr(id,customer_id,address) VALUES(3,21,'通信街3号');