ShardingJDBC实战配置

分库分表

shardingjdbc的核心功能是数据分片和读写分离，通过ShardingJDBC，应用可以
透明的使用JDBC访问已经分库分表、读写分离的多个数据源，而不用关心数据源的
数量以及数据如何分布

• 先了解几个概念

逻辑表：水平拆分的数据库的相同逻辑和数据结构表的总称

真实表：在分片的数据库中真实存在的物理表。

数据节点：数据分片的最小单元。由数据源名称和数据表组成

绑定表：分片规则一致的主表和子表。

广播表：也叫公共表，指素有的分片数据源中都存在的表，表结构和表中的数据

在每个数据库中都完全一致。例如字典表。

分片键：用于分片的数据库字段，是将数据库(表)进行水平拆分的关键字段。

SQL中若没有分片字段，将会执行全路由，性能会很差。

分片算法：通过分片算法将数据进行分片，支持通过=、BETWEEN和IN分片。

分片算法需要由应用开发者自行实现，可实现的灵活度非常高。

分片策略：真正用于进行分片操作的是分片键+分片算法，也就是分片策略。在

ShardingJDBC中一般采用基于Groovy表达式的inline分片策略，通过一个包含

分片键的算法表达式来制定分片策略，如t_user_$->{u_id%8}标识根据u_id模

8，分成8张表，表名称为t_user_0到t_user_7。

基础配置

在一个库中将一张表的数据按照奇数和偶数分到两个表中

#配置数据源
spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://115.29.197.4:3306/masterdemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
#配置真实表分布 逻辑表 m1.course_$->{1..2} 表示course表的真实分布 course_1 和 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
#主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#雪花算法
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#默认的配置 可以不配
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#配置分表策略 分片建是cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#分片算法 偶数就会进去course_1 奇数就会加入到course_2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#其他运行属性
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true

分库操作

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://115.29.197.4:3306/course1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://115.29.197.4:3306/course2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
#真实表分布，分库，分表
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#可以不配置使用默认既可
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1

#策略1：
#inline分片策略只支持精确查找 where id = ...这种
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}

#策略2：
#standard标准分片策略 只支持一个分片建，也就是一个字段，支持精确查询和范围查询
#standard支持precise和range（精确的 where id = ... 和 范围查询where id between ... and ...）需要配置策略类
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.roy.shardingDemo.algorithem.MyPreciseTableShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=com.roy.shardingDemo.algorithem.MyRangeTableShardingAlgorithm

#spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.roy.shardingDemo.algorithem.MyPreciseDSShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.roy.shardingDemo.algorithem.MyRangeDSShardingAlgorithm

#策略3：
#complex复杂分片策略 支持多字段的分库分表，例如：（where id = ... and name = ... and status in(..) ）需要配置策略类
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns= cid, user_id
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=com.roy.shardingDemo.algorithem.MyComplexTableShardingAlgorithm
#
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.sharding-columns=cid, user_id
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.algorithm-class-name=com.roy.shardingDemo.algorithem.MyComplexDSShardingAlgorithm

#策略4：
#hint强制路由策略 只需要配置算法类，不需要配置分片建 ，这里只指定了分表的策略 分库的策略默认是全部，也可以指定其他策略
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.roy.shardingDemo.algorithem.MyHintTableShardingAlgorithm

#广播表配置 t_dict广播表 广播表的概念就是每个库中都保存全量的数据
spring.shardingsphere.sharding.broadcast-tables=t_dict
#配置主键生成策略
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
#雪花算法 work-id可选属性可以不配置
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE


spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true

• 注意多种策略不能同时使用，否则会冲突。
• 广播表的概念： 每个库中都保存全量的数据

以下贴出策略类

精确查找或范围查找的表策略

