当前位置:   article > 正文

【实战】基于springboot2.7x集成ShardingSphere5.X的分表及自定义分片策略配置_shardingsphere 自定义策略

shardingsphere 自定义策略

项目场景:

由于公司项目统一升级springboot 2.7x版本,导致原有sharding-jdbc 4x版本无法启动,主要涉及自定义分片策略,找遍全网没有发现完整的解决方案,所以历时两天半终于成功上岸,文章最后有完整配置


5.0.0-beta版本(推荐)

1、自定义分表查询多表不会有异常
2、5.0.0-beta版本偏低,可能存在潜在风险

		<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0-beta</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>2.7.18</version>
        </dependency>
        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

5.2.1版本

		<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>2.7.18</version>
        </dependency>
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>
        
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

经小编测试,
1、启动提示 Factory method ‘shardingSphereDataSource’ threw exception; nested exception is java.lang.NoSuchMethodError: org.yaml.snakeyaml.representer.Representer: method ()V not found,需要单独引用snakeyaml 1.33版本,
2、当自定义分表查询多表时,会出现Caused by: java.lang.NullPointerException: null
at org.apache.shardingsphere.sharding.merge.dql.groupby.GroupByMemoryMergedResult.getValueCaseSensitiveFromTables(GroupByMemoryMergedResult.java:135)错误,追踪代码发现schema对象中没有被分片的表,于是我追踪了schema对象,发现在初始化时,schema对象不会加载spring.shardingsphere.rules.sharding.tables下的所以表,初步怀疑可能是配置文件问题,但是官方配置中也没有说明,已经提交issues等待官方解答,如果已经解决的同学可以评论回答一下,感激不尽
在这里插入图片描述

遇到的问题

问题1:启动提示java.lang.NullPointerException: The props algorithmClassName cannot be null when uses class based sharding strategy,这个错误是真的坑,shardingsphere 4.x和5.x部分配置参数名不同,导致启动找不到这个属性,注意5.x的分片配置和4.x的也不同

shardingsphere 4.x 配置自定义分片属性名 algorithm-class-name

    sharding:
      tables:
        # 订单表基础表
        t_ent_order:
          # 真实表
          actualDataNodes: ds$->{0..3}.t_ent_order
          # 分库策略
          databaseStrategy:
            complex:
              sharding-columns: id,ent_id
              algorithm-class-name: cn.javayong.shardingjdbc4.spring.common.sharding.HashSlotAlgorithm
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

shardingsphere 5.x 配置自定义分片属性名 shardingAlgorithmName

rules
	sharding:
	  - !SHARDING
	    tables:
	      # 订单表基础表
	      t_ent_order:
	        # 真实表
	        actualDataNodes: ds$->{0..3}.t_ent_order
	        # 分库策略
	        databaseStrategy:
	          complex:
	            shardingColumns: id,ent_id
	            shardingAlgorithmName: hash-slot-algorithm
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

问题2:启动提示org.apache.shardingsphere.infra.util.spi.exception.ServiceProviderNotFoundServerException: SPI-00001: No implementation class load from SPI
这个错误消息表示ShardingSphere在尝试加载自定义的分片算法实现类时遇到了问题。错误消息指出没有从SPI(Service Provider Interface)接口,需要在项目目录resources\META-INF\services下创建org.apache.shardingsphere.sharding.spi.ShardingAlgorithm文件,把你自定义的分片类写入文件中
例如:

##多个类写多行
org.apache.shardingsphere.sharding.algorithm.xxx
  • 1
  • 2

问题3:项目启动后,自定义分片策略不执行,实现自定义分片ComplexKeysShardingAlgorithm类时,必须重写public String getType()方法,需要给每个类设置一个唯一的string值(CORE.COMPLEX.FIXTURE)并且和配置文件中的shardingAlgorithms.xx.type一样,配置文件参考 自定义分片类参考

package org.apache.shardingsphere.sharding.fixture;

import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;

import java.util.Collection;

