慢SQL原因分析：

深度翻页

多表JOIN

大IN

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范围查询

selectmin(b.id) as minId, max(b.id) as maxId, count(1) as countfrom storage_batchnum b

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;@Getter@Setter@ToString@AllArgsConstructorpublic class ExportIdRangeDTO implements Serializable {        private int minId = 0;        private int maxId = 0;        private long count = 0;    public boolean isValid() {        return minId > 0 && maxId > 0;    }        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();                    }                }            } 

来源地址：https://blog.csdn.net/Jinliang_890905/article/details/132668549