Mycat之——自定义数字范围分片

转载请注明出处：http://blog.csdn.net/l1028386804/article/details/77016539

一、简单描述

自定义数字范围分片，故名思议，就是要自己定义数字的范围来规划每个分片的区域，这就要求我们提前规划好分片字段某个范围属于哪个分片，比如说将第一个500W的数据分片在第一个节点上面，第二个500W的数据分片在第二个节点上，依次类推。

二、实施自定义数字范围分片

1、配置rule.xml

在rule.xml中添加如下配置：

<tableRule name="rang-long--rule-customer"><rule> <columns>id</columns> <algorithm>rang-long-customer</algorithm></rule></tableRule><!-- 对自定义数字分片规则rang-long-customer的定义 --><function name="rang-long-customer" class="org.opencloudb.route.function.AutoPartitionByLong"><property name="mapFile">autopartition-long-customer.txt</property><property name="defaultNode">0</property> <property name="type">0</property></function>

2、创建autopartition-long-customer.txt文件

在Mycat的conf目录下创建autopartition-long-customer.txt文件，内容如下：

0-500M=0500M-1000M=11000M-1500M=2

3、配置schema.xml

<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://org.opencloudb/" ><schema name="lyzdb" checkSQLschema="false" sqlMaxLimit="100"><!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node --><table name="t_order" primaryKey="id" dataNode="dn1,dn2" rule="rang-long--rule-customer"/></schema><!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --><dataNode name="dn1" dataHost="localhost1" database="db1" /><dataNode name="dn2" dataHost="localhost1" database="db2" /><!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" /> <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> <dataNodename="jdbc_dn2" dataHost="jdbchost" database="db2" /> <dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> --><dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="hostM1" url="192.168.209.137:3306" user="root" password="root"></writeHost><!--<writeHost host="hostS1" url="localhost:3316" user="root"--><!--password="123456" />--><!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --></dataHost></mycat:schema>

至此，我们的Mycat就配置完成了，是不是很简单，不过需要注意一点：预先制定可能的id范围到某个分片，所有的节点配置都是从0开始，及0代表节点1

三、测试

1、建表并且录入数据

首先我们创建表

mysql> explain create table t_order (id int not null auto_increment primary key,province varchar(16),sn varchar(64),create_time datetime);+-----------+----------------------------------------------------------------------------------------------------------------------------+| DATA_NODE | SQL                                                                                                                        |+-----------+----------------------------------------------------------------------------------------------------------------------------+| dn1       | create table t_order (id int not null auto_increment primary key,province varchar(16),sn varchar(64),create_time datetime) || dn2       | create table t_order (id int not null auto_increment primary key,province varchar(16),sn varchar(64),create_time datetime) |+-----------+----------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.04 sec)mysql> create table t_order (id int not null auto_increment primary key,province varchar(16),sn varchar(64),create_time datetime);Query OK, 0 rows affected (0.25 sec)

可以看出建表语句是在所有分片执行。

然后我们录入数据：

mysql> explain INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10006_10001',NOW());+-----------+-----------------------------------------------------------------------------------------------------+| DATA_NODE | SQL                                                                                                 |+-----------+-----------------------------------------------------------------------------------------------------+| dn1       | INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10006_10001',NOW()) |+-----------+-----------------------------------------------------------------------------------------------------+1 row in set (0.05 sec)mysql>mysql> INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(10001,'beijing','beijing10006_10001',NOW());Query OK, 1 row affected (0.04 sec)mysql> explain INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'beiing','beijing10006_5000001',NOW());+-----------+--------------------------------------------------------------------------------------------------------+| DATA_NODE | SQL                                                                                                    |+-----------+--------------------------------------------------------------------------------------------------------+| dn2       | INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'beiing','beijing10006_5000001',NOW()) |+-----------+--------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>mysql> INSERT INTO t_order (ID,PROVINCE,SN,CREATE_TIME) VALUES(5000001,'beiing','beijing10006_5000001',NOW());Query OK, 1 row affected (0.04 sec)

可以看出当ID为1001时路由到了dn1节点，当id为5000001时，路由到了dn2节点，符合我们配置的路由规则。

2、测试查询语句

2-1、指定分片字段查询

mysql> explain select * from t_order t1 where t1.ID=5000001;+-----------+----------------------------------------------+| DATA_NODE | SQL                                          |+-----------+----------------------------------------------+| dn2       | select * from t_order t1 where t1.ID=5000001 |+-----------+----------------------------------------------+1 row in set (0.02 sec)mysql>mysql>mysql> select * from t_order t1 where t1.ID=5000001;+---------+----------+----------------------+---------------------+| id      | province | sn                   | create_time         |+---------+----------+----------------------+---------------------+| 5000001 | beiing   | beijing10006_5000001 | 2017-08-09 20:36:34 |+---------+----------+----------------------+---------------------+1 row in set (0.00 sec)

可以看出，如果我们的查询语句中指定了分片字段，则直接路由到相应的数据节点进行查询。

2-2、按照分片字段范围查询

mysql> explain select * from t_order t1 where t1.ID=5000001;+-----------+----------------------------------------------+| DATA_NODE | SQL                                          |+-----------+----------------------------------------------+| dn2       | select * from t_order t1 where t1.ID=5000001 |+-----------+----------------------------------------------+1 row in set (0.02 sec)mysql>mysql>mysql> select * from t_order t1 where t1.ID=5000001;+---------+----------+----------------------+---------------------+| id      | province | sn                   | create_time         |+---------+----------+----------------------+---------------------+| 5000001 | beiing   | beijing10006_5000001 | 2017-08-09 20:36:34 |+---------+----------+----------------------+---------------------+1 row in set (0.00 sec)

可以看出，如果分片字段范围的查询，则走所有节点去检索，哪怕只有一条数据在一个分片上，route路由也是走所有的分片进行检索查询

2-3、不走分片字段查询

mysql> explain select * from t_order t1 where t1.dn='beijing10006_10001';+-----------+-----------------------------------------------------------------------+| DATA_NODE | SQL                                                                   |+-----------+-----------------------------------------------------------------------+| dn1       | SELECT * FROM t_order t1 WHERE t1.dn = 'beijing10006_10001' LIMIT 100 || dn2       | SELECT * FROM t_order t1 WHERE t1.dn = 'beijing10006_10001' LIMIT 100 |+-----------+-----------------------------------------------------------------------+2 rows in set (0.00 sec)

可以看出，如果不走分片字段的查询，即使是单个数据，也要route路由所有的分片，走所有的分片进行查询