分库分表、读写分离--ShardingJDBC

1. 项目准备

1.1 建立数据库表

建立user_manage数据库，在该库中建立1张表app_user用来做分库前的测试，另外建12张按月份命名的表app_user_2024XX用来做分库。

CREATE DATABASE IF NOT EXISTS user_manage
CHARACTER SET utf8
COLLATE utf8_general_ci;

USE user_manage;

CREATE TABLE `app_user` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202401` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202402` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202403` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202404` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202405` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202406` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202407` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202408` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202409` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202410` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202411` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

CREATE TABLE `app_user_202412` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `age` INT(10) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `address` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted` TINYINT(1) DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

1.2 项目搭建-集成mybatisplus

目录结构

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>

    <groupId>com.gzdemo</groupId>
    <artifactId>shardingjdbc-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

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

        <lombok.version>1.18.8</lombok.version>
        <mysql.version>5.1.46</mysql.version>
        <mybatis.version>3.5.1</mybatis.version>
        <mybatis.plus.version>3.4.1</mybatis.plus.version>
    </properties>

    <!-- 继承Spring boot -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
    </parent>


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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!-- Mysql 数据库 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis.plus.version}</version>
        </dependency>
    </dependencies>
</project>

application.yml

server:
  port: 8080

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://192.168.200.131:3306/user_manage?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
    username: root
    password: root
# 设置Mapper接口所对应的XML文件位置，如果你在Mapper接口中有自定义方法，需要进行该配置
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  # 设置别名包扫描路径，通过该属性可以给包中的类注册别名
  type-aliases-package: com.gzdemo.shardingdemo.pojo

AppUser

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

@Data
//默认映射到app_user表
public class AppUser {

    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private String address;
    private String phone;
    private Date create_time;
    private Date update_time;
    private Short deleted;
}

AppUserService

public interface AppUserService extends IService<AppUser> {

    boolean addOne(AppUser appUser);
}

AppUserServiceImpl

@Service
public class AppUserServiceImpl extends ServiceImpl<AppUserMapper, AppUser> implements AppUserService {

    @Autowired
    AppUserMapper appUserMapper;

    @Override
    public boolean addOne(AppUser appUser){
        return appUserMapper.insertOne(appUser);
    }
}

AppUserMapper

@Mapper
public interface AppUserMapper extends BaseMapper<AppUser> {

    boolean insertOne(@Param("user") AppUser appUser);
}

AppUserMapper.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="com.gzdemo.shardingdemo.dao.AppUserMapper">

    <insert id="insertOne" parameterType="com.gzdemo.shardingdemo.pojo.AppUser">
        insert into `app_user`
            (id,name,age,sex,address,phone,create_time,update_time,deleted)
        values(#{user.id},#{user.name},#{user.age},#{user.sex},#{user.address},#{user.phone},
               #{user.create_time},#{user.update_time},#{user.deleted});
    </insert>
</mapper>

AppUserController

@RestController
@RequestMapping("/appUser")
public class AppUserController {

    @Autowired
    AppUserService appUserService;

    @PostMapping("/add")
    public String addAppUser(@RequestBody AppUser appUser){

		appUser.setCreate_time(new Date());
        appUser.setUpdate_time(new Date());
        appUserService.save(appUser); //mybatisplus生成

        appUser.setId(appUser.getId()+1000);
        appUserService.addOne(appUser); //手写的sql
        return "成功";
    }
}

ShardingJdbcDemoApplication

@SpringBootApplication
public class ShardingJdbcDemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcDemoApplication.class,args);
    }
}

1.3 postman测试

数据插入到了app_user表中，mybatisplus没问题

2. 分库分表实战

添加依赖

<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
	<version>5.0.0</version>
</dependency>

<!--druid-->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.21</version>
</dependency>

2.1 分表

更改application.yml配置
包括配置数据源、配置逻辑表、配置分片算法

测试

查看数据库，发现数据被插入到app_user_202406表里

2.2 分库

再新建3个数据库，接下来将所有数据分在4个库里。

更改配置

• 添加数据源
  
• 设置分库策略
  
• 配置分库算法
  这里的算法是取appUser的id的hash值除以取模4求余，再取绝对值，根据结果放在ds0,ds1,ds2,ds3库里
  
  测试
  写一个mapping测试

