Mycat分片规则是怎么样的,针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
1.sharding-by-intfile
hash分片
-
表对应的分片规则
查看rule.xml查看对应的关系
-
<tableRule name="sharding-by-intfile">
-
<rule>
-
<columns>sharding_id</columns> 根据该字段分片
-
<algorithm>hash–int</algorithm> 分片的方法
-
</rule>
-
</tableRule>
查看rule.xml对应的方法
-
<function name="hash-int"
-
class="io.mycat.route.function.PartitionByFileMap">
-
<property name="mapFile">partition–hash–int.txt</property> —对应的文件
-
<property name="defaultNode">1</property>
-
</function>
查看文件
-
[root@localhost conf]# more partition-hash-int.txt
-
10000=0 ####sharding_id为10000发到1节点
-
10010=1 ####sharding_id为10010发到2节点
-
DEFAULT_NODE=1 ###其它插到2节点
实验
mysql> create table employee (id int not null primary key,name varchar(100),sharding_id int not null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into employee(id,name,sharding_id) values(2,'leader us',10000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee(id,name,sharding_id) values(4,'leader us',10000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee(id,name,sharding_id) values(3,'leader us',100003); —–其它插到2节点
mysql> insert into employee(id,name,sharding_id) values(4,'leader us',10010);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee(id,name,sharding_id) values(5,'leader us',10010);
Query OK, 1 row affected (0.03 sec)
2.auto-sharding-long
范围分片
-
分片表如下:
-
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
-
查看rule.xml对应的关系
-
<tableRule name="auto-sharding-long">
-
<rule>
-
<columns>id</columns>
-
<algorithm>rang-long</algorithm>
-
</rule>
-
</tableRule>
-
对应的方法
-
<function name="rang-long"
-
class="io.mycat.route.function.AutoPartitionByLong">
-
<property name="mapFile">autopartition-long.txt</property>
-
</function>
-
对应的文件:
-
# range start-end ,data node index
-
# K=1000,M=10000.
-
0-500M=0 #####范围0-500M插到第一个节点
-
500M-1000M=1 #####范围500m-1000M插到第2个节点
-
1000M-1500M=2 。。。类推
3.mod-log
取模分片
-
<table name="tt2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
-
-
fun:
-
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
-
<!– how many data nodes –>
-
<property name="count">3</property>
-
</function>
-
<tableRule name="mod-long">
-
<rule>
-
<columns>id</columns>
-
<algorithm>mod-long</algorithm>
-
</rule>
-
</tableRule>
<table name="tt2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />
fun:
-
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
-
<!– how many data nodes –>
-
<property name="count">3</property>
-
</function>
-
<tableRule name="mod-long">
-
<rule>
-
<columns>id</columns>
-
<algorithm>mod-long</algorithm>
-
</rule>
-
</tableRule>
4. sharding-by-month
按月分片
tab:
-
<table name="month_tab" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="sharding-by-month" />
rule:
-
<tableRule name="sharding-by-month">
-
<rule>
-
<columns>create_time</columns>
-
<algorithm>partbymonth</algorithm>
-
</rule>
-
</tableRule>
-
<function name="partbymonth"
-
class="io.mycat.route.function.PartitionByMonth">
-
<property name="dateFormat">yyyy-MM-dd</property>
-
<property name="sBeginDate">2015-01-01</property> ##开始时间
-
</function>
测试:
mysql> insert into month_tab(id,name,sharding_id,create_time) values (1,'1',1,'2015-01-01');
Query OK, 1 row affected (0.43 sec)
mysql> insert into month_tab(id,name,sharding_id,create_time) values (2,'2',2,'2015-02-02');
Query OK, 1 row affected (0.01 sec)
mysql> insert into month_tab(id,name,sharding_id,create_time) values (3,'3',3,'2015-03-03');
Query OK, 1 row affected (0.49 sec)
mysql> insert into month_tab(id,name,sharding_id,create_time) values (4,'4',4,'2015-04-04'); ###按月分片,只有三个节点,只能插到1,2,3月份的,4月份就开始报错了
ERROR 1064 (HY000): Can't find a valid data node for specified node index :MONTH_TAB -> CREATE_TIME -> 2015-04-04 -> Index : 3
5 sharding-by-day
按日分片(1.6默认文件都没写,自己配置的)
-
tab:
-
<table name="day_tab" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-day"/>
-
rule:
-
<tableRule name="sharding-by-day">
-
<rule>
-
<columns>create_time</columns>
-
<algorithm>partbyday</algorithm>
-
</rule>
-
</tableRule>
-
<function name="partbyday"
-
class="io.mycat.route.function.PartitionByDate">
-
<property name="dateFormat">yyyy-MM-dd</property>
-
<property name="sBeginDate">2015-01-01</property> ###起始日期
-
<property name="sPartionDay">3</property> ###多少天后开始分片
-
</function>
-
测试:
-
插了前9天,分到三个分片
-
mysql> select * from day_tab;
+—-+——+————-+———————+
| id | name | sharding_id | create_time |
+—-+——+————-+———————+
| 7 | 1 | 1 | 2015-01-08 00:00:00 |
| 8 | 1 | 1 | 2015-01-09 00:00:00 |
| 13 | 1 | 1 | 2015-01-07 00:00:00 |
| 7 | 1 | 1 | 2015-01-01 00:00:00 |
| 8 | 1 | 1 | 2015-01-02 00:00:00 |
| 9 | 1 | 1 | 2015-01-03 00:00:00 |
| 10 | 1 | 1 | 2015-01-04 00:00:00 |
| 11 | 1 | 1 | 2015-01-05 00:00:00 |
| 12 | 1 | 1 | 2015-01-06 00:00:00 |
+—-+——+————-+———————+
9 rows in set (0.01 sec) -
mysql> insert into day_tab(id,name,sharding_id,create_time) values (17,'1',1,'2015-01-10'),(18,'1',1,'2015-01-11'); ###插第10天的,开始报错
ERROR 1064 (HY000): Index: 3, Size: 3
关于Mycat分片规则是怎么样的问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注亿速云行业资讯频道了解更多相关知识。
原创文章,作者:3628473679,如若转载,请注明出处:https://blog.ytso.com/204343.html