问题描述:
查询昨天生成的记录并生成文件,原先是分批查询,每一次返回上一次查询结果的最大的ID,下一次查询 用id> 上一次查询的最大ID,最后一轮查询 超时,explain 结果如下:
经过分析线上数据分布如下:
采用 时间戳跨度太大造成 索引区分度不理想,因此进行了时间分片,分片长度是可以配置的,默认30分钟,在进行分页查询,并优化 查询条件如下:
select * from wjs_hotel_info where create_datetime>=%s and create_datetime< %s ORDER BY create_datetime,id limit %s,%s
调节思路如下:使用查询条件 create_datetime 的时候排序使用 create_datetime和id
避免create_datetime相同时候分页查询有序
查询条件如果是 update_datetime 的时候排序使用 update_datetime
分批次,分页代码如下:
@Slf4j
public abstract class GenTextFileSpliteTime<T,R> {
//开始时间
private long startTime=0;
//结束时间
private long endTime=24;
//时间分片增长,毫秒,默认30分钟
private long increaseTime=30L*60*1000;
private Function<T,String> convertObjToStr;
private boolean startFlag ;
protected R qryInfo;
protected abstract R getQryInfo();
protected abstract Function<T,String> initConvertObjToStr();
/**
* @author: wuss@wjs.com
* @date: 2021/2/4 10:28 AM
* @param startTime 开始时间戳
* @param endTime 结束时间戳
* @param increaseMinute 分片时间长短,分钟
* @return:
*/
public GenTextFileSpliteTime(long startTime, long endTime, long increaseMinute) {
this.startTime = startTime;
this.endTime = endTime;
this.increaseTime = increaseMinute*60L*1000;
}
public void setIncreaseTime(long increaseTime) {
this.increaseTime = increaseTime*60L*1000;
}
public GenTextFileSpliteTime(long startTime, long endTime){
this(startTime,endTime,30L);
}
public void setConvertObjToStr(Function<T, String> convertObjToStr) {
this.convertObjToStr = convertObjToStr;
}
/**
* 分割的时间开始于结束
* @param timeStart
* @param timeEnd
* @param pageNum
* @return
*/
protected abstract List<T> listValue(long timeStart,long timeEnd,int pageNum);
public void writeDateToFile(BufferedWriter bufferedWriter){
qryInfo = getQryInfo();
convertObjToStr = initConvertObjToStr();
startFlag = false;
long start = startTime;
long end = start;
while (true){
if (start>= endTime){
break;
}
end = start+increaseTime;
if (end > endTime){
end = endTime;
}
writeDateToFileHelper(bufferedWriter,start,end);
start = end;
}
}
/**
* 时间片内分页查询
* @author: wuss@wjs.com
* @date: 2021/2/4 1:09 PM
* @param bufferedWriter
* @param timeStart
* @param timeEnd
* @return: void
*/
private void writeDateToFileHelper(BufferedWriter bufferedWriter,long timeStart,long timeEnd) {
int pageNum = 0;
while (true){
List<T> values = listValue(timeStart,timeEnd,pageNum++);
if (CollectionUtils.isEmpty(values)){
break;
}
try {
for (T value : values) {
if (startFlag){
bufferedWriter.newLine();
}
bufferedWriter.write(convertObjToStr.apply(value));
startFlag = true;
}
bufferedWriter.flush();
}catch (IOException e){
log.info("写文件发生异常",e);
throw new BaseException("写文件发生异常",e);
}
}
}
}
调用地方修改如下:
private GenTextFileSpliteTime<WjsHotelInfo, WjsHotelInfoQry> getAddWjsFile(Long start, Long end) {
return new GenTextFileSpliteTime<WjsHotelInfo, WjsHotelInfoQry>(start,end) {
@Override
protected WjsHotelInfoQry getQryInfo() {
WjsHotelInfoQry qry = new WjsHotelInfoQry();
qry.setLimit(10);
qry.setOrderBy("create_datetime,id");
return qry;
}
@Override
protected List<WjsHotelInfo> listValue(long timeStart, long timeEnd, int pageNum) {
qryInfo.setCreateDateStart(timeStart);
qryInfo.setCreateDateEnd(timeEnd);
qryInfo.setStart(pageNum*qryInfo.getLimit());
return pageWjsHotelRetry(qryInfo);
}
@Override
protected Function<WjsHotelInfo, String> initConvertObjToStr() {
return e->convertOnlineContent(e);
}
};
}
设置配置分片大小实现如下:
GenTextFileSpliteTime<WjsHotelInfo, WjsHotelInfoQry> addWjsFile = getAddWjsFile(start, end);
GenTextFileSpliteTime<WjsHotelInfo, WjsHotelInfoQry> updateWjsFile = getUpdateWjsFile(start, end);
GenTextFileSpliteTime<WjsHotelInfo, WjsHotelInfoQry> deleteWjsFile = getDeleteWjsFile(start, end);
if (StringUtils.isNotBlank(minute)){
Long minuteL = Long.valueOf(minute);
addWjsFile.setIncreaseTime(minuteL);
updateWjsFile.setIncreaseTime(minuteL);
deleteWjsFile.setIncreaseTime(minuteL);
}
实现了针对大数据量的分片处理,提高响应速度