这个图标让我想起了火狐

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”),假设插入后的id728988040994754561

不难发现,这个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这种查询,ShardingSpheresql优化为limit 0,1010,对于数据库而言,同样是扫描1010行数据,shardingsphere巧妙地利用流式处理和优先级队列结合的方式(“流式归并”),消除了客户端内存消耗的压力, 但是网络消耗的影响依然是无法消除),所以,尽量使用上次分页数据结尾ID作为下次查询条件(适合在线客服),如果想精确分页查询,可使用二次查询法

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

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