深度翻页导出导致慢SQL，mysqlCPU飙升优化方案

慢SQL原因分析：

1.深度翻页

2.多表JOIN

3. 大IN

4. id倒排序

本文针对深度翻页的优化进行探讨

方案1： 

将limit   offset, pageSize的方式改成 id > xx limit pageSize.

这样能走Id索引，提高速度。

缺点：不能使用多线程，入参ID从上页结果。

方案2：

基于 方案1再优化， 将limit   offset, pageSize 的方式改成 id > startId and id< endId .

一次性查出符合条件的ID范围，然后切分ID范围进行查询。（可分实际ID划分，或逻辑范围划分）

优点:  能用多线程并发查询。

缺点：逻辑范围划分有的id范围可能无数据，进行无效查询。

方案3：

终极方案：设置fetchSize，思想是 一次查询在Mysql侧缓冲全量数据，程序侧通过游标cursor批量读取数据，通过回调函数resulthandler处理数据。

优点： 不用多次和Mysql查询，一次查询多次读取数据。回调里可以使用多线程操作数据。

缺点： Mysql要缓冲全量数据，内存飙升

方案二步骤：

（1） 查询 对应表的ID范围，COUNT条数

（2） 根据count条数，和每页数量，计算页数，根据页数 和 ID范围进行ID范围切分。

（3）根据ID范围，发起多线程并发查询。

其中具体核心逻辑代码：

ID范围查询

<!-- 统计分页查询总条数 -->
	<select id="findIdRange" resultType="com.xyy.ms.export.core.erpreport.dto.ExportIdRangeDTO">
	select
		min(b.id) as minId, max(b.id) as maxId, count(1) as count
		from storage_batchnum b
		<include refid="batchNumExportWhere"></include>
	</select>

ID切分逻辑：

package com.xyy.ms.export.core.erpreport.dto;
 
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
 
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author stivenjin
 * @version 1.0
 * @description 说明: 取表中最小和最大ID， 用ID翻页查询，避免深度翻页（批号库存翻页导出）
 * 翻页优化步骤：
 * 1:根据ID范围，进行切分组
 * 2:用每组 的边界值进行id范围翻页查询。
 * @date 2023/9/1 18:10
 */
@Getter
@Setter
@ToString
@AllArgsConstructor
public class ExportIdRangeDTO implements Serializable {
    /**
     * 最小ID
     */
    private int minId = 0;
    /**
     * 最大ID
     */
    private int maxId = 0;
    /**
     * 总条数
     */
    private long count = 0;
 
    public boolean isValid() {
        return minId > 0 && maxId > 0;
    }
 
    /**
     * 按页数分隔ID范围
     * @param pageCount
     * @return
     */
    public List splitByPageCount(int pageCount) {
        List splitList = new ArrayList();
        int startId = minId;
        int endId = maxId;
        int pageSize = (int)Math.ceil((Double.valueOf(maxId) - Double.valueOf(minId)) / pageCount);
        System.out.println("pageSize:" + pageSize + ",pageCount:" + pageCount);
        int tmp = endId;
        for(int i = 1 ;i<=pageCount;i++){
            if(startId <= tmp){
                if(startId + pageSize <= tmp){
                    endId = startId + pageSize ;
                }else{
                    endId = tmp;
                }
            }else{
                break;
            }
            //System.out.println("循环调用：" + startId + " : " + endId);
            splitList.add(new ExportIdRangeDTO(startId, endId, 0));
            if(endId <= tmp){
                startId = endId +1;
            }
        }
        return splitList;
    }
 
    public static void main(String[] args) {
        ExportIdRangeDTO dto = new ExportIdRangeDTO(100,823540, 0);
        dto.splitByPageCount(10);
        System.out.println("切分一片原始：" + dto.getMinId() + " : " + dto.getMaxId());
    }
}

   and b.id >= #{minId} and b.id <= #{maxId}

按ID范围切分后，可用多线程并发查询导出

taskExecutor.submit

// 增加顺序按起点ID导出模式，避免深度翻页慢SQL(之前是多线程并发深度翻页查MYSQL，mysql cpu飙升)
            if (batchNumExportUseId) {
                ExportIdRangeDTO idRangeRes = exportStorageBatchNumApi.findIdRange(params);
                logger.info(" taskId [{}] 开始-异步顺序导出,idRange={}",taskId, JSON.toJSONString(idRangeRes));
                if (idRangeRes != null && idRangeRes.isValid()) {
                    paramsObject.put("pageSize", StorageWebConstant.PURCHASE_CALL_PAGESIZE);
 
                    int pageCnt = (int)(idRangeRes.getCount()/StorageWebConstant.PURCHASE_CALL_PAGESIZE);
                    pageCnt = pageCnt + (idRangeRes.getCount()%StorageWebConstant.PURCHASE_CALL_PAGESIZE == 0 ? 0:1);
                    List idRangeList = idRangeRes.splitByPageCount(pageCnt);
 
                    AtomicInteger pageNum = new AtomicInteger(0);
                    for (ExportIdRangeDTO idRange : idRangeList) {
                        int pn = pageNum.incrementAndGet();
                        Map exportParamMap = new HashMap<>();
                        exportParamMap.putAll(paramsObject);
                        exportParamMap.put("pageNum", pn);
                        exportParamMap.put("minId", idRange.getMinId());
                        exportParamMap.put("maxId", idRange.getMaxId());
                        logger.info("##  taskId [" + taskId + "]开始导出,第 " + pn + " 页 {}-{}", idRange.getMaxId(), idRange.getMaxId());
                        exportMap.putIfAbsent(pn, taskExecutor.submit(() -> storageReportService.listStorageBatchNumReportView(exportParamMap)));
                    }
 
                    for (int i = 1; i <= pageNum.get(); i++) {
                        List list = exportMap.get(i).get().getList();
                        ExportExcelUtil.insertDataToExcel(work, colName, list, line, true);
                        line = line + list.size();
                    }
                }
 
 
            }