ShardingSphere-JDBC实战——读写分离

写在前面：

你好，我是小零。一个和你一样喜欢探索新技术的开发者。本次带来的是ShardingSphere框架实现的mysql读写分离的实战篇。在这里我要感谢“竹子爱熊猫”大佬带来的mysql专https://juejin.cn/column/7140138832598401054。推荐小伙伴你去阅读，一定会有所收获。好，接下来让我们进入正题。

一. 搭建一个maven管理的项目，相信大家。这里我就省略了。

二.项目开始

1. 导入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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

<!--    springboot父工程-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.5</version>
    </parent>

    <groupId>com.wad</groupId>
    <artifactId>mysql_read_wirte</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
<!--        spring-test依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
<!--        web依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.6.5</version>
        </dependency>
<!--        druid依赖，使用原生的，排除spring-boot-starter版本-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
<!--        mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.1</version>
        </dependency>
<!--        junit测试依赖-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
<!--        lombok依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
<!--        mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.31</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
            <version>2.7.2</version>
        </dependency>

<!--        sharding-sphere-jdbc依赖，本次采用的是5.2.1版本-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.1</version>
        </dependency>
<!--        snakeyaml依赖，如果使用yml配置需要这个依赖-->
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>



    </dependencies>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

</project>

2. 编写spring配置文件(application.yml或application.properties)

SharadingSphere推荐使用application.properties配置,这里给出两个自行选择。

server.port=8901

#数据源名字，这里采用了一主两从的读写分离架构
spring.shardingsphere.datasource.names=master,slave1,slave2


#配置主数据源
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://192.168.200.133:3306/ry_vue?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

#配置第一个从数据源
spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://192.168.200.134:3306/ry_vue?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

#配置第二个从数据源
spring.shardingsphere.datasource.slave2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave2.url=jdbc:mysql://192.168.200.136:3306/ry_vue?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=123456

#配置读写分离的规则

#主
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.write-data-source-name=master 
#从，多个从用逗号分隔
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.read-data-source-names=slave1,slave2
#读负载均衡的名字
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=read-random

#负载均衡的配置采用轮询
spring.shardingsphere.rules.readwrite-splitting.load-balancers.read-random.type=round_robin

#输出结果时打印sql方便调试
spring.shardingsphere.props.sql-show=true

#mybatis配置
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.wad.domain
spring.main.allow-bean-definition-overriding=true

server:
  port: 8901

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.wad.domain

spring:
#  配置shardingsphere
  shardingsphere:
    #    配置多个数据源
    datasource:
      names: master,slave1,slave2

    #    主服务器数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.200.133:3306/ry_vue?useUnicode=true&characterEncoding=utf-8&useSSL=true
        username: root
        password: 123456

#    从服务器数据源
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.200.134:3306/ry_vue?useUnicode=true&characterEncoding=utf-8&useSSL=true
        username: root
        password: 123456
      slave2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.200.136:3306/ry_vue?useUnicode=true&characterEncoding=utf-8&useSSL=true
        username: root
        password: 123456
        
    rules:
      readwrite-splitting:
        data-sources:
          myds:
            static-strategy:
              write-data-source-name: master
              read-data-source-names: slave1,slave2
            load-balancer-name: read-random
        load-balancers:
          read-random:
            type: round_robin
    props:
      sql-show: true #是否打印sql
      sql-simple: true #打印简单的sql

yml文件配置数据源出现报红为正常现象，我的idea版本为2022.3.1

3. 编写mapper和service

package com.wad.domain;

import lombok.Data;
import lombok.NoArgsConstructor;

/**
  * @author wad
  * @date 2023/09/07 9:25
  * @project mysql_read_wirte
  **/
@Data
@NoArgsConstructor
public class Book {

    private Long id;
    private String name;
}

package com.wad.service;

import com.wad.domain.Book;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Service;

/**
 * @author wad
 * @date 2023/09/07 9:27
 * @project mysql_read_wirte
 **/
@Service
public interface BookService {

    Book selectBookById(Long id);


    void addBook(Book book);

}

package com.wad.service.impl;

import com.wad.domain.Book;
import com.wad.mapper.BookMapper;
import com.wad.service.BookService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

/**
 * @author wad
 * @date 2023/09/07 9:28
 * @project mysql_read_wirte
 **/
@Service
public class BookServiceImpl implements BookService {

    @Resource
    private BookMapper bookMapper;



    @Override
    public Book selectBookById(Long id) {
        return bookMapper.selectBookById(id);
    }

    @Override
    public void addBook(Book book) {
        bookMapper.addBook(book);
    }
}

package com.wad.mapper;

import com.wad.domain.Book;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

/**
 * @author wad
 * @date 2023/09/07 9:37
 * @project mysql_read_wirte
 **/
@Repository
public interface BookMapper {


    Book selectBookById(@Param("id") Long id);


    void addBook(Book book);
}

<?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="com.wad.mapper.BookMapper">

  <resultMap id="BookResult" type="com.wad.domain.Book">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
  </resultMap>


  <sql id="selectBook">
    select id,name from book
  </sql>

  <select id="selectBookById" resultMap="BookResult">
    <include refid="selectBook" />
    where id=#{id}
  </select>
  <insert id="addBook" parameterType="com.wad.domain.Book" useGeneratedKeys="true">
    insert into book(name) values (#{name})
  </insert>
</mapper>

package com.wad;

import com.wad.domain.Book;
import com.wad.service.BookService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

/**
 * @author wad
 * @date 2023/09/19 13:59
 * @project mysql_read_wirte
 **/

@SpringBootTest(classes = Main.class)
@RunWith(SpringRunner.class)
public class MainTest {

    @Resource
    private BookService bookService;

    @Test
    public void testSelect(){
        Book book = bookService.selectBookById(1L);
        Book book1 = bookService.selectBookById(2L);
        System.out.println(book.toString());
    }

    @Test
    @Transactional(rollbackFor = Exception.class)
    public void testAdd(){
        Book book=new Book();
        book.setName("linux老鸟");
        bookService.addBook(book);
        Book book1 = bookService.selectBookById(4L);
        System.out.println(book1);
    }
}

4. 数据库创建库，在主库创建

-- auto-generated definition
create table book
(
    id   bigint auto_increment comment '主键id'
        primary key,
    name varchar(255) not null comment '名字'
);

三、测试结果

测试读操作我们可以看到，请求自动被负载到了两个从库，并遵循轮询算法。

测试写操作我们可以看到，请求走了主库，但是写之后紧跟着读却也走了主库，这是为什么呢？

接下来可以看看这个 这两个读请求就走了从库，有没有发现什么不一样。 细心的你肯定已经发现了，一个方法我加了spring的事务注解，一个没有。如果加了事务注解，那么spring就会把方法中的数据库操作合并为一个原子操作。打包发给这次请求的数据库，也就是主库。这样读写请求就都会走主库。 思考一下：我们是不是可以使用这种方式来强制走主库呢？