Sharding-JDBC读写分离案例
前面我们介绍的了Sharding-jdbc的简介和对比其他的分库分表，接下来我们写demo，介绍SpringBoot使用Sharding-JDBC进行读写分离。话不多说，直接写代码。
准备
- SpringBoot 2.1.12
- Sharding-JDBC 4.0.0
- Mybatis 3.x
- Mysql 8.0
- lombok（暂时没使用）
本文场景介绍
主从两个库：
- 主库负责写入master
- 从库负责查询slave
POM文件
pom文件引入如下相关依赖：
<?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代码
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代码
package cn.cicoding.service;
import cn.cicoding.model.User;
import java.util.List;
public interface UserService {
List<User> list();
Long add(User user);
}
ServiceImpl代码
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() {
// 强制路由主库
// HintManager.getInstance().setMasterRouteOnly();
return userRepository.list();
}
public Long add(User user) {
return userRepository.addUser(user);
}
}
Dao代码
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代码实现
<?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>
实体类
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包，我们来看主要的配置信息
#使用取模的方式来实现表分片
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
启动类
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
查看控制台
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
查看控制台日志
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
源码位置：
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
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;
/**
* Master-slave rule configuration.
*
* @author zhangliang
* @author panjuan
*/
@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
源码位置：
package org.apache.shardingsphere.spi.masterslave;
import org.apache.shardingsphere.spi.TypeBasedSPI;
import java.util.List;
/**
* Master-slave database load-balance algorithm.
*
* @author zhangliang
*/
public interface MasterSlaveLoadBalanceAlgorithm extends TypeBasedSPI {
/**
* Get data source.
*
* @param name master-slave logic data source name
* @param masterDataSourceName name of master data sources
* @param slaveDataSourceNames names of slave data sources
* @return name of selected data source
*/
String getDataSource(String name, String masterDataSourceName, List<String> slaveDataSourceNames);
}
MasterSlaveLoadBalanceAlgorithm接口继承了TypeBasedSPI接口，它定义了getDataSource方法；它有两个实现类分别是RandomMasterSlaveLoadBalanceAlgorithm、RoundRobinMasterSlaveLoadBalanceAlgorithm
RandomMasterSlaveLoadBalanceAlgorithm
源码位置：
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;
/**
* Random slave database load-balance algorithm.
*
* @author zhangliang
*/
@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
源码位置：
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;
/**
* Round-robin slave database load-balance algorithm.
*
* @author zhangliang
*/
@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
源码位置：
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
源码位置：
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;
/**
* Databases and tables master-slave rule.
*
* @author zhangliang
* @author panjuan
*/
@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());
}
/**
* Judge whether contain data source name.
*
* @param dataSourceName data source name
* @return contain or not.
*/
public boolean containDataSourceName(final String dataSourceName) {
return masterDataSourceName.equals(dataSourceName) || slaveDataSourceNames.contains(dataSourceName);
}
}
MasterSlaveRule内置了loadBalanceAlgorithm、masterSlaveRuleConfiguration
至此我们完成了主从主写的分离！