@PostMapping("/addBatch")
    public String addAppUser(){
        List<AppUser> list = new ArrayList<>();
        for (int i = 0;i<100;i++){
            AppUser appUser = new AppUser();
            appUser.setId(11001+i);
            System.out.print(Math.abs(appUser.hashCode()%4)+",");
            appUser.setSex("0");
            appUser.setName("zhangsan"+i);
            appUser.setAge(25);
            appUser.setDeleted((short)0);
            appUser.setCreate_time(new Date());
            list.add(appUser);
        }

        appUserService.saveBatch(list);
        return "成功";

发送请求
查看数据库，发现4个库的app_user_202406表里都有数据

2.3 分库分表后读取数据

@GetMapping("/list")
    public List<AppUser> list(){
        return appUserService.list();
    }

测试发现把所有分库的所有表都读取出来了

2.4 分库分表完整配置

server:
  port: 8080

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage_01?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage_02?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage_03?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root

    rules:
      sharding:
        tables:
          #配置逻辑数据库
          app_user:
            #数据库节点
            actual-data-nodes: ds$->{0..3}.app_user_$->{202401..202412}
            #分表策略
            table-strategy:
              standard:
                #分片键为 created_time  根据哪个字段分片，该字段叫分片键
                sharding-column: create_time
                #分表算法为
                sharding-algorithm-name: app-user-table-inline
            #分库策略
            database-strategy:
              standard:
                #分库列为id列
                sharding-column: id
                #分库算法 database_inline
                sharding-algorithm-name: app-user-database-inline

        #算法配置
        sharding-algorithms:
          #订单表分表算法
          app-user-table-inline:
            type: INTERVAL
            props:
              datetime-pattern: yyyy-MM-dd HH:mm:ss
              datetime-lower: 2024-01-01 00:00:00
              datetime-upper: 2024-12-01 00:00:00
              sharding-suffix-pattern: yyyyMM
              datetime-interval-unit: MONTHS
          #分库算法配置
          app-user-database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{Math.abs(id.hashCode()%4)}

# 设置Mapper接口所对应的XML文件位置，如果你在Mapper接口中有自定义方法，需要进行该配置
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  # 设置别名包扫描路径，通过该属性可以给包中的类注册别名
  type-aliases-package: com.gzdemo.shardingdemo.pojo

3. 读写分离

使两个主库ds0,ds1负责写数据，因此把分库算法中取模4设置为取模2

ds0作为主库负责写，ds2负责读；ds1作为主库负责写，ds1和ds3负责读

配置

          #分库算法配置
          app-user-database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{Math.abs(id.hashCode()%2)}
    rules:
      #读写分离配置
      readwrite-splitting:
        data-sources:
          ds0:
            #主数据源
            write-data-source-name: ds0
            #从数据源
            read-data-source-names: ds2
            #负载均衡算法
            load-balancer-name: random
          ds1:
            #主数据源
            write-data-source-name: ds1
            #从数据源
            read-data-source-names: ds1,ds3
            #负载均衡算法
            load-balancer-name: random
        #负载均衡算法
        load-balancers:
          round-robin:
            type: ROUND_ROBIN
          random:
            type: RANDOM

修改user_manage库（ds0）中的i第一个name为zhangsanmanage;
修改user_manage_01库（ds1）中的i第一个name为zhangsanmanage01;
修改user_manage_02库（ds2）中的i第一个name为zhangsan3manage02;
修改user_manage_03库（ds3）中的i第一个name为zhangsanmanage03;

测试list接口，发现每次请求id为11004的只有来自于ds02的数据；
而id为11001的由于采用random算法，有时候只有来自于ds03的数据，有时候ds01和ds03的都有；
当采用rr算法时，发现每次只有来自于ds03的数据。没有ds02的数据。
这里暂时还不清楚原因。

4. 分库分表、读写分离所有配置

server:
  port: 8080

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage_01?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage_02?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.200.131:3306/user_manage_03?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
        username: root
        password: root

    rules:
      sharding:
        tables:
          #配置逻辑数据库
          app_user:
            #数据库节点
            actual-data-nodes: ds$->{0..3}.app_user_$->{202401..202412}
            #分表策略
            table-strategy:
              standard:
                #分片键为 created_time  根据哪个字段分片，该字段叫分片键
                sharding-column: create_time
                #分表算法为
                sharding-algorithm-name: app-user-table-inline
            #分库策略
            database-strategy:
              standard:
                #分库列为id列
                sharding-column: id
                #分库算法 database_inline
                sharding-algorithm-name: app-user-database-inline

        #算法配置
        sharding-algorithms:
          #订单表分表算法
          app-user-table-inline:
            type: INTERVAL
            props:
              datetime-pattern: yyyy-MM-dd HH:mm:ss
              datetime-lower: 2024-01-01 00:00:00
              datetime-upper: 2024-12-01 00:00:00
              sharding-suffix-pattern: yyyyMM
              datetime-interval-unit: MONTHS
          #分库算法配置
          app-user-database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{Math.abs(id.hashCode()%2)}

      #读写分离配置
      readwrite-splitting:
        data-sources:
          ds0:
            #主数据源
            write-data-source-name: ds0
            #从数据源
            read-data-source-names: ds2
            #负载均衡算法
            load-balancer-name: random
          ds1:
            #主数据源
            write-data-source-name: ds1
            #从数据源
            read-data-source-names: ds1,ds3
            #负载均衡算法
            load-balancer-name: random
        #负载均衡算法
        load-balancers:
          round-robin:
            type: ROUND_ROBIN
          random:
            type: RANDOM


# 设置Mapper接口所对应的XML文件位置，如果你在Mapper接口中有自定义方法，需要进行该配置
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  # 设置别名包扫描路径，通过该属性可以给包中的类注册别名
  type-aliases-package: com.gzdemo.shardingdemo.pojo