在多資料來源中對部分資料表使用shardingsphere進行分庫分表

背景

近期在專案中需要使用多資料來源，其中有一些表的資料量比較大，需要對其進行分庫分表；而其他資料表資料量比較正常，單表就可以。
專案中可能使用其他組的資料來源資料，因此需要多資料來源支援。
經過調研多資料來源配置比較方便。在該專案中分庫分表的策略比較簡單，僅根據一個欄位分就可以，因此分庫分表方案選用比較流行方便易用的 sharding-jdbc
需要實現的目標是 根據學生姓名欄位 student_name 進行分表，但是不需要分庫。資料表是student_hist0 - student_hist9

引入 sharding-jdbc maven 依賴

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>4.1.1</version>
</dependency>

資料來源配置檔案

spring:
  application:
    name: student-service-provider
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    defaultPropertyInclusion: non_null
    deserialization:
      FAIL_ON_UNKNOWN_PROPERTIES: false 
  #對返回的時間進行格式化
  datasource:
    hikari:
      student:
        url: jdbc:mysql://127.0.0.1:3306/student_service?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useTimezone=true&serverTimezone=GMT%2
        username: root
        password: root123
      log1:
        url: jdbc:mysql://127.0.0.1:3306/log1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password: root123
      log2:
        url: jdbc:mysql://127.0.0.1:3306/log2?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password: root123

配置多資料來源程式碼

DataSourceProperties 資料來源

import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.hikari")
public class DataSourceProperties {

    private HikariDataSource student;
	private HikariDataSource log1;
	private HikariDataSource log2;

}

DynamicDataSource 動態資料來源

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

    /*
    當前據源名稱
     */
    private static final ThreadLocal<String> dataSourceContextHolder = new ThreadLocal<>();

    /*
    設定資料來源名稱
     */
    public static void setDataSourceName(String dataSourceName) {
        dataSourceContextHolder.set(dataSourceName);
    }

    /*
    獲取據源名稱
     */
    public static String getDataSourceName() {
        return dataSourceContextHolder.get();
    }

    /*
    清除當資料來源名稱
     */
    public static void clearDataSource() {
        dataSourceContextHolder.remove();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSourceName();
    }
}

MultiDataSource 多資料來源標記

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface MultiDataSource {
    String value() default DataSourceConfig.DEFAULT_DATASOURCE_NAME;
}

重點來了，因為是根據表的某一個欄位進行分表，該欄位是一個字串型別，因此需要想根據字串的一致性hash碼算出在哪張表上。在sharding-jdbc需要實現 PreciseShardingAlgorithm 類
例如：想要在student.student_hist 表中根據學生姓名進行分表，邏輯表是student_hist，真實表是 student_hist0 - student_hist9
DataSourceConfig.SHARD_MMS_DATASOURCE_TABLE_COUNT=10

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;

public class PreciseNodeIdShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        for (String tbnm : collection) {
            if (tbnm.endsWith("hist" + (getHash(preciseShardingValue.getValue()) % DataSourceConfig.SHARD_MMS_DATASOURCE_TABLE_COUNT))) {
                return tbnm;
            }
        }
        throw new UnsupportedOperationException();
    }
	
	private static int getHash(String str) {
        final int p = 16777619;
        int hash = (int) 2166136261L;
        for (int i = 0; i < str.length(); i++)
            hash = (hash ^ str.charAt(i)) * p;
        hash += hash << 13;
        hash ^= hash >> 7;
        hash += hash << 3;
        hash ^= hash >> 17;
        hash += hash << 5;
 
        // 如果算出來的值為負數則取其絕對值
        if (hash < 0)
            hash = Math.abs(hash);
        return hash;
    }
}

多資料來源裝配 DataSourceConfig 。需要指定預設資料來源，當不需要使用 分表的表時就使用預設的資料來源，否則指定需要分表的資料來源。
在配置分表策略時如果不需要分庫，可以不進行設定 tableRuleConfiguration.setDatabaseShardingStrategyConfig();

import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
public class DataSourceConfig {

    public static final String DEFAULT_DATASOURCE_NAME = "defaultDataSource";
    public static final String MMS_DATASOURCE_NAME = "mmsDatasource";
    public static final String SHARD_MMS_DATASOURCE_NAME = "shardMmsDatasource";

    public static int SHARD_MMS_DATASOURCE_TABLE_COUNT = 10;

    @Autowired
    private DataSourceProperties properties;

    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 預設資料來源
        dynamicDataSource.setDefaultTargetDataSource(properties.getMms());
        // 配置多資料來源
        Map<Object, Object> dsMap = new HashMap();
        dsMap.put(DEFAULT_DATASOURCE_NAME, properties.getStudent());
        dsMap.put(MMS_DATASOURCE_NAME, properties.getStudent());
        dsMap.put(SHARD_MMS_DATASOURCE_NAME, buildShardDatasources());
        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    public DataSource buildShardDatasources() {
        // 配置多資料來源
        Map<String, DataSource> dsMap = new HashMap();
        dsMap.put("shardMms", properties.getMms());
        TableRuleConfiguration stuHisTableRuleConfig = new TableRuleConfiguration("student_hist", "shardMms.student_hist${0.." + (SHARD_MMS_DATASOURCE_TABLE_COUNT - 1) + "}");
//        tableRuleConfiguration.setDatabaseShardingStrategyConfig();
        stuHisTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("student_name", new PreciseNodeIdShardingAlgorithm()));

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(stuHisTableRuleConfig);
        try {
            Properties properties = new Properties();
            properties.setProperty("sql.show", "true");
            return ShardingDataSourceFactory.createDataSource(dsMap, shardingRuleConfig, properties);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new IllegalArgumentException();
        }
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

}

多資料來源切換 DataSourceAspect ,需要使用多資料來源切換時，需要在service方法上使用標註方法 MultiDataSource 並指定資料來源。

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import java.lang.reflect.Method;

@Aspect
@Configuration
@Slf4j
@Order(1)
public class DataSourceAspect {

    //切入點，service 中所有註解方法
    @Pointcut("execution(* com.huitong..service..*.*(..)) && @annotation(com.huitong.app.config.datasource.MultiDataSource)")
    public void dataSourceAspect() {
    }

    @Around("dataSourceAspect()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        MethodSignature signature = (MethodSignature) joinPoint.getSignature();
        Method method = signature.getMethod();
        MultiDataSource ds = method.getAnnotation(MultiDataSource.class);
        if (ds != null) {
            DynamicDataSource.setDataSourceName(ds.value());
        }
        try {
            return joinPoint.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
        }
    }
}

參考文獻：

• https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/usage/sharding/
• https://www.cnblogs.com/lpfuture/p/5796398.html
• https://blog.csdn.net/u010558660/article/details/52767218