这篇文章主要讲解了“SpringBoot整合sharding-jdbc实现自定义分库分表的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“SpringBoot整合sharding-jdbc实现自定义分库分表的方法是什么”吧！
一、前言
SpringBoot整合sharding-jdbc实现分库分表与读写分离
二、简介
1、分片键
用于数据库/表拆分的关键字段
ex: 用户表根据user_id取模拆分到不同的数据库中
2、分片算法
可参考：https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding
精确分片算法
范围分片算法
复合分片算法
Hint分片算法
分片策略（分片键+分片算法）
行表达式分片策略
标准分片策略
复合分片策略
Hint分片策略
不分片策略
可查看源码 org.apache.shardingsphere.core.yaml.config.sharding.YamlShardingStrategyConfiguration
三、程序实现
温馨小提示：详情可查看案例demo源码
这里先贴出完整的application.yml配置，后面实现每一种分片策略时，放开其相应配置即可~
# 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: com.mysql.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: com.mysql.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 # 主库1-从库2 ds-slave-0-2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.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: com.mysql.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: com.mysql.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: com.mysql.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.apache.shardingsphere.core.yaml.config.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.zhengqing.demo.config.sharding.precise.MyDbPreciseShardingAlgorithm # 自定义分库算法实现类# table-strategy:# standard:# sharding-column: sex # 添加数据分表字段(根据字段插入数据到那个表 ex：sex)# precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyTablePreciseShardingAlgorithm # 自定义分表算法实现类 # 范围分片算法 => sql在分库/分表键上执行 BETWEEN AND、>、<、>=、<= 时触发计算逻辑，否则不走分库/分表，全库/全表执行。# database-strategy:# standard:# sharding-column: user_id# precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyDbPreciseShardingAlgorithm# range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyDbRangeShardingAlgorithm# table-strategy:# standard:# sharding-column: sex# precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyTablePreciseShardingAlgorithm# range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyTableRangeShardingAlgorithm # =========== ↓↓↓↓↓↓ 复合分片策略 ↓↓↓↓↓↓ =========== # SQL 语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 等操作符，不同的是复合分片策略支持对多个分片健操作。# database-strategy:# complex:# sharding-columns: user_id,sex# algorithm-class-name: com.zhengqing.demo.config.sharding.complex.MyDbComplexKeysShardingAlgorithm# table-strategy:# complex:# sharding-columns: user_id,sex# algorithm-class-name: com.zhengqing.demo.config.sharding.complex.MyTableComplexKeysShardingAlgorithm # =========== ↓↓↓↓↓↓ hint分片策略 ↓↓↓↓↓↓ =========== # 通过 Hint API实现个性化配置 => 可查看 com.zhengqing.demo.service.impl.UserServiceImpl.listPageForHint database-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyDbHintShardingAlgorithm table-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyTableHintShardingAlgorithm行表达式分片策略
# =========== ↓↓↓↓↓↓ 行表达式分片策略 ↓↓↓↓↓↓ ===========# 在配置中使用 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} # 分片算法表达式 => 根据用户性别取模拆分到不同的表中标准分片策略
A: 精确分片算法
# 精确分片算法 => sql在分库/分表键上执行 = 与 IN 时触发计算逻辑，否则不走分库/分表，全库/全表执行。database-strategy: standard: sharding-column: user_id # 分库用到的键 precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyDbPreciseShardingAlgorithm # 自定义分库算法实现类table-strategy: standard: sharding-column: sex # 添加数据分表字段(根据字段插入数据到那个表 ex：sex) precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.MyTablePreciseShardingAlgorithm # 自定义分表算法实现类@Slf4jpublic class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> dbNameList, PreciseShardingValue<Long> shardingValue) { log.info("[MyDbPreciseShardingAlgorithm] SQL执行时传入的分片值: [{}]", shardingValue); // 根据user_id取模拆分到不同的库中 Long userId = shardingValue.getValue(); for (String dbNameItem : dbNameList) { if (dbNameItem.endsWith(String.valueOf(userId % 2))) { return dbNameItem; } } return null; }}@Slf4jpublic class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Byte> { @Override public String doSharding(Collection<String> tableNameList, PreciseShardingValue<Byte> shardingValue) { log.info("[MyTablePreciseShardingAlgorithm] SQL执行时传入的分片值: [{}]", shardingValue); // 根据用户性别取模拆分到不同的表中 Byte sex = shardingValue.getValue(); for (String tableNameItem : tableNameList) { if (tableNameItem.endsWith(String.valueOf(sex % 2))) { return tableNameItem; } } return null; }}B: 范围分片算法
# 范围分片算法 => sql在分库/分表键上执行 BETWEEN AND、>、<、>=、<= 时触发计算逻辑，否则不走分库/分表，全库/全表执行。database-strategy: standard: sharding-column: user_id precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyDbPreciseShardingAlgorithm range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyDbRangeShardingAlgorithmtable-strategy: standard: sharding-column: sex precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyTablePreciseShardingAlgorithm range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.MyTableRangeShardingAlgorithm@Slf4jpublic class MyDbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> dbNameList, PreciseShardingValue<Long> shardingValue) { log.info("[MyDbPreciseShardingAlgorithm] SQL执行时传入的分片值: [{}]", shardingValue); // 根据user_id取模拆分到不同的库中 Long userId = shardingValue.getValue(); for (String dbNameItem : dbNameList) { if (dbNameItem.endsWith(String.valueOf(userId % 2))) { return dbNameItem; } } return null; }}@Slf4jpublic class MyDbRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> { @Override public Collection<String> doSharding(Collection<String> dbNameList, RangeShardingValue<Long> shardingValue) { log.info("[MyDbRangeShardingAlgorithm] shardingValue: [{}]", shardingValue); List<String> result = Lists.newLinkedList(); int dbSize = dbNameList.size(); // 从sql 中获取 Between 1 and 1000 的值 // lower：1 // upper：1000 Range<Long> rangeValue = shardingValue.getValueRange(); Long lower = rangeValue.lowerEndpoint(); Long upper = rangeValue.upperEndpoint(); // 根据范围值取偶选择库 for (Long i = lower; i <= upper; i++) { for (String dbNameItem : dbNameList) { if (dbNameItem.endsWith(String.valueOf(i % 2))) { result.add(dbNameItem); } if (result.size() >= dbSize) { return result; } } } return result; }}@Slf4jpublic class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Byte> { @Override public String doSharding(Collection<String> tableNameList, PreciseShardingValue<Byte> shardingValue) { log.info("[MyTablePreciseShardingAlgorithm] SQL执行时传入的分片值: [{}]", shardingValue); // 根据用户性别取模拆分到不同的表中 Byte sex = shardingValue.getValue(); for (String tableNameItem : tableNameList) { if (tableNameItem.endsWith(String.valueOf(sex % 2))) { return tableNameItem; } } return null; }}@Slf4jpublic 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 = shardingValue.getValueRange(); Byte lower = rangeValue.lowerEndpoint(); Byte upper = rangeValue.upperEndpoint(); // between 0 and 1 // 根据性别值选择表 for (String tableNameItem : tableNameList) { if (tableNameItem.endsWith(String.valueOf(lower)) || tableNameItem.endsWith(String.valueOf(upper))) { tableNameResultList.add(tableNameItem); } } return tableNameResultList; }}复合分片策略
# =========== ↓↓↓↓↓↓ 复合分片策略 ↓↓↓↓↓↓ ===========# SQL 语句中有>，>=, <=，<，=，IN 和 BETWEEN AND 等操作符，不同的是复合分片策略支持对多个分片健操作。database-strategy: complex: sharding-columns: user_id,sex algorithm-class-name: com.zhengqing.demo.config.sharding.complex.MyDbComplexKeysShardingAlgorithmtable-strategy: complex: sharding-columns: user_id,sex algorithm-class-name: com.zhengqing.demo.config.sharding.complex.MyTableComplexKeysShardingAlgorithm@Slf4jpublic 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 = complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("user_id"); // 性别 List<String> sexValueList = this.getShardingValue(complexKeysShardingValue, "sex"); // 对两个分片健进行逻辑操作，选择最终数据进哪一库？ TODO for (String sex : sexValueList) { String suffix = String.valueOf(Long.parseLong(sex) % 2); for (String dbNameItem : dbNameList) { if (dbNameItem.endsWith(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 = shardingValues.getColumnNameAndShardingValuesMap(); if (columnNameAndShardingValuesMap.containsKey(key)) { valueList.addAll(columnNameAndShardingValuesMap.get(key)); } return valueList; }}@Slf4jpublic 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 = complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("user_id"); Long lower = rangeUserId.lowerEndpoint(); Long upper = rangeUserId.upperEndpoint(); // 根据user_id选择表 TODO ... for (String tableNameItem : tableNameList) { if (tableNameItem.endsWith(String.valueOf(lower % 2)) || tableNameItem.endsWith(String.valueOf(upper % 2))) { tableNameResultList.add(tableNameItem); } if (tableNameResultList.size() >= tableSize) { return tableNameResultList; } } return tableNameResultList; }}Hint分片策略
#=========== ↓↓↓↓↓↓ hint分片策略 ↓↓↓↓↓↓ ===========# 通过 Hint API实现个性化配置 => 可查看 com.zhengqing.demo.service.impl.UserServiceImpl.listPageForHintdatabase-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyDbHintShardingAlgorithmtable-strategy: hint: algorithm-class-name: com.zhengqing.demo.config.sharding.hint.MyTableHintShardingAlgorithm@Slf4jpublic 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 : hintShardingValue.getValues()) { if (dbNameItem.endsWith(String.valueOf(shardingValue % 2))) { dbResultList.add(dbNameItem); } if (dbResultList.size() >= dbSize) { return dbResultList; } } } return dbResultList; }}@Slf4jpublic 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 = hintShardingValue.getValues(); for (String tableName : tableNameList) { for (Integer shardingValue : hintShardingValueValueList) { if (tableName.endsWith(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 = HintManager.getInstance(); // 库 => 主要是将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;}感谢各位的阅读，以上就是“SpringBoot整合sharding-jdbc实现自定义分库分表的方法是什么”的内容了，经过本文的学习后，相信大家对SpringBoot整合sharding-jdbc实现自定义分库分表的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！
