sharding jdbc （二） - 日期分片

sharding jdbc （二） - 日期分片

准备

mysql> show tables;
+----------------------+
| Tables_in_shard_ds_0 |
+----------------------+
| t_order_2021_6       |
| t_order_2021_7       |
| t_order_2021_8       |
| t_order_item_2021_6  |
| t_order_item_2021_7  |
| t_order_item_2021_8  |
| t_sequence           |
+----------------------+
14 rows in set (0.00 sec)

application.properties 配置文件

spring.shardingsphere.datasource.names=ds0
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/shard_ds_0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=111111


spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order_2021_$->{6..8}
# 区别 inline 和 standard
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=create_time
# 指定分片策略，自定义
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.x.z.shardjdbc.frame.shard.precise.OrderStrategy
# 因为有查询 >= ,<= 需要range 策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.range-algorithm-class-name=com.x.z.shardjdbc.frame.shard.range.OrderRangeStrategy


spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item_2021_$->{6..8}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.precise-algorithm-class-name=com.x.z.shardjdbc.frame.shard.precise.OrderItemStrategy
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.range-algorithm-class-name=com.x.z.shardjdbc.frame.shard.range.OrderRangeStrategy


spring.shardingsphere.props.sql.show=true
logging.level.com.x.z.shardjdbc.persist.mapper=debug
mybatis-plus.type-aliases-package=com.x.z.shardjdbc.persist.model

日期分片策略

PreciseShardingAlgorithm

• OrderItemStrategy

/**
 * 日期分片
 * Precise处理 = 和 in 的路由
 */
@Slf4j
public class OrderItemStrategy implements PreciseShardingAlgorithm<String> {

    private static  final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {

        String str = shardingValue.getValue();
        if (Objects.isNull(str)) {
            return availableTargetNames.stream().findFirst().get();
        }

        LocalDateTime value = LocalDateTime.parse(str, formatter);

        int year = value.getYear();
        int monthValue = value.getMonthValue();
        String logicTableName = shardingValue.getLogicTableName();
        String table = logicTableName.concat("_").concat(year + "").concat("_").concat(monthValue + "");

        log.info("OrderItemStrategy.doSharding table name: {}", table);
        return availableTargetNames.stream().filter(s -> s.equals(table)).findFirst().orElseThrow(() -> new RuntimeException("逻辑分表不存在"));
    }
}

• OrderStrategy

/**
 * 日期分片
 */
@Slf4j
public class OrderStrategy implements PreciseShardingAlgorithm<String> {

    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {

        String str = shardingValue.getValue();
        if (Objects.isNull(str)) {
            return availableTargetNames.stream().findFirst().get();
        }

        LocalDateTime value = LocalDateTime.parse(str, formatter);

        int year = value.getYear();
        int monthValue = value.getMonthValue();
        String logicTableName = shardingValue.getLogicTableName();
        String table = logicTableName.concat("_").concat(year + "").concat("_").concat(monthValue + "");

        log.info("OrderStrategy.doSharding table name: {}", table);
        return availableTargetNames.stream().filter(s -> s.equals(table)).findFirst().orElseThrow(() -> new RuntimeException("逻辑分表不存在"));
    }
}

RangeShardingAlgorithm

/**
 * RangeShardingAlgorithm 是可选的，用于处理BETWEEN AND， >， <，>=，<= 条件分片
 */
@Slf4j
public class OrderRangeStrategy implements RangeShardingAlgorithm<String> {

    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<String> shardingValue) {

        log.info("OrderRangeStrategy availableTargetNames: " + JSON.toJSONString(availableTargetNames));
        String logicTableName = shardingValue.getLogicTableName();
        Range<String> valueRange = shardingValue.getValueRange();

        Set<String> queryRangeTables = extracted(logicTableName, LocalDateTime.parse(valueRange.lowerEndpoint(), formatter),
                LocalDateTime.parse(valueRange.upperEndpoint(), formatter));
        ArrayList<String> tables = new ArrayList<>(availableTargetNames);
        tables.retainAll(queryRangeTables);
        System.out.println(tables);
        return tables;
    }

    private Set<String> extracted(String logicTableName, LocalDateTime lowerEndpoint, LocalDateTime upperEndpoint) {
        Set<String> rangeTable = new HashSet<>();
        while (lowerEndpoint.isBefore(upperEndpoint)) {
            String str = getTableNameByDate(lowerEndpoint, logicTableName);
            rangeTable.add(str);
            lowerEndpoint = lowerEndpoint.plusMonths(1);
        }

        //up 月份
        String str = getTableNameByDate(upperEndpoint, logicTableName);
        rangeTable.add(str);
        return rangeTable;
    }

    private String getTableNameByDate(LocalDateTime dateTime, String logicTableName) {
        String year = dateTime.getYear() + "";
        String monthValue = dateTime.getMonthValue() + "";
        return logicTableName.concat("_").concat(year.concat("_").concat(monthValue));
    }
}

测试用例

同取模分片