文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2023-09-18 10:35

关注

慢SQL原因分析:

深度翻页

多表JOIN

大IN

id倒排序

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

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

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

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

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

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

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

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

终极方案:设置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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