深度翻页导出导致慢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">selectmin(b.id) as minId, max(b.id) as maxId, count(1) as countfrom 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<ExportIdRangeDTO> splitByPageCount(int pageCount) {List<ExportIdRangeDTO> splitList = new ArrayList<ExportIdRangeDTO>();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());}
}

<if test="minId != null and maxId != null">and b.id >= #{minId} and b.id &lt;= #{maxId}
</if>

按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<ExportIdRangeDTO> idRangeList = idRangeRes.splitByPageCount(pageCnt);AtomicInteger pageNum = new AtomicInteger(0);for (ExportIdRangeDTO idRange : idRangeList) {int pn = pageNum.incrementAndGet();Map<String, Object> 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<StorageReportViewVo> list = exportMap.get(i).get().getList();ExportExcelUtil.insertDataToExcel(work, colName, list, line, true);line = line + list.size();}}}