慢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范围,发起多线程并发查询。
其中具体核心逻辑代码:文章来源:https://www.toymoban.com/news/detail-701631.html
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<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 <= #{maxId} </if>
按ID范围切分后,可用多线程并发查询导出
taskExecutor.submit文章来源地址https://www.toymoban.com/news/detail-701631.html
// 增加顺序按起点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();
}
}
}
到了这里,关于深度翻页导出导致慢SQL,mysqlCPU飙升优化方案的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!