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_test7" dataNode="dnmycatdb$1-9" rule="sharding-by-date-joindate"></table>
</schema>
2.规则配置。
<tableRule name="sharding-by-date-joindate">
<rule>
<columns>joindate</columns>
<algorithm>sharding-by-date</algorithm>
</rule>
</tableRule>
<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2024-01-01</property>
<property name="sEndDate">2024-12-31</property>
<property name="sPartionDay">90</property>
</function>
3个月一个片。
3.数据准备
"sspu@192.168.1.11">show tables;
+-------------------+
| Tables in mycatdb |
+-------------------+
| my_test2 |
| my_test3 |
| my_test4 |
| my_test4_child |
| my_test5 |
| my_test6 |
| my_test7 |
+-------------------+
7 rows in set (0.01 sec)
--90天一个分区。
create table my_test7(id int,name varchar(20),joindate date);
insert into my_test7(id,name,joindate) values(1,'xsq1','2024-01-01');
insert into my_test7(id,name,joindate) values(2,'xsq2','2024-03-01');
insert into my_test7(id,name,joindate) values(3,'xsq3','2024-04-01');
insert into my_test7(id,name,joindate) values(4,'xsq4','2024-05-30');
insert into my_test7(id,name,joindate) values(5,'xsq5','2024-06-03');
insert into my_test7(id,name,joindate) values(6,'xsq6','2024-06-30');
insert into my_test7(id,name,joindate) values(7,'xsq7','2024-07-30');
insert into my_test7(id,name,joindate) values(8,'xsq8','2024-07-21');
"sspu@192.168.1.11">select * from my_test7;
+------+------+------------+
| id | name | joindate |
+------+------+------------+
| 1 | xsq1 | 2024-01-01 |
| 2 | xsq2 | 2024-03-01 |
| 3 | xsq3 | 2024-04-01 |
| 4 | xsq4 | 2024-05-30 |
| 5 | xsq5 | 2024-06-03 |
| 6 | xsq6 | 2024-06-30 |
| 7 | xsq7 | 2024-07-30 |
| 8 | xsq8 | 2024-07-21 |
+------+------+------------+
8 rows in set (0.12 sec)
"sspu@192.168.1.11">explain select * from my_test7;
+------------+----------------------------------+
| DATA_NODE | SQL |
+------------+----------------------------------+
| dnmycatdb1 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb2 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb3 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb4 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb5 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb6 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb7 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb8 | SELECT * FROM my_test7 LIMIT 100 |
| dnmycatdb9 | SELECT * FROM my_test7 LIMIT 100 |
+------------+----------------------------------+
9 rows in set (0.00 sec)
--创建索引,依然会全库扫描。
"sspu@192.168.1.11">create index idx_joindate on my_test7(joindate);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
"sspu@192.168.1.11">explain select * from my_test7 where joindate<='2024-04-01';
+------------+-----------------------------------------------------------------+
| DATA_NODE | SQL |
+------------+-----------------------------------------------------------------+
| dnmycatdb1 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb2 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb3 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb4 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb5 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb6 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb7 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb8 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
| dnmycatdb9 | SELECT * FROM my_test7 WHERE joindate <= '2024-04-01' LIMIT 100 |
+------------+-----------------------------------------------------------------+
9 rows in set (0.00 sec)
4.查看数据分布
"root@localhost">select * from sspudb1.my_test7;
+------+------+------------+
| id | name | joindate |
+------+------+------------+
| 1 | xsq1 | 2024-01-01 |
| 2 | xsq2 | 2024-03-01 |
+------+------+------------+
2 rows in set (0.00 sec)
"root@localhost">select * from sspudb2.my_test7;
+------+------+------------+
| id | name | joindate |
+------+------+------------+
| 3 | xsq3 | 2024-04-01 |
| 4 | xsq4 | 2024-05-30 |
| 5 | xsq5 | 2024-06-03 |
+------+------+------------+
3 rows in set (0.00 sec)
"root@localhost">select * from sspudb3.my_test7;
+------+------+------------+
| id | name | joindate |
+------+------+------------+
| 6 | xsq6 | 2024-06-30 |
| 7 | xsq7 | 2024-07-30 |
| 8 | xsq8 | 2024-07-21 |
+------+------+------------+
3 rows in set (0.00 sec)
"root@localhost">select * from sspudb4.my_test7;
Empty set (0.00 sec)
"root@localhost">select * from sspudb5.my_test7;
Empty set (0.00 sec)
"root@localhost">select * from sspudb6.my_test7;
Empty set (0.00 sec)
"root@localhost">select * from sspudb7.my_test7;
Empty set (0.00 sec)
"root@localhost">select * from sspudb8.my_test7;
Empty set (0.00 sec)
"root@localhost">select * from sspudb9.my_test7;
Empty set (0.00 sec)
--由于数据量太少。其他分片上暂时未插入数据。随着数据量增多。应该会在其他节点上
--出现数据。