转载请注明出处：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=0 500M-1000M=1 1000M-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" /> <dataNode name="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路由所有的分片，走所有的分片进行查询