公司有个记录表,每天有几百万的数据,所以我决定按月把他分下表。
用spring整合的。
首先,sharding-sphere不支持自动创建表,所以我提前创建了两年的表,命名规则 logicTableName + _2019_06
以下是官方文档上面的分片算法介绍:
由于这个记录表在业务层面,只有insert 和 query ,我就用create_datetime作为分表位,查询是有时间范围的,所以我选择复合分片算法。
以下是代码。
package com.beisheng.sharding;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import com.google.common.collect.Lists;
import com.google.common.collect.Range;
import com.xhlc.pay.util.DatetimeUtil;
import io.shardingsphere.api.algorithm.sharding.ListShardingValue;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.ShardingValue;
import io.shardingsphere.api.algorithm.sharding.complex.ComplexKeysShardingAlgorithm;
import lombok.extern.slf4j.Slf4j;
/**
*
* @author OliverAAAAA
* @since 2019-6-10 12:00:00
*
*/
@Service("tShopUploadAppInfoRecordTableShardingAlgorithm")
@Slf4j
public class TShopUploadAppInfoRecordTableShardingAlgorithm implements ComplexKeysShardingAlgorithm {
@SuppressWarnings("unchecked")
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue> shardingValues) {
Collection<String> routTables = new HashSet<String>();
if (shardingValues != null) {
for (ShardingValue shardingValue : shardingValues) {
// eq 条件
if (shardingValue instanceof PreciseShardingValue) {
PreciseShardingValue<Date> preciseShardingValue = (PreciseShardingValue<Date>) shardingValue;
Date value = preciseShardingValue.getValue();
String routTable = getRoutTable(preciseShardingValue.getLogicTableName(), value);
if (StringUtils.isNotBlank(routTable)) {
routTables.add(routTable);
}
// between 条件
} else if (shardingValue instanceof RangeShardingValue) {
RangeShardingValue<Date> rangeShardingValue = (RangeShardingValue<Date>) shardingValue;
Range<Date> valueRange = rangeShardingValue.getValueRange();
Date lowerEnd = (Date) valueRange.lowerEndpoint();
Date upperEnd = (Date) valueRange.upperEndpoint();
Collection<String> tables = getRoutTable(shardingValue.getLogicTableName(), lowerEnd, upperEnd);
if (tables != null && tables.size() > 0) {
routTables.addAll(tables);
}
//多个参数
} else if (shardingValue instanceof ListShardingValue) {
ListShardingValue<Date> rangeShardingValue = (ListShardingValue<Date>) shardingValue;
Collection<Date> values = rangeShardingValue.getValues();
for (Date date : values) {
String routTable = getRoutTable(shardingValue.getLogicTableName(), date);
if (StringUtils.isNotBlank(routTable)) {
routTables.add(routTable);
}
}
}
if (routTables != null && routTables.size() > 0) {
return routTables;
}
}
}
throw new UnsupportedOperationException();
}
private String getRoutTable(String logicTable, Date keyValue) {
if (keyValue != null) {
String formatDate = DatetimeUtil.formatDate(keyValue, "_YYYY_MM");
return logicTable + formatDate;
}
return null;
}
public static void main(String[] args) {
Calendar cal = Calendar.getInstance();
Date start = cal.getTime();
String formatDate = DatetimeUtil.formatDate(start, "_YYYY_MM");
cal.set(Calendar.MONTH, 10);
Date end = cal.getTime();
List<String> list = getRangeNameList(start, end);
System.out.println(list);
System.out.println(formatDate);
}
private static List<String> getRangeNameList(Date start, Date end) {
List<String> result = Lists.newArrayList();
Calendar dd = Calendar.getInstance();// 定义日期实例
dd.setTime(start);// 设置日期起始时间
while (dd.getTime().before(end)) {// 判断是否到结束日期
SimpleDateFormat sdf = new SimpleDateFormat("_YYYY_MM");
String str = sdf.format(dd.getTime());
result.add(str);
dd.add(Calendar.MONTH, 1);// 进行当前日期月份加1
}
return result;
}
private Collection<String> getRoutTable(String logicTable, Date lowerEnd, Date upperEnd) {
Set<String> routTables = new HashSet<String>();
if (lowerEnd != null && upperEnd != null) {
List<String> rangeNameList = getRangeNameList(lowerEnd, upperEnd);
for (String string : rangeNameList) {
routTables.add(logicTable + string);
}
}
return routTables;
}
}
下面是配置:
<bean id="tShopUploadAppInfoRecordTableShardingAlgorithm" class="com.beisheng.sharding.TShopUploadAppInfoRecordTableShardingAlgorithm" />
<sharding:complex-strategy id="strategyTableTShopUploadAppInfoRecord" sharding-columns="create_datetime" algorithm-ref="tShopUploadAppInfoRecordTableShardingAlgorithm" />
<sharding:data-source id="dynamicDataSource">
<sharding:sharding-rule data-source-names="ds_cdf_master_0,ds_cdf_slave_0"
default-data-source-name="ds_cdf_master_0">
<sharding:master-slave-rules>
<sharding:master-slave-rule id="ds_ms0"
master-data-source-name="ds_cdf_master_0" slave-data-source-names="ds_cdf_slave_0"
strategy-type="RANDOM" />
</sharding:master-slave-rules>
<sharding:table-rules>
<sharding:table-rule logic-table="t_shop_upload_app_info_record"
table-strategy-ref="strategyTableTShopUploadAppInfoRecord" />
</sharding:table-rules>
<sharding:binding-table-rules>
<sharding:binding-table-rule
logic-tables="t_shop_upload_app_info_record" />
</sharding:binding-table-rules>
</sharding:sharding-rule>
<sharding:props>
<prop key="sql.show">${shard.sql.show}</prop>
</sharding:props>
</sharding:data-source>
纯原创,转载请注明出处~
本人最近开了一个公众号,会讲一些常用的技术,以及面试题,欢迎关注
扫码关注,每天获取最前沿的互联网知识~