ShardingSphere-分库分表

介绍

Apache ShardingSphere 是一个开源的分布式数据库中间件解决方案组成的生态圈，且它的产品有Sharding-JDBC和Sharding-Proxy组成（他们两个之间是独立的），同时又能混合部署（组合起来一起使用）。它们都提供了标准化的数据分片、分布式事务和数据库的治理能力，可适用如Java、云原生开发的应用场景。

ShardingSphere定位是关系型数据库中间件，目的是充分为了合理地在分布式的场景下利用关系型数据库的计算能力和存储能力，而不是实现一个全新的关系型数据库。

在我们的工作中又或是在生活中，我们能够接触到的数据量是持续增加的，并且数据量不一定是可控的，在没有进行分库分表的情况下，随着时间和业务的不断发展，数据库中的表会越来越多，同时表中的数据量也会越来越多。所以对于数据库的操作，比如CRUD数据库表的开销也随之增加；另外，由于无法进行分布式部署，而一台服务器的资源（CPU、磁盘、内存、IO）都是有限的，最终数据库所能承载的数据量变小，从而达到提升数据库性能的目的。

我们在工作中针对分库分表，一般都是从以下两个方面进行实现：
方案一：从硬件上（增加CPU、磁盘、网络、内存等）
方案二：从数据库分库分表

环境搭建

• 所需依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>-->

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

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

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

ShardingSphere-分库分表

水平分表

实现

• 创建数据库及表(水平分表)

mysql> create table course_1(
    cid bigint(20) primary key,
    cname varchar(20) not null,
    user_id bigint(20) not null,
    cstatus varchar(20) not null
);
mysql> create table course_2(
    cid bigint(20) primary key,
    cname varchar(20) not null,
    user_id bigint(20) not null,
    cstatus varchar(20) not null
);

• 规则：如果添加数据，如果id是偶数的话就放在course_1里面，反之放在course_2里面。
• 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Course {
    private Long cid;
    private String cname;
    @TableField("user_id")
    private Long userId;
    private String cstatus;
}

• 编写mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zyl.shardingsphere.bean.Course;
import org.springframework.stereotype.Repository;

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

• 启动类修改

@SpringBootApplication
@MapperScan("com.zyl.shardingsphere.dao")
public class SpringbootShardingSphereApplication {

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

}

• application.properties配置文件

spring.shardingsphere.datasource.names=ds-0
spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:mysql://localhost:3306/ssm?serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=123123

spring.main.allow-bean-definition-overriding=true

spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-0.course_$->{1..2}

spring.shardingsphere.sharding.tables.course.key-generator.column=cid

spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid

spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

spring.shardingsphere.props.sql.show=true

• 测试

@SpringBootTest
class SpringbootShardingSphereApplicationTests {

    @Resource
    CourseMapper courseMapper;
    @Resource
    UserMapper userMapper;
    @Test
    void contextLoads() {
        Course course = null;
        for (int i = 0; i < 100; i++) {
            course = new Course();
            course.setCname("Java");
            course.setUserId(1000L);
            course.setCstatus("Nor1");
            courseMapper.insert(course);
        }
    }
}

水平分库

• 概念：创建多个结构相同的数据库，里面的表是一模一样的。
  
• 数据库规则：
  userid为偶数时候添加到edu_db_1数据库
  userid为基数的时候添加到edu_db_2数据库

实现

• 创建数据库和表

-- 创建两个数据库
CREATE DATABASE edu_db_1;
CREATE DATABASE edu_db_2;
-- 需要在两个数据库中都执行下面的脚本
CREATE TABLE course_1(
    cid BIGINT(20) PRIMARY KEY,
    cname VARCHAR(20) NOT NULL,
    user_id BIGINT(20) NOT NULL,
    cstatus VARCHAR(20) NOT NULL
);


CREATE TABLE course_2(
    cid BIGINT(20) PRIMARY KEY,
    cname VARCHAR(20) NOT NULL,
    user_id BIGINT(20) NOT NULL,
    cstatus VARCHAR(20) NOT NULL
);

• application.properties配置文件

spring.shardingsphere.datasource.names=ds-1,ds-2
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=123123

spring.shardingsphere.datasource.ds-2.jdbc-url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds-2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.username=root
spring.shardingsphere.datasource.ds-2.password=123123

spring.main.allow-bean-definition-overriding=true

spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-$->{1..2}.course_$->{1..2}

spring.shardingsphere.sharding.tables.course.key-generator.column=cid

spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=ds-$->{user_id % 2 + 1}

spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid

spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

spring.shardingsphere.props.sql.show=true

• 测试

@SpringBootTest
class SpringbootShardingSphereApplicationTests {

    @Resource
    CourseMapper courseMapper;
    
    @Test
    void addCorseFpdb(){
        Course course = new Course();
        course.setCname("java01");
        // 根据user_id进行分库，偶数是在edu_db_1，基数是在edu_db_2
        course.setUserId(75L);
        course.setCstatus("已启用");
        courseMapper.insert(course);
    }
}

垂直分库分表

• 概念：把单一的数据库按照业务的不同进行划分（专库专表）
  

实现

• 创建数据库和表

CREATE DATABASE user_db;
USE user_db;
CREATE TABLE T_USER(
	user_id BIGINT(20) NOT NULL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    ustatus VARCHAR(50) NOT NULL
);

• 创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
@TableName("t_user")
public class User {
    @TableId
    private Long userId;
    private String username;
    private String ustatus;
}

• mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zyl.shardingsphere.bean.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

• application.properties配置文件

spring.shardingsphere.datasource.names=ds-1,ds-2,ds-3
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=123123

spring.shardingsphere.datasource.ds-2.jdbc-url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds-2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.username=root
spring.shardingsphere.datasource.ds-2.password=123123

spring.shardingsphere.datasource.ds-3.jdbc-url=jdbc:mysql://localhost:3306/user_db?serverTimezone=Asia/Shanghai&useSSL=false
spring.shardingsphere.datasource.ds-3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-3.username=root
spring.shardingsphere.datasource.ds-3.password=123123

spring.main.allow-bean-definition-overriding=true

#spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds-$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds-$->{3}.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id

spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id

spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

spring.shardingsphere.props.sql.show=true

• 测试

@SpringBootTest
class SpringbootShardingSphereApplicationTests {

    @Resource
    UserMapper userMapper;
    @Test
    void addUser() {
        User user = new User();
        user.setUsername("nuanqin");
        user.setUstatus("a");
        userMapper.insert(user);
    }

}

垂直分表概念

操作数据库中的某张表，我们把这张表里面的一部分字段拿出来存储到一张新的表里，剩下的字段放在另一张表里。如下图