既有项目使用shardingshpere分库分表改造

背景

项目数据量比较大，导致查询很慢。sql优化之后，还得不到预期效果。由于业务关联多，多个业务表数据量都很大。故采用分库分表方案。
一开始，同事用shardingshpere 分片键进行分库分表。这样的话由于关联的表比较多，所以有些表要加上分片键，且所有的sql都要加上分片键条件才行。
其中一个模块分给我这边改。处于改动很大与觉得这样不太科学。去了解了一下shardingshpere。发现可以强制路由，即不带分片键，在代码层面先指定库表。故进行了改造。

方案

我们分库分表策略是根据数据的地区来的，且区域内的用户才能操作对应区域的数据。所以这边通过切面获取操作用户的地区，代码层面指定具体的库表。这样sql也用改动，也不用将没有表都加上分片键了。

实现

配置

#数据源别名
spring.shardingsphere.datasource.names=ds1,ds2
#数据源配置
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.url=jdbc:postgresql://127.0.0.1:5432/test?currentSchema=public
spring.shardingsphere.datasource.ds1.username=test
spring.shardingsphere.datasource.ds1.password=123456
spring.shardingsphere.datasource.ds1.driverClassName=org.postgresql.Driver

spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.url=jdbc:postgresql://127.0.0.1:5432/test2?currentSchema=public
spring.shardingsphere.datasource.ds2.username=test
spring.shardingsphere.datasource.ds2.password=123456
spring.shardingsphere.datasource.ds2.driverClassName=org.postgresql.Driver

#默认数据源配置
spring.shardingsphere.sharding.defaultDataSourceName=ds1
spring.shardingsphere.props.sql.parsecash=true
spring.shardingsphere.props.sql.show=true
spring.shardingsphere.props.sql.insertsExpedite.switch=true
spring.shardingsphere.props.sql.insertsExpedite.values=500

#分表配置
sharding.cloumn.region.ds1=1,2
sharding.cloumn.region.ds2=3,4

#分库分表算法类
hit.sharding.database.algorithm=com.augurit.test.HintShardingDataBaseAlgorithm
hit.sharding.table.algorithm=com.test.HintShardingTableAlgorithm
 
#分片表配置
spring.shardingsphere.sharding.binding-tables[0]=\
	goods, \
	order

#实际节点
spring.shardingsphere.sharding.tables.goods.actual-data-nodes=\
 ds$->{[ds1]}.goods$->{[${sharding.cloumn.region.ds1}]},\
 ds$->{[ds2]}.goods$->{[${sharding.cloumn.region.ds2}]}

#强制路由实现类
spring.shardingsphere.sharding.tables.goods.database-strategy.hint.algorithmClassName=${hit.sharding.database.algorithm}
spring.shardingsphere.sharding.tables.goods.table-strategy.hint.algorithmClassName=${hit.sharding.table.algorithm}



spring.shardingsphere.sharding.tables.order.actual-data-nodes=\
 ds$->{[ds1]}.order$->{[${sharding.cloumn.region.ds1}]},\
 ds$->{[ds2]}.order$->{[${sharding.cloumn.region.ds2}]}

spring.shardingsphere.sharding.tables.order.database-strategy.hint.algorithmClassName=${hit.sharding.database.algorithm}
spring.shardingsphere.sharding.tables.order.table-strategy.hint.algorithmClassName=${hit.sharding.table.algorithm}

获取分片表

由于打算尽量复用原shardingshpere 的配置。从断点源码得，可以从SpringBootShardingRuleConfigurationProperties类获得。

SpringBootShardingRuleConfigurationProperties.class部分源码

@ConfigurationProperties(
    prefix = "spring.shardingsphere.sharding"
)
public class SpringBootShardingRuleConfigurationProperties extends YamlShardingRuleConfiguration {
    public SpringBootShardingRuleConfigurationProperties() {
    }
}

YamlShardingRuleConfiguration.class部分源码

public class YamlShardingRuleConfiguration implements YamlConfiguration {
    private Map<String, YamlTableRuleConfiguration> tables = new LinkedHashMap();
    private Collection<String> bindingTables = new ArrayList();
    private Collection<String> broadcastTables = new ArrayList();
    private String defaultDataSourceName;
    private YamlShardingStrategyConfiguration defaultDatabaseStrategy;
    private YamlShardingStrategyConfiguration defaultTableStrategy;
    private YamlKeyGeneratorConfiguration defaultKeyGenerator;
    private Map<String, YamlMasterSlaveRuleConfiguration> masterSlaveRules = new LinkedHashMap();
    private YamlEncryptRuleConfiguration encryptRule;
}

AOP拦截所有请求

参照官网的强制路由文档编写AOP拦截器

@Aspect
@Component
@Slf4j
@EnableAspectJAutoProxy
public class ShardingControllerAop {

    @Autowired
    private SpringBootShardingRuleConfigurationProperties shardingRuleConfigurationProperties;

    @Pointcut("execution(* com..*Controller.*(..))")
    public void round() {
    }

    /**
     * @param point
     * @return
     */
    @Around("round()")
    public Object round(ProceedingJoinPoint point) throws Throwable {
        HintManager hintManager = HintManager.getInstance();
        try {
        	//获取当前用户区域
            String region = getRegion();
			//此处将region当成分片键。将值传入。会在后面的分库强制路由实体类和分表强制路由实体类去具体实现路由逻辑。
			//也可以参照文档，直接指定库和表。			
			//hintManager.addDatabaseShardingValue("t_order", 1);
    		//hintManager.addTableShardingValue("t_order", 2);
            shardingRuleConfigurationProperties.getBindingTables().stream().map(s -> s.split(",")).flatMap(Arrays::stream).forEach(
                    table -> {
                hintManager.addDatabaseShardingValue(table, region );
                hintManager.addTableShardingValue(table, region );
            });
            return point.proceed();
        }  finally {
            hintManager.close();
        }
    }
}

分库强制路由实现类

@Slf4j
public class HintShardingDataBaseAlgorithm implements HintShardingAlgorithm<String> {

    public Collection<String> doSharding(Collection<String> collection, HintShardingValue<String> value) {
        log.info("分库强制路由参数：collection:{},value:{}",JSON.toJSONString(value),JSON.toJSONString(collection));
        Collection<String> result = new HashSet<>();
        for(String s :value.getValues()){
            //根据AOP传进来的region匹配对应哪个数据库
            result.addAll(getDataBasesByHintShardingValue(s));
        }

        log.info("分库强制路由结果：{}",JSON.toJSONString(result));
        return result;
    }
    
}

分表强制路由实现类

@Slf4j
public class HintShardingTableAlgorithm implements HintShardingAlgorithm<String> {

    public Collection<String> doSharding(Collection<String> collection, HintShardingValue<String> value) {
        log.info("分表强制路由参数：collection:{},value:{}",JSON.toJSONString(value),JSON.toJSONString(collection));
        Collection<String> result = new HashSet<>();
        for(String s :value.getValues()){
            //根据AOP传进来的region匹配对应哪个表
            result.addAll(getTablesByHintShardingValue(s));
        }

        log.info("分表强制路由结果：{}",JSON.toJSONString(result));
        return result;
    }

}