DevOps开发运维
成长之路

MyCAT核心特性——分片(水平拆分)

在垂直分表情况下,其中有一张表访问异常繁忙,数据量级很大,需要对这张热点数据表,进行水平拆分。
对一个"bigtable"分片:比如说t3表
(1)行数非常多,800w
(2)访问非常频繁
分片的目的:
(1)将大数据量进行分布存储
(2)提供均衡的访问路由

分片策略

范围分片: range 800w 1-400w一张表;400w-800w一张表
取模分片:mod 取余分配
枚举
哈希hash
时间:按月份分片,比如流水信息

优化关联查询

全局表
ER分片

range范围分片

比如说t3表
(1)行数非常多,2000w(1-1000w:sh1 1000w01-2000w:sh2)
(2)访问非常频繁,用户访问较离散
cp schema.xml schema.xml.chuizhi
[root@db01 conf]# vim schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
<table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
</schema>

auto-sharding-long参数

vim rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule> 
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>

分片范围定义

vim autopartition-long.txt
0-10=0
10-20=1

创建测试表

mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

range分片测试

[root@db01 conf]# mycat restart
mysql> use TESTDB
insert into t3(id,name) values(1,'a');
insert into t3(id,name) values(2,'b');
insert into t3(id,name) values(3,'c');
insert into t3(id,name) values(4,'d');
insert into t3(id,name) values(11,'aa');
insert into t3(id,name) values(12,'bb');
insert into t3(id,name) values(13,'cc');
insert into t3(id,name) values(14,'dd');
mysql> select * from t3;
[root@db01 conf]# mysql -S /data/3307/mysql.sock
mysql> use taobao
mysql> select* from t3;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
[root@db01 conf]# mysql -S /data/3308/mysql.sock
mysql> use taobao
mysql> select* from t3;
+----+------+
| id | name |
+----+------+
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
+----+------+

取模分片(mod-long)

取余分片方式:分片键(一个列)与节点数量进行取余,得到余数,将数据写入对应节点
[root@db01 conf]# vim schema.xml
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />
vim rule.xml 
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
....
function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>///更改节点数
</function>

创建测试表

mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

取模分片测试

[root@db01 conf]# mycat restart 
[root@db01 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
use TESTDB
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');

分别登录后端节点查询数据
mysql -S /data/3307/mysql.sock 
use taobao
select * from t4;
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |

mysql -S /data/3308/mysql.sock 
use taobao
select * from t4;
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |

枚举分片

id name telnum
1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555
vim schema.xml
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />
vim rule.xml
<tableRule name="sharding-by-intfile"> 
<rule> 
<columns>name</columns> //name列做分片
<algorithm>hash-int</algorithm> 
</rule> 
</tableRule> 
...
<function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"> 
<property name="mapFile">partition-hash-int.txt</property> 
<property name="type">1</property>//这行为新增
<property name="defaultNode">0</property>//可以在这里添加也可以在文件里添加partition-hash-int.txt,表示默认表
</function> 
[root@db01 conf]# vim partition-hash-int.txt
bj=0
sh=1
DEFAULT_NODE=1

准备测试环境

mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

测试枚举分片

重启mycat 
mycat restart 
mysql -uroot -p123456 -h10.0.0.51 -P8066
use TESTDB
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
[root@db01 conf]# mysql -S /data/3307/mysql.sock
mysql> use taobao
mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
+----+------+
[root@db01 conf]# mysql -S /data/3308/mysql.sock
mysql> use taobao
mysql> select * from t5;
+----+------+
| id | name |
+----+------+
| 2 | sh |
| 4 | sh |
| 5 | tj |
+----+------+
赞(0)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图