sharding-jdbc四種分片策略使用詳解
標(biāo)準(zhǔn)分片策略(standard)
1、精確分片
配置文件
spring:
shardingsphere:
#開啟sql顯示
props:
sql:
show: true
datasource:
# 配置數(shù)據(jù)源
names: db0,db1
db0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
db1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
sharding:
#唯一庫數(shù)據(jù)
default-data-source-name: db0
#分庫
default-database-strategy:
standard:
# 添加數(shù)據(jù)分庫字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
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 # 主鍵ID
type: SNOWFLAKE # 生成策略雪花id
table-strategy:
standard:
sharding-column: id
#精確分片
precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
分庫規(guī)則
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<Long> {
/**
* 精確分片
* @param collection 數(shù)據(jù)源集合
* @param preciseShardingValue 分片參數(shù)
* @return 數(shù)據(jù)庫
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
//分片鍵的值
Long value = preciseShardingValue.getValue();
String dbName = "db" + (value % 2);
if(!collection.contains(dbName)){
throw new UnsupportedOperationException("數(shù)據(jù)源"+ dbName + "不存在");
}
return dbName;
}
}
分表規(guī)則
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<Long> {
/**
* 精確分片
* @param collection 數(shù)據(jù)源集合
* @param preciseShardingValue 分片參數(shù)
* @return 數(shù)據(jù)庫
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> 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: true
datasource:
# 配置數(shù)據(jù)源
names: db0,db1
db0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
db1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
sharding:
#唯一庫數(shù)據(jù)
default-data-source-name: db0
#分庫
default-database-strategy:
standard:
# 添加數(shù)據(jù)分庫字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
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 # 主鍵ID
type: SNOWFLAKE # 生成策略雪花id
table-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
分庫規(guī)則
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<Long> {
/**
* 范圍分片
* @param collection 數(shù)據(jù)源集合
* @param rangeShardingValue 分片參數(shù)
* @return 直接返回源
*/
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
return collection;
}
}
分表規(guī)則
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<Long> {
/**
* 范圍分片
* @param collection 數(shù)據(jù)源集合
* @param rangeShardingValue 分片參數(shù)
* @return 直接返回源
*/
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
//邏輯表名稱
String logicTableName = rangeShardingValue.getLogicTableName();
return Arrays.asList(logicTableName+"_0",logicTableName+"_1",logicTableName+"_2");
}
}
行表達(dá)式分片策略(inline)
配置文件(需要注釋其他的分片規(guī)則)
spring:
shardingsphere:
#開啟sql顯示
props:
sql:
show: true
datasource:
# 配置數(shù)據(jù)源
names: db0,db1
db0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
db1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
sharding:
#唯一庫數(shù)據(jù)
default-data-source-name: db0
#分庫
default-database-strategy:
# standard:
# # 添加數(shù)據(jù)分庫字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
# 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:
# 添加數(shù)據(jù)分表字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
sharding-column: id
# 分片算法表達(dá)式 => 通過id取余
algorithm-expression: db$->{id % 2}
#分表
tables:
#表名
db_user:
actual-data-nodes: db$->{0..1}.db_user_$->{0..2}
key-generator:
column: id # 主鍵ID
type: SNOWFLAKE # 生成策略雪花id
table-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:
# 添加數(shù)據(jù)分表字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
sharding-column: id
# 分片算法表達(dá)式 => 通過id取余
algorithm-expression: db_user_$->{id % 3}
分庫規(guī)則與分表規(guī)則可直接使用groovy腳本
例:db_user_$->{id % 3}
復(fù)合分片策略(complex)
配置文件
spring:
shardingsphere:
#開啟sql顯示
props:
sql:
show: true
datasource:
# 配置數(shù)據(jù)源
names: db0,db1
db0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
db1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
sharding:
#唯一庫數(shù)據(jù)
default-data-source-name: db0
#分庫
default-database-strategy:
# standard:
# # 添加數(shù)據(jù)分庫字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
# 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:
# # 添加數(shù)據(jù)分表字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
# sharding-column: id
# # 分片算法表達(dá)式 => 通過id取余
# algorithm-expression: db$->{id % 2}
#復(fù)合分片
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 # 主鍵ID
type: SNOWFLAKE # 生成策略雪花id
table-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:
# # 添加數(shù)據(jù)分表字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
# sharding-column: id
# # 分片算法表達(dá)式 => 通過id取余
# algorithm-expression: db_user_$->{id % 3}
#復(fù)合分片
complex:
sharding-columns: id,age
algorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm
分庫規(guī)則
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;
/**
* 復(fù)合分片
*
* @author shuai
* @since 2023-03-19
*/
public class DatabaseComplexAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {
/**
*
* @param collection 數(shù)據(jù)源集合
* @param complexKeysShardingValue 分片鍵的值集合
* @return 需要查找的數(shù)據(jù)源集合
*/
@Override
public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {
//獲取age的值
Collection<Integer> ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");
List<String> dbs = Lists.newArrayList();
//通過age取模
ageValues.forEach(item->{
String dbName = "db"+((item+3)%2);
dbs.add(dbName);
});
return dbs;
}
}
分表規(guī)則
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;
/**
* 復(fù)合分片
*
* @author shuai
* @since 2023-03-19
*/
public class TableComplexAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {
@Override
public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {
//獲取age的值
Collection<Integer> ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");
List<String> dbs = Lists.newArrayList();
//通過age取模
ageValues.forEach(item->{
String dbName = "db_user_"+((item+3)%3);
dbs.add(dbName);
});
return dbs;
}
}
Hint分片策略(hint)
配置文件(需要注釋其他的分片規(guī)則)
spring:
shardingsphere:
#開啟sql顯示
props:
sql:
show: true
datasource:
# 配置數(shù)據(jù)源
names: db0,db1
db0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
db1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
sharding:
#唯一庫數(shù)據(jù)
default-data-source-name: db0
#分庫
default-database-strategy:
# standard:
# # 添加數(shù)據(jù)分庫字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
# 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:
# 添加數(shù)據(jù)分表字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
sharding-column: id
# 分片算法表達(dá)式 => 通過id取余
algorithm-expression: db$->{id % 2}
# #復(fù)合分片
# 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 # 主鍵ID
type: SNOWFLAKE # 生成策略雪花id
table-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:
# # 添加數(shù)據(jù)分表字段(根據(jù)字段插入數(shù)據(jù)到那個(gè)表)
# sharding-column: id
# # 分片算法表達(dá)式 => 通過id取余
# algorithm-expression: db_user_$->{id % 3}
# #復(fù)合分片
# complex:
# sharding-columns: id,age
# algorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm
#強(qiáng)制分片
hint:
algorithm-class-name: com.example.sharding_test.strategy.table.TableHintAlgorithm
分表規(guī)則
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<Integer> {
@Override
public Collection<String> doSharding(Collection<String> collection, HintShardingValue<Integer> hintShardingValue) {
String logicTableName = hintShardingValue.getLogicTableName();
String dbName = logicTableName+"_"+hintShardingValue.getValues().toArray()[0];
return Arrays.asList(dbName);
}
}
測試hint
@Test
void selectHintData(){
HintManager manager = HintManager.getInstance();
manager.addTableShardingValue("db_user",2);
LambdaQueryWrapper<DbUser> wrapper = Wrappers.lambdaQuery();
wrapper.eq(DbUser::getAge,34);
List<DbUser> dbUsers = dbUserMapper.selectList(wrapper);
dbUsers.forEach(System.out::println);
}
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
- SpringBoot整合sharding-jdbc?實(shí)現(xiàn)分庫分表操作的示例代碼
- Sharding-Jdbc如何配置主從讀寫分離模式
- Mysql使用Sharding-JDBC配置教程
- Sharding-jdbc報(bào)錯(cuò):Missing the data source name:‘m0‘解決方案
- SpringBoot項(xiàng)目中使用Sharding-JDBC實(shí)現(xiàn)讀寫分離的詳細(xì)步驟
- sharding-jdbc 兼容 MybatisPlus動(dòng)態(tài)數(shù)據(jù)源的配置方法
- SpringBoot集成Sharding-JDBC實(shí)現(xiàn)分庫分表方式
相關(guān)文章
Spring boot如何配置請求的入?yún)⒑统鰠son數(shù)據(jù)格式
這篇文章主要介紹了spring boot如何配置請求的入?yún)⒑统鰠son數(shù)據(jù)格式,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-11-11
Java隊(duì)列篇之實(shí)現(xiàn)數(shù)組模擬隊(duì)列及可復(fù)用環(huán)形隊(duì)列詳解
像棧一樣,隊(duì)列(queue)也是一種線性表,它的特性是先進(jìn)先出,插入在一端,刪除在另一端。就像排隊(duì)一樣,剛來的人入隊(duì)(push)要排在隊(duì)尾(rear),每次出隊(duì)(pop)的都是隊(duì)首(front)的人2021-10-10
SpringBoot項(xiàng)目從搭建到發(fā)布一條龍
這篇文章主要介紹了SpringBoot項(xiàng)目從搭建到發(fā)布一條龍,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2021-02-02
java中將一個(gè)List等分成n個(gè)list的工具方法(推薦)
下面小編就為大家?guī)硪黄猨ava中將一個(gè)List等分成n個(gè)list的工具方法(推薦)。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-03-03
SpringBoot搭配AOP實(shí)現(xiàn)自定義注解
這篇文章主要為大家詳細(xì)介紹了SpringBoot如何搭配AOP實(shí)現(xiàn)自定義注解,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2022-12-12

