SpringBoot3.3.3+shardingsphere-jdbc5.5.0读写分离、自定义生成主键策略

developerFBI

已于 2024-12-30 19:31:19 修改

阅读量1.6k

点赞数 12

文章标签： spring boot

于 2024-12-30 19:18:28 首次发布

本文链接：https://blog.youkuaiyun.com/developerFBI/article/details/144831821

版权

最近在开发项目搭建框架时，考虑后期支付模块的订单数据量可能会比较大，于是使用现在主流的shardingsphere的读写分离、水平分表来解决后期数据量大影响查询效率的问题。

项目技术栈：jdk17+Springboot3.3.3+shardingsphere-jdbc5.5.0+mybatis-plus3.5.7+mybatis-plus-generator3.5.9+mysql8.0.20

1.报错解决方案

shardingsphere的官网上各个版本的配置有一些差异，官网文档的使用也写得不全，全靠看别人blog和查看源码对应去解决。特别注意自定义class所在resources的文件路为META-INF\services\org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm让我花了一天的时间才解决。

若resources下的包名和文件名不是上面标红所示，会报错：

报这个错其实主要是我的自定义生成主键策略类没有加载进来，一起这个错，搞我真死了好多脑细胞。

SPI-00001: No implementation class load from SPI 'org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm' with type 'MY_KEY_GENERATE_ALGORITHM'.

