ShardingJDBC读写分离案例

前面我们介绍的了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

源码位置：

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

/*
 * 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

源码位置：

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

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

源码位置：

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

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

源码位置：

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

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

源码位置：

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

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

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

至此我们完成了主从主写的分离！