JeeSite 4.x

Spring Boot 最好的快速开发平台

读写分离、分库分表、分布式数据库解决方案

适应于 v4.3.0+ 版本

读写分离(JeeSite 实现)

特点:支持复杂SQL语句;支持多数据源或扩展数据源的读写分离;支持负载均衡算法自定义。

配置方法

# 数据源配置
jdbc:

  # 读写分离配置(专业版)v4.3.0
  readwriteSplitting:
    # 读库的数据源名称列表(默认数据源)
    readDataSourceNames: ds_read_01, ds_read_02
    # 负载均衡算法(ROUND_ROBIN轮询、RANDOM随机、自定义类名)
    loadBalancerAlgorithm: RANDOM

  # 多数据源名称列表,多个用逗号隔开,使用方法:@MyBatisDao(dataSourceName="ds2")
  dataSourceNames: ds_read_01, ds_read_02

  # 默认数据源的从库01
  ds_read_01:
    type: mysql
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/jeesite_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    testSql: SELECT 1
    pool:
      init: 1
      minIdle: 3
      maxActive: 20
  
  # 默认数据源的从库02
  ds_read_02:
    type: mysql
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/jeesite_test2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    testSql: SELECT 1
    pool:
      init: 1
      minIdle: 3
      maxActive: 20

扩展数据源

# 数据源配置
jdbc:

  dataSourceNames: ds2, ds2_read_01, ds2_read_02

  # 多数据源配置:ds2
  ds2:
    type: mysql
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/jeesite2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    testSql: SELECT 1

    # 其它数据源支持密码加密
    encrypt:
      username: false
      password: true

    # 其它数据源支持连接池设置
    pool:
      init: 1
      minIdle: 3
      maxActive: 20

    # 其它数据源支持读写分离(重点)
    readwriteSplitting:
      readDataSourceNames: ds2_read_01, ds2_read_02
      loadBalancerAlgorithm: RANDOM

  # 默认数据源的从库01
  ds2_read_01:
    type: mysql
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/jeesite_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    testSql: SELECT 1
    pool:
      init: 1
      minIdle: 3
      maxActive: 20
  
  # 默认数据源的从库02
  ds2_read_02:
    type: mysql
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/jeesite_test2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    testSql: SELECT 1
    pool:
      init: 1
      minIdle: 3
      maxActive: 20

ShardingSphere 实现

基于 ShardingSphere-JDBC,该框架定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库,可理解为增强版的 JDBC 驱动,完全兼容 JDBC 和各种 ORM 框架。 支持 MySQL,Oracle,SQLServer,PostgreSQL 以及任何遵循 SQL92 标准的数据库。

相关链接:

  1. 项目地址:https://github.com/apache/incubator-shardingsphere
  2. 使用帮助:https://shardingsphere.apache.org/document/current/cn/overview/

缺点:复杂SQL不支持:https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/#支持的sql

引入依赖模块

<dependency>
	<groupId>com.jeesite</groupId>
	<artifactId>jeesite-module-sharding</artifactId>
	<version>${project.parent.version}</version>
</dependency>

注意:此功能是专业版功能,请联系技术支持获取下载权限

读写分离

# 数据源配置
jdbc:

  # 开启默认数据源分片
  sharding:
    enabled: true
    
    # 分片数据源列表
    dataSourceNames: ds_read_01,ds_read_02
    
    # 默认数据源的从库01
    ds_read_01:
      type: mysql
      driver: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/jeesite_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      testSql: SELECT 1
      pool:
        init: 1
        minIdle: 3
        maxActive: 20
      
    # 默认数据源的从库02
    ds_read_02:
      type: mysql
      driver: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/jeesite_test2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      testSql: SELECT 1
      pool:
        init: 1
        minIdle: 3
        maxActive: 20
    
# 帮助文档:https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/spring-boot-starter/

spring.shardingsphere:
  
  # 是否开启
  enabled: true
  
  # 路由规则
  rules:

    # 读写分离
    readwrite-splitting:
    
      # 读写分离数据源
      data-sources:
      
        # 读写分离逻辑数据源名称(对应 jdbc.dataSourceNames,如果主库设置 default)
        default:
        
          # 读库的数据源名称(对应 jdbc.dataSourceNames,如果主库设置 default)
          write-data-source-name: default
          # 从库的数据源名称(对应 jdbc.sharding.dataSourceNames、jdbc.ds2.sharding.dataSourceNames,其中的ds2为多数据源名)
          read-data-source-names: ds_read_01,ds_read_02
            
          # 从库负载均衡算法类型
          load-balancer-name: round_robin
        
      # 负载均衡算法配置
      load-balancers:
        # 轮询算法
        round_robin:
          type: ROUND_ROBIN
        # 随机访问
        random:
          type: RANDOM

扩展数据源

上述列表举例 default 默认数据源的读写分离,jeesite 也支持扩展数据源的读写分离及分片设置

