springboot+Sharding-JDBC實現讀寫分離
點擊上方藍色字體，選擇“標星公眾號”
優(yōu)質文章，第一時間送達
? 作者?|??yepk
來源 |? urlify.cn/U3iIfa
技術選型
SpringBoot?+?hikari?+?Sharding-JDBC?+?MyBatis
使用Sharding-JDBC配置讀寫分離，優(yōu)點在于數據源完全有Sharding托管，寫操作自動執(zhí)行master庫，讀操作自動執(zhí)行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
????
配置文件
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
參數解讀：
load-balance-algorithm-type 用于配置從庫負載均衡算法類型，可選值：ROUND_ROBIN(輪詢)，RANDOM（隨機）
props.sql.show=true 在執(zhí)行SQL時，會打印SQL，并顯示執(zhí)行庫的名稱
項目測試
可以看到啟動了兩個數據源，說明配置成功:
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.
執(zhí)行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
可以看到新增走的是主庫，查詢走的是從庫
相關問題
讀寫分離架構中經常出現，那就是讀延遲的問題如何解決？
剛插入一條數據，然后馬上就要去讀取，這個時候有可能會讀取不到？歸根到底是因為主節(jié)點寫入完之后數據是要復制給從節(jié)點的，讀不到的原因是復制的時間比較長，也就是說數據還沒復制到從節(jié)點，你就已經去從節(jié)點讀取了，肯定讀不到。mysql5.7 的主從復制是多線程了，意味著速度會變快，但是不一定能保證百分百馬上讀取到，這個問題我們可以有兩種方式解決：
業(yè)務層面妥協(xié)，是否操作完之后馬上要進行讀取
對于操作完馬上要讀出來的，且業(yè)務上不能妥協(xié)的，我們可以對于這類的讀取直接走主庫，當然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
開發(fā)配置文檔：https://yepk.lanzous.com/iC7twishqji
粉絲福利：Java從入門到入土學習路線圖
???
?長按上方微信二維碼?2 秒
感謝點贊支持下哈?
評論
圖片
表情