【mysql】mycat水平分库主键范围

1.schema.xml 

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100">
        <table name="my_test2" dataNode="dnmycatdb$1-9" rule="sharding-by-intfile-mycatdb-my_test2"></table>
        <table name="my_test3" primaryKey="jobid" type="global" dataNode="dnmycatdb$1-9" ></table>
        <table name="my_test4" dataNode="dnmycatdb$1-3" rule="mod-long" >
           <childTable name="my_test4_child" primaryKey="orderdetail_id" joinKey="order_id" parentKey="order_id"/>
        </table>
        <table name="my_test5" dataNode="dnmycatdb$1-9" rule="auto-sharding-long"></table>
</schema>

2.规则 

vim rule.xml 

<tableRule name="auto-sharding-long">
	<rule>
		<columns>id</columns>
		<algorithm>rang-long</algorithm>
	</rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
	<property name="mapFile">autopartition-long-my_test5.txt</property>
	<property name="defaultNode">0</property> 
</function>

[root@mysql1 conf]# cat autopartition-long-my_test5.txt 
20000000-20005000=0
20005001-20010000=1
20010001-20015000=2
20015001-20020000=3
20020001-20025000=4
20025001-20030000=5
20030001-20035000=6
20035001-20040000=7
20040001-20050000=8

3.准备数据

create table my_test5(id int,name varchar(20));
insert into my_test5(id ,name)values(20000000,'xsq1');
insert into my_test5(id ,name)values(20000001,'xsq2');
insert into my_test5(id ,name)values(20005001,'xsq3');
insert into my_test5(id ,name)values(20005002,'xsq4');
insert into my_test5(id ,name)values(20010001,'xsq5');
insert into my_test5(id ,name)values(20010002,'xsq6');
insert into my_test5(id ,name)values(20015001,'xsq7');
insert into my_test5(id ,name)values(20015002,'xsq8');
insert into my_test5(id ,name)values(20020001,'xsq9');
insert into my_test5(id ,name)values(20020002,'xsq10');
insert into my_test5(id ,name)values(20025001,'xsq11');
insert into my_test5(id ,name)values(20025002,'xsq12');
insert into my_test5(id ,name)values(20030001,'xsq13');
insert into my_test5(id ,name)values(20030002,'xsq14');
insert into my_test5(id ,name)values(20035001,'xsq15');
insert into my_test5(id ,name)values(20035002,'xsq16');
insert into my_test5(id ,name)values(20040001,'xsq17');
insert into my_test5(id ,name)values(20040002,'xsq18');

"sspu@192.168.1.11">select * from my_test5;
+----------+-------+
| id       | name  |
+----------+-------+
| 20025001 | xsq11 |
| 20025002 | xsq12 |
| 20020001 | xsq9  |
| 20020002 | xsq10 |
| 20030001 | xsq13 |
| 20030002 | xsq14 |
| 20015001 | xsq7  |
| 20015002 | xsq8  |
| 20035001 | xsq15 |
| 20035002 | xsq16 |
| 20000000 | xsq1  |
| 20000001 | xsq2  |
| 20040001 | xsq17 |
| 20040002 | xsq18 |
| 20010001 | xsq5  |
| 20010002 | xsq6  |
| 20005001 | xsq3  |
| 20005002 | xsq4  |
+----------+-------+
18 rows in set (0.08 sec)

4.查询并查看执行计划 

"sspu@192.168.1.11">explain select * from my_test5 where id <20020002;
+------------+------------------------------------------------------+
| DATA_NODE  | SQL                                                  |
+------------+------------------------------------------------------+
| dnmycatdb1 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb2 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb3 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb4 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb5 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb6 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb7 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb8 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
| dnmycatdb9 | SELECT * FROM my_test5 WHERE id < 20020002 LIMIT 100 |
+------------+------------------------------------------------------+
9 rows in set (0.01 sec)
"root@localhost">select * from sspudb1.my_test5;
+----------+------+
| id       | name |
+----------+------+
| 20000000 | xsq1 |
| 20000001 | xsq2 |
+----------+------+
2 rows in set (0.00 sec)

"root@localhost">select * from sspudb2.my_test5;
+----------+------+
| id       | name |
+----------+------+
| 20005001 | xsq3 |
| 20005002 | xsq4 |
+----------+------+
2 rows in set (0.00 sec)

"root@localhost">select * from sspudb3.my_test5;
+----------+------+
| id       | name |
+----------+------+
| 20010001 | xsq5 |
| 20010002 | xsq6 |
+----------+------+
2 rows in set (0.00 sec)
"root@localhost">select * from sspudb4.my_test5;
+----------+------+
| id       | name |
+----------+------+
| 20015001 | xsq7 |
| 20015002 | xsq8 |
+----------+------+
2 rows in set (0.00 sec)

"root@localhost">select * from sspudb5.my_test5;
+----------+-------+
| id       | name  |
+----------+-------+
| 20020001 | xsq9  |
| 20020002 | xsq10 |
+----------+-------+
2 rows in set (0.00 sec)

"root@localhost">select * from sspudb6.my_test5;
+----------+-------+
| id       | name  |
+----------+-------+
| 20025001 | xsq11 |
| 20025002 | xsq12 |
+----------+-------+
2 rows in set (0.00 sec)
--由此可见，已经实现了水平切分。

5.总结 

水平分库，会将一张逻辑表的数据，分布到所有指定的节点和库中。