SpringBoot+ShardingSphere-分库分表教程（一）

日常使用数据库的时候，更多的时间是在关心业务功能的实现，为了尽快完成新版本的发布上线，通常在项目初期不太会去在意数据库的压力和性能问题。在服务上线一段时间之后，就会发现当初设计存在着很多的不足，这都是项目研发的正常过程。对于有经验的程序员，在项目设计初期就会想到将来有一天会遇到这些问题，所以就从一开始就将代码写的比较完善，这也是提现大龄程序员优势的地方。

有一种常见的现象就是，项目上线初期，由于用户量不多，所以数据库中的数据也不会太多，服务运行的非常顺畅，但是随着生产数据的积累，很快就导致了数据库性能瓶颈的到来。这时候我们最先想到的是提升数据库的资源，加大内存，加大磁盘，从而度过数据库压力的难关，但是这毕竟是暂时的，比如一张表里的数据量迅速的增长，而且你又不能删除这里面的数据，终究有一天会让这张表爆掉。

以mysql为例，如果单张表的行数超过500万行的时候，通常就能感受到非常明显的性能衰减，这点不得不佩服oracle动辄几亿的单表查询能力，但是没办法，两者价格的差距也是性能的差距。如何应对这种持续增长的单表数据呢？一种常用的方式就是分库分表，就是把一张巨大的表，按照一定的规则分到不同的表里去，这样每张分表的数据量就小了，从而保证每个分表的性能，如果分表也不足以支撑大数据量，就通过分库，把数据量分到多个库里去，从而支撑住业务功能。

shardingsphere是诸多分库分表工具中比较优秀的一款，在我经历过的公司中，也应用在了生产服务中，虽然使用过程中遇到的坑也不少，不过总体来说，还是足够支撑业务功能。我们首先介绍一下，分库和分表是两个截然不同的功能，虽然总混在一起说，分表只要我们在Springboot中引入shardingsphere-jdbc这个依赖库即可，但是分库就要单独部署一个服务shardingsphere-proxy，其他服务连接shardingsphere-proxy，从而实现分库的功能。

我们先用shardingsphere-jdbc来进行单库的分表，分表常用的规则有两种，一种是通过时间进行分表，比如一个月一张表，或者一周一张表，另外一种就根据列的数值进行分表，比如id是1-1000用一张表，1001-2000用一张表，分表的规则要按照业务功能去切分，无论哪种分表策略，最终的目标就是让数据均匀的分布在各个分表中。

1、创建数据表

我们先创建一张存储消息的表，过去我们创建消息表就是一张，比如叫sys_message，但是现在我们是用分表，所以就要创建一批表，我们设定消息表使用时间分表策略，每7天一张表，从2025年1月1日开始，所以我们就要创建一张分表sys_message_20250101，然后按照每7天一张表创建出多干个消息分表，这里注意，shardingsphere的分表默认是不自动创建表的，所以我们先手动创建，我制作了一个存储过程可以快速创建出多张sys_message分表。

sys_message_20250101这一张是分表的基础表，没有启动会报错，其他的分表即使没有，启动的时候也不报错，但是用到了就会抛出异常。

