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.总结
水平分库,会将一张逻辑表的数据,分布到所有指定的节点和库中。