ShareJoin是一个简单的跨分片Join,基于HBT的方式实现。
目前支持2个表的join,原理就是解析SQL语句，拆分成单表的SQL语句执行，然后把各个节点的数据汇集。
配置
支持任意配置的A,B表，如：
A,B的dataNode相同
<table name="A" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <table name="B" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
A,B的dataNode不同
<table name="A" dataNode="dn1,dn2 " rule="auto-sharding-long" /> <table name="B" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
或
<table name="A" dataNode="dn1 " rule="auto-sharding-long" /<table name="B" dataNode=" dn2,dn3" rule="auto-sharding-long" /
代码测试
先把表company从全局表修改下配置
<table name="company" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="mod-long" />
重新插入数据
mysql> delete from company; Query OK, 9 rows affected (0.19 sec) mysql> insert company (id,name) values(1,'mycat'); Query OK, 1 row affected (0.08 sec) mysql> insert company (id,name) values(2,'ibm'); Query OK, 1 row affected (0.03 sec) mysql> insert company (id,name) values(3,'hp'); Query OK, 1 row affected (0.03 sec)
下面可以看下普通的join和sharejoin的区别
mysql> select a.*,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) mysql> /*!mycat:catlet=demo.catlets.ShareJoin */ select a.*,b.id, b.name as tit from customer a,company b on a.company_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.05 sec)
其他两种写法
/*!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; +----+------+------------+-------------+----+-------+ | 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.01 sec) /*!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; +----+------+------------+-------------+----+-------+ | 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.01 sec)
对*的支持，还可以这样写SQL
