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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
1.E-R关系策略的由来
join
是关系数据库最常用的一个特性,然而在分布式环境中,跨分片的
join
最复杂,最难解决。
这是官方文档的描述。
具体点,比如:
mycat逻辑库hello,两张表格t1,t2。做了分库策略,t1放到了datanode1,t2放到了datanode2。如果我t1
join
t2检索数据,
怎么办?
这就是E-R关系策略要解决的问题。
mycat借鉴了table group的概念,将子表的存储位置依赖于子表,并且在物理上紧邻存放,解决了
join
的效率和性能问题。E-R关系的数据分片策略,根据这一思路,将子表的记录和所关联的父表记录存放在同一个数据分片上。
2.测试官方教程文档上的E-R关系表
customer采用sharding-by-intfile(分片枚举)策略,分片在dn1,dn2上,orders依赖父表进行分片,两个表的关联关系为orders.customer_id=customer.
id
。示意图如下:
![](http:
//i2
.51cto.com
/images/blog/201712/21/1298cf4400570a0d5bcc4bdd40070e27
.png?x-oss-process=image
/watermark
,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
<table name=
"customer"
primaryKey=
"ID"
dataNode=
"dn1,dn2"
rule=
"sharding-by-intfile"
>
<childTable name=
"orders"
joinKey=
"customer_id"
parentKey=
"id"
/>
<
/table
>
解释:
<table name=
"customer"
primaryKey=
"ID"
dataNode=
"dn1,dn2"
rule=
"sharding-by-intfile"
>
这一行是定义customer表,主键是
id
,分片部署在dn1,dn2,分片规则是sharding-by-intfile
<childTable name=
"orders"
joinKey=
"customer_id"
parentKey=
"id"
/>
这一行是定义orders是childtable。
childtable是依赖父表的结构,就是前面时候的E-R关系的表。
childtable的joinkey会按照父表的parentkey一起切分。
<
/table
>
这是对应 <table name= 的结束格式,参考xml格式。
3.
表格设计:
customer表
id
(primarykey) name city (用city做分片)
orders表
customer_id(primary key) orders
两表格关系:
customer表的主键
id
为orders表主键customer_id的外键
4.mycat上实际测试:
停止mycat服务,修改配置文件,如下:
[root@ha1 conf]
# cat schema.xml
<?xml version=
"1.0"
?>
<!DOCTYPE mycat:schema SYSTEM
"schema.dtd"
>
<mycat:schema xmlns:mycat=
"http://io.mycat/"
>
<schema name=
"hello"
checkSQLschema=
"false"
sqlMaxLimit=
"100"
>
<!-- auto sharding by
id
(long) -->
<table name=
"t1"
dataNode=
"dn1,dn2"
rule=
"sharding-by-intfile"
/>
<!-- global table is auto cloned to all defined data nodes ,so can
join
with any table whose sharding node is
in
the same data node -->
<table name=
"t2"
primaryKey=
"ID"
type
=
"global"
dataNode=
"dn1,dn2"
/>
<table name=
"t3"
dataNode=
"dn1"
/>
<table name=
"t4"
dataNode=
"dn2"
/>
<table name=
"customer"
primaryKey=
"id"
dataNode=
"dn1,dn2"
rule=
"sharding-by-intfile"
>
<childTable name=
"orders"
joinKey=
"customer_id"
parentKey=
"id"
/>
<
/table
>
<
/schema
>
<!-- <dataNode name=
"dn1$0-743"
dataHost=
"localhost1"
database=
"db$0-743"
/> -->
<dataNode name=
"dn1"
dataHost=
"mysql1"
database=
"db1"
/>
<dataNode name=
"dn2"
dataHost=
"mysql3"
database=
"db2"
/>
<dataHost name=
"mysql1"
maxCon=
"1000"
minCon=
"10"
balance=
"3"
writeType=
"0"
dbType=
"mysql"
dbDriver=
"native"
switchType=
"1"
slaveThreshold=
"100"
>
<heartbeat>
select
user()<
/heartbeat
>
<!-- can have multi write hosts -->
<writeHost host=
"hostM1"
url=
"192.168.211.138:3306"
user=
"root"
password=
"Alex2010@"
>
<
/writeHost
>
<writeHost host=
"hostS1"
url=
"192.168.211.139:3306"
user=
"root"
password=
"Alex2010@"
>
<
/writeHost
>
<
/dataHost
>
<dataHost name=
"mysql3"
maxCon=
"1000"
minCon=
"10"
balance=
"0"
writeType=
"0"
dbType=
"mysql"
dbDriver=
"native"
switchType=
"1"
slaveThreshold=
"100"
>
<heartbeat>
select
user()<
/heartbeat
>
<!-- can have multi write hosts -->
<writeHost host=
"hostM1"
url=
"192.168.211.142:3306"
user=
"root"
password=
"Alex2010@"
>
<!-- can have multi
read
hosts -->
<readHost host=
"hostS2"
url=
"192.168.211.142:3306"
user=
"root"
password=
"Alex2010@"
/>
<
/writeHost
>
<
/dataHost
>
<
/mycat
:schema>
定义分片规则:
<tableRule name=
"sharding-by-intfile"
>
<rule>
<columns>city<
/columns
>
<algorithm>
hash
-int<
/algorithm
>
<
/rule
>
<
/tableRule
>
<
function
name=
"hash-int"
class=
"io.mycat.route.function.PartitionByFileMap"
>
<property name=
"mapFile"
>partition-
hash
-int.txt<
/property
>
<property name=
"type"
>1<
/property
>
<property name=
"defaultNode"
>0<
/property
>
<
/function
>
[root@ha2 conf]
# cat partition-hash-int.txt
gz=0
sz=1
启动mycat,创建表格:
mysql> create table customer(
id
int not null primary key,name varchar(10),city varchar(20));
Query OK, 0 rows affected (0.11 sec)
mysql> create table orders (customer_id int not null primary key,orders int not null,foreign key(customer_id) references customer(
id
)
on delete cascade on update cascade);
Query OK, 0 rows affected (0.25 sec)
customer插入数据测试:
mysql> insert into customer(
id
,name,city) values(1,
'am1'
,
'gz'
),(2,
'am2'
,
'gz'
),(3,
'am3'
,
'sz'
);
mysql>
select
* from customer where city=
'gz'
;
+----+------+------+
|
id
| name | city |
+----+------+------+
| 1 | am1 | gz |
| 2 | am2 | gz |
+----+------+------+
2 rows
in
set
(0.08 sec)
mysql> explain
select
* from customer where city=
'gz'
;
+-----------+----------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------+
| dn1 | SELECT * FROM customer WHERE city =
'gz'
LIMIT 100 |
+-----------+----------------------------------------------------+
1 row
in
set
(0.01 sec)
gz的数据都在dn1实现了分片。
orders插入数据测试:
mysql> insert into orders(customer_id,orders) values(1,10001);
Query OK, 1 row affected (0.33 sec)
mysql> insert into orders(customer_id,orders) values(2,10002);
Query OK, 1 row affected (0.29 sec)
mysql> insert into orders(customer_id,orders) values(3,10003);
Query OK, 1 row affected (0.48 sec)
根据E-R分片规则,orders表格根据外键的值也就是customer的主键值切分,
也就是orders.customer_id=customer.
id
的数据分在一个区。
分别在db1,db2检索数据,看看是否达到E-R分片的设计要求。
|
本文转自 am2012 51CTO博客,原文链接:http://blog.51cto.com/goome/2058958