这个图标让我想起了火狐
## Java分库分表

概念

对于分库分表的概念，建议查看这篇文章：[00 如何正确学习一款分库分表开源框架？.md (lianglianglee.com)](https://learn.lianglianglee.com/专栏/ShardingSphere 核心原理精讲-完/00 如何正确学习一款分库分表开源框架？.md)，这篇文章大致讲述了分库分表的起源、原因以及各种解决方案。

本文主要讲述如何使用 ShardingSphere ，通过Java代码来分库分表，对于该组件的详细说明请参考概览 :: ShardingSphere (apache.org)。

引入Maven

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<properties>
    <sharding-sphere.version>4.1.1</sharding-sphere.version>
    <shardingsphere.version>5.1.0</shardingsphere.version>
</properties>

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

注意：引入该组件后，如果你使用了其他数据库连接池（比如阿里的Durid），切记不要引入druid-spring-boot-starter，亲测项目无法启动

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<properties>
	<druid.version>1.1.10</druid.version>
</properties>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>${druid.version}</version>
    <exclusions>
        <exclusion>
            <groupId>com.sun</groupId>
            <artifactId>tools</artifactId>
        </exclusion>
        <exclusion>
            <groupId>com.sun</groupId>
            <artifactId>jconsole</artifactId>
        </exclusion>
    </exclusions>
</dependency>

原因如下：

  1. 因为数据连接池的 starter（比如 druid）可能会先加载并且其创建一个默认数据源，这将会使得 ShardingSphere-JDBC 创建数据源时发生冲突。
  2. 解决办法为，去掉数据连接池的 starter 即可，ShardingSphere-JDBC 自己会创建数据连接池。

YML配置

背景：我想对两张表[t_platform_medical_record] 和 [t_platform_medical_record_result] 进行分库分表，规则为每月分表，分为2个库。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
spring:
    shardingsphere:
        datasource:
          names: ds1,ds2 #想分几个库就顺序写
          ds1:
            type: com.alibaba.druid.pool.DruidDataSource #换成你自己使用的连接池
            driver-class-name: com.mysql.cj.jdbc.Driver #换成你使用的数据源
            url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
            username: root
            password: 123465
          ds2:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
            username: root
            password: 123465
        sharding:
          tables:
            t_platform_medical_record: #第一个想要分表的表名
              actual-data-nodes: ds$->{1..2}.t_platform_medical_record_$->{2022..2022}_$->{1..12} #分表后的列表，采用Groovy语法
              database-strategy: #分片分库策略
                inline: #inline分库策略
                  sharding-column: id #分库字段
                  algorithm-expression: ds$->{id%2+1} #分库规则
              table-strategy: #分片分表策略
                standard: #standard标准策略
                  sharding-column: out_hospital_date #分表字段
                  precise-algorithm-class-name: com.xxx.TableShardingAlgorithm #精确查询分片算法包名
                  range-algorithm-class-name: com.xxx.DateRangeShardingAlgorithm #范围查询分片算法包名
              key-generator: #主键策略
                column: id #主键列
                type: SNOWFLAKE #自动生成主键策略，雪花id
                props:
                  worker:
                    id: 1 # 机器ID，用于位运算
            t_platform_medical_record_result:  #第二个想要分表的表名
              actual-data-nodes: ds$->{1..2}.t_platform_medical_record_result_$->{2022..2022}_$->{1..12}
              database-strategy:
                inline:
                  sharding-column: id
                  algorithm-expression: ds$->{id%2+1}
              table-strategy:
                standard:
                  sharding-column: out_hospital_date
                  precise-algorithm-class-name: com.xxx.algorithm.TableShardingAlgorithm
                  range-algorithm-class-name: com.xxx.algorithm.DateRangeShardingAlgorithm
              key-generator:
                column: id
                type: SNOWFLAKE
                props:
                  worker:
                    id: 1 # 机器ID，用于位运算
          props:
            sql:
              show: true #显示sql

分片算法

由于我需要按照时间分表，因此我们采用 standard标准策略，并指定时间精确查询与范围查询时，能成功路由对应库表，下面是对应的算法，请根据自己的情况自定义。

精确时间路由算法

主要应对sql中，=、in

P.S. 如果你的项目没有引入Lombok，请自行声明Logger

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package com.xxx.algorithm;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

/**
 * 标准分片-时间分片算法-等值时间路由
 * P.S. PreciseShardingAlgorithm<String> 这里根据实际情况声明，比如 PreciseShardingAlgorithm<Date>
 */
@Slf4j
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        //真实数据库节点
        log.debug("actual db -> [{}]", availableTargetNames);

        //逻辑表、分片的字段名、分片数据字段值
        log.debug("logicTable -> [{}]  shardingColumn -> [{}]  shardingColumnValue -> [{}]", shardingValue.getLogicTableName(), shardingValue.getColumnName(), shardingValue.getValue());

        //获取表名前缀
        String tb_name = shardingValue.getLogicTableName();

        //根据日期分表
        String value = shardingValue.getValue();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = null;
        try {
            date = sdf.parse(value);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        String year = String.format("%tY", date);
        String mon = String.valueOf(Integer.parseInt(String.format("%tm", date)));
        //String dat = String.format("%td", date);

        // 选择表
        tb_name = String.format("%s_%s_%s", tb_name, year, mon);
        log.debug("finalShardingTable -> [{}]", tb_name);

        // 存在即返回
        for (String each : availableTargetNames) {
            if (each.equals(tb_name)) {
                return each;
            }
        }

        throw new IllegalArgumentException("未找到匹配的数据表");
    }
}

