一、分库分表
需求分析：
- 创建数据库 sharding_db1和sharding_db1
- 在数据库创建两张表 course_1 和 course_2
- 数据库规则：如果课程 id 是偶数，则添加 sharding_db1库，如果是奇数添加到 sharding_db2库。
- 数据表规则：如果课程 id 是偶数，则添加 course_1表，如果是奇数添加到 course_2表。
在项目 application.properties 配置文件中进行配置。
# 配置真实数据源
spring.shardingsphere.datasource.names=db1,db2
# 配置第1个数据源
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding_db1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=123456
# 配置第2个数据源
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding_db2?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=123456
# 指定库表的分布情况：分库，分表
spring.shardingsphere.sharding.tables.course.actual-data-nodes=db$->{1..2}.course_$->{1..2}
# 指定表的主键生成策略,雪花算法
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#指定分片策略
#1. inline分片策略
#1.1 库策略
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=db$->{id%2+1}
#1.2 表策略
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%2+1}
# 打开shardingsphere的sql日志输出。
spring.shardingsphere.props.sql.show=true
# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true
1）保存
@Test
public void testSave() throws InterruptedException {
for (int i = 0; i {id%2+1}
#1.2 表策略
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%2+1}
对应StandardShardingStrategy。提供对SQL语句中的=, >, =, {id%2+1} 分库策略 BigInteger shardingValueB = BigInteger.valueOf(shardingValue.getValue()); BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1")); String key = "db"+resB ; if(availableTargetNames.contains(key)){ return key; } throw new UnsupportedOperationException(" route "+key+" is not supported. please check your config"); } }
/**
* 自定义扩展的范围分片算法。
*/
public class MyRangeDSShardingAlgorithm implements RangeShardingAlgorithm {
@Override
public Collection doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) {
return availableTargetNames;
}
}
3）表策略算法
public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm {
@Override
public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
//实现按照 = 或 IN 进行精确分片。
//例如 select * from course where id = 1 or id in (1,3,5)
//实现course_$->{cid%2+1} 分表策略
BigInteger shardingValueB = BigInteger.valueOf(shardingValue.getValue());
BigInteger resB = shardingValueB.mod(new BigInteger("2"))
.add(new BigInteger("1"));
String key = shardingValue.getLogicTableName()+"_"+resB ;
if(availableTargetNames.contains(key)){
return key;
}
throw new UnsupportedOperationException(" route "+key+" is not supported. please check your config");
}
}
public class MyRangeTableShardingAlgorithm implements RangeShardingAlgorithm {
@Override
public Collection doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) {
//实现按照 Between 进行范围分片。
//例如 select * from course where id between 2000 and 3000;
Long lowerEndpoint = shardingValue.getValueRange().lowerEndpoint();//2000
Long upperEndpoint = shardingValue.getValueRange().upperEndpoint();//3000
//实现course_$->{(3000 -2000 )%2+1} 分片策略
// return Arrays.asList(shardingValue.getLogicTableName()+"_"+shardingValue.getLogicTableName() + ((upperEndpoint - lowerEndpoint) % 2 + 1));
//对于我们这个奇偶分离的场景，大部分范围查询都是要两张表都查。
return Arrays.asList(shardingValue.getLogicTableName()+"_1",shardingValue.getLogicTableName()+"_2");
}
}
4）测试查询 
对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, =, {cid%2+1} for(Long id : cidCol){ BigInteger idI = BigInteger.valueOf(id); BigInteger target = (idI.mod(BigInteger.valueOf(2L))).add(new BigInteger("1")); result.add("course_"+target); } return result; } } 4、Hint分片策略
对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。
注意使用它时有非常多的限制。
1）在 application.properties 配置文件中进行配置
#4. hint强制路由策略：与sql无关
##4.1 库策略
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.hint.MyHintTableShardingAlgorithm
##4.2 库策略
spring.shardingsphere.sharding.tables.course.database-strategy.hint.algorithm-class-name=com.charge.learn.shardingsphere.jdbc.study.sharding.strategy.hint.MyHintTableShardingAlgorithm
2）策略算法
public class MyHintDSShardingAlgorithm implements HintShardingAlgorithm {
@Override
public Collection doSharding(Collection availableTargetNames, HintShardingValue shardingValue) {
return Arrays.asList("db1","db2");
}
}
public class MyHintTableShardingAlgorithm implements HintShardingAlgorithm {
/**
*
* @param availableTargetNames 可选 数据源 和 表 的名称
* @param shardingValue
* @return
*/
@Override
public Collection doSharding(Collection availableTargetNames, HintShardingValue shardingValue) {
// 对SQL的零侵入分片方案。shardingValue是通过HintManager.
// 比如我们要实现将 select * from t_user where user_id in {1,2,3,4,5,.....}; 按照in的第一个值，全部路由到course_1表中。
String key = "course_"+shardingValue.getValues().toArray()[0];
if(availableTargetNames.contains(key)){
return Arrays.asList(key);
}
// return Arrays.asList("course_1");
throw new UnsupportedOperationException(" route "+key+" is not supported. please check your config");
}
}
分片策略，有时是针对sql定制的，所以在使用过程中有一定的限制。 参见官网文档： https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/
参考文章：
- ShardingSphere官方网址：https://shardingsphere.apache.org/document/current/cn/overview/
- ShardingSphere基本介绍及核心概念：https://blog.csdn.net/Kiven_ch/article/details/119087048
– 求知若饥，虚心若愚。
