Sharding-JDBC的读写分离:透明化读写分离所带来的影响,让使用方尽量像使用一个数据库一样使用主从数据库集群,是 ShardingSphere 读写分离模块的主要设计目标。
本篇描述 Sharding-JDBC 读写分离的相关概念,一主多从,写主读从,强制读主等。基于 Spring Boot 集成 Sharding-JDBC 实现读写分离 实践。
此系列文章都是基于 Sharding-JDBC 4.x
版本, 在写此文章时,正式发布的是 4.1.0
版本,点此 4.x 官方文档 。
读写分离 介绍 将数据库拆分为 主库 和 从库 ,主库负责处理事务性的增删改 操作,从库负责处理查询 操作,能够有效的避免由数据更新导致的行锁 ,使得整个系统的查询性能得到极大的改善。
通过一主多从 的配置方式,可以将查询请求均匀的分散到多个数据副本,能够进一步的提升系统的处理能力。
使用多主多从的方式,不但能够提升系统的吞吐量,还能够提升系统的可用性,可以达到在任何一个数据库宕机,甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。
与将数据根据分片键打散至各个数据节点的水平分片不同,读写分离则是根据SQL语义 的分析,将读操作和写操作分别路由至主库与从库。
读写分离的数据节点中的数据内容是一致的,而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用,能够更加有效的提升系统性能。
核心概念 主库 添加、更新以及删除数据操作所使用的数据库,目前仅支持单主库。
从库 查询数据操作所使用的数据库,可支持多从库。
主从同步 将主库的数据异步的同步到从库的操作。由于主从同步的异步性,从库与主库的数据会短时间内不一致。
负载均衡策略 通过负载均衡策略将查询请求路由到不同从库。
核心功能
提供一主多从的读写分离配置,可独立使用,也可配合分库分表使用。
独立使用读写分离支持SQL透传。
同一线程且同一数据库连接内,如有写入操作,以后的读操作均从主库读取,用于保证数据一致性。
基于Hint的强制主库路由。
使用规范 支持项
提供一主多从的读写分离配置,可独立使用,也可配合分库分表使用;
独立使用读写分离支持SQL透传;
同一线程且同一数据库连接内,如有写入操作,以后的读操作均从主库读取,用于保证数据一致性;
基于 Hint 的强制主库路由。
不支持项
主库和从库的数据同步;
主库和从库的数据同步延迟导致的数据不一致;
主库双写或多写;
跨主库和从库之间的事务的数据不一致。主从模型中,事务中读写均用主库。
数据一致性问题 读写分离虽然可以提升系统的吞吐量 和可用性 ,但同时也带来了数据不一致 的问题。
这包括多个主库之间的数据一致性,以及主库与从库之间的数据一致性的问题。
并且,读写分离也带来了与数据分片同样的问题,它同样会使得应用开发和运维人员对数据库的操作和运维变得更加复杂。 下图展现了将分库分表与读写分离一同使用时,应用程序与数据库集群之间的复杂拓扑关系。
使用手册 Sharding-JDBC 数据分片配置方式非常灵活方便。下方内容源自官方文档:[用户手册 > 使用手册I][https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/usage/]
下面几种示例都需要引入 Maven 依赖,目前最新的版本是 4.1.0:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 <dependency > <groupId > org.apache.shardingsphere</groupId > <artifactId > shardingsphere-jdbc-core</artifactId > <version > ${shardingsphere.version}</version > </dependency > <dependency > <groupId > org.apache.shardingsphere</groupId > <artifactId > sharding-jdbc-spring-boot-starter</artifactId > <version > ${sharding-sphere.version}</version > </dependency > <dependency > <groupId > org.apache.shardingsphere</groupId > <artifactId > sharding-jdbc-spring-namespace</artifactId > <version > ${sharding-sphere.version}</version > </dependency >
Java API 配置 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Map<String, DataSource> dataSourceMap = new HashMap<>(); BasicDataSource masterDataSource = new BasicDataSource(); masterDataSource.setDriverClassName("com.mysql.jdbc.Driver" ); masterDataSource.setUrl("jdbc:mysql://localhost:3306/ds_master" ); masterDataSource.setUsername("root" ); masterDataSource.setPassword("" ); dataSourceMap.put("ds_master" , masterDataSource); BasicDataSource slaveDataSource1 = new BasicDataSource(); slaveDataSource1.setDriverClassName("com.mysql.jdbc.Driver" ); slaveDataSource1.setUrl("jdbc:mysql://localhost:3306/ds_slave0" ); slaveDataSource1.setUsername("root" ); slaveDataSource1.setPassword("" ); dataSourceMap.put("ds_slave0" , slaveDataSource1); BasicDataSource slaveDataSource2 = new BasicDataSource(); slaveDataSource2.setDriverClassName("com.mysql.jdbc.Driver" ); slaveDataSource2.setUrl("jdbc:mysql://localhost:3306/ds_slave1" ); slaveDataSource2.setUsername("root" ); slaveDataSource2.setPassword("" ); dataSourceMap.put("ds_slave1" , slaveDataSource2); MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave" , "ds_master" , Arrays.asList("ds_slave0" , "ds_slave1" )); DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, masterSlaveRuleConfig, new Properties());
Yaml 配置 或通过Yaml方式配置,与以上配置等价:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 dataSources: ds_master: !!org.apache.commons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds_master username: root password: ds_slave0: !!org.apache.commons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds_slave0 username: root password: ds_slave1: !!org.apache.commons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds_slave1 username: root password: masterSlaveRule: name: ds_ms masterDataSourceName: ds_master slaveDataSourceNames: [ds_slave0 , ds_slave1 ] props: sql.show: true
加载 YAML 文件创建数据源
1 DataSource dataSource = YamlMasterSlaveDataSourceFactory.createDataSource(yamlFile);
使用原生JDBC
通过YamlMasterSlaveDataSourceFactory工厂和规则配置对象获取MasterSlaveDataSource,MasterSlaveDataSource实现自JDBC的标准接口DataSource。然后可通过DataSource选择使用原生JDBC开发,或者使用JPA, MyBatis等ORM工具。 以JDBC原生实现为例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 DataSource dataSource = YamlMasterSlaveDataSourceFactory.createDataSource(yamlFile); String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?" ; try ( Connection conn = dataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement(sql)) { preparedStatement.setInt(1 , 10 ); preparedStatement.setInt(2 , 1001 ); try (ResultSet rs = preparedStatement.executeQuery()) { while (rs.next()) { System.out.println(rs.getInt(1 )); System.out.println(rs.getInt(2 )); } } }
Spring Boot 配置 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 spring.shardingsphere.datasource.names =master,slave0,slave1 spring.shardingsphere.datasource.master.type =org.apache.commons.dbcp.BasicDataSource spring.shardingsphere.datasource.master.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.master.url =jdbc:mysql://localhost:3306/master spring.shardingsphere.datasource.master.username =root spring.shardingsphere.datasource.master.password =spring.shardingsphere.datasource.slave0.type =org.apache.commons.dbcp.BasicDataSource spring.shardingsphere.datasource.slave0.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave0.url =jdbc:mysql://localhost:3306/slave0 spring.shardingsphere.datasource.slave0.username =root spring.shardingsphere.datasource.slave0.password =spring.shardingsphere.datasource.slave1.type =org.apache.commons.dbcp.BasicDataSource spring.shardingsphere.datasource.slave1.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave1.url =jdbc:mysql://localhost:3306/slave1 spring.shardingsphere.datasource.slave1.username =root spring.shardingsphere.datasource.slave1.password =spring.shardingsphere.masterslave.name =ms spring.shardingsphere.masterslave.master-data-source-name =master spring.shardingsphere.masterslave.slave-data-source-names =slave0,slave1 spring.shardingsphere.props.sql.show =true
Spring 命名空间配置 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 <?xml version="1.0" encoding="UTF-8"?> <beans xmlns ="http://www.springframework.org/schema/beans" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance" xmlns:master-slave ="http://shardingsphere.apache.org/schema/shardingsphere/masterslave" xsi:schemaLocation ="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://shardingsphere.apache.org/schema/shardingsphere/masterslave http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd " > <bean id ="ds_master" class ="org.apache.commons.dbcp.BasicDataSource" destroy-method ="close" > <property name ="driverClassName" value ="com.mysql.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ds_master" /> <property name ="username" value ="root" /> <property name ="password" value ="" /> </bean > <bean id ="ds_slave0" class ="org.apache.commons.dbcp.BasicDataSource" destroy-method ="close" > <property name ="driverClassName" value ="com.mysql.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ds_slave0" /> <property name ="username" value ="root" /> <property name ="password" value ="" /> </bean > <bean id ="ds_slave1" class ="org.apache.commons.dbcp.BasicDataSource" destroy-method ="close" > <property name ="driverClassName" value ="com.mysql.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ds_slave1" /> <property name ="username" value ="root" /> <property name ="password" value ="" /> </bean > <master-slave:data-source id ="masterSlaveDataSource" master-data-source-name ="ds_master" slave-data-source-names ="ds_slave0, ds_slave1" > <master-slave:props > <prop key ="sql.show" > true</prop > <prop key ="executor.size" > 10</prop > <prop key ="foo" > bar</prop > </master-slave:props > </master-slave:data-source > </beans >
在Spring中使用DataSource
直接通过注入的方式即可使用DataSource,或者将DataSource配置在JPA、Hibernate或MyBatis中使用。
1 2 @Resource private DataSource dataSource;
更多的详细配置请参考配置手册 。
配置手册 Java API 配置 配置示例 1 2 3 4 5 6 7 8 9 10 11 12 DataSource getMasterSlaveDataSource () throws SQLException { MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave" , "ds_master" , Arrays.asList("ds_slave0" , "ds_slave1" )); return MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(), masterSlaveRuleConfig, new Properties()); } Map<String, DataSource> createDataSourceMap () { Map<String, DataSource> result = new HashMap<>(); result.put("ds_master" , DataSourceUtil.createDataSource("ds_master" )); result.put("ds_slave0" , DataSourceUtil.createDataSource("ds_slave0" )); result.put("ds_slave1" , DataSourceUtil.createDataSource("ds_slave1" )); return result; }
配置项说明 MasterSlaveDataSourceFactory :读写分离的数据源创建工厂。
名称
数据类型
说明
dataSourceMap
Map<String, DataSource>
数据源与其名称的映射
masterSlaveRuleConfig
MasterSlaveRuleConfiguration
读写分离规则
props
Properties
属性配置
MasterSlaveRuleConfiguration :读写分离规则配置对象。
名称
数据类型
说明
name
String
读写分离数据源名称
masterDataSourceName
String
主库数据源名称
slaveDataSourceNames
Collection
从库数据源名称列表
loadBalanceAlgorithm
MasterSlaveLoadBalanceAlgorithm
从库负载均衡算法
Properties :属性配置项,可以为以下属性。
名称
数据类型
说明
sql.show
boolean
是否打印SQL解析和改写日志,默认值: false
executor.size
int
用于SQL执行的工作线程数量,为零则表示无限制。默认值: 0
max.connections.size.per.query
int
每个物理数据库为每次查询分配的最大连接数量。默认值: 1
check.table.metadata.enabled
boolean
是否在启动时检查分表元数据一致性,默认值: false
Yaml 配置 配置示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 dataSources: ds_master: !!org.apache.commons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds_master username: root password: ds_slave0: !!org.apache.commons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds_slave0 username: root password: ds_slave1: !!org.apache.commons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds_slave1 username: root password: masterSlaveRule: name: ds_ms masterDataSourceName: ds_master slaveDataSourceNames: - ds_slave0 - ds_slave1 props: sql.show: true
配置项说明 1 2 3 4 5 6 7 8 9 10 11 12 dataSources: masterSlaveRule: name: masterDataSourceName: slaveDataSourceNames: - <data_source_name1> - <data_source_name2> - <data_source_name_x> loadBalanceAlgorithmType: props: <property-name>:
Spring Boot Start 配置 配置示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 spring.shardingsphere.datasource.names =master,slave0,slave1 spring.shardingsphere.datasource.master.type =org.apache.commons.dbcp.BasicDataSource spring.shardingsphere.datasource.master.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.master.url =jdbc:mysql://localhost:3306/master spring.shardingsphere.datasource.master.username =root spring.shardingsphere.datasource.master.password =spring.shardingsphere.datasource.slave0.type =org.apache.commons.dbcp.BasicDataSource spring.shardingsphere.datasource.slave0.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave0.url =jdbc:mysql://localhost:3306/slave0 spring.shardingsphere.datasource.slave0.username =root spring.shardingsphere.datasource.slave0.password =spring.shardingsphere.datasource.slave1.type =org.apache.commons.dbcp.BasicDataSource spring.shardingsphere.datasource.slave1.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave1.url =jdbc:mysql://localhost:3306/slave1 spring.shardingsphere.datasource.slave1.username =root spring.shardingsphere.datasource.slave1.password =spring.shardingsphere.masterslave.load-balance-algorithm-type =round_robin spring.shardingsphere.masterslave.name =ms spring.shardingsphere.masterslave.master-data-source-name =master spring.shardingsphere.masterslave.slave-data-source-names =slave0,slave1 spring.shardingsphere.props.sql.show =true
配置项说明 1 2 3 4 5 6 7 8 9 10 11 12 spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.master-data-source-name = #主库数据源名称 spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[0] = #从库数据源名称列表 spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[1] = #从库数据源名称列表 spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[x] = #从库数据源名称列表 spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-class-name = #从库负载均衡算法类名称。该类需实现MasterSlaveLoadBalanceAlgorithm接口且提供无参数构造器 spring.shardingsphere.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-type = #从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。若`load-balance-algorithm-class-name`存在则忽略该配置 spring.shardingsphere.props.sql.show = #是否开启SQL显示,默认值: false spring.shardingsphere.props.executor.size = #工作线程数量,默认值: CPU核数 spring.shardingsphere.props.check.table.metadata.enabled = #是否在启动时检查分表元数据一致性,默认值: false
Spring 命令空间配置 配置示例 命名空间:http://shardingsphere.apache.org/schema/shardingsphere/primary-replica-replication/primary-replica-replication-4.1.0.xsd
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 <?xml version="1.0" encoding="UTF-8"?> <beans xmlns ="http://www.springframework.org/schema/beans" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance" xmlns:context ="http://www.springframework.org/schema/context" xmlns:p ="http://www.springframework.org/schema/p" xmlns:tx ="http://www.springframework.org/schema/tx" xmlns:master-slave ="http://shardingsphere.apache.org/schema/shardingsphere/masterslave" xsi:schemaLocation ="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://shardingsphere.apache.org/schema/shardingsphere/masterslave http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd" > <context:annotation-config /> <context:component-scan base-package ="org.apache.shardingsphere.example.core.jpa" /> <bean id ="entityManagerFactory" class ="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" > <property name ="dataSource" ref ="masterSlaveDataSource" /> <property name ="jpaVendorAdapter" > <bean class ="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:database ="MYSQL" /> </property > <property name ="packagesToScan" value ="org.apache.shardingsphere.example.core.jpa.entity" /> <property name ="jpaProperties" > <props > <prop key ="hibernate.dialect" > org.hibernate.dialect.MySQLDialect</prop > <prop key ="hibernate.hbm2ddl.auto" > create</prop > <prop key ="hibernate.show_sql" > true</prop > </props > </property > </bean > <bean id ="transactionManager" class ="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref ="entityManagerFactory" /> <tx:annotation-driven /> <bean id ="ds_master" class ="org.apache.commons.dbcp.BasicDataSource" destroy-method ="close" > <property name ="driverClassName" value ="com.mysql.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ds_master" /> <property name ="username" value ="root" /> <property name ="password" value ="" /> </bean > <bean id ="ds_slave0" class ="org.apache.commons.dbcp.BasicDataSource" destroy-method ="close" > <property name ="driverClassName" value ="com.mysql.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ds_slave0" /> <property name ="username" value ="root" /> <property name ="password" value ="" /> </bean > <bean id ="ds_slave1" class ="org.apache.commons.dbcp.BasicDataSource" destroy-method ="close" > <property name ="driverClassName" value ="com.mysql.jdbc.Driver" /> <property name ="url" value ="jdbc:mysql://localhost:3306/ds_slave1" /> <property name ="username" value ="root" /> <property name ="password" value ="" /> </bean > <master-slave:load-balance-algorithm id ="randomStrategy" type ="RANDOM" /> <master-slave:data-source id ="masterSlaveDataSource" master-data-source-name ="ds_master" slave-data-source-names ="ds_slave0, ds_slave1" strategy-ref ="randomStrategy" > <master-slave:props > <prop key ="sql.show" > true</prop > <prop key ="executor.size" > 10</prop > <prop key ="foo" > bar</prop > </master-slave:props > </master-slave:data-source > </beans >
配置项说明 命名空间:http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd
<master-slave:data-source />
名称
类型
说明
id
属性
Spring Bean Id
master-data-source-name
属性
主库数据源Bean Id
slave-data-source-names
属性
从库数据源Bean Id列表,多个Bean以逗号分隔
strategy-ref
属性
从库负载均衡算法引用。 该类需实现MasterSlaveLoadBalanceAlgorithm接口
strategy-type
属性
从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。 若strategy-ref
存在则忽略该配置
props
标签
属性配置
<master-slave:props />
名称
类型
说明
sql.show
属性
是否开启SQL显示,默认值: false
executor.size
属性
工作线程数量,默认值: CPU核数
max.connections.size.per.query
属性
每个物理数据库为每次查询分配的最大连接数量。默认值: 1
check.table.metadata.enabled
属性
是否在启动时检查分表元数据一致性,默认值: false
<master-slave:load-balance-algorithm />
4.0.0-RC2 版本 添加
名称
类型
说明
id
属性
Spring Bean Id
type
属性
负载均衡算法类型,‘RANDOM’或’ROUND_ROBIN’,支持自定义拓展
props-ref
属性
负载均衡算法配置参数
读写分离实践 数据库准备 分别创建三个数据库,一个主库 master,两个从库 slave0,slave1。建库建表 SQL 如下:
创建三个数据库:一主 user_master,两从 user_slave0 和 user_slave1
1 CREATE DATABASE `user_master` CHARACTER SET utf8mb4;
修改创建数据库 SQL 语句中的 数据库名为 user_slave0 和 user_slave1 后执行,创建三个数据库。
创建表:三个数据库创建相同的表
1 2 3 4 5 6 7 8 CREATE TABLE `user_info` ( `id` bigint (20 ) NOT NULL , `real_name` varchar (100 ) DEFAULT NULL , `nick_name` varchar (50 ) DEFAULT NULL , `age` smallint (2 ) DEFAULT NULL , `address` varchar (100 ) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE= InnoDB DEFAULT CHARSET= utf8mb4
表插入数据:三个库同样的 user_info 分别插入一条不一样的数据
1 2 3 4 5 6 7 8 9 10 INSERT INTO `user_master`.`user_info` (`id`, `real_name`, `nick_name`, `age`, `address`) VALUES ('1' , '小李' , 'Xiao Li' , '21' , '深圳' );INSERT INTO `user_slave0`.`user_info` (`id`, `real_name`, `nick_name`, `age`, `address`) VALUES ('101' , '小王' , 'Wang' , '25' , '广州' );INSERT INTO `user_slave0`.`user_info` (`id`, `real_name`, `nick_name`, `age`, `address`) VALUES ('102' , '小关' , 'Guan' , '24' , '佛山' );INSERT INTO `user_slave0`.`user_info` (`id`, `real_name`, `nick_name`, `age`, `address`) VALUES ('201' , '小张' , 'Zhang' , '20' , '杭州' );INSERT INTO `user_slave1`.`user_info` (`id`, `real_name`, `nick_name`, `age`, `address`) VALUES ('202' , '小刘' , 'Liu' , '16' , '苏州' );
添加依赖
创建 Spring Boot 项目,在 pom.xml 添加依赖,本示例使用 jdbc 操作数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 <parent > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-parent</artifactId > <version > 2.1.5.RELEASE</version > <relativePath /> </parent > <groupId > com.springcloud</groupId > <artifactId > sharding-jdbc</artifactId > <version > 0.0.1-SNAPSHOT</version > <name > sharding-jdbc</name > <description > Demo project for Spring Boot</description > <properties > <java.version > 1.8</java.version > <spring-cloud.version > Greenwich.SR1</spring-cloud.version > <sharding.version > 4.0.0-RC1</sharding.version > </properties > <dependencies > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-web</artifactId > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-jdbc</artifactId > </dependency > <dependency > <groupId > mysql</groupId > <artifactId > mysql-connector-java</artifactId > <scope > runtime</scope > </dependency > <dependency > <groupId > com.alibaba</groupId > <artifactId > druid-spring-boot-starter</artifactId > <version > 1.1.10</version > </dependency > <dependency > <groupId > org.apache.shardingsphere</groupId > <artifactId > sharding-jdbc-spring-boot-starter</artifactId > <version > ${sharding.version}</version > </dependency > </dependencies >
解决创建数据源(DataSource) Bean 冲突问题
druid-spring-boot-starter 默认开启了自动配置,在 application.properties 文件中配置多数据源的话,因无法定义数据源名称而采用默认的,自动配置在创建多个数据源 Bean 时会存在冲突。
解决方案一: 使用纯 druid 包 替换 druid-spring-boot-starter 包
1 2 3 4 5 <dependency > <groupId > com.alibaba</groupId > <artifactId > druid</artifactId > <version > 1.1.12</version > </dependency >
解决方案二: 使用 druid-spring-boot-starter 包,但关闭自动配置
1 2 3 4 5 6 7 @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class) public class ShardingJdbcApplication { public static void main (String[] args) { SpringApplication.run(ShardingJdbcApplication.class, args); } }
配置数据源 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 spring.shardingsphere.datasource.names =master,slave0,slave1 spring.shardingsphere.datasource.master.type =com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.master.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.master.url =jdbc:mysql://localhost:3306/user_master spring.shardingsphere.datasource.master.username =panda spring.shardingsphere.datasource.master.password =123456 spring.shardingsphere.datasource.slave0.type =com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave0.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave0.url =jdbc:mysql://localhost:3306/user_slave0 spring.shardingsphere.datasource.slave0.username =panda spring.shardingsphere.datasource.slave0.password =123456 spring.shardingsphere.datasource.slave1.type =com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave1.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave1.url =jdbc:mysql://localhost:3306/user_slave1 spring.shardingsphere.datasource.slave1.username =panda spring.shardingsphere.datasource.slave1.password =123456 spring.shardingsphere.masterslave.name =ms spring.shardingsphere.masterslave.master-data-source-name =master spring.shardingsphere.masterslave.slave-data-source-names =slave0,slave1 spring.shardingsphere.props.sql.show =true
示例代码
实体类:UserInfo
1 2 3 4 5 6 7 8 9 10 public class UserInfo implements Serializable { private static final long serialVersionUID = 8316990185935533197L ; private Long id; private String realName; private String nickName; private int age; private String address; }
请求接口:UserInfoController
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 @RestController @RequestMapping("/userInfo") public class UserInfoController { @Autowired private UserInfoService userInfoService; @RequestMapping("/getAll") public List<UserInfo> getAll () { List<UserInfo> userInfoList = userInfoService.getAll(); return userInfoList; } @RequestMapping("/add") public int addUserInfo (Long id) { UserInfo userInfo = new UserInfo(id, "李小小" , "Li xiao xiao" , 19 , "南山" ); int rows = userInfoService.addUserInfo(userInfo); return rows; } }
服务层
服务层接口:UserInfoService
1 2 3 4 public interface UserInfoService { List<UserInfo> getAll () ; int addUserInfo (UserInfo userInfo) ; }
服务层接口实现:UserInfoServiceImpl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 @Service public class UserInfoServiceImpl implements UserInfoService { @Autowired private JdbcTemplate jdbcTemplate; @Override public List<UserInfo> getAll () { String sql = "select * from user_info" ; RowMapper<UserInfo> userInfoRowMapper = new RowMapper<UserInfo>() { @Override public UserInfo mapRow (ResultSet resultSet, int i) throws SQLException { UserInfo userInfo = new UserInfo() .setId(resultSet.getLong("id" )) .setRealName(resultSet.getString("real_name" )) .setNickName(resultSet.getString("nick_name" )) .setAge(resultSet.getInt("age" )) .setAddress(resultSet.getString("address" )); return userInfo; } }; return jdbcTemplate.query(sql, userInfoRowMapper); } @Override public int addUserInfo (UserInfo userInfo) { String sql = "insert into user_info (id,real_name,nick_name,age,address) values(?,?,?,?,?)" ; return jdbcTemplate.update(sql, userInfo.getId(), userInfo.getRealName(), userInfo.getNickName(), userInfo.getAge(), userInfo.getAddress()); } }
验证读从库
向获取数据的接口发送请求:http://localhost:8080/userInfo/getAll ,可以看到返回的是两个 从库 的数据,并且多次请求后,slave0 和 slave1 两个从库的数据交替出现。
1 2 3 4 5 6 7 8 9 [ {"id" :201 ,"realName" :"小张" ,"nickName" :"Zhang" ,"age" :20 ,"address" :"杭州" }, {"id" :202 ,"realName" :"小刘" ,"nickName" :"Liu" ,"age" :16 ,"address" :"苏州" } ] [ {"id" :101 ,"realName" :"小王" ,"nickName" :"Wang" ,"age" :25 ,"address" :"广州" }, {"id" :102 ,"realName" :"小关" ,"nickName" :"Guan" ,"age" :24 ,"address" :"佛山" } ]
查看控制台打印输出,可以看到是通过 从数据源 执行查询操作,多从库默认使用 轮询 负载均衡。
1 2 3 4 2019-06-19 11:23:14,245 INFO [o.a.s.c.r.SQLLogger->log:89] [http-nio-8080-exec-9] Rule Type: master-slave 2019-06-19 11:23:14,247 INFO [o.a.s.c.r.SQLLogger->log:89] [http-nio-8080-exec-9] SQL: select * from user_info ::: DataSources: slave0 2019-06-19 11:23:16,936 INFO [o.a.s.c.r.SQLLogger->log:89] [http-nio-8080-exec-1] Rule Type: master-slave 2019-06-19 11:23:16,937 INFO [o.a.s.c.r.SQLLogger->log:89] [http-nio-8080-exec-1] SQL: select * from user_info ::: DataSources: slave1
验证写主库 读写分离,读从库,写主库。
向新增数据接口发送请求:http://localhost:8080/userInfo/add?id=2 ,打开数据库表,可以看到数据写入到主库 。
查看控制台打印输出,可以看到是通过 主数据源 执行的插入操作
1 2 2019-06-19 14:35:23,893 INFO [o.a.s.c.r.SQLLogger->log:89] [http-nio-8080-exec-7] Rule Type: master-slave 2019-06-19 14:35:23,894 INFO [o.a.s.c.r.SQLLogger->log:89] [http-nio-8080-exec-7] SQL: insert into user_info (id,real_name,nick_name,age,address) values(?,?,?,?,?) ::: DataSources: master
Hint 强制路由主库 主从数据库的同步是需要时间的,快则几十毫秒,慢则几秒,所以查询从库的操作获取到得仍有可能不是最新的数据,这是典型的读写分离同步延时导致数据不一致的问题。
Sharding-JDBC 提供了基于 Hint 强制路由主库的功能,实现将查询操作强制路由到主库上,解决上面描述的问题。
在调用查询方法前,通过获取 HintManager 实例,设置强制路由主库,就一行代码,如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 @Override public UserInfo getById (Long id) { String sql = "select * from user_info where id = ?" ; Object[] objArr = new Object[1 ]; objArr[0 ] = id; RowMapper<UserInfo> userInfoRowMapper = new RowMapper<UserInfo>() { @Override public UserInfo mapRow (ResultSet resultSet, int i) throws SQLException { UserInfo userInfo = new UserInfo() .setId(resultSet.getLong("id" )) .setRealName(resultSet.getString("real_name" )) .setNickName(resultSet.getString("nick_name" )) .setAge(resultSet.getInt("age" )) .setAddress(resultSet.getString("address" )); return userInfo; } }; HintManager.getInstance().setMasterRouteOnly(); return jdbcTemplate.queryForObject(sql, objArr, userInfoRowMapper); }
读写分离示例源码 :https://github.com/gxing19/SpringCloud-Example/tree/master/sharding-jdbc