ShardingSphere分库分表读写分离配置

版本

springcloud版本+sharding-jdbc版本

• springcloud版本：2021.0.5
• springcloudalibaba版本：2021.0.6.0
• shardingsphere版本：5.2.1
• springboot版本：2.6.13
• mysql版本：8.0.30
• pom配置如下：

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.6.13</version> <!-- 请使用最新的Spring Boot版本 -->
    <relativePath/>
  </parent>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <spring-cloud-alibaba.version>2021.0.6.0</spring-cloud-alibaba.version>
    <spring-cloud.version>2021.0.5</spring-cloud.version>
    <jackson.version>2.16.1</jackson.version>
  </properties>
  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-dependencies</artifactId>
        <version>${spring-cloud.version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
      <dependency>
        <groupId>com.alibaba.cloud</groupId>
        <artifactId>spring-cloud-alibaba-dependencies</artifactId>
        <version>${spring-cloud-alibaba.version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>
  <dependencies>
    <!-- Spring Boot Starter -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
      <exclusions>
        <exclusion>
          <groupId>org.yaml</groupId>
          <artifactId>snakeyaml</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>org.yaml</groupId>
      <artifactId>snakeyaml</artifactId>
      <version>1.33</version>
    </dependency>
    <!-- Web Starter -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-configuration-processor</artifactId>
      <optional>true</optional>
    </dependency>
    <!-- Nacos 服务发现 -->
    <dependency>
      <groupId>com.alibaba.cloud</groupId>
      <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
    </dependency>
    <!-- 服务发现：OpenFeign服务调用 -->
    <dependency>
      <groupId>org.springframework.cloud</groupId>
      <artifactId>spring-cloud-starter-openfeign</artifactId>
    </dependency>
    <!-- 负载均衡器 -->
    <dependency>
      <groupId>org.springframework.cloud</groupId>
      <artifactId>spring-cloud-starter-loadbalancer</artifactId>
    </dependency>
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.5.8</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.30</version>
    </dependency>

    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
      <version>5.2.1</version>
      <exclusions>
        <exclusion>
          <groupId>org.yaml</groupId>
          <artifactId>snakeyaml</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>${jackson.version}</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>${jackson.version}</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-annotations</artifactId>
      <version>${jackson.version}</version>
    </dependency>
    <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
      <version>5.8.32</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.22</version> <!-- 请使用最新的版本号 -->
      <scope>provided</scope>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

配置

• 首先需要配置数据库的主从服务，参考地址为：juejin.cn/post/742669…
• yml配置

server:
  port: 8082
  shutdown: graceful
spring:
  application:
    name: test-api
  filter:
    patterns: /*
  main:
    allow-bean-definition-overriding: true
  lifecycle:
    timeout-per-shutdown-phase: 40s
  cloud:
    loadbalancer:
      ribbon:
        enabled: false
      cache:
        enabled: true
        ttl: 5s
        capacity: 256
    #Nacos注册中心配置
    nacos:
      discovery:
        enabled: true
        register-enabled: true
        server-addr:127.0.0.1
        username: nacos
        password: nacos
        group: group_center_1
        service: ${spring.application.name}
        namespace: test
        metadata:
          mgp:
            system: test
            version: 1.0
      cache:
        enabled: true
        ttl: 5s        #远程实例列表缓存时间
        capacity: 256  #缓存大小
  shardingsphere:
      datasource:
        names: test,test_slave,test_00,test_00_slave,test_01,test_01_slave
        test:
          driver-class-name: com.mysql.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
          password: huyg32
          type: com.zaxxer.hikari.HikariDataSource
          username: huyg32
        test_slave:
          driver-class-name: com.mysql.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3307/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
          password: huyg32
          type: com.zaxxer.hikari.HikariDataSource
          username: huyg32
        test_00:
          driver-class-name: com.mysql.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3306/test_00?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
          password: huyg32
          type: com.zaxxer.hikari.HikariDataSource
          username: huyg32
        test_00_slave:
          driver-class-name: com.mysql.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3307/test_00?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
          password: huyg32
          type: com.zaxxer.hikari.HikariDataSource
          username: huyg32
        test_01:
          driver-class-name: com.mysql.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3306/test_01?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
          password: huyg32
          type: com.zaxxer.hikari.HikariDataSource
          username: huyg32
        test_01_slave:
          driver-class-name: com.mysql.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3307/test_01?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
          password: huyg32
          type: com.zaxxer.hikari.HikariDataSource
          username: huyg32
      props:
        sql:
          show: true
      rules:
        readwrite-splitting:
          data-sources:
            test:
              static-strategy:
                write-data-source-name: test
                read-data-source-names: test_slave
              load-balancer-name: round_robin
            test_00:
              static-strategy:
                write-data-source-name: test_00
                read-data-source-names: test_00_slave
              load-balancer-name: round_robin
            test_01:
              static-strategy:
                write-data-source-name: test_01
                read-data-source-names: test_01_slave
              load-balancer-name: round_robin
          load-balancers:
            round_robin:
              type: ROUND_ROBIN
        sharding:
          sharding-algorithms:
            all_database_alg:
              type: CLASS_BASED
              props:
                strategy: COMPLEX
                algorithmClassName: 自定义类3
            all_table_alg:
              type: CLASS_BASED
              props:
                strategy: COMPLEX
                algorithmClassName: 自定义类4
          default-data-source-name: test
          default-table-strategy:
            complex:
              sharding-columns: id
              sharding-algorithm-name: all_table_alg
          tables:
            shop_order:
              actual-data-nodes: test_$->{(0..1).collect{t->t.toString().padLeft(2,'0')}}.shop_order_$->{2024..2025}
              database-strategy:
                complex:
                  sharding-columns: tenant_code,id
                  sharding-algorithm-name: all_database_alg
              table-strategy:
                complex:
                  sharding-columns: id
                  sharding-algorithm-name: all_table_alg




#mybatis配置
mybatis-plus:
  mapper-locations: classpath*:mapping/**/*Mapper.xml
  global-config:
    field-strategy: 2
    db-column-underline: true
    refresh-mapper: true
    logic-delete-value: 0
    logic-not-delete-value: 1
    db-config:
      db-type: mysql
      id-type: assign_id
      logic-delete-value: 1
      logic-not-delete-value: 0
      table-underline: true
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

• 分库策略：分库这边采用的是租户的方式进行分库，给定一个租户hash取模(分几个库和几取模)，例如租户号T20241020100001.hashCode()%2
• 分表策略：按照ID进行分表，ID的生成策略为按照年月+分库+10位雪花算法/年月日+10位雪花算法
• 主键生成策略

package org.example.config;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import lombok.extern.slf4j.Slf4j;

import java.util.Date;

@Slf4j
public class 类名自定义 {

    // 分为2个数据库
    private static final Integer DATA_BASE_SIZE  = 2;



    // 带租户生成的ID
    public static Long getDataIdWithTenantCode(String tenantCode){
        int tenantCodeMod = Math.abs(tenantCode.hashCode() % DATA_BASE_SIZE);
        String dataSource = String.format("%02d",tenantCodeMod);
        String date = DateUtil.format(new Date(), "yyyyMM");
        String snowFlakeId = IdUtil.getSnowflake().nextIdStr();
        log.info("生成的雪花算法ID:{}",snowFlakeId);
        String tenBitSnowFlakeId = snowFlakeId.substring(0, 10);
       return Long.parseLong(date+dataSource+tenBitSnowFlakeId);
    }


    // 不带租户生成的ID

    public static Long getDataIdWithoutTenantCode() {
        String date = DateUtil.format(new Date(), "yyyyMMdd");
        String snowFlakeId = IdUtil.getSnowflake().nextIdStr();
        log.info("生成的雪花算法ID:{}",snowFlakeId);
        String tenBitSnowFlakeId = snowFlakeId.substring(0, 10);
        return Long.parseLong(date+tenBitSnowFlakeId);
    }
}

package org.example.config;


import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;

import java.util.Objects;

@Slf4j
@Component
@Primary
public class 类名自定义2 implements IdentifierGenerator {

    String tenantCodeStr = "";
    @Override
    public Number nextId(Object entity) {

        try {
            Object tenantCode = entity.getClass().getMethod("getTenantCode").invoke(entity);
            if (Objects.nonNull(tenantCode)){
                tenantCodeStr =  tenantCode.toString();
            }
        }catch (Exception e){
            if (log.isDebugEnabled()) {
                log.warn("插入实体表没有租户信息{}",entity,e);
            }
        }

        if (StringUtils.isNotBlank(tenantCodeStr)){

            // yyyyMM 6位年月  +  2位分库信息 + 10位雪花算法随机数
          return   PrcIdGenerate.getDataIdWithTenantCode(tenantCodeStr);
        }

        // yyyyMMdd 8位年月日 + 10位雪花算法算计数    1 + 41  + 10  + 11

        return 类名自定义.getDataIdWithoutTenantCode();
    }
}

• 分库代码

package org.example.config;

import cn.hutool.core.collection.CollUtil;
import com.google.common.collect.Range;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;

import java.util.*;

/**
 * 分库策略
 */
public class 类名自定义3 implements ComplexKeysShardingAlgorithm<String> {


    private Properties props;


    private static final String TENANT_CODE = "tenant_code";

    private static final String ID = "id";

    /**
     * 这里先分为2个库
     */
    private static final int DATA_BASE_SIZE = 2;


    /**
     * 复合分库策略，先根据租户进行分库，如果没有租户，则按照ID分库
     * 精准分库：包含= ，in等
     * 范围分库： 包含> < <= >=  BETWEEN AND
     * @param availableTargetNames
     * @param shardingValues
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<String> shardingValues) {
        Map<String,Collection<String>> columnNameAndShardingValuesMap = shardingValues.getColumnNameAndShardingValuesMap();
        Map<String, Range<String>> columnNameAndRangeValuesMap = shardingValues.getColumnNameAndRangeValuesMap();
        if (ObjectUtils.isNotEmpty(columnNameAndShardingValuesMap)){
            return this.doColumnNameAndSharding(availableTargetNames,columnNameAndShardingValuesMap);
        }
        if (ObjectUtils.isNotEmpty(columnNameAndRangeValuesMap)){
           return this.doColumnNameAndRange(availableTargetNames,columnNameAndRangeValuesMap);
        }
        throw new UnsupportedOperationException();
    }


    private Collection<String> doColumnNameAndRange(Collection<String> availableTargetNames, Map<String, Range<String>> columnNameAndRangeValuesMap) {
        if (columnNameAndRangeValuesMap.containsKey(TENANT_CODE)) {
            throw new UnsupportedOperationException("落库分片键" + TENANT_CODE + "不支持范围分片");
        }
        if (columnNameAndRangeValuesMap.containsKey(ID)) {
            throw new UnsupportedOperationException("落库分片键" + ID + "不支持范围分片");
        }
        return availableTargetNames;
    }

    private Collection<String> doColumnNameAndSharding(Collection<String> availableTargetNames, Map<String, Collection<String>> columnNameAndShardingValuesMap) {

        if (columnNameAndShardingValuesMap.containsKey(TENANT_CODE)){
            Collection<String> tenantCodeValueCol = columnNameAndShardingValuesMap.get(TENANT_CODE);
            Set<String> tenantModList = new HashSet<>();
            Set<String> tenantShardingList = new HashSet<>();
            Iterator<String> tenantCodeColIterator = tenantCodeValueCol.iterator();
            while(tenantCodeColIterator.hasNext()){
                String tenantCode = tenantCodeColIterator.next();
                int tenantCodeMod = Math.abs(tenantCode.hashCode() % DATA_BASE_SIZE);
                String tenantCodeModStr = String.format("%02d", tenantCodeMod);
                tenantModList.add(tenantCodeModStr);
            }
            for (String availableTargetName: availableTargetNames){
                String availableTargetMod = availableTargetName.substring(availableTargetName.length() - 2);
                if (tenantModList.contains(availableTargetMod)){
                    tenantShardingList.add(availableTargetName);
                }

            }
            if (CollUtil.isNotEmpty(tenantShardingList)){
                return tenantShardingList;
            }
        }
        if (columnNameAndShardingValuesMap.containsKey(ID)){
             Set<String>  idModeList = new HashSet<>();
             Set<String>  idShardingList = new HashSet<>();
             Collection<String> idCol = columnNameAndShardingValuesMap.get(ID);
             Iterator<String> idColIterator = idCol.iterator();
             while(idColIterator.hasNext()){
                 String id =  String.valueOf(idColIterator.next());
                 String idMod = id.substring(6, 8);
                 idModeList.add(idMod);
             }
            for (String availableTargetName: availableTargetNames){
                String availableTargetMod = availableTargetName.substring(availableTargetName.length() - 2);
                if (idModeList.contains(availableTargetMod)){
                    idShardingList.add(availableTargetName);
                }
            }
            if (CollUtil.isNotEmpty(idShardingList)){
                return idShardingList;
            }
        }
        return availableTargetNames;
    }

    @Override
    public Properties getProps() {
        return props;
    }

    @Override
    public void init(Properties properties) {
        this.props = properties;
    }
}

• 分表代码

package org.example.config;

import cn.hutool.core.collection.CollUtil;
import com.google.common.collect.Lists;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;
import org.example.exception.BusinessException;

import java.util.*;

/**
 * 分表算法
 */
public class 类名自定义4 implements ComplexKeysShardingAlgorithm<Long> {

    private Properties props;

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValues) {
        Collection<String> result = new HashSet<>();
        Map<String, Collection<Long>> columnNameAndShardingValuesMap = shardingValues.getColumnNameAndShardingValuesMap();
        if (CollUtil.isNotEmpty(columnNameAndShardingValuesMap)){
            for (Collection<Long> ids: columnNameAndShardingValuesMap.values()){
                for (Long id: ids){
                    String shardingKey = columnNameAndShardingValuesMap.keySet().iterator().next();
                    if (columnNameAndShardingValuesMap.containsKey(shardingKey)){
                        String keyValue = String.valueOf(id);
                        result.addAll(getTargetNamesById(availableTargetNames, keyValue, shardingValues.getLogicTableName()));
                    }
                }

            }
        }
        if (CollUtil.isNotEmpty(result)){
            return result;
        }
        return availableTargetNames;
    }

    private Collection<String> getTargetNamesById(Collection<String> availableTargetNames, String keyValue, String logicTableName) {
          if (keyValue.length()<=6){
              throw new BusinessException("00001","业务ID异常，请删除历史数据重新生成");
          }
        //ID字段前8位是yyyyMMdd日期字符串，截取前6位，按年月分表
        String substr = keyValue.substring(0, 6);
        for (String availableTargetName : availableTargetNames) {
            if (availableTargetName.endsWith(substr) && availableTargetName.startsWith(logicTableName)) {
                return Lists.newArrayList(availableTargetName);
            }
        }
        //ID字段前8位是yyyyMMdd日期字符串，截取前4位，按年分表
        substr = keyValue.substring(0, 4);
        for (String availableTargetName : availableTargetNames) {
            if (availableTargetName.endsWith(substr) && availableTargetName.startsWith(logicTableName)) {
                return Lists.newArrayList(availableTargetName);
            }
        }
        //未匹配到相关表，返回所有节点表
        return availableTargetNames;
    }

    @Override
    public Properties getProps() {
        return props;
    }

    @Override
    public void init(Properties properties) {
        this.props =  properties;
    }
}

测试

• 请自测：正常的insert和query即可

动态数据源的实现

• 参考www.cnblogs.com/lwjQAQ/p/18…