CREATE TABLE `sys_message_20250101` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `msg` longblob COMMENT '消息内容',
  `version` int NOT NULL DEFAULT '1' COMMENT '版本号',
  `is_logic_delete` int NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  `create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1989 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='系统-消息表';

自动创建从2025年1月1日起到一年后的分表存储过程：

CREATE PROCEDURE `sp_generate_message_tables`(IN start_date DATE)
BEGIN
    DECLARE end_date DATE DEFAULT DATE_ADD(CURRENT_DATE(), INTERVAL 1 YEAR);
    DECLARE item_date DATE;
    DECLARE table_name VARCHAR(50);

    SET item_date = DATE_ADD(start_date, INTERVAL 7 DAY);

    WHILE item_date <= end_date DO
        SET table_name = CONCAT('sys_message_', DATE_FORMAT(item_date, '%Y%m%d'));
        SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', table_name, ' LIKE sys_message_', DATE_FORMAT(start_date, '%Y%m%d'));
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET item_date = DATE_ADD(item_date, INTERVAL 7 DAY);
    END WHILE;

    SELECT CONCAT('分表生成完成，时间范围：', start_date, ' 至 ', end_date) AS result;
END

2、创建项目shardingsphere-demo

创建一个新项目shardingsphere-demo，并且在pom.xml文件中引入MyBatis-Plus、Shardingsphere和mysql依赖。

<dependencies>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>3.5.10.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-jsqlparser</artifactId>
            <version>3.5.10.1</version>
        </dependency>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.4.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc</artifactId>
            <version>5.5.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.24</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>2.0.54</version>
        </dependency>

        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-openapi3-jakarta-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

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

3、创建MyBatis-Plus的各个类

虽然各个分表的表名是不一样的，但是在代码里我们并不用去记录这些表名，而是使用逻辑表名sys_message进行操作，让Shardingsphere去自动帮我们定位真正的分表。

MessageDO：

package com.mj.shardingsphere.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * 系统-消息表
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "sys_message")
public class MessageDO implements Serializable {
    /**
     * id
     */
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;

    /**
     * 消息value
     */
    @TableField(value = "msg")
    private String msg;

    /**
     * 版本号
     */
    @Version
    @TableField(value = "version")
    private Integer version;

    /**
     * 逻辑删除
     */
    @TableLogic
    @TableField(value = "is_logic_delete")
    private Integer logicDelete;

    /**
     * 创建人
     */
    @TableField(value = "create_by", fill = FieldFill.INSERT)
    private String createBy;

    /**
     * 创建时间
     */
    @TableField(value = "create_time", fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    /**
     * 修改人
     */
    @TableField(value = "update_by", fill = FieldFill.INSERT_UPDATE)
    private String updateBy;

    /**
     * 修改时间
     */
    @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;

    private static final long serialVersionUID = 1L;
}

MessageMapper：

package com.mj.shardingsphere.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mj.shardingsphere.entity.MessageDO;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface MessageMapper extends BaseMapper<MessageDO> {
}

MessageMapper.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.mj.shardingsphere.dao.MessageMapper">
  <resultMap id="BaseResultMap" type="com.mj.shardingsphere.entity.MessageDO">
    <!--@mbg.generated-->
    <!--@Table sys_message-->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="msg" jdbcType="VARCHAR" property="msg" />
    <result column="version" jdbcType="INTEGER" property="version" />
    <result column="is_logic_delete" jdbcType="INTEGER" property="logicDelete" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, msg, version, is_logic_delete, 
    create_by, create_time, update_by, update_time
  </sql>
</mapper>

4、Shardingsphere配置：

创建一个文件sharding.yml，上半部分就是数据库的配置，将数据源和连接池交给了Sharding进行管理，Springboot里面就不用再配置了。sys_message_algorithm决定了分表的策略，按照时间分表的时候，要定好分表的时间段，可以写一个很长的时间。sharding-suffix-pattern是分表的后缀格式，正是因为有这个配置，Sharding才能很好的把所有的分表整合成了一个逻辑表让我们用分表的时候就像只有一张表一样。最后就是配置分表时间是7天，这时候数据库里的分表也要严格按照7的跨度去生成。

# 模式配置
mode:
  type: Standalone
  repository:
    type: JDBC
# 数据源配置
dataSources:
  sharding:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.18.42:3306/sharding?useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    druid:
      test-on-borrow: true
      validation-query: SELECT 1 FROM DUAL
      web-stat-filter:
        enabled: true
      stat-view-servlet:
        enabled: true
        login-username: druid
        login-password: 12345
      pool-prepared-statements: false
      max-pool-prepared-statement-per-connection-size: 20
# 规则配置
rules:
  # 单表配置
  - !SINGLE
    tables:
      - sharding.*
  # 数据分片
  - !SHARDING
    tables:
      sys_message:
        actualDataNodes: sharding.sys_message_${20250101..20991231}
        tableStrategy:
          standard: # 用于单分片键的标准分片场景
            shardingColumn: create_time
            shardingAlgorithmName: sys_message_algorithm
        keyGenerateStrategy: # 分布式序列策略
          column: id
          keyGeneratorName: snowflake
        auditStrategy: # 分片审计策略
          auditorNames: # 分片审计算法名称
            - sharding_key_required_auditor
          allowHintDisable: true
    # 分片算法配置
    shardingAlgorithms:
      sys_message_algorithm:
        type: INTERVAL
        props:
          datetime-pattern: yyyy-MM-dd HH:mm:ss
          datetime-lower: "2025-01-01 00:00:00"  # 添加引号确保格式正确
          datetime-upper: "2099-12-31 23:59:59"  # 添加引号确保格式正确
          sharding-suffix-pattern: yyyyMMdd
          datetime-interval-amount: 7
          datetime-interval-unit: DAYS

    # 分布式序列算法配置
    keyGenerators:
      snowflake:
        type: SNOWFLAKE

    # 分片审计算法配置
    auditors:
      sharding_key_required_auditor:
        type: DML_SHARDING_CONDITIONS

props:
  sql-show: true
#  sql-simple: false
#  max-connections-size-per-query: 1
#  check-table-metadata-enabled: false

修改一下application.yml文件，把sharding.yml文件引入进去。

spring:
  application:
    name: shardingsphere-demo
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    # 引入shardingsphere
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding.yml?placeholder-type=environment
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: 'SELECT 1 FROM DUAL'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    filters: 'stat,wall'
    connectionProperties: 'druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000'
    useGlobalDataSourceStat: true

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto
      # ????
      logic-delete-field: deleted
      logic-delete-value: 1
      logic-not-delete-value: 0
  mapper-locations: classpath:/mapper/**.xml

springdoc:
  swagger-ui:
    path: /swagger-ui.html
    tags-sorter: alpha
    operations-sorter: alpha
  api-docs:
    path: /v3/api-docs
  group-configs:
    - group: 'default'
      paths-to-match: '/**'
      packages-to-scan: com.mj.shardingsphere
knife4j:
  enable: true
  production: false
  setting:
    language: zh_cn

5、编写服务类

MessageService：

package com.mj.shardingsphere.service;

import com.mj.shardingsphere.entity.MessageDO;

import java.util.List;

public interface MessageService {
    String sendMessage(String message);
    List<MessageDO> getMessages();
}

MessageServiceImpl：

这里要注意查询的使用，由于shardingsphere是从分表里进行操作，所以查询的时候也是从所有的分表里进行查询，这是一件很恐怖的事情，所以一定要默认带着分片键并且固定一个区间，让它从有限的分表里进行查询，防止因为查询太多数据而导致服务宕机。这里可能会给业务功能带来一定的困扰，比如就是不知道查询多久数据，这时候必须要在业务功能上做让步。

package com.mj.shardingsphere.service.impl;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.mj.shardingsphere.dao.MessageMapper;
import com.mj.shardingsphere.entity.MessageDO;
import com.mj.shardingsphere.service.MessageService;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;

@AllArgsConstructor
@Service
public class MessageServiceImpl implements MessageService {
    private final MessageMapper messageMapper;

    @Override
    public String sendMessage(String message) {
        MessageDO messageDO = new MessageDO();
        messageDO.setMsg(message);
        messageDO.setCreateBy("SYSTEM");
        messageDO.setCreateTime(LocalDateTime.now());
        messageDO.setUpdateBy("SYSTEM");
        messageDO.setUpdateTime(LocalDateTime.now());
        messageMapper.insert(messageDO);
        return "ok";
    }

    @Override
    public List<MessageDO> getMessages() {
        //查询分表的时候，一定要使用分片键去固定分表的区间，防止查询太多的表
        return messageMapper.selectList(Wrappers.lambdaQuery(MessageDO.class)
                .ge(MessageDO::getCreateTime, LocalDateTime.now().minusMonths(1))
                .le(MessageDO::getCreateTime, LocalDateTime.now()));
    }
}

6、测试接口

MessageController：

package com.mj.shardingsphere.controller;

import com.mj.shardingsphere.entity.MessageDO;
import com.mj.shardingsphere.service.MessageService;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@AllArgsConstructor
@RequestMapping("/message")
@RestController
public class MessageController {
    private final MessageService messageService;

    @GetMapping("/add")
    public String add() {
        return messageService.sendMessage("message-" + System.currentTimeMillis());
    }

    @GetMapping("/list")
    public List<MessageDO> lst() {
        return messageService.getMessages();
    }
}

http://127.0.0.1:8080/message/add 通过调用add接口，通过日志和数据库，能发现进入到对应时间段内的那个分表了。

http://127.0.0.1:8080/message/list 查询接口查询了最近一个月的数据