ER分片介绍
以mycat逻辑库里面自带的例子,例如客户（CUSTOMER）跟订单（orders）以及订单条目（orders_item),订单条目依
赖订单表，订单表依赖客户，这样客户与订单以及订单条目之间存在依赖关系，这类似业务的切分可以抽象出合适的切分
规则，比如根据用户ID切分,其它相关的表都依赖于用户ID，再或者根据订单ID进行切分，总之部分业务总会可以抽象出
父子关系的表。这类表适用于ER分片表，子表的记录与所关联的父表记录存放在同一个数据分片上，避免数据Join跨库操
作，以order与order_item例子为例和customer与order，schema.xml中定义合适的分片配置,order,order_item根
据id迕行数据切分，保证相同id的数据分到同一个分片上，在进行数据插入操作时，Mycat会获取order所在的分片，然
后将order_item也插入到order所在的分片。同理order与customer也是这样关系。
| Tables | 主键 | 对应的父表 | 关联关系 |
|---|---|---|---|
| customer | id | ||
| orders | id | customer | orders.customer_id=customer.id |
| order_items | id | orders | order_items.order_id=orders.id |
| customer_addr | id | customer | customer_addr.customer_id= customer.id |
1 修改 scehma.xml 添加 ER分片对应的父子表
#customer 采用rule是sharding-by-intfile，是枚举类型，类似range，可以看成是range特例情况.
[root@localhost conf]# vi schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<table name="T_VOTE" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
<childTable name="order_items" joinKey="order_id"
parentKey="id" />
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
parentKey="id" />
</table>
</schema>
<dataNode name="dn1" dataHost="192.168.2.130" database="db1" />
<dataNode name="dn2" dataHost="192.168.2.130" database="db2" />
<dataNode name="dn3" dataHost="192.168.2.130" database="db3" />
<dataHost name="192.168.2.130" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.2.130:3306" user="root"
password="root123">
</writeHost>
</dataHost>
</mycat:schema>
2 修改 rule.xml
#修改默认规则columns里面对应sharding_id变成id
<tableRule name="sharding-by-intfile">
<rule>
<columns>id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int"
class="org.opencloudb.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
#修改func 对应的文本:定义2个值对应datanode
[root@localhost conf]# vi partition-hash-int.txt
10000=0
10010=1
#reload config
mysql> reload @@config;
Query OK, 1 row affected (0.24 sec)
Reload config success
3 在逻辑库中创建需要表
注释:创建customer和customer_addr表演示ER分片.
[root@localhost bin]# mysql -h 192.168.2.130 -P8066 -u test -ptest
Warning: Using a password on the command line interfa ce can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql>
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| customer |
| customer_addr |
| orders |
| order_items |
| travelrecord |
| t_vote |
+------------------+
6 rows in set (0.00 sec)
mysql> create table customer(id int not null,customer_id int not null,datanode varchar(10),primary key(id));
Query OK, 0 rows affected (0.09 sec)
mysql> create table customer_addr(id int not null,customer_id int not null,customer_addr varchar(200),datanode varchar(10),primary key(id));
Query OK, 0 rows affected (0.04 sec)
插入数据到customer和customer_addr表
a:插入数据到customer
mysql> insert into customer(id,customer_id,datanode) values(10000,1,database());
Query OK, 1 row affected (0.07 sec)
对应的日志
可以看到写入到db1
b:插入数据到 customer_addr
mysql> insert into customer_addr (id, customer_id, customer_addr, datanode)
values (10000, 10000, 'shanghai', DATABASE())
Query OK, 1 row affected (0.11 sec)
对应的日志
c:继续插入数据到dn2
mysql> insert into customer(id,customer_id,datanode) values(10010,2,database());
Query OK, 1 row affected (0.01 sec)
mysql> insert into customer_addr(id,customer_id,customer_addr,datanode) values
(10010,10010,'chengdu',database());
Query OK, 1 row affected (0.11 sec)
4 验证ER分片
#获取全部数据
mysql> explain select customer.id,customer.customer_id,customer.datanode,
customer_addr.customer_addr
from customer,customer_addr
where customer.id=customer_addr.customer_id;
+-----------+-----------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------+
| dn1 |select customer.id,customer.customer_id,customer.datanode, |
| | customer_addr.customer_addr |
| |from customer,customer_addr |
| |where customer.id=customer_addr.customer_id; |
| | |
| dn2 |select customer.id,customer.customer_id,customer.datanode, |
| | customer_addr.customer_addr |
| |from customer,customer_addr |
| |where customer.id=customer_addr.customer_id; |
+-----------+-----------------------------------------------------------+
对应的日志
由上可知走的是全部路由
#获取单个分片上的数据
mysql> explain select customer.id,customer.customer_id,customer.datanode,
customer_addr.customer_addr
from customer,customer_addr
where customer.id=customer_addr.customer_id and customer.id=10000;
+-----------+--------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------+
| dn1 | select customer.id,customer.customer_id,customer.datanode, |
| | customer_addr.customer_addr |
| | from customer,customer_addr |
| | where customer.id=customer_addr.customer_id and customer.id=10000 |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
由上可知路由到了dn1上