sharding-jdbc使用详解

一、概念先行

1. SQL相关的

逻辑表：水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为2张表，分别是t_order_0到t_order_1，他们的逻辑表名为t_order。

真实表：在分片的数据库中真实存在的物理表。例：示例中的t_order_0到t_order_1

数据节点：数据分片的最小单元。由数据源名称和数据表组成，例：ds_0.t_order_0；ds_0.t_order_1；

绑定表：指分片规则一致的主表和子表。例如：t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。

广播表：指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表，示例中的t

2. 分片相关

分片键：用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。

分片算法：精确分片算法 / 范围分片算法 / 复合分片算法 / Hint分片算法

分片策略：标准分片策略 / 复合分片策略 / 行表达式分片策略 / Hint分片策略 / 不分片策略

3. 配置相关

分片规则：分片规则配置的总入口。包含数据源配置、表配置、绑定表配置以及读写分离配置等。

数据源配置：真实数据源列表。

表配置：逻辑表名称、数据节点与分表规则的配置

数据节点配置：用于配置逻辑表与真实表的映射关系。

分片策略配置：

a. 数据源分片策略：对应于DatabaseShardingStrategy。用于配置数据被分配的目标数据源。

b. 表分片策略：对应于TableShardingStrategy。用于配置数据被分配的目标表，该目标表存在与该数据的目标数据源内。故表分片策略是依赖与数据源分片策略的结果的。

c. 自增主键生成策略：通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复。（雪花算法）

二、pom

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

三、ShardingJDBC提供了5种分片策略及分片算法

1、标准分片策略 StandardShardingStrategyConfiguration

支持单个分片键，对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。

a. yml

spring:
  shardingsphere:
    props:
      sql.show: true #是否输出sql
    datasource:
      names: ds0 #指定数据源 名称可以自定义，注意：名称要跟后面的配置一致
      ds0: #配置数据源的连接信息
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.52.10:3306/ball_dashboard?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: Yifan123.
    sharding:
      # 默认数据源
      default-data-source-name: ds0
      tables:
        #逻辑表名
        db_trading:
          key-generator:
            colun: id #主键
          #分表数据节点
          actual-data-nodes: ds0.db_trading_$->{2022..2023}$->{(1..12).collect{t -> t.toString().padLeft(2,'0')}}    #数据节点,均匀分布
          #分库策略
          #database-strategy:
          #  standard:
          #    algorithm-class-name: com.chain.utils.HintShardingKeyAlgorithm
          #分表策略
          table-strategy:
            #标准分片策略
            standard:
              shardingColumn: commission_time
              # 精确分片算法，用于 = 和 IN
              precise-algorithm-class-name: com.chain.config.shardingjdbc.StandardShardingAlgorithm
              # 范围分片算法类名称，用于 范围查询 可选
              range-algorithm-class-name: com.chain.config.shardingjdbc.StandardShardingAlgorithm

b. 精确分片算法 / 范围分片算法

/**
 * 标准分片策略
 */
public class StandardShardingAlgorithm implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {
    /**
     * 精确匹配算法，可以实现对 `=`以及`in`的查询（必选）
     *
     * @param tbNames       规则中定义的真实表
     * @param shardingValue 分片字段和值
     * @return 返回匹配的数据表名
     */
    @Override
    public String doSharding(Collection<String> tbNames, PreciseShardingValue<Date> shardingValue) {
        String index = DateUtil.format(shardingValue.getValue(), "yyyyMM");
        for (String tableName : tbNames) {
            // 匹配满足当前分片规则的表名称
            if (tableName.endsWith(index)) {
                return tableName;
            }
        }
        throw new RuntimeException("数据表不存在");
    }

