SpringBoot整合sharding-jdbc实现⾃定义分库分表的实践
springboot推荐算法⽬录
⼀、前⾔
⼆、简介
1、分⽚键
2、分⽚算法
三、程序实现
⼀、前⾔
本⽂将通过⾃定义算法来实现定制化的分库分表来扩展相应业务
⼆、简介
1、分⽚键
⽤于数据库/表拆分的关键字段
ex: ⽤户表根据user_id取模拆分到不同的数据库中
2、分⽚算法
精确分⽚算法
范围分⽚算法
复合分⽚算法
Hint分⽚算法
3、分⽚策略（分⽚键+分⽚算法）
⾏表达式分⽚策略
标准分⽚策略
复合分⽚策略
Hint分⽚策略
不分⽚策略
可查看源码org.fig.sharding.YamlShardingStrategyConfiguration
三、程序实现
温馨⼩提⽰：详情可查看案例demo源码
这⾥先贴出完整的l配置，后⾯实现每⼀种分⽚策略时，放开其相应配置即可~
# sharding-jdbc配置
spring:
shardingsphere:
# 是否开启SQL显⽰
props:
sql:
show: true
# ====================== ↓↓↓↓↓↓数据源配置↓↓↓↓↓↓ ======================
datasource:
names: ds-master-0,ds-slave-0-1,ds-slave-0-2,ds-master-1,ds-slave-1-1,ds-slave-1-2
# ====================== ↓↓↓↓↓↓配置第1个主从库↓↓↓↓↓↓ ======================
# 主库1
ds-master-0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: sql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL在⾼版本需要指明是否进⾏SSL连接解决则加上 &useSSL=false username: root
password: root
# 主库1-从库1
ds-slave-0-1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: sql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncod
ing=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL在⾼版本需要指明是否进⾏SSL连接解决则加上 &useSSL=false
username: root
password: root
# 主库1-从库2
ds-slave-0-2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: sql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL在⾼版本需要指明是否进⾏SSL连接解决则加上 &useSSL=false username: root
password: root
# ====================== ↓↓↓↓↓↓配置第2个主从库↓↓↓↓↓↓ ======================
# 主库2
ds-master-1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: sql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL在⾼版本需要指明是否进⾏SSL连接解决则加上 &useSSL=false username: root
password: root
# 主库2-从库1
ds-slave-1-1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: sql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL在⾼版本需要指明是否进⾏SSL连接解决则加上 &useSSL=false username: root
password: root
# 主库2-从库2
ds-slave-1-2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: sql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3307/ds1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false # MySQL在⾼版本需要指明是否进⾏SSL连接解决则加上 &useSSL=false username: root
password: root
sharding:
# ====================== ↓↓↓↓↓↓读写分离配置↓↓↓↓↓↓ ======================
master-slave-rules:
ds-master-0:
# 主库
masterDataSourceName: ds-master-0
# 从库
slaveDataSourceNames:
- ds-slave-0-1
- ds-slave-0-2
# 从库查询数据的负载均衡算法⽬前有2种算法 round_robin（轮询）和 random（随机）
# 算法接⼝ org.apache.shardingsphere.spi.masterslave.MasterSlaveLoadBalanceAlgorithm
# 实现类 RandomMasterSlaveLoadBalanceAlgorithm 和 RoundRobinMasterSlaveLoadBalanceAlgorithm
loadBalanceAlgorithmType: ROUND_ROBIN
ds-master-1:
masterDataSourceName: ds-master-1
slaveDataSourceNames:
- ds-slave-1-1
- ds-slave-1-2
loadBalanceAlgorithmType: ROUND_ROBIN
# ====================== ↓↓↓↓↓↓分库分表配置↓↓↓↓↓↓ ======================
tables:
t_user:
actual-data-nodes: ds-master-$->{0..1}.t_user$->{0..1}
# 配置属性可参考 org.fig.sharding.YamlShardingStrategyConfiguration
# =========== ↓↓↓↓↓↓⾏表达式分⽚策略↓↓↓↓↓↓ ===========
# 在配置中使⽤ Groovy 表达式，提供对 SQL语句中的 = 和 IN 的分⽚操作⽀持，只⽀持单分⽚健。
# # ====== ↓↓↓↓↓↓分库↓↓↓↓↓↓ ======
# database-strategy:
# inline:
# sharding-column: user_id # 添加数据分库字段(根据字段插⼊数据到哪个库 ex：user_id)
# algorithm-expression: ds-master-$->{user_id % 2} # 根据user_id取模拆分到不同的库中
# # ====== ↓↓↓↓↓↓分表↓↓↓↓↓↓ ======
# table-strategy:
# inline:
# sharding-column: sex # 添加数据分表字段(根据字段插⼊数据到哪个表 ex：sex)
# algorithm-expression: t_user$->{sex % 2} # 分⽚算法表达式 => 根据⽤户性别取模拆分到不同的表中
# =========== ↓↓↓↓↓↓标准分⽚策略↓↓↓↓↓↓ ===========
# 精确分⽚算法 => sql在分库/分表键上执⾏ = 与 IN 时触发计算逻辑，否则不⾛分库/分表，全库/全表执⾏。
# database-strategy:
# standard:
# sharding-column: user_id # 分库⽤到的键
# precise-algorithm-class-name: com.fig.sharding.precise.MyDbPrecise
ShardingAlgorithm # ⾃定义分库算法实现类
# table-strategy:
# standard:
# sharding-column: sex # 添加数据分表字段(根据字段插⼊数据到那个表 ex：sex)
# precise-algorithm-class-name: com.fig.sharding.precise.MyTablePreciseShardingAlgorithm # ⾃定义分表算法实现类
# 范围分⽚算法 => sql在分库/分表键上执⾏ BETWEEN AND、>、<、>=、<= 时触发计算逻辑，否则不⾛分库/分表，全库/全表执⾏。
# database-strategy:
# standard:
# sharding-column: user_id
# precise-algorithm-class-name: com.fig.sharding.range.MyDbPreciseShardingAlgorithm
# range-algorithm-class-name: com.fig.sharding.range.MyDbRangeShardingAlgorithm
# table-strategy:
# standard:
# sharding-column: sex
# precise-algorithm-class-name: com.fig.sharding.range.MyTablePreciseShardingAlgorithm
# range-algorithm-class-name: com.fig.sharding.range.MyTableRangeShardingAlgorithm
# =========== ↓↓↓↓↓↓复合分⽚策略↓↓↓↓↓↓ ===========
# SQL 语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 等操作符，不同的是复合分⽚策略⽀持对多个分⽚健操作。
# database-strategy:
# complex:
# sharding-columns: user_id,sex
# algorithm-class-name: com.fig.shardingplex.MyDbComplexKeysShardingAlgorithm
# table-strategy:
# complex:
# sharding-columns: user_id,sex
# algorithm-class-name: com.fig.shardingplex.MyTableComplexKeysShardingAlgorithm
# =========== ↓↓↓↓↓↓ hint分⽚策略↓↓↓↓↓↓ ===========
# 通过 Hint API实现个性化配置 => 可查看 com.zhengqing.demo.service.impl.UserServiceImpl.listPageForHint
database-strategy:
hint:
algorithm-class-name: com.fig.sharding.hint.MyDbHintShardingAlgorithm
table-strategy:
hint:
algorithm-class-name: com.fig.sharding.hint.MyTableHintShardingAlgorithm
1、⾏表达式分⽚策略
# =========== ↓↓↓↓↓↓⾏表达式分⽚策略↓↓↓↓↓↓ ===========
# 在配置中使⽤ Groovy 表达式，提供对 SQL语句中的 = 和 IN 的分⽚操作⽀持，只⽀持单分⽚健。
# ====== ↓↓↓↓↓↓分库↓↓↓↓↓↓ ======
database-strategy:
inline:
sharding-column: user_id # 添加数据分库字段(根据字段插⼊数据到哪个库 ex：user_id)
algorithm-expression: ds-master-$->{user_id % 2} # 根据user_id取模拆分到不同的库中
# ====== ↓↓↓↓↓↓分表↓↓↓↓↓↓ ======
table-strategy:
inline:
sharding-column: sex # 添加数据分表字段(根据字段插⼊数据到哪个表 ex：sex)
algorithm-expression: t_user$->{sex % 2} # 分⽚算法表达式 => 根据⽤户性别取模拆分到不同的表中
2、标准分⽚策略
A: 精确分⽚算法
# 精确分⽚算法 => sql在分库/分表键上执⾏ = 与 IN 时触发计算逻辑，否则不⾛分库/分表，全库/全表执⾏。
database-strategy:
standard:
sharding-column: user_id # 分库⽤到的键
precise-algorithm-class-name: com.fig.sharding.precise.MyDbPreciseShardingAlgorithm # ⾃定义分库算法实现类table-strategy:
standard:
sharding-column: sex # 添加数据分表字段(根据字段插⼊数据到那个表 ex：sex)
precise-algorithm-class-name: com.fig.sharding.precise.MyTablePreciseShardin
gAlgorithm # ⾃定义分表算法实现类@Slf4j
public class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 分⽚策略
*
* @param dbNameList 所有数据源
* @param shardingValue SQL执⾏时传⼊的分⽚值
* @return 数据源名称
*/
@Override
public String doSharding(Collection<String> dbNameList, PreciseShardingValue<Long> shardingValue) {
log.info("[MyDbPreciseShardingAlgorithm] SQL执⾏时传⼊的分⽚值: [{}]", shardingValue);
// 根据user_id取模拆分到不同的库中
Long userId = Value();
for (String dbNameItem : dbNameList) {
if (dsWith(String.valueOf(userId % 2))) {
return dbNameItem;
}
}
return null;
}
}
@Slf4j
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Byte> {
/**
* 分⽚策略
*
* @param tableNameList 所有表名
* @param shardingValue SQL执⾏时传⼊的分⽚值
* @return 表名
*/
@Override
public String doSharding(Collection<String> tableNameList, PreciseShardingValue<Byte> shardingValue) {
log.info("[MyTablePreciseShardingAlgorithm] SQL执⾏时传⼊的分⽚值: [{}]", shardingValue);
// 根据⽤户性别取模拆分到不同的表中
Byte sex = Value();
for (String tableNameItem : tableNameList) {
if (dsWith(String.valueOf(sex % 2))) {
return tableNameItem;
}
}
return null;
}
}
B: 范围分⽚算法
# 范围分⽚算法 => sql在分库/分表键上执⾏ BETWEEN AND、>、<、>=、<= 时触发计算逻辑，否则不⾛分库/分表，全库/全表执⾏。database-strategy:
standard:
sharding-column: user_id
precise-algorithm-class-name: com.fig.sharding.range.MyDbPreciseShardingAlgorithm
range-algorithm-class-name: com.fig.sharding.range.MyDbRangeShardingAlgorithm
table-strategy:
standard:
sharding-column: sex
precise-algorithm-class-name: com.fig.sharding.range.MyTablePreciseShardingAlgorithm
range-algorithm-class-name: com.fig.sharding.range.MyTableRangeShardingAlgorithm
@Slf4j
public class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 分⽚策略
*
* @param dbNameList 所有数据源
* @param shardingValue SQL执⾏时传⼊的分⽚值
* @return 数据源名称
*/
@Override
public String doSharding(Collection<String> dbNameList, PreciseShardingValue<Long> shardingValue) {
log.info("[MyDbPreciseShardingAlgorithm] SQL执⾏时传⼊的分⽚值: [{}]", shardingValue);
// 根据user_id取模拆分到不同的库中
Long userId = Value();
for (String dbNameItem : dbNameList) {
if (dsWith(String.valueOf(userId % 2))) {
return dbNameItem;
}
}
return null;
}
}
@Slf4j
public class MyDbRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> dbNameList, RangeShardingValue<Long> shardingValue) {
log.info("[MyDbRangeShardingAlgorithm] shardingValue: [{}]", shardingValue);
List<String> result = wLinkedList();
int dbSize = dbNameList.size();
// 从sql 中获取 Between 1 and 1000 的值
// lower：1
// upper：1000
Range<Long> rangeValue = ValueRange();
Long lower = rangeValue.lowerEndpoint();
Long upper = rangeValue.upperEndpoint();
// 根据范围值取偶选择库
for (Long i = lower; i <= upper; i++) {
for (String dbNameItem : dbNameList) {
if (dsWith(String.valueOf(i % 2))) {
result.add(dbNameItem);
}
if (result.size() >= dbSize) {
return result;
}
}
}
return result;
}
}
@Slf4j
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Byte> {
/**
* 分⽚策略
*
* @param tableNameList 所有表名
* @param shardingValue SQL执⾏时传⼊的分⽚值
* @return 表名
*/
@Override
public String doSharding(Collection<String> tableNameList, PreciseShardingValue<Byte> shardingValue) {
log.info("[MyTablePreciseShardingAlgorithm] SQL执⾏时传⼊的分⽚值: [{}]", shardingValue);
// 根据⽤户性别取模拆分到不同的表中
Byte sex = Value();
for (String tableNameItem : tableNameList) {
if (dsWith(String.valueOf(sex % 2))) {
return tableNameItem;
}
}
return null;
}
}
@Slf4j
public class MyTableRangeShardingAlgorithm implements RangeShardingAlgorithm<Byte> {
@Override
public Collection<String> doSharding(Collection<String> tableNameList, RangeShardingValue<Byte> shardingValue) {
log.info("[MyTableRangeShardingAlgorithm] shardingValue: [{}]", shardingValue);
Set<String> tableNameResultList = new LinkedHashSet<>();
Range<Byte> rangeValue = ValueRange();
Byte lower = rangeValue.lowerEndpoint();
Byte upper = rangeValue.upperEndpoint();
// between 0 and 1
// 根据性别值选择表
for (String tableNameItem : tableNameList) {
if (dsWith(String.valueOf(lower))
|| dsWith(String.valueOf(upper))) {
tableNameResultList.add(tableNameItem);
}
}
return tableNameResultList;
}
}
3、复合分⽚策略
# =========== ↓↓↓↓↓↓复合分⽚策略↓↓↓↓↓↓ ===========
# SQL 语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 等操作符，不同的是复合分⽚策略⽀持对多个分⽚健操作。
database-strategy:
complex:
sharding-columns: user_id,sex
algorithm-class-name: com.fig.shardingplex.MyDbComplexKeysShardingAlgorithm
table-strategy:
complex:
sharding-columns: user_id,sex
algorithm-class-name: com.fig.shardingplex.MyTableComplexKeysShardingAlgorithm
@Slf4j
public class MyDbComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> dbNameList, ComplexKeysShardingValue<String> complexKeysShardingValue) { log.info("[MyDbComplexKeysShardingAlgorithm] complexKeysShardingValue: [{}]", complexKeysShardingValue);
List<String> dbResultList = new ArrayList<>();
int dbSize = dbNameList.size();
// 得到每个分⽚健对应的值
// ⽤户id 范围查询
Range<String> rangeUserId = ColumnNameAndRangeValuesMap().get("user_id");
// 性别
List<String> sexValueList = ShardingValue(complexKeysShardingValue, "sex");
// 对两个分⽚健进⾏逻辑操作，选择最终数据进哪⼀库？ TODO
for (String sex : sexValueList) {
String suffix = String.valueOf(Long.parseLong(sex) % 2);
for (String dbNameItem : dbNameList) {
if (dsWith(suffix)) {
dbResultList.add(dbNameItem);
}
if (dbResultList.size() >= dbSize) {
return dbResultList;
}
}
}
return dbResultList;
}
private List<String> getShardingValue(ComplexKeysShardingValue<String> shardingValues, final String key) {
List<String> valueList = new ArrayList<>();
Map<String, Collection<String>> columnNameAndShardingValuesMap = ColumnNameAndShardingValuesMap();
if (ainsKey(key)) {
valueList.(key));
}
return valueList;
}
}
@Slf4j
public class MyTableComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> tableNameList, ComplexKeysShardingValue<Long> complexKeysShardingValue) {
log.info("[MyTableComplexKeysShardingAlgorithm] complexKeysShardingValue: [{}]", complexKeysShardingValue); Set<String> tableNameResultList = new LinkedHashSet<>();
int tableSize = tableNameList.size();
// ⽤户id 范围查询
Range<Long> rangeUserId = ColumnNameAndRangeValuesMap().get("user_id");
Long lower = rangeUserId.lowerEndpoint();
Long upper = rangeUserId.upperEndpoint();
// 根据user_id选择表 TODO ...
for (String tableNameItem : tableNameList) {
if (dsWith(String.valueOf(lower % 2))
|| dsWith(String.valueOf(upper % 2))) {
tableNameResultList.add(tableNameItem);
}
if (tableNameResultList.size() >= tableSize) {
return tableNameResultList;
}
}
return tableNameResultList;
}
}
4、Hint分⽚策略
#=========== ↓↓↓↓↓↓ hint分⽚策略↓↓↓↓↓↓ ===========
# 通过 Hint API实现个性化配置 => 可查看 com.zhengqing.demo.service.impl.UserServiceImpl.listPageForHint
database-strategy:
hint:
algorithm-class-name: com.fig.sharding.hint.MyDbHintShardingAlgorithm
table-strategy:
hint:
algorithm-class-name: com.fig.sharding.hint.MyTableHintShardingAlgorithm
@Slf4j
public class MyDbHintShardingAlgorithm implements HintShardingAlgorithm<Integer> {
@Override
public Collection<String> doSharding(Collection<String> dbNameList, HintShardingValue<Integer> hintShardingValue) { log.info("[MyDbHintShardingAlgorithm] hintShardingValue: [{}]", hintShardingValue);
Collection<String> dbResultList = new ArrayList<>();
int dbSize = dbNameList.size();
for (String dbNameItem : dbNameList) {
for (Integer shardingValue : Values()) {
if (dsWith(String.valueOf(shardingValue % 2))) {
dbResultList.add(dbNameItem);
}
if (dbResultList.size() >= dbSize) {
return dbResultList;
}
}
}
return dbResultList;
}
}
@Slf4j
public class MyTableHintShardingAlgorithm implements HintShardingAlgorithm<Integer> {
@Override
public Collection<String> doSharding(Collection<String> tableNameList, HintShardingValue<Integer> hintShardingValue) { log.info("[MyTableHintShardingAlgorithm] hintShardingValue: [{}]", hintShardingValue);
Collection<String> tableResultList = new ArrayList<>();
int tableSize = tableNameList.size();
Collection<Integer> hintShardingValueValueList = Values();
for (String tableName : tableNameList) {
for (Integer shardingValue : hintShardingValueValueList) {
if (dsWith(String.valueOf(shardingValue % 2))) {
tableResultList.add(tableName);
}
if (tableResultList.size() >= tableSize) {
return tableResultList;
}
}
}
return tableResultList;
}
}
使⽤时动态触发如下：
public IPage<User> listPageForHint() {
// 清除掉上⼀次的规则，否则会报错
HintManager.clear();
// HintManager API ⼯具类实例
HintManager hintManager = Instance();
// 库 => 主要是将value值传送到 MyDbHintShardingAlgorithm 中做逻辑分库处理
hintManager.addDatabaseShardingValue("t_user", 100);
hintManager.addDatabaseShardingValue("t_user", 1000);
// 指定表的分⽚健 => 指定查t_user0
hintManager.addTableShardingValue("t_user", 0);
// hintManager.addTableShardingValue("t_user", 1);
/
/ 读写分离强制读主库，避免造成主从复制导致的延迟
hintManager.setMasterRouteOnly();
// 查询数据
Page<User> result = this.userMapper.selectPage(new Page<>(1, 10),
new LambdaQueryWrapper<User>()
.eq(User::getSex, "0")
.between(User::getUserId, 1L, 1000L)
);
// 清除规则
hintManager.close();
return result;
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论