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

这篇具有很好参考价值的文章主要介绍了深度翻页导出导致慢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要缓冲全量数据,内存飙升

深度翻页导出导致慢SQL,mysqlCPU飙升优化方案,JAVA,大数据的应用,sql,数据库

方案二步骤:

(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<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文章来源地址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模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 积木报表Excel数据量大导出慢导不出问题、大量数据导不出问题优化方案和分析解决思路(优化前一万多导出失败,优化后支持百万级跨库表导出)

    原积木导出有两种导出,直接导出和大数据导出(大数据导出是做了优化去掉了一些样式之类的,性能更好) 实测中发现 原积木大数据导出性能:1万条数据导出耗时30秒,1.5万条耗时1.5分钟导出失败,数据超过一万条后经常导出失败,还会导致容器实例探活失败/内存撑爆重

    2024年04月11日
    浏览(78)
  • 如何解决vscode频繁唤起git for Windows 导致电脑内存占用多大,cpu占用率飙升、风扇狂转问题

    亲爱的小伙伴,你是不是发现最近电脑风扇狂转,在打开任务管理器后发现 git for window 占用了大量的cup和内存。不要担心,一招教你解决这个问题。 如下图, 希望可以帮助到你哟!

    2024年02月12日
    浏览(69)
  • 记一次翻页性能优化

       由于是公司项目,所以不方便给出代码或者视频,只能列一些自己画的流程图。    大致情况如上,前端有7个显示区。在对其进行滚动翻页的时候,存在以下问题:    通过分析代码,调查log发现,翻页切换平均耗时在600ms。其主要的业务逻辑如下: 主要问题有

    2024年02月05日
    浏览(48)
  • MySQL慢SQL优化方案汇总

      ⛰️个人主页:       蒾酒 🔥系列专栏 : 《mysql经验总结》 🌊山高路远,行路漫漫,终有归途 目录 写在前面 优化思路 避免查询不必要的列 分页优化 索引优化 JOIN优化 排序优化 UNION 优化 写在最后 本文介绍了MySQL常见的优化慢sql的手段,坚持看完相信对你有帮助。 同时

    2024年04月17日
    浏览(33)
  • 12条 SQL 优化方案(非常实用)

    文章目录 一、SQL语句及索引的优化 SQL语句的优化 尽量避免使用子查询 用IN来替换OR 读取适当的记录LIMIT M,N,而不要读多余的记录 禁止不必要的Order By排序 总和查询可以禁止排重用union all 避免随机取记录 将多次插入换成批量Insert插入 只返回必要的列,用具体的字段列表代替

    2024年02月10日
    浏览(32)
  • PostgreSQL慢sql原因和优化方案

    1. 数据库服务器硬件不足,例如CPU、内存、磁盘I/O等。 2. 数据库中存在大量的慢查询,需要优化查询语句或索引。 3. 数据库中存在大量的并发连接,需要调整数据库连接池的大小。 4. 数据库中存在大量的锁争用,需要优化事务隔离级别或调整锁等待时间。 5. 数据库中存在大

    2024年02月06日
    浏览(42)
  • PostgreSQL查询慢sql原因和优化方案

    PostgreSQL sql查询慢优化方案有一下几种解决方案: 查询慢sql的执行会话,关闭进程。 查看数据库后台连接进程 查看数据库后台连接进程,但是此条SQL不包含当前查询进程 查看当前慢SQL,例如查询执行时间超过1秒的SQL 可以使用pg_terminate_backend()终止连接。您必须是超级用户才能

    2024年02月13日
    浏览(60)
  • MySQL数据库CPU飙升到100%解决方案

    当cpu飙升到100%时,先用操作系统命令top命令观察是不是mysqld占用导致的,如果不是,找出占用高的进程,并进行相关处理。 进入mysql命令行 查看慢查询SQL是否启用:ON是开启,OFF是关闭。 show variables like ‘log_slow_queries’; 开启慢查询日志 set global log_slow_queries = on; 如果是mysql

    2024年02月16日
    浏览(48)
  • 面试中常被问到sql优化几种方案

    目录 一、索引优化 二、合理的查询设计 三、分页优化: 四、内存管理和缓存: 五、合理使用批量操作: 六、使用连接池: 七、分区表: 八、避免使用SELECT : 九、数据库升级和优化器统计信息: 十、避免不必要的约束和触发器: 十一、使用EXPLAIN分析查询计划: 十二、

    2024年02月10日
    浏览(68)
  • Element UI 多选表格【翻页多选】全能版(含翻页多选数据反显、toggleRowSelection失效的原因解析和解决方案)

    data中定义 selectedList 数组保存选中的数据 在页面初次渲染、翻页、切换每页数据数量等每次重新获取表格数据渲染表格时,都根据 selectedList 勾选表格中已经选中的行 切换单行勾选状态时,判断是选中还是取消选中,选中则增加选中项,取消选中则移除选中项。 切换全选和

    2023年04月10日
    浏览(35)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包