public final class CoreComplexKeysShardingAlgorithmFixture implements ComplexKeysShardingAlgorithm<Integer> {
    
    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final ComplexKeysShardingValue<Integer> shardingValue) {
        return availableTargetNames;
    }
    
    @Override
    public String getType() {
        return "CORE.COMPLEX.FIXTURE";
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
rules:
- !SHARDING
  tables:
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: core_standard_fixture
  bindingTables:
  - t_order, t_order_item
  - foo:t_order, t_order_item
  shardingAlgorithms:
    core_standard_fixture:
      type: CORE.STANDARD.FIXTURE
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

整体配置:

以下是ShardingSphere5.X所有配置,需要yml可以自己转换,实现了StandardShardingAlgorithm、ComplexKeysShardingAlgorithm两个自定义分片

spring.shardingsphere.datasource.names=xxxx
spring.shardingsphere.datasource.cemos.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.cemos.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.cemos.jdbc-url=jdbc:mysql://${datasource.ip}:${datasource.port}/xxxx?serverTimezone=${time_zone:Asia/Shanghai}&useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.cemos.username=${datasource-username}
spring.shardingsphere.datasource.cemos.password=${datasource-password}


spring.shardingsphere.rules.sharding.sharding-algorithms.day-Long-sharding-algorithm.type=DAY_LONG_BASED

spring.shardingsphere.rules.sharding.sharding-algorithms.complex-sharding-algorithm.type=COMPLEX_BASED

spring.shardingsphere.rules.sharding.tables.gps_record.actual-data-nodes=cemos.gps_record_$->{2022..2099}$->{(1..12).collect{t ->t.toString().padLeft(2,'0')}}$->{(1..31).collect{t ->t.toString().padLeft(2,'0')}}
spring.shardingsphere.rules.sharding.tables.gps_record.table-strategy.standard.sharding-column=gps_time
spring.shardingsphere.rules.sharding.tables.gps_record.table-strategy.standard.sharding-algorithm-name=day-Long-sharding-algorithm
spring.shardingsphere.rules.sharding.tables.alarm.actual-data-nodes=cemos.alarm_$->{2022..2099}$->{(1..12).collect{t ->t.toString().padLeft(2,'0')}}$->{(1..31).collect{t ->t.toString().padLeft(2,'0')}}
spring.shardingsphere.rules.sharding.tables.alarm.table-strategy.complex.sharding-columns=id,start_time,task_id
spring.shardingsphere.rules.sharding.tables.alarm.table-strategy.complex.sharding-algorithm-name=complex-sharding-algorithm


spring.shardingsphere.rules.sharding.binding-tables[0]=alarm,device_media_task
spring.shardingsphere.rules.sharding.binding-tables[1]=alarm,device_media_file
spring.shardingsphere.rules.sharding.defaultDataSourceName=xxxx
spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.props.sql-show=false
  • 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
public class DayLongShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    private Properties properties;
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        String db_name = preciseShardingValue.getLogicTableName();
        // 获取到分片键中的值(time), 重改表名
        try {
            Date date = DateUtil.getDateByString(DateUtil.getDateByTime(preciseShardingValue.getValue()* NumberConstant.ONE_THOUSAND));
            String year = String.format("%tY", date);
            String mon = String.format("%tm",date);
            String day = String.format("%td",date);
            String newTable = db_name+"_"+year+mon+day;
            
            return newTable ;
        } catch (Exception e) {
            log.error("doSharding error {}",e);
        }

        throw new IllegalArgumentException("");
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
    //需要自己实现逻辑
        String db_name = rangeShardingValue.getLogicTableName();
        Range<Long> valueRange = rangeShardingValue.getValueRange();
        Long startDay = valueRange.lowerEndpoint()* NumberConstant.ONE_THOUSAND;
        Long endDay = valueRange.upperEndpoint()* NumberConstant.ONE_THOUSAND;

        Set<String> res = new HashSet<>();
        // 计算出这【startDay~endDay】日期之间的所有日期,同时转换成对应数据库表名
        Calendar calendar = Calendar.getInstance();
        while (startDay<=endDay){
            String year = String.format("%tY", startDay);
            String mon = String.format("%tm",startDay);
            String day = String.format("%td",startDay);
            res.add(db_name+"_"+year+mon+day);

            calendar.setTime(DateUtil.formatDate(DateUtil.getDateByTime(startDay)));
            calendar.add(Calendar.DATE, 1);
            // 获取增加后的日期
            startDay = calendar.getTimeInMillis();
        }
        
        return Arrays.asList(db_name);
    }

    @Override
    public String getType() {
        return "DAY_LONG_BASED";
    }


    @Override
    public Properties getProps() {
        return this.properties;
    }

    @Override
    public void init(Properties properties) {
        this.properties = properties;
    }
}
  • 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
@Slf4j
public class ComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {
    private Properties properties;
    @Override
    public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
        // 得到每个分片健对应的值,需要自己实现逻辑
        String db_name = complexKeysShardingValue.getLogicTableName();

        return Arrays.asList(db_name);
    }
    
    

    @Override
    public String getType() {
        return "COMPLEX_BASED";
    }


    @Override
    public Properties getProps() {
        return this.properties;
    }

    @Override
    public void init(Properties properties) {
        this.properties = properties;
    }
}
  • 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

总结:

遇到问题不要怕,多查阅官方提供的demo,不理解的参数直接问AI(chatgpt),世上无难事,只怕有心人
shardingsphere配置参数说明
shardingsphere自定义分片类示例
shardingsphere自定义分片配置示例


本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号