Mycat discrete fragment-) specifies the fragment of the partition.

Mycat discrete fragment-) specifies the fragment of the partition.
1. partitions specified by the program

This rule is automatically routed to the shard by an application in the running stage.
This method calculates the Partition Number directly based on the Character substring (which must be a number) (the Partition Number is explicitly specified by parameters passed by the application ).

2. Add a configuration file

Configure in function. xml:

<Code class = "hljs xml"> <function name = "sharding-by-substring-040302" class = "org. opencloudb. route. function. partitionDirectBySubString "> <property name =" startIndex "> 0 </property> <property name =" size "> 1 </property> <property name =" partitionCount "> 8 </ property> <property name = "defaultPartition"> 0 </property> </function> for example, id = 0-00000020 indicates that startIndex = 0 based on the id, start with siz = 1 digit, that is, 0 is the obtained partition, if you do not pass the default to defaultPartition <tablerule name = "tr-sharding-substr-mc040302"> <rule> <columns> CODE </columns> <algorithm> sharding-by-substring-040302 </algorithm> </rule> </ tablerule> </code>

Configure in schema. xml:

3. Create a table and input data

 CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME); mysql> CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW()); INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()); mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW()); +-----------+----------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+----------------------------------------------------------------------------------------------------+ | dn21 | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW()) | +-----------+----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW()); Query OK, 1 row affected (0.01 sec) mysql> mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()); +-----------+-----------------------------------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+-----------------------------------------------------------------------------------------------------+ | dn22 | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()) | +-----------+-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()); Query OK, 1 row affected (0.00 sec) mysql> 

4. Analyze the insert operation process. Refer to mycat. log for a complete analysis of the routing process.

View the background mycat. log analysis as follows:

Start to get the connection: 02/13 00:40:18. 173 DEBUG [$ _ NIOREACTOR-1-RW] (ServerQueryHandler. java: 56)-ServerConnection [id = 1, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] insert into ORDER040302 (ID, CODE, SN, CREATE_TIME) VALUES (2, '1-10000020 ', 'shanghai_10-10000020', NOW () Start route matching: 02/13 00:40:18. 175 DEBUG [$ _ NIOREACTOR-1-RW] (NonBlockingSession. java: 113)-ServerConnection [id = 1, schema = TESTDB, host = 127.0.0.1, user = test, txIsolation = 3, autocommit = true, schema = TESTDB] insert into ORDER040302 (ID, CODE, SN, CREATE_TIME) VALUES (2, '1-10000020 ', 'shanghai_10-10000020', NOW ()), route = {1-> dn22 {insert into ORDER040302 (ID, CODE, SN, CREATE_TIME) VALUES (2, '1-10000020 ', 'shanghai _ 10-10000020 ', NOW ()} after a route is matched, insert data into the corresponding dn21 node: 02/13 00:40:18. 180 DEBUG [$ _ NIOREACTOR-1-RW] (NonBlockingSession. java: 229)-release connection MySQLConnection [id = 17, lastTime = 1455295218162, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 199, charset = latin1, txIsolation = 3, autocommit = true, attachment = dn22 {insert into ORDER040302 (ID, CODE, SN, CREATE_TIME) VALUES (2, '1-10000020 ', 'shanghai_10-10000020', NOW ()}, respHandler = SingleNodeHandler [node = dn22 {insert into ORDER040302 (ID, CODE, SN, CREATE_TIME) VALUES (2, '1-10000020 ', 'shanghai_10-10000020', NOW ()}, packetId = 0], host = 10.254.1.11, port = 3327, statusSync = null, writeQueue = 0, modifiedSQLExecuted = true] connection release: 02/13 00:40:18. 180 DEBUG [$ _ NIOREACTOR-1-RW] (PhysicalDatasource. java: 403)-release channel MySQLConnection [id = 17, lastTime = 1455295218162, user = root, schema = db3, old shema = db3, borrowed = true, fromSlaveDB = false, threadId = 199, charset = latin1, txIsolation = 3, autocommit = true, attachment = null, respHandler = null, host = 10.254.1.11, port = 3327, statusSync = null, writeQueue = 0, modifiedSQLExecuted = false] 02/13 00:40:24. 390 DEBUG [Timer1] (SQLJob. java: 85)-con query s

5. Execute a simple query. If the shard field ID field is specified, query a single shard node through the shard.

Result of mycat command:

 mysql> explain select * from ORDER040302 t1 where CODE= '0-10000020'; +-----------+------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+------------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER040302 t1 WHERE CODE = '0-10000020' LIMIT 100 | +-----------+------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from ORDER040302 t1 where CODE= '0-10000020'; +----+------------+---------------------+---------------------+ | ID | CODE | SN | CREATE_TIME | +----+------------+---------------------+---------------------+ | 1 | 0-10000020 | beijing_03-10000020 | 2016-02-13 00:40:27 | +----+------------+---------------------+---------------------+ 1 row in set (0.01 sec) mysql>

View the background mycat log information:

 02/13 00:44:46.717 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]explain select * from ORDER040302 t1 where CODE= '0-10000020' 02/13 00:44:46.718 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select * from ORDER040302 t1 where CODE= '0-10000020' 02/13 00:44:46.719 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER040302 t1 where CODE= '0-10000020' value:select * from ORDER040302 t1 where CODE= '0-10000020', route={ 1 -> dn21{SELECT * FROM ORDER040302 t1 WHERE CODE = '0-10000020' LIMIT 100} }

6. If the range of shard fields is queried, all nodes are used for retrieval. Even if only one piece of data is located on one Shard, the route also uses all shards for retrieval and query.

 mysql> explain select * from ORDER040302 t1 where CODE likt '0-1%'; +-----------+---------------------------------------------------+ | DATA_NODE | SQL | +-----------+---------------------------------------------------+ | dn21 | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 | | dn22 | SELECT * FROM ORDER040302 t1 WHERE CODE LIMIT 100 | +-----------+---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> 

7. If you do not need to query the shard field, even a single data, you must route all shards and query them using all shards.

 mysql> explain select * from ORDER040302 t1 where SN='beijing_03-10000020'; +-----------+-------------------------------------------------------------------------+ | DATA_NODE | SQL | +-----------+-------------------------------------------------------------------------+ | dn21 | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03-10000020' LIMIT 100 | | dn22 | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03-10000020' LIMIT 100 | +-----------+-------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>