    /**
     * 范围匹配算法，可以实现对 > < >= <= between and 的查询（非必选，不配置则全库路由处理）
     */
    @Override
    public Collection<String> doSharding(Collection<String> tbNames, RangeShardingValue<Date> rangeShardingValue) {
        // 获取逻辑表名称
        String logicTableName = rangeShardingValue.getLogicTableName();

        Date lower = rangeShardingValue.getValueRange().hasLowerBound() ? DateUtil.beginOfMonth(rangeShardingValue.getValueRange().lowerEndpoint()) : null;
        Date upper = rangeShardingValue.getValueRange().hasUpperBound() ? DateUtil.beginOfMonth(rangeShardingValue.getValueRange().upperEndpoint()) : null;

        List<String> tableNameList = new ArrayList<>();
        //当只有最大值或者只有最小值时 >= || <= || > || <
        if (ObjectUtil.isEmpty(lower) || ObjectUtil.isEmpty(upper)) {
            //循环所有定义的真实表
            for (String tbName : tbNames) {
                String dateStr = tbName.split("_")[2];
                Date date = DateUtil.parse(dateStr, "yyyyMM");
                //当只有最小值时，最小值时间应小于逻辑表时间，当只有最大值时，逻辑表时间应小于最大值时间
                if ((ObjectUtil.isNotEmpty(lower) ? lower.compareTo(date) : date.compareTo(upper)) <= 0) {
                    tableNameList.add(logicTableName + "_" + dateStr);
                }
            }
        }
        //当使用Between and 时
        else {
            //便利目标中间所有时间段
            for (; lower.compareTo(upper) <= 0; lower = DateUtils.addMonths(lower, 1)) {
                String tableName = logicTableName + "_" + DateUtil.format(lower, "yyyyMM");
                //判断是否存在定义的真实表
                if (tbNames.contains(tableName)) {
                    tableNameList.add(tableName);
                }
            }
        }
        return tableNameList;
    }
}

2、复合分片策略 ComplexShardingStrategyConfiguration

支持多个分片键，对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

a. yml

spring:
  shardingsphere:
    props:
      sql.show: true #是否输出sql
    datasource:
      names: ds0 #指定数据源 名称可以自定义，注意：名称要跟后面的配置一致
      ds0: #配置数据源的连接信息
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.52.10:3306/ball_dashboard?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: Yifan123.
    sharding:
      # 默认数据源
      default-data-source-name: ds0
      tables:
        #逻辑表名
        db_trading:
          key-generator:
            colun: id #主键
          #分表数据节点
          actual-data-nodes: ds0.db_trading_$->{2022..2023}$->{(1..12).collect{t -> t.toString().padLeft(2,'0')}}    #数据节点,均匀分布
          #分库策略
          #database-strategy:
          #  standard:
          #    algorithm-class-name: com.chain.utils.HintShardingKeyAlgorithm
          #分表策略
          table-strategy:
            #复合分片策略
            complex:
              sharding-columns: commission_time,category
              # 精确+范围分片算法
              algorithm-class-name: com.chain.config.shardingjdbc.ComplexShardingAlgorithm

b. 精确分片算法 / 范围分片算法

/**
 * 复合分片算法
 */
