ShardingSphere(八) 分库分表的多种分片策略

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

标准分片策略 （PreciseShardingAlgorithm、RangeShardingAlgorithm）复合分片策略 （ComplexKeysShardingAlgorithm）Hint分片策略 （HintShardingAlgorithm）行表达式分片策略不分片策略

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

一、标准分片策略

标准分片策略用于处理单一建（分表字段）作为分表建的场景，包含两种分片算法：

精确分片算法，对应实现接口PreciseShardingAlgorithm。sql在分表键上执行= 与 IN时触发分表算逻辑，否则不走分表，全表执行。范围分片算法，对应实现接口RangeShardingAlgorithm。sql在分表键上执行BETWEEN AND、>、<、>=、<=时触发分表算逻辑，否则不走分表，全表执行。

spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cidspring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.xiaohui.strategy.TabStandardPreciseShardingAlgorithmspring.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=cidspring.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() {}@Overridepublic 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 mon.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() {}@Overridepublic 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");}}}

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

@Testpublic void standand(){Course course = new Course();course.setCname("精确分表测试");course.setUserId(100L);course.setCstatus("1");courseMapper.insert(course);}@Testpublic 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)-02-07 11:42:06.470 INFO 7092 --- [ main] ShardingSphere-SQL : Rule Type: sharding-02-07 11:42:06.473 INFO 7092 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO course ( cname,user_id,cstatus ) VALUES ( ?,?,? )-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])])-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])-02-07 11:36:09.503 INFO 10132 --- [ main] ShardingSphere-SQL : Rule Type: sharding-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 ?-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=[])-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]-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=56301401848944, 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=56418303524993, 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.plexKeysShardingAlgorithm;import org.apache.shardingsphere.plexKeysShardingValue;import java.util.Arrays;import java.util.Collection;import java.util.LinkedList;import java.util.Map;public class TabComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm {public TabComplexKeysShardingAlgorithm() {}@Overridepublic 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");//此处根据获取到的值进行业务逻辑编写，如下仅做举例,如果没有传参则获取到的为nullLong 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-plex.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() {}@Overridepublic 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 写法）：

@Testpublic 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])-02-07 14:47:54.867 INFO 8080 --- [ main] ShardingSphere-SQL : Rule Type: sharding-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 = ?-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=[])-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]-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=