基于shardingsphere在springMvc上分库分表策略的实现

Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

Apache ShardingSphere 设计哲学为 Database Plus，旨在构建异构数据库上层的标准和生态。 它关注如何充分合理地利用数据库的计算和存储能力，而并非实现一个全新的数据库。 它站在数据库的上层视角，关注它们之间的协作多于数据库自身。

在当前分布式数据库已经大行其道的今天，分库分表的设计已经在大数据量的处理上已经稍显落后，但是对于小型公司低成本而言，仅需在原有的mysql进行拓展上，分库分表的改造还是很适用的。

大部分网上的配置使用都是以yml的springboot适用的，这里是以springmvc的application.properties形式进行配置的，便于兼容

pom.xml

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.0.0</version>
</dependency>

application.properties

spring.shardingsphere.datasource.names=ds0,ds1

# ds0
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=${datasource.ds0.url}
spring.shardingsphere.datasource.ds0.username=${datasource.ds0.username}
spring.shardingsphere.datasource.ds0.password=${datasource.ds0.password}
spring.shardingsphere.datasource.ds0.initialSize=10
spring.shardingsphere.datasource.ds0.minIdle=10
spring.shardingsphere.datasource.ds0.maxActive=50
spring.shardingsphere.datasource.ds0.maxWait=6000
spring.shardingsphere.datasource.ds0.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.ds0.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.ds0.minEvictableIdleTimeMillis=300000
# ds1
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=${datasource.ds1.url}
spring.shardingsphere.datasource.ds1.username=${datasource.ds1.username}
spring.shardingsphere.datasource.ds1.password=${datasource.ds1.password}
spring.shardingsphere.datasource.ds1.initialSize=10
spring.shardingsphere.datasource.ds1.minIdle=10
spring.shardingsphere.datasource.ds1.maxActive=50
spring.shardingsphere.datasource.ds1.maxWait=6000
spring.shardingsphere.datasource.ds1.validationQuery=SELECT 1 FROM DUAL
spring.shardingsphere.datasource.ds1.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.ds1.minEvictableIdleTimeMillis=300000
# 分库策略
spring.shardingsphere.rules.sharding.binding-tables=attendance_student,attendance_teacher,user_base
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=city_id
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=dataBaseShardingStrategyAlgorithm

# 分表策略
spring.shardingsphere.rules.sharding.tables.attendance_student.actual-data-nodes=ds$->{0..1}.attendance_student_$->{0..99}
spring.shardingsphere.rules.sharding.tables.attendance_student.table-strategy.standard.sharding-algorithm-name=tableShardingStrategyAlgorithm
spring.shardingsphere.rules.sharding.tables.acs_attendance_student_record.table-strategy.standard.sharding-column=school_id

spring.shardingsphere.rules.sharding.tables.attendance_teacher.actual-data-nodes=ds$->{0..1}.attendance_teacher
spring.shardingsphere.rules.sharding.tables.user_base.actual-data-nodes=ds$->{0..1}.user_base

spring.shardingsphere.props.sql-show=true

这里对上面的配置文件进行一下解释

spring.shardingsphere.rules.sharding.binding-tables： 这个里面需要对数据库的所有表名以逗号分隔的形式进行配置，因为所有表都会执行分库策略
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=city_id 这里面是以city_id作为分库策略的配置
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=dataBaseShardingStrategyAlgorithm 这里面的dataBaseShardingStrategyAlgorithm是我这边自定义的一个分库策略

@Component(value = "dataBaseShardingStrategyAlgorithm")
public class DataBaseShardingStrategyAlgorithm implements StandardShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> databaseNames, PreciseShardingValue<String> preciseShardingValue) {

        //后期分库逻辑在配置后台处理，目前只是演示分库效果
        String cityIds= "1000,2000";
        List<Long> cityIdArray = JSONObject.parseArray(cityIds,Long.class);
        Long cityId= Long.parseLong(preciseShardingValue.getValue());
        if(cityIdArray .contains(cityId)){
            return "ds1";
        }else{
            return "ds0";
        }
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }


    @Override
    public void init() {

    }

    @Override
    public String getType() {
        return null;
    }
}

spring.shardingsphere.rules.sharding.tables.attendance_student.actual-data-nodes=ds$->{0..1}.attendance_student_$->{0..99} 这个是分表的配置，逻辑表是attendance_student，即sql查询的逻辑表表名，对应的实际表是attendance_student_0 -> attendance_student_99 这100张表，sharding会自动执行分表策略，这样就不需要对原始的sql进行改造。如果那张表不需要进行分表的话，那么就可以把逻辑表和实际表配置相同，也就不需要对这张表配置分表策略和分表依据字段了

spring.shardingsphere.rules.sharding.tables.attendance_student.table-strategy.standard.sharding-algorithm-name=tableShardingStrategyAlgorithm  这个是我的分表策略

@Component(value = "tableShardingStrategyAlgorithm")
public class TableShardingStrategyAlgorithm implements StandardShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        String tableName = "attendance_student_"+String.format("%s",Long.parseLong(shardingValue.getValue()) % 100);
        return tableName;
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }


    @Override
    public void init() {

    }

    @Override
    public String getType() {
        return null;
    }
}

spring.shardingsphere.rules.sharding.tables.attendance_student.table-strategy.standard.sharding-column=school_id 这个是我分表的依据字段

spring.shardingsphere.props.sql-show=true 这个是分库分表打印的日志，能够展示逻辑表和实际表，在研发和测试环境可以打开方便追查，但是生产环境的话会产生大量日志，前期可以开启排查问题，稳定后一定要关闭