使用ShardingJDBC实现按时间维度分表轻松支撑千万级数据

---

前言

之前在公司开发的一个产品，数据量巨大，疫情期间更是单月数据量增长超过100万，我们的单机MySQL数据库查询速度已经完全力不从心了，于是走上了分表的道路。我们的产品目标客户主要是学校，根据产品经理给的业务需求，决定按学期分表，即按主表数据入库时间按学期存放到对应分表，将原来超大的主表拆分成无数的小表，拆分后单表最大数据量为300万。
经过一段时间的考察研究决定使用ShardingJDBC来简化分表后的业务开发工作，ShardingJDBC的主要作用是根据设定好的路由规则将SQL语句路由到对应的数据库及分表上，由于我们使用的是单库分表，因此只涉及表的路由。在做业务开发的过程中踩过一些坑，也有了一些经验，于是在这里把代码抽取出来做成Demo来做一个分享。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、框架搭建

先介绍一下Demo使用的框架和版本，主要是SpringBoot全家桶2.2.6版，MyBatisPlus的2.3.3版以及我们今天的主角ShardingJDBC的4.0.1版本。
主要说下ShardingJDBC框架的集成与配置，首先在pom.xml引入依赖：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>

接下来在application.yml中加入配置：

spring:
  shardingsphere:
    datasource:
      names: ds
      ds:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/shardingjdbc?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&tinyInt1isBit=false
        username: root
        password: admin123
    sharding:
      tables:
        t_order:
          actual-data-nodes: ds.t_order_$->{2020..2021}_$->{1..12}
          table-strategy:
            standard:
              sharding-column: create_time
              precise-algorithm-class-name: io.ian.demo.core.shardingjdbc.DatePreciseShardingAlgorithm
              range-algorithm-class-name: io.ian.demo.core.shardingjdbc.DateRangeShardingAlgorithm
        t_order_item:
          actual-data-nodes: ds.t_order_item_$->{2020..2021}_$->{1..12}
          table-strategy:
            standard:
              sharding-column: order_create_time
              precise-algorithm-class-name: io.ian.demo.core.shardingjdbc.DatePreciseShardingAlgorithm
              range-algorithm-class-name: io.ian.demo.core.shardingjdbc.DateRangeShardingAlgorithm
      binding-tables:
        - t_order,t_order_item
      defaultDataSourceName: ds
    props:
      sql:
        show: true

由于我们是单库分表，因此只配置一个数据源ds。主要说下表的配置，先看表结构：

t_order和t_order_item表以order_id字段关联，为一对多关系。t_order_item表的order_create_time字段值与关联的t_order表的create_time值相同，create_time字段和order_create_time字段分别为两表的分表字段。在ShardingJDBC中t_order和t_order_item表均为逻辑表，实际的表是例如t_order_2020_6这种带月后缀的表，这里我们为了简化分表逻辑进行了按月分表。具体的配置方式可以参考官方配置手册。

二、代码编写

下面我们着重看一下自定义的分表逻辑类DatePreciseShardingAlgorithm和DateRangeShardingAlgorithm：

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;
import java.util.Date;

public class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
        Date date = preciseShardingValue.getValue();
        String suffix = ShardingUtils.getSuffixByYearMonth(date);
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(suffix)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException("未找到匹配的数据表");
    }
}

DatePreciseShardingAlgorithm实现了PreciseShardingAlgorithm接口，用于条件精确匹配时的表路由（=和in）。availableTargetNames就是我们在配置中配的actual-data-nodes，ds.t_order_$->{2020…2021}_$->{1…12}表示实际表为ds.t_order_2020_1到ds.t_order_2021_12共两年24张表。preciseShardingValue.getValue()获取到的是我们执行sql传递的时间参数，我们在这里用Date去做查询条件，不要使用String的日期。

import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.*;

@Slf4j
public class DateRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
        List<String> list = new ArrayList<>();
        log.info("availableTargetNames : " + availableTargetNames);
        log.info(rangeShardingValue.toString());
        Range<Date> valueRange = rangeShardingValue.getValueRange();
        Date lowerDate = valueRange.lowerEndpoint();
        Date upperDate = valueRange.upperEndpoint();
        String lowerSuffix = ShardingUtils.getSuffixByYearMonth(lowerDate);
        String upperSuffix = ShardingUtils.getSuffixByYearMonth(upperDate);
        TreeSet<String> suffixList = ShardingUtils.getSuffixListForRange(lowerSuffix, upperSuffix);
        for (String tableName : availableTargetNames) {
            if (containTableName(suffixList, tableName)) {
                list.add(tableName);
            }
        }
        log.info("match tableNames-----------------------" + list.toString());
        return list;
    }

    private boolean containTableName(Set<String> suffixList, String tableName) {
        boolean flag = false;
        for (String s : suffixList) {
            if (tableName.endsWith(s)) {
                flag = true;
                break;
            }
        }
        return flag;
    }
}

DateRangeShardingAlgorithm 实现了RangeShardingAlgorithm接口，用于条件范围匹配时的路由（between、<、>等）。valueRange.lowerEndpoint()和valueRange.upperEndpoint()分别获取范围的上下边接，我们这两个时间的跨度去计算目标表的后缀，然后在可用的表中选取路由的目标表。

下面我们来看下增删改查的业务代码与不分表时有哪些变化。

新增t_order数据时我们给createTime赋值，在执行sql时会触发DatePreciseShardingAlgorithm中的精确匹配，直接把数据插入到createTime对应的分表，在代码上完全没有任何改动。
修改、单条删除、单条查询在分表前查询条件只需要id主键即可，但在分表后我们应该加上时间参数，可以是精确时间也可以是时间段，这样才能快速找到目标表，否则会在所有分表执行sql导致性能十分低下。

