ShardingSphere-JDBC 4.1.1版本复杂键分片算法配置介绍

前言

因网络资源较少，本文记录ShardingSphere-JDBC 4.1.1版本复杂键分片算法的简单配置。
本文测试项目代码地址：
https://github.com/JazzHeric/ShardingSphereComplexDatabaseShardingAlgorithm

项目主要依赖以及版本
spring-boot 2.3.0.RELEASE
sharding-jdbc-core 4.1.1
mybatis 2.1.1
alibaba-druid 1.0.23-cat-SNAPSHOT

ShardingSphere-JDBC介绍

    ShardingSphere是由当当网贡献给apache社区的项目，前身是Sharding-jdbc，其当前官网文档地址为：https://shardingsphere.apache.org/document/current/cn/user-manual/；该数据库中间件主要用于数据库、表分片，成熟可靠。在由关系型数据库提供的表分区功能，已不足以支撑越来越大的数据量要求之后，横向的扩容数据库，增加数据库的承载能力成为很多项目的必选方案。而ShardingSphere中间件则对横向扩展数据库提供了访问方式的良好支持。
    目前ShardingSphere项目下有2个成熟产品，分别为ShardingSphere-JDBC和ShardingSphere-Proxy。
按照官方文档介绍，ShardingSphere-Proxy操作数据库更简单，不需要像ShardingSphere-JDBC一样操作表时必须在筛选条件中包含分表字段，貌似能减少数据库连接数的消耗，我们暂未在生产环境使用过ShardingSphere-Proxy，对其具体表现如何暂无依据，单值得研究一下。

ShardingSphere-JDBC配置

数据库结构

分库10个

测试2张表结构如下（DDL语句见github测试项目）：
order_info

order_detail

数据源配置以及分片策略

数据源配置 com.test.sharding.config.DataSourceConfig （非关键代码较多，本文其他部分只贴较重要的部分）

package com.test.sharding.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.collect.Maps;
import com.test.sharding.config.sharding.ComplexDatabaseShardingAlgorithm;
import com.test.sharding.config.sharding.PreciseDatabaseShardingAlgorithm;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.ComplexShardingStrategyConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

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

@Configuration
public class DataSourceConfig {

