SpringBoot整合sharding-jdbc实现自定义分库分表的实践

程序员文章站 2022-03-06 15:39:21

目录一、前言二、简介1、分片键2、分片算法三、程序实现一、前言springboot整合sharding-jdbc实现分库分表与读写分离本文将通过自定义算法来实现定制化的分库分表来扩展相应业务二、简介1...

一、前言

springboot整合sharding-jdbc实现分库分表与读写分离

本文将通过自定义算法来实现定制化的分库分表来扩展相应业务

二、简介

1、分片键

用于数据库/表拆分的关键字段

ex: 用户表根据user_id取模拆分到不同的数据库中

2、分片算法

可参考：https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding

精确分片算法
范围分片算法
复合分片算法
hint分片算法

3、分片策略（分片键+分片算法）

行表达式分片策略
标准分片策略
复合分片策略
hint分片策略
不分片策略

可查看源码 org.apache.shardingsphere.core.yaml.config.sharding.yamlshardingstrategyconfiguration

SpringBoot整合sharding-jdbc实现自定义分库分表的实践

三、程序实现

温馨小提示：详情可查看案例demo源码

SpringBoot整合sharding-jdbc实现自定义分库分表的实践

这里先贴出完整的application.yml配置，后面实现每一种分片策略时，放开其相应配置即可~

# sharding-jdbc配置
spring:
  shardingsphere:
    # 是否开启sql显示
    props:
      sql:
        show: true
    # ====================== ↓↓↓↓↓↓ 数据源配置 ↓↓↓↓↓↓ ======================
    datasource:
      names: ds-master-0,ds-slave-0-1,ds-slave-0-2,ds-master-1,ds-slave-1-1,ds-slave-1-2
      # ====================== ↓↓↓↓↓↓ 配置第1个主从库 ↓↓↓↓↓↓ ======================
      # 主库1
      ds-master-0:
        type: com.zaxxer.hikari.hikaridatasource
        driver-class-name: com.mysql.jdbc.driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?allowmultiqueries=true&useunicode=true&characterencoding=utf8&zerodatetimebehavior=converttonull&usessl=false # mysql在高版本需要指明是否进行ssl连接 解决则加上 &usessl=false
        username: root
        password: root
      # 主库1-从库1
      ds-slave-0-1:
        type: com.zaxxer.hikari.hikaridatasource
        driver-class-name: com.mysql.jdbc.driver
        jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowmultiqueries=true&useunicode=true&characterencoding=utf8&zerodatetimebehavior=converttonull&usessl=false # mysql在高版本需要指明是否进行ssl连接 解决则加上 &usessl=false
        username: root
        password: root
      # 主库1-从库2
      ds-slave-0-2:
        type: com.zaxxer.hikari.hikaridatasource
        driver-class-name: com.mysql.jdbc.driver
        jdbc-url: jdbc:mysql://127.0.0.1:3307/ds0?allowmultiqueries=true&useunicode=true&characterencoding=utf8&zerodatetimebehavior=converttonull&usessl=false # mysql在高版本需要指明是否进行ssl连接 解决则加上 &usessl=false
        username: root
        password: root
      # ====================== ↓↓↓↓↓↓ 配置第2个主从库 ↓↓↓↓↓↓ ======================
      # 主库2
      ds-master-1:
        type: com.zaxxer.hikari.hikaridatasource
        driver-class-name: com.mysql.jdbc.driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/ds1?allowmultiqueries=true&useunicode=true&characterencoding=utf8&zerodatetimebehavior=converttonull&usessl=false # mysql在高版本需要指明是否进行ssl连接 解决则加上 &usessl=false
        username: root
        password: root
      # 主库2-从库1
      ds-slave-1-1:
        type: com.zaxxer.hikari.hikaridatasource
        driver-class-name: com.mysql.jdbc.driver
        jdbc-url: jdbc:mysql://127.0.0.1:3307/ds1?allowmultiqueries=true&useunicode=true&characterencoding=utf8&zerodatetimebehavior=converttonull&usessl=false # mysql在高版本需要指明是否进行ssl连接 解决则加上 &usessl=false
        username: root
        password: root
      # 主库2-从库2
      ds-slave-1-2:
        type: com.zaxxer.hikari.hikaridatasource
        driver-class-name: com.mysql.jdbc.driver
        jdbc-url: jdbc:mysql://127.0.0.1:3307/ds1?allowmultiqueries=true&useunicode=true&characterencoding=utf8&zerodatetimebehavior=converttonull&usessl=false # mysql在高版本需要指明是否进行ssl连接 解决则加上 &usessl=false
        username: root
        password: root

    sharding:
      # ====================== ↓↓↓↓↓↓ 读写分离配置 ↓↓↓↓↓↓ ======================
      master-slave-rules:
        ds-master-0:
          # 主库
          masterdatasourcename: ds-master-0
          # 从库
          slavedatasourcenames:
            - ds-slave-0-1
            - ds-slave-0-2
          # 从库查询数据的负载均衡算法 目前有2种算法 round_robin（轮询）和 random（随机）
          # 算法接口 org.apache.shardingsphere.spi.masterslave.masterslaveloadbalancealgorithm
          # 实现类 randommasterslaveloadbalancealgorithm 和 roundrobinmasterslaveloadbalancealgorithm
          loadbalancealgorithmtype: round_robin
        ds-master-1:
          masterdatasourcename: ds-master-1
          slavedatasourcenames:
            - ds-slave-1-1
            - ds-slave-1-2
          loadbalancealgorithmtype: round_robin

      # ====================== ↓↓↓↓↓↓ 分库分表配置 ↓↓↓↓↓↓ ======================
      tables:
        t_user:
          actual-data-nodes: ds-master-$->{0..1}.t_user$->{0..1}

          # 配置属性可参考 org.apache.shardingsphere.core.yaml.config.sharding.yamlshardingstrategyconfiguration

          # =========== ↓↓↓↓↓↓ 行表达式分片策略 ↓↓↓↓↓↓ ===========
          # 在配置中使用 groovy 表达式，提供对 sql语句中的 = 和 in 的分片操作支持，只支持单分片健。

