Apply the specified algorithm

During the runtime, the application determines which shard to route to and directly calculates the shard number based on character substrings (which must be numbers). The configuration is as follows:

<tableRule name="sharding-by-substring"> <rule> <columns>id</columns> <algorithm>sharding-by-substring</algorithm> </rule> </tableRule> <function name="sharding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString"> <property name="startIndex">0</property> <! -- zero-based --> <property name="size">2</property> <property name="partitionCount">3</property> <property name="defaultPartition">0</property> </function>Copy the code

Configuration description:

Example description:

If the partition id is 05-100000002, the partition id starts from 0 and siz=2, that is, 05 is obtained. If the partition is not transmitted, 05 is allocated to defaultPartition by default.

Testing:

configuration

<table name="tb_app" dataNode="dn1,dn2,dn3" rule="sharding-by-substring"/>
Copy the code

data

CREATE TABLE 'tb_app' (id varchar(10) NOT NULL COMMENT 'id ', name varchar(200) DEFAULT NULL COMMENT' id ', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Insert into TB_app (id,name) VALUES ('00-00001','Testx00001'); insert into tb_app (id,name) values('01-00001','Test100001'); insert into tb_app (id,name) values('01-00002','Test200001'); insert into tb_app (id,name) values('02-00001','Test300001'); insert into tb_app (id,name) values('02-00002','TesT400001');Copy the code

Second, string hash parsing algorithm

Intercepts the substring at the specified position in the string to compute the fragment using the hash algorithm. The configuration is as follows:

<tableRule name="sharding-by-stringhash"> <rule> <columns>user_id</columns> <algorithm>sharding-by-stringhash</algorithm> </rule> </tableRule> <function name="sharding-by-stringhash" class="io.mycat.route.function.PartitionByString"> <property name="partitionLength">512</property> <! -- zero-based --> <property name="partitionCount">2</property> <property name="hashSlice">0:2</property> </function>Copy the code

Configuration description:

Testing:

configuration

<table name="tb_strhash" dataNode="dn1,dn2,dn3" rule="sharding-by-stringhash"/>
Copy the code

data

Create table tb_strhash(name varchar(20) primary key, content varchar(100) )engine=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO tb_strhash (name,content) VALUES('T1001', UUID()); INSERT INTO tb_strhash (name,content) VALUES('ROSE', UUID()); INSERT INTO tb_strhash (name,content) VALUES('JERRY', UUID()); INSERT INTO tb_strhash (name,content) VALUES('CRISTINA', UUID()); INSERT INTO tb_strhash (name,content) VALUES('TOMCAT', UUID());Copy the code

Principle:

3. Consistent hash algorithm

The consistent Hash algorithm effectively solves the expansion problem of distributed data. The configuration is as follows:

<tableRule name="sharding-by-murmur"> <rule> <columns>id</columns> <algorithm>murmur</algorithm> </rule> </tableRule> <function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash"> <property name="seed">0</property> <property name="count">3</property><! -- --> <property name="virtualBucketTimes">160</property> <! -- <property name="weightMapFile">weightMapFile</property> --> <! -- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> --> </function>Copy the code

Configuration description:

Testing:

configuration

<table name="tb_order" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur"/>
Copy the code

data

Create table tb_order(id int(11) primary key, money int(11), content varchar(200))engine=InnoDB; INSERT INTO TB_ORDER (id,money,content) VALUES(1, 100, UUID()); INSERT INTO tb_order (id,money,content) VALUES(212, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(312, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(412, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(534, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(621, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(754563, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(8123, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(91213, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(23232, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(112321, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(21221, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(112132, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(12132, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(124321, 100 , UUID()); INSERT INTO tb_order (id,money,content) VALUES(212132, 100 , UUID());Copy the code

4. Date sharding algorithm

Shard by date

<tableRule name="sharding-by-date"> 
    <rule>
        <columns>create_time</columns> 
        <algorithm>sharding-by-date</algorithm> 
    </rule> 
</tableRule> 
<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate"> 
    <property name="dateFormat">yyyy-MM-dd</property> 
    <property name="sBeginDate">2020-01-01</property> 
    <property name="sEndDate">2020-12-31</property> 
    <property name="sPartionDay">10</property> 
</function>
Copy the code

Configuration description:

Note: The number of dataNode fragments in the table configured with rules must be the same as the number of fragments in the table. For example, one fragment is generated every 10 days from 2020-01-01 to 2020-12-31. A total of 37 fragments are required.

Five, one month hour algorithm

The minimum granularity is hours. There can be 24 sharding at most and 1 sharding at least in a day. The cycle starts from the beginning next month and manual data cleaning is required at the end of each month.

The configuration is as follows:

<tableRule name="sharding-by-hour"> 
    <rule>
        <columns>create_time</columns> 
        <algorithm>sharding-by-hour</algorithm> 
    </rule> 
</tableRule> 
<function name="sharding-by-hour" class="io.mycat.route.function.LatestMonthPartion"> 
    <property name="splitOneDay">24</property> 
</function>
Copy the code

Configuration description:

Six, natural month sharding algorithm

The usage scenario is partitioning by month column. Each natural month is a fragment. The configuration is as follows:

<tableRule name="sharding-by-month"> 
    <rule>
        <columns>create_time</columns> 
        <algorithm>sharding-by-month</algorithm> 
    </rule> 
</tableRule> 
<function name="sharding-by-month" class="io.mycat.route.function.PartitionByMonth"> 
    <property name="dateFormat">yyyy-MM-dd</property> 
    <property name="sBeginDate">2020-01-01</property> 
    <property name="sEndDate">2020-12-31</property> 
</function>
Copy the code

Configuration description:

7. Date range Hash algorithm

The idea is the same as that of range module sharding. First, range sharding is carried out according to the date to get the sharding group, and then according to the time hash to make the data distribution more uniform in the short term.

Advantages: Avoids data migration during capacity expansion and avoids hot issues of range fragmentation to a certain extent

Note: the date format is required to be as accurate as possible, otherwise it cannot achieve the purpose of local uniformity

<tableRule name="range-date-hash"> 
    <rule>
    <columns>create_time</columns> 
    <algorithm>range-date-hash</algorithm> 
    </rule>
</tableRule> 

<function name="range-date-hash" class="io.mycat.route.function.PartitionByRangeDateHash"> 
    <property name="dateFormat">yyyy-MM-dd HH:mm:ss</property> 
    <property name="sBeginDate">2020-01-01 00:00:00</property> 
    <property name="groupPartionSize">6</property> 
    <property name="sPartionDay">10</property>
</function>
Copy the code

Configuration description: