devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Shardingsphere，Mybatis Plus之使用自定义精确分片算法---RangeShardingAlgorithm

作者：IT小白 | 2024-06-25 16:09:39

赞

踩

rangeshardingalgorithm

前言

使用自定义范围算法(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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

分库相关示例代码

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;
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

分表示例代码

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
 * &#064;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;
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

范围分片抽象类，抽象类继承的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;
    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56

测试示例代码

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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68

测试结果

插入测试，可以看到按时间范围插入成功
在这里插入图片描述
在这里插入图片描述
查询测试，可以看到已经通过年份路由到了对应的数据表：
在这里插入图片描述

相关心得

1.在yml里面配置时，需要配置精确分片算法策略，不然会无法，报空指针异常。原因在于：
在项目启动，读取配置信息时，在org.apache.shardingsphere.core.yaml.swapper.ShardingStrategyConfigurationYamlSwapper#swap类里面有生成精确分片算法的内容，如果没有配置精确分片算法，则无法生成导致空指针异常。
在这里插入图片描述
2.分片算法和精确分片算法的配置，最好使用同一个分片键或者复合分片键时，两者的算法实现最终的路由一致，这样能更好的提高性能，提供查询效率

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/756731
推荐阅读