#           # ====== ↓↓↓↓↓↓ 分库 ↓↓↓↓↓↓ ======
#          database-strategy:
#            inline:
#              sharding-column: user_id # 添加数据分库字段(根据字段插入数据到哪个库 ex：user_id)
#              algorithm-expression: ds-master-$->{user_id % 2} # 根据user_id取模拆分到不同的库中
#           # ====== ↓↓↓↓↓↓ 分表 ↓↓↓↓↓↓ ======
#          table-strategy:
#            inline:
#              sharding-column: sex   # 添加数据分表字段(根据字段插入数据到哪个表 ex：sex)
#              algorithm-expression: t_user$->{sex % 2} # 分片算法表达式 => 根据用户性别取模拆分到不同的表中

          # =========== ↓↓↓↓↓↓ 标准分片策略 ↓↓↓↓↓↓ ===========

          # 精确分片算法 => sql在分库/分表键上执行 = 与 in 时触发计算逻辑，否则不走分库/分表，全库/全表执行。
#          database-strategy:
#            standard:
#              sharding-column: user_id # 分库用到的键
#              precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.mydbpreciseshardingalgorithm # 自定义分库算法实现类
#          table-strategy:
#            standard:
#              sharding-column: sex # 添加数据分表字段(根据字段插入数据到那个表 ex：sex)
#              precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.mytablepreciseshardingalgorithm # 自定义分表算法实现类

          # 范围分片算法 => sql在分库/分表键上执行 between and、>、<、>=、<= 时触发计算逻辑，否则不走分库/分表，全库/全表执行。
