1.前言
有一个比较老的N年系统,MySQL某单表A数据量太大,查询效率较慢了,考虑使用Shardingsphere
分表,表结构如下:
CREATE TABLE `A` (
`ID` bigint(20) NOT NULL COMMENT '唯一标识',
`USER_ID` bigint(11) DEFAULT NULL COMMENT '用户id',
`BUSINESS_DATA` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '业务数据',
`BUSINESS_TIME` datetime DEFAULT NULL COMMENT '业务时间',
PRIMARY KEY (`ID`) USING BTREE,
KEY `user_id_idx` (`USER_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT COMMENT='表A信息';
系统中主要的查询SQL如下:
- 根据
ID
查询,in
,=
查询条件 - 根据
USER_ID
字段,in
,=
查询,BUSINESS_TIME
字段,<=
,>=
查询 - 单独对
BUSINESS_TIME
字段,<=
,>=
查询
根据上面的三种查询方式,有以下思考方向:
- 不给
BUSINESS_TIME
字段建立二级索引的原因?
某些场景可以对时间建立索引。因为考虑BUSINESS_TIME
字段不保证增量插入,有部分业务是回拨时间
插入。 - 如何分表方式?
根据业务表规律发现每年的增量在800W内,所以可以按照年份进行水平拆分
,避免数据倾斜问题。 - 用户根据
ID
、BUSINESS_TIME
字段查询,如何确定到哪一张分表?
由于根据年份拆表,BUSINESS_TIME
字段查询可以计算哪一张分表。ID
字段使用是hutool工具包的雪花算法生成,因为可以使用ID
反推出生成的年份。源码如下:
/**
* 根据Snowflake的ID,获取生成时间
*
* @param id snowflake算法生成的id
* @return 生成的时间
*/
public long getGenerateDateTime(long id) {
return (id >> TIMESTAMP_LEFT_SHIFT & ~(-1L << 41L)) + twepoch;
}
2.设计与实现
把表A按照年份拆分A_2022、A_2023表。
SpringBoot集成Mybatis-plus 3.4.3.4、Shardingsphere 5.4.0、Dynamic-datasource 3.4.1、druid 1.2.4。
- 依赖及版本问题
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.4.0</version>
</dependency>
使用的是druid 1.2.4
,项目排除一下druid-spring-boot-starter
依赖包。
使用springboot中snakeyaml
包比较老,也升级一下。
<dependency>
<artifactId>snakeyaml</artifactId>
<groupId>org.yaml</groupId>
<version>1.33</version>
</dependency>
- 实现多数据源
由于使用了shardingsphere,会对所有的表都进行分表策略判断,并且每个版本的配置方式都不一样。故使用多数据源的方式,在对有需要分表的查询,使用@DS("sharding")
注解使用shardingsphere
的数据源。
@Configuration
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DataSourceProperties.class})
@ConditionalOnClass(NacosConfigAutoConfiguration.class)
public class ShardingConfiguration {
@Resource
private DynamicDataSourceProperties properties;
@Resource
private NacosConfigProperties nacosConfigProperties;
@Value("${spring.shardindsphereUrl}")
private String shardingsphereUrl;
@Bean
@Primary
public DynamicDataSourceProvider dynamicDataSourceProvider() {
NacosConfigiServiceUtils.init(nacosConfigProperties);
Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
//SPI机制
Collection<ShardingSphereDriverURLProvider> provider = ShardingSphereServiceLoader.getServiceInstances(ShardingSphereDriverURLProvider.class);
provider.forEach(item -> {
if (item.accept(shardingsphereUrl)) {
try {
DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(item.getContent(shardingsphereUrl));
dataSourceMap.put("sharding", dataSource);
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
});
return dataSourceMap;
}
};
}
}
- 实现Nacos中读取yaml
一般yaml配置都是放在Nacos ,但是shardingsphere
无法读取nacos配置。
ShardingSphereDriver
类实现了JDBC Driver,该类有一个DriverDataSourceCache类实例,createDataSource()
调用了ShardingSphereDriverURLManager
静态方法,该方法负责采用SPI机制读取yaml中配置的分表策略。接口为ShardingSphereDriverURLProvider
所以实现ShardingSphereDriverURLProvider
类
public final class ShardingJdbcNacosProvider implements ShardingSphereDriverURLProvider {
private static final String CLASSPATH_TYPE = "nacos:";
private static final String URL_PREFIX = "jdbc:shardingsphere:";
@Override
public boolean accept(String url) {
return StringUtils.isNotEmpty(url) && url.contains(CLASSPATH_TYPE);
}
@SneakyThrows
@Override
public byte[] getContent(String url) {
String dataId = url.substring(CLASSPATH_TYPE.length() + URL_PREFIX.length());
Preconditions.checkArgument(!dataId.isEmpty(), "Nacos namespace is required in ShardingSphere dataId.");
NacosConfigProperties nacosConfigProperties = NacosConfigiServiceUtils.getNacosConfigProperties();
ConfigService configService = nacosConfigProperties.configServiceInstance();
String content = configService.getConfig(dataId, nacosConfigProperties.getGroup(), nacosConfigProperties.getTimeout());
return content.getBytes(StandardCharsets.UTF_8);
}
}
配置
spring:
datasource:
dynamic:
primary: master
datasource:
master:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.0.1:3306/db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
username: xxx
password: xxx
shardindsphereUrl: jdbc:shardingsphere:nacos:a-sharding.yaml
NacosConfigiServiceUtils
类,
public class NacosConfigiServiceUtils {
private static NacosConfigProperties nacosConfigProperties;
public static void init(NacosConfigProperties properties){
nacosConfigProperties = properties;
}
public static NacosConfigProperties getNacosConfigProperties() {
return nacosConfigProperties;
}
}
- 算法配置
dataSources:
ds:
dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.0.1:3306/db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
username: xxx
password: xxx
rules:
- !SHARDING
tables:
attendance_checkin:
actualDataNodes: ds.a_$->{2022..2024}
tableStrategy:
complex:
shardingColumns: ID,BUSINESS_TIME
shardingAlgorithmName: aAlgorithm
shardingAlgorithms:
aAlgorithm:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName: com.lemom.algorithm.AShardingAlgorithm
AShardingAlgorithm
类实现
public class AShardingAlgorithm implements ComplexKeysShardingAlgorithm<Comparable<?>> {
public static final String LOGIC_ID = "ID";
public static final String LOGIC_BUSINESS_TIME = "BUSINESS_TIME";
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Comparable<?>> shardingValue) {
Map<String, Range<Comparable<?>>> shardingRangeMaps = shardingValue.getColumnNameAndRangeValuesMap();
Map<String, Collection<Comparable<?>>> shardingMaps = shardingValue.getColumnNameAndShardingValuesMap();
List<Integer> years = new ArrayList<>();
String logicTableName = shardingValue.getLogicTableName();
if (!shardingRangeMaps.isEmpty()) {
//范围分片算法 省略
} else {
Collection<Comparable<?>> ids = shardingMaps.getOrDefault(LOGIC_ID, new ArrayList<>());
ids.forEach(id -> {
if (id instanceof Long) { years.add(DateUtil.date(IdWorker.SNOWFLAKE.getGenerateDateTime((Long) id)).getField(DateField.YEAR));
}
});
Collection<Comparable<?>> timeList = shardingMaps.getOrDefault(LOGIC_BUSINESS_TIME , new ArrayList<>());
timeList .forEach(t -> {
if (t instanceof Date) {
years.add((DateUtil.date((Date) t).getField(DateField.YEAR)));
}
});
}
return years.stream().map(id -> logicTableName + "_" + id).collect(Collectors.toSet());
}
}
3.总结
1.使用范围查询时候,尽量把分片范围控制最小化,防止shardingJDBC查询很多个分片。
2.如果明确确定那个分片,应不使用@DS(‘sharding’),使用master database查询。
3.sharding 5.4.0新版本的的SPI特性,增强了系统可扩展性。