SpringCloud下结合shardingSphere进⾏分库分表（实现
Sharding。。。
通过ShardingAlgorithm的实现，可以进⼀步发现分⽚策略的灵活和强⼤；可以实现⼀致性hash算法、按时间分⽚算法、以及mod算法等；更进⼀步，可以对同⼀个表按业务需求实现不同的分⽚算法，⽐如原来按年分⽚的业务表，⽐如随着业务量的扩展，需要提⾼分⽚频率，可是⼜不想进⾏⼤量历史数据迁移，可以在某⼀时刻开始按⽉或者按⽇分⽚；当然前提是要维护⼀个相对复杂的分⽚算法；
下⾯展⽰⼀个⾃定义分⽚算法原型，留作业务扩展；
业务模型和上⼀篇的inline表达式⼀样，下⾯进⾏核⼼代码说明：
1）核⼼pom⽂件内容
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
2）核⼼yml内容：
sharding:
jdbc:
datasource:
names: master0,master0salve0,master0slave1,master1,master1slave0,master1slave1
master0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/mcspcsharding0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
master0salve0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/mcspcsharding0s0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
master0slave1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/mcspcsharding0s1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
master1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/mcspcsharding1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
master1slave0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/mcspcsharding1s0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
master1slave1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/mcspcsharding1s1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
config:
sharding:
tables:
mc_member:
actual-nodes: mcspcsharding$->{0..1}.mc_member$->{0..1}
database-strategy:
standard:
sharding-column: gender
precise-algorithm-class-name: spcshardingdbtable.sharding.DbShardingAlgorithm
table-strategy:
complex:
sharding-columns: id
algorithm-class-name: spcshardingdbtable.sharding.MemberTblComplexKeySharding
binding-tables: mc_member  # 多个时逗号隔开
broadcast-tables: mc_master
master-slave-rules:
ms0:
master-data-source-name: master0
slave-data-source-names: master0salve0,master0slave1
ms1:
master-data-source-name: master1
slave-data-source-names: master1slave0,master1slave1
props:
sql:
show: true
3）database数据源的sharding算法，实现了PreciseShardingAlgorithm
package spcshardingdbtable.sharding;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import org.springframework.stereotype.Component;
import java.util.Collection;
@Component
public class DbShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {        Integer index = Value() % 2;
for (String dataSourceName : collection) {
if (dsWith(index + "")) {
return dataSourceName;
}
}
throw new UnsupportedOperationException();
}
}
4）table的sharding算法，实现了ComplexKeysShardingAlgorithm
package spcshardingdbtable.sharding;
llect.Range;
import io.shardingsphere.api.algorithm.sharding.ListShardingValue;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.ShardingValue;
import io.shardingsphere.api.algorithm.shardingplex.ComplexKeysShardingAlgorithm;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
* 通过复合分⽚键进⾏演⽰，覆盖Precise，Range，List三种类型的ShardingValue。
* 项⽬中应根据实际情况实现：
* 1精确分⽚PreciseShardingAlgorithm、
* 2范围分⽚RangeShardingAlgorithm
* 3复合分⽚ComplexKeysShardingAlgorithm
* 4⾮SQL解析分⽚HintShardingAlgorithm
*/
@Component
public class MemberTblComplexKeySharding implements ComplexKeysShardingAlgorithm {
private static String shardingColumn1 = "id"; // todo: 业务扩展
private static String targetLogicTable = "mc_member";
@Override
public Collection<String> doSharding(Collection<String> logicTables, Collection<ShardingValue> shardingValues) {
// 当设置多个shardingcolumn时，重写下⾯逻辑，根据shardingValues参数和实际分表业务规则计算出实际的actualTable        List<Long> ids = new ArrayList<>(); // todo: 业务扩展
ShardingValue shardingValue = getShardingValue(shardingValues, shardingColumn1); // todo: 业务扩展         if (shardingValue instanceof PreciseShardingValue) {
PreciseShardingValue<Long> preciseShardingValue = (PreciseShardingValue<Long>) shardingValue;
Long id = Value();
ids.add(id);
} else if (shardingValue instanceof RangeShardingValue) {
RangeShardingValue<Long> rangeShardingValue = (RangeShardingValue<Long>) shardingValue;
Range<Long> range = ValueRange();
for (Long index = range.lowerEndpoint(); index <= range.upperEndpoint(); index++) {
ids.add(index);
}
} else if (shardingValue instanceof ListShardingValue) {
ListShardingValue<Long> listShardingValue = (ListShardingValue<Long>) shardingValue;
ids.Values());
}
return getActualTables(logicTables, ids, targetLogicTable);  // todo：业务扩展传参     }
/**
* 根据sql语句中的id值，和logicTable进⾏拼接，获取实际要查询的表.
* 实际业务中遇到多个分⽚列时，除了ids还需要考虑其他key值，合并计算对逻辑表进⾏组装
*/
private List<String> getActualTables(Collection<String> logicTables, List<Long> ids, String targetLogicTable) {
List<String> actualTables = new ArrayList<>();
if (ains(targetLogicTable)) {
for (Long id : ids) {
// 作为演⽰，仅对id%2作为表后缀的匹配规则，今后应根据实际情况重置分⽚列和匹配规则。
String actualTableName = targetLogicTable + id%2;
if(!ains(actualTableName)) {
actualTables.add(targetLogicTable + (id % 2));
}
}
}
if (actualTables.size() == 0) {
throw new UnsupportedOperationException();
}
return actualTables;
}
/**
* 根据预知的分拆列获取到对应的shardingvalue对象
*/
private ShardingValue getShardingValue(Collection<ShardingValue> shardingValues, String column) {
for (ShardingValue sv : shardingValues) {
if (sv.getColumnName().equals(column)) {
return sv;
}
}
throw new UnsupportedOperationException();
}
}
5）启动类
package spcshardingdbtable;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
import t.annotation.ComponentScan;
import ansaction.annotation.EnableTransactionManagement;
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
@EnableDiscoveryClient
@EnableTransactionManagement
springboot推荐算法@ComponentScan(basePackages = {"com.chongmon","spcshardingdbtable"})
public class McSpcShardingDbTableApplication {
public static void main(String[] args) {
SpringApplication.run(McSpcShardingDbTableApplication.class, args);    }
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。