前言
使用自定义范围算法(RangeShardingAlgorithm),通常用来处理时间分片处理比较方便。在该demo中,通过delivery_time来分库,分表,实现范围的不同分库分表,数据源,表使用的配置见复合分片的数据源,表信息配置
创建表SQL,按照年代时间建表c_order2023,c_order2024
CREATE TABLE `c_order2023` (
`id` bigint NOT NULL AUTO_INCREMENT,
`is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
`user_id` int NOT NULL COMMENT '用户id',
`company_id` int NOT NULL COMMENT '公司id',
`publish_user_id` int NOT NULL COMMENT 'B端用户id',
`position_id` int NOT NULL COMMENT '职位ID',
`resume_type` int NOT NULL DEFAULT '0' COMMENT '简历类型:0 附件 1 在线',
`status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '处理时间',
`delivery_time` datetime DEFAULT NULL COMMENT '用户投递时间',
PRIMARY KEY (`id`),
KEY `idx_userId_operateTime` (`user_id`,`update_time`) USING BTREE,
KEY `index_userId_positionId` (`user_id`,`position_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
yml配置
在原来的配置上加上范围分片的配置信息。原来的配置见yml配置
c_order:
actualDataNodes: ftdb$->{0..1}.c_order$->{2023..2024}
database-strategy:
standard:
sharding-column: delivery_time #数据库范围分库策略,需要配置精确分片策略,不然会报空指针,无法启动
rangeAlgorithmClassName: com.example.ftserver.sharding.shardingalgrithm.range.FtDataBaseRangeShardingAlgorithm
precise-algorithm-class-name: com.example.ftserver.sharding.shardingalgrithm.range.FtDataBaseRangeShardingAlgorithm
tableStrategy:
standard:
sharding-column: delivery_time # 数据库范围分表策略,需要配置精确分片策略,不然会报空指针,无法启动
rangeAlgorithmClassName: com.example.ftserver.sharding.shardingalgrithm.range.FtTableRangeShardingAlgorithm
precise-algorithm-class-name: com.example.ftserver.sharding.shardingalgrithm.range.FtTableRangeShardingAlgorithm
key-generator:
column: id
type: MYKEY
分库相关示例代码
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.stereotype.Component;
import java.util.Collection;
@Component
@Slf4j
public class FtDataBaseRangeShardingAlgorithm extends AbstractRangSharding<Long> implements RangeShardingAlgorithm<Comparable<?>>, PreciseShardingAlgorithm<Comparable<?>> {
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Comparable<?>> rangeShardingValue) {
Collection<String> dataSource = super.rangeTargetDataSource(rangeShardingValue, false);
return CollectionUtils.isEmpty(dataSource) ? collection : dataSource;
}
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Comparable<?>> preciseShardingValue) {
String dataSource = super.preciseTargetDataSource(preciseShardingValue, false);
// 为空时使用默认数据源
return StringUtils.isEmpty(dataSource) ? "ftdb0" : dataSource;
}
}
分表示例代码
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.stereotype.Component;
import java.util.Collection;
/**
* @author zhangxin
* @description: 表范围分片算法,需要配置精确分片算法,且这两者的参数需要一致
*/
@Component
@Slf4j
public class FtTableRangeShardingAlgorithm extends AbstractRangSharding<Long> implements RangeShardingAlgorithm<Comparable<?>>, PreciseShardingAlgorithm<Comparable<?>> {
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Comparable<?>> rangeShardingValue) {
Collection<String> dataSource = super.rangeTargetDataSource(rangeShardingValue, true);
return CollectionUtils.isEmpty(dataSource) ? collection : dataSource;
}
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Comparable<?>> preciseShardingValue) {
String dataSource = super.preciseTargetDataSource(preciseShardingValue, true);
// 为空时使用默认数据表
return StringUtils.isEmpty(dataSource) ? "c_order2023" : dataSource;
}
}
范围分片抽象类,抽象类继承的AbstractComplexSharding见标题:复合分库分片算法
import com.example.ftserver.sharding.shardingalgrithm.AbstractComplexSharding;
import com.example.ftserver.sharding.shardingalgrithm.complex.sharingvalue.ShardingParam;
import com.example.ftserver.sharding.shardingalgrithm.complex.sharingvalue.ShardingRangeConfig;
import com.google.common.collect.Range;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.util.*;
public abstract class AbstractRangSharding<T extends Comparable<? super T>> extends AbstractComplexSharding<T> {
private List<ShardingParam> rangInitSharingParams(RangeShardingValue<Comparable<?>> shardingValue) {
List<ShardingParam> shardingParams = new ArrayList<>();
Range<Comparable<?>> comparableRange = shardingValue.getValueRange();
if (Objects.nonNull(comparableRange)) {
ShardingParam param = super.initShardingParamsFromRange(comparableRange, shardingValue.getColumnName());
shardingParams.add(param);
}
return shardingParams;
}
/**
* 范围分片时数据源,表信息获取
* @param rangeShardingValue value
* @param isTable 是否为表
* @return 数据源信息
*/
protected Collection<String> rangeTargetDataSource(RangeShardingValue<Comparable<?>> rangeShardingValue,boolean isTable) {
String logicTableName = rangeShardingValue.getLogicTableName();
List<ShardingParam> sharingParams = this.rangInitSharingParams(rangeShardingValue);
if (CollectionUtils.isEmpty(sharingParams)) {
return Collections.emptyList();
}
List<ShardingRangeConfig> rangeConfigs =super.shardingRangeConfigs(isTable,logicTableName);
return super.getTargetDataSource(sharingParams, rangeConfigs);
}
/**
* 精确分片时数据源,表信息获取
* @param shardingValue value
* @param isTable 是否为表
* @return 数据源信息
*/
protected String preciseTargetDataSource(PreciseShardingValue<Comparable<?>> shardingValue, boolean isTable) {
String columnName = shardingValue.getColumnName();
ShardingParam param = this.initShardingParamsFromCollection(Collections.singletonList(shardingValue.getValue()), columnName);
if (Objects.isNull(param)) {
return null;
}
List<ShardingRangeConfig> rangeConfigs =super.shardingRangeConfigs(isTable,shardingValue.getLogicTableName());
Collection<String> dataSource = super.getTargetDataSource(Collections.singletonList(param), rangeConfigs);
if (CollectionUtils.isNotEmpty(dataSource)) {
return dataSource.iterator().next();
}
return null;
}
测试示例代码
package com.example.ftserver;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.ftserver.mapper.COrderMapper;
import com.test.ft.common.entity.COrderEntity;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Repeat;
import org.springframework.test.context.junit4.SpringRunner;
import java.time.LocalDateTime;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest()
public class CustomRangeShardingTest {
@Autowired
private COrderMapper mapper;
@Test
@Repeat(4)
public void rangeShardingTest() {
COrderEntity entity = new COrderEntity();
entity.setIsDel(false);
entity.setUserId(RandomUtil.randomInt(200,500));
entity.setCompanyId(RandomUtil.randomInt(2000,3000));
entity.setPublishUserId(RandomUtil.randomInt(1000,5000));
entity.setPositionId(RandomUtil.randomInt(10,50));
entity.setResumeType(1);
entity.setStatus(String.valueOf(RandomUtil.randomChinese()));
LocalDateTime dateTime = LocalDateTimeUtil.parse("2023-08-01 24:00:00", "yyyy-MM-dd HH:mm:ss");
entity.setDeliveryTime(dateTime);
int insert = mapper.insert(entity);
System.out.println("新增条数"+insert);
entity.setIsDel(false);
entity.setUserId(RandomUtil.randomInt(200,500));
entity.setCompanyId(RandomUtil.randomInt(2000,3000));
entity.setPublishUserId(RandomUtil.randomInt(1000,5000));
entity.setPositionId(RandomUtil.randomInt(10,50));
entity.setResumeType(1);
entity.setStatus(String.valueOf(RandomUtil.randomChinese()));
dateTime = LocalDateTimeUtil.parse("2024-03-01 12:00:00", "yyyy-MM-dd HH:mm:ss");
entity.setDeliveryTime(dateTime);
insert = mapper.insert(entity);
System.out.println("新增条数"+insert);
}
@Test
public void queryTest(){
LambdaQueryWrapper<COrderEntity> queryWrapper = Wrappers.lambdaQuery(COrderEntity.class).between(COrderEntity::getDeliveryTime, LocalDateTimeUtil.
parse("2024-01-01 00:00:00", "yyyy-MM-dd HH:mm:ss"),
LocalDateTimeUtil.parse("2024-08-31 23:59:59", "yyyy-MM-dd HH:mm:ss"));
List<COrderEntity> list = mapper.selectList(queryWrapper);
System.out.println("查询结果"+ JSONUtil.toJsonStr(list));
}
}
测试结果
插入测试,可以看到按时间范围插入成功
查询测试,可以看到已经通过年份路由到了对应的数据表:
相关心得
1.在yml里面配置时,需要配置精确分片算法策略,不然会无法,报空指针异常。原因在于:
在项目启动,读取配置信息时,在org.apache.shardingsphere.core.yaml.swapper.ShardingStrategyConfigurationYamlSwapper#swap类里面有生成精确分片算法的内容,如果没有配置精确分片算法,则无法生成导致空指针异常。
2.分片算法和精确分片算法的配置,最好使用同一个分片键或者复合分片键时,两者的算法实现最终的路由一致,这样能更好的提高性能,提供查询效率