springboot+Sharding-JDBC实现读写分离

java1234

共 7287字,需浏览 15分钟

 ·

2020-12-13 00:49

点击上方蓝色字体,选择“标星公众号”

优质文章,第一时间送达

  作者 |  yepk

来源 |  urlify.cn/U3iIfa

66套java从入门到精通实战课程分享

技术选型

SpringBoot + hikari + Sharding-JDBC + MyBatis


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

核心jar包


            org.springframework.boot
            spring-boot-starter-web
        


        
            org.springframework.boot
            spring-boot-starter-test
            test
            
                
                    org.junit.vintage
                    junit-vintage-engine
                

            

        

        
            org.springframework.boot
            spring-boot-devtools
        


        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.1.3
        


        
            org.springframework.boot
            spring-boot-starter-jdbc
        

        
            mysql
            mysql-connector-java
            ${mysql-connector-java.version}
        


        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter
            4.0.0-RC1
        


        
            com.alibaba
            fastjson
            1.2.74
        


        
            com.baomidou
            mybatis-plus-boot-starter
            3.3.2
        


        
            com.github.pagehelper
            pagehelper-spring-boot-autoconfigure
            1.3.0
        


        
            com.github.pagehelper
            pagehelper
            5.2.0
        

        
            org.projectlombok
            lombok
        



版本控制

   
        1.8
        UTF-8
        UTF-8
        2.3.4.RELEASE
        2.0.0
        8.0.20
    


配置文件

  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 getList() {
        // 强制路由主库
        HintManager.getInstance().setMasterRouteOnly();
        return this.list();
    }

参考资料:https://www.yepk.cn/archives/springboot-sharding-jdbc-io.html
官网:http://shardingsphere.apache.org/index_zh.html
开发配置文档:https://yepk.lanzous.com/iC7twishqji






粉丝福利:Java从入门到入土学习路线图

???

?长按上方微信二维码 2 秒


感谢点赞支持下哈 

浏览 32
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报