springboot、dynamic-datasource整合shardingsphere-jdbc多租户方案参考

总体设计

一个租户一套分片表

相关Maven依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>5.4.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>1.33</version>
</dependency>

配置文件

application.yml

其中{...}为占位符，自行修改

spring:
  datasource:
    dynamic:
      # 设置默认的数据源或者数据源组,默认值即为 master
      primary: master
      datasource:
        # 主库数据源
        master:
          type: ${spring.datasource.type}
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: {...}
          username: {...}
          password: {...}
        # 分表数据源
        sharding:
          url: jdbc:shardingsphere:classpath:sharding.yaml
          driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver

sharding.yml

mode:
  type: Standalone
  repository:
    type: JDBC
dataSources:
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: {...}
    username: {...}
    password: {...}
  ds_2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: {...}
    username: {...}
    password: {...}
  ds_3:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: {...}
    username: {...}
    password: {...}
  ds_4:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: {...}
    username: {...}
    password: {...}
rules:
# 分片配置
- !SHARDING
  tables:
    sharding_one: 
      actualDataNodes: ds_$->{1..4}.sharding_one_$->{com.example.sharding.common.utils.CommonUtil.getActualDataNodes()}
      tableStrategy: 
        standard:
          shardingColumn: tenant_code
          shardingAlgorithmName: common_sharding
    sharding_one_item: 
      actualDataNodes: ds_$->{1..4}.sharding_one_item_$->{com.example.sharding.common.utils.CommonUtil.getActualDataNodes()}
      tableStrategy: 
        standard:
          shardingColumn: tenant_code
          shardingAlgorithmName: common_sharding
  bindingTables:
    - sharding_one,sharding_one_item
#  defaultShardingColumn: id
  defaultDatabaseStrategy:
    standard:
      shardingColumn: tenant_code
      shardingAlgorithmName: database_sharding
  defaultTableStrategy:
    none:

  shardingAlgorithms:
    database_sharding:
      type: CLASS_BASED
      props:
        strategy: STANDARD
        algorithmClassName: com.example.sharding.common.sharding.DatabaseShardingAlgorithm
    common_sharding:
      type: CLASS_BASED
      props:
        strategy: STANDARD
        algorithmClassName: com.example.sharding.common.sharding.CommonShardingAlgorithm

# 广播表配置
# 广播表需要在每个分片库中均存在
- !BROADCAST
  tables:
    - broadcast_dict

# 全局参数
props:
  sql-show: true

分片表实际节点静态方法

// CommonUtil.java

import cn.hutool.core.collection.CollUtil;
import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@UtilityClass
public class CommonUtil {

   public List<String> getActualDataNodes() {

		List<String> ret = CollUtil.newArrayList();

        
		// TODO 根据 tenant_code 的取值范围 预先创建出分片表的范围

        // 基础表 需要在首个分片库中预先创建 如 sharding_one_ 
		ret.add(0, "");
		return ret;
	}

}

默认分库算法实现类

// DatabaseShardingAlgorithm.java
import java.util.Collection;
import java.util.Map;

import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import lombok.extern.slf4j.Slf4j;
@Slf4j
public class DatabaseShardingAlgorithm implements StandardShardingAlgorithm<String> {
	
    // 配置的分片库前缀，参见sharding.yml
	private static final String DB_PREFIX = "ds_";
	// 租户与租户分片表所在的分片库的映射
	public static Map<String,Integer> databaseShardingMap;
    // 每个分片库所含租户数的映射
	public static Map<Integer,Long> databaseShardingGroup;
	// 分片库数量
	public static Integer shardingDatabaseCount;
	
	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        // 分片字段的值，即 tenant_code
		String value = shardingValue.getValue();
		Integer curDbIndex = databaseShardingMap.get(value);
		return DB_PREFIX + curDbIndex;
	}

	@Override
	public Collection<String> doSharding(Collection<String> availableTargetNames,
			RangeShardingValue<String> shardingValue) {
		return null;
	}

}

通用分表算法实现类

// CommonShardingAlgorithm.java

import java.util.Collection;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class CommonShardingAlgorithm implements StandardShardingAlgorithm<String> {

	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        // 分片字段的值，即 tenant_code
		String value = shardingValue.getValue();

        // 查找对应租户的分片表
		Map<String, String> availableTargetMap = availableTargetNames.stream().collect(Collectors.toMap(item -> {
			return item.substring(item.lastIndexOf(StrUtil.C_UNDERLINE) + 1);
		}, Function.identity()));

		return availableTargetMap.get(value);
	}

	@Override
	public Collection<String> doSharding(Collection<String> availableTargetNames,
			RangeShardingValue<String> shardingValue) {
		return null;
	}

}

相关实现

在master数据库中创建一张分片记录表，用于记录各租户分片表所在的分片数据库

-- sharding_record
CREATE TABLE `sharding_record` (
  `id` bigint NOT NULL COMMENT '主键',
  `ds` int DEFAULT NULL COMMENT '数据源序号',
  `tenant_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '租户编号'
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tenant_code` (`tenant_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='分片记录表';

在项目启动的时候加载到映射缓存中：

// ApplicationInitializer.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;

import lombok.extern.slf4j.Slf4j;
@Slf4j
@Component
public class ApplicationInitializer implements ApplicationRunner {


    @Override
	public void run(ApplicationArguments args) throws Exception {
        // TODO 
        // 1.查询分片记录表的列表
        // 2.将列表数据转换成 DatabaseShardingAlgorithm.databaseShardingMap

    }

}

mybatis测试sql：

SELECT * FROM sharding_one 
WHERE tenant_code = {...}

注意此处 terant_code 条件必须有，通过terant_code，会将分片逻辑表明sharding_one转换成对应的分片表名sharding_one_{terant_code}以及所在的分片库，然后再执行sql语句。

参考

shardingsphere官方文档地址

《dynamic-datasource+shardingsphere-jdbc实现分库分表》

《关于 ShardingSphere 5.4.0，一文全知道！》