#          database-strategy:
#            standard:
#              sharding-column: user_id
#              precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mydbpreciseshardingalgorithm
#              range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mydbrangeshardingalgorithm
#          table-strategy:
#            standard:
#              sharding-column: sex
#              precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mytablepreciseshardingalgorithm
#              range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mytablerangeshardingalgorithm

          # =========== ↓↓↓↓↓↓ 复合分片策略 ↓↓↓↓↓↓ ===========
          # sql 语句中有>，>=, <=，<，=，in 和 between and 等操作符，不同的是复合分片策略支持对多个分片健操作。

#          database-strategy:
#            complex:
#              sharding-columns: user_id,sex
#              algorithm-class-name: com.zhengqing.demo.config.sharding.complex.mydbcomplexkeysshardingalgorithm
#          table-strategy:
#            complex:
#              sharding-columns: user_id,sex
#              algorithm-class-name: com.zhengqing.demo.config.sharding.complex.mytablecomplexkeysshardingalgorithm

          # =========== ↓↓↓↓↓↓ hint分片策略 ↓↓↓↓↓↓ ===========
          # 通过 hint api实现个性化配置 => 可查看 com.zhengqing.demo.service.impl.userserviceimpl.listpageforhint

          database-strategy:
            hint:
              algorithm-class-name: com.zhengqing.demo.config.sharding.hint.mydbhintshardingalgorithm
          table-strategy:
            hint:
              algorithm-class-name: com.zhengqing.demo.config.sharding.hint.mytablehintshardingalgorithm

1、行表达式分片策略

# =========== ↓↓↓↓↓↓ 行表达式分片策略 ↓↓↓↓↓↓ ===========
# 在配置中使用 groovy 表达式，提供对 sql语句中的 = 和 in 的分片操作支持，只支持单分片健。

# ====== ↓↓↓↓↓↓ 分库 ↓↓↓↓↓↓ ======
database-strategy:
  inline:
    sharding-column: user_id # 添加数据分库字段(根据字段插入数据到哪个库 ex：user_id)
    algorithm-expression: ds-master-$->{user_id % 2} # 根据user_id取模拆分到不同的库中
# ====== ↓↓↓↓↓↓ 分表 ↓↓↓↓↓↓ ======
table-strategy:
  inline:
    sharding-column: sex   # 添加数据分表字段(根据字段插入数据到哪个表 ex：sex)
    algorithm-expression: t_user$->{sex % 2} # 分片算法表达式 => 根据用户性别取模拆分到不同的表中

2、标准分片策略

a: 精确分片算法

# 精确分片算法 => sql在分库/分表键上执行 = 与 in 时触发计算逻辑，否则不走分库/分表，全库/全表执行。
database-strategy:
  standard:
    sharding-column: user_id # 分库用到的键
    precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.mydbpreciseshardingalgorithm # 自定义分库算法实现类
table-strategy:
  standard:
    sharding-column: sex # 添加数据分表字段(根据字段插入数据到那个表 ex：sex)
    precise-algorithm-class-name: com.zhengqing.demo.config.sharding.precise.mytablepreciseshardingalgorithm # 自定义分表算法实现类

@slf4j
public class mydbpreciseshardingalgorithm implements preciseshardingalgorithm<long> {

    /**
     * 分片策略
     *
     * @param dbnamelist    所有数据源
     * @param shardingvalue sql执行时传入的分片值
     * @return 数据源名称
     */
    @override
    public string dosharding(collection<string> dbnamelist, preciseshardingvalue<long> shardingvalue) {
        log.info("[mydbpreciseshardingalgorithm] sql执行时传入的分片值: [{}]", shardingvalue);
        // 根据user_id取模拆分到不同的库中
        long userid = shardingvalue.getvalue();
        for (string dbnameitem : dbnamelist) {
            if (dbnameitem.endswith(string.valueof(userid % 2))) {
                return dbnameitem;
            }
        }
        return null;
    }
}

@slf4j
public class mytablepreciseshardingalgorithm implements preciseshardingalgorithm<byte> {

