1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
| a
join
b
on a.xx =b.yy
a
id name
1 a sh1
3 c
2 b sh2
4 d
b
id addr aid
1001 bj 1
1002 sh 2
1003 tj 3 sh2
1004 wh 4
为了防止跨分片join，可以使用E-R模式
<table name="a" dataNode="sh1,sh2" rule="mod-long">
<childTable name="b" joinKey="aid" parentKey="id" />
</table>
select * from a join b on a.id = b.aid where a.name=d
例子：
1. 修改配置文件
vim schema.xml
<table name="a" dataNode="sh1,sh2" rule="mod-long_oldguo">
<childTable name="b" joinKey="aid" parentKey="id" />
</table>
2. 修改rule.xml mod-log分片策略:
vim rule.xml
<tableRule name="mod-long_oldguo">
<rule>
<columns>id</columns>
<algorithm>mod-long_oldguo</algorithm>
</rule>
</tableRule>
<function name="mod-long_oldguo"
class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
3. 创建测试表
mysql -S /data/3307/mysql.sock -e "use taobao;create table a (id int not
null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3307/mysql.sock -e "use taobao;create table b (id int not
null primary key auto_increment,addr varchar(20) not null ,aid int );"
mysql -S /data/3308/mysql.sock -e "use taobao;create table a (id int not
null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table b (id int not
null primary key auto_increment,addr varchar(20) not null ,aid int );"
4. 重启mycat 测试
mycat restart
mysql -uroot -p123456 -h10.0.0.51 -P8066
use TESTDB
insert into a(id,name) values(1,'a'); 1 3308
insert into a(id,name) values(2,'b'); 0 3307
insert into a(id,name) values(3,'c'); 1 3308
insert into a(id,name) values(4,'d'); 0 3307
insert into a(id,name) values(5,'e'); 1 3308
insert into b(id,addr,aid) values(1001,'bj',1); 1 3308
insert into b(id,addr,aid) values(1002,'sj',3); 1 3308
insert into b(id,addr,aid) values(1003,'sd',4); 0 3307
insert into b(id,addr,aid) values(1004,'we',2); 0 3307
insert into b(id,addr,aid) values(1005,'er',5); 0 3307
========================================================
后端数据节点数据分布：
mysql -S /data/3307/mysql.sock -e "select * from taobao.a"
mysql -S /data/3307/mysql.sock -e "select * from taobao.b"
mysql -S /data/3308/mysql.sock -e "select * from taobao.a"
mysql -S /data/3308/mysql.sock -e "select * from taobao.b"
|
