mycat分片join——sharejoin-改自官方权威指南

最新推荐文章于 2021-07-12 17:22:24 发布

原创最新推荐文章于 2021-07-12 17:22:24 发布 · 3.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mycat #数据库中间件 #分片join

本文介绍了Mycat数据库中间件中的ShareJoin特性，用于解决跨分片JOIN的问题。通过解析SQL并拆分为单表查询，ShareJoin能够在多个数据节点上执行并汇总结果。配置灵活，支持不同数据节点的表进行JOIN操作。通过示例展示了不同JOIN写法的使用效果，如普通JOIN、JOIN with ON 和 WHERE JOIN，以及对星号(*)的支持。

Share join

ShareJoin 是一个简单的跨分片 Join,基于 HBT 的方式实现。

目前支持 2 个表的 join,原理就是解析 SQL 语句，拆分成单表的 SQL 语句执行，然后把各个节点的数据汇集。

配置

支持任意配置的 A,B 表如：

A,B 的 dataNode 相同

<tablename="A" dataNode="dn1,dn2,dn3"rule="auto-sharding-long" />

<tablename="B" dataNode="dn1,dn2,dn3"rule="auto-sharding-long" />

A,B 的 dataNode 不同

<tablename="A" dataNode="dn1,dn2 "rule="auto-sharding-long" />

<tablename="B" dataNode="dn1,dn2,dn3"rule="auto-sharding-long" />

或

<tablename="A" dataNode="dn1 "rule="auto-sharding-long" />

<tablename="B" dataNode=" dn2,dn3"rule="auto-sharding-long" />

代码测试

先把表 company 从全局表修改下配置

<tablename="company" primaryKey="ID"dataNode="dn1,dn2,dn3" rule="mod-long" />

重新插入数据

create table company(id int not null primary key,name varchar(100));

mysql> deletefrom company;

Query OK, 9 rowsaffected (0.19 sec)

insert company(id,name) values(1,'mycat');

insert company(id,name) values(2,'ibm');

insert company(id,name) values(3,'hp');

下面可以看下普通的 join 和 sharejoin 的区别

create table customer(id int not null primary key,namevarchar(100),company_id int not null,sharding_id int not null);

insert into customer(id,name,company_id,sharding_id)values(1,'wang',1,10000),(2,'xue',2,10010),(3,'feng',3,10000);

普通的join

mysql> selecta.*,b.id, b.name as tit from customer a,company b where a.company_id=b.id;

+----+------+------------+-------------+----+------+

| id | name |company_id | sharding_id | id | tit |

+----+------+------------+-------------+----+------+

| 3 | feng | 3 |10000 | 3 | hp |

+----+------+------------+-------------+----+------+

1 row in set (0.03 sec)---------会发现只查出一条，但是按照正常的逻辑这里应该是查出来三条，但是因为分库分表了，所以要用share join，如下：

mysql> /*!mycat:catlet=demo.catlets.ShareJoin */ select a.*,b.id,b.name as tit from customer a,company b ona.company_id=b.id;------------------------------------from a,b ona.id=b.id

+----+------+------------+-------------+----+-------+

| id | name |company_id | sharding_id | id | tit |

+----+------+------------+-------------+----+-------+

| 3 | feng | 3 |10000 | 3 | hp |

| 1 | wang | 1 |10000 | 1 | mycat |

| 2 | xue | 2 |10010 | 2 | ibm |

+----+------+------------+-------------+----+-------+

3 rows in set (0.05sec)

其他两种写法

/*!mycat:catlet=demo.catlets.ShareJoin*/ select a.*,b.id, b.name as tit from customer a join company b on

a.company_id=b.id;-------------------from a join b ona.id=b.id

+----+------+------------+-------------+----+-------+

| id | name |company_id | sharding_id | id | tit |

+----+------+------------+-------------+----+-------+

| 3 | feng | 3 |10000 | 3 | hp |

| 1 | wang | 1 |10000 | 1 | mycat |

| 2 | xue | 2 |10010 | 2 | ibm |

+----+------+------------+-------------+----+-------+

3 rows in set (0.01sec)

/*!mycat:catlet=demo.catlets.ShareJoin*/ select a.*,b.id, b.name as tit from customer a join company b where

a.company_id=b.id;--------------------------from ajoin b where a.id=b.id

+----+------+------------+-------------+----+-------+

| id | name |company_id | sharding_id | id | tit |

+----+------+------------+-------------+----+-------+

| 3 | feng | 3 |10000 | 3 | hp |

| 1 | wang | 1 |10000 | 1 | mycat |

| 2 | xue | 2 |10010 | 2 | ibm |

+----+------+------------+-------------+----+-------+

3 rows in set (0.01sec)

对*的支持，还可以这样写 SQL

mysql>/*!mycat:catlet=demo.catlets.ShareJoin */ select a.*,b.* from customer a joincompany b on

a.company_id=b.id;

+----+------+------------+-------------+-------+

+----+------+------------+-------------+-------+

| 1 | wang | 1 |10000 | mycat |

| 2 | xue | 2 |10010 | ibm |

| 3 | feng | 3 |10000 | hp |

+----+------+------------+-------------+-------+

3 rows in set (0.02sec)

mysql>/*!mycat:catlet=demo.catlets.ShareJoin */ select * from customer a join companyb on

a.company_id=b.id;

+----+------+------------+-------------+-------+

104

+----+------+------------+-------------+-------+

| 1 | wang | 1 |10000 | mycat |

| 2 | xue | 2 |10010 | ibm |

| 3 | feng | 3 |10000 | hp |

+----+------+------------+-------------+-------+

3 rows in set (0.02sec)

/*!mycat:catlet=demo.catlets.ShareJoin*/ select a.id,a.user_id,a.traveldate,a.fee,a.days,b.id as nnid, b.title as

tit fromtravelrecord a join hotnews b on b.id=a.days order by a.id ;

以上两张表的配置：

在mysql物理库中的分配

customer表

mysql>select * from db1.customer;

+----+------+------------+-------------+

+----+------+------------+-------------+

| 1 | wang | 1 | 10000 |

| 3 | feng | 3 | 10000 |

+----+------+------------+-------------+

2 rowsin set (0.00 sec)

mysql>select * from db2.customer;

+----+------+------------+-------------+

+----+------+------------+-------------+

| 2 | xue | 2 | 10010 |

+----+------+------------+-------------+

1 rowin set (0.00 sec)

company表

mysql> select *from db1.company;

+----+--------+

| id | name |

+----+--------+

| 3 | hp |

| 6 | huawei |

+----+--------+

2 rows in set (0.00sec)

mysql> select *from db2.company;

+----+--------+

| id | name |

+----+--------+

| 1 | mycat |

| 4 | anyong |

+----+--------+

2 rows in set (0.00sec)

mysql> select *from db3.company;

+----+-------+

| id | name |

+----+-------+

| 2 | ibm |

| 5 | deqin |

+----+-------+

2 rows in set (0.01sec)

以上两张表在逻辑库中的查到的结果

mycat>select *from customer;

+----+------+------------+-------------+

+----+------+------------+-------------+

| 2 | xue | 2 | 10010 |

| 1 | wang | 1 | 10000 |

| 3 | feng | 3 | 10000 |

+----+------+------------+-------------+

3 rows in set (0.01sec)

mycat>select *from company;

+----+--------+

| id | name |

+----+--------+

| 1 | mycat |

| 4 | anyong |

| 3 | hp |

| 6 | huawei |

| 2 | ibm |

| 5 | deqin |

+----+--------+

6 rows in set (0.02sec)