做a 视频在线观看网站,河北省建设工程安全生产网站,石家庄商标设计,建设教育网站公司有个记录表#xff0c;每天有几百万的数据#xff0c;所以我决定按月把他分下表。
用spring整合的。
首先#xff0c;sharding-sphere不支持自动创建表#xff0c;所以我提前创建了两年的表#xff0c;命名规则 logicTableName _2019_06
以下是官方文档上面的分片…公司有个记录表每天有几百万的数据所以我决定按月把他分下表。
用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)Overridepublic CollectionString doSharding(CollectionString availableTargetNames, CollectionShardingValue shardingValues) {CollectionString routTables new HashSetString();if (shardingValues ! null) {for (ShardingValue shardingValue : shardingValues) {// eq 条件if (shardingValue instanceof PreciseShardingValue) {PreciseShardingValueDate preciseShardingValue (PreciseShardingValueDate) shardingValue;Date value preciseShardingValue.getValue();String routTable getRoutTable(preciseShardingValue.getLogicTableName(), value);if (StringUtils.isNotBlank(routTable)) {routTables.add(routTable);}// between 条件} else if (shardingValue instanceof RangeShardingValue) {RangeShardingValueDate rangeShardingValue (RangeShardingValueDate) shardingValue;RangeDate valueRange rangeShardingValue.getValueRange();Date lowerEnd (Date) valueRange.lowerEndpoint();Date upperEnd (Date) valueRange.upperEndpoint();CollectionString tables getRoutTable(shardingValue.getLogicTableName(), lowerEnd, upperEnd);if (tables ! null tables.size() 0) {routTables.addAll(tables);}//多个参数} else if (shardingValue instanceof ListShardingValue) {ListShardingValueDate rangeShardingValue (ListShardingValueDate) shardingValue;CollectionDate 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();ListString list getRangeNameList(start, end);System.out.println(list);System.out.println(formatDate);}private static ListString getRangeNameList(Date start, Date end) {ListString 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 CollectionString getRoutTable(String logicTable, Date lowerEnd, Date upperEnd) {SetString routTables new HashSetString();if (lowerEnd ! null upperEnd ! null) {ListString rangeNameList getRangeNameList(lowerEnd, upperEnd);for (String string : rangeNameList) {routTables.add(logicTable string);}}return routTables;}}下面是配置
bean idtShopUploadAppInfoRecordTableShardingAlgorithm classcom.beisheng.sharding.TShopUploadAppInfoRecordTableShardingAlgorithm /sharding:complex-strategy idstrategyTableTShopUploadAppInfoRecord sharding-columnscreate_datetime algorithm-reftShopUploadAppInfoRecordTableShardingAlgorithm /sharding:data-source iddynamicDataSourcesharding:sharding-rule data-source-namesds_cdf_master_0,ds_cdf_slave_0default-data-source-nameds_cdf_master_0sharding:master-slave-rulessharding:master-slave-rule idds_ms0master-data-source-nameds_cdf_master_0 slave-data-source-namesds_cdf_slave_0strategy-typeRANDOM //sharding:master-slave-rulessharding:table-rulessharding:table-rule logic-tablet_shop_upload_app_info_recordtable-strategy-refstrategyTableTShopUploadAppInfoRecord //sharding:table-rulessharding:binding-table-rulessharding:binding-table-rulelogic-tablest_shop_upload_app_info_record //sharding:binding-table-rules/sharding:sharding-rulesharding:propsprop keysql.show${shard.sql.show}/prop/sharding:props/sharding:data-source
纯原创转载请注明出处~ 本人最近开了一个公众号会讲一些常用的技术以及面试题欢迎关注 扫码关注每天获取最前沿的互联网知识~