    /**
     * 分片策略
     *
     * @param tablenamelist 所有表名
     * @param shardingvalue sql执行时传入的分片值
     * @return 表名
     */
    @override
    public string dosharding(collection<string> tablenamelist, preciseshardingvalue<byte> shardingvalue) {
        log.info("[mytablepreciseshardingalgorithm] sql执行时传入的分片值: [{}]", shardingvalue);
        // 根据用户性别取模拆分到不同的表中
        byte sex = shardingvalue.getvalue();
        for (string tablenameitem : tablenamelist) {
            if (tablenameitem.endswith(string.valueof(sex % 2))) {
                return tablenameitem;
            }
        }
        return null;
    }
}

b: 范围分片算法

# 范围分片算法 => sql在分库/分表键上执行 between and、>、<、>=、<= 时触发计算逻辑，否则不走分库/分表，全库/全表执行。
database-strategy:
  standard:
    sharding-column: user_id
    precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mydbpreciseshardingalgorithm
    range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mydbrangeshardingalgorithm
table-strategy:
  standard:
    sharding-column: sex
    precise-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mytablepreciseshardingalgorithm
    range-algorithm-class-name: com.zhengqing.demo.config.sharding.range.mytablerangeshardingalgorithm

@slf4j
public class mydbpreciseshardingalgorithm implements preciseshardingalgorithm<long> {

    /**
     * 分片策略
     *
     * @param dbnamelist    所有数据源
     * @param shardingvalue sql执行时传入的分片值
     * @return 数据源名称
     */
    @override
    public string dosharding(collection<string> dbnamelist, preciseshardingvalue<long> shardingvalue) {
        log.info("[mydbpreciseshardingalgorithm] sql执行时传入的分片值: [{}]", shardingvalue);
        // 根据user_id取模拆分到不同的库中
        long userid = shardingvalue.getvalue();
        for (string dbnameitem : dbnamelist) {
            if (dbnameitem.endswith(string.valueof(userid % 2))) {
                return dbnameitem;
            }
        }
        return null;
    }
}

@slf4j
public class mydbrangeshardingalgorithm implements rangeshardingalgorithm<long> {

    @override
    public collection<string> dosharding(collection<string> dbnamelist, rangeshardingvalue<long> shardingvalue) {
        log.info("[mydbrangeshardingalgorithm] shardingvalue: [{}]", shardingvalue);
        list<string> result = lists.newlinkedlist();
        int dbsize = dbnamelist.size();
        // 从sql 中获取 between 1 and 1000 的值
        // lower：1
        // upper：1000
        range<long> rangevalue = shardingvalue.getvaluerange();
        long lower = rangevalue.lowerendpoint();
        long upper = rangevalue.upperendpoint();
        // 根据范围值取偶选择库
        for (long i = lower; i <= upper; i++) {
            for (string dbnameitem : dbnamelist) {
                if (dbnameitem.endswith(string.valueof(i % 2))) {
                    result.add(dbnameitem);
                }
                if (result.size() >= dbsize) {
                    return result;
                }
            }
        }
        return result;
    }
}

@slf4j
public class mytablepreciseshardingalgorithm implements preciseshardingalgorithm<byte> {

    /**
     * 分片策略
     *
     * @param tablenamelist 所有表名
     * @param shardingvalue sql执行时传入的分片值
     * @return 表名
     */
    @override
    public string dosharding(collection<string> tablenamelist, preciseshardingvalue<byte> shardingvalue) {
        log.info("[mytablepreciseshardingalgorithm] sql执行时传入的分片值: [{}]", shardingvalue);
        // 根据用户性别取模拆分到不同的表中
        byte sex = shardingvalue.getvalue();
        for (string tablenameitem : tablenamelist) {
            if (tablenameitem.endswith(string.valueof(sex % 2))) {
                return tablenameitem;
            }
        }
        return null;
    }
}

@slf4j
public class mytablerangeshardingalgorithm implements rangeshardingalgorithm<byte> {

