枚舉分片：通過在配置文件中配置可能的枚舉id，自己配置分片，本規則適用於特定的場景，比如有些業務需要按照省份或區縣來做保存，而全國省份區縣固定的
在function.xml裡面進行配置：
<code class=" hljs xml"> <!--{cke_protected}{C}%3C!%2D%2D%20%0A%20%20%20%20mapFile%E6%A0%87%E8%AF%86%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6%E5%90%8D%E7%A7%B0%0A%20%20%20%20type%E9%BB%98%E8%AE%A4%E5%80%BC%E4%B8%BA0%EF%BC%880%E8%A1%A8%E7%A4%BAInteger%EF%BC%8C%E9%9D%9E%E9%9B%B6%E8%A1%A8%E7%A4%BAString%EF%BC%89%0A%20%20%20%20%E9%BB%98%E8%AE%A4%E8%8A%82%E7%82%B9%E7%9A%84%E4%BD%9C%E7%94%A8%EF%BC%9A%E6%9E%9A%E4%B8%BE%E5%88%86%E7%89%87%E6%97%B6%EF%BC%8C%E5%A6%82%E6%9E%9C%E7%A2%B0%E5%88%B0%E4%B8%8D%E8%AF%86%E5%88%AB%E7%9A%84%E6%9E%9A%E4%B8%BE%E5%80%BC%EF%BC%8C%E5%B0%B1%E8%AE%A9%E5%AE%83%E8%B7%AF%E7%94%B1%E5%88%B0%E9%BB%98%E8%AE%A4%E8%8A%82%E7%82%B9%20%20%20%20%0A%20%20%20%20%2D%2D%3E-->
<function name="hash-int-mc040301" class="org.opencloudb.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="defaultNode">0</property>
<property name="type">0</property>
</function>
<tablerule name="tr-hash-int-mc040301">
<rule>
<columns>PROVINCE</columns>
<algorithm>hash-int-mc040301</algorithm>
</rule>
</tablerule></code>
在schema.xml裡面進行配置：
autopartition-long04.txt文件的配置, 10000代表北京分片到第一個節點，10010代表上海分片到第二個節點
[root@crm_1_21 conf]# more partition-hash-int04.txt
10000=0
10010=1
[root@crm_1_21 conf]#
注意： 此配置非常簡單，即預先制定可能的id范圍到某個分片，所有的節點配置都是從0開始，及0代表節點1
3，建表並且錄入數據
CREATE TABLE ORDER040301(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE INT,SN VARCHAR(64),CREATE_TIME DATETIME);
mysql> CREATE TABLE ORDER040301(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE INT,SN VARCHAR(64),CREATE_TIME DATETIME);
Query OK, 0 rows affected (0.05 sec)
mysql>
INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW());
INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW());
mysql> explain INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW());
+-----------+-------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------------------------+
| dn21 | INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW()) |
+-----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.12 sec)
mysql> INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,10000,'beijing_10006_10000',NOW());
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> explain INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW());
+-----------+--------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------------------+
| dn22 | INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW()) |
+-----------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW());
Query OK, 1 row affected (0.00 sec)
mysql>
4，對insert操作流程進行分析，參考mycat.log對路由過程做完整的分析查看後台mycat.log分析如下：
開始獲取連接：
02/12 23:32:11.323 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW())
開始路由匹配：
02/12 23:32:11.324 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW()), route={
1 -> dn22{INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW())}
} rrs
匹配好路由後，直接往對應的dn21節點開始執行insert數據錄入操作：
02/12 23:32:11.327 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=16, lastTime=1455291131321, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=153, charset=latin1, txIsolation=3, autocommit=true, attachment=dn22{INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW())}, respHandler=SingleNodeHandler [node=dn22{INSERT INTO ORDER040301(ID,PROVINCE,SN,CREATE_TIME) VALUES(2,10010,'shanghai_10010',NOW())}, packetId=0], host=13.123.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
連接釋放：
02/12 23:32:11.328 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=16, lastTime=1455291131321, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=153, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.123.1.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
5，執行簡單的查詢，如果指定分片字段ID字段，則走分片查詢單個分片節點mycat命令台顯示結果：
mysql> explain select * from ORDER040301 t1 where PROVINCE=10000;
+-----------+---------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------------+
| dn21 | SELECT * FROM ORDER040301 t1 WHERE PROVINCE = 10000 LIMIT 100 |
+-----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from ORDER040301 t1 where PROVINCE=10000;
+----+----------+---------------------+---------------------+
| ID | PROVINCE | SN | CREATE_TIME |
+----+----------+---------------------+---------------------+
| 1 | 10000 | beijing_10006_10000 | 2016-02-12 23:31:55 |
+----+----------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql>
查看後台mycat日志信息：
02/12 23:36:08.302 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER040301 t1 where PROVINCE=10000
02/12 23:36:08.303 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from ORDER040301 t1 where PROVINCE=10000
02/12 23:36:08.304 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect * from ORDER040301 t1 where PROVINCE=10000 value:select * from ORDER040301 t1 where PROVINCE=10000, route={
1 -> dn21{SELECT *
FROM ORDER040301 t1
WHERE PROVINCE = 10000
LIMIT 100}
}
02/12 23:36:08.305 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER040301 t1 where PROVINCE=10000, route={
1 -> dn21{SELECT *
FROM ORDER040301 t1
WHERE PROVINCE = 10000
LIMIT 100}
} rrs
02/12 23:36:08.305 DEBUG [$_NIOREACTOR-2-RW] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1
02/12 23:36:08.307 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=5, lastTime=1455291368304, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=161, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{SELECT *
FROM ORDER040301 t1
WHERE PROVINCE = 10000
LIMIT 100}, respHandler=SingleNodeHandler [node=dn21{SELECT *
FROM ORDER040301 t1
WHERE PROVINCE = 10000
LIMIT 100}, packetId=7], host=13.123.1.11, port=3317, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@1cfa73be, writeQueue=0, modifiedSQLExecuted=false]
02/12 23:36:08.308 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=5, lastTime=1455291368304, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=161, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=13.123.1.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
6，如果分片字段范圍的查詢，則走所有節點去檢索，哪怕只有一條數據在一個分片上，route路由也是走所有的分片進行檢索查詢
mysql> explain select * from ORDER040301 t1 where SN='beijing_10006_10000';
+-----------+-------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------+
| dn21 | SELECT * FROM ORDER040301 t1 WHERE SN = 'beijing_10006_10000' LIMIT 100 |
| dn22 | SELECT * FROM ORDER040301 t1 WHERE SN = 'beijing_10006_10000' LIMIT 100 |
+-----------+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from ORDER040301 t1 where SN='beijing_10006_10000';
+----+----------+---------------------+---------------------+
| ID | PROVINCE | SN | CREATE_TIME |
+----+----------+---------------------+---------------------+
| 1 | 10000 | beijing_10006_10000 | 2016-02-12 23:31:55 |
+----+----------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql>
7，如果不走分片字段的查詢，即使是單個數據，也要route路由所有的分片，走所有的分片進行查詢
mysql> explain select * from ORDER040301 t1 where PROVINCE < 10010;
+-----------+---------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------------+
| dn21 | SELECT * FROM ORDER040301 t1 WHERE PROVINCE < 10010 LIMIT 100 |
| dn22 | SELECT * FROM ORDER040301 t1 WHERE PROVINCE < 10010 LIMIT 100 |
+-----------+---------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from ORDER040301 t1 where PROVINCE < 10010;
+----+----------+---------------------+---------------------+
| ID | PROVINCE | SN | CREATE_TIME |
+----+----------+---------------------+---------------------+
| 1 | 10000 | beijing_10006_10000 | 2016-02-12 23:31:55 |
+----+----------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql>
