数据量比较大的高效分页查询

问题描述：
查询昨天生成的记录并生成文件，原先是分批查询，每一次返回上一次查询结果的最大的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);
        }

实现了针对大数据量的分片处理,提高响应速度