此規則是在運行階段有應用自主決定路由到那個分片。
此方法為直接根據字符子串（必須是數字）計算分區號（由應用傳遞參數，顯式指定分區號）。
在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>
例如id=0-100000020在此配置中代表根據id中從startIndex=0，開始，截取siz=1位數字即0，0就是獲取的分區，如果沒傳默認分配到defaultPartition
<tablerule name="tr-sharding-substr-mc040302">
<rule>
<columns>CODE</columns>
<algorithm>sharding-by-substring-040302</algorithm>
</rule>
</tablerule></code>
在schema.xml裡面進行配置：
3，建表並且錄入數據
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，對insert操作流程進行分析，參考mycat.log對路由過程做完整的分析查看後台mycat.log分析如下：
開始獲取連接：
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())
開始路由匹配：
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())}
匹配好路由後，直接往對應的dn21節點開始執行insert數據錄入操作：
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]
連接釋放：
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，執行簡單的查詢，如果指定分片字段ID字段，則走分片查詢單個分片節點mycat命令台顯示結果：
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>
查看後台mycat日志信息：
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，如果分片字段范圍的查詢，則走所有節點去檢索，哪怕只有一條數據在一個分片上，route路由也是走所有的分片進行檢索查詢
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，如果不走分片字段的查詢，即使是單個數據，也要route路由所有的分片，走所有的分片進行查詢
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>
