最近需要重构下公司SaaS系统的分库分表的中间件, 发现2020年刚孵化完毕的Sharding-Sphere还是不错的, 故有了此文.

话不多说直接干货, 直接一个分库分表小demo献上!
首先, 这个小demo做了什么事说明下:

1. 提供course课程的crud
2. 利用course的id进行 水平分表
3. 利用course所属的userId 进行水平分库

Demo很简单, 结构如下:

首先, maven依赖, 版本其实无所谓, 现在的兼容性比之前强了很多, 主要是要将shardingsphere支持引入, 这里为了方便, 直接用mybatisplus举例子, 没用过也能看懂, 算是最简单的方案了.

<?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>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.haoxuanli</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</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-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>
        </dependency>
        <!-- 德鲁伊连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
        <!-- 分库分表中间件 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Course实体类

package com.haoxuanli.demo.entity;

public class Course {
    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;

    public Long getCid() {
        return cid;
    }

    public void setCid(Long cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getCstatus() {
        return cstatus;
    }

    public void setCstatus(String cstatus) {
        this.cstatus = cstatus;
    }

    @Override
    public String toString() {
        return "Course{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", userId=" + userId +
                ", cstatus='" + cstatus + '\'' +
                '}';
    }
}

mybatis-plus 支持类

package com.haoxuanli.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.haoxuanli.demo.entity.Course;
import org.springframework.stereotype.Repository;

@Repository
public interface CourseMapper extends BaseMapper<Course> {
}

单元测试类

package com.haoxuanli.demo;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.haoxuanli.demo.entity.Course;
import com.haoxuanli.demo.mapper.CourseMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Random;

@RunWith(SpringRunner.class)
@SpringBootTest
public class DemoApplicationTests {

    @Autowired
    private CourseMapper courseMapper;

    @Test
    public void testMapper() {
        for (int i = 0; i < 10; i++) {
            Course course = new Course();
            course.setCname("ksl");
            course.setCstatus("ok");
            course.setUserId(102L);
            courseMapper.insert(course);
        }
    }


    @Test
    public void findCourse() {
        QueryWrapper<Course> courseQueryWrapper = new QueryWrapper<>();
        courseQueryWrapper.eq("cid", 564449593979305985L);
        Course course = courseMapper.selectOne(courseQueryWrapper);
        System.out.println(course);
    }
}

配置文件: application.yml

server:
  port: 8080
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      # 数据源信息
      names: ds1, ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-className: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/SaaS_user1?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-className: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/SaaS_user2?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
    sharding:
      tables:
        course:
          # 指定数据库,表的分布情况, 下面表达式代表着 ds1 ds2 两个数据库下的 course1,course2表
          actual-dataNodes: ds$->{1..2}.course$->{1..2}
          # 指定主键以及生成策略
          key-generator:
            column: cid
            type: SNOWFLAKE
          # 表规则 ---> 根据 <course id> 水平分表
          table-strategy:
            inline:
              sharding-column: cid
              algorithm-expression: course$->{cid%2+1}
          # 数据库规则 --> 根据 <user id> 水平分库   [default] 代表默认应用到所有表
      default-database-strategy:
        inline:
          sharding-column: user_id
          algorithm-expression: ds$->{user_id%2+1}

#    props:
#      sql:
#        show: true

数据库结构

搞定