08、Sharding-Sphere 实战:分库分表的多种分片策略

在之前文章《02、Sharding-Sphere 实战:水平分表,实现分表写入读取》中,我们介绍了数据库的水平分表配置,在文章中只介绍了最简单的行表达式分表配置方式,但往往在实际中我们的业务场景单一的行表达式不能满足。Sharding jdbc为我们实际提供了5种的分库分表策略实现方式。如下:

  • 标准分片策略 (PreciseShardingAlgorithm、RangeShardingAlgorithm)
  • 复合分片策略 (ComplexKeysShardingAlgorithm)
  • Hint分片策略 (HintShardingAlgorithm)
  • 行表达式分片策略
  • 不分片策略

数据库的分库与分表策略使用方式一致,其中doSharding 方法第一个参数表示可用的表或库,第二个参数为传入的字段参数信息对象,如下文章只对分表进行演示说明。

一、标准分片策略

标准分片策略用于处理单一建(分表字段)作为分表建的场景,包含两种分片算法:

1、 精确分片算法,对应实现接口PreciseShardingAlgorithmsql在分表键上执行**=与IN时触发分表算逻辑,否则不走分表,全表执行;
2、 范围分片算法,对应实现接口RangeShardingAlgorithmsql在分表键上执行
BETWEENAND、>、<、>=、<=**时触发分表算逻辑,否则不走分表,全表执行;

spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.xiaohui.strategy.TabStandardPreciseShardingAlgorithm
spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=com.xiaohui.strategy.TabStandardRangeShardingAlgorithm#指定course表里面主键cid的生成策略 SNOWFKAKE 雪花算法
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

精确分片算法示例(当cid字段生成的值大于564183025835835392L时操作course_2表,否则操作course_1表)精确算法只能操作一张表:

package com.xiaohui.strategy;

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

/**
 *  单分片键的标准分片场景
 */
public class TabStandardPreciseShardingAlgorithm implements PreciseShardingAlgorithm {

    public TabStandardPreciseShardingAlgorithm() {
    }

    @Override
    public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
        String resultTab = "";
        System.out.println("有效表信息:"+collection);
        System.out.println("分表键信息:"+preciseShardingValue.toString());
        Comparable value = preciseShardingValue.getValue();
        Long cid = (Long)value;
        if(cid > 564183025835835392L){
            resultTab =  "course_2";
        }else {
            resultTab =  "course_1";
        }
        if(collection.contains(resultTab)){
            return resultTab;
        }else {
            return "course_1";
        }
    }
}

范围分片算法示例(根据id范围操作对应的表,范围分片算法可以返回操作多张表):

package com.xiaohui.strategy;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.util.Arrays;
import java.util.Collection;

public class TabStandardRangeShardingAlgorithm implements RangeShardingAlgorithm {

    public TabStandardRangeShardingAlgorithm() {
    }

    @Override
    public Collection<String> doSharding(Collection collection, RangeShardingValue rangeShardingValue) {
        System.out.println("有效表信息:"+collection);
        System.out.println("分片参数信息:"+rangeShardingValue);
        Range valueRange = rangeShardingValue.getValueRange();
        Long smallVal = (Long)valueRange.lowerEndpoint();
        Long bigVal = (Long) valueRange.upperEndpoint();
        if( bigVal < 564183025835835392L){
            return Arrays.asList("course_1");
        }else if(smallVal > 564183025835835392L){
            return Arrays.asList("course_2");
        }else{
            return Arrays.asList("course_1","course_2");
        }
    }
}

对应的测试类以及打印日志:

    @Test
    public void standand(){
        Course course = new Course();
        course.setCname("精确分表测试");
        course.setUserId(100L);
        course.setCstatus("1");
        courseMapper.insert(course);
    }
    @Test
    public void testGetRageList(){
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("cstatus","1");
        wrapper.between("cid",0L,667588085982887936L);
        List<Course> courseList = courseMapper.selectList(wrapper);
        System.out.println(courseList.toString());
    }
