【分库分表】sharding-jdbc—分片策略

2022-01-01 20:11:10

一、分片策略

Sharding-JDBC认为对于分片策略存有两种维度：

数据源分片策略（DatabaseShardingStrategy）：数据被分配的目标数据源
表分片策略（TableShardingStrategy）：数据被分配的目标表

两种分片策略API完全相同，但是表分片策略是依赖于数据源分片策略的（即：先分库然后才有分表）

二、分片算法

Sharding分片策略继承自ShardingStrategy，提供了5种分片策略：

由于分片算法和业务实现紧密相关，因此Sharding-JDBC并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。

StandardShardingStrategy

标准分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。

StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。

PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。
RangeShardingAlgorithm是可选的，用于处理BETWEEN AND分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。

ComplexShardingStrategy

复合分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。

ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此Sharding-JDBC并未做过多的封装，而是直接将分片键值组合以及分片操作符交于算法接口，完全由应用开发者实现，提供最大的灵活度。

InlineShardingStrategy

Inline表达式分片策略。使用Groovy的Inline表达式，提供对SQL语句中的=和IN的分片操作支持。

InlineShardingStrategy只支持单分片键，对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: tuser${user_id % 8} 表示t_user表按照user_id按8取模分成8个表，表名称为t_user_0到t_user_7。

HintShardingStrategy

通过Hint而非SQL解析的方式分片的策略。

NoneShardingStrategy

不分片的策略。

三、自定义分片算法

Sharding提供了以下4种算法接口：

PreciseShardingAlgorithm
RangeShardingAlgorithm
HintShardingAlgorithm
ComplexKeysShardingAlgorithm

可以自己实现自定义的分片算法，下面以t_order_items表为例自己实现分片算法：

标准分片策略（StandardShardingStrategy）

a、PreciseShardingAlgorithm实现：（Precise处理 = 和 in 的路由）

        // 配置order_item表规则...

        TableRuleConfiguration orderItemTableRuleConfig = new TableRuleConfiguration();

        orderItemTableRuleConfig.setLogicTable("t_order_items");

        orderItemTableRuleConfig.setActualDataNodes("db${0..2}.t_order_items_${0..1}");

        // 自定义的分片算法实现

        StandardShardingStrategyConfiguration standardStrategy = new StandardShardingStrategyConfiguration("order_id",MyPreciseShardingAlgorithm.class.getName());

        // 配置分库策略

        orderItemTableRuleConfig.setDatabaseShardingStrategyConfig(standardStrategy);

        // 配置分表策略

        orderItemTableRuleConfig.setTableShardingStrategyConfig(standardStrategy);

        shardingRuleConfig.getTableRuleConfigs().add(orderItemTableRuleConfig);

        // 获取数据源对象

        DataSource dataSource = null;

        try {

            dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return dataSource;

自定义的分片算法，先继承接口，打印参数：

@Slf4j

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override

    public String doSharding(Collection collection, PreciseShardingValue<Long> preciseShardingValue) {

        log.info("collection:" + JSON.toJSONString(collection) + ",preciseShardingValue:" + JSON.toJSONString(preciseShardingValue));

        return null;

    }

}

输出如下日志：（第一行路由是db，下一行是table）

2018-01-19 20:13:36,790 -2 collection:["db0","db1","db2"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":100}

……

2018-01-21 16:33:22,269 -2 collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":100}

于是可以简单实现一个类似Inline配置的规则：

@Slf4j

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override

    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {

        log.info("collection:" + JSON.toJSONString(collection) + ",preciseShardingValue:" + JSON.toJSONString(preciseShardingValue));

        for (String name : collection) {

            if (name.endsWith(preciseShardingValue.getValue() % collection.size() + "")) {

                log.info("return name:"+name);

                return name;

            }

        }

        return null;

    }

}

IN 条件的处理示例：