org.apache.shardingsphere.infra.spi.exception.ServiceProviderNotFoundException: SPI-00001: No implementation class load from SPI 'org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm' with type 'MY_KEY_GENERATE_ALGORITHM'.
	at org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader.checkService(TypedSPILoader.java:129) ~[shardingsphere-infra-spi-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.lambda$checkKeyGeneratorAlgorithms$1(ShardingRuleConfigurationChecker.java:76) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183) ~[na:na]
	at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179) ~[na:na]
	at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133) ~[na:na]
	at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1845) ~[na:na]
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[na:na]
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[na:na]
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150) ~[na:na]
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173) ~[na:na]
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[na:na]
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596) ~[na:na]
	at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.checkKeyGeneratorAlgorithms(ShardingRuleConfigurationChecker.java:76) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.check(ShardingRuleConfigurationChecker.java:60) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.sharding.checker.ShardingRuleConfigurationChecker.check(ShardingRuleConfigurationChecker.java:55) ~[shardingsphere-sharding-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.infra.rule.builder.database.DatabaseRulesBuilder.build(DatabaseRulesBuilder.java:67) ~[shardingsphere-infra-common-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase.create(ShardingSphereDatabase.java:91) ~[shardingsphere-infra-common-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.metadata.factory.ExternalMetaDataFactory.createGenericDatabases(ExternalMetaDataFactory.java:85) ~[shardingsphere-metadata-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.metadata.factory.ExternalMetaDataFactory.create(ExternalMetaDataFactory.java:72) ~[shardingsphere-metadata-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.mode.metadata.MetaDataContextsFactory.create(MetaDataContextsFactory.java:98) ~[shardingsphere-mode-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.mode.metadata.MetaDataContextsFactory.create(MetaDataContextsFactory.java:72) ~[shardingsphere-mode-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.mode.manager.standalone.StandaloneContextManagerBuilder.build(StandaloneContextManagerBuilder.java:53) ~[shardingsphere-standalone-mode-core-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.createContextManager(ShardingSphereDataSource.java:79) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.<init>(ShardingSphereDataSource.java:67) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:95) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:135) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:70) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.createDataSource(DriverDataSourceCache.java:55) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.lambda$get$0(DriverDataSourceCache.java:48) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at java.base/java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1708) ~[na:na]
	at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.get(DriverDataSourceCache.java:48) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at org.apache.shardingsphere.driver.ShardingSphereDriver.connect(ShardingSphereDriver.java:56) ~[shardingsphere-jdbc-5.5.0.jar:5.5.0]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1682) ~[druid-1.2.23.jar:na]
	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1803) ~[druid-1.2.23.jar:na]
	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2914) ~[druid-1.2.23.jar:na]

查看shardingsphere5.5.0官网关于此信息链接如下：

服务器错误码 :: ShardingSphere

官网写得的这个提示没卵用！

后面查看shardingsphere5.5.0的源码发现UUIDKeyGenerateAlgorithm.java和SnowflakeKeyGenerateAlgorithm.java都是继承了KeyGenerateAlgorithm.java，然后根据上面的报错，猜测是我的resources包名和文件名不对，于是改源码中的包名和文件名就OK了。

2.我的项目配置：

3.自定义策略的类 MyKeyGenerateAlgorithm.java

package com.tfq.shardingshperedemo.config;


import org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext;
import org.apache.shardingsphere.infra.algorithm.keygen.core.KeyGenerateAlgorithm;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedList;
import java.util.concurrent.ThreadLocalRandom;

/**
 * @Description: 生成支付订单号算法
 * @Author: tfq
 * @Date: 2024-12-26 15:33
 */

public final class MyKeyGenerateAlgorithm implements KeyGenerateAlgorithm {

    /**
     * @param context          algorithm SQL context
     * @param keyGenerateCount key generate count
     * @return
     */
    @Override
    public Collection<String> generateKeys(final AlgorithmSQLContext context, final int keyGenerateCount) {
        Collection<String> result = new LinkedList<>();
        ThreadLocalRandom threadLocalRandom = ThreadLocalRandom.current();
        for (int index = 0; index < keyGenerateCount; index++) {
            result.add(generateKey(threadLocalRandom));
        }
        return result;
    }

    /**
     * 订单编号：时间戳+6位随机数，例如："20241127124523"+"123456"，前面是日期时间戳，后面是订单号
     *
     * @param threadLocalRandom
     * @return
     */
    private String generateKey(ThreadLocalRandom threadLocalRandom) {
        String nowDate = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String randomNum = threadLocalRandom.nextLong(100000, 999999) + "";
        return nowDate + randomNum;
    }

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

上面getType返回的String:MY_KEY_GENERATE_ALGORITHM要配置到下shardingsphere-level-table.yaml的keyGenerators(生成主键方法)的type后面。

4.pom.xml的配置如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.tfq</groupId>
    <artifactId>shardingshperedemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingshperedemo</name>
    <description>shardingshperedemo</description>

    <properties>
        <java.version>17</java.version>
        <maven.compiler.source>${java.version}</maven.compiler.source>
        <shardingsphere.version>5.5.0</shardingsphere.version>
        <spring.boot.version>3.3.3</spring.boot.version>
        <!-- DB 相关 -->
        <mysql.version>8.0.33</mysql.version>
        <druid.version>1.2.23</druid.version>
        <mybatis-plus.version>3.5.7</mybatis-plus.version>
        <mybatis-plus-generator>3.5.9</mybatis-plus-generator>
    </properties>


    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>6.1.14</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>6.1.14</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>6.1.14</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>6.1.14</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-3-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.github.yulichang</groupId>
            <artifactId>mybatis-plus-join-boot-starter</artifactId>
            <version>1.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>${mybatis-plus-generator}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>6.1.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc</artifactId>
            <version>${shardingsphere.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.shardingsphere</groupId>
                    <artifactId>shardingsphere-test-util</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.14.0</version>
        </dependency>
        <dependency>
            <groupId>com.google.protobuf</groupId>
            <artifactId>protobuf-java</artifactId>
            <version>3.25.5</version>
        </dependency>

        <dependency>
            <groupId>com.github.yulichang</groupId>
            <artifactId>mybatis-plus-join</artifactId>
            <version>1.4.13</version>
        </dependency>
        <!-- 测试使用-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.xmlunit</groupId>
            <artifactId>xmlunit-core</artifactId>
            <version>2.10.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.platform</groupId>
            <artifactId>junit-platform-launcher</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <!-- 用于生成自定义的 Spring @ConfigurationProperties 配置类的说明文件 -->
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>
        <!-- swagger-->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-openapi3-jakarta-spring-boot-starter</artifactId>
            <version>4.5.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/io.github.classgraph/classgraph -->
        <dependency>
            <groupId>io.github.classgraph</groupId>
            <artifactId>classgraph</artifactId>
            <version>4.8.112</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>6.1.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-core</artifactId>
            <version>10.1.31</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/cn.hutool/hutool-core -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-core</artifactId>
            <version>5.8.25</version>
        </dependency>


    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

5.application.yaml配置如下

server:
  port: 8888

spring:
  application:
    name: shardingshperedemo
  datasource:
    driverClassName: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:shardingsphere-level-table.yaml
    #在shardingshere的配置参考官网：
    #https://shardingsphere.apache.org/document/5.5.0/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/readwrite-splitting/

  mybatis-plus:
    configuration:
      map-underscore-to-camel-case: true #开启下划线转驼峰
    mapper-locations: classpath*:/mapper/**/*.xml #Mapper.xml文件地址，默认值
    type-aliases-package: com.tfq.shardingshperedemo.entity #别名扫描包

# springdoc-openapi项目配置
springdoc:
  swagger-ui:
    #自定义swagger前端请求路径,输入http:localhost:8080/swagger-ui会自动重定向到swagger页面
    path: /swagger-ui
    tags-sorter: alpha
    operations-sorter: alpha
  #此项配置解决controller的Getmapping的参数对象展开并能传递对象参数的值到后台
  default-flat-param-object: true # 参见 https://doc.xiaominfo.com/docs/faq/v4/knife4j-parameterobject-flat-param 文档
  api-docs:
    path: /v3/api-docs  #swagger后端请求地址
    enabled: true   #是否开启文档功能
  group-configs: #分组配置,可配置多个分组
    - group: 'default'             #分组名称
      paths-to-match: '/**'        #配置需要匹配的路径
      packages-to-scan: com.tfq.shardingshperedemo.controller    #配置要扫描包的路径,一般配置到启动类所在的包名
    - group: 'admin-api'
      paths-to-match: '/**'
      packages-to-scan: com.tfq.shardingshperedemo.controller

6.shardingsphere-level-table.yaml配置如下

#水平分表-数据源配置
dataSources:
  ds1: #数据源名称-用于水平分表
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.56.10:3306/course_db?serverTimeznotallow=GMT%2B8&useSSL=false&rewriteBatchedStatements=true
    username: root
    password: 123456

#数据分片规则配置:指定数据库存表course_1表分布情况,表名是什么
rules:
  - !SINGLE #不分库分表的表在 ShardingSphere 中叫做单表，可以使用 LOAD 语句或者 SINGLE 规则配置需要加载的单表。
    tables:
      - "*.*"
  # 数据分片
  - !SHARDING
    tables:
      course: #逻辑表名
        actualDataNodes: ds1.course_${0..1} #由数据源名 + 表名组成（参考 Inline 语法规则）
        tableStrategy: #指定分片策略，约定cid的值偶数添加到course_1,cid的值为奇数则添加到course_2
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: t-course-inline # 分片算法名称（必须以中划线命名）
        keyGenerateStrategy:
          column: cid
          keyGeneratorName: My-PAYORDER-ID 
    shardingAlgorithms: #分片算法配置
      t-course-inline: # 分片算法名称（必须以中划线命名）
        type: INLINE # 分片算法类型
        props: #分片算法属性配置
          algorithm-expression: course_$->{user_id % 2}
    keyGenerators:
      My-PAYORDER-ID:
        type: MY_KEY_GENERATE_ALGORITHM
        props:
          worker:
            id: 1  # 可选：传递给自定义算法的配置
props:
  max.connections.size.per.query: 10
  acceptor.size: 200  # The default value is available processors count * 2.
  executor.size: 200  # Infinite by default.
  query.with.cipher.column: true
  sql-show: true
  allow.range.query.with.inline.sharding: false
  check.table.metadata.enabled: false

7.测试代码

ShardingshperedemoApplicationTests.java

package com.tfq.shardingshperedemo;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.tfq.shardingshperedemo.dto.UserDto;
import com.tfq.shardingshperedemo.entity.Course;
import com.tfq.shardingshperedemo.entity.Udict;
import com.tfq.shardingshperedemo.entity.User;
import com.tfq.shardingshperedemo.mapper.CourseMapper;
import com.tfq.shardingshperedemo.mapper.UdictMapper;
import com.tfq.shardingshperedemo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import java.util.Random;

@SpringBootTest
class ShardingshperedemoApplicationTests {

    @Autowired
    private CourseMapper courseMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private UdictMapper udictMapper;

    /**
     * 水平分表
     * 添加课程方法
     */
    @Test
    void addCourse() throws InterruptedException {
        for (int i = 0; i < 10; i++) {
            Course course = new Course();
            course.setCname("ruby" + i);
            course.setCstatus("Normal");
            course.setUserId(103L + i);
            courseMapper.insert(course);
            Thread.sleep(1000);
        }

    }

    /**
     * 水平分表-查询课程的方法
     */
    @Test
    public void findCourse() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("cid", 1069938186098049024L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }
   

}

8.运行结果

插入数据库数据：

查询运行结果：

2024-12-30T18:56:13.879+08:00  INFO 31644 --- [shardingshperedemo] [           main] ShardingSphere-SQL                       : Logic SQL: SELECT    cid,cname,user_id,cstatus    FROM  course         WHERE  (cid = ?)
2024-12-30T18:56:13.880+08:00  INFO 31644 --- [shardingshperedemo] [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT    cid,cname,user_id,cstatus    FROM  course_0         WHERE  (cid = ?) UNION ALL SELECT    cid,cname,user_id,cstatus    FROM  course_1         WHERE  (cid = ?) ::: [20241230184613690172, 20241230184613690172]
Course(cid=20241230184613690172, cname=ruby1, userId=104, cstatus=Normal, users=null)

若大家还有问题请留言在解答。

项目下载地址：https://download.youkuaiyun.com/download/developerFBI/90205506

参考文档：

FAQ :: ShardingSphere

基础算法 :: ShardingSphere

springboot整合ShardingSphere5.2.1（最新版）_spi-00001: no implementation class load from spi `-优快云博客

Docker部署Mysql8.0.20并配置主从复制 - C3Stones - 博客园