Spring Boot demo系列(十四):ShardingSphere + MyBatisPlus 分库分表 + 读写分离

1 概述

之前笔者写过两篇文章:

这里将两者结合起来,实现读写分离+分库分表的功能。关于环境的配置本文将进行简化叙述,详细可以参考前两篇文章。

2 环境

  • MySQL 8.0.25Docker
  • MyBatis Plus 3.4.3.1
  • MyBatis Plus Generator 3.5.0
  • Druid 1.2.6
  • ShardingSphere 4.1.1
  • Yitter 1.0.6(一个雪花id生成器)

3 数据库环境准备

由于环境准备不是本文的重点,一主一从的主从复制环境可以参考此处搭建。

准备好环境,本地启动两个MySQL,主节点环境:

  • 名字:master
  • 端口:3306
  • 数据库:两个库(test0test1
  • 数据表:六个表,每个库三个(test0.user0test0.user1test0.user2test1.user0test1.user1test1.user2

从节点环境:

  • 名字:slave
  • 端口:3307
  • 数据库:两个库(test0test1
  • 数据表:六个表,每个库三个(test0.user0test0.user1test0.user2test1.user0test1.user1test1.user2

主库配置文件:

[mysqld]
server-id=1
binlog-do-db=test0
binlog-do-db=test1

从库配置文件:

[mysqld]
server-id=2
replicate-do-db=test0
replicate-do-db=test1

完整的数据库脚本和MySQL配置文件放在文末的源码链接中。

4 新建项目

新建项目并引入如下依赖:

  • Druid
  • MyBatis Plus starter
  • MyBaits Plus Generator
  • Velocity core
  • ShardingSphere
  • Yitter

Maven如下:

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.3.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.5.0</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>
<dependency>
    <groupId>org.realityforge.org.jetbrains.annotations</groupId>
    <artifactId>org.jetbrains.annotations</artifactId>
    <version>1.7.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>com.github.yitter</groupId>
    <artifactId>yitter-idgenerator</artifactId>
    <version>1.0.6</version>
</dependency>

Gradle如下:

implementation 'com.baomidou:mybatis-plus-boot-starter:3.4.3.1'
implementation 'org.apache.velocity:velocity-engine-core:2.3'
implementation 'org.realityforge.org.jetbrains.annotations:org.jetbrains.annotations:1.7.0'
implementation 'com.alibaba:druid:1.2.6'
implementation 'org.apache.shardingsphere:sharding-jdbc-spring-boot-starter:4.1.1'
implementation 'com.github.yitter:yitter-idgenerator:1.0.6'

5 配置文件

spring:
  shardingsphere:
    datasource:
      names: master-test0,master-test1,slave-test0,slave-test1                 # 数据源节点名字
                                                                               # master-test0表示主节点的test0库,master-test1表示主节点的test1库
                                                                               # slave-test0表示从节点的test0库,slave-test1表示从节点的test1库
      master-test0:
        type: com.alibaba.druid.pool.DruidDataSource                           # 连接池
        url: jdbc:mysql://127.0.0.1:3306/test0                                 # 主节点的test0库
        username: root
        password: 123456
      master-test1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test1                                 # 主节点的test1库
        username: root
        password: 123456
      slave-test0:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3307/test0                                 # 从节点的test0库,端口3307
        username: root
        password: 123456
      slave-test1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3307/test1                                 # 从节点的test1库,端口3307
        username: root
        password: 123456
    sharding:
      default-database-strategy:
        inline:
          sharding-column: age                                                 # 按照哪一列分库
          algorithm-expression: master-test$->{age % 2}                        # 分库规则为对年龄取模
      tables:
        user:
          actual-data-nodes: master-test$->{0..1}.user$->{0..2}                # 分表的节点,格式为 [数据源.表名]
          table-strategy:
            inline:
              sharding-column: id                                              # 按照哪一列分表
              algorithm-expression: user$->{id%3}                              # 分表规则,对id取模

      master-slave-rules:                                                      # 读写分离的规则
        master-test0:                                                          # 哪一个主节点
          master-datasource-name: master-test0                                 # 指定主节点名字
          slave-data-source-names: slave-test0                                 # 指定从节点名字
        master-test1:
          master-datasource-name: master-test1
          slave-data-source-names: slave-test1
    props:
      sql:
        show:
          true                                                                 # 打印SQL

6 准备测试代码

使用MyBatis Plus Generator生成器类生成代码:

import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.*;

public class MyBatisPlusGenerator {
    public static void main(String[] args) {
        DataSourceConfig dataSourceConfig = new DataSourceConfig.Builder("jdbc:mysql://localhost:3306/test0", "root", "123456").build();
        String projectPath = System.getProperty("user.dir");
        StrategyConfig strategyConfig = new StrategyConfig.Builder().addInclude("user").build();
        GlobalConfig globalConfig = new GlobalConfig.Builder().outputDir(projectPath + "/src/main/java").openDir(false).build();
        PackageConfig packageConfig = new PackageConfig.Builder().moduleName("user").parent("com.example.demo").serviceImpl("service").build();
        new AutoGenerator(dataSourceConfig).global(globalConfig).packageInfo(packageConfig).strategy(strategyConfig).execute();
    }
}

实体类加上@Builder,同时设置id类型为IdType.ASSIGN_ID

@Builder
public class User implements Serializable {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    //...
}

修改Controller类:

@RestController
@RequestMapping("/user")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserController {
    private final Random random = new Random();
    private final UserServiceImpl service;
    @GetMapping("/select")
    public List<User> select(){
        return service.list();
    }

    @GetMapping("/insert")
    public boolean insert(){
        return service.save(User.builder().age(random.nextInt(80)+20).name("test name").email("test@test.com").build());
    }
}

同时新增一个雪花id生成器类(具体配置方法可以参考MyBatis Plus官方文档):

import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import com.github.yitter.contract.IdGeneratorOptions;
import com.github.yitter.idgen.YitIdHelper;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;

@Component
public class IdGenerator implements IdentifierGenerator {
    final IdGeneratorOptions options = new IdGeneratorOptions((short) 1);

    @PostConstruct
    public void init() {
        YitIdHelper.setIdGenerator(options);
    }

    @Override
    public Long nextId(Object entity) {
        return YitIdHelper.nextId();
    }
}

7 测试

刷新几次插入页面:

http://localhost:8080/user/insert

从输出可以看到插入都是在主节点中进行的:

Spring Boot demo系列(十四):ShardingSphere + MyBatisPlus 分库分表 + 读写分离

而查询的时候:

http://localhost:8080/user/select

输出如下:

Spring Boot demo系列(十四):ShardingSphere + MyBatisPlus 分库分表 + 读写分离

是在从节点查询的。

8 参考代码

Java版:

Kotlin版:

上一篇:Ansible执行流程


下一篇:10个js的优化技巧