    @override
    public collection<string> dosharding(collection<string> tablenamelist, rangeshardingvalue<byte> shardingvalue) {
        log.info("[mytablerangeshardingalgorithm] shardingvalue: [{}]", shardingvalue);
        set<string> tablenameresultlist = new linkedhashset<>();
        range<byte> rangevalue = shardingvalue.getvaluerange();
        byte lower = rangevalue.lowerendpoint();
        byte upper = rangevalue.upperendpoint();
        // between 0 and 1
        // 根据性别值选择表
        for (string tablenameitem : tablenamelist) {
            if (tablenameitem.endswith(string.valueof(lower))
                    || tablenameitem.endswith(string.valueof(upper))) {
                tablenameresultlist.add(tablenameitem);
            }
        }
        return tablenameresultlist;
    }
}

3、复合分片策略

# =========== ↓↓↓↓↓↓ 复合分片策略 ↓↓↓↓↓↓ ===========
# sql 语句中有>，>=, <=，<，=，in 和 between and 等操作符，不同的是复合分片策略支持对多个分片健操作。

database-strategy:
  complex:
    sharding-columns: user_id,sex
    algorithm-class-name: com.zhengqing.demo.config.sharding.complex.mydbcomplexkeysshardingalgorithm
table-strategy:
  complex:
    sharding-columns: user_id,sex
    algorithm-class-name: com.zhengqing.demo.config.sharding.complex.mytablecomplexkeysshardingalgorithm

@slf4j
public class mydbcomplexkeysshardingalgorithm implements complexkeysshardingalgorithm<string> {

    @override
    public collection<string> dosharding(collection<string> dbnamelist, complexkeysshardingvalue<string> complexkeysshardingvalue) {
        log.info("[mydbcomplexkeysshardingalgorithm] complexkeysshardingvalue: [{}]", complexkeysshardingvalue);
        list<string> dbresultlist = new arraylist<>();
        int dbsize = dbnamelist.size();
        // 得到每个分片健对应的值
        // 用户id 范围查询
        range<string> rangeuserid = complexkeysshardingvalue.getcolumnnameandrangevaluesmap().get("user_id");
        // 性别
        list<string> sexvaluelist = this.getshardingvalue(complexkeysshardingvalue, "sex");
        // 对两个分片健进行逻辑操作，选择最终数据进哪一库？ todo
        for (string sex : sexvaluelist) {
            string suffix = string.valueof(long.parselong(sex) % 2);
            for (string dbnameitem : dbnamelist) {
                if (dbnameitem.endswith(suffix)) {
                    dbresultlist.add(dbnameitem);
                }
                if (dbresultlist.size() >= dbsize) {
                    return dbresultlist;
                }
            }
        }
        return dbresultlist;
    }

    private list<string> getshardingvalue(complexkeysshardingvalue<string> shardingvalues, final string key) {
        list<string> valuelist = new arraylist<>();
        map<string, collection<string>> columnnameandshardingvaluesmap = shardingvalues.getcolumnnameandshardingvaluesmap();
        if (columnnameandshardingvaluesmap.containskey(key)) {
            valuelist.addall(columnnameandshardingvaluesmap.get(key));
        }
        return valuelist;
    }
}

@slf4j
public class mytablecomplexkeysshardingalgorithm implements complexkeysshardingalgorithm<long> {

    @override
    public collection<string> dosharding(collection<string> tablenamelist, complexkeysshardingvalue<long> complexkeysshardingvalue) {
        log.info("[mytablecomplexkeysshardingalgorithm] complexkeysshardingvalue: [{}]", complexkeysshardingvalue);
        set<string> tablenameresultlist = new linkedhashset<>();
        int tablesize = tablenamelist.size();
        // 用户id 范围查询
        range<long> rangeuserid = complexkeysshardingvalue.getcolumnnameandrangevaluesmap().get("user_id");
        long lower = rangeuserid.lowerendpoint();
        long upper = rangeuserid.upperendpoint();
        // 根据user_id选择表 todo ...
        for (string tablenameitem : tablenamelist) {
            if (tablenameitem.endswith(string.valueof(lower % 2))
                    || tablenameitem.endswith(string.valueof(upper % 2))) {
                tablenameresultlist.add(tablenameitem);
            }
            if (tablenameresultlist.size() >= tablesize) {
                return tablenameresultlist;
            }
        }
        return tablenameresultlist;
    }
}

