springboot+Sharding-JDBC实现读写分离

技术选型

SpringBoot + hikari + Sharding-JDBC + MyBatis

使用Sharding-JDBC配置读写分离,优点在于数据源完全有Sharding托管,写操作自动执行master库,读操作自动执行slave库。不需要程序员在程序中关注这个实现了。

核心jar包

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>

        <!--mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector-java.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.74</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-autoconfigure</artifactId>
            <version>1.3.0</version>
        </dependency>

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

版本控制

   <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.3.4.RELEASE</spring-boot.version>
        <spring-boot-mybatis.version>2.0.0</spring-boot-mybatis.version>
        <mysql-connector-java.version>8.0.20</mysql-connector-java.version>
    </properties>

配置文件

  1. properties配置文件
# 应用服务 WEB 访问端口
server.port=8080

spring.mvc.servlet.load-on-startup=1
spring.application.name=qtv-io-dev


spring.main.allow-bean-definition-overriding=true
spring.shardingsphere.datasource.names=master,slave
## 主
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql:/xxx/io?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=xxx
## 从
spring.shardingsphere.datasource.slave.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave.jdbc-url=jdbc:mysql://xxx/io?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=xxx
## 配置
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=dataSource
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave
spring.shardingsphere.props.sql.show=true
spring.datasource.password=GuangDian@2019

#########################################
################Mybatis Config Setting ############
########################################
mybatis.mapper-locations=classpath:/mapper/*.xml
mybatis.type-aliases-package=xxx.xxx.xxx.domain
mybatis-plus.typeAliasesPackage=xxx.xxx.xxx.domain
mybatis-plus.global-config.refresh=true
mybatis-plus.global-config.db-config.column-underline=true
mybatis-plus.global-config.db-config.column-like=true
mybatis-plus.configuration.cache-enabled=false
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.mapper-locations=classpath:/mapper/*.xml


pagehelper.helperDialect=mysql
pagehelper.reasonable=false
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

### mybatis和mybatis-plus配置
mybatis:
  mapper-locations: classpath:/mapper/*.xml
  type-aliases-package: xxx.xxx.xxx.domain
mybatis-plus:
  configuration:
    cache-enabled: false
    map-underscore-to-camel-case: true
  global-config:
    db-config:
      column-like: true
      column-underline: true
    refresh: true
  mapper-locations: classpath:/mapper/*.xml
  typeAliasesPackage: xxx.xxx.xxx.domain
### 分页插件
pagehelper:
  helperDialect: mysql
  params: count=countSql
  reasonable: false
  supportMethodsArguments: true
server:
  port: 8080
### 数据库配置
spring:
  application:
    name: qtv-io-dev
  datasource:
    password: GuangDian@2019
  main:
    allow-bean-definition-overriding: true
  mvc:
    servlet:
      load-on-startup: 1
  shardingsphere:
    datasource:
      names: master,slave
      # 主库
      master:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql:/xxx/io?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
        password: xxx
        type: com.zaxxer.hikari.HikariDataSource
        username: root
      # 从库
      slave:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://xxx/io?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
        password: xxx
        type: com.zaxxer.hikari.HikariDataSource
        username: root
    masterslave:
      load-balance-algorithm-type: round_robin
      master-data-source-name: master
      name: dataSource
      slave-data-source-names: slave
    props:
      sql:
        show: true

参数解读:
  1. load-balance-algorithm-type 用于配置从库负载均衡算法类型,可选值:ROUND_ROBIN(轮询),RANDOM(随机)
  2. props.sql.show=true 在执行SQL时,会打印SQL,并显示执行库的名称

项目测试

可以看到启动了两个数据源,说明配置成功:

2020-11-26 13:54:25.872  INFO 2480 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-11-26 13:54:33.033  INFO 2480 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-11-26 13:54:33.046  INFO 2480 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2020-11-26 13:54:34.068  INFO 2480 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.

执行SQL时,会打印一下日志:

2020-11-26 13:54:37.228  INFO 2480 --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave
2020-11-26 13:54:37.229  INFO 2480 --- [           main] ShardingSphere-SQL                       : SQL: INSERT INTO test  ( product_id,title,body )  VALUES  ( ?,?,? ) ::: DataSources: master
2020-11-26 13:54:37.483  INFO 2480 --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave
2020-11-26 13:54:37.483  INFO 2480 --- [           main] ShardingSphere-SQL                       : SQL: SELECT  id,product_id,title,body,type,url  FROM test ::: DataSources: slave

可以看到新增走的是主库,查询走的是从库

相关问题

读写分离架构中经常出现,那就是读延迟的问题如何解决?

刚插入一条数据,然后马上就要去读取,这个时候有可能会读取不到?归根到底是因为主节点写入完之后数据是要复制给从节点的,读不到的原因是复制的时间比较长,也就是说数据还没复制到从节点,你就已经去从节点读取了,肯定读不到。mysql5.7 的主从复制是多线程了,意味着速度会变快,但是不一定能保证百分百马上读取到,这个问题我们可以有两种方式解决:

  1. 业务层面妥协,是否操作完之后马上要进行读取
  2. 对于操作完马上要读出来的,且业务上不能妥协的,我们可以对于这类的读取直接走主库,当然Sharding-JDBC也是考虑到这个问题的存在,所以给我们提供了一个功能,可以让用户在使用的时候指定要不要走主库进行读取。在读取前使用下面的方式进行设置就可以了:
   public List<UserInfo> getList() {
        // 强制路由主库
        HintManager.getInstance().setMasterRouteOnly();
        return this.list();
    }

官网:http://shardingsphere.apache.org/index_zh.html

开发配置文档:https://yepk.lanzous.com/iC7twishqji

# mysql   springboot   架构   sharding-jdbc  

评论

企鹅群:39438021

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×