==> Preparing: select id,order_id,unique_no,quantity,is_active,inserttime,updatetime from t_order_items where is_active = 1 AND order_id in ( ? , ? , ? )

==> Parameters: 100(Long), 101(Long), 102(Long)

//第一轮route筛选数据库（分片键路由规则）：

09:55:09.634 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["db0","db1","db2"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":100}

09:55:13.758 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:db1

09:55:17.767 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["db0","db1","db2"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":101}

09:55:21.361 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:db2

09:55:23.127 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["db0","db1","db2"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":102}

09:55:24.190 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:db0

//第二轮route按第一轮筛选到的db，逐个进行table的计算：

09:58:45.086 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":100}

09:58:46.725 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_0

09:58:58.647 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":101}

09:59:02.197 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_1

09:59:11.710 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":102}

09:59:12.604 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_0

10:00:01.538 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":100}

10:00:01.538 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_0

10:00:02.042 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":101}

10:00:02.042 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_1

10:00:02.442 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":102}

10:00:02.442 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_0

10:00:03.581 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":100}

10:00:03.581 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_0

10:00:03.946 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":101}

10:00:03.946 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_1

10:00:04.578 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","value":102}

10:00:04.578 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:t_order_items_0

b、PreciseShardingAlgorithm + RangeShardingAlgorithm

@Slf4j

public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {

    @Override

    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {

        log.info("Range collection:" + JSON.toJSONString(collection) + ",rangeShardingValue:" + JSON.toJSONString(rangeShardingValue));

        Collection<String> collect = new ArrayList<>();

        Range<Long> valueRange = rangeShardingValue.getValueRange();

        for (Long i = valueRange.lowerEndpoint(); i <= valueRange.upperEndpoint(); i++) {

            for (String each : collection) {

                if (each.endsWith(i % collection.size() + "")) {

                    collect.add(each);

                }

            }

        }

        return collect;

    }

}

22:17:35.318 logback-demo [http-nio-8082-exec-6] INFO s.j.demo.controller.OrderController - selectByOrderIds ,startNo:100,endNo:101

路由输出log：

-- 第一轮计算db
22:16:51.732 logback-demo [http-nio-8082-exec-6] INFO s.j.d.d.MyRangeShardingAlgorithm - Range collection:["db0","db1","db2"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","valueRange":{"empty":false}}

-- 第二轮计算table

22:17:16.325 logback-demo [http-nio-8082-exec-6] INFO s.j.d.d.MyRangeShardingAlgorithm - Range collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","valueRange":{"empty":false}}
22:17:32.771 logback-demo [http-nio-8082-exec-6] INFO s.j.d.d.MyRangeShardingAlgorithm - Range collection:["t_order_items_0","t_order_items_1"],preciseShardingValue:{"columnName":"order_id","logicTableName":"t_order_items","valueRange":{"empty":false}}

路由到[db0,db1]X[t_order_items_0,t_order_items_1]表。

ComplexShardingStrategy

分库分表配置：user_id单键分库 + 【user_id+order_id】组合键分表

    @Bean(name = "shardingComplexDataSource", destroyMethod = "close")

    @Qualifier("shardingComplexDataSource")

    public DataSource getComlpexShardingDataSource() {

        // 配置真实数据源

        Map<String, DataSource> dataSourceMap = new HashMap<>(3);

        List<String> dbNames = new ArrayList<>();

        dbNames.add("db0");

        dbNames.add("db1");

        dbNames.add("db2");

        for (String dbName : dbNames) {

            DruidDataSource dataSource = createDefaultDruidDataSource();

            dataSource.setDriverClassName("com.mysql.jdbc.Driver");

            dataSource.setUrl("jdbc:mysql://localhost:3306/" + dbName);

            dataSource.setUsername("root");

            dataSource.setPassword("root");

            dataSourceMap.put(dbName, dataSource);

        }

        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();

        orderTableRuleConfig.setLogicTable("t_order");

        orderTableRuleConfig.setActualDataNodes("db${0..2}."+"t_order_${0..1}_${0..1}");

        /**分库采用单片键 user_id*/

        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", MyPreciseShardingAlgorithm.class.getName()));

        /**分表采用双片键 user_id*/

        orderTableRuleConfig.setTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration("user_id,order_id", MyComplexShardingAlgorithm.class.getName()));

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        // 获取数据源对象

        DataSource dataSource = null;

        try {

            dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return dataSource;

    }

实现ComplexKeysShardingAlgorithm算法：

@Slf4j

public class MyComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {

    @Override

    public Collection<String> doSharding(Collection<String> collection, Collection<ShardingValue> shardingValues) {

        log.info("collection:" + JSON.toJSONString(collection) + ",shardingValues:" + JSON.toJSONString(shardingValues));

        Collection<Long> orderIdValues = getShardingValue(shardingValues, "order_id");

        Collection<Long> userIdValues = getShardingValue(shardingValues, "user_id");

        List<String> shardingSuffix = new ArrayList<>();

        /**例如：根据user_id + order_id 双分片键来进行分表*/

        //Set<List<Integer>> valueResult = Sets.cartesianProduct(userIdValues, orderIdValues);

        for (Long userIdVal : userIdValues) {

            for (Long orderIdVal : orderIdValues) {

                String suffix = userIdVal % 2 + "_" + orderIdVal % 2;

                collection.forEach(x -> {

                    if (x.endsWith(suffix)) {

                        shardingSuffix.add(x);

                    }

                });

            }

        }

        return shardingSuffix;

    }

    private Collection<Long> getShardingValue(Collection<ShardingValue> shardingValues, final String key) {

        Collection<Long> valueSet = new ArrayList<>();

        Iterator<ShardingValue> iterator = shardingValues.iterator();

        while (iterator.hasNext()) {

            ShardingValue next = iterator.next();

            if (next instanceof ListShardingValue) {

                ListShardingValue value = (ListShardingValue) next;

                /**例如：根据user_id + order_id 双分片键来进行分表*/

                if (value.getColumnName().equals(key)) {

                    return value.getValues();

                }

            }

        }

        return valueSet;

    }

}

运行示例：

16:53:16.267 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - collection:["db0","db1","db2"],preciseShardingValue:{"columnName":"user_id","logicTableName":"t_order","value":123}
16:53:16.267 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyPreciseShardingAlgorithm - return name:db0

16:53:16.740 logback-demo [http-nio-8082-exec-1] INFO s.j.d.d.MyComplexShardingAlgorithm - collection:["t_order_0_0","t_order_0_1","t_order_1_0","t_order_1_1"],shardingValues:[{"columnName":"order_id","logicTableName":"t_order","values":[321]},{"columnName":"user_id","logicTableName":"t_order","values":[123]}]

四、级联绑定表

级联绑定表代表一组表，这组表的逻辑表与实际表之间的映射关系是相同的。比如t_order与t_order_item就是这样一组绑定表关系，它们的分库与分表策略是完全相同的,那么可以使用它们的表规则将它们配置成级联绑定表。

 ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

 shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());

 shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());

 shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");

那么在进行SQL路由时，如果SQL为：

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?

其中t_order在FROM的最左侧，Sharding-JDBC将会以它作为整个绑定表的主表。所有路由计算将会只使用主表的策略，那么t_order_item表的分片计算将会使用t_order的条件。故绑定表之间的分区键要完全相同。

码农公寓

一、分片策略

二、分片算法

StandardShardingStrategy

ComplexShardingStrategy

InlineShardingStrategy

HintShardingStrategy

NoneShardingStrategy

三、自定义分片算法

标准分片策略（StandardShardingStrategy）

a、PreciseShardingAlgorithm实现：（Precise处理 = 和 in 的路由）

b、PreciseShardingAlgorithm + RangeShardingAlgorithm

ComplexShardingStrategy

四、级联绑定表

相关文章