4、hint分片策略

#=========== ↓↓↓↓↓↓ hint分片策略 ↓↓↓↓↓↓ ===========
# 通过 hint api实现个性化配置 => 可查看 com.zhengqing.demo.service.impl.userserviceimpl.listpageforhint

database-strategy:
  hint:
    algorithm-class-name: com.zhengqing.demo.config.sharding.hint.mydbhintshardingalgorithm
table-strategy:
  hint:
    algorithm-class-name: com.zhengqing.demo.config.sharding.hint.mytablehintshardingalgorithm

@slf4j
public class mydbhintshardingalgorithm implements hintshardingalgorithm<integer> {

    @override
    public collection<string> dosharding(collection<string> dbnamelist, hintshardingvalue<integer> hintshardingvalue) {
        log.info("[mydbhintshardingalgorithm] hintshardingvalue: [{}]", hintshardingvalue);
        collection<string> dbresultlist = new arraylist<>();
        int dbsize = dbnamelist.size();
        for (string dbnameitem : dbnamelist) {
            for (integer shardingvalue : hintshardingvalue.getvalues()) {
                if (dbnameitem.endswith(string.valueof(shardingvalue % 2))) {
                    dbresultlist.add(dbnameitem);
                }
                if (dbresultlist.size() >= dbsize) {
                    return dbresultlist;
                }
            }
        }
        return dbresultlist;
    }
}

@slf4j
public class mytablehintshardingalgorithm implements hintshardingalgorithm<integer> {

    @override
    public collection<string> dosharding(collection<string> tablenamelist, hintshardingvalue<integer> hintshardingvalue) {
        log.info("[mytablehintshardingalgorithm] hintshardingvalue: [{}]", hintshardingvalue);
        collection<string> tableresultlist = new arraylist<>();
        int tablesize = tablenamelist.size();
        collection<integer> hintshardingvaluevaluelist = hintshardingvalue.getvalues();
        for (string tablename : tablenamelist) {
            for (integer shardingvalue : hintshardingvaluevaluelist) {
                if (tablename.endswith(string.valueof(shardingvalue % 2))) {
                    tableresultlist.add(tablename);
                }
                if (tableresultlist.size() >= tablesize) {
                    return tableresultlist;
                }
            }
        }
        return tableresultlist;
    }
}

使用时动态触发如下：

public ipage<user> listpageforhint() {
   // 清除掉上一次的规则，否则会报错
    hintmanager.clear();
    // hintmanager api 工具类实例
    hintmanager hintmanager = hintmanager.getinstance();

    // 库 => 主要是将value值传送到 mydbhintshardingalgorithm 中做逻辑分库处理
    hintmanager.adddatabaseshardingvalue("t_user", 100);
    hintmanager.adddatabaseshardingvalue("t_user", 1000);

    // 指定表的分片健 => 指定查t_user0
    hintmanager.addtableshardingvalue("t_user", 0);
//        hintmanager.addtableshardingvalue("t_user", 1);

    // 读写分离强制读主库，避免造成主从复制导致的延迟
    hintmanager.setmasterrouteonly();

    // 查询数据
    page<user> result = this.usermapper.selectpage(new page<>(1, 10),
            new lambdaquerywrapper<user>()
                    .eq(user::getsex, "0")
                    .between(user::getuserid, 1l, 1000l)
    );

    // 清除规则
    hintmanager.close();
    return result;
}

运行项目，接口文档：http://127.0.0.1/doc.html 提供了几个测试api如下

SpringBoot整合sharding-jdbc实现自定义分库分表的实践