sharding-jdbc四种分片策略
迪丽瓦拉
2025-05-31 07:22:12
0次
一、标准分片策略（standard）
1、精确分片
配置文件
spring:shardingsphere:#开启sql显示props:sql:show: truedatasource:# 配置数据源names: db0,db1db0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdb1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootsharding:#唯一库数据default-data-source-name: db0#分库default-database-strategy:standard:# 添加数据分库字段(根据字段插入数据到那个表)sharding-column: id#精确分片precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm#分表tables:#表名db_user:actual-data-nodes: db$->{0..1}.db_user_$->{0..2}key-generator:column: id # 主键IDtype: SNOWFLAKE # 生成策略雪花idtable-strategy:standard:sharding-column: id#精确分片precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm分库规则
package com.example.sharding_test.strategy.database;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import java.util.Collection;/*** 精确分片** @author shuai* @since 2023-03-19*/
public class DatabasePreciseAlgorithm implements PreciseShardingAlgorithm {/*** 精确分片* @param collection 数据源集合* @param preciseShardingValue 分片参数* @return 数据库*/@Overridepublic String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {//分片键的值Long value = preciseShardingValue.getValue();String dbName = "db" + (value % 2);if(!collection.contains(dbName)){throw new UnsupportedOperationException("数据源"+ dbName + "不存在");}return dbName;}
} 分表规则
package com.example.sharding_test.strategy.table;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import java.util.Collection;/*** 精确分片** @author shuai* @since 2023-03-19*/
public class TablePreciseAlgorithm implements PreciseShardingAlgorithm {/*** 精确分片* @param collection 数据源集合* @param preciseShardingValue 分片参数* @return 数据库*/@Overridepublic String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {//分片键的值Long value = preciseShardingValue.getValue();String tableName = preciseShardingValue.getLogicTableName() + "_" + (value % 3);if(!collection.contains(tableName)){throw new UnsupportedOperationException("表"+ tableName + "不存在");}return tableName;}
} 2、范围分片
配置文件
spring:shardingsphere:#开启sql显示props:sql:show: truedatasource:# 配置数据源names: db0,db1db0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdb1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootsharding:#唯一库数据default-data-source-name: db0#分库default-database-strategy:standard:# 添加数据分库字段(根据字段插入数据到那个表)sharding-column: id#精确分片precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm#范围分片range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm #分表tables:#表名db_user:actual-data-nodes: db$->{0..1}.db_user_$->{0..2}key-generator:column: id # 主键IDtype: SNOWFLAKE # 生成策略雪花idtable-strategy:standard:sharding-column: id#精确分片precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm#范围分片range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
分库规则
package com.example.sharding_test.strategy.database;import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;import java.util.Collection;/*** 范围分片** @author shuai* @since 2023-03-19*/
public class DatabaseRangeAlgorithm implements RangeShardingAlgorithm {/*** 范围分片* @param collection 数据源集合* @param rangeShardingValue 分片参数* @return 直接返回源*/@Overridepublic Collection doSharding(Collection collection, RangeShardingValue rangeShardingValue) {return collection;}
} 分表规则
package com.example.sharding_test.strategy.table;import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;import java.util.Arrays;
import java.util.Collection;/*** 范围分片** @author shuai* @since 2023-03-19*/
public class TableRangeAlgorithm implements RangeShardingAlgorithm {/*** 范围分片* @param collection 数据源集合* @param rangeShardingValue 分片参数* @return 直接返回源*/@Overridepublic Collection doSharding(Collection collection, RangeShardingValue rangeShardingValue) {//逻辑表名称String logicTableName = rangeShardingValue.getLogicTableName();return Arrays.asList(logicTableName+"_0",logicTableName+"_1",logicTableName+"_2");}
} 行表达式分片策略（inline）
配置文件（需要注释其他的分片规则）
spring:shardingsphere:#开启sql显示props:sql:show: truedatasource:# 配置数据源names: db0,db1db0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdb1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootsharding:#唯一库数据default-data-source-name: db0#分库default-database-strategy:
# standard:
# # 添加数据分库字段(根据字段插入数据到那个表)
# sharding-column: id
# #精确分片
# precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
# #范围分片
# range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithminline:# 添加数据分表字段(根据字段插入数据到那个表)sharding-column: id# 分片算法表达式 => 通过id取余algorithm-expression: db$->{id % 2}#分表tables:#表名db_user:actual-data-nodes: db$->{0..1}.db_user_$->{0..2}key-generator:column: id # 主键IDtype: SNOWFLAKE # 生成策略雪花idtable-strategy:
# standard:
# sharding-column: id
# #精确分片
# precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
# #范围分片
# range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithminline:# 添加数据分表字段(根据字段插入数据到那个表)sharding-column: id# 分片算法表达式 => 通过id取余algorithm-expression: db_user_$->{id % 3}分库规则与分表规则可直接使用groovy脚本
例：db_user_$->{id % 3}
复合分片策略（complex）
配置文件
spring:shardingsphere:#开启sql显示props:sql:show: truedatasource:# 配置数据源names: db0,db1db0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdb1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootsharding:#唯一库数据default-data-source-name: db0#分库default-database-strategy:
# standard:
# # 添加数据分库字段(根据字段插入数据到那个表)
# sharding-column: id
# #精确分片
# precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
# #范围分片
# range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
# #行分片
# inline:
# # 添加数据分表字段(根据字段插入数据到那个表)
# sharding-column: id
# # 分片算法表达式 => 通过id取余
# algorithm-expression: db$->{id % 2}#复合分片complex:sharding-columns: id,agealgorithm-class-name: com.example.sharding_test.strategy.database.DatabaseComplexAlgorithm#分表tables:#表名db_user:actual-data-nodes: db$->{0..1}.db_user_$->{0..2}key-generator:column: id # 主键IDtype: SNOWFLAKE # 生成策略雪花idtable-strategy:
# standard:
# sharding-column: id
# #精确分片
# precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
# #范围分片
# range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
# inline:
# # 添加数据分表字段(根据字段插入数据到那个表)
# sharding-column: id
# # 分片算法表达式 => 通过id取余
# algorithm-expression: db_user_$->{id % 3}#复合分片complex:sharding-columns: id,agealgorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm
分库规则
package com.example.sharding_test.strategy.database;import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.assertj.core.util.Lists;import java.util.Collection;
import java.util.List;/*** 复合分片** @author shuai* @since 2023-03-19*/
public class DatabaseComplexAlgorithm implements ComplexKeysShardingAlgorithm {/**** @param collection 数据源集合* @param complexKeysShardingValue 分片键的值集合* @return 需要查找的数据源集合*/@Overridepublic Collection doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {//获取age的值Collection ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");List dbs = Lists.newArrayList();//通过age取模ageValues.forEach(item->{String dbName = "db"+((item+3)%2);dbs.add(dbName);});return dbs;}
} 分表规则
package com.example.sharding_test.strategy.table;import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.assertj.core.util.Lists;import java.util.Arrays;
import java.util.Collection;
import java.util.List;/*** 复合分片** @author shuai* @since 2023-03-19*/
public class TableComplexAlgorithm implements ComplexKeysShardingAlgorithm {@Overridepublic Collection doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {//获取age的值Collection ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");List dbs = Lists.newArrayList();//通过age取模ageValues.forEach(item->{String dbName = "db_user_"+((item+3)%3);dbs.add(dbName);});return dbs;}
} Hint分片策略（hint）
配置文件（需要注释其他的分片规则）
spring:shardingsphere:#开启sql显示props:sql:show: truedatasource:# 配置数据源names: db0,db1db0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootdb1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghaiusername: rootpassword: rootsharding:#唯一库数据default-data-source-name: db0#分库default-database-strategy:
# standard:
# # 添加数据分库字段(根据字段插入数据到那个表)
# sharding-column: id
# #精确分片
# precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
# #范围分片
# range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm#行分片inline:# 添加数据分表字段(根据字段插入数据到那个表)sharding-column: id# 分片算法表达式 => 通过id取余algorithm-expression: db$->{id % 2}
# #复合分片
# complex:
# sharding-columns: id,age
# algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseComplexAlgorithm#分表tables:#表名db_user:actual-data-nodes: db$->{0..1}.db_user_$->{0..2}key-generator:column: id # 主键IDtype: SNOWFLAKE # 生成策略雪花idtable-strategy:
# standard:
# sharding-column: id
# #精确分片
# precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
# #范围分片
# range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
# inline:
# # 添加数据分表字段(根据字段插入数据到那个表)
# sharding-column: id
# # 分片算法表达式 => 通过id取余
# algorithm-expression: db_user_$->{id % 3}
# #复合分片
# complex:
# sharding-columns: id,age
# algorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm#强制分片hint:algorithm-class-name: com.example.sharding_test.strategy.table.TableHintAlgorithm分表规则
package com.example.sharding_test.strategy.table;import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;import java.util.Arrays;
import java.util.Collection;/*** 精确分片** @author shuai* @since 2023-03-19*/
public class TableHintAlgorithm implements HintShardingAlgorithm {@Overridepublic Collection doSharding(Collection collection, HintShardingValue hintShardingValue) {String logicTableName = hintShardingValue.getLogicTableName();String dbName = logicTableName+"_"+hintShardingValue.getValues().toArray()[0];return Arrays.asList(dbName);}
} 测试hint
@Testvoid selectHintData(){HintManager manager = HintManager.getInstance();manager.addTableShardingValue("db_user",2);LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();wrapper.eq(DbUser::getAge,34);List dbUsers = dbUserMapper.selectList(wrapper);dbUsers.forEach(System.out::println);}