数据库

ShardingJDBC配置读写分离

前提条件：MySQL已搭建好主从复制环境，这里采用一主二从形式（尚未搭建）

使用Sharding版本：4.0.0-RC1

使用SpringBoot版本：2.7.0

官网：https://shardingsphere.apache.org/index_zh.html

一、导入依赖

<properties>
    <java.version>11</java.version>
    <!--就用这个版本，其他版本这里暂未研究明白-->
    <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
</properties>

<!--这三个依赖不加会报错：java.lang.ClassNotFoundException: javax.xml.bind.JAXBContext-->
<!--从Java9开始，Java SE 的整体jar 结构都进行高度模块化，因此不会自动加载javax.xml.bind内容-->
<dependency>
    <groupId>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.1</version>
</dependency>
<dependency>
    <groupId>com.sun.xml.bind</groupId>
    <artifactId>jaxb-impl</artifactId>
    <version>2.3.1</version>
</dependency>
<dependency>
    <groupId>org.glassfish.jaxb</groupId>
    <artifactId>jaxb-runtime</artifactId>
    <version>2.3.1</version>
</dependency>
<!--==================================================================-->
<!--sharding依赖-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-core-common</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<!--==================================================================-->
<!--mybatis和mysql驱动 -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<!--依赖数据源druid-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.11</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

二、编写配置

主配置 application.yml:

server:
  port: 8099
spring:
  main:
    allow-bean-definition-overriding: true
  profiles:
    active: MasterSlave		# 激活指定配置文件

mybatis:	# 使用mybatis-plus不用配置
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.konan.sharding.entity

从配置application-MasterSlave.yml:

spring:
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的ds1,ds2,ds3任意取名字,ds1对应的是主库
      names: ds1,ds2,ds3
      # 给master-ds1每个数据源配置数据库连接信息
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://117.50.185.199:3307/sharding-study?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 1234Abc666
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://117.50.185.199:3308/sharding-study?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 1234Abc666
        maxPoolSize: 100
        minPoolSize: 5
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3308/sharding-study?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 12345Abc666
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds1
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds1
    # 配置数据源的读写分离，但是数据库一定要做主从复制
    masterslave:
      # 配置主从名称，可以任意取名字
      name: ms
      # 配置主库master，负责数据的写入
      master-data-source-name: ds1
      # 配置从库slave节点
      slave-data-source-names: ds2,ds3
      # 配置slave节点的负载均衡均衡策略，采用轮询机制,只有一个从库，无须配置
      load-balance-algorithm-type: round_robin

三、建立测试表

只需在主库中建立即可，从库会自动复制，建立如下表：

对应SQL：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nickname` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `birthday` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET FOREIGN_KEY_CHECKS = 1;

四、编写测试程序

1. 目录结构

2. entity

package com.konan.shardingstudy.entity;

import lombok.Data;
import java.util.Date;

@Data
public class User {
    private Integer id;
    private String nickname;
    private String password;
    private Integer sex;
    private Date birthday;
}

3. mapper

package com.konan.shardingstudy.mapper;

import com.konan.shardingstudy.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper //表示这是一个mybatis的mapper类
@Repository
public interface UserMapper {

    @Insert("insert into user(nickname,password,sex,birthday) values(#{nickname},#{password},#{sex},#{birthday})")
    @Options(useGeneratedKeys = true,keyColumn = "id",keyProperty = "id")	//可以回填主键的值，mbp中自动回填
    void addUser(User user);

    @Select("select * from user)
    List<User> findUsers();
}

4. controller

package com.konan.shardingstudy.controller;

import com.konan.shardingstudy.entity.User;
import com.konan.shardingstudy.mapper.UserMapper;
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.Date;
import java.util.List;
import java.util.Random;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/save")
    public String insert() {
        User user = new User();
        user.setNickname("zhangsan"+ new Random().nextInt());
        user.setPassword("1234567");
        user.setSex((int)(Math.random()*2));
        user.setBirthday(new Date());
        userMapper.addUser(user);
        return user.getId().toString();
    }

    @GetMapping("/list")
    public List<User> listuser() {
        return userMapper.findUsers();
    }
}

五、测试

1：访问 http://localhost:8099/user/save 一直进入到ds1主节点
2：访问 http://localhost:8099/user/list 一直进入到ds2、ds3节点，并且轮询进入。

日志查看

总结

ShardingSphere 的 3 个产品（Sharding-JDBC、Sharding-Proxy、Sharding-Sidecar）的数据分片主要流程是完全一致的

核心由 SQL 解析 => 执行器优化 => SQL 路由 => SQL 改写 => SQL 执行 => 结果归并的流程组成

Sharding-JDBC的读写分离原理图

如果觉得文章对你有用，请随意赞赏

MySQL ShardingSphere

ShardingJDBC配置读写分离

https://qiandao.space/archives/shardingjdbc配置读写分离

作者

千帆暮雨

发布于

2022-07-05

更新于

2022-07-05

许可协议

CC BY 4.0

ShardingJDBC配置读写分离

一、导入依赖

二、编写配置

三、建立测试表

四、编写测试程序

1. 目录结构

2. entity

3. mapper

4. controller

五、测试

作者

发布于

更新于

许可协议

评论