MyCat topic - Enumeration and segmentation of MyCat segmentation rules

1. introduction

In MyCat, there are many sharding rules, such as enumeration sharding, modulus sharding, range sharding, consistency hash sharding and so on. Each sharding rule needs to be determined according to the amount of data in the specific business of the project. Therefore, no sharding rule is very good, and no sharding rule is good. All of them are determined according to the business. This paper will give a brief introduction to one of them, which is also a relatively simple fragmentation rule - Enumeration fragmentation, and show its usage and rules through an example.

2. Implementation mode

According to the enumeration matching rules configured by partition-hash-int-enum.txt, the records that meet the conditions are allocated to the corresponding nodes, and the records that do not meet the conditions are allocated to the default nodes (in short, it is determined which database this data is placed in according to a certain value).

3. Use scenario

For example, some businesses need to be saved according to provinces or districts and counties, while national provinces and counties are fixed. At this time, you can consider using enumeration and fragmentation rules. The specific usage scenarios can be determined according to your own specific business scenarios.

4. Implementation steps

[a] Create databases and tables:

create database testEnum1;
create database testEnum2;
 
use testEnum1;
create table order_info(id int not null,name varchar(32),province varchar(32));
 
 
use testEnum2;
create table order_info(id int not null,name varchar(32),province varchar(32));


[b] Configure server.xml to specify logical library, user name and password, etc

<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="useSqlStat">0</property>  <!-- 1 To turn on real-time statistics, 0 to turn off -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1 To enable full overtime consistency detection, 0 to close -->
 
		<property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1 For opening mysql Compression protocol-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--Set simulated MySQL Version number-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
		<!--Default is type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
		<property name="processorBufferPoolType">0</property>
		<!--The default is 65535 64 K Be used for sql Maximum text length when parsing -->
		<!--<property name="maxStringLiteralLength">65535</property>-->
		<!--<property name="sequnceHandlerType">0</property>-->
		<!--<property name="backSocketNoDelay">1</property>-->
		<!--<property name="frontSocketNoDelay">1</property>-->
		<!--<property name="processorExecutor">16</property>-->
		<!--
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
		<!--Distributed transaction switch, 0 for unfiltered distributed transactions, 1 for filtered distributed transactions (if only global tables are involved in distributed transactions, then no filtering), 2 for unfiltered distributed transactions,But log distributed transactions-->
		<property name="handleDistributedTransactions">0</property>
		
			<!--
			off heap for merge/order/group/limit      1 On 0 off
		-->
		<property name="useOffHeapForMerge">1</property>
 
		<!--
			Unit is m
		-->
		<property name="memoryPageSize">1m</property>
 
		<!--
			Unit is k
		-->
		<property name="spillsFileBufferSize">1k</property>
 
		<property name="useStreamOutput">0</property>
 
		<!--
			Unit is m
		-->
		<property name="systemReserveMemorySize">384m</property>
 
 
		<!--Whether to adopt zookeeper Coordinated switching  -->
		<property name="useZKSwitch">true</property>
 
 
	</system>
	
	<!-- Overall situation SQL Firewall settings -->
	<!-- 
	<firewall> 
	   <whitehost>
	      <host host="127.0.0.1" user="mycat"/>
	      <host host="127.0.0.2" user="mycat"/>
	   </whitehost>
       <blacklist check="false">
       </blacklist>
	</firewall>
	-->
	
	<user name="root">
		<property name="password">123456</property>
		<property name="schemas">TESTENUM</property>
		
		<!-- Table level DML Permission settings -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>
 
	<user name="user">
		<property name="password">123456</property>
		<property name="schemas">TESTENUM</property>
		<property name="readOnly">true</property>
	</user>
 
</mycat:server>

[c] Configure schema.xml to specify logical table, enumerate partition rules, partition nodes, etc

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
        <schema name="TESTENUM" checkSQLschema="false" sqlMaxLimit="100">
                <table name="order_info" dataNode="dn$1-2" rule="sharding-by-intfile-enum"/>
        </schema>
 
        <dataNode name="dn1" dataHost="localhost1" database="testEnum1" />
        <dataNode name="dn2" dataHost="localhost1" database="testEnum2" />
 
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.70.128:3306" user="root" password="123">
                </writeHost>
        </dataHost>
 