最麻烦的分页查询

首先希望大家能看下官方文档针对分页的章节。
我们为了获得总数据条数使用的count语句将会对所有分表进行查询然后归并结果，根据笔者在产品开发中的实际经验，当数据量到百万千万级别时，这个查询耗费的时间是不可接受的，因此在数据量巨大时，传统的分页查询方式是不可取的。为了解决这个问题从业务上我们可以不展示总数据量和总页数，而改用无限下拉滚动的方式来展示分页数据，但如果产品一定要展示呢？由于我们是按时间分表，因此从业务上看，除了当前月，其他的分表数据总量应该已经固定不再增长了，因此可以把当前月之前月份的数据总数做统计并存入中间表，这个数据再加上当前月的分表数据量就可以算出总数据量了，有了总数计算总页数也就简单了。但事实上我们的需求一般不会是对所有数据进行分页查询，而是有条件的对数据进行分页查询，比如本例中就是根据用户名进行订单的分页查询，那么我们的总数统计就应该按用户名去统计存入中间表。

下面简单介绍一下无限下拉滚动分页查询的实现：

/**
 * 从第二页开始lastCreateTime和lastRowNum为必选参数
 */
@ApiOperation(value = "无限滚动分页查询", httpMethod = "GET")
@ApiImplicitParams({
        @ApiImplicitParam(name = "lastCreateTime", value = "上一页最后一条数据的创建时间", dataType = "String", paramType = "query"),
        @ApiImplicitParam(name = "lastRowNum", value = "上一页最后一条数据的行号", dataType = "int", paramType = "query"),
        @ApiImplicitParam(name = "size", value = "每页条数", dataType = "int", paramType = "query"),
        @ApiImplicitParam(name = "userName", value = "用户名", dataType = "String", paramType = "query")
})
@GetMapping("/listPageByTime")
public ResponseEntity<RestResult> listPageByTime(String lastCreateTime, Integer lastRowNum, Integer size, String userName) {
    return ResponseEntity.ok(RestResult.getSuccessRestResult(orderService.listPageByTime(lastCreateTime, lastRowNum, size, userName)));
}

查询第一页数据时只需要提供每页条数参数即可，如果需要根据用户名查询可以加入用户名参数。从第二页开始就需要传入上一页最后一条数据的创建时间，以及上一页最后一条数据的行号。

@Override
public List<Order> listPageByTime(String lastCreateTime, Integer lastRowNum, Integer size, String userName) {
    //lastCreateTime 有助于快速定位当前查询的分表 ，如果是第一页则可不传，默认使用当前时间
    Date date = StringUtils.isBlank(lastCreateTime) ? new Date() : DateUtils.parseTime(lastCreateTime);
    String suffix = ShardingUtils.getSuffixByYearMonth(date);
    int resultSize = size == null ? 10 : size;
    //rowNum用于获取当前页数据的起始位置，如果是第一页可以不传，默认为0
    int rowNum = lastRowNum == null ? 0 : lastRowNum;
    List<Order> orderList = baseMapper.listByRowNum(suffix, resultSize, rowNum, userName);
    if (orderList.size() > 0) {
        while (orderList.size() < resultSize) { //查询出的数据不足 找更早的分表补足
            if ("2020_6".equals(suffix)) {    //假设最早的分表为 t_order_2020_6
                break;
            }
            suffix = ShardingUtils.getPrevSuffix(suffix);
            List<Order> tempOrderList = baseMapper.listByRowNum(suffix, resultSize - orderList.size(), 0, userName);
            if (tempOrderList.size() > 0) {
                orderList.addAll(tempOrderList);
            }
        }
        //获取orderList中数据的时间范围 查询子表数据
        Wrapper<OrderItem> orderItemWrapper = new EntityWrapper<OrderItem>()
                .between("order_create_time", orderList.get(orderList.size() - 1).getCreateTime(), orderList.get(0).getCreateTime());
        this.appendOrderItem(orderList, orderItemWrapper);
    }
    return orderList;
}

上一页的最后一条数据的创建时间用于计算上次查询结束时数据所在的分表，此次查询也从该分表开始。如果该分表剩余数据不足每页条数，则继续查上一个月的分表，直到查询出的数据达到每页条数或已超过最早月份的分表。

SELECT
    A.*
FROM
    (
    SELECT
        @rownum := @rownum + 1 AS rowNum,
        t.*
    FROM
        t_order_${suffix} t,
        ( SELECT @rownum := 0 ) r
    <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="userName != null and userName != ''">
           AND t.user_name = #{userName}
        </if>
    </trim>
    ORDER BY
        create_time DESC,id
    ) A
WHERE
    A.rowNum > #{rowNum}
    LIMIT #{size}

从上面SQL语句可以看出，行号指的是当前分表里数据排序后的序号。因此我们可以根据上一页最后一条数据行号找到当前页的数据起始位置。
该分页查询方式经过实际测试在单分表数据量超过百万，总数据量超过千万级别时仍然可以在1秒内完成请求响应，当然查询条件需要建立索引。

---

三、题外话

强烈建议在数据量特别巨大的表使用长整型作为主键数据类型，在数据量超过百万同时用长整型数据作为外键查询时，在都建立索引的情况下，查询速度比字符类型外键快上十倍到百倍！当然长整型作为主键在传递到前端时会有JS精度丢失问题，这个问题可以通过转字符串传递解决。

源码下载

觉得有用的朋友顺手给个STAR吧