必读:场景
访问记录表使用yyyyMM做后缀分表,表会每月自动创建
分表规则配置如下
文档:https://shardingsphere.apache.org/document/4.1.1/cn/overview/
shardingRule:
tables:
lpf_user:
actualDataNodes: ms_ds${0}.lpf_user_${202308..202312},ms_ds${0}.lpf_user_20${24..30}0${1..9},ms_ds${0}.lpf_user_20${24..30}${10..12}
tableStrategy:
standard:
shardingColumn: create_time
preciseAlgorithmClassName: com.example.user.config.MonthPreciseShardingAlgorithm
rangeAlgorithmClassName: com.example.user.config.MonthRangeShardingAlgorithm
defaultDataSourceName: ms_ds0
defaultTableStrategy:
none:
masterSlaveRules:
ms_ds0:
masterDataSourceName: db_master
slaveDataSourceNames:
- db_slave
loadBalanceAlgorithmType: ROUND_ROBIN
主要修改
yaml配置
- standard: 用于单分片键的标准分片场景
- actualDataNodes划分了一个较大的时间范围
- preciseAlgorithmClassName指定添加和精确查询时表的范围 实现PreciseShardingAlgorithm接口并加入新逻辑
- rangeAlgorithmClassName指定范围查询时表的范围 实现RangeShardingAlgorithm接口并加入新逻辑
精确分片算法类(MonthPreciseShardingAlgorithm.java)
package com.example.user.config;
import cn.hutool.core.date.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
/**
* 4.1version 精准分片
* 根据月精准分片 -- 插入数据、精准查找使用
*/
@Slf4j
public class MonthPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
/**
* 每个月分一片
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
//精确分片
log.info("logic table name:{},rout column:{},column value:{}", preciseShardingValue.getLogicTableName(), preciseShardingValue.getColumnName(),preciseShardingValue.getValue());
// 根据当前日期 来 分库分表
Date date = preciseShardingValue.getValue();
String format = DateUtil.format(date,"yyyyMM");
// 选择表
String tb_name = new StringBuilder(preciseShardingValue.getLogicTableName()).append("_").append(format).toString();
log.info("Precise路由到的真实表:" + tb_name);
if(collection.contains(tb_name))
return tb_name;
throw new IllegalArgumentException();
}
}
范围分片算法类(MonthRangeShardingAlgorithm.java)
这里将表范围定在了当前月
package com.example.user.config;
import cn.hutool.core.date.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.util.*;
/**
* 4.1version 范围分片 -- 范围查询使用
*/
@Slf4j
public class MonthRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {
log.info("collection:{},rangeShardingValue:{}", collection, rangeShardingValue);
Set<String> set = new HashSet<>();
Date lowerEndpoint = rangeShardingValue.getValueRange().lowerEndpoint();
Date upperEndpoint = rangeShardingValue.getValueRange().upperEndpoint();
Integer start = Integer.parseInt(DateUtil.format(lowerEndpoint, "yyyyMM"));
Integer end = Integer.parseInt(DateUtil.format(upperEndpoint, "yyyyMM"));
for(String item:collection){
if(Integer.parseInt(item.substring(9)) >= start && Integer.parseInt(item.substring(9)) <= end){
set.add(item);
}
}
log.info("range真实路由的表:{}", set);
return set;
}
}
查询方法
在查询时一定要加上create_time的范围查询(注意这个字段必须和shardingColumn配置的字段一致) 才能触发 范围分片算法类(rangeAlgorithmClassName)使列表查询时 只查询时间范围内涉及到的表
between或者> <只要是对分表规则的字段 操作范围查询就行
-
xml
-
或者方法里
两种都行
有一点投机取巧,有不同意见请指出