连云港网站建设推广,环保设备公司网站模板,如何做旅游计划的网站,网站建设知识点的总结背景
面对日益增加的系统访问量#xff0c;数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说#xff0c;将数据库拆分为主库和从库#xff0c;主库负责处理事务性的增删改操作#xff0c;从库负责处理查询操作#xff0c;能够…背景
面对日益增加的系统访问量数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说将数据库拆分为主库和从库主库负责处理事务性的增删改操作从库负责处理查询操作能够有效的避免由数据更新导致的行锁使得整个系统的查询性能得到极大的改善。
通过一主多从的配置方式可以将查询请求均匀的分散到多个数据副本能够进一步的提升系统的处理能力。 使用多主多从的方式不但能够提升系统的吞吐量还能够提升系统的可用性可以达到在任何一个数据库宕机甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。
与将数据根据分片键打散至各个数据节点的水平分片不同读写分离则是根据 SQL 语义的分析将读操作和写操作分别路由至主库与从库。 读写分离的数据节点中的数据内容是一致的而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用能够更加有效的提升系统性能。
许多系统通过采用主从数据库架构的配置来提高整个系统的吞吐量但是主从的配置也给业务的使用带来了一定的复杂性。接入 ShardingSphere可以利用读写分离功能管理主从数据库实现透明化的读写分离功能让用户像使用一个数据库一样使用主从架构的数据库。
1、创建SpringBoot程序
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/spring-boot/
1.1、创建项目
项目类型Spring Initializr
SpringBoot脚手架http://start.aliyun.com
项目名sharding-jdbc-demo SpringBoot版本2.3.12.RELEASE 1.2、添加依赖 propertiesjava.version1.8/java.versionproject.build.sourceEncodingUTF-8/project.build.sourceEncodingproject.reporting.outputEncodingUTF-8/project.reporting.outputEncodingspring-boot.version2.3.12.RELEASE/spring-boot.version/propertiesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core/artifactIdversion5.4.0/version/dependencydependencygroupIdorg.yaml/groupIdartifactIdsnakeyaml/artifactIdversion1.33/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdscoperuntime/scope/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.3.1/version/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scopeexclusionsexclusiongroupIdorg.junit.vintage/groupIdartifactIdjunit-vintage-engine/artifactId/exclusion/exclusions/dependency/dependencies升级到 ShardingSphere 5.3.0 或更高的版本以Spring Boot项目举例原有相关的依赖将会失效
dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactIdversion${shardingsphere.version}/version
/dependency调整为
dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core/artifactIdversion${shardingsphere.version}/version
/dependency1.3、创建实体类
package com.dongguo.shardingjdbc.entity;import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;TableName(t_user)
Data
public class User {TableId(type IdType.AUTO)private Long id;private String uname;
}1.4、创建Mapper
package com.dongguo.shardingjdbc.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dongguo.shardingjdbc.entity.User;
import org.apache.ibatis.annotations.Mapper;Mapper
public interface UserMapper extends BaseMapperUser {
}1.5、配置读写分离
application.yml
# 应用名称
spring:application:name: sharding-jdbc-demoprofiles:active: devdatasource:# 配置 DataSource Driverdriver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver# 指定 YAML 配置文件url: jdbc:shardingsphere:classpath:shardingsphere-readwrite.yamlshardingsphere-readwrite.yaml
#模式配置
mode:type: Standalone
# 数据源配置
dataSources:master:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3306/db_userusername: rootpassword: 123456slave1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3307/db_userusername: rootpassword: 123456slave2:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3308/db_userusername: rootpassword: 123456
#规则配置
rules:
- !READWRITE_SPLITTINGdataSources:myds:writeDataSourceName: masterreadDataSourceNames:- slave1- slave2loadBalancerName: alg_round#算法配置loadBalancers:alg_round:type: ROUND_ROBINalg_random:type: RANDOMalg_weight:type: WEIGHTprops:slave1: 1slave2: 2
#属性配置
props:sql-show: true从ShardingSphere 5.x版本开始支持使用jdbc:shardingsphere:作为DataSource URL的前缀来引入ShardingSphere的配置。这个特性被称为Spring ShardingSphere的可插拔数据源特性可以通过可插拔的方式将ShardingSphere数据源无缝集成到Spring应用中从而实现自动化和透明化的分库分表。
模式配置
mode (?): # 不配置则默认单机模式type: # 运行模式类型。可选配置Standalone、Clusterrepository (?): # 持久化仓库配置ShardingSphere 5.x版本开始支持3种模式包括Memory内存、Standalone 独立和Cluster集群模式
ShardingSphere 5.4 取消了 memory 模式配置原因是在该模式下数据全部存储在内存中当数据量过大时会导致内存不足影响系统性能。为了解决这个问题ShardingSphere 推荐使用基于数据库的标准模式配置来代替 memory 模式这样可以避免内存不足的问题同时也可以更好地保证数据的安全性和一致性。如果您需要在 ShardingSphere 5.4 中使用 memory 模式配置可以考虑使用之前的版本或者自定义实现。
数据源配置
dataSources: # 数据源配置可配置多个 data-source-namedata_source_name: # 数据源名称dataSourceClassName: # 数据源完整类名driverClassName: # 数据库驱动类名以数据库连接池自身配置为准jdbcUrl: # 数据库 URL 连接以数据库连接池自身配置为准username: # 数据库用户名以数据库连接池自身配置为准password: # 数据库密码以数据库连接池自身配置为准# ... 数据库连接池的其它属性
示例的数据库驱动为 MySQL连接池为 HikariCP可以更换为其他数据库驱动和连接池。当使用 ShardingSphere-JDBC 时JDBC 池的属性名取决于各自 JDBC 池自己的定义并不由 ShardingSphere 硬定义相关的处理可以参考类org.apache.shardingsphere.infra.datasource.pool.creator.DataSourcePoolCreator。例如对于 Alibaba Druid 1.2.9 而言使用 url 代替如下示例中的 jdbcUrl 是预期行为。
如
datasource:# 主数据源master:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.122.150:3306/db_userusername: rootpassword: 123456# 从数据源slave:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://192.168.122.150:3306/db_userusername: rootpassword: 123456规则配置-读写分离
rules:
- !READWRITE_SPLITTINGdataSources:data_source_name (): # 读写分离逻辑数据源名称writeDataSourceName: # 写库数据源名称readDataSourceNames: # 读库数据源名称多个从数据源用逗号分隔transactionalReadQueryStrategy (?): # 事务内读请求的路由策略可选值PRIMARY路由至主库、FIXED同一事务内路由至固定数据源、DYNAMIC同一事务内路由至非固定数据源。默认值DYNAMICloadBalancerName: # 负载均衡算法名称# 负载均衡算法配置loadBalancers:load_balancer_name (): # 负载均衡算法名称type: # 负载均衡算法类型props: # 负载均衡算法属性配置# ...注意这里官网给出的参数解释中write_data_source_name、read_data_source_names有误
配置项使用了_分隔命名但是会运行会显示解析错误因此需要改为驼峰命名 在负载均衡算法的讲解中的参数是正确的
https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/load-balance/ 算法配置-读写分离负载均衡算法
loadBalancers:# loadBalancerName 由用户指定需要和读写分离规则中的 loadBalancerName 属性一致loadBalancerName:# type 和 props请参考读写分离负载均衡内置算法https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/load-balance/type: xxxprops:xxx: xxx
配置标识详细 说明全限定类名ROUND_ROBIN基于轮询的读库负载均衡算法org.apache.shardingsphere.readwritesplitting.algorithm.loadbalance.RoundRobinReadQueryLoadBalanceAlgorithmRANDOM基于随机的读库负载均衡算法org.apache.shardingsphere.readwritesplitting.algorithm.loadbalance.RandomReadQueryLoadBalanceAlgorithmWEIGHT基于权重的读库负载均衡算法org.apache.shardingsphere.readwritesplitting.algorithm.loadbalance.WeightReadQueryLoadBalanceAlgorithm
通用配置-属性配置
props:sql-show: true名称数据类型说明默认值sql-showboolean是否在日志中打印 SQL 打印 SQL 可以帮助开发者快速定位系统问题。日志内容包含逻辑 SQL真实 SQL 和 SQL 解析结果。 如果开启配置日志将使用 Topic ShardingSphere-SQL日志级别是 INFOfalse
yaml配置参考
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/
2、测试
2.1、读写分离测试
写入
package com.dongguo.shardingjdbc;import com.dongguo.shardingjdbc.entity.User;
import com.dongguo.shardingjdbc.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;SpringBootTest
class ReadwriteTest {Autowiredprivate UserMapper userMapper;/*** 写入数据的测试*/Testpublic void testInsert(){User user new User();user.setUname(张三丰);userMapper.insert(user);}
}执行这个测试类,运行结果;
2023-08-09 13:12:51.670 INFO 38604 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( uname ) VALUES ( ? )
2023-08-09 13:12:51.670 INFO 38604 --- [ main] ShardingSphere-SQL : Actual SQL: master ::: INSERT INTO t_user ( uname ) VALUES ( ? ) ::: [张三丰]查看数据库
master slave1 slave2 查询 Testpublic void testSelect(){User user userMapper.selectById(1);System.out.println(user);}运行结果
2023-08-09 21:43:44.232 INFO 27836 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user WHERE id?
2023-08-09 21:43:44.232 INFO 27836 --- [ main] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user WHERE id? ::: [1]
User(id1, unamezhang3)这里可以看到在master写入在slave读取
为了更加确认我们可以在同一个方法中执行写入和读取操作 Testpublic void testReadwrite(){User user new User();user.setUname(张三);userMapper.insert(user);ListUser userList userMapper.selectList(null);userList.forEach(System.out::println);}运行结果
2023-08-09 22:04:09.897 INFO 37108 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( uname ) VALUES ( ? )
2023-08-09 22:04:09.897 INFO 37108 --- [ main] ShardingSphere-SQL : Actual SQL: master ::: INSERT INTO t_user ( uname ) VALUES ( ? ) ::: [张三]
2023-08-09 22:04:10.032 INFO 37108 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-09 22:04:10.032 INFO 37108 --- [ main] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)
User(id6, uname张三)master负责写slave负责读实现读写分离。
报错
java.lang.NoSuchMethodError: org.yaml.snakeyaml.LoaderOptions.setCodePointLimit(I)Vshardingsphere5.4.0与snakeyaml1.26不兼容 尝试升级版本正常启动 dependencygroupIdorg.yaml/groupIdartifactIdsnakeyaml/artifactIdversion1.33/version/dependency具体原因
https://github.com/apache/shardingsphere/issues/21476 2.2、事务测试
为了保证主从库间的事务一致性避免跨服务的分布式事务ShardingSphere-JDBC的主从模型中事务中的数据读写均用主库。 不添加Transactionalinsert对主库操作select对从库操作 shardingsphere5.4.0版本读写分离添加Transactional由规则transactionalReadQueryStrategy 决定 事务内读请求的路由策略可选值PRIMARY路由至主库、FIXED同一事务内路由至固定数据源、DYNAMIC同一事务内路由至非固定数据源。默认值 **注意**在JUnit环境下的Transactional注解默认情况下就会对事务进行回滚即使在没加注解Rollback也会对事务回滚
在该测试方法上加上Transactional开启事务 TransactionalTestpublic void testReadwrite(){User user new User();user.setUname(张三);userMapper.insert(user);ListUser userList userMapper.selectList(null);userList.forEach(System.out::println);}运行结果
2023-08-09 22:09:26.323 INFO 12836 --- [ main] o.s.t.c.transaction.TransactionContext : Began transaction (1) for test context [DefaultTestContext33bc72d1 testClass ReadwriteTest, testInstance com.dongguo.shardingjdbc.ReadwriteTest2917abdb, testMethod testReadwriteReadwriteTest, testException [null], mergedContextConfiguration [WebMergedContextConfiguration1a75e76a testClass ReadwriteTest, locations {}, classes {class com.dongguo.shardingjdbc.ShardingJdbcDemoApplication}, contextInitializerClasses [], activeProfiles {}, propertySourceLocations {}, propertySourceProperties {org.springframework.boot.test.context.SpringBootTestContextBootstrappertrue}, contextCustomizers set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer3ce1e309, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer74e52303, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer1aa7ecca, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer1d2adfbe, org.springframework.boot.test.context.SpringBootTestArgs1, org.springframework.boot.test.context.SpringBootTestWebEnvironment69ea3742], resourceBasePath src/main/webapp, contextLoader org.springframework.boot.test.context.SpringBootContextLoader, parent [null]], attributes map[org.springframework.test.context.web.ServletTestExecutionListener.activateListener - true, org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder - true, org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder - true]]; transaction manager [org.springframework.jdbc.datasource.DataSourceTransactionManager4f6fe058]; rollback [true]
2023-08-09 22:09:27.093 INFO 12836 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( uname ) VALUES ( ? )
2023-08-09 22:09:27.093 INFO 12836 --- [ main] ShardingSphere-SQL : Actual SQL: master ::: INSERT INTO t_user ( uname ) VALUES ( ? ) ::: [张三]
2023-08-09 22:09:27.267 INFO 12836 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-09 22:09:27.267 INFO 12836 --- [ main] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)
User(id6, uname张三)
2023-08-09 22:09:27.335 INFO 12836 --- [ main] o.s.t.c.transaction.TransactionContext : Rolled back transaction for test: [DefaultTestContext33bc72d1 testClass ReadwriteTest, testInstance com.dongguo.shardingjdbc.ReadwriteTest2917abdb, testMethod testReadwriteReadwriteTest, testException [null], mergedContextConfiguration [WebMergedContextConfiguration1a75e76a testClass ReadwriteTest, locations {}, classes {class com.dongguo.shardingjdbc.ShardingJdbcDemoApplication}, contextInitializerClasses [], activeProfiles {}, propertySourceLocations {}, propertySourceProperties {org.springframework.boot.test.context.SpringBootTestContextBootstrappertrue}, contextCustomizers set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer3ce1e309, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer74e52303, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer1aa7ecca, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer1d2adfbe, org.springframework.boot.test.context.SpringBootTestArgs1, org.springframework.boot.test.context.SpringBootTestWebEnvironment69ea3742], resourceBasePath src/main/webapp, contextLoader org.springframework.boot.test.context.SpringBootContextLoader, parent [null]], attributes map[org.springframework.test.context.web.ServletTestExecutionListener.activateListener - true, org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder - true, org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder - true]]在配置中加入transactionalReadQueryStrategy: PRIMARY实现事务内读请求均路由至主库
shardingsphere-readwrite.yaml
# 数据源配置
dataSources:master:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3306/db_userusername: rootpassword: 123456slave1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3307/db_userusername: rootpassword: 123456slave2:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.122.150:3308/db_userusername: rootpassword: 123456
#规则配置
rules:
- !READWRITE_SPLITTINGdataSources:myds:writeDataSourceName: masterreadDataSourceNames:- slave1- slave2transactionalReadQueryStrategy: PRIMARYloadBalancerName: alg_round#算法配置loadBalancers:alg_round:type: ROUND_ROBINalg_random:type: RANDOMalg_weight:type: WEIGHTprops:slave1: 1slave2: 2
#属性配置
props:sql-show: true运行结果
2023-08-09 22:47:33.631 INFO 28972 --- [ main] o.s.t.c.transaction.TransactionContext : Began transaction (1) for test context [DefaultTestContext33bc72d1 testClass ReadwriteTest, testInstance com.dongguo.shardingjdbc.ReadwriteTest14eb2f67, testMethod testTransactionalReadwriteTest, testException [null], mergedContextConfiguration [WebMergedContextConfiguration1a75e76a testClass ReadwriteTest, locations {}, classes {class com.dongguo.shardingjdbc.ShardingJdbcDemoApplication}, contextInitializerClasses [], activeProfiles {}, propertySourceLocations {}, propertySourceProperties {org.springframework.boot.test.context.SpringBootTestContextBootstrappertrue}, contextCustomizers set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer3ce1e309, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer74e52303, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer1aa7ecca, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer1d2adfbe, org.springframework.boot.test.context.SpringBootTestArgs1, org.springframework.boot.test.context.SpringBootTestWebEnvironment69ea3742], resourceBasePath src/main/webapp, contextLoader org.springframework.boot.test.context.SpringBootContextLoader, parent [null]], attributes map[org.springframework.test.context.web.ServletTestExecutionListener.activateListener - true, org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder - true, org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder - true]]; transaction manager [org.springframework.jdbc.datasource.DataSourceTransactionManager7abbd5c1]; rollback [true]
2023-08-09 22:47:34.355 INFO 28972 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( uname ) VALUES ( ? )
2023-08-09 22:47:34.355 INFO 28972 --- [ main] ShardingSphere-SQL : Actual SQL: master ::: INSERT INTO t_user ( uname ) VALUES ( ? ) ::: [张三]
2023-08-09 22:47:34.530 INFO 28972 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-09 22:47:34.530 INFO 28972 --- [ main] ShardingSphere-SQL : Actual SQL: master ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname884300ef58d6)
User(id3, uname张三丰)
User(id14, uname张三)
2023-08-09 22:47:34.572 INFO 28972 --- [ main] o.s.t.c.transaction.TransactionContext : Rolled back transaction for test: [DefaultTestContext33bc72d1 testClass ReadwriteTest, testInstance com.dongguo.shardingjdbc.ReadwriteTest14eb2f67, testMethod testTransactionalReadwriteTest, testException [null], mergedContextConfiguration [WebMergedContextConfiguration1a75e76a testClass ReadwriteTest, locations {}, classes {class com.dongguo.shardingjdbc.ShardingJdbcDemoApplication}, contextInitializerClasses [], activeProfiles {}, propertySourceLocations {}, propertySourceProperties {org.springframework.boot.test.context.SpringBootTestContextBootstrappertrue}, contextCustomizers set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer3ce1e309, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer74e52303, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer1aa7ecca, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer1d2adfbe, org.springframework.boot.test.context.SpringBootTestArgs1, org.springframework.boot.test.context.SpringBootTestWebEnvironment69ea3742], resourceBasePath src/main/webapp, contextLoader org.springframework.boot.test.context.SpringBootContextLoader, parent [null]], attributes map[org.springframework.test.context.web.ServletTestExecutionListener.activateListener - true, org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder - true, org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder - true]]2.3、负载均衡测试
规则配置中负载均衡算法配置如果没有指定loadBalancerName则使用默认的负载均衡算法 ROUND_ROBIN。
这里项目中shardingsphere.yaml中已经配置loadBalancerName: alg_roundalg_round对应的就是轮询ROUND_ROBIN
轮询算法 Testpublic void testLoadBalancer(){for (long i 1L; i 3L; i) {User user userMapper.selectById(i);System.out.println(user);}}运行
2023-08-09 23:00:05.803 INFO 17756 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user WHERE id?
2023-08-09 23:00:05.803 INFO 17756 --- [ main] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user WHERE id? ::: [1]
User(id1, unamezhang3)
2023-08-09 23:00:05.880 INFO 17756 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user WHERE id?
2023-08-09 23:00:05.880 INFO 17756 --- [ main] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user WHERE id? ::: [2]
User(id2, unamee981b9526f35)
2023-08-09 23:00:05.884 INFO 17756 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user WHERE id?
2023-08-09 23:00:05.884 INFO 17756 --- [ main] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user WHERE id? ::: [3]
User(id3, uname张三丰)也可以在web请求中测试负载均衡
package com.dongguo.shardingjdbc.controller;import com.dongguo.shardingjdbc.entity.User;
import com.dongguo.shardingjdbc.mapper.UserMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** author dongguo* date 2023/8/9* description:*/
RestController
RequestMapping(/user)
Api(tags 用戶管理)
public class UserController {Autowiredprivate UserMapper userMapper;/*** 测试负载均衡策略*/ApiOperation(获取用户列表,测试负载均衡策略)GetMapping(/selectAll)public void selectAll(){ListUser users userMapper.selectList(null);users.forEach(System.out::println);}
}这里我们用swagger去访问 第一次访问
2023-08-10 19:42:29.926 INFO 18296 --- [nio-8080-exec-7] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 19:42:29.927 INFO 18296 --- [nio-8080-exec-7] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)第二次访问
2023-08-10 19:43:01.538 INFO 18296 --- [nio-8080-exec-8] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 19:43:01.538 INFO 18296 --- [nio-8080-exec-8] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)第三次访问
2023-08-10 19:43:19.598 INFO 18296 --- [nio-8080-exec-9] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 19:43:19.598 INFO 18296 --- [nio-8080-exec-9] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)第四次访问
2023-08-10 19:43:31.283 INFO 18296 --- [io-8080-exec-10] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 19:43:31.283 INFO 18296 --- [io-8080-exec-10] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)随机算法
只需要在shardingsphere-readwrite.yaml中的规则配置修改为loadBalancerName: alg_random
第一次访问
2023-08-10 22:12:04.116 INFO 15908 --- [nio-8080-exec-9] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:12:04.116 INFO 15908 --- [nio-8080-exec-9] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)第二次访问
2023-08-10 22:12:04.738 INFO 15908 --- [io-8080-exec-10] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:12:04.738 INFO 15908 --- [io-8080-exec-10] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)第三次访问
2023-08-10 22:12:05.366 INFO 15908 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:12:05.366 INFO 15908 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)第四次访问
2023-08-10 22:12:05.931 INFO 15908 --- [nio-8080-exec-2] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:12:05.932 INFO 15908 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)权重算法
shardingsphere-readwrite.yaml中算法配置slave1权重为1slave2权重为2 alg_weight:type: WEIGHTprops:slave1: 1slave2: 2规则配置修改为loadBalancerName: alg_weight共访问九次其中slave1处理请求3次slave2处理请求6次正好1:2.
2023-08-10 22:21:24.846 INFO 18216 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:24.846 INFO 18216 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)
2023-08-10 22:21:25.498 INFO 18216 --- [nio-8080-exec-2] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:25.498 INFO 18216 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)
2023-08-10 22:21:26.203 INFO 18216 --- [nio-8080-exec-3] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:26.203 INFO 18216 --- [nio-8080-exec-3] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)
2023-08-10 22:21:27.147 INFO 18216 --- [nio-8080-exec-4] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:27.147 INFO 18216 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)
2023-08-10 22:21:27.891 INFO 18216 --- [nio-8080-exec-5] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:27.892 INFO 18216 --- [nio-8080-exec-5] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)
2023-08-10 22:21:28.563 INFO 18216 --- [nio-8080-exec-6] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:28.563 INFO 18216 --- [nio-8080-exec-6] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)
2023-08-10 22:21:29.195 INFO 18216 --- [nio-8080-exec-7] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:29.196 INFO 18216 --- [nio-8080-exec-7] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)
2023-08-10 22:21:29.787 INFO 18216 --- [nio-8080-exec-8] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:29.787 INFO 18216 --- [nio-8080-exec-8] ShardingSphere-SQL : Actual SQL: slave2 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, unamee981b9526f35)
User(id3, uname张三丰)
2023-08-10 22:21:30.562 INFO 18216 --- [nio-8080-exec-9] ShardingSphere-SQL : Logic SQL: SELECT id,uname FROM t_user
2023-08-10 22:21:30.563 INFO 18216 --- [nio-8080-exec-9] ShardingSphere-SQL : Actual SQL: slave1 ::: SELECT id,uname FROM t_user
User(id1, unamezhang3)
User(id2, uname75a5627e4b69)
User(id3, uname张三丰)整合swagger的部分就不写出来了可以查看完整代码传送门
https://github.com/dongguo4812/shardingsphere-study/tree/master/sharding-jdbc-demo