# 数据源配置
jdbc:

  dataSourceNames: ds2
  
  # 多数据源配置:ds2
  ds2:
    # Mysql 数据库配置
    type: mysql
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/jeesite_bpm?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    testSql: SELECT 1
    
    # 开启ds2数据源分片
    sharding:
      enabled: true
      
      # ds2数据源的分片列表
      dataSourceNames: ds2_read_01,ds2_read_02
      
      # ds2数据源的从库01
      ds2_read_01:
        type: mysql
        driver: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/jeesite_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
        testSql: SELECT 1
        pool:
          init: 1
          minIdle: 3
          maxActive: 20
        
      # ds2数据源的从库02
      ds2_read_02:
        type: mysql
        driver: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/jeesite_test2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
        testSql: SELECT 1
        pool:
          init: 1
          minIdle: 3
          maxActive: 20
    
# 帮助文档:https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/spring-boot-starter/

spring.shardingsphere:
  
  # 是否开启
  enabled: false
  
  # 路由规则
  rules:

    # 读写分离
    readwrite-splitting:
    
      # 读写分离数据源
      data-sources:
      
        # 读写分离逻辑数据源名称(对应 jdbc.dataSourceNames,如果主库设置 default)
        ds2:
        
          # 读库的数据源名称(对应 jdbc.dataSourceNames,如果主库设置 default)
          write-data-source-name: default
          # 从库的数据源名称(对应 jdbc.sharding.dataSourceNames、jdbc.ds2.sharding.dataSourceNames,其中的ds2为多数据源名)
          read-data-source-names: ds2_read_01,ds2_read_02
            
          # 从库负载均衡算法类型
          load-balancer-name: round_robin
        
      # 负载均衡算法配置
      load-balancers:
        # 轮询算法
        round_robin:
          type: ROUND_ROBIN
        # 随机访问
        random:
          type: RANDOM

分库分表

使用 js_sys_log 表举例,进行分表

1、系统创建两张相同的表 js_sys_log_1、js_sys_log_2

2、配置参数如下:

spring.shardingsphere:
  
  # 是否开启
  enabled: true
  
  # 路由规则
  rules:

    # 分库分表
    sharding:
      
      # 绑定表说明:https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sql/#绑定表
      binding-tables:
        - js_sys_log
      
      # 绑定表详细配置
      tables:
        # 日志表分片,分别创建2张相同表结构的表:js_sys_log_1,js_sys_log_2
        js_sys_log:
          # 表达式说明:https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/inline-expression/#语法说明
          actual-data-nodes: default.js_sys_log_$->{1..2}
          table-strategy:
            standard:
              # 分片字段指定为创建时间,分片算法取时间戳的奇数偶数进行分片
              sharding-column: create_date
              sharding-algorithm-name: log-inline
      
      # 分片算法配置:InlineShardingAlgorithm
      sharding-algorithms:
        log-inline:
          type: INLINE
          props:
            algorithm-expression: js_sys_log_$->{create_date.getTime() % 2 + 1}
      

适应于 v4.2.3 之前版本

相关链接:

  1. 项目地址:https://github.com/apache/incubator-shardingsphere
  2. 使用帮助:https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/

引入依赖模块

<dependency>
	<groupId>com.jeesite</groupId>
	<artifactId>jeesite-module-sharding</artifactId>
	<version>${project.parent.version}</version>
</dependency>

注意:此功能是专业版功能,请联系技术支持获取下载权限

读写分离配置文件

classpath:config/application.yml

# 数据源配置
jdbc:

  # Mysql 数据库配置
  type: mysql
  driver: com.mysql.cj.jdbc.Driver
  url: jdbc:mysql://192.168.0.5:3306/jeesite_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai
  username: root
  password: 123456
  testSql: SELECT 1

  # 分片数据源
  sharding:
    enabled: true
    
	# 分片数据源列表
    dataSourceNames: slave0,slave1
    
	# 从库01
    slave0:
      type: mysql
      driver: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.0.6:3306/jeesite_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=UTC
      username: root
      password: 123456
      testSql: SELECT 1
      pool:
        init: 1
        minIdle: 3
        maxActive: 20
    
	# 从库02
    slave1:
      type: mysql
      driver: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.0.7:3306/jeesite_test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=UTC
      username: root
      password: 123456
      testSql: SELECT 1
      pool:
        init: 1
        minIdle: 3
        maxActive: 20

配置文件规则

命名规则:classpath:config/sharding-数据源名.yml

该数据源名是 jdbc.dataSourceNames 的数据源名,不是分片 jdbc.sharding.dataSourceNames 的数据源。

配置参数 jdbc 直属下的数据源配置为默认数据源 default,如:sharding-default.yml,扩展数据源可参考,如:sharding-ds2.yml、sharding-ds3.yml

详细文档:https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/

classpath:config/sharding-default.yml

# 读写分离
shardingRule:  
  masterSlaveRules:
    ms_default:

	  # 主库数据源名
      masterDataSourceName: default

	  # 从库数据源名列表
      slaveDataSourceNames:
        - slave0
        - slave1

      # 从库负载均衡算法类型,可选值:ROUND_ROBIN、RANDOM
      loadBalanceAlgorithmType: ROUND_ROBIN
      
# 属性配置
props:
  # 是否开启SQL显示,默认值: false
  sql.show: true

关注 JeeSite 公众号,了解最新动态