mycat 离散分片(程序指定分区的分片)

mycat 离散分片(程序指定分区的分片)
1：程序指定分区的分片
此规则是在运行阶段有应用自主决定路由到那个分片。 
此方法为直接根据字符子串（必须是数字）计算分区号（由应用传递参数，显式指定分区号）。

2：添加配置文件
在rule.xml里面进行配置：
        <tableRule name="tr-sharding-substr-mc040302">
                 <rule>
                       <columns>CODE</columns>
                       <algorithm>sharding-by-substring-040302</algorithm>
                </rule>
        </tableRule>

        <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


在schema.xml里面进行配置：
    <table name="ORDER040302" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="tr-sharding-substr-mc040302"/>
    
[root@mycat1 ~]# /usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...

[root@mycat1 logs]# tail -800 wrapper.log 
STATUS | wrapper  | 2016/02/25 22:43:18 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2016/02/25 22:43:18 | Launching a JVM...
INFO   | jvm 1    | 2016/02/25 22:43:21 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2016/02/25 22:43:21 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2016/02/25 22:43:21 | 
INFO   | jvm 1    | 2016/02/25 22:43:22 | log4j 2016-02-25 22:43:22 [./conf/log4j.xml] load completed.
INFO   | jvm 1    | 2016/02/25 22:43:22 | MyCAT Server startup successfully. see logs in logs/mycat.log

3：建表并且录入数据
[root@mycat1 ~]# mysql -uroot -poracle -h192.168.10.21 -P8066 -DTESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> explain CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME);
+-----------+---------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                       |
+-----------+---------------------------------------------------------------------------------------------------------------------------+
| dn1       | CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn2       | CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn3       | CREATE TABLE ORDER040302(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,CODE VARCHAR(64),SN VARCHAR(64),CREATE_TIME DATETIME) |
+-----------+---------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.04 sec)

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.37 sec)

mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());
+-----------+----------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                |
+-----------+----------------------------------------------------------------------------------------------------+
| dn1       | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW()) |
+-----------+----------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(1,'0-10000020','beijing_03-10000020',NOW());
Query OK, 1 row affected (0.06 sec)

mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());
+-----------+-----------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                 |
+-----------+-----------------------------------------------------------------------------------------------------+
| dn2       | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW()) |
+-----------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(2,'1-10000020','shanghai_10-10000020',NOW());
Query OK, 1 row affected (0.06 sec)

mysql> explain INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW());
+-----------+------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                  |
+-----------+------------------------------------------------------------------------------------------------------+
| dn3       | INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW()) |
+-----------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW());
Query OK, 1 row affected (0.05 sec)

4：对insert操作流程进行分析，参考mycat.log对路由过程做完整的分析
开始获取连接：
02/25 23:02:51.987  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW())
开始路由匹配：
02/25 23:02:51.988  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW()), route={
   1 -> dn3{INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW())}
} rrs 
匹配好路由后，直接往对应的dn3节点开始执行insert数据录入操作：
02/25 23:02:52.041  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=4, lastTime=1456412571986, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=60, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW())}, respHandler=SingleNodeHandler [node=dn3{INSERT INTO ORDER040302(ID,CODE,SN,CREATE_TIME) VALUES(3,'2-10000020','guangzhou_20-10000020',NOW())}, packetId=0], host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
连接释放：
02/25 23:02:52.041  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=4, lastTime=1456412571986, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=60, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

5：执行简单的查询，如果指定分片字段ID字段，则走分片查询单个分片节点
mysql> explain select * from ORDER040302 t1 where CODE= '0-10000020';
+-----------+------------------------------------------------------------------+
| DATA_NODE | SQL                                                              |
+-----------+------------------------------------------------------------------+
| dn1       | SELECT * FROM ORDER040302 t1 WHERE CODE = '0-10000020' LIMIT 100 |
+-----------+------------------------------------------------------------------+
1 row in set (0.07 sec)

mysql> select * from ORDER040302 t1 where CODE= '0-10000020';
+----+------------+---------------------+---------------------+
| ID | CODE       | SN                  | CREATE_TIME         |
+----+------------+---------------------+---------------------+
|  1 | 0-10000020 | beijing_03-10000020 | 2016-02-25 22:52:20 |
+----+------------+---------------------+---------------------+
1 row in set (0.01 sec)

查看mycat.log：
02/25 23:41:31.944  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER040302 t1 where CODE= '0-10000020'
02/25 23:41:31.944  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:TESTDBselect * from ORDER040302 t1 where CODE= '0-10000020'
02/25 23:41:31.946  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect * from ORDER040302 t1 where CODE= '0-10000020' value:select * from ORDER040302 t1 where CODE= '0-10000020', route={
   1 -> dn1{SELECT *
FROM ORDER040302 t1
WHERE CODE = '0-10000020'
LIMIT 100}
}

6：如果分片字段范围的查询，则走所有节点去检索，哪怕只有一条数据在一个分片上，route路由也是走所有的分片进行检索查询
mysql> explain select * from ORDER040302 where CODE like '0-1%';
+-----------+------------------------------------------------------------+
| DATA_NODE | SQL                                                        |
+-----------+------------------------------------------------------------+
| dn1       | SELECT * FROM ORDER040302 WHERE CODE LIKE '0-1%' LIMIT 100 |
| dn2       | SELECT * FROM ORDER040302 WHERE CODE LIKE '0-1%' LIMIT 100 |
| dn3       | SELECT * FROM ORDER040302 WHERE CODE LIKE '0-1%' LIMIT 100 |
+-----------+------------------------------------------------------------+
3 rows in set (0.00 sec)

7：如果不走分片字段的查询，即使是单个数据，也要route路由所有的分片，走所有的分片进行查询
mysql> explain select * from ORDER040302 t1 where SN='beijing_03-10000020';
+-----------+-------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                     |
+-----------+-------------------------------------------------------------------------+
| dn1       | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03-10000020' LIMIT 100 |
| dn2       | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03-10000020' LIMIT 100 |
| dn3       | SELECT * FROM ORDER040302 t1 WHERE SN = 'beijing_03-10000020' LIMIT 100 |
+-----------+-------------------------------------------------------------------------+
3 rows in set (0.03 sec)