技术选型
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>
配置文件
- 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 在执行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 的主从复制是多线程了，意味着速度会变快，但是不一定能保证百分百马上读取到，这个问题我们可以有两种方式解决：
- 业务层面妥协，是否操作完之后马上要进行读取
- 对于操作完马上要读出来的，且业务上不能妥协的，我们可以对于这类的读取直接走主库，当然Sharding-JDBC也是考虑到这个问题的存在，所以给我们提供了一个功能，可以让用户在使用的时候指定要不要走主库进行读取。在读取前使用下面的方式进行设置就可以了：
public List<UserInfo> getList() {
// 强制路由主库
HintManager.getInstance().setMasterRouteOnly();
return this.list();
}
官网：
http://shardingsphere.apache.org/index_zh.html
开发配置文档：https://yepk.lanzous.com/iC7twishqji