【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_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)

--由于数据量太少。其他分片上暂时未插入数据。随着数据量增多。应该会在其他节点上
--出现数据。