shardingsphere如果要实现按月分表需要我们实现分表路由算法实现,默认是没有用。下面进入实战环节,本文所说按月份分表是相同月份的数据放入同一个表,当然如果你要每个月保存到一个表也是可以的,修改一下算法就行了。按月分表使用每个月数据都比较稳定业务场景使用,每个月数据量不会有太大的差异。使用了分表一定查询的时候一定要注意带上分片键,不然就是查询所有的分表了,那分表就没有多大的意义了。
首先项目pom加入指定shardingsphere的依赖,版本使用稳定版本就行,我这里使用的是
4.0.0-RC1版本。
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${yl-sharding-jdbc-spring-boot-starter.version}</version>
</dependency>
在数据库创建好对应的分表,分表可以在同一个数据库也可以在不同的数据库。这里我创建了十二张表,t_log_0-t_log_11。
配置分表规则,以下配置使用的yml格式配置。
配置简单说明:
tables:具体表配置
actualDataNodes:具体表所在的数据库位置
tableStrategy:分表策略
precise-algorithm-class-name: 精确查询算法实现类
range-algorithm-class-name: 范围查询算法实现类
shardingColumn:分片键(就是根据哪个字段的值进行分表)
spring:
shardingsphere:
datasource:
names: master
master:
driver-class-name: com.mysql.jdbc.Driver
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/test_db?characterEncoding=utf8&useSSL=false
username: root
props:
sql:
show: true
sharding:
default-data-source-name: master
master-slave-rules:
master:
master-data-source-name: master
slave-data-source-names: slave0
tables:
t_log:
actualDataNodes: master.t_log_$->{0..11}
tableStrategy:
standard:
preciseAlgorithmClassName: com.xxx.xxx.MyPreciseShardingAlgorithm
rangeAlgorithmClassName: com.xxx.xxx.MyRangeShardingAlgorithm
shardingColumn: create_time
keyGenerator:
column: id
type: SNOWFLAKE
需要将以下算法实现类放到具体的项目中
分表算法具体实现:
精准匹配算法,主要针对查询条件使用=的时候
package com.xxx.xxx.xxx;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
@Slf4j
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Timestamp> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Timestamp> preciseShardingValue) {
String logicTableName = preciseShardingValue.getLogicTableName();
log.info("精准匹配时间为:{}", preciseShardingValue.getValue());
int month = 0;
try {
Long timestamp = preciseShardingValue.getValue().getTime();
Date date = new Date(timestamp);
Calendar c = Calendar.getInstance();
c.setTime(date);
month = c.get(Calendar.MONTH);
} catch (Exception ex) {
log.error("MyPreciseShardingAlgorithm is error [{}]", ex.getMessage());
}
log.info("匹配分表表表名:{}", new StringBuilder().append(logicTableName).append("_").append(month).toString());
return new StringBuilder().append(logicTableName).append("_").append(month).toString();
}
}
范围查询算法,主要针对查询条件使用>,<, >=, <=, IN 和 BETWEEN AND 的时候
package com.xxx.xxx.xxx;
import com.google.common.collect.Lists;
import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.sql.Timestamp;
import java.util.*;
@Slf4j
public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Timestamp> {
@Override
public Collection<String> doSharding(Collection<String> collection,
RangeShardingValue<Timestamp> rangeShardingValue) {
Range<Timestamp> valueRange = rangeShardingValue.getValueRange();
Date lowerEnd = valueRange.lowerEndpoint();
Date upperEnd = valueRange.upperEndpoint();
log.info("范围分片开始时间:{},结束时间:{}",lowerEnd,upperEnd);
Set<String> routTables = new HashSet<>();
if (lowerEnd != null && upperEnd != null) {
List<String> rangeNameList = getTableNames(lowerEnd, upperEnd);
for (String string : rangeNameList) {
routTables.add(rangeShardingValue.getLogicTableName() + "_" + string);
}
}
log.info("范围分片表名为:{}",routTables.toString());
return routTables;
}
private static List<String> getTableNames(Date start, Date end) {
List<String> result = Lists.newArrayList();
// 定义日期实例
Calendar calendar = Calendar.getInstance();
// 设置日期起始时间
calendar.setTime(start);
// 判断是否到结束日期
while (calendar.getTime().before(end)) {
int month = calendar.get(Calendar.MONTH);
result.add(String.valueOf(month));
// 进行当前日期月份加1
calendar.add(Calendar.MONTH, 1);
}
Calendar endCalendar = Calendar.getInstance();
endCalendar.setTime(end);
if (calendar.get(Calendar.MONTH)==endCalendar.get(Calendar.MONTH)){
result.add(String.valueOf(calendar.get(Calendar.MONTH)));
}
return result;
}
}