有效表信息:[course_1, course_2]
分表键信息:PreciseShardingValue(logicTableName=course, columnName=cid, value=565139373587169281)
2023-02-07 11:42:06.470  INFO 7092 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2023-02-07 11:42:06.473  INFO 7092 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,user_id,cstatus )  VALUES  ( ?,?,? )
2023-02-07 11:42:06.473  INFO 7092 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=course, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=course, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=20)], parametersIndex=3, logicSQL=INSERT INTO course  ( cname,user_id,cstatus )  VALUES  ( ?,?,? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[cname, user_id, cstatus], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@4519f676, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@78ec89a6, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3596b249])])
2023-02-07 11:42:06.474  INFO 7092 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: INSERT INTO course_2   (cname, user_id, cstatus, cid) VALUES (?, ?, ?, ?) ::: [精确分表测试, 100, 1, 565139373587169281]
有效表信息:[course_1, course_2]
分片参数信息:RangeShardingValue(logicTableName=course, columnName=cid, valueRange=[0‥667588085982887936])
2023-02-07 11:36:09.503  INFO 10132 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2023-02-07 11:36:09.507  INFO 10132 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  cid,cname,user_id,cstatus  FROM course  WHERE  cstatus = ? AND cid BETWEEN ? AND ?
2023-02-07 11:36:09.507  INFO 10132 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=course, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=cid, tableName=course), operator=BETWEEN, compareOperator=null, positionValueMap={}, positionIndexMap={0=1, 1=2})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=course, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=3, logicSQL=SELECT  cid,cname,user_id,cstatus  FROM course  WHERE  cstatus = ? AND cid BETWEEN ? AND ?)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=32, groupByLastIndex=0, items=[CommonSelectItem(expression=cid, alias=Optional.absent()), CommonSelectItem(expression=cname, alias=Optional.absent()), CommonSelectItem(expression=user_id, alias=Optional.absent()), CommonSelectItem(expression=cstatus, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2023-02-07 11:36:09.508  INFO 10132 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  cid,cname,user_id,cstatus  FROM course_1  WHERE  cstatus = ? AND cid BETWEEN ? AND ? ::: [1, 0, 667588085982887936]
2023-02-07 11:36:09.508  INFO 10132 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  cid,cname,user_id,cstatus  FROM course_2  WHERE  cstatus = ? AND cid BETWEEN ? AND ? ::: [1, 0, 667588085982887936]
[Course(cid=101, cname=test_1, userId=1, cstatus=1), Course(cid=102, cname=mq_1, userId=101, cstatus=1), Course(cid=1001, cname=mq_1, userId=1002, cstatus=1), Course(cid=1002, cname=mq_2, userId=1002, cstatus=1), Course(cid=1003, cname=mq_3, userId=1002, cstatus=1), Course(cid=557588085567651840, cname=java_2, userId=100, cstatus=1), Course(cid=557588085768978432, cname=java_4, userId=100, cstatus=1), Course(cid=557588085982887936, cname=java_6, userId=100, cstatus=1), Course(cid=557588086192603136, cname=java_8, userId=100, cstatus=1), Course(cid=557588086402318336, cname=java_10, userId=100, cstatus=1), Course(cid=563014015635161088, cname=html_2, userId=100, cstatus=1), Course(cid=563014016281083904, cname=html_4, userId=100, cstatus=1), Course(cid=563014017644232704, cname=html_6, userId=100, cstatus=1), Course(cid=563014018420178944, cname=html_8, userId=100, cstatus=1), Course(cid=563014019351314432, cname=html_10, userId=100, cstatus=1), Course(cid=564115829558345729, cname=docker_1, userId=101, cstatus=1), Course(cid=564115830325903360, cname=docker_2, userId=102, cstatus=1), Course(cid=564118336892305409, cname=docker_1, userId=101, cstatus=1), Course(cid=564118338049933312, cname=docker_2, userId=102, cstatus=1), Course(cid=564118572457000961, cname=docker_1, userId=101, cstatus=1), Course(cid=564118637858783232, cname=docker_2, userId=102, cstatus=1), Course(cid=564121085180641281, cname=docker_1, userId=101, cstatus=1), Course(cid=564121086376017920, cname=docker_2, userId=102, cstatus=1), Course(cid=564121303334780929, cname=docker_1, userId=101, cstatus=1), Course(cid=564121305067028480, cname=docker_2, userId=102, cstatus=1), Course(cid=564124021579317248, cname=docker_2, userId=102, cstatus=1), Course(cid=564182395339669505, cname=html_1, userId=100, cstatus=1), Course(cid=564182395901706240, cname=html_2, userId=100, cstatus=1), Course(cid=564182396442771457, cname=html_3, userId=100, cstatus=1), Course(cid=564182397168386048, cname=html_4, userId=100, cstatus=1), Course(cid=564182397734617089, cname=html_5, userId=100, cstatus=1), Course(cid=564182398019829760, cname=html_6, userId=100, cstatus=1), Course(cid=564182398359568385, cname=html_7, userId=100, cstatus=1), Course(cid=564182398653169664, cname=html_8, userId=100, cstatus=1), Course(cid=564182398988713985, cname=html_9, userId=100, cstatus=1), Course(cid=564182399357812736, cname=html_10, userId=100, cstatus=1), Course(cid=564183020173524993, cname=java_1, userId=100, cstatus=1), Course(cid=564183020936888320, cname=java_2, userId=100, cstatus=1), Course(cid=564183021217906689, cname=java_3, userId=100, cstatus=1), Course(cid=564183021549256704, cname=java_4, userId=100, cstatus=1), Course(cid=564183022081933313, cname=java_5, userId=100, cstatus=1), Course(cid=564183022509752320, cname=java_6, userId=100, cstatus=1), Course(cid=564183022945959937, cname=java_7, userId=100, cstatus=1), Course(cid=564183023243755520, cname=java_8, userId=100, cstatus=1), Course(cid=564183025160552449, cname=java_9, userId=100, cstatus=1), Course(cid=564183025835835392, cname=java_10, userId=100, cstatus=1), Course(cid=1002, cname=mq_2, userId=1002, cstatus=1), Course(cid=557587021787299841, cname=java, userId=100, cstatus=1), Course(cid=557588084686848001, cname=java_1, userId=100, cstatus=1), Course(cid=557588085659926529, cname=java_3, userId=100, cstatus=1), Course(cid=557588085869641729, cname=java_5, userId=100, cstatus=1), Course(cid=557588086079356929, cname=java_7, userId=100, cstatus=1), Course(cid=557588086284877825, cname=java_9, userId=100, cstatus=1), Course(cid=563014014649499649, cname=html_1, userId=100, cstatus=1), Course(cid=563014015903596545, cname=html_3, userId=100, cstatus=1), Course(cid=563014016985726977, cname=html_5, userId=100, cstatus=1), Course(cid=563014018067857409, cname=html_7, userId=100, cstatus=1), Course(cid=563014018797666305, cname=html_9, userId=100, cstatus=1), Course(cid=564121085180641281, cname=docker_1, userId=101, cstatus=1), Course(cid=564121086376017920, cname=docker_2, userId=102, cstatus=1), Course(cid=564121303334780929, cname=docker_1, userId=101, cstatus=1), Course(cid=564121305067028480, cname=docker_2, userId=102, cstatus=1), Course(cid=564124006152667137, cname=docker_1, userId=101, cstatus=1), Course(cid=564183362516811777, cname=java_1, userId=100, cstatus=1), Course(cid=564183363330506752, cname=java_2, userId=100, cstatus=1), Course(cid=564183363636690945, cname=java_3, userId=100, cstatus=1), Course(cid=564183365440241664, cname=java_4, userId=100, cstatus=1), Course(cid=564183365733842945, cname=java_5, userId=100, cstatus=1), Course(cid=564183366048415744, cname=java_6, userId=100, cstatus=1), Course(cid=564183366367182849, cname=java_7, userId=100, cstatus=1), Course(cid=564183366690144256, cname=java_8, userId=100, cstatus=1), Course(cid=564183367096991745, cname=java_9, userId=100, cstatus=1), Course(cid=564183367512227840, cname=java_10, userId=100, cstatus=1), Course(cid=565135784911306753, cname=精确分表测试, userId=100, cstatus=1)]

二、复合分片策略

对应ComplexKeysShardingAlgorithm,用于处理使用多键(多字段)作为分片键进行分片的场景,包含多个分片键的逻辑较复杂,需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。复合分片策略提供对SQL语句中的 =, >, <, >=, <=, INBETWEEN AND 的分片操作支持。该策略实现了对多字段逻辑处理,以及返回多表的支持。

自定义类实现ComplexKeysShardingAlgorithm接口,示例:

package com.xiaohui.strategy;

import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedList;
import java.util.Map;

public class TabComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm {

    public TabComplexKeysShardingAlgorithm() {
    }

    @Override
    public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
        System.out.println("当前存在的表有:"+collection);
        Map columnNameAndShardingValuesMap = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
        System.out.println("传入的参数列表:"+columnNameAndShardingValuesMap);
        //业务逻辑
        LinkedList cidList = (LinkedList)columnNameAndShardingValuesMap.get("cid");
        LinkedList userIdList = (LinkedList)columnNameAndShardingValuesMap.get("user_id");
        //此处根据获取到的值进行业务逻辑编写,如下仅做举例,如果没有传参则获取到的为null
        Long cid = (Long) cidList.get(0);
        Long userId = (Long)  userIdList.get(0);
        if(cid>userId){
            return Arrays.asList("course_1");
        }else if(cid <userId) {
            return Arrays.asList("course_1");
        }else {
            return Arrays.asList("course_1","course_2");
        }
    }
}

#用于多分片键的复合分片场景 可实现操作多表
spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns=user_id,cid

三、Hint分片策略

hint分片策略与其他分片策略不同,其他策略都是根据配置的分片键,以及配置的分片策略来实现表路由。当 hint用来实现比较复杂的sql或sql条件字段中没有分片字段时的一种强制路由策略。该策略需要在业务代码中使用HintManager 对象设置线程绑定参数,用于在该分片实现类中获取。使用完成之后不用再路由时,需要将该线程参数清除。

#Hint分片算法类名称。该类需实现HintShardingAlgorithm接口并提供无参数的构造器
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.xiaohui.strategy.TabHintShardingAlgorithm

分片接口实现示例如下:

package com.xiaohui.strategy;

import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
import java.util.*;

public class TabHintShardingAlgorithm  implements HintShardingAlgorithm {

    public TabHintShardingAlgorithm() {
    }

    @Override
    public Collection<String> doSharding(Collection collection, HintShardingValue hintShardingValue) {
        System.out.println("当前存在的表有:"+collection);
        String s = hintShardingValue.toString();
        System.out.println("传入的参数列表:"+s);
        //业务逻辑计算表
        Collection values = hintShardingValue.getValues();
        List<String> list = new ArrayList<>();
        for (Object value : values) {
            list.add("course_"+value);
        }
        return list;
    }
}

测试代码(使用了 try-with-resource 写法):

   @Test
    public void testHintGet(){
        try(HintManager hintManager = HintManager.getInstance()){
            hintManager.addTableShardingValue("course", 1);
            hintManager.addTableShardingValue("course", 2);

            QueryWrapper<Course> wrapper = new QueryWrapper<>();
            wrapper.eq("cid",563102168291213313L);
            wrapper.eq("user_id",101L);
            Course course = courseMapper.selectOne(wrapper);
            System.out.println(course);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

打印日志:

当前存在的表有:[course_1, course_2]
传入的参数列表:HintShardingValue(logicTableName=course, columnName=, values=[1, 2])
2023-02-07 14:47:54.867  INFO 8080 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2023-02-07 14:47:54.870  INFO 8080 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  cid,cname,user_id,cstatus  FROM course  WHERE  cid = ? AND user_id = ?
2023-02-07 14:47:54.870  INFO 8080 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=course, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=course, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=SELECT  cid,cname,user_id,cstatus  FROM course  WHERE  cid = ? AND user_id = ?)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=32, groupByLastIndex=0, items=[CommonSelectItem(expression=cid, alias=Optional.absent()), CommonSelectItem(expression=cname, alias=Optional.absent()), CommonSelectItem(expression=user_id, alias=Optional.absent()), CommonSelectItem(expression=cstatus, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2023-02-07 14:47:54.871  INFO 8080 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  cid,cname,user_id,cstatus  FROM course_1  WHERE  cid = ? AND user_id = ? ::: [563102168291213313, 101]
2023-02-07 14:47:54.871  INFO 8080 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  cid,cname,user_id,cstatus  FROM course_2  WHERE  cid = ? AND user_id = ? ::: [563102168291213313, 101]
null

四、行表达式分片策略

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

五、不分片策略

对应NoneShardingStrategy,将会对所有表进行操作(新增更新查询等)。配置方式如下:

#不分片策略 none后面.任意字符
spring.shardingsphere.sharding.tables.course.table-strategy.none.anystr=