public class ComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {
    /**
     * 精确匹配查询 + 范围匹配查询
     *
     * @param availableTargetNames     分片相关信息["db_trading_202201","db_trading_202202","db_trading_202203"]
     * @param complexKeysShardingValue 数据库中所有的真实表{"columnNameAndRangeValuesMap":{"category":[a..b]],"commission_time":[2023-07-07 17:02:03.0..2023-07-08 00:00:00.0]},"columnNameAndShardingValuesMap":{},"logicTableName":"db_trading"}
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue complexKeysShardingValue) {
        System.out.println(JSON.toJSONString(availableTargetNames));
        System.out.println(JSON.toJSONString(complexKeysShardingValue));
        // 获取逻辑表名称
        String logicTableName = complexKeysShardingValue.getLogicTableName();
        List<String> tableNameList = new ArrayList<>();

        //1. 处理精确查找 = in
        Set<Map.Entry<String, List>> columnNameAndShardingValuesSet = complexKeysShardingValue.getColumnNameAndShardingValuesMap().entrySet();
        if (columnNameAndShardingValuesSet.size() > 0) {
            Boolean hasCommissionTime = columnNameAndShardingValuesSet.stream().anyMatch(i -> "commission_time".equals(i.getKey()));
            Boolean hasCategory = columnNameAndShardingValuesSet.stream().anyMatch(i -> "category".equals(i.getKey()));

            //1.1 有commission_time 无category
            if (hasCommissionTime && !hasCategory) {
                Map.Entry<String, List> commissionTimeMap = columnNameAndShardingValuesSet.stream().filter(i -> "commission_time".equals(i.getKey())).findFirst().get();
                List<Date> shardingValues = commissionTimeMap.getValue();
                for (Date value : shardingValues) {
                    for (Object availableTargetName : availableTargetNames) {
                        Date date = DateUtil.parse(String.valueOf(availableTargetName).split("_")[2], "yyyyMM");
                        if (DateUtil.year(date) == DateUtil.year(value)) {
                            tableNameList.add(String.valueOf(availableTargetName));
                        }
                    }
                }
            }
            //1.2 无commission_time 有category
            else if (!hasCommissionTime && hasCategory) {
                Map.Entry<String, List> categoryMap = columnNameAndShardingValuesSet.stream().filter(i -> "category".equals(i.getKey())).findFirst().get();
                List<String> shardingValues = categoryMap.getValue();
                for (String value : shardingValues) {
                    for (Object availableTargetName : availableTargetNames) {
                        Date date = DateUtil.parse(String.valueOf(availableTargetName).split("_")[2], "yyyyMM");
                        if (DateUtil.month(date) == Math.abs(value.hashCode()) % 12) {
                            tableNameList.add(String.valueOf(availableTargetName));
                        }
                    }
                }
            }
            //1.3 有commission_time 有category
            else if (hasCommissionTime && hasCategory) {
                Map.Entry<String, List> commissionTimeMap = columnNameAndShardingValuesSet.stream().filter(i -> "commission_time".equals(i.getKey())).findFirst().get();
                List<Date> shardingYearValues = commissionTimeMap.getValue();
                Map.Entry<String, List> categoryMap = columnNameAndShardingValuesSet.stream().filter(i -> "category".equals(i.getKey())).findFirst().get();
                List<String> shardingMonthValues = categoryMap.getValue();
                for (Date yearValue : shardingYearValues) {
                    for (String monthValue : shardingMonthValues) {
                        Integer year = DateUtil.year(yearValue);
                        Integer month = Math.abs(monthValue.hashCode()) % 12 + 1;
                        tableNameList.add(logicTableName + "_" + year + String.format("%02d", month));
                    }
                }
            }
        }

        //2. 处理范围 > < >= <= between and
        Set<Map.Entry<String, Range>> columnNameAndRangeValuesMap = complexKeysShardingValue.getColumnNameAndRangeValuesMap().entrySet();
        if (columnNameAndRangeValuesMap.size() > 0) {
            Boolean hasCommissionTime = columnNameAndRangeValuesMap.stream().anyMatch(i -> "commission_time".equals(i.getKey()));
            if (hasCommissionTime) {
                Map.Entry<String, Range> commissionTimeMap = columnNameAndRangeValuesMap.stream().filter(i -> "commission_time".equals(i.getKey())).findFirst().get();
                Range shardingValues = commissionTimeMap.getValue();
                Date lower = DateUtil.beginOfMonth((Date) shardingValues.lowerEndpoint());
                Date upper = DateUtil.beginOfMonth((Date) shardingValues.upperEndpoint());

                if (ObjectUtil.isEmpty(lower) || ObjectUtil.isEmpty(upper)) {
                    //循环所有定义的真实表
                    for (Object availableTargetName : availableTargetNames) {
                        String tbName = String.valueOf(availableTargetName);
                        String dateStr = tbName.split("_")[2];
                        Date date = DateUtil.parse(dateStr, "yyyyMM");
                        //当只有最小值时，最小值时间应小于逻辑表时间，当只有最大值时，逻辑表时间应小于最大值时间
                        if ((ObjectUtil.isNotEmpty(lower) ? lower.compareTo(date) : date.compareTo(upper)) <= 0) {
                            tableNameList.add(logicTableName + "_" + dateStr);
                        }
                    }
                }
                //当使用Between and 时
                else {
                    //便利目标中间所有时间段
                    for (; lower.compareTo(upper) <= 0; lower = DateUtils.addMonths(lower, 1)) {
                        String tableName = logicTableName + "_" + DateUtil.format(lower, "yyyyMM");
                        //判断是否存在定义的真实表
                        if (availableTargetNames.contains(tableName)) {
                            tableNameList.add(tableName);
                        }
                    }
                }
            }
        }
        return tableNameList;
    }
}

3、Inline表达式分片策略 InlineShardingStrategyConfiguration

只支持单分片键，对应InlineShardingStrategy，使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持。针于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。

a. yml

spring:
  shardingsphere:
    props:
      sql.show: true #是否输出sql
    datasource:
      names: ds0 #指定数据源 名称可以自定义，注意：名称要跟后面的配置一致
      ds0: #配置数据源的连接信息
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.52.10:3306/ball_dashboard?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: Yifan123.
    sharding:
      # 默认数据源
      default-data-source-name: ds0
      tables:
        #逻辑表名
        db_trading:
          key-generator:
            colun: id #主键
          #分表数据节点
          actual-data-nodes: ds0.db_trading_$->{2022..2023}$->{(1..12).collect{t -> t.toString().padLeft(2,'0')}}    #数据节点,均匀分布
          #分库策略
          #database-strategy:
          #  standard:
          #    algorithm-class-name: com.chain.utils.HintShardingKeyAlgorithm
          #分表策略
          table-strategy:
            #行表达式策略
            inline:
              #按照指定列进行分表---分表策略使用ID字段取模
              sharding-column: id
              #按模运算分配
              algorithm-expression: sys_role${id % 2}

4、Hint分片策略 HintShardingStrategyConfiguration

对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。

a. yml

spring:
  shardingsphere:
    props:
      sql.show: true #是否输出sql
    datasource:
      names: ds0 #指定数据源 名称可以自定义，注意：名称要跟后面的配置一致
      ds0: #配置数据源的连接信息
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.52.10:3306/ball?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: Yifan123.
    sharding:
      tables:
        sys_role: #逻辑表名
          key-generator-column-name: id #主键
          #分表数据节点
          actual-data-nodes: ds0.sys_role$->{0..1}    #数据节点,均匀分布
          database-strategy:
            hint.algorithm-class-name: com.chain.utils.HintShardingKeyAlgorithm
          #分表策略
          table-strategy:
            #hint分片策略
            hint.algorithm-class-name: com.chain.utils.HintShardingKeyAlgorithm

b. 分表策略 implements HintShardingAlgorithm （如果另外需要进行分库，代码一样继承类也一样，只需要在yaml上放开database-strategy即可）

public class HintShardingKeyAlgorithm implements HintShardingAlgorithm {
    @Override
    public Collection<String> doSharding(Collection collection, HintShardingValue hintShardingValue) {
        System.out.println("--------------------->1");
        System.out.println(hintShardingValue.toString());
        return Lists.newArrayList(hintShardingValue.getLogicTableName() + "1");
    }
}

c. java（使用HintManager）

HintManager instance = HintManager.getInstance();
//不管是分库还是分表第一个参数都要是表的名称
//instance.addDatabaseShardingValue("sys_role", 0);
instance.addTableShardingValue("sys_role", 0);

List<Long> list = new ArrayList<Long>();
list.add(1l);
list.add(2l);
list.add(1688865184984084481l);
List<Role> roleList = roleMapper.inList(list);

instance.close();

5、不分片的策略 NoneShardingStrategyConfiguration

对应NoneShardingStrategy。不分片的策略。 

四、sharding-jdbc有一些语法不支持

不支持distinct，单表可使用group by进行替代

不支持having，可使用嵌套子查询进行替代

不支持union（all），可拆分成多个查询，在程序拼接

严禁无切分键的深分页！因为会对SQL进行以下解释，然后在内存运行。

select * from a limit 10 offset 1000
Actual SQL:db0 ::: select * from a limit 1010 offset 0