Mycat水平分表(E-R表)

Mycat水平分表E-R表

1.E-R表

关系型数据库是基于实体关系模型（Entity-Relationship Model)之上，通过其描述了真实世界中事物与关系，
Mycat 中的 ER 表即是来源于此。根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的
父表记录存放在同一个数据分片上，即子表依赖于父表，通过表分组（Table Group）保证数据 Join 不会跨库操
作。
表分组（Table Group）是解决跨分片数据 join 的一种很好的思路，也是数据切分规划的重要一条规则。

2.修改schema.xml配置文件

  <table name = "orders_hor" dataNode = "dn1,dn2" rule = "mod_rule">
    <!--添加子表配置-->
        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
  </table>

3.创建表orders_detail

• master1和master2上

CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);

4.插入数据

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.验证

mysql> select * from orders_detail;
+----+---------+----------+
| id | detail  | order_id |
+----+---------+----------+
|  1 | detail1 |        1 |
|  2 | detail1 |        2 |
|  6 | detail1 |        6 |
|  3 | detail1 |        3 |
|  4 | detail1 |        4 |
|  5 | detail1 |        5 |
+----+---------+----------+
6 rows in set (0.01 sec)

mysql> Select o.*,od.detail  from orders_hor o inner join orders_detail od  on o.id=od.order_id;
+----+------------+-------------+-----------+---------+
| id | order_type | customer_id | amount    | detail  |
+----+------------+-------------+-----------+---------+
|  3 |        101 |         101 | 120000.00 | detail1 |
|  4 |        101 |         101 | 103000.00 | detail1 |
|  5 |        102 |         101 | 100400.00 | detail1 |
|  1 |        101 |         100 | 100100.00 | detail1 |
|  2 |        101 |         100 | 100300.00 | detail1 |
|  6 |        102 |         100 | 100020.00 | detail1 |
+----+------------+-------------+-----------+---------+
6 rows in set (0.00 sec)