概述

按月分片来设置分片规则。

设置分片规则

1.rule.xml

<tableRule name="sharding-by-month">
        <rule>
                <columns>createtime</columns>
                <algorithm>partbymonth</algorithm>
        </rule>
</tableRule>
<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd HH:mm:ss</property>
        <property name="sBeginDate">2020-01-01 00:00:00</property>
</function>

2.schema.xml

      <schema name="logdb" checkSQLschema="false" sqlMaxLimit="100">         
          <table name="partbymonth_t" dataNode="dn$1-12" rule="sharding-by-month" />
      </schema>
        <dataNode name="dn1" dataHost="localhost1" database="log01" />
        <dataNode name="dn2" dataHost="localhost1" database="log02" />
        <dataNode name="dn3" dataHost="localhost1" database="log03" />
        <dataNode name="dn4" dataHost="localhost1" database="log04" />
        <dataNode name="dn5" dataHost="localhost1" database="log05" />
        <dataNode name="dn6" dataHost="localhost1" database="log06" />
        <dataNode name="dn7" dataHost="localhost1" database="log01" />
        <dataNode name="dn8" dataHost="localhost1" database="log02" />
        <dataNode name="dn9" dataHost="localhost1" database="log03" />
        <dataNode name="dn10" dataHost="localhost1" database="log04" />
        <dataNode name="dn11" dataHost="localhost1" database="log05" />
        <dataNode name="dn12" dataHost="localhost1" database="log06" />

        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"     
                          slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.201.33:3306" user="root"
                                   password="mysql">
                        <!-- can have multi read hosts -->
                      <readHost host="hostS2" url="192.168.201.35:3306" user="root" password="mysql" />
                </writeHost>
        </dataHost>

这里定义了"dn$1-12" 12个datanode,每个datanode写入不同的实际数据库(log01至log06),12个datanode,只能写入12个月份,月份从2020-01至2020-12。

3. 创建表结构

CREATE TABLE `partbymonth_t` (
  `id` varchar(32) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4.写入数据

insert into partbymonth_t(id,name,createtime) values('0001','part1','2020-01-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0002','part2','2020-02-02 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0003','part3','2020-03-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0004','part4','2020-04-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0005','part5','2020-05-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0006','part6','2020-06-02 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0007','part7','2020-07-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0008','part8','2020-08-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0009','part9','2020-09-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0010','part10','2020-10-02 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0011','part11','2020-11-01 00:00:00');
insert into partbymonth_t(id,name,createtime) values('0012','part12','2020-12-01 00:00:00');

总结

这种分片规则，还不如单库分表的分片技术。

 <table name="city" primaryKey="id" autoIncrement="true" subTables="city$1-3" dataNode="node1" rule="sharding-by-month" />

有没有单库分表subTables，再加多datanode？
继续研究，拭目以待。