Mycat连续分片(自定义数字范围分片)学习
1:自定义数字范围分片
自定义数字范围分片,提前规划好分片字段某个范围属于哪个分片,比如说将第一个500W的数据分片在第一个节点上面,
第二个500W的数据分片在第二个节点上,依次类推
2:添加配置
在schema.xml里面进行配置:
<table name="ORDER0401" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="rang-long-tr04"/>
在function.xml里面进行配置:
<!-- 对表进行分片的描述,字段以及分片规则 -->
<tableRule name="rang-long-tr04">
<rule>
<columns>ID</columns>
<algorithm>rang-long-04</algorithm>
</rule>
</tableRule>
<!-- 对自定义数字分片规则rang-long-tr04的定义 -->
<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>
0-500M=0
500M-1000M=1
1000M-1500M=2
注意: 此配置非常简单,即预先制定可能的id范围到某个分片,所有的节点配置都是从0开始,及0代表节点1
重启mycat,或者reload:
[root@mycat1 conf]# /usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
查看相关日志:
[root@mycat1 logs]# tail -80 wrapper.log
STATUS | wrapper | 2016/02/22 22:52:55 | --> Wrapper Started as Daemon
STATUS | wrapper | 2016/02/22 22:52:56 | Launching a JVM...
INFO | jvm 1 | 2016/02/22 22:52:58 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2016/02/22 22:52:58 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2016/02/22 22:52:58 |
INFO | jvm 1 | 2016/02/22 22:52:59 | log4j 2016-02-22 22:52:59 [./conf/log4j.xml] load completed.
INFO | jvm 1 | 2016/02/22 22:52:59 | MyCAT Server startup successfully. see logs in logs/mycat.log
3:建表并且录入数据
[root@mycat1 conf]# 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 ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
+-----------+-----------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------------------------------------+
| dn1 | CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn2 | CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn3 | CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
+-----------+-----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.03 sec)
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.35 sec)
mysql> EXPLAIN INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
+-----------+--------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------------------------------+
| dn1 | INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()) |
+-----------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.13 sec)
mysql> EXPLAIN INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
+-----------+------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------------------------------------------------------------+
| dn2 | INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',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.06 sec)
mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
Query OK, 1 row affected (0.06 sec)
4:对insert操作流程进行分析,参考mycat.log对路由过程做完整的分析
查看、分析mycat.log:
开始获取连接
02/22 23:20:14.464 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())
开始建立缓存
02/22 23:20:14.465 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/22 23:20:14.466 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:10001
开始匹配路由
02/22 23:20:14.466 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()), route={
1 -> dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}
} rrs
匹配好路由后,直接往对应的dn1节点开始执行insert数据录入操作:
02/22 23:20:14.470 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 2 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=1, lastTime=1456154414469, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=58, charset=latin1, txIsolation=0, autocommit=true, attachment=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, respHandler=SingleNodeHandler [node=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
连接释放
02/22 23:20:14.518 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=1, lastTime=1456154414461, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=58, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, respHandler=SingleNodeHandler [node=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/22 23:20:14.522 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=1, lastTime=1456154414461, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=58, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:20:15.931 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())
02/22 23:20:15.933 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/22 23:20:15.933 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:5000001
02/22 23:20:15.934 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW()), route={
1 -> dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}
} rrs
02/22 23:20:15.934 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 2 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=10, lastTime=1456154415934, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=61, charset=latin1, txIsolation=0, autocommit=true, attachment=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, respHandler=SingleNodeHandler [node=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/22 23:20:15.994 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=10, lastTime=1456154415916, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=61, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, respHandler=SingleNodeHandler [node=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
5:执行查询,如果指定分片字段ID字段,则走分片查询单个分片节点
mysql> explain select * from ORDER0401 t1 where t1.ID=5000001;
+-----------+------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------+
| dn2 | select * from ORDER0401 t1 where t1.ID=5000001 |
+-----------+------------------------------------------------+
1 row in set (0.06 sec)
mysql> select * from ORDER0401 t1 where t1.ID=5000001;
+---------+----------+-----------------------+---------------------+
| ID | PROVINCE | SN | CREATE_TIME |
+---------+----------+-----------------------+---------------------+
| 5000001 | shanghai | shanghai10006_5000001 | 2016-02-22 23:20:14 |
+---------+----------+-----------------------+---------------------+
1 row in set (0.00 sec)
查看mycat.log:
02/22 23:41:12.221 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER0401 t1 where t1.ID=5000001
02/22 23:41:12.221 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from ORDER0401 t1 where t1.ID=5000001
02/22 23:41:12.222 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/22 23:41:12.223 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:5000001
02/22 23:41:12.223 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER0401 t1 where t1.ID=5000001, route={
1 -> dn2{select * from ORDER0401 t1 where t1.ID=5000001}
} rrs
02/22 23:41:12.223 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source mysql2 for dataHost:mysql1
02/22 23:41:12.226 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 3 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:true con:MySQLConnection [id=11, lastTime=1456155672225, user=root, schema=db2, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=0, autocommit=true, attachment=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, respHandler=SingleNodeHandler [node=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, packetId=0], host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:41:12.228 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=11, lastTime=1456155672205, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, respHandler=SingleNodeHandler [node=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, packetId=7], host=192.168.10.23, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@5fc9ead7, writeQueue=0, modifiedSQLExecuted=false]
6:如果分片字段范围的查询,则走所有节点去检索,哪怕只有一条数据在一个分片上,route路由也是走所有的分片进行检索查询
mysql> explain select * from ORDER0401 t1 where t1.ID<50000;
+-----------+----------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------+
| dn1 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
| dn2 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
| dn3 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
+-----------+----------------------------------------------------------+
3 rows in set (0.04 sec)
查看mycat.log:
02/22 23:46:03.383 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]explain select * from ORDER0401 t1 where t1.ID<50000
02/22 23:46:03.384 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select * from ORDER0401 t1 where t1.ID<50000
02/22 23:46:03.413 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER0401 t1 where t1.ID<50000 value:select * from ORDER0401 t1 where t1.ID<50000, route={
1 -> dn1{SELECT *
FROM ORDER0401 t1
WHERE t1.ID < 50000
LIMIT 100}
2 -> dn2{SELECT *
FROM ORDER0401 t1
WHERE t1.ID < 50000
LIMIT 100}
3 -> dn3{SELECT *
FROM ORDER0401 t1
WHERE t1.ID < 50000
LIMIT 100}
}
02/22 23:46:10.508 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=11, lastTime=1456155970508, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:46:10.509 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=14, lastTime=1456155970509, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=64, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:46:10.510 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=14, lastTime=1456155970495, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=64, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:46:10.552 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1456155970495, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
7:如果不走分片字段的查询,即使是单个数据,也要route路由所有的分片,走所有的分片进行查询
mysql> explain select * from ORDER0401 t1 where t1.SN='shanghai10006_10000';
+-----------+--------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------+
| dn1 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
| dn2 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
| dn3 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
+-----------+--------------------------------------------------------------------------+
3 rows in set (0.01 sec)
查看mycat.log:
02/22 23:49:37.004 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]explain select * from ORDER0401 t1 where t1.SN='shanghai10006_10000'
02/22 23:49:37.005 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select * from ORDER0401 t1 where t1.SN='shanghai10006_10000'
02/22 23:49:37.007 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER0401 t1 where t1.SN='shanghai10006_10000' value:select * from ORDER0401 t1 where t1.SN='shanghai10006_10000', route={
1 -> dn1{SELECT *
FROM ORDER0401 t1
WHERE t1.SN = 'shanghai10006_10000'
LIMIT 100}
2 -> dn2{SELECT *
FROM ORDER0401 t1
WHERE t1.SN = 'shanghai10006_10000'
LIMIT 100}
3 -> dn3{SELECT *
FROM ORDER0401 t1
WHERE t1.SN = 'shanghai10006_10000'
LIMIT 100}
}
02/22 23:49:40.554 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=11, lastTime=1456156180554, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:49:40.554 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=14, lastTime=1456156180554, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=64, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:49:40.556 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1456156180554, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
1:自定义数字范围分片
自定义数字范围分片,提前规划好分片字段某个范围属于哪个分片,比如说将第一个500W的数据分片在第一个节点上面,
第二个500W的数据分片在第二个节点上,依次类推
2:添加配置
在schema.xml里面进行配置:
<table name="ORDER0401" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="rang-long-tr04"/>
在function.xml里面进行配置:
<!-- 对表进行分片的描述,字段以及分片规则 -->
<tableRule name="rang-long-tr04">
<rule>
<columns>ID</columns>
<algorithm>rang-long-04</algorithm>
</rule>
</tableRule>
<!-- 对自定义数字分片规则rang-long-tr04的定义 -->
<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>
more autopartition-long04.txt
0-500M=0
500M-1000M=1
1000M-1500M=2
注意: 此配置非常简单,即预先制定可能的id范围到某个分片,所有的节点配置都是从0开始,及0代表节点1
重启mycat,或者reload:
[root@mycat1 conf]# /usr/local/mycat/bin/mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
查看相关日志:
[root@mycat1 logs]# tail -80 wrapper.log
STATUS | wrapper | 2016/02/22 22:52:55 | --> Wrapper Started as Daemon
STATUS | wrapper | 2016/02/22 22:52:56 | Launching a JVM...
INFO | jvm 1 | 2016/02/22 22:52:58 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2016/02/22 22:52:58 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2016/02/22 22:52:58 |
INFO | jvm 1 | 2016/02/22 22:52:59 | log4j 2016-02-22 22:52:59 [./conf/log4j.xml] load completed.
INFO | jvm 1 | 2016/02/22 22:52:59 | MyCAT Server startup successfully. see logs in logs/mycat.log
3:建表并且录入数据
[root@mycat1 conf]# 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 ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);
+-----------+-----------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------------------------------------+
| dn1 | CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn2 | CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
| dn3 | CREATE TABLE ORDER0401(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME) |
+-----------+-----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.03 sec)
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.35 sec)
mysql> EXPLAIN INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW());
+-----------+--------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------------------------------+
| dn1 | INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()) |
+-----------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.13 sec)
mysql> EXPLAIN INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
+-----------+------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------------------------------------------------------------+
| dn2 | INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',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.06 sec)
mysql> INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW());
Query OK, 1 row affected (0.06 sec)
4:对insert操作流程进行分析,参考mycat.log对路由过程做完整的分析
查看、分析mycat.log:
开始获取连接
02/22 23:20:14.464 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())
开始建立缓存
02/22 23:20:14.465 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/22 23:20:14.466 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:10001
开始匹配路由
02/22 23:20:14.466 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW()), route={
1 -> dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}
} rrs
匹配好路由后,直接往对应的dn1节点开始执行insert数据录入操作:
02/22 23:20:14.470 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 2 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=1, lastTime=1456154414469, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=58, charset=latin1, txIsolation=0, autocommit=true, attachment=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, respHandler=SingleNodeHandler [node=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
连接释放
02/22 23:20:14.518 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=1, lastTime=1456154414461, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=58, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, respHandler=SingleNodeHandler [node=dn1{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'shanghai','shanghai10006_10000',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/22 23:20:14.522 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=1, lastTime=1456154414461, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=58, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:20:15.931 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())
02/22 23:20:15.933 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/22 23:20:15.933 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:5000001
02/22 23:20:15.934 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW()), route={
1 -> dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}
} rrs
02/22 23:20:15.934 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 2 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=10, lastTime=1456154415934, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=61, charset=latin1, txIsolation=0, autocommit=true, attachment=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, respHandler=SingleNodeHandler [node=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/22 23:20:15.994 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=10, lastTime=1456154415916, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=61, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, respHandler=SingleNodeHandler [node=dn2{INSERT INTO ORDER0401(ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'shanghai','shanghai10006_5000001',NOW())}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
5:执行查询,如果指定分片字段ID字段,则走分片查询单个分片节点
mysql> explain select * from ORDER0401 t1 where t1.ID=5000001;
+-----------+------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------+
| dn2 | select * from ORDER0401 t1 where t1.ID=5000001 |
+-----------+------------------------------------------------+
1 row in set (0.06 sec)
mysql> select * from ORDER0401 t1 where t1.ID=5000001;
+---------+----------+-----------------------+---------------------+
| ID | PROVINCE | SN | CREATE_TIME |
+---------+----------+-----------------------+---------------------+
| 5000001 | shanghai | shanghai10006_5000001 | 2016-02-22 23:20:14 |
+---------+----------+-----------------------+---------------------+
1 row in set (0.00 sec)
查看mycat.log:
02/22 23:41:12.221 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER0401 t1 where t1.ID=5000001
02/22 23:41:12.221 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from ORDER0401 t1 where t1.ID=5000001
02/22 23:41:12.222 DEBUG [$_NIOREACTOR-1-RW] (RouterUtil.java:951) -try to find cache by primary key
02/22 23:41:12.223 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -TableID2DataNodeCache.TESTDB_ORDER0401 miss cache ,key:5000001
02/22 23:41:12.223 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]select * from ORDER0401 t1 where t1.ID=5000001, route={
1 -> dn2{select * from ORDER0401 t1 where t1.ID=5000001}
} rrs
02/22 23:41:12.223 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source mysql2 for dataHost:mysql1
02/22 23:41:12.226 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 3 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:true con:MySQLConnection [id=11, lastTime=1456155672225, user=root, schema=db2, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=0, autocommit=true, attachment=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, respHandler=SingleNodeHandler [node=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, packetId=0], host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:41:12.228 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=11, lastTime=1456155672205, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, respHandler=SingleNodeHandler [node=dn2{select * from ORDER0401 t1 where t1.ID=5000001}, packetId=7], host=192.168.10.23, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@5fc9ead7, writeQueue=0, modifiedSQLExecuted=false]
6:如果分片字段范围的查询,则走所有节点去检索,哪怕只有一条数据在一个分片上,route路由也是走所有的分片进行检索查询
mysql> explain select * from ORDER0401 t1 where t1.ID<50000;
+-----------+----------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------+
| dn1 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
| dn2 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
| dn3 | SELECT * FROM ORDER0401 t1 WHERE t1.ID < 50000 LIMIT 100 |
+-----------+----------------------------------------------------------+
3 rows in set (0.04 sec)
查看mycat.log:
02/22 23:46:03.383 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]explain select * from ORDER0401 t1 where t1.ID<50000
02/22 23:46:03.384 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select * from ORDER0401 t1 where t1.ID<50000
02/22 23:46:03.413 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER0401 t1 where t1.ID<50000 value:select * from ORDER0401 t1 where t1.ID<50000, route={
1 -> dn1{SELECT *
FROM ORDER0401 t1
WHERE t1.ID < 50000
LIMIT 100}
2 -> dn2{SELECT *
FROM ORDER0401 t1
WHERE t1.ID < 50000
LIMIT 100}
3 -> dn3{SELECT *
FROM ORDER0401 t1
WHERE t1.ID < 50000
LIMIT 100}
}
02/22 23:46:10.508 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=11, lastTime=1456155970508, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:46:10.509 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=14, lastTime=1456155970509, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=64, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:46:10.510 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=14, lastTime=1456155970495, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=64, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:46:10.552 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1456155970495, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
7:如果不走分片字段的查询,即使是单个数据,也要route路由所有的分片,走所有的分片进行查询
mysql> explain select * from ORDER0401 t1 where t1.SN='shanghai10006_10000';
+-----------+--------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------+
| dn1 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
| dn2 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
| dn3 | SELECT * FROM ORDER0401 t1 WHERE t1.SN = 'shanghai10006_10000' LIMIT 100 |
+-----------+--------------------------------------------------------------------------+
3 rows in set (0.01 sec)
查看mycat.log:
02/22 23:49:37.004 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=TESTDB]explain select * from ORDER0401 t1 where t1.SN='shanghai10006_10000'
02/22 23:49:37.005 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select * from ORDER0401 t1 where t1.SN='shanghai10006_10000'
02/22 23:49:37.007 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select * from ORDER0401 t1 where t1.SN='shanghai10006_10000' value:select * from ORDER0401 t1 where t1.SN='shanghai10006_10000', route={
1 -> dn1{SELECT *
FROM ORDER0401 t1
WHERE t1.SN = 'shanghai10006_10000'
LIMIT 100}
2 -> dn2{SELECT *
FROM ORDER0401 t1
WHERE t1.SN = 'shanghai10006_10000'
LIMIT 100}
3 -> dn3{SELECT *
FROM ORDER0401 t1
WHERE t1.SN = 'shanghai10006_10000'
LIMIT 100}
}
02/22 23:49:40.554 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=11, lastTime=1456156180554, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:49:40.554 DEBUG [Timer1] (SQLJob.java:85) -con query sql:show slave status to con:MySQLConnection [id=14, lastTime=1456156180554, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=64, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:49:40.556 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1456156180554, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=25, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/22 23:49:40.556 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=14, lastTime=1456156180554, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=64, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
参考文档:
http://blog.csdn.net/mchdba/article/details/50656337