分库分表ShardingSphere

本文旨在：通过jeecg-system-cloud-start项目集成分库分表例子，进行讲解分库分表用法

准备环境

1. 数据表：sys_log0（日志分表1），sys_log1（日志分表2）拷贝复制系统sys_log表即可
2. 数据库: jeecg-boot2(拷贝jeecg-boot即可,分库分表使用)

示例代码

示例代码在jeecg-cloud-test-shardingsphere中编写，该示例场景用于插入日志时对日志进行分表存放，分表规则是根据日志类型进行取余计算余数为0的存放到sys_log0表中，余数为1的存到sys_log1表中

单库分表

1. 在nacos中新建jeecg-sharding.yaml分表配置文件，如下所示

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://jeecg-boot-mysql:3306/jeecg-boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
    props:
      sql-show: true
    rules:
      sharding:
        binding-tables: sys_log
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123
        sharding-algorithms:
          table-classbased:
            props:
              strategy: standard
              algorithmClassName: org.jeecg.modules.test.sharding.algorithm.StandardModTableShardAlgorithm
            type: CLASS_BASED
        tables:
          sys_log:
            actual-data-nodes: ds0.sys_log$->{0..1}
            table-strategy:
              standard:
                sharding-algorithm-name: table-classbased
                sharding-column: log_type

2. 修改jeecg-system-cloud-start项目，引入新增的nacos配置 jeecg-sharding.yaml

- optional:nacos:jeecg-sharding.yaml

提醒：引入的配置文件名字要和nacos创建的文件保持一致，不然会报错。另外注意尽量nacos中的配置尽量不要有中文。

3、引入分库分表测试模块 jeecg-cloud-test-shardingsphere

4.启动成功后浏览器输入http://localhost:9999 打开接口文档如下图

如下代码批量插入10条数据，根据分配规则logType未奇数的会插入sys_log1表中，logType未偶数的会插入sys_log0表中

测试结果如下

分库分表

1. 在nacos中新建jeecg-sharding-multi.yaml 分库配置文件

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://jeecg-boot-mysql:3306/jeecg-boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: root
      ds1:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://jeecg-boot-mysql:3306/jeecg-boot2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: root
    props:
      sql-show: true
    rules:
      replica-query:
        load-balancers:
          round-robin:
            type: ROUND_ROBIN
            props:
              default: 0
        data-sources:
          prds:
            primary-data-source-name: ds0
            replica-data-source-names: ds1
            load-balancer-name: round_robin
      sharding:
        binding-tables:
          - sys_log
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123
        sharding-algorithms:
          table-classbased:
            props:
              strategy: standard
              algorithmClassName: org.jeecg.modules.test.sharding.algorithm.StandardModTableShardAlgorithm
            type: CLASS_BASED
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{operate_type % 2}
        tables:
          sys_log:
            actual-data-nodes: ds$->{0..1}.sys_log$->{0..1}
            database-strategy:
              standard:
                sharding-column: operate_type
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-algorithm-name: table-classbased
                sharding-column: log_type

2. 修改jeecg-system-cloud-start项目，引入新增的nacos配置 jeecg-sharding-multi.yaml

- optional:nacos:jeecg-sharding-multi.yaml

3.测试插入和查询接口

示例代码：

4.测试结果如下，可以看到operate_type%2==0的进入了jeecg-boot 库(ds0),operate_type%2==1的进入了jeecg-boot2库(ds1)

其他：ShardingSphere官方文档

https://shardingsphere.apache.org/document/current/cn/overview