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语句中的 =, >, <, >=, <=, IN和BETWEEN 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=