MySQL-高可用篇-②分库分表实践

holic-x...大约 11 分钟

MySQL-高可用篇-②分库分表实践

分表不分库

1.环境准备

项目环境

JAVA环境：JAVA 8、JDK1.8
Springboot：2.x
分表组件：shardingsphere-jdbc
MySQL：V8.0.21

数据准备

建立用于测试的数据：

库名：db_camps
数据库：t_user、t_student_1、t_student_2

# 创建数据库
create database db_camps;
use db_camps;

# 创建数据表
CREATE TABLE IF NOT EXISTS `t_student_1`(
   `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增',
   `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
   `create_time` varchar(32) NOT NULL DEFAULT '' COMMENT '创建日期,yyyy-MM-dd HH:mm:ss',
   `grade_code` varchar(32) NOT NULL DEFAULT '' COMMENT '年级,first,second,third,fourth',
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `t_student_2`(
   `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增',
   `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
   `create_time` varchar(32) NOT NULL DEFAULT '' COMMENT '创建日期,yyyy-MM-dd HH:mm:ss',
   `grade_code` varchar(32) NOT NULL DEFAULT '' COMMENT '年级,first,second,third,fourth',
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `t_user`(
   `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增',
   `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
   `create_time` varchar(32) NOT NULL DEFAULT '' COMMENT '创建日期,yyyy-MM-dd HH:mm:ss',
   `age` int(4) NOT NULL DEFAULT 0 COMMENT '年龄',
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.项目构建

根据相关开发环境，初始化Springboot项目，然后按照下述步骤改造项目：

引入shardingsphere分表组件和数据库连接相关依赖（需考虑版本兼容性）
配置数据库连接和分库分表策略
构建MVC层，对外提供接口测试数据库连接访问

pom.xml 依赖构建

核心依赖参考配置

<properties>
  <java.version>1.8</java.version>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <spring-boot.version>2.7.6</spring-boot.version>
</properties>
<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
  </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>

  <!-- 引入分库分表组件 -->
  <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter -->
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <!--            <version>5.2.1</version>-->
    <version>5.0.0</version>
  </dependency>

  <!-- 引入数据库连接相关 -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
  </dependency>

  <!-- tocheck 为什么要引入自身项目依赖？ -->
  <!--        -->
  <!--        <dependency>-->
  <!--            <groupId>com.noob.base</groupId>-->
  <!--            <artifactId>springboot-demo-sharding01</artifactId>-->
  <!--            <version>0.0.1-SNAPSHOT</version>-->
  <!--            <scope>compile</scope>-->
  <!--        </dependency>-->


</dependencies>
<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-dependencies</artifactId>
      <version>${spring-boot.version}</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>

application.yml 配置

分库分表策略&数据库连接配置

# server配置
server:
  port: 8080
spring:
  profiles:
    active: local

# mybatis 配置
mybatis:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

---
# 第一种：不分库，水平分表
spring:
  shardingsphere:
    datasource:
      names: ds0    # 自定义数据源名称（如果有多个，用逗号分隔），例如此处采用分表不分库策略只需配置一个数据库连接信息
      ds0:          # 根据上面的定义的自定义数据源名称，配置对应数据源的连接信息
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/db_camps?useUnicode=true&autoReconnect=true
        username: root
        password: 123456
    rules:         # 配置分库分表策略（配置路由规则等）
      sharding:
        tables:    # 分表配置：例如有两张分表t_student_0,t_student_1,这里写t_student即可，数据具体保存到1还是2，由后面配置的算法自动路由
          t_student:
            actual-data-nodes: ds0.t_student_$->{1..2}  # 实际的数据表，由数据源+表名组成，用点号分隔，$->{1..2}表示表的后缀范围，这里只有两个表
            table-strategy:     # 分表策略配置
              standard:
                sharding-column: id   # 用哪个字段来分表（此处分片键为主键id）
                sharding-algorithm-name: table-alg   # 分表算法名（可自定义算法名称），后面将根据这个名称来配置具体的分表算法
            key-generate-strategy:    # 主键的生成策略
              column: id              # 主键id
              key-generator-name: pkey-alg           # 主键生成算法名（可自定义算法名称），后面将根据这个名字来配置具体的主键生成算法

        # 配置分片算法
        sharding-algorithms:
          table-alg:                  # 引用前面配置的分表算法名称
            type: INLINE
            props:
              algorithm-expression: t_student_$->{id%2+1}    # 根据主键取模然后加1来确定具体的那张表，$->{id%2+1} =》计算到底是1还是2，然后拼接到t_student_后面
        key-generators:
          pkey-alg:                  #  引用前面配置的主键生成算法名称
            type: SNOWFLAKE          #  雪花算法
    props:
      sql:
        show: true                   # 执行过程中展示sql

MVC层构建

此处为简单测试，简化MVC层构建，只引入mapper、controller进行处理

（1）User 相关操作（用于普通插入测试）

model 层

@Data
@NoArgsConstructor
@AllArgsConstructor
@With
public class User {
    private long id;
    private String name;
    private int age;
    private String createTime;
}

mapper 层

@Mapper
public interface UserMapper {
    @Insert("INSERT INTO `t_user` (`name`,`create_time`,`age`) VALUES (#{user.name},#{user.createTime},#{user.age})")
    int insert(@Param("user") User user);
}

controller 层

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

    @Autowired
    UserMapper userMapper;

    @RequestMapping("/addUser")
    public String addUser(@RequestParam("name") String name, @RequestParam("createTime") String createTime, @RequestParam("age") int age) {
        User user = new User()
                .withName(name)
                .withCreateTime(createTime)
                .withAge(age);
        userMapper.insert(user);
        return "success";
    }

}

（2）Student 相关操作（用于分表不分库插入测试）

model 层

@Data
@AllArgsConstructor
@NoArgsConstructor
@With
public class Student {
    private long id;
    private String name;
    private String createTime;
    private String gradeCode;
}

mapper 层

@Mapper
public interface StudentMapper {

    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @Insert("INSERT INTO t_student (`name`,`create_time`,`grade_code`) VALUES (#{student.name},#{student.createTime},#{student.gradeCode})")
    int insert(@Param("student") Student student);

    @Select("SELECT * FROM t_student WHERE id=#{id}")
    Student selectById(@Param("id") long id);

    @Select("SELECT * FROM t_student WHERE create_time >= #{start} AND create_time < #{end}")
    List<Student> queryByTime(@Param("start") String start, @Param("end") String end);
}

controller 层

@RestController
@RequestMapping("/camps/student")
public class StudentController {

    @Autowired
    StudentMapper studentMapper;

    @RequestMapping("/addStudent")
    public String add(@RequestParam("name") String name, @RequestParam("createTime") String createTime, @RequestParam("gradeCode") String gradeCode) {
        Student student = new Student()
                .withName(name)
                .withCreateTime(createTime)
                .withGradeCode(gradeCode);
        studentMapper.insert(student);
        return "success";
    }

    @RequestMapping("/queryStudentById")
    public Student queryStudentByTime(@RequestParam("id") long id) {
        Student student = studentMapper.selectById(id);
        return student;
    }
}

3.测试

分表不分库测试

# 浏览器访问测试添加数据
http://localhost:8080/camps/student/addStudent?name=东邪&createTime=2024-06-12%2012:07:46&gradeCode=first
http://localhost:8080/camps/student/addStudent?name=西毒&createTime=2024-06-12%2012:07:46&gradeCode=second
http://localhost:8080/camps/student/addStudent?name=南帝&createTime=2024-06-12%2012:07:46&gradeCode=third
http://localhost:8080/camps/student/addStudent?name=北丐&createTime=2024-06-12%2012:07:46&gradeCode=first

# 查看数据库信息
use db_camps;
select * from t_student_1;
select * from t_student_2;

# 浏览器访问数据（根据ID检索数据信息）
http://localhost:8080/camps/student/queryStudentById?id=1020113227024957441
# output
{
  "id": 1.0201132270249574e+18,
  "name": "北丐",
  "createTime": "2024-06-12 12:07:46",
  "gradeCode": "first"
}

不分表测试

前面测试的分表功能，对于t_student表在camps库里分成了两张表t_student_1和t_student_2，分表之后的插入和查询功能正常调试。那对于其它没有进行分表的数据表会产生影响嘛？一般的业务场景是之前是不分表的，现在要进行分表，不能因为改一个分表策略，而影响了其它所有的表，如果对其他业务操作逻辑产生影响的话引入分表组件的意义就得不偿失了

测试一下不分表功能：前面在camps库里创建的t_user表是没有分表的，测试一下t_user的插入功能是否正确

# 浏览器访问测试添加数据
http://localhost:8080/camps/user/addUser?name=%E9%BB%84%E8%93%89&createTime=2024-06-12%2012:07:46&age=18

# 查看数据库信息
use db_camps;
select * from t_user;

分库分表

1.环境准备

数据准备

CREATE DATABASE db_camps1;
USE db_camps1;
CREATE TABLE `t_student_1` (
                                `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增',
                                `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
                                `create_time` varchar(32) NOT NULL DEFAULT '' COMMENT '创建日期,yyyy-MM-dd HH:mm:ss',
                                `grade_code` varchar(32) NOT NULL DEFAULT '' COMMENT '年级,first,second,third,fourth',
                                PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8;
CREATE TABLE `t_student_2` (
                                `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增',
                                `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
                                `create_time` varchar(32) NOT NULL DEFAULT '' COMMENT '创建日期,yyyy-MM-dd HH:mm:ss',
                                `grade_code` varchar(32) NOT NULL DEFAULT '' COMMENT '年级,first,second,third,fourth',
                                PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8;


CREATE DATABASE db_camps2;
USE db_camps2;
CREATE TABLE `t_student_1` (
                               `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增',
                               `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
                               `create_time` varchar(32) NOT NULL DEFAULT '' COMMENT '创建日期,yyyy-MM-dd HH:mm:ss',
                               `grade_code` varchar(32) NOT NULL DEFAULT '' COMMENT '年级,first,second,third,fourth',
                               PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8;
CREATE TABLE `t_student_2` (
                               `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增',
                               `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
                               `create_time` varchar(32) NOT NULL DEFAULT '' COMMENT '创建日期,yyyy-MM-dd HH:mm:ss',
                               `grade_code` varchar(32) NOT NULL DEFAULT '' COMMENT '年级,first,second,third,fourth',
                               PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8;

2.项目构建

根据相关开发环境，初始化Springboot项目，然后按照下述步骤改造项目：

引入shardingsphere分表组件和数据库连接相关依赖（需考虑版本兼容性）
配置数据库连接和分库分表策略
构建MVC层，对外提供接口测试数据库连接访问

pom.xml 依赖构建

核心依赖参考配置

<properties>
  <java.version>1.8</java.version>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <spring-boot.version>2.7.6</spring-boot.version>
</properties>
<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
  </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>

  <!-- 引入分库分表组件 -->
  <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter -->
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <!--            <version>5.2.1</version>-->
    <version>5.0.0</version>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
  </dependency>
  <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
  </dependency>

</dependencies>
<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-dependencies</artifactId>
      <version>${spring-boot.version}</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>

application.yml 配置

分库分表策略&数据库连接配置

# server配置
server:
  port: 8080
spring:
  profiles:
    active: local

# 此处按照 不分库-垂直分表 不分库-水评分表等类型去区分配置，当想使用哪种方式，直接切换配置即可
mybatis:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

---
# 第二种：分库分表
spring:
  shardingsphere:
    datasource:
      names: ds1,ds2     # 自定义数据源的名称（如果由多个，用逗号分隔）
      ds1:               # 根据自定义的数据源名称，配置该数据源对应的连接信息
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/db_camps1?useUnicode=true&autoReconnect=true
        username: root
        password: 123456
      ds2:               # 根据自定义的数据源名称，配置该数据源对应的连接信息
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/db_camps2?useUnicode=true&autoReconnect=true
        username: root
        password: 123456
    rules:              # 配置分库分表策略
      sharding:
        tables:
          t_student:   # 分表配置：配置逻辑表名，比如有两张分表t_student_0,t_student_1,这里写t_student即可，数据具体保存到1还是2，由后面配置的算法自动路由
            actual-data-nodes: ds$->{1..2}.t_student_$->{1..2}   # 实际的数据表，由数据源+表名组成，用点号分隔，$->{1..2}表示表的后缀范围（此处测试有2个库、两个表）
            database-strategy:   # 分库策略
              standard:
                sharding-column: id     # 分库所用的字段（此处使用【主键id】）
                sharding-algorithm-name: db-alg   # 自定义分库算法名，后面将根据这个名字来配置具体的分库算法
            table-strategy:       # 分表策略配置
              standard:
                sharding-column: id     # 用那个字段来分表（此处使用【主键id】）
                sharding-algorithm-name: table-alg  # 自定义分表算法名，后面将根据这个名字来配置具体的分表算法
            key-generate-strategy:     # 主键的生成策略
              column: id               # 主键id
              key-generator-name: pkey-alg    # 自定义主键生成算法名，后面将根据这个名字来配置具体的主键生成算法

        # 配置分片算法
        sharding-algorithms:
          db-alg:              # 根据前面配置的分库算法名称，此处定义分库算法配置
            type: INLINE
            props:
              algorithm-expression: ds$->{id%2+1}  # 根据主键取模来确定具体的哪个库，$->{id%2+1}其实就是计算到底是1还是2，然后拼接到ds后面（这里ds是个别名，实际上引用对应的数据库名）
          table-alg:           # 根据前面配置的分表算法名称，此处定义分表算法配置
            type: INLINE
            props:
              algorithm-expression: t_student_$->{id%2+1}   # 根据主键取模然后加1来确定具体的那张表，$->{id%2+1}其实就是计算到底是1还是2，然后拼接到t_student_后面

        key-generators:
          pkey-alg:          #  前面配置的主键生成算法名称
            type: SNOWFLAKE  #  雪花算法
    props:
      sql:
        show: true            # 执行过程中展示sql

MVC层构建

此处为简单测试，简化MVC层构建，只引入mapper、controller进行处理

（1）Student 相关操作

mode 层

@Data
@AllArgsConstructor
@NoArgsConstructor
@With
public class Student {
    private long id;
    private String name;
    private String createTime;
    private String gradeCode;
}

mapper 层

@Mapper
public interface StudentMapper {

    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @Insert("INSERT INTO t_student (`name`,`create_time`,`grade_code`) VALUES (#{student.name},#{student.createTime},#{student.gradeCode})")
    int insert(@Param("student") Student student);
}

controller 层

@RestController
@RequestMapping("/camps/student")
public class StudentController {

    @Autowired
    StudentMapper studentMapper;

    @RequestMapping("/addStudent")
    public String add(@RequestParam("name") String name, @RequestParam("createTime") String createTime, @RequestParam("gradeCode") String gradeCode) {
        Student student = new Student()
                .withName(name)
                .withCreateTime(createTime)
                .withGradeCode(gradeCode);
        studentMapper.insert(student);
        return "success";
    }
}

3.测试

分库分表测试

# 浏览器访问测试添加数据
http://localhost:8080/camps/student/addStudent?name=东邪&createTime=2024-06-12%2012:07:46&gradeCode=first
http://localhost:8080/camps/student/addStudent?name=西毒&createTime=2024-06-12%2012:07:46&gradeCode=second
http://localhost:8080/camps/student/addStudent?name=南帝&createTime=2024-06-12%2012:07:46&gradeCode=third
http://localhost:8080/camps/student/addStudent?name=北丐&createTime=2024-06-12%2012:07:46&gradeCode=first

# 查看数据库信息
USE db_camps1;
select * from t_student_1;
select * from t_student_2;

USE db_camps2;
select * from t_student_1;
select * from t_student_2;

# 测试结果
db_camps1：东邪、南帝 添加到数据库db_camps1的表t_student_1
db_camps2：西毒、北丐 添加到数据库db_camps2的表t_student_2

实践总结

综合上述实践可知，通过引入分库分表组件，以最小成本改造项目。在不影响原有业务逻辑的基础上完成分库分表的项目改造，其改造核心在于将原有的数据操作拆到对应拆分的库表中进行操作。核心步骤总结如下：

【1】数据库改造
- 初始化要拆分的库表信息
【2】项目改造
- pom.xml：项目中引入shardingsphere分库分表组件
- application.yml：配置分库分表策略
【3】测试
- 构建MVC层进行接口测试分库分表结果是否正常响应
- 如果是基于现有项目接口改造，实际上并不需要调整业务逻辑部分，只需要调整数据库操作策略即可（这种方式对业务代码的侵入性最低，改造数据库分库分表策略）

昵称

邮箱

网址

按正序
按倒序
按热度

MySQL-高可用篇-②分库分表实践

预览: