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,所以不支持。假如支持的话,可以查出匹配的三条记录,而不是上面的一条。