1、场景:
(1)行数非常多,2000w。
(2)访问非常频繁,用户访问在1-500w为最多,500-2000w也有,但是少
2、原理:
取余分片方式:分片键(一个列)与节点数量进行取余,得到余数,将数据写入对应节点
比如有4个节点,插入1-12 的 id,这样,会平均分配给各个节点。解决了,500w数据,只能在单一节点保存.
12 ÷ 4 : | 商= 3; 余数= 0 |
11 ÷ 4 : | 商= 2; 余数= 3 |
10 ÷ 4 : | 商= 2; 余数= 2 |
9 ÷ 4 : | 商= 2; 余数= 1 |
8 ÷ 4 : | 商= 2; 余数= 0 |
7 ÷ 4 : | 商= 1; 余数= 3 |
6 ÷ 4 : | 商= 1; 余数= 2 |
5 ÷ 4 : | 商= 1; 余数= 1 |
4 ÷ 4 : | 商= 1; 余数= 0 |
3 ÷ 4 : | 商= 0; 余数= 3 |
2 ÷ 4 : | 商= 0; 余数= 2 |
1 ÷ 4 : | 商= 0; 余数= 1 |
3、测试:
对t4表分两个节点保存数据
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
<table name="t4" dataNode="sh1,sh2" rule="mod-long"/>
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.78.4:3307" user="root" password="123456">
<readHost host="db2" url="192.168.78.4:3309" user="root" password="123456" />
</writeHost>
<writeHost host="db3" url="192.168.78.5:3307" user="root" password="123456">
<readHost host="db4" url="192.168.78.5:3309" user="root" password="123456" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.78.4:3308" user="root" password="123456">
<readHost host="db2" url="192.168.78.4:3310" user="root" password="123456" />
</writeHost>
<writeHost host="db3" url="192.168.78.5:3308" user="root" password="123456">
<readHost host="db4" url="192.168.78.5:3310" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />
把一张表拆分两张表,用rule,"mod-long (取模分片)策略把t4表进行拆分,分片到两个节点sh1,sh2。
看一下取模分片策略定义:
cat /usr/local/mycat/conf/rule.xml
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
解释:
<columns>id</columns> 考虑取模分片最好拿什么做分片--主键id--没有重复--叫分片键也叫分片列-
如id =1 1%2 d =2 2%2
<algorithm>mod-long</algorithm> mod-long函数名字,通过mycat写好的分片策略
cat /usr/local/mycat/conf/rule.xml
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
解释:
函数要有参数,参数:你的告诉,怎么分配
<!-- how many data nodes -->请你告诉我多少个数据节点
<property name="count">3</property>
按照测试环境:2个数据节点
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
创建测试表:
mysql -uroot -p123456 -S /data/3307/mysql.sock -P 3307 -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -uroot -p123456 -S /data/3308/mysql.sock -P 3308 -e "use taobao;create table t4 (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 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 -uroot -p123456 -S /data/3307/mysql.sock -P 3307
mysql>use taobao;
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
+----+------+
mysql -uroot -p123456 -S /data/3308/mysql.sock -P 3308
mysql> use taobao;
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
+----+------+