    @Bean("dataSource00")
    public DataSource druidDataSource00(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl00);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource01")
    public DataSource druidDataSource01(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl01);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource02")
    public DataSource druidDataSource02(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl02);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource03")
    public DataSource druidDataSource03(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl03);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource04")
    public DataSource druidDataSource04(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl04);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource05")
    public DataSource druidDataSource05(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl05);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource06")
    public DataSource druidDataSource06(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl06);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource07")
    public DataSource druidDataSource07(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl07);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource08")
    public DataSource druidDataSource08(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl08);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    @Bean("dataSource09")
    public DataSource druidDataSource09(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl09);
        setConnectionProperty(druidDataSource);
        return druidDataSource;
    }

    private void setConnectionProperty(DruidDataSource druidDataSource) {
        druidDataSource.setUsername(userName);
        druidDataSource.setPassword(password);
        druidDataSource.setDriverClassName(driverClassName);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        druidDataSource.setValidationQuery(validationQuery);
        druidDataSource.setTestWhileIdle(testWhileIdle);
        druidDataSource.setTestOnBorrow(testOnBorrow);
        druidDataSource.setTestOnReturn(testOnReturn);
    }


    private Map<String, DataSource> buildDataSourceMap() {
        Map<String, DataSource> result = Maps.newHashMap();
        result.put("db_source_00", druidDataSource00());
        result.put("db_source_01", druidDataSource01());
        result.put("db_source_02", druidDataSource02());
        result.put("db_source_03", druidDataSource03());
        result.put("db_source_04", druidDataSource04());
        result.put("db_source_05", druidDataSource05());
        result.put("db_source_06", druidDataSource06());
        result.put("db_source_07", druidDataSource07());
        result.put("db_source_08", druidDataSource08());
        result.put("db_source_09", druidDataSource09());
        return result;
    }

    @Bean("shardingDataSource")
    DataSource getShardingDataSource() throws SQLException {

        //设置表精准sharding规则
        TableRuleConfiguration orderDetailTableRule = new TableRuleConfiguration("order_detail");
        StandardShardingStrategyConfiguration standardShardingStrategyConfiguration =
                new StandardShardingStrategyConfiguration("merchant_id",
                new PreciseDatabaseShardingAlgorithm());
        orderDetailTableRule.setDatabaseShardingStrategyConfig(standardShardingStrategyConfiguration);

        //设置表复杂sharding规则
        TableRuleConfiguration orderInfoTableRule = new TableRuleConfiguration("order_info");
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration =
                new ComplexShardingStrategyConfiguration("order_no,merchant_id",
                new ComplexDatabaseShardingAlgorithm());
        orderInfoTableRule.setDatabaseShardingStrategyConfig(complexShardingStrategyConfiguration);

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderDetailTableRule);
        shardingRuleConfig.getTableRuleConfigs().add(orderInfoTableRule);

        shardingRuleConfig.getBindingTableGroups().add("order_detail,order_info");
        //设置默认规则
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(standardShardingStrategyConfiguration);

        return ShardingDataSourceFactory.createDataSource(buildDataSourceMap(), shardingRuleConfig, new Properties());
    }

    @Bean
    public PlatformTransactionManager shardingTransactionManager() throws Exception {
        return new DataSourceTransactionManager(getShardingDataSource());
    }



    /** ---------------------数据库连接配置----------------------*/
    @Value("${spring.datasource.url_00}")
    private String dbUrl00;

    @Value("${spring.datasource.url_01}")
    private String dbUrl01;

    @Value("${spring.datasource.url_02}")
    private String dbUrl02;

    @Value("${spring.datasource.url_03}")
    private String dbUrl03;

    @Value("${spring.datasource.url_04}")
    private String dbUrl04;

    @Value("${spring.datasource.url_05}")
    private String dbUrl05;

    @Value("${spring.datasource.url_06}")
    private String dbUrl06;

    @Value("${spring.datasource.url_07}")
    private String dbUrl07;

    @Value("${spring.datasource.url_08}")
    private String dbUrl08;

    @Value("${spring.datasource.url_09}")
    private String dbUrl09;

    @Value("${spring.datasource.username}")
    private String userName;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;
}

注意分库规则设置代码：

精准分片策略需要实现接口
org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm
复杂分片策略需要实现接口
org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm

精准分片策略

精准策略根据CRUD时，配置的分片字段的值进行判断。在本测试项目中，表order_detail使用了字段merchant_id进行分片。那么preciseShardingValue即CRUD时中的merchant_id的值。根据这一值，自行决定响应分片最终结果为什么。
测试如下代码：

该精准分片策略时入参：

注意入参collection为ShardingDataSourceFactory.createDataSource时，传入的DataSource Map数据类型key。doSharding方法最终要返回准确分片的Map中的key。

复杂分片策略

复杂分片策略的灵活性更高，可以根据多个分片字段按照自定义的算法，匹配到一个或者多个目标库或目标表的key。
在本测试项目中，主要的逻辑是，当主分片字段merchant_id不存在的时候，通过order_no的最后2位（order_no生成规则约定为：最后两位带上分片库序号）匹配分片库。当分片字段merchant_id存在时，以merchant_id分片结果为准。该具体逻辑取决于系统设计时实际需求，本项目仅是测试Demo。

• 测试方法一：merchant_id和order_no同时存在
  
  
• 测试方法二：主分片字段merchant_id不存在
  
  复杂分片策略的灵活性非常高，也允许匹配出多个分片。但是在实际使用的时候，应谨慎的控制匹配的分片结果集合，一个sql如果访问多个库示例或多个表，必然会出现较大的资源消耗。另外，如果分片策略doSharding内部逻辑处理的不好，出现没有匹配的结果，即doSharding方法return结果为null，ShardingSphere-JDBC兜底策略就会出现扫描所有库实例或扫描所有表的现象发生，这种现象也一定要杜绝。