原因:
由于业务需要,单标数据量达到800万,因此需要做分库分表
采用技术:
sharding-jdbc
集成sharding
引用pom
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
增加配置:
spring.shardingsphere.datasource.names = ds2020,ds2021
#数据源
spring.shardingsphere.datasource.ds2020.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2020.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2020.url=jdbc:mysql://**.***.***.***:3306/***?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds2020.username = root
spring.shardingsphere.datasource.ds2020.password = 11202
spring.shardingsphere.datasource.ds2021.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2021.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2021.url=jdbc:mysql://**.***.***.***:3306/***?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds2021.username = root
spring.shardingsphere.datasource.ds2021.password = 11202
# 分表配置
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = ds$->{2020..2021}.t_order_$->{1..4}
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column= create_time
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name = com.test.srm.biz.shardingAlgorithm.ShardingTableByQuarterPreciseAlgorithm
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.range-algorithm-class-name = com.test.srm.biz.shardingAlgorithm.ShardingTableByQuarterRangeAlgorithm
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{2020..2021}.t_order_item_$->{1..12}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.sharding-column= create_time
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.precise-algorithm-class-name = com.test.srm.biz.shardingAlgorithm.ShardingTableByMonthPreciseAlgorithm
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.range-algorithm-class-name = com.test.srm.biz.shardingAlgorithm.ShardingTableByMonthRangeAlgorithm
spring.shardingsphere.sharding.tables.t_order_info.actual-data-nodes=ds$->{2020..2021}.t_order_info
# 分库配置
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column= create_time
spring.shardingsphere.sharding.default-database-strategy.standard.range-algorithm-class-name = com.test.srm.biz.shardingAlgorithm.ShardingDataBaseByYearRangeAlgorithm
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name = com.test.srm.biz.shardingAlgorithm.ShardingDataBaseByYearPreciseAlgorithm
# 不分库分表的数据源指定
spring.shardingsphere.sharding.default-data-source-name = ds2020
分库规则(按照年进行分库+精确搜索)
public class ShardingDataBaseByYearPreciseAlgorithm implements PreciseShardingAlgorithm<Date> {
private static Map<String, String> dataSourceMap = new HashMap<String, String>();
static {
dataSourceMap.put("2020", "ds2020");
dataSourceMap.put("2021", "ds2021");
dataSourceMap.put("2022", "ds2022");
}
@Override
public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Date> shardingValue) {
String year=String.format("%tY", shardingValue.getValue());
String dataBase = dataSourceMap.get(year);
log.info("function=ShardingDataBaseByYearPreciseAlgorithm.doSharding;msg=databaseNames:{},dataBase:{}", JSON.toJSONString(databaseNames), dataBase);
if (StringUtils.isBlank(dataBase))
{
throw new UnsupportedOperationException("ShardingTableByQuarterAlgorithm ********** No database matching the rule **********");
}
return dataBase;
}
}
分库规则(按照年进行范围搜索)
public class ShardingDataBaseByYearRangeAlgorithm implements RangeShardingAlgorithm<Date>
{
private static Map<Integer, String> dataSourceMap = new HashMap<Integer, String>();
static {
dataSourceMap.put(2020, "ds2020");
dataSourceMap.put(2021, "ds2021");
dataSourceMap.put(2022, "ds2022");
}
@Override
public Collection<String> doSharding(Collection<String> databaseNames, RangeShardingValue<Date> rangeShardingValue) {
List<String> dataBases = new ArrayList<>(databaseNames);
Range<Date> valueRange = rangeShardingValue.getValueRange();
if (valueRange.hasLowerBound() && valueRange.hasUpperBound())
{
log.info("function=ShardingDataBaseByYearRangeAlgorithm.doSharding;msg=dataBases:{},valueRange1:{},valueRange2:{}"
, JSON.toJSONString(dataBases), String.format("%tY", valueRange.lowerEndpoint()),
String.format("%tY", valueRange.upperEndpoint()));
List<Integer> yearList = new ArrayList<>();
for (Integer lowerYear = Integer.parseInt(String.format("%tY", valueRange.lowerEndpoint())),
upperYear = Integer.parseInt(String.format("%tY", valueRange.upperEndpoint()));
lowerYear <= upperYear; lowerYear++)
{
yearList.add(lowerYear);
}
List<String> dataNameList = new ArrayList<>();
for (Integer year : yearList)
{
if (StringUtils.isNotBlank(dataSourceMap.get(year)))
{
dataNameList.add(dataSourceMap.get(year));
}
}
if (CollectionUtils.isEmpty(dataNameList))
{
return dataBases;
}
else {
return dataNameList;
}
}
return dataBases;
}
}
分表规则(按照月进行分表+精确搜索)
public class ShardingTableByMonthPreciseAlgorithm implements PreciseShardingAlgorithm<Date> {
@Override
public String doSharding(Collection<String> tablesNames, PreciseShardingValue<Date> shardingValue) {
Date date = shardingValue.getValue();
int month = date.getMonth() + 1;
log.info("function=ShardingTableByMonthPreciseAlgorithm.doSharding;msg=tablesNames:{},month:{}", JSON.toJSONString(tablesNames), month);
//按月路由
for (String each : tablesNames)
{
if (each.contains("_" + month) || each.contains("_0" + month) )
{
return each;
}
}
throw new UnsupportedOperationException("ShardingTableByMonthAlgorithm ********** No database matching the rule **********");
}
}
分表规则(按照月进行范围搜索)
public class ShardingTableByMonthRangeAlgorithm implements RangeShardingAlgorithm<Date>
{
@Override
public Collection<String> doSharding(Collection<String> tablesNames, RangeShardingValue<Date> rangeShardingValue)
{
List<String> tablesNameList = new ArrayList<>(tablesNames);
Range<Date> valueRange = rangeShardingValue.getValueRange();
log.info("function=ShardingTableByMonthRangeAlgorithm.doSharding;msg=tablesNames:{},rangeValue1:{},rangeValue2:{}"
, JSON.toJSONString(tablesNames), valueRange.lowerEndpoint(), valueRange.upperEndpoint());
// 这里一定要对valueRange重新新建对象赋值,不要改变rangeShardingValue中的参数,因为在下次进来的时候值会变
Date startDate = new Date(valueRange.lowerEndpoint().getTime());
Date endDate = new Date(valueRange.upperEndpoint().getTime());
List<Integer> allMonth = ShardingCommon.getAllMonth(startDate, endDate);
List<String> newTableNames = new ArrayList<>();
for (Integer month : allMonth)
{
if (month -1 <= tablesNameList.size())
{
newTableNames.add(tablesNameList.get(month -1));
}
}
if (CollectionUtils.isEmpty(newTableNames))
{
return tablesNameList;
}
else {
return newTableNames;
}
}
}
分表规则(按照季度进行分表+精确搜索)
public class ShardingTableByQuarterPreciseAlgorithm implements PreciseShardingAlgorithm<Date> {
// 第一季度(集合中为月份)
private static final List<Integer> oneQuarterList = Arrays.asList(1,2,3);
// 第二季度(集合中为月份)
private static final List<Integer> twoQuarterList = Arrays.asList(4,5,6);
// 第三季度(集合中为月份)
private static final List<Integer> threeQuarterList = Arrays.asList(7,8,9);
// 第四季度(集合中为月份)
private static final List<Integer> fourQuarterList = Arrays.asList(10,11,12);
@Override
public String doSharding(Collection<String> tableNames, PreciseShardingValue<Date> shardingValue) {
List<String> tables = new ArrayList<>(tableNames);
Date date = shardingValue.getValue();
int month = date.getMonth() + 1;
log.info("function=ShardingTableByQuarterAlgorithm.doSharding;msg=tableNames:{},month:{}", JSON.toJSONString(tableNames), month);
// 按季度路由
if (oneQuarterList.contains(month))
{
return tables.get(0);
}
if (twoQuarterList.contains(month))
{
return tables.get(1);
}
if (threeQuarterList.contains(month))
{
return tables.get(2);
}
if (fourQuarterList.contains(month))
{
return tables.get(3);
}
throw new UnsupportedOperationException("ShardingTableByQuarterAlgorithm ********** No database matching the rule **********");
}
}
分表规则(按照季度进行范围搜索)
public class ShardingTableByQuarterRangeAlgorithm implements RangeShardingAlgorithm<Date>
{
// 第一季度(集合中为月份)
private static final List<Integer> oneQuarterList = Arrays.asList(1,2,3);
// 第二季度(集合中为月份)
private static final List<Integer> twoQuarterList = Arrays.asList(4,5,6);
// 第三季度(集合中为月份)
private static final List<Integer> threeQuarterList = Arrays.asList(7,8,9);
// 第四季度(集合中为月份)
private static final List<Integer> fourQuarterList = Arrays.asList(10,11,12);
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue)
{
List<String> tablesNameList = new ArrayList<>(collection);
Range<Date> valueRange = rangeShardingValue.getValueRange();
if (valueRange.hasLowerBound() && valueRange.hasUpperBound())
{
log.info("function=ShardingTableByQuarterRangeAlgorithm.doSharding;msg=tablesNameList:{},rangeValue1:{},rangeValue2:{}"
, JSON.toJSONString(tablesNameList), valueRange.lowerEndpoint(), valueRange.upperEndpoint());
List<String> newTableNames = new ArrayList<>();
// 这里一定要对valueRange重新新建对象赋值,不要改变rangeShardingValue中的参数,因为在下次进来的时候值会变
Date startDate = new Date(valueRange.lowerEndpoint().getTime());
Date endDate = new Date(valueRange.upperEndpoint().getTime());
List<Integer> allMonth = ShardingCommon.getAllMonth(startDate, endDate);
for (Integer month : allMonth)
{
if (oneQuarterList.contains(month))
{
newTableNames.add(tablesNameList.get(0));
}
if (twoQuarterList.contains(month))
{
newTableNames.add(tablesNameList.get(1));
}
if (threeQuarterList.contains(month))
{
newTableNames.add(tablesNameList.get(2));
}
if (fourQuarterList.contains(month))
{
newTableNames.add(tablesNameList.get(3));
}
}
if (CollectionUtils.isNotEmpty(newTableNames))
{
return newTableNames;
}
else {
return tablesNameList;
}
}
return tablesNameList;
}
获取两个时间段内所有的月份
public static List<Integer> getAllMonth(Date startDate, Date endDate)
{
List<Integer> allMonth = new ArrayList<>();
if (Objects.isNull(startDate) || Objects.isNull(endDate))
{
return allMonth;
}
while (startDate.getTime() <= endDate.getTime())
{
Integer month = startDate.getMonth() + 1;
if (!allMonth.contains(month))
{
allMonth.add(month);
}
startDate.setMonth(startDate.getMonth() + 1);
}
return allMonth;
}
集成过程中存在的问题
1.获取时间中年的时候
int year = endDate.getYear(); 获取到结果预期为2021结果是121
String year=String.format("%tY", shardingValue.getValue()) 获取到结果为2021
2.当进行范围查询的时候首先会进分库规则,加入说查找到两个库,则会进两次分表规则
比如查询2020年12月->2021年1月
会查询
2020年库1月,12月库
2021年库1月,12月库
该问题暂时无法解决,因为在debug中发现无法区分出对应的是2020库 还是2021库
3. int month = endDate.getMonth() 结果是0->11 0:一月份
4.在根据时间进行范围查询的时候目前4.0.0版本不支持<= >= 查询,仅支持between and 需要升级到4.1.1版本之后才支持.