1，程序指定分区的分片
此规则是在运行阶段有应用自主决定路由到那个分片。
此方法为直接根据字符子串（必须是数字）计算分区号（由应用传递参数，显式指定分区号）。
2，添加配置文件
在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，建表并且录入数据
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对路由过程做完整的分析
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 s5，执行简单的查询，如果指定分片字段ID字段，则走分片查询单个分片节点
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路由也是走所有的分片进行检索查询
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路由所有的分片，走所有的分片进行查询
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>