public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    //select * from course where cid = ? or cid in (?,?)
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String logicTableName = shardingValue.getLogicTableName();
        String cid = shardingValue.getColumnName();
        Long cidValue = shardingValue.getValue();
        //实现 course_$->{cid%2+1) 找表，和之前分表存储的时候一样，偶数去course_1表找，奇数去course_2表找，要实现相同的算法
        BigInteger shardingValueB = BigInteger.valueOf(cidValue);
        BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
        String key = logicTableName+"_"+resB;
        if(availableTargetNames.contains(key)){
            return key;
        }
        //couse_1, course_2
        throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
    }
}

精确查找或范围查找的数据库策略

public class MyPreciseDSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    //select * from course where cid = ? or cid in (?,?)
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String logicTableName = shardingValue.getLogicTableName();
        String cid = shardingValue.getColumnName();
        Long cidValue = shardingValue.getValue();
        //实现 course_$->{cid%2+1)
        BigInteger shardingValueB = BigInteger.valueOf(cidValue);
        BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
        String key = "m"+resB;
        if(availableTargetNames.contains(key)){
            return key;
        }
        //couse_1, course_2
        throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
    }
}

---

范围查找的表策略

public class MyRangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        //select * from course where cid between 1 and 100;
        // 上限
        Long upperVal = shardingValue.getValueRange().upperEndpoint();//100
        // 下限
        Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//1

        String logicTableName = shardingValue.getLogicTableName();
         // 两个表都要查询
        return Arrays.asList(logicTableName+"_1",logicTableName+"_2");
    }
}

范围查找的数据库策略

public class MyRangeDSShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        //select * from course where cid between 1 and 100;
        Long upperVal = shardingValue.getValueRange().upperEndpoint();//100
        Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//1

        String logicTableName = shardingValue.getLogicTableName();
        return Arrays.asList("m1","m2");
    }
}

复杂分片表策略类

public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
//    SELECT  cid,cname,user_id,cstatus  FROM course
//    WHERE  cid BETWEEN ? AND ? AND user_id = ?
    // 这里返回值是什么类型的就构建什么数据类型
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
        // 因为user_id 不是一个主键所以查询结果是一个集合，所以也支持in查询
        Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");

        // 上限和下限
        Long upperVal = cidRange.upperEndpoint();
        Long lowerVal = cidRange.lowerEndpoint();

        List<String> res = new ArrayList<>();

        for(Long userId: userIdCol){
            // 针对user_id 进行查找
            //course_{userID%2+1}
            BigInteger userIdB = BigInteger.valueOf(userId);
            BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));

            res.add("m"+target);
        }

        return res;
    }
}

复杂分片的类策略类

public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
//    SELECT  cid,cname,user_id,cstatus  FROM course
//    WHERE  cid BETWEEN ? AND ? AND user_id = ?
    // 这里返回值是什么类型的就构建什么数据类型
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
        // 因为user_id 不是一个主键所以查询结果是一个集合，所以也支持in查询
        Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");

        // 上限和下限
        Long upperVal = cidRange.upperEndpoint();
        Long lowerVal = cidRange.lowerEndpoint();

        List<String> res = new ArrayList<>();

        for(Long userId: userIdCol){
            // 针对user_id 进行查找
            //course_{userID%2+1}
            BigInteger userIdB = BigInteger.valueOf(userId);
            BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));

            res.add("m"+target);
        }

        return res;
    }
}

**简单理解就是：按照一定的策略将数据存到指定的库或者表中，在查找时候再按照对应的策略到指定的库或表中查找 **

关于多表关联下绑定表的配置

spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://115.29.197.4:3306/masterdemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

#逻辑表
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=m1.t_dict_$->{1..2}
#分片建
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict_$->{ustatus.toInteger()%2+1}