范围时间路由算法

主要应对sql中，>=、<=、between ... and ...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
package com.xxx.algorithm;

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) {

        //真实数据库节点
        log.debug("actual db -> [{}]", availableTargetNames);

        //逻辑表、分片的字段名、分片数据字段值
        log.debug("logicTable -> [{}]  shardingColumn -> [{}]  shardingColumnValueRange -> [{}]",
                rangeShardingValue.getLogicTableName(), rangeShardingValue.getColumnName(), rangeShardingValue.getValueRange());

        List<String> list = new ArrayList<>();
        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);
        log.debug("finalShardingTable -> [{}_{}]", rangeShardingValue.getLogicTableName(), suffixList);
        for (String tableName : availableTargetNames) {
            if (containTableName(suffixList, tableName)) {
                list.add(tableName);
            }
        }
        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;
    }
}

对应的Util工具如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.xxx.utils;

import org.apache.commons.lang3.StringUtils;

import java.util.Calendar;
import java.util.Date;
import java.util.TreeSet;

public class ShardingUtils {


    public static TreeSet<String> getSuffixListForRange(String lowerSuffix, String upperSuffix) {
        TreeSet<String> suffixList = new TreeSet<>();
        if (lowerSuffix.equals(upperSuffix)) { //上下界在同一张表
            suffixList.add(lowerSuffix);
        } else {  //上下界不在同一张表  计算间隔的所有表
            String tempSuffix = lowerSuffix;
            while (!tempSuffix.equals(upperSuffix)) {
                suffixList.add(tempSuffix);
                String[] ym = tempSuffix.split("_");
                Date tempDate = DateUtils.parse(ym[0] + (ym[1].length() == 1 ? "0" + ym[1] : ym[1]), "yyyyMM");
                Calendar cal = Calendar.getInstance();
                cal.setTime(tempDate);
                cal.add(Calendar.MONTH, 1);
                tempSuffix = ShardingUtils.getSuffixByYearMonth(cal.getTime());
            }
            suffixList.add(tempSuffix);
        }
        return suffixList;
    }

    public static String getSuffixByYearMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.YEAR) + "_" + (calendar.get(Calendar.MONTH) + 1);
    }

    public static String getPrevSuffix(String suffix) {
        if (StringUtils.isBlank(suffix)) {
            return getSuffixByYearMonth(new Date());
        }
        String[] arr = suffix.split("_");
        if ("1".equals(arr[1])) {
            return (Integer.valueOf(arr[0]) - 1) + "_12";
        } else {
            return arr[0] + "_" + (Integer.valueOf(arr[1]) - 1);
        }
    }

}

测试

插入

在创建好对应的表之后，我们在表t_platform_medical_record中插入一条数据（时间为”2022-05-05 12:00:00”），假设插入后的id为728988040994754561

不难发现，这个id是奇数，因此被分库在ds2也就是test2数据库，原因是我们的分库策略声明了是根据id取余数加一来决定的( algorithm-expression: ds$->{id%2+1} #分库规则)

根据时间可知，它被分表在t_platform_medical_record_2022_5这张表内

综上所述，这行记录最终分库分表在test2.t_platform_medical_record_2022_5里。

查询

对于查询，我们只需要在查询语句使用范围查询即可，你会发现，ShardingSphere会根据实际的分库依次执行，由于我分了两个库，因此，我的范围时间路由算法会执行两遍，我猜测，最终的结果也是这两次执行的并集。

思考

这样的分库分表还有哪些优化的地方？

对于代码而言，我们还未考虑分布式、未考虑主从模式读写分离、未考虑本地事务与分布式事务、未考虑时钟回拨。

对于查询而言，某些计数类的查询(count)，分页类查询(limit)没有对应的优化，比如计数查询在分库分表后的效率如何（可以根据业务特性，如果是按照时间分片，那不是这个时间段的数据是否还会有变化，如果没有变化，可以将执行结果单独存表，并单独计算当月数据后累加即可），比如分页在分库分表后该如何处理？（1、将分页模糊化，类比聊天记录，根据雪花id的唯一性和有序性，将limit 10 offset 1000 化为 id > xxxxx limit 5，这种方式能减少网络消耗，2、对于limit 1000,10这种查询，ShardingSphere将sql优化为limit 0,1010，对于数据库而言，同样是扫描1010行数据，shardingsphere巧妙地利用流式处理和优先级队列结合的方式(“流式归并”)，消除了客户端内存消耗的压力, 但是网络消耗的影响依然是无法消除），所以，尽量使用上次分页数据结尾ID作为下次查询条件（适合在线客服），如果想精确分页查询，可使用二次查询法。

对于运维而言，我们没有考虑分布式部署、未考虑微服务数据库网格。

长路漫漫，我们要学的还有很多…