前面我们介绍的了Sharding-jdbc的简介和对比其他的分库分表,接下来我们写demo,介绍SpringBoot使用Sharding-JDBC进行读写分离。话不多说,直接写代码。
准备
SpringBoot 2.1.12
Sharding-JDBC 4.0.0
Mybatis 3.x
Mysql 8.0
lombok(暂时没使用)
本文场景介绍 主从两个库:
POM文件 pom文件引入如下相关依赖:
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 <?xml version="1.0" encoding="UTF-8"?> <project xmlns ="http://maven.apache.org/POM/4.0.0" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation ="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd" > <modelVersion > 4.0.0</modelVersion > <parent > <groupId > cn.cicoding</groupId > <artifactId > shardingsphere-example</artifactId > <version > 1.0-SNAPSHOT</version > </parent > <groupId > cn.cicoding</groupId > <artifactId > sharding-jdbc-read-write</artifactId > <version > 0.0.1-SNAPSHOT</version > <name > sharding-jdbc-read-write</name > <description > Demo project for Spring Boot</description > <properties > <java.version > 1.8</java.version > </properties > <dependencies > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-web</artifactId > </dependency > <dependency > <groupId > org.mybatis.spring.boot</groupId > <artifactId > mybatis-spring-boot-starter</artifactId > <version > 2.0.1</version > </dependency > <dependency > <groupId > mysql</groupId > <artifactId > mysql-connector-java</artifactId > </dependency > <dependency > <groupId > org.projectlombok</groupId > <artifactId > lombok</artifactId > <optional > true</optional > </dependency > <dependency > <groupId > org.apache.shardingsphere</groupId > <artifactId > sharding-jdbc-spring-boot-starter</artifactId > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-test</artifactId > <scope > test</scope > </dependency > </dependencies > <build > <plugins > <plugin > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-maven-plugin</artifactId > </plugin > </plugins > </build > </project >
相关代码实现 Controller代码 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 package cn.cicoding.controller;import cn.cicoding.model.User;import cn.cicoding.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RestController;@RestController public class UserController { @Autowired private UserService userService; @GetMapping ("/users" ) public Object list () { return userService.list(); } @GetMapping ("/add" ) public Object add () { User user = new User(); user.setId(100L ); user.setCity("深圳" ); user.setName("李四" ); return userService.add(user); } }
Service代码 1 2 3 4 5 6 7 8 9 10 11 12 13 package cn.cicoding.service;import cn.cicoding.model.User;import java.util.List;public interface UserService { List<User> list () ; Long add (User user) ; }
ServiceImpl代码 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 package cn.cicoding.service;import java.util.List;import cn.cicoding.model.User;import cn.cicoding.dao.UserRepository;import org.apache.shardingsphere.api.hint.HintManager;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;@Service public class UserServiceImpl implements UserService { @Autowired private UserRepository userRepository; public List<User> list () { return userRepository.list(); } public Long add (User user) { return userRepository.addUser(user); } }
Dao代码 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 package cn.cicoding.dao;import java.util.List;import cn.cicoding.model.User;import org.apache.ibatis.annotations.Mapper;@Mapper public interface UserRepository { Long addUser (User user) ; List<User> list () ; }
Mapper.xml代码实现 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 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace ="cn.cicoding.dao.UserRepository" > <resultMap id ="baseResultMap" type ="cn.cicoding.model.User" > <result column ="id" property ="id" jdbcType ="INTEGER" /> <result column ="city" property ="city" jdbcType ="VARCHAR" /> <result column ="name" property ="name" jdbcType ="VARCHAR" /> </resultMap > <insert id ="addUser" > INSERT INTO user ( id, city, name ) VALUES ( #{id,jdbcType=INTEGER}, #{city,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR} ) </insert > <select id ="list" resultMap ="baseResultMap" > SELECT u.* FROM user u </select > </mapper >
实体类 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 package cn.cicoding.model;import java.io.Serializable;public class User implements Serializable { private static final long serialVersionUID = -1205226416664488559L ; private Long id; private String city = "" ; private String name = "" ; public Long getId () { return id; } public void setId (Long id) { this .id = id; } public String getCity () { return city; } public void setCity (String city) { this .city = city; } public String getName () { return name; } public void setName (String name) { this .name = name; } }
到这我们完成了基本的代码编写,由于sharding-jdbc是jar包,我们来看主要的配置信息
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 #使用取模的方式来实现表分片 server.port=8084 # mybatis对应的映射文件路径 mybatis.mapper-locations=classpath:mapper/*.xml # mybatis对应的实体类 mybatis.type-aliases-package=cn.cicoding.model 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://localhost:3309/sharding-jdbc-read-write_0?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC spring.shardingsphere.datasource.master.username=root spring.shardingsphere.datasource.master.password=root # 从数据源 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://localhost:3309/sharding-jdbc-read-write_1?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC spring.shardingsphere.datasource.slave.username=root spring.shardingsphere.datasource.slave.password=root # 读写分离配置 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 # 显示SQL spring.shardingsphere.props.sql.show=true
启动类 1 2 3 4 5 6 7 8 9 10 11 12 13 package cn.cicoding;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication public class ShardingJdbcReadWriteApplication { public static void main (String[] args) { SpringApplication.run(ShardingJdbcReadWriteApplication.class, args); } }
测试演示 启动启动类,访问http://localhost:8084/add
查看控制台
1 2 3 4 5 6 7 8 9 2020-02-06 12:13:02.445 INFO 22628 --- [nio-8084-exec-6] ShardingSphere-SQL : Rule Type: master-slave 2020-02-06 12:13:02.445 INFO 22628 --- [nio-8084-exec-6] ShardingSphere-SQL : SQL: INSERT INTO user ( id, city, name ) VALUES ( ?, ?, ? ) ::: DataSources: master
我们看到DataSources是master节点主库
我们再次访问http://localhost:8084/users
查看控制台日志
1 2 2020-02-06 12:13:59.848 INFO 22628 --- [nio-8084-exec-8] ShardingSphere-SQL : Rule Type: master-slave 2020-02-06 12:13:59.848 INFO 22628 --- [nio-8084-exec-8] ShardingSphere-SQL : SQL: SELECT u.* FROM user u ::: DataSources: slave
我们看到DataSources是slave节点从库
到此我们就实现了sharding-jdbc主从读写分离实现,更多配置请参考此处 !
源码分析 我们看到读写分离配置
spring.shardingsphere.masterslave.load-balance-algorithm-type 源码位置:
https://github.com/apache/incubator-shardingsphere/blob/4.0.0/sharding-core/sharding-core-api/src/main/java/org/apache/shardingsphere/api/config/masterslave/MasterSlaveRuleConfiguration.java
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 package org.apache.shardingsphere.api.config.masterslave;import com.google.common.base.Preconditions;import com.google.common.base.Strings;import lombok.Getter;import org.apache.shardingsphere.api.config.RuleConfiguration;import java.util.List;@Getter public class MasterSlaveRuleConfiguration implements RuleConfiguration { private final String name; private final String masterDataSourceName; private final List<String> slaveDataSourceNames; private final LoadBalanceStrategyConfiguration loadBalanceStrategyConfiguration; public MasterSlaveRuleConfiguration (final String name, final String masterDataSourceName, final List<String> slaveDataSourceNames) { this (name, masterDataSourceName, slaveDataSourceNames, null ); } public MasterSlaveRuleConfiguration (final String name, final String masterDataSourceName, final List<String> slaveDataSourceNames, final LoadBalanceStrategyConfiguration loadBalanceStrategyConfiguration) { Preconditions.checkArgument(!Strings.isNullOrEmpty(name), "Name is required." ); Preconditions.checkArgument(!Strings.isNullOrEmpty(masterDataSourceName), "MasterDataSourceName is required." ); Preconditions.checkArgument(null != slaveDataSourceNames && !slaveDataSourceNames.isEmpty(), "SlaveDataSourceNames is required." ); this .name = name; this .masterDataSourceName = masterDataSourceName; this .slaveDataSourceNames = slaveDataSourceNames; this .loadBalanceStrategyConfiguration = loadBalanceStrategyConfiguration; } }
MasterSlaveRuleConfiguration定义了name、masterDataSourceName、slaveDataSourceNames、loadBalanceStrategyConfiguration属性,供我们读写分离配置使用!
我们看一下round_robin定义的属性配置:
MasterSlaveLoadBalanceAlgorithm 源码位置:
https://github.com/apache/incubator-shardingsphere/blob/4.0.0/sharding-core/sharding-core-api/src/main/java/org/apache/shardingsphere/spi/masterslave/MasterSlaveLoadBalanceAlgorithm.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 package org.apache.shardingsphere.spi.masterslave;import org.apache.shardingsphere.spi.TypeBasedSPI;import java.util.List;public interface MasterSlaveLoadBalanceAlgorithm extends TypeBasedSPI { String getDataSource (String name, String masterDataSourceName, List<String> slaveDataSourceNames) ; }
MasterSlaveLoadBalanceAlgorithm接口继承了TypeBasedSPI接口,它定义了getDataSource方法;它有两个实现类分别是RandomMasterSlaveLoadBalanceAlgorithm、RoundRobinMasterSlaveLoadBalanceAlgorithm
RandomMasterSlaveLoadBalanceAlgorithm 源码位置:
https://github.com/apache/incubator-shardingsphere/blob/4.0.0/sharding-core/sharding-core-common/src/main/java/org/apache/shardingsphere/core/strategy/masterslave/RandomMasterSlaveLoadBalanceAlgorithm.java
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 package org.apache.shardingsphere.core.strategy.masterslave;import lombok.Getter;import lombok.Setter;import org.apache.shardingsphere.spi.masterslave.MasterSlaveLoadBalanceAlgorithm;import java.util.List;import java.util.Properties;import java.util.concurrent.ThreadLocalRandom;@Getter @Setter public final class RandomMasterSlaveLoadBalanceAlgorithm implements MasterSlaveLoadBalanceAlgorithm { private Properties properties = new Properties(); @Override public String getType () { return "RANDOM" ; } @Override public String getDataSource (final String name, final String masterDataSourceName, final List<String> slaveDataSourceNames) { return slaveDataSourceNames.get(ThreadLocalRandom.current().nextInt(slaveDataSourceNames.size())); } }
RandomMasterSlaveLoadBalanceAlgorithm使用Random().nextInt来进行随机
RoundRobinMasterSlaveLoadBalanceAlgorithm 源码位置:
https://github.com/apache/incubator-shardingsphere/blob/4.0.0/sharding-core/sharding-core-common/src/main/java/org/apache/shardingsphere/core/strategy/masterslave/RoundRobinMasterSlaveLoadBalanceAlgorithm.java
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 package org.apache.shardingsphere.core.strategy.masterslave;import lombok.Getter;import lombok.Setter;import org.apache.shardingsphere.spi.masterslave.MasterSlaveLoadBalanceAlgorithm;import java.util.List;import java.util.Properties;import java.util.concurrent.ConcurrentHashMap;import java.util.concurrent.atomic.AtomicInteger;@Getter @Setter public final class RoundRobinMasterSlaveLoadBalanceAlgorithm implements MasterSlaveLoadBalanceAlgorithm { private static final ConcurrentHashMap<String, AtomicInteger> COUNTS = new ConcurrentHashMap<>(); private Properties properties = new Properties(); @Override public String getType () { return "ROUND_ROBIN" ; } @Override public String getDataSource (final String name, final String masterDataSourceName, final List<String> slaveDataSourceNames) { AtomicInteger count = COUNTS.containsKey(name) ? COUNTS.get(name) : new AtomicInteger(0 ); COUNTS.putIfAbsent(name, count); count.compareAndSet(slaveDataSourceNames.size(), 0 ); return slaveDataSourceNames.get(Math.abs(count.getAndIncrement()) % slaveDataSourceNames.size()); } }
我们看一下sharding-jdbc的路由策略
MasterSlaveRouter 源码位置:
https://github.com/apache/incubator-shardingsphere/blob/4.0.0/sharding-core/sharding-core-route/src/main/java/org/apache/shardingsphere/core/route/router/masterslave/MasterSlaveRouter.java
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 package org.apache.shardingsphere.core.route.router.masterslave; import lombok.RequiredArgsConstructor; import org.apache.shardingsphere.api.hint.HintManager; import org.apache.shardingsphere.sql.parser.SQLParseEngine; import org.apache.shardingsphere.sql.parser.sql.statement.SQLStatement; import org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement; import org.apache.shardingsphere.core.route.SQLLogger; import org.apache.shardingsphere.core.rule.MasterSlaveRule; import java.util.ArrayList; import java.util.Collection; import java.util.Collections; /** * Master slave router interface. * * @author zhangliang * @author panjuan */ @RequiredArgsConstructor public final class MasterSlaveRouter { private final MasterSlaveRule masterSlaveRule; private final SQLParseEngine parseEngine; private final boolean showSQL; /** * Route Master slave. * * @param sql SQL * @param useCache use cache or not * @return data source names */ // TODO for multiple masters may return more than one data source public Collection<String> route(final String sql, final boolean useCache) { Collection<String> result = route(parseEngine.parse(sql, useCache)); if (showSQL) { SQLLogger.logSQL(sql, result); } return result; } private Collection<String> route(final SQLStatement sqlStatement) { if (isMasterRoute(sqlStatement)) { MasterVisitedManager.setMasterVisited(); return Collections.singletonList(masterSlaveRule.getMasterDataSourceName()); } return Collections.singletonList(masterSlaveRule.getLoadBalanceAlgorithm().getDataSource( masterSlaveRule.getName(), masterSlaveRule.getMasterDataSourceName(), new ArrayList<>(masterSlaveRule.getSlaveDataSourceNames()))); } private boolean isMasterRoute(final SQLStatement sqlStatement) { return containsLockSegment(sqlStatement) || !(sqlStatement instanceof SelectStatement) || MasterVisitedManager.isMasterVisited() || HintManager.isMasterRouteOnly(); } private boolean containsLockSegment(final SQLStatement sqlStatement) { return sqlStatement instanceof SelectStatement && ((SelectStatement) sqlStatement).getLock().isPresent(); } }
MasterSlaveRouter的route方法使用masterSlaveRule来进行路由
MasterSlaveRule 源码位置:
https://github.com/apache/incubator-shardingsphere/blob/4.0.0/sharding-core/sharding-core-common/src/main/java/org/apache/shardingsphere/core/rule/MasterSlaveRule.java
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 package org.apache.shardingsphere.core.rule;import lombok.Getter;import org.apache.shardingsphere.api.config.masterslave.LoadBalanceStrategyConfiguration;import org.apache.shardingsphere.api.config.masterslave.MasterSlaveRuleConfiguration;import org.apache.shardingsphere.spi.algorithm.masterslave.MasterSlaveLoadBalanceAlgorithmServiceLoader;import org.apache.shardingsphere.spi.masterslave.MasterSlaveLoadBalanceAlgorithm;import java.util.List;@Getter public class MasterSlaveRule implements BaseRule { private final String name; private final String masterDataSourceName; private final List<String> slaveDataSourceNames; private final MasterSlaveLoadBalanceAlgorithm loadBalanceAlgorithm; private final MasterSlaveRuleConfiguration ruleConfiguration; public MasterSlaveRule (final String name, final String masterDataSourceName, final List<String> slaveDataSourceNames, final MasterSlaveLoadBalanceAlgorithm loadBalanceAlgorithm) { this .name = name; this .masterDataSourceName = masterDataSourceName; this .slaveDataSourceNames = slaveDataSourceNames; this .loadBalanceAlgorithm = null == loadBalanceAlgorithm ? new MasterSlaveLoadBalanceAlgorithmServiceLoader().newService() : loadBalanceAlgorithm; ruleConfiguration = new MasterSlaveRuleConfiguration(name, masterDataSourceName, slaveDataSourceNames, new LoadBalanceStrategyConfiguration(this .loadBalanceAlgorithm.getType(), this .loadBalanceAlgorithm.getProperties())); } public MasterSlaveRule (final MasterSlaveRuleConfiguration config) { name = config.getName(); masterDataSourceName = config.getMasterDataSourceName(); slaveDataSourceNames = config.getSlaveDataSourceNames(); loadBalanceAlgorithm = createMasterSlaveLoadBalanceAlgorithm(config.getLoadBalanceStrategyConfiguration()); ruleConfiguration = config; } private MasterSlaveLoadBalanceAlgorithm createMasterSlaveLoadBalanceAlgorithm (final LoadBalanceStrategyConfiguration loadBalanceStrategyConfiguration) { MasterSlaveLoadBalanceAlgorithmServiceLoader serviceLoader = new MasterSlaveLoadBalanceAlgorithmServiceLoader(); return null == loadBalanceStrategyConfiguration ? serviceLoader.newService() : serviceLoader.newService(loadBalanceStrategyConfiguration.getType(), loadBalanceStrategyConfiguration.getProperties()); } public boolean containDataSourceName (final String dataSourceName) { return masterDataSourceName.equals(dataSourceName) || slaveDataSourceNames.contains(dataSourceName); } }
MasterSlaveRule内置了loadBalanceAlgorithm、masterSlaveRuleConfiguration
至此我们完成了主从主写的分离!
相关文章