自定義數字范圍分片，提前規劃好分片字段某個范圍屬於哪個分片，比如說將第一個500W的數據分片在第一個節點上面，第二個500W的數據分片在第二個節點上，依次類推
在function.xml裡面進行配置：
<code class=" hljs xml"> <!--{cke_protected}{C}%3C!%2D%2D%20%E5%AF%B9%E8%87%AA%E5%AE%9A%E4%B9%89%E6%95%B0%E5%AD%97%E5%88%86%E7%89%87%E8%A7%84%E5%88%99rang-long-tr04%E7%9A%84%E5%AE%9A%E4%B9%89%20%2D%2D%3E-->
<function name="rang-long-04" class="org.opencloudb.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long04.txt</property>
<property name="defaultNode">0</property>
<property name="type">0</property>
</function>
<!--{cke_protected}{C}%3C!%2D%2D%20%E5%AF%B9%E8%A1%A8%E8%BF%9B%E8%A1%8C%E5%88%86%E7%89%87%E7%9A%84%E6%8F%8F%E8%BF%B0%EF%BC%8C%E5%AD%97%E6%AE%B5%E4%BB%A5%E5%8F%8A%E5%88%86%E7%89%87%E8%A7%84%E5%88%99%20%2D%2D%3E-->
<tablerule name="rang-long-tr04">
<rule>
<columns>ID</columns>
<algorithm>rang-long-04</algorithm>
</rule>
</tablerule></code>
在schema.xml裡面進行配置：
autopartition-long04.txt文件的配置：
[root@crm_idc_squid_1_11 conf]# more autopartition-long04.txt
0-500M=0
500M-1000M=1
1000M-1500M=2
You have mail in /var/spool/mail/root
[root@crm_idc_squid_1_11 conf]#
注意： 此配置非常簡單，即預先制定可能的id范圍到某個分片，所有的節點配置都是從0開始，及0代表節點1
3，建表並且錄入數據
CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
mysql> CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
Query OK, 0 rows affected (0.05 sec)
mysql>
INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
mysql> explain INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
+-----------+--------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------------------------------+
| dn21 | INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()) |
+-----------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
Query OK, 1 row affected (0.00 sec)
mysql>
4，對insert操作流程進行分析，參考mycat.log對路由過程做完整的分析查看後台mycat.log分析如下：
開始獲取連接：
02/12 21:17:13.614 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 ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())
開始建立緩存：
02/12 21:17:13.615 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/12 21:17:13.616 INFO [$_NIOREACTOR-1-RW] (DefaultLayedCachePool.java:80) -create child Cache: TESTDB_ORDER0401 for layered cache TableID2DataNodeCache, size 10000, expire seconds 18000
02/12 21:17:13.616 DEBUG [$_NIOREACTOR-1-RW] (CacheManager.java:794) -Attempting to create an existing singleton. Existing singleton returned.
02/12 21:17:13.616 DEBUG [$_NIOREACTOR-1-RW] (Cache.java:955) -No BootstrapCacheLoaderFactory class specified. Skipping...
02/12 21:17:13.617 DEBUG [$_NIOREACTOR-1-RW] (Cache.java:929) -CacheWriter factory not configured. Skipping...
02/12 21:17:13.618 DEBUG [$_NIOREACTOR-1-RW] (MemoryStore.java:153) -Initialized net.sf.ehcache.store.NotifyingMemoryStore for TableID2DataNodeCache.TESTDB_ORDER0401
02/12 21:17:13.619 DEBUG [$_NIOREACTOR-1-RW] (Cache.java:1165) -Initialised cache: TableID2DataNodeCache.TESTDB_ORDER0401
開始路由匹配：
02/12 21:17:13.621 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 ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()), route={
1 -> dn21{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}
} rrs
匹配好路由後，直接往對應的dn21節點開始執行insert數據錄入操作：
02/12 21:17:13.624 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1455283033608, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=52, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, packetId=0], host=13.217.1.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
連接釋放：
02/12 21:17:13.624 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455283033608, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=52, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.217.1.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
5，執行簡單的查詢，如果指定分片字段ID字段，則走分片查詢單個分片節點mycat命令台顯示結果：
mysql> explain select * from ORDER0401 t1 where t1.ID=5000001;
+-----------+------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------+
| dn22 | select * from ORDER0401 t1 where t1.ID=5000001 |
+-----------+------------------------------------------------+
1 row in set (0.08 sec)
mysql> select * from ORDER0401 t1 where t1.ID=5000001;
+---------+----------+-----------------------+---------------------+
| ID | PROVINCE | SN | CREATE_TIME |
+---------+----------+-----------------------+---------------------+
| 5000001 | shanghai | shanghai10006_5000001 | 2016-02-12 21:18:17 |
+---------+----------+-----------------------+---------------------+
1 row in set (0.00 sec)
mysql>
查看後台mycat日志信息：
02/12 21:44:01.116 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]select * from ORDER0401 t1 where t1.ID=5000001
02/12 21:44:01.117 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from ORDER0401 t1 where t1.ID=5000001
02/12 21:44:01.118 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/12 21:44:01.118 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:5000001
02/12 21:44:01.118 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]select * from ORDER0401 t1 where t1.ID=5000001, route={
1 -> dn22{select * from ORDER0401 t1 where t1.ID=5000001}
} rrs
02/12 21:44:01.119 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2
02/12 21:44:01.120 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=17, lastTime=1455284641106, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=59, charset=latin1, txIsolation=3, autocommit=true, attachment=dn22{select * from ORDER0401 t1 where t1.ID=5000001}, respHandler=SingleNodeHandler [node=dn22{select * from ORDER0401 t1 where t1.ID=5000001}, packetId=7], host=13.217.1.11, port=3327, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@6d1ef03, writeQueue=0, modifiedSQLExecuted=false]
02/12 21:44:01.121 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=17, lastTime=1455284641106, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=59, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.217.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
6，如果分片字段范圍的查詢，則走所有節點去檢索，哪怕只有一條數據在一個分片上，route路由也是走所有的分片進行檢索查詢
mysql> explain select * from ORDER0401 t1 where t1.ID<50000;
+-----------+----------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------+
| dn21 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
| dn22 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
+-----------+----------------------------------------------------------+
2 rows in set (0.01 sec)
mysql>
7，如果不走分片字段的查詢，即使是單個數據，也要route路由所有的分片，走所有的分片進行查詢
mysql> explain select * from ORDER0401 t1 where t1.SN='shanghai10006_10000';
+-----------+--------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------+
| dn21 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
| dn22 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
+-----------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
