分片策略
-
数据源配置
# 所有配置的数据源都必须配置在对应数据节点下 # 支持配置多数据节点 spring.shardingsphere.datasource.names=m1,m2 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/sharding_sphere_1?serverTimezone=UTC spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=root
-
使用分表(分库)后,数据库自增主键不能再继续使用,需要重新配置
# 真实表配置(Groovy表达式) spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2} # 指定主键 spring.shardingsphere.sharding.tables.course.key-generator.column=cid # 使用雪花算法生成主键 spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE # 雪花算法的自定义属性 spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
- 以下所有配置的
course
、t_dict
等为测试使用数据表database-strategy
为对应分库策略table-strategy
为对应分表策略- 以下只是练习时的测试DEMO,更多配置需要查看文档
- 分片算法接口包括:
RangeShardingAlgorithm
、ComplexKeysShardingAlgorithm
、PreciseShardingAlgorithm
、HintShardingAlgorithm
,所有算法都需要自己实现- 不同的算法对应不同的情况,例如分别处理
=
、>=
等
inline
# (库)分片键
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
# (库)分片算法(Groovy表达式)
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}
# (表)分片键
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
# (表)分片策略(Groovy表达式)
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
standard
# (库)分片键
spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
# (库)范围分片算法
spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=org.example.ss.algorithm.MyDSRangeShardingAlgorithm
# (库)精确分片算法
spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=org.example.ss.algorithm.MyDSPreciseShardingAlgorithm
# (表)分片键
spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
# (表)范围分片算法
spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=org.example.ss.algorithm.MyTableRangeShardingAlgorithm
# (表)精确分片算法
spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=org.example.ss.algorithm.MyTablePreciseShardingAlgorithm
complex
# (表)分片键
spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns=cid,user_id
# (表)分片算法
spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=org.example.ss.algorithm.MyComplexTableShardingAlgorithm
public class MyComplexTableShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
// 示例SQL: select * from course where cid between xxx and xxx and user_id = xxx
// TODO 这里的泛型Long标识为参数类型,所以(所有的)参数类型必需都为同一类型
Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");
String logicTableName = shardingValue.getLogicTableName();
Long cidUpper = cidRange.upperEndpoint();
Long cidLower = cidRange.lowerEndpoint();
Set<String> ret = new HashSet<>();
for (Long userId : userIdCol) {
BigInteger userIdB = BigInteger.valueOf(userId);
BigInteger target = userIdB.mod(new BigInteger("2")).add(new BigInteger("1"));
ret.add(logicTableName + "_" + target.intValue());
}
return ret;
}
}
hint
# 分片键不再与SQL相关,由程序指定
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=org.example.ss.algorithm.MyHintTableShardingAlgorithm
/**
* Hint 自定义分片键
*/
@Test
public void queryCourseHint() {
// 与线程绑定 todo 线程安全
HintManager hintManager = HintManager.getInstance();
// 逻辑表名:真实表对应编码
// todo 提前给定查询表范围
// hintManager.addTableShardingValue("course", 1);
hintManager.addTableShardingValue("course", 2);
// hintManager.addTableShardingValue("course", 3);
// select * from course
List<Course> courses = courseMapper.selectList(null);
courses.forEach(System.out::println);
// 使用完后关闭
hintManager.close();
}
绑定表
# t_dict 的分片策略
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=m1.t_dict_$->{1..2}
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict_$->{ustatus.toInteger()%2+1}
# t_user 的分片策略
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m1.t_user_$->{1..2}
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{ustatus.toInteger()%2+1}
# 绑定表:t_user、t_dict使用相同的分片策略(不配置的话会进行全路由查询,且结果为笛卡尔集)
spring.shardingsphere.sharding.binding-tables[0]=t_user,t_dict
广播表
# 广播表:所有数据源中都存在的表
# t_dict为测试使用数据表
spring.shardingsphere.sharding.broadcast-tables=t_dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
读写分离
# 读写分离依赖于数据库的主从配置
# 主从库逻辑定义(这个名称暂时还不知道含义)
spring.shardingsphere.masterslave.name=ms
# 主库只负责写(数据节点名称)
spring.shardingsphere.masterslave.master-data-source-name=m1
# 从库只负责读(数据节点名称)
spring.shardingsphere.masterslave.slave-data-source-names=m2
以上只是ShardingJDBC
的几种典型使用配置方式,更详细的配置方式可参考官方文档