Mycat目前版本支持跨分片的join,主要实现方式有四种。 全局表，ER分片，catletT(人工智能)和ShareJoin，ShareJoin在开发版中支持，前面三种斱方式1.3.0.1支持。
8.1 全局表
一个真实的业务系统中，往往存在多量的类似字典表的表格，这些表基本上很少变动，如图：
配置：
全局表配置比较简单，不用写Rule规则，如下配置即可：
在TESTDB下配置一个全局表province:
vi schema.xml
添加：
<table name="province" dataNode="node_db01,node_db02,node_db03" type="global"></table>
登陆9066端口， mysql -uroot -proot -h127.0.0.1 -P9066 -DTESTDB
执行命令：reload @@config_all;
-
建表：
-
mysql -utest -ptest -h127.0.0.1 -P8066 -DUSERDB
-
mysql> create table province( id int, name varchar( 30));
-
Query OK, 0 rows affected (0.19 sec)
-
mysql> insert into province( id, name) values( 1, 'beijing');
-
Query OK, 3 rows affected (0.11 sec)
-
mysql> select * from province limit 1;
-
+ ------+---------+
-
| id | name |
-
+ ------+---------+
-
| 1 | beijing |
-
+ ------+---------+
-
1 row in set ( 3.56 sec)
-
在mysql里的db10,db11,db12都能看到插入的数据：
-
mysql> select * from db12.province;
-
+ ------+---------+
-
| id | name |
-
+ ------+---------+
-
| 1 | beijing |
-
+ ------+---------+
-
1 row in set ( 0.00 sec)
-
mysql> select * from db11.province;
-
+ ------+---------+
-
| id | name |
-
+ ------+---------+
-
| 1 | beijing |
-
+ ------+---------+
-
1 row in set ( 0.01 sec)
-
mysql> select * from db10.province;
-
+ ------+---------+
-
| id | name |
-
+ ------+---------+
-
| 1 | beijing |
-
+ ------+---------+
-
1 row in set ( 0.00 sec)
8.2 ER join
基于E-R关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。
分片在dn1,dn2上，orders依赖父表进行分片，两个表的关联关系为orders.customer_id=customer.id。示意图如下：
配置：
<tablen ame="customer" dataNode="dn1,dn2" rule="sharding-by-intfile">
<childTable name="orders" joinKey="customer_id" parentKey="id"/>
</table>
示例：
我先创建了一个分片规则：auto-sharding-long-custom
id属于0-1000范围内的在分区1里，1000-2000的在分区2里，2000-3000的在分片3里。
vi schema.xml
在USERDB处配置：
<table name="customer" dataNode="dn10,dn11,dn12" rule="auto-sharding-long-custom">
<childTable name="orders" joinKey="customer_id" parentKey="id"/>
</table>
登录9066端口，使得配置生效：
reload @@config_all;
-
[root@PC conf]# mysql -u test -ptest -P8066 -h 127.0.0.1 TESTDB
-
Reading table information for completion oftable and column names
-
You can turn off this feature to get aquicker startup with -A
-
-
Welcome to the MySQL monitor. Commands end with ; or \g.
-
Your MySQL connection id is 1
-
Server version: 5.5.8-mycat-1.3 MyCatServer (OpenCloundDB)
-
-
Copyright (c) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved.
-
-
Oracle is a registered trademark of OracleCorporation and/or its
-
affiliates. Other names may be trademarksof their respective
-
owners.
-
-
Type ' help;' or '\h' for help. Type '\c' toclear the current input statement.
-
-
mysql> use USERDB;
-
Reading table information for completion oftable and column names
-
You can turn off this feature to get aquicker startup with -A
-
-
Database changed
-
mysql> show tables;
-
+ ------------------+
-
| Tables in USERDB |
-
+ ------------------+
-
| company |
-
| customer |
-
| employee |
-
| orders |
-
| province |
-
+ ------------------+
-
5 rows in set ( 0.01 sec)
-
-
建表：
-
mysql> create table customer( id int primary key, name varchar( 30));
-
Query OK, 0 rows affected (0.13 sec)
-
-
mysql> create table orders( id int, name varchar( 30),customer_id int, constraint fk_companyid foreign key(customer_id) references customer( id));
-
Query OK, 0 rows affected (0.25 sec)
-
-
插入几条数据：
-
mysql> insert into customer( id, name) values( 999, 'dan'),( 1000, 'jiao'),( 1003, 'song'),( 2002, 'yang');
-
mysql> insert into orders( id, name,customer_id) values( 1, 'mirror', 999),( 2, 'banana', 2002),( 3, 'apple', 1003),( 4, 'pear', 2002);
-
ERROR 1064 (HY000): ChildTable multi insertnot provided
-
看来mycat子表不支持一次插入多条记录。
-
mysql> insert into orders( id, name,customer_id) values( 1, 'mirror', 999);
-
Query OK, 1 row affected (0.07 sec)
-
-
mysql> insert into orders( id, name,customer_id) values( 2, 'banana', 2002)
-
-
Query OK, 1 row affected ( 0.02 sec)
-
-
mysql> insert into orders( id, name,customer_id) values( 3, 'apple', 1003);
-
Query OK, 1 row affected (0.02 sec)
-
-
mysql> insert into orders( id, name,customer_id) values( 4, 'pear', 2002);
-
Query OK, 1 row affected (0.02 sec)
-
-
进入mysql验证：
-
mysql> select * from db10.customer;
-
+ ------+------+
-
| id | name |
-
+ ------+------+
-
| 999 | dan |
-
| 1000 | jiao |
-
+ ------+------+
-
2 rows in set ( 0.01 sec)
-
-
mysql> select * from db11.customer;
-
+ ------+------+
-
| id | name |
-
+ ------+------+
-
| 1003 | song |
-
+ ------+------+
-
1 row in set ( 0.00 sec)
-
-
mysql> select * from db12.customer;
-
+ ------+------+
-
| id | name |
-
+ ------+------+
-
| 2002 | yang |
-
+ ------+------+
-
1 row in set ( 0.00 sec)
-
-
mysql> select * from db12.orders;
-
+ ------+--------+-------------+
-
| id | name | customer_id |
-
+ ------+--------+-------------+
-
| 2 | banana | 2002 |
-
| 4 | pear | 2002 |
-
+ ------+--------+-------------+
-
2 rows in set ( 0.00 sec)
-
-
mysql> select * from db11.orders;
-
+ ------+-------+-------------+
-
| id | name | customer_id |
-
+ ------+-------+-------------+
-
| 3 | apple | 1003 |
-
+ ------+-------+-------------+
-
1 row in set ( 0.00 sec)
-
-
mysql> select * from db10.orders;
-
+ ------+--------+-------------+
-
| id | name | customer_id |
-
+ ------+--------+-------------+
-
| 1 | mirror | 999 |
-
+ ------+--------+-------------+
-
1 row in set ( 0.00 sec)
-
-
我们看到orders列customer_id对应的customer的 id属于哪个分片，orders的那条记录就在哪个分片内。
-
-
可以在mycat上正常地联合查询：
-
mysql> select b.*,a.name as custome_name from customer a inner join orders b on a.id=b.customer_id;
-
+ ------+--------+-------------+--------------+
-
| id | name | customer_id |custome_name |
-
+ ------+--------+-------------+--------------+
-
| 2 | banana | 2002 |yang |
-
| 4 | pear | 2002 | yang |
-
| 1 | mirror | 999 |dan |
-
| 3 | apple | 1003 | song |
-
+ ------+--------+-------------+--------------+
-
4 rows in set ( 0.02 sec)
8.3 share join
ShareJoin是一个简单的跨分片Join,基于HBT的方式实现。 目前支持2个表的join,原理就是解析SQL语句，拆分成单表的SQL语句执行，然后把各个节点的数据汇集。配置支持任意配置的A,B表如：
A,B的dataNode相同
A,B的dataNode不同
示例：
我先创建了一个分片规则：auto-sharding-long-custom
id属于0-1000范围内的在分区1里，1000-2000的在分区2里。
新定义两个处于不同分片中的两个表
vi schema.xml
在USERDB处添加：
<table name="student" primaryKey="ID" dataNode="dn10,dn11" rule="auto-sharding-long-custom"></table>
<table name="score" primaryKey="ID" dataNode="dn11,dn12" rule="auto-sharding-long-custom"></table>
登录9066端口，使得配置生效：
reload @@config_all;
-
建表：
-
mysql> create table student( id int primary key, name varchar( 30));
-
Query OK, 0 rows affected (0.19 sec)
-
-
mysql> create table score( id int,studentid int,score int, constraint fk_studentid foreign key(studentid) references student( id));
-
Query OK, 0 rows affected (0.13 sec)
-
-
插入数据：
-
-
mysql> insert into student( id, name) values( 1, 'dan'),( 1002, 'jiao'),( 88, 'song');
-
Query OK, 3 rows affected (0.33 sec)
-
Records: 2 Duplicates: 0 Warnings: 0
-
-
mysql> insert into score( id,studentid,score) values( 1, 1, 100);
-
Query OK, 1 row affected (0.07 sec)
-
-
mysql> insert into score( id,studentid,score) values( 1008, 88, 90);
-
Query OK, 1 row affected (0.03 sec)
-
-
mysql> insert into score( id,studentid,score) values( 8, 1002, 99);
-
Query OK, 1 row affected (0.01 sec)
-
进mysql查询：
-
mysql> select * from db10.student;
-
+ ----+------+
-
| id | name |
-
+ ----+------+
-
| 1 | dan |
-
| 88 | song |
-
+ ----+------+
-
2 rows in set ( 0.01 sec)
-
-
mysql> select * from db11.student;
-
+ ------+------+
-
| id | name |
-
+ ------+------+
-
| 1002 | jiao |
-
+ ------+------+
-
1 row in set ( 0.00 sec)
-
-
mysql> select * from db12.student;
-
ERROR 1146 (42S02): Table 'db12.student'doesn't exist
-
-
mysql> select * from db10.score;
-
ERROR 1146 (42S02): Table 'db10.score'doesn't exist
-
mysql> select * from db12.score;
-
+ ------+-----------+-------+
-
| id | studentid | score |
-
+ ------+-----------+-------+
-
| 1008 | 88 | 90 |
-
+ ------+-----------+-------+
-
1 row in set ( 0.00 sec)
-
-
mysql> select * from db11.score;
-
+ ------+-----------+-------+
-
| id | studentid | score |
-
+ ------+-----------+-------+
-
| 1 | 1 | 100 |
-
| 8 | 1002 | 99 |
-
+ ------+-----------+-------+
-
2 rows in set ( 0.00 sec)
-
-
不使用 share join在mycat查询：
-
mysql> select * from student a inner join score b on a.id=b.studentid;
-
+ ------+------+------+-----------+-------+
-
| id | name | id | studentid | score|
-
+ ------+------+------+-----------+-------+
-
| 1002 | jiao | 8 | 1002 | 99 |
-
+ ------+------+------+-----------+-------+
-
1 row in set ( 0.02 sec)
-
只查到了db11分区里面的数据。
-
-
使用 share join:
-
mysql> /*!mycat:catlet=demo.catlets.ShareJoin */ select * from student a inner join score b on a.id=b.studentid;
-
ERROR 1064 (HY000):java.lang.ClassNotFoundException: demo.catlets.ShareJoin
-
-
Share join只在开发版本中支持，我的是mycat 1.3，所以不支持。假如支持的话，可以查出匹配的三条记录，而不是上面的一条。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
