SpringBoot --- 使用动态多数据源来解决 ShardingSphere jdbc 接管 SpringBoot 全部数据源的问题

说明

Spring Boot 项目使用 ShardingSphere-JDBC,默认情况下会接管配置的全部数据源,这会导致一些问题
比如,所有的 sql 执行都会走 ShardingSphere 的分库或者分别的逻辑判断
最重要的是,ShardingSphere 不支持的 SQL 会直接报错
比如: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/unsupported-items/
还有: https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/#%E4%B8%8D%E6%94%AF%E6%8C%81%E7%9A%84sql

1
2
3
4
5
6
7
8
9
10
-- SELECT子句暂不支持使用*号简写及内置的分布式主键生成器
INSERT INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ?
-- SELECT子句暂不支持使用*号简写及内置的分布式主键生成器
REPLACE INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ?
-- 会导致全路由
SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 UNION
SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 UNION ALL
SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ?
-- 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名
SELECT MAX(tbl_name.col1) FROM tbl_name

这是不能忍的情况

解决方案

官方已经给出的解决方案:
FQA

  1. 如果只有部分数据库分库分表,是否需要将不分库分表的表也配置在分片规则中?
    回答:

是的。因为ShardingSphere是将多个数据源合并为一个统一的逻辑数据源。因此即使不分库分表的部分,不配置分片规则ShardingSphere即无法精确的断定应该路由至哪个数据源。
但是ShardingSphere提供了两种变通的方式,有助于简化配置。

方法1:配置default-data-source,凡是在默认数据源中的表可以无需配置在分片规则中,ShardingSphere将在找不到分片数据源的情况下将表路由至默认数据源。

方法2:将不参与分库分表的数据源独立于ShardingSphere之外,在应用中使用多个数据源分别处理分片和不分片的情况。

方法 1 的配置方式不适合我
我选择了 方法 2
具体做法如下:

操作

依赖引入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-beta</version>
<exclusions>
<!-- 版本太低,有安全漏洞:log4j-1.2.17.jar: CVE-2020-9488, CVE-2019-17571 ,排除掉 -->
<exclusion>
<artifactId>log4j</artifactId>
<groupId>log4j</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
</dependency>

项目配置

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
spring:
application:
name: im
datasource:
dynamic:
primary: im
strict: false
datasource:
im:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://xxxxxx:xxxx/im?useUnicode=true&characterEncoding=utf-8
type: com.zaxxer.hikari.HikariDataSource
username: xxxx
password: xxxx
shardingsphere:
datasource:
names: sharding-sphere
sharding-sphere:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://xxxxxx:xxxx/sharding-sphere?useUnicode=true&characterEncoding=utf-8
type: com.zaxxer.hikari.HikariDataSource
username: xxxx
password: xxxx
rules:
sharding:
tables:
message:
actual-data-nodes: sharding-sphere.message_$->{0..1}_$->{2021..2030}${(1..12).collect{t ->t.toString().padLeft(2,'0')}}
table-strategy:
complex:
sharding-columns: conversation_type, timestamp
sharding-algorithm-name: message-table-strategy
sharding-algorithms:
message-table-strategy:
type: MessageComplexKeysShardingAlgorithm
props: { }
props:
sql-show: true

配置了 2 个数据库
im 为主库:正常增删改查的数据库
sharding-sphere:专为分库分表的使用的数据库

还配置一个分表规则,分表策略为自定义策略 MessageComplexKeysShardingAlgorithm

自定义策略

shardingsphere-jdbc 5.x 的分表策略使用的是 SPI 机制

具体就是在 resources/META-INF/services 目录下新增配置文件 org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
将 shardingsphere-jdbc 5.x 自带的策略和自定义的策略加入进去

如下

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
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

org.apache.shardingsphere.sharding.algorithm.sharding.inline.InlineShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.mod.ModShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.mod.HashModShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.range.VolumeBasedRangeShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.range.BoundaryBasedRangeShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.datetime.AutoIntervalShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.datetime.IntervalShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.classbased.ClassBasedShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.complex.ComplexInlineShardingAlgorithm
org.apache.shardingsphere.sharding.algorithm.sharding.hint.HintInlineShardingAlgorithm

com.hfky.im.wildfirechat.msgforward.policy.MessageComplexKeysShardingAlgorithm

自定义策略如下:

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
public class MessageComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Comparable<?>> {

/**
* 根据type和time分片。
*/
@Override
public Collection<String> doSharding(
Collection<String> collection, ComplexKeysShardingValue<Comparable<?>> shardingValue) {
}

@Override
public void init() {
//
}

@Override
public String getType() {
return "MessageComplexKeysShardingAlgorithm";
}

@Override
public Properties getProps() {
return ComplexKeysShardingAlgorithm.super.getProps();
}

@Override
public void setProps(Properties props) {
ComplexKeysShardingAlgorithm.super.setProps(props);
}
}

安装规则重写 doSharding 方法即可

动态多数据源配置

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
@Configuration
public class DataSourceConfiguration {

private final DynamicDataSourceProperties properties;

private final Map<String, DataSource> dataSources;

public DataSourceConfiguration(DynamicDataSourceProperties properties, @Lazy Map<String, DataSource> dataSources) {
this.properties = properties;
this.dataSources = dataSources;
}


/**
* 加入 shardingSphere 的数据源。
*/
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put(ShardingSphereDataSource.class.getAnnotation(DS.class).value(),
dataSources.get("shardingSphereDataSource"));
return dataSourceMap;
}
};
}

/**
* 设置主数据源。
*/
@Bean
@Primary
public DataSource dataSource() {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
}

2 个注解用来切换数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@DS("im")
public @interface ImDataSource {
}


@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@DS("sharding-sphere")
public @interface ShardingSphereDataSource {
}

使用方法

是需要使用数据源的 Mapper 或者 Service 加入 @ImDataSource 注解 或者 @ShardingSphereDataSource 注解, 不加的话使用的是默认数据源 im
如上配置的话,正常使用用的就是 im 数据库,分库分表使用的就是 sharding-sphere 数据库