在多数据源中对部分数据表使⽤shardingsphere进⾏分库分表
背景
近期在项⽬中需要使⽤多数据源，其中有⼀些表的数据量⽐较⼤，需要对其进⾏分库分表；⽽其他数据表数据量⽐较正常，单表就可以。
项⽬中可能使⽤其他组的数据源数据，因此需要多数据源⽀持。
经过调研多数据源配置⽐较⽅便。在该项⽬中分库分表的策略⽐较简单，仅根据⼀个字段分就可以，因此分库分表⽅案选⽤⽐较流⾏⽅便易⽤的 sharding-jdbc
需要实现的⽬标是根据学⽣姓名字段 student_name 进⾏分表，但是不需要分库。数据表是student_hist0 - student_hist9
引⼊ sharding-jdbc maven 依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
数据源配置⽂件
spring:
application:
name: student-service-provider
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
defaultPropertyInclusion: non_null
deserialization:
FAIL_ON_UNKNOWN_PROPERTIES: false
#对返回的时间进⾏格式化
datasource:
hikari:
student:
url: jdbc:mysql://127.0.0.1:3306/student_service?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useTimezone=true&serverTimezone=GMT%2 username: root
password: root123
log1:
url: jdbc:mysql://127.0.0.1:3306/log1?allowMultiQueries=true&useUnicode=true&characterEncoding=U
TF-8&useSSL=false
username: root
password: root123
log2:
url: jdbc:mysql://127.0.0.1:3306/log2?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root123
配置多数据源代码
DataSourceProperties 数据源
import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;
import org.t.properties.ConfigurationProperties;
import t.annotation.Configuration;
@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.hikari")
public class DataSourceProperties {
private HikariDataSource student;
private HikariDataSource log1;
private HikariDataSource log2;
}
DynamicDataSource 动态数据源
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
properties在哪打开public class DynamicDataSource extends AbstractRoutingDataSource {
/*
当前据源名称
*/
private static final ThreadLocal<String> dataSourceContextHolder = new ThreadLocal<>();
/*
设置数据源名称
*/
public static void setDataSourceName(String dataSourceName) {
dataSourceContextHolder.set(dataSourceName);
}
/*
获取据源名称
*/
public static String getDataSourceName() {
();
}
/*
清除当数据源名称
*/
public static void clearDataSource() {
}
@Override
protected Object determineCurrentLookupKey() {
return getDataSourceName();
}
}
MultiDataSource 多数据源标记
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface MultiDataSource {
String value() default DataSourceConfig.DEFAULT_DATASOURCE_NAME;
}
重点来了，因为是根据表的某⼀个字段进⾏分表，该字段是⼀个字符串类型，因此需要想根据字符串的⼀致性hash码算出在哪张表上。在sharding-jdbc需要实现 PreciseShardingAlgorithm 类
例如：想要在student.student_hist 表中根据学⽣姓名进⾏分表，逻辑表是student_hist，真实表是 student_hist0 - student_hist9
DataSourceConfig.SHARD_MMS_DATASOURCE_TABLE_COUNT=10
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
public class PreciseNodeIdShardingAlgorithm implements PreciseShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
for (String tbnm : collection) {
if (dsWith("hist" + (Value()) % DataSourceConfig.SHARD_MMS_DATASOURCE_TABLE_COUNT))) {
return tbnm;
}
}
throw new UnsupportedOperationException();
}
private static int getHash(String str) {
final int p = 16777619;
int hash = (int) 2166136261L;
for (int i = 0; i < str.length(); i++)
hash = (hash ^ str.charAt(i)) * p;
hash += hash << 13;
hash ^= hash >> 7;
hash += hash << 3;
hash ^= hash >> 17;
hash += hash << 5;
// 如果算出来的值为负数则取其绝对值
if (hash < 0)
hash = Math.abs(hash);
return hash;
}
}
多数据源装配 DataSourceConfig 。需要指定默认数据源，当不需要使⽤分表的表时就使⽤默认的数据源，否则指定需要分表的数据源。
在配置分表策略时如果不需要分库，可以不进⾏设置 tableRuleConfiguration.setDatabaseShardingStrategyConfig();
import org.apache.fig.sharding.ShardingRuleConfiguration;
import org.apache.fig.sharding.TableRuleConfiguration;
import org.apache.fig.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Autowired;
import t.annotation.Bean;
import t.annotation.Configuration;
import t.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import ansaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
@Configuration
public class DataSourceConfig {
public static final String DEFAULT_DATASOURCE_NAME = "defaultDataSource";
public static final String MMS_DATASOURCE_NAME = "mmsDatasource";
public static final String SHARD_MMS_DATASOURCE_NAME = "shardMmsDatasource";
public static int SHARD_MMS_DATASOURCE_TABLE_COUNT = 10;
@Autowired
private DataSourceProperties properties;
@Primary
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource() {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 默认数据源
dynamicDataSource.Mms());
// 配置多数据源
Map<Object, Object> dsMap = new HashMap();
dsMap.put(DEFAULT_DATASOURCE_NAME, Student());
dsMap.put(MMS_DATASOURCE_NAME, Student());
dsMap.put(SHARD_MMS_DATASOURCE_NAME, buildShardDatasources());
dynamicDataSource.setTargetDataSources(dsMap);
return dynamicDataSource;
}
public DataSource buildShardDatasources() {
// 配置多数据源
Map<String, DataSource> dsMap = new HashMap();
dsMap.put("shardMms", Mms());
TableRuleConfiguration stuHisTableRuleConfig = new TableRuleConfiguration("student_hist", "shardMms.student_hist${0.." + (SHARD_MMS_DATASOURCE_TABLE_COUNT - 1) + "}"); // tableRuleConfiguration.setDatabaseShardingStrategyConfig();
stuHisTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("student_name", new PreciseNodeIdShardingAlgorithm()));
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
try {
Properties properties = new Properties();
properties.setProperty("sql.show", "true");
ateDataSource(dsMap, shardingRuleConfig, properties);
} catch (SQLException throwables) {
throwables.printStackTrace();
throw new IllegalArgumentException();
}
}
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
}
多数据源切换 DataSourceAspect ，需要使⽤多数据源切换时，需要在service⽅法上使⽤标注⽅法 MultiDataSource 并指定数据源。
slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.flect.MethodSignature;
import t.annotation.Configuration;
import annotation.Order;
import flect.Method;
@Aspect
@Configuration
@Slf4j
@Order(1)
public class DataSourceAspect {
//切⼊点，service 中所有注解⽅法
@Pointcut("execution(* com.huitong..service..*.*(..)) && @annotation(com.fig.datasource.MultiDataSource)")
public void dataSourceAspect() {
}
@Around("dataSourceAspect()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
MethodSignature signature = (MethodSignature) Signature();
Method method = Method();
MultiDataSource ds = Annotation(MultiDataSource.class);
if (ds != null) {
DynamicDataSource.setDataSourceName(ds.value());
}
try {
return joinPoint.proceed();
} finally {
DynamicDataSource.clearDataSource();
}
}
}
参考⽂献：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论