#逻辑表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=m1.t_user_$->{1..2}
#分片建
spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=t_user_$->{ustatus.toInteger()%2+1}
#绑定表示例 user和t_dict分别是逻辑表的名称，默认1会和1绑定2会和2绑定，这里是一个数组，可以指定多组绑定，绑定的原因下面会说明
spring.shardingsphere.sharding.binding-tables[0]=user,t_dict

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true

    @Select("select u.user_id,u.username,d.uvalue ustatus from user u left join t_dict d on u.ustatus = d.ustatus")
    public List<User> queryUserStatus();

如果不进行绑定执行以上sql会出现下面的场景—出现笛卡尔积的查询

2021-09-25 00:00:45.515  INFO 85580 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_1 d on u.ustatus = d.ustatus
2021-09-25 00:00:45.515  INFO 85580 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_2 d on u.ustatus = d.ustatus
2021-09-25 00:00:45.515  INFO 85580 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_1 d on u.ustatus = d.ustatus
2021-09-25 00:00:45.515  INFO 85580 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_2 d on u.ustatus = d.ustatus

User{userId=648309825893044225, username='user No 1', ustatus='null', uage=0}
User{userId=648309827151335425, username='user No 3', ustatus='null', uage=0}
User{userId=648309828405432321, username='user No 5', ustatus='null', uage=0}
User{userId=648309829634363393, username='user No 7', ustatus='null', uage=0}
User{userId=648309830963957761, username='user No 9', ustatus='null', uage=0}
User{userId=648309825893044225, username='user No 1', ustatus='??', uage=0}
User{userId=648309827151335425, username='user No 3', ustatus='??', uage=0}
User{userId=648309828405432321, username='user No 5', ustatus='??', uage=0}
User{userId=648309829634363393, username='user No 7', ustatus='??', uage=0}
User{userId=648309830963957761, username='user No 9', ustatus='??', uage=0}
User{userId=648309825247121408, username='user No 0', ustatus='???', uage=0}
User{userId=648309826517995520, username='user No 2', ustatus='???', uage=0}
User{userId=648309827772092416, username='user No 4', ustatus='???', uage=0}
User{userId=648309828950691840, username='user No 6', ustatus='???', uage=0}
User{userId=648309830305452032, username='user No 8', ustatus='???', uage=0}
User{userId=648309825247121408, username='user No 0', ustatus='null', uage=0}
User{userId=648309826517995520, username='user No 2', ustatus='null', uage=0}
User{userId=648309827772092416, username='user No 4', ustatus='null', uage=0}
User{userId=648309828950691840, username='user No 6', ustatus='null', uage=0}
User{userId=648309830305452032, username='user No 8', ustatus='null', uage=0}

加上绑定之后可以看到t_user_1只和t_dict_1关联

2021-09-25 00:03:20.027  INFO 85617 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_1 d on u.ustatus = d.ustatus
2021-09-25 00:03:20.027  INFO 85617 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_2 d on u.ustatus = d.ustatus
User{userId=648309825247121408, username='user No 0', ustatus='???', uage=0}
User{userId=648309826517995520, username='user No 2', ustatus='???', uage=0}
User{userId=648309827772092416, username='user No 4', ustatus='???', uage=0}
User{userId=648309828950691840, username='user No 6', ustatus='???', uage=0}
User{userId=648309830305452032, username='user No 8', ustatus='???', uage=0}
User{userId=648309825893044225, username='user No 1', ustatus='??', uage=0}
User{userId=648309827151335425, username='user No 3', ustatus='??', uage=0}
User{userId=648309828405432321, username='user No 5', ustatus='??', uage=0}
User{userId=648309829634363393, username='user No 7', ustatus='??', uage=0}
User{userId=648309830963957761, username='user No 9', ustatus='??', uage=0}

主从 读写分离配置

`#配置主从数据源，要基于MySQL主从架构。
spring.shardingsphere.datasource.names=m0,s0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/slave_test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=rootroot

spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://115.29.197.4:3306/master_test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root
#读写分离规则， m0 主库，s0 从库
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names[0]=s0
#基于读写分离的表分片
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=ds0.dict

spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true