</mycat:schema>

[d] Configure rule.xml to specify the fragmentation rule as enumeration fragmentation

<tableRule name="sharding-by-intfile-enum">
	<rule>
		<columns>province</columns>
		<algorithm>hash-int-enum</algorithm>
	</rule>
</tableRule>
 
<function name="hash-int-enum" class="io.mycat.route.function.PartitionByFileMap">
	<property name="mapFile">partition-hash-int-enum.txt</property>
	<property name="type">1</property>
	<property name="defaultNode">0</property>
</function>
mapFile property: segmentation rule matching profile
 Type attribute: type, 1 for string, 0 for int
 defaultNode property: the default node. If it is less than 0, the default node will not be set. If it is greater than or equal to 0, the default node will be set

[e] Configure partition-hash-int-enum.txt to specify which enumeration value corresponds to which partition node

[root@centos1 conf]# chmod 777 partition-hash-int-enum.txt 


Add custom fragment rule configuration file vim partition-hash-int-enum.txt

gz=0
sz=1

[f] Test insert data

Restart mycat and log in to mycat:

Then insert the data for testing:

insert into order_info(id,name,province) values('1111','1','gz');
insert into order_info(id,name,province) values('2222','2','sz');

According to the enumeration matching results, the normal first record should be matched to the first partition node dn1, and the second record should be saved in the dn2 node.

Logic library TESTENUM:

It can be seen that two pieces of data have been successfully inserted into the logical database. Let's see which real physical database these two pieces of data are inserted into.

testEnum1 database:

testEnum2 database:

As shown in the above two figures, the data is indeed routed according to the expected rules of enumeration and fragmentation. The data of province=gz is routed to DN1, and the data of province = SZ is routed to dn2.

If an unmatched enumeration value is encountered, the default partition node dn1 will be used, as follows:

insert into order_info(id,name,province) values('3333','3','zh');


Logic library:
testEnum1 database:

[g] Query test

explain select * from order_info where province = 'sz';

Use the fragment field to query:

Do not use fragment fields to query:

explain select * from order_info where name = '1';


It can be seen that if the fragment field is not used for query, the query sql statement will be sent to all the fragment nodes for query.

5. summary

The above is the detailed implementation steps of enumeration and fragmentation, and several points need to be noted are summarized:

a. Define rule configuration file rule.xml, specify fragment field, fragment function and mapFile;
b. When the schema.xml declares the logical table, specify rule = "xxx", which corresponds to the name of tableRule in rule.xml;
c. Add the custom partition rule configuration file VIM partition hash int enum.txt;

In addition, add the rule.xml configuration file description:

rule.xml: many kinds of segmentation algorithms and rules are defined, and the segmentation rules are formulated when the logical table is declared.
(1) tableRule label:

<tableRule name="mod-long"> 
    <rule> 
        <columns>order_id</columns> 
        <algorithm>mod-long</algorithm> 
    </rule> 
</tableRule>
Name attribute: Specifies the name of the fragmentation rule, which corresponds to the name of table label rule in schema.xml
 columns property: Specifies the specific fragment field
 Algorithm attribute: Specifies the partition function, that is, the partition algorithm, corresponding to the name of the function tag

(2) function tag

<function name="hash-int" 
class="org.opencloudb.route.function.PartitionByFileMap">                 
    <property name="mapFile">partition-hash-int.txt</property> 
</function>
Name attribute: Specifies the name of the segmentation algorithm, which is used in the algorithm attribute of the rule tag
 Class attribute: Specifies the specific implementation class name of the sharding algorithm
 Property property: specify some properties needed by the sharding algorithm
288 original articles published, 82 praised, 20000 visited+
Private letter follow

Tags: mycat Database xml Fragment

Posted on Mon, 09 Mar 2020 01:42:42 -0400 by shikhartandon