隨著項(xiàng)目上線功氨，業(yè)務(wù)數(shù)據(jù)會(huì)越來越多。這個(gè)時(shí)候手幢，很多開發(fā)時(shí)適用的方法捷凄、任務(wù)，在龐大數(shù)據(jù)量面前就會(huì)變得很不堪围来，經(jīng)常會(huì)出現(xiàn)超時(shí)跺涤，慢查詢，異常等等問題监透。
所以没酣，一般在開發(fā)階段勋眯，我們?cè)趺茨鼙苊膺@些問題呢？一切皆有套路。
一般處理套路：
SQL優(yōu)化缤言，數(shù)據(jù)庫(kù)加索引罗心，多線程，并行計(jì)算，異步處理超营，大事務(wù)拆小事務(wù)，緩存阅虫，數(shù)據(jù)異構(gòu)等等演闭。
今天分享一個(gè)通過時(shí)間維度優(yōu)化SQL的方法。說白了颓帝，就是如何將時(shí)間拆小米碰。
還是直接貼代碼：
開始版本，按天拆分時(shí)間：
/**
* 按給定的天數(shù)切割時(shí)間段
* @param startDate
* @param endDate
* @param amount 按多少天切割
* @return
* @throws ParseException
*/
public static List<TimeSlot> splitTimeSlot(Date startDate, Date endDate, Integer amount) throws ParseException {
Calendar canlandar1 = Calendar.getInstance();//開始時(shí)間
Calendar canlandar2 = Calendar.getInstance();//結(jié)束時(shí)間
canlandar1.setTime(com.midea.ec.fc.impl.utils.DateUtils.getDateStartTime(startDate));
canlandar2.setTime(com.midea.ec.fc.impl.utils.DateUtils.getDateStartTime(endDate));
List<TimeSlot> returnList = new ArrayList<TimeSlot>();
while(canlandar1.compareTo(canlandar2) < 1){
TimeSlot timeSlot = new TimeSlot();
Date start = canlandar1.getTime();
canlandar1.add(Calendar.DATE, amount);//每次循環(huán)增加amount天
Date end = canlandar1.getTime();
timeSlot.setStartDate(DateTool.getDateTime(start));
timeSlot.setEndDate(DateTool.getDateTime(end.before(canlandar2.getTime()) ? end : canlandar2.getTime()));
returnList.add(timeSlot);
timeSlot.setStartDateTime(start);
timeSlot.setEndDateTime(end.before(canlandar2.getTime()) ? end : canlandar2.getTime());
}
return returnList;
}
發(fā)現(xiàn)某些天數(shù)據(jù)量大跑不動(dòng)了购城，然后繼續(xù)拆分成小時(shí)：
public static List<TimeSlot> splitTimeSlotByHour(String startDate, String endDate) throws ParseException {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date date1 = format.parse(startDate);
Date date2 = format.parse(endDate);
Calendar canlandar1 = Calendar.getInstance();//開始時(shí)間
Calendar canlandar2 = Calendar.getInstance();//結(jié)束時(shí)間
canlandar1.setTime(date1);//2016-11-01
canlandar2.setTime(date2);//2016-11-11
List<TimeSlot> returnList = new ArrayList<TimeSlot>();
while(canlandar1.compareTo(canlandar2) < 1){
TimeSlot timeSlot = new TimeSlot();
Date start = canlandar1.getTime();
canlandar1.add(Calendar.HOUR, 1);//每次循環(huán)增加一天
Date end = canlandar1.getTime();
timeSlot.setStartDate(DateTool.getDateTime(start));
timeSlot.setEndDate(DateTool.getDateTime(end));
returnList.add(timeSlot);
timeSlot.setStartDateTime(start);
timeSlot.setEndDateTime(end);
}
if(CollectionUtils.isNotEmpty(returnList)) returnList.remove(returnList.size()-1);
return returnList;
}
最后發(fā)現(xiàn)光棍節(jié)的這天吕座，小時(shí)也跑不動(dòng)了，還要繼續(xù)拆分鐘瘪板？改成自己拆吧吴趴。
/**
* @Auther: majx2
* @Date: 2018-11-14 11:24
* @Description:
*/
public class JobTimeoutHelper {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
// 時(shí)間區(qū)間拆分?jǐn)?shù)量
private int intervalCount = 3;
// 拆分最大層級(jí)
private int maxCount = 3;
private JobTimeoutHelper(){}
public static JobTimeoutHelper create(){
return new JobTimeoutHelper();
}
public void splitTime(Date startDate,Date endDate,String jobName,TimeSplitHandler handler){
splitTime(startDate,endDate,0,jobName,handler);
}
public void splitTime(Date startDate,Date endDate,int level,String jobName,TimeSplitHandler handler){
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if(level>=maxCount){
logger.info("{}超時(shí)，拆分已到上限侮攀，無(wú)法繼續(xù)拆分：時(shí)間：{}",jobName,MessageFormat.format("開始時(shí)間：{0}锣枝，結(jié)束時(shí)間：{1}",df.format(startDate),df.format(endDate)));
return;
}
level++;
if(startDate.compareTo(endDate) < 1) {
long offset = startDate.getTime();
long diff = endDate.getTime() - offset;
long interval = diff/intervalCount;
for (int i = 0 ; i <intervalCount;i++){
Date start = new Date(offset);
Date end ;
if(i == (intervalCount-1)){
end = endDate;
}else{
offset += interval;
end = new Date(offset);
}
try{
logger.info("{}處理開始，層級(jí)：{}兰英，時(shí)間：{}", jobName,level,MessageFormat.format("開始時(shí)間：{0}撇叁，結(jié)束時(shí)間：{1}",df.format(start),df.format(end)));
handler.deal(start,end);
logger.info("{}處理結(jié)束，層級(jí)：{}畦贸，時(shí)間：{}", jobName,level,MessageFormat.format("開始時(shí)間：{0}陨闹，結(jié)束時(shí)間：{1}",df.format(start),df.format(end)));
}catch (MySQLNonTransientConnectionException ex){
logger.info("{}超時(shí)，進(jìn)行拆分處理薄坏，層級(jí)：{}",jobName,level);
splitTime(start,end,level,jobName,handler);
} catch (Exception e) {
logger.error("{}異常啦：{}",jobName, ExceptionUtils.getFullStackTrace(e));
}
}
}
}
public interface TimeSplitHandler{
void deal(Date start,Date end) throws Exception;
}
public JobTimeoutHelper setIntervalCount(int intervalCount) {
this.intervalCount = intervalCount;
return this;
}
public JobTimeoutHelper setMaxCount(int maxCount){
this.maxCount = maxCount;
return this;
}
public static void main(String[] args) {
final Date today = new Date();
final Date tomorrow = DateUtils.addDays(today, +1);
final Date yestoday = DateUtils.addDays(today, -1);
JobTimeoutHelper.create().setIntervalCount(2).setMaxCount(3)
.splitTime(yestoday, tomorrow,"JobTimeoutHelper", new TimeSplitHandler() {
@Override
public void deal(Date start, Date end) throws Exception {
throw new MySQLNonTransientConnectionException();
}
});
}
}
這是一個(gè)任務(wù)超時(shí)幫助類趋厉，原來是通過遞歸的方式，不斷將時(shí)間拆小胶坠，這樣在龐大的數(shù)據(jù)面前也可以淡定的run起來君账。只需要多花點(diǎn)時(shí)間而已。同時(shí)涵但，也可以配合多線程處理，讓程序加速奔跑起來帖蔓。
溫馨提示：要根據(jù)實(shí)際需求矮瘟，記得要設(shè)置多少等份，和最大層級(jí)哦塑娇。
