ShardingJDBC分库分表配置⽂件详解基于 shardingshpere 4.0
POM依赖（Springboot）
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
版本对应上不需要在启动类进⾏excloud操作
总体配置
favoritesrver:
port:9090
mybatis:
mapper-locations: classpath:mappers/*l
##分库配置
spring:
shardingsphere:
sharding:
master-slave-rules:
m0:
master-data-source-name: ds0
slave-data-source-names: ds0s0
load-balance-algorithm-type: round_robin
m1:
master-data-source-name: ds1
slave-data-source-names: ds1s0
load-balance-algorithm-type: round_robin
default-databa-strategy:
inline:
sharding-column: id
algorithm-expression: m$->{id % 3}
tables:
ur:
actual-data-nodes: m$->{0..1}.ur$->{0..1}
databa-strategy:#策略只能4选1
# standard: #标准分⽚策略
# sharding-column: id
# preci-algorithm-class-name: demo.sharding.db.DBShardingAlgorithm
# range-algorithm-class-name: demo.sharding.db.DBShardingAlorithmRange
complex:#复合分⽚策略
sharding-columns: id,ur_age
algorithm-class-name: demo.sharding.db.MyComplex
# inline: #⾏内
# sharding-column: id
# algorithm-expression: ds$->{id % 3}
# hint: #⾃定义
# algorithm-class-name: demo.sharding.db.MyHint
# table-strategy: #所有表策略和库的策略同理sasha blonde
# hint:
# algorithm-class-name: demo.sharding.table.TableHint
props:
sql.show:true
sql.show:true
datasource:
names: ds0,ds1,ds0s0,ds1s0
ds0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/shardingdb1?uUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&uSSL=fal&rverTimezone= GMT
driver-class-name: sql.cj.jdbc.Driver
password: root
urname: root
ds1:
tinman
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/shardingdb2?uUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&uSSL=fal&rverTimezone= GMT
driver-class-name: sql.cj.jdbc.Driver
password: root
urname: rootpostpone
ds0s0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/shardingdb3?uUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&uSSL=fal&rverTimezone= GMT
driver-class-name: sql.cj.jdbc.Driver
password: root
hmp
urname: root
ds1s0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/shardingdb4?uUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&uSSL=fal&rverTimezone= GMT
driver-class-name: sql.cj.jdbc.Driver
password: root
urname: root
读写分离
spring:
shardingsphere:
sharding:
master-slave-rules:
m0:
master-data-source-name: ds0
slave-data-source-names: ds0s0
load-balance-algorithm-type: round_robin
m1:
master-data-source-name: ds1hex
slave-data-source-names: ds1s0
load-balance-algorithm-type: round_robin
以后在填写actual-data-nodes参数的时候使⽤m0,m1来代理。
分库或分表策略
宏观来说以下策略分库和分表都适⽤。
HintShardingStrategy：
配置⽂件：
##分库配置
spring:
shardingsphere:
sharding:
master-slave-rules:
m0:
master-data-source-name: ds0
slave-data-source-names: ds0s0
load-balance-algorithm-type: round_robin
m1:
master-data-source-name: ds1
slave-data-source-names: ds1s0
load-balance-algorithm-type: round_robin
default-databa-strategy:
inline:
sharding-column: id
algorithm-expression: m$->{id % 3}
dota是什么意思tables:
ur:
actual-data-nodes: m$->{0..1}.ur$->{0..1}
databa-strategy:
谢谢英文怎么写hint:
algorithm-class-name: demo.sharding.db.MyHint
table-strategy:
hint:
algorithm-class-name: demo.sharding.table.TableHint
⾃定义业务
public class MyHint implements HintShardingAlgorithm<String>{
//hintShardingValue就是咱们在HintManager配置的
@Override
public Collection<String>doSharding(Collection<String> collection, HintShardingValue<String> hintShardingValue){ HashSet<String> dbName =new HashSet<>();
rvice chargeif("ur".LogicTableName())){//针对ur表
Collection<String> values = Values();
String next = values.iterator().next();
if(next.equals("d0")){//⾃定义业务逻辑
dbName.add("m0");
}el{
dbName.add("m1");
}
return dbName;
}
return null;
}
}
测试⽤例，这⾥经过测试，对于库⼊参d0会⾛m0，d1⾛m1，表同理。
public void testHint(){
//清空ThreadLocal
HintManager.clear();
HintManager hintManager = Instance();
//因为会配置多个表规则，所以这⾥使⽤表名作为key，value未⾃定义标识，⽅便后续分库分表//分库回调⼊参
hintManager.addDatabaShardingValue("ur","d1");
//分表回调⼊参
hintManager.addTableShardingValue("ur","t0");
//强⾛主库
hintManager.tMasterRouteOnly();
System.out.println(urService.lectHint());
}
标准策略：
PreciShardingAlgorithm
针对= , in 触发
RangeShardingAlgorithm
针对⽐较运算符以及between and 触发
spring:
shardingsphere:
sharding:
master-slave-rules:
m0:
master-data-source-name: ds0
陶喆i love you
slave-data-source-names: ds0s0
load-balance-algorithm-type: round_robin
m1:
master-data-source-name: ds1
slave-data-source-names: ds1s0
load-balance-algorithm-type: round_robin
default-databa-strategy:
inline:
sharding-column: id
algorithm-expression: m$->{id % 3}
tables:
ur:
actual-data-nodes: m$->{0..1}.ur$->{0..1}
databa-strategy:#策略只能4选1
standard:#标准分⽚策略
sharding-column: id
preci-algorithm-class-name: demo.sharding.db.DBShardingAlgorithm
range-algorithm-class-name: demo.sharding.db.DBShardingAlorithmRange
在代码⾥分别实现，其提供的接⼝和HintShardingStrategy⽅式⼀样。ComplexKeysShardingAlgorithm
针对多个分⽚键来触发。
shardingsphere:
sharding:
master-slave-rules:
m0:
master-data-source-name: ds0
slave-data-source-names: ds0s0
load-balance-algorithm-type: round_robin
m1:
master-data-source-name: ds1
slave-data-source-names: ds1s0
load-balance-algorithm-type: round_robin
default-databa-strategy:
inline:
sharding-column: id
algorithm-expression: m$->{id % 3}
tables:
ur:
actual-data-nodes: m$->{0..1}.ur$->{0..1}
databa-strategy:#策略只能4选1
# standard: #标准分⽚策略
# sharding-column: id
# preci-algorithm-class-name: demo.sharding.db.DBShardingAlgorithm
# range-algorithm-class-name: demo.sharding.db.DBShardingAlorithmRange complex:#复合分⽚策略
sharding-columns: id,ur_age
algorithm-class-name: demo.sharding.db.MyComplex
