Mycat分片規則是怎么樣的

Mycat分片規則是怎么樣的，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

1.sharding-by-intfile

hash分片

1. 表對應的分片規則

2. 
   

查看rule.xml查看對應的關系

1. <tableRule name="sharding-by-intfile">
   

2.                 <rule>
   

3.                         <columns>sharding_id</columns>   根據該字段分片
   

4.                         <algorithm>hash-int</algorithm>    分片的方法
   

5.                 </rule>
   

6.         </tableRule>

查看rule.xml對應的方法

1. <function name="hash-int"
   

2.                 class="io.mycat.route.function.PartitionByFileMap">
   

3.                 <property name="mapFile">partition-hash-int.txt</property>   ---對應的文件
   

4.                 <property name="defaultNode">1</property>
   

5.         </function>

查看文件

1. [root@localhost conf]# more partition-hash-int.txt
   

2. 10000=0   ####sharding_id為10000發到1節點
   

3. 10010=1   ####sharding_id為10010發到2節點

4. 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

范圍分片

1. 分片表如下：
   
   

1. <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

2. 查看rule.xml對應的關系
   
   

1. <tableRule name="auto-sharding-long">
   

2.                 <rule>
   

3.                         <columns>id</columns>
   

4.                         <algorithm>rang-long</algorithm>
   

5.                 </rule>
   

6.         </tableRule>

3. 對應的方法
   
   

1. <function name="rang-long"
   

2.                 class="io.mycat.route.function.AutoPartitionByLong">
   

3.                 <property name="mapFile">autopartition-long.txt</property>
   

4.         </function>

4. 對應的文件：
   
   

1. # range start-end ,data node index
   

2. # K=1000,M=10000.
   

3. 0-500M=0           #####范圍0-500M插到第一個節點
   

4. 500M-1000M=1       #####范圍500m-1000M插到第2個節點
   

5. 1000M-1500M=2      。。。類推

5. 
   

3.mod-log

取模分片

1. <table name="tt2" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" />

2. 
   

3. 
    fun:
   
   
   
   

1. <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
   

2.                 <!-- how many data nodes -->
   

3.                 <property name="count">3</property>
   

4.         </function>

5. <tableRule name="mod-long">
   

6.                 <rule>
   

7.                         <columns>id</columns>
   

8.                         <algorithm>mod-long</algorithm>
   

9.                 </rule>
   

10.         </tableRule>

4. sharding-by-month

按月分片

1. tab:
   

2.  <table name="month_tab" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="sharding-by-month" />

rule:

1. <tableRule name="sharding-by-month">
   

2.                 <rule>
   

3.                         <columns>create_time</columns>
   

4.                         <algorithm>partbymonth</algorithm>
   

5.                 </rule>
   

6.         </tableRule>
   

7. 
   

8. 
   

9. <function name="partbymonth"
   

10.                 class="io.mycat.route.function.PartitionByMonth">
    

11.                 <property name="dateFormat">yyyy-MM-dd</property>
    

12.                 <property name="sBeginDate">2015-01-01</property>   ##開始時間
    

13.         </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默認文件都沒寫，自己配置的）

1. tab：
   

2. <table name="day_tab" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-day"/>
   

3. 
   

4. rule：
   
   

1. <tableRule name="sharding-by-day">
   

2.                 <rule>
   

3.                         <columns>create_time</columns>
   

4.                         <algorithm>partbyday</algorithm>
   

5.                 </rule>
   

6.         </tableRule>
   

7. 
   

8. 
   

9. <function name="partbyday"
   

10.                 class="io.mycat.route.function.PartitionByDate">
    

11.                 <property name="dateFormat">yyyy-MM-dd</property>
    

12.                 <property name="sBeginDate">2015-01-01</property>    ###起始日期
    

13.                 <property name="sPartionDay">3</property>            ###多少天后開始分片
    

14.         </function>

5. 測試：

6. 插了前9天，分到三個分片

7. 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)
   
   

8. 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分片規則是怎么樣的問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。