logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

【原】系统优化查询：SkyWalking排查

 流楚丶格念 2024-11-15 发布于河北

文章目录

排查问题

SkyWalking里这个请求时间太长了，然后优化一下

在这里插入图片描述

PUT:/mobile/replay/replaySummaryListSelfDuration:1msTotalDuration:10325ms

导出为：
在这里插入图片描述

原逻辑：

public List<ArchivesReplayDotSumRespDTO> replaySummaryList(IPage<ArchivesReplayDotSearchDTO> page, ArchivesReplayDotSearchDTO param) {
    CurrentUserInfo currentUserInfo = ServiceUserInfoUtil.getCurrentUserInfo();
    Assert.notNull(currentUserInfo, "用户信息不存在");
    PermissionDetailRespDTO dp = dataPermissionUtil.getCurrentUserOrgDataPerm();
    switch (dp.getAuthorizeStatus()) {
        case ALL_PERMITTED:
            //全部授权，等于不做过滤
            param.setDataPermissimOrgIds(null);
            break;
        case WHITE_LIST:
            //白名单列表
            List<Long> orgIds = dp.getPerms().stream().map(e -> Long.valueOf(e.getDataPk())).collect(Collectors.toList());
            param.setDataPermissimOrgIds(orgIds);
            break;
        default:
            break;
    }
    List<ArchivesReplayDotSumRespDTO> replaySummaryReportList = archivesReplayDotMapper.replaySummaryReportList(page, param);
    Optional.ofNullable(replaySummaryReportList).orElse(Collections.emptyList())
            .forEach(mission -> {
                param.setMissionId(mission.getMissionId());
                param.setBusinessManagerCode(mission.getBusinessManagerCode());
                Integer totalNum=mission.getTotalNum().intValue();
                Integer completeNum = archivesReplayDotMapper.completeNum(param).intValue();;
                Integer optimizeNum = archivesReplayDotMapper.optimizeNum(param).intValue();;
                Integer incompleteNum = mission.getTotalNum()-completeNum;
                BigDecimal completeRate = new BigDecimal(completeNum).divide(new BigDecimal(totalNum == 0 ? 1 : totalNum), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100));
                mission.setCompleteNum(completeNum);
                mission.setOptimizeNum(optimizeNum);
                mission.setIncompleteNum(incompleteNum);
                mission.setCompleteRate(completeRate);
            });
    return replaySummaryReportList;


}


    <select id="replaySummaryReportList"
            resultType="com.meicloud.mcu.archives.api.dto.response.ArchivesReplayDotSumRespDTO">
        SELECT DISTINCT M.MISSION_ID,M.MISSION_CODE, M.MISSION_BEGIN_DATE,
        M.MISSION_END_DATE,
                        M.MISSION_NAME,
                        O.NAME AS orgName,
                        O.NAME_PATH,
                        SM.BUSINESS_MANAGER as businessManagerCode,
                        U.NAME AS businessManagerName,
                        count(distinct t.dot_code) as totalNum
        FROM (SELECT DISTINCT DOT.REPLAY_DOT_ID,
                              DOT.DOT_CODE,
                              DOT.DOT_ID,
                              DOT.DOT_NAME,
                              DOT.REPLAY_TYPE,
                              DOT.REPLAY_STATUS,
                              DOT.REPLAY_USER_CODE,
                              DOT.REPLAY_USER_NAME,
                              DOT.REPLAY_DATE,
                              DOT.MISSION_ID,
                              DOT.SERVICE_MANAGER_CODE,
                              DOT.SERVICE_MANAGER_NAME,
                              DD.ACTUAL_CONTROLLER_CODE,
                              DD.DOT_DIVISION_ID,
                              DD.STATUS,
                              DD.ORG_ID,
                              DD.SETS_OF_BOOKS_ID,
                              DD.ITEM_CLASS_IDS
              FROM ARCHIVES_CENTER.ARCHIVES_REPLAY_DOT DOT
                       LEFT JOIN ARCHIVES_CENTER.ARCHIVES_DOT_DIVISION DD
                                 ON dot.DOT_ID = DD.DOT_ID
              UNION
              SELECT DISTINCT DOT.REPLAY_DOT_ID,
                              DOT.DOT_CODE,
                              DOT.DOT_ID,
                              DOT.DOT_NAME,
                              DOT.REPLAY_TYPE,
                              DOT.REPLAY_STATUS,
                              DOT.REPLAY_USER_CODE,
                              DOT.REPLAY_USER_NAME,
                              DOT.REPLAY_DATE,
                              DOT.MISSION_ID,
                              DOT.SERVICE_MANAGER_CODE,
                              DOT.SERVICE_MANAGER_NAME,
                              DT.ACTUAL_CONTROLLER_CODE,
                              DD.DOT_DIVISION_ID,
                              DD.STATUS,
                              DD.ORG_ID,
                              DD.SETS_OF_BOOKS_ID,
                              DD.ITEM_CLASS_IDS
              FROM ARCHIVES_CENTER.ARCHIVES_REPLAY_DOT DOT
                       LEFT JOIN ARCHIVES_CENTER.ARCHIVES_DOT_DIVISION DD
                                 ON dot.DOT_ID = DD.DOT_ID
                       LEFT JOIN ARCHIVES_CENTER.ARCHIVES_DISTRIBUTOR drt
                                 ON drt.DISTRIBUTOR_CODE = DD.distributor_code
                       LEFT JOIN ARCHIVES_CENTER.ARCHIVES_DISTRIBUTOR_BUY_SALE dt
                                 ON dt.DISTRIBUTOR_ID = drt.DISTRIBUTOR_ID
                                     AND dt.org_id = DD.org_id
                                     AND dt.STATUS = '2'
                                     and dt.item_class_ids = dd.item_class_ids) T
                 INNER JOIN ARCHIVES_CENTER.Archives_Agency aa
                            ON aa.actual_controller_code = T.actual_controller_code
                 INNER JOIN ARCHIVES_CENTER.ARCHIVES_AGENCY_SUP_MAP SM
                            ON SM.AGENCY_ID = AA.AGENCY_ID
                                AND SM.ORG_ID = T.ORG_ID
--                                 and sm.item_class_ids = T.item_class_ids
                                AND SM.SALE_ORG_CODE NOT IN ('6315')  and aa.status='2' AND SM.status = '3'
                 LEFT JOIN ARCHIVES_CENTER.ARCHIVES_REPLAY_MISSION M
                           ON M.MISSION_ID = T.MISSION_ID
                 LEFT JOIN SYSTEM_CENTER.SYSTEM_ORG O
                           ON O.ORG_ID = T.ORG_ID
                 LEFT JOIN SYSTEM_CENTER.SYSTEM_USER U
                           ON U.LOGIN_ID = SM.BUSINESS_MANAGER
        where T.SETS_OF_BOOKS_ID = 1
          AND SM.business_manager IS NOT NULL
--           AND T.ITEM_CLASS_IDS IN ('4798')
          AND T.DOT_DIVISION_ID IN
              (select DOT_DIVISION_ID
               from (select row_number() over(partition by DOT_ID order by STATUS DESC, LAST_UPDATE_DATE desc) rn,
                            D.DOT_DIVISION_ID
                     from ARCHIVES_CENTER.ARCHIVES_DOT_DIVISION D
                     WHERE D.DOT_ID = T.DOT_ID
                       AND D.ITEM_CLASS_IDS = T.ITEM_CLASS_IDS
                       AND T.SETS_OF_BOOKS_ID = D.SETS_OF_BOOKS_ID)
               where rn = 1)
        <if test="req != null and req.dataPermissimOrgIds != null and req.dataPermissimOrgIds.size() > 0">
            AND EXISTS (
            SELECT 1 FROM SYSTEM_CENTER.SYSTEM_ORG so WHERE so.ORG_ID = T.ORG_ID
            START WITH so.ORG_ID in
            <foreach collection="req.dataPermissimOrgIds" item="orgTemp" open="(" separator="," close=")">
                #{orgTemp}
            </foreach>
            CONNECT BY PRIOR so.ORG_ID = so.PARENT_ID
            )
        </if>
        <if test="req != null and req.missionId != null">
            and M.MISSION_ID = #{req.missionId}
        </if>
        <if test="req != null and req.missionCode != null and req.missionCode != ''">
            and M.MISSION_CODE = #{req.missionCode}
        </if>
        <if test="req != null and req.missionName != null and req.missionName != ''">
            and M.MISSION_NAME  LIKE CONCAT('%',CONCAT(#{req.missionName},'%'))
        </if>
        <if test="req != null and req.missionStatus != null and req.missionStatus != ''">
            and M.MISSION_STATUS = #{req.missionStatus}
        </if>
        <if test="req != null and req.orgId != null">
            and O.ORG_ID = #{req.orgId}
        </if>
        <if test="req != null and req.actualControllerCode != null and req.actualControllerCode != ''">
            and AA.ACTUAL_CONTROLLER_CODE = #{req.actualControllerCode}
        </if>
        <if test="req != null and req.dotCode != null and req.dotCode != ''">
            and T.DOT_CODE = #{req.dotCode}
        </if>
        <if test="req != null and req.dotName != null and req.dotName != ''">
            and T.DOT_NAME LIKE CONCAT(CONCAT('%',#{req.dotName}),'%')
        </if>
        <if test="req != null and req.businessManagerCode != null and req.businessManagerCode != ''">
            and SM.BUSINESS_MANAGER = #{req.businessManagerCode}
        </if>
        <if test="req != null and req.businessManagerName != null and req.businessManagerName != ''">
            and U.NAME = #{req.businessManagerName}
        </if>
        <if test="req != null and req.serviceManagerCode != null and req.serviceManagerCode != ''">
            and T.SERVICE_MANAGER_CODE = #{req.serviceManagerCode}
        </if>
        <if test="req != null and req.serviceManagerName != null and req.serviceManagerName != ''">
            and T.SERVICE_MANAGER_NAME = #{req.serviceManagerName}
        </if>
        <if test="req != null and req.replayUserCode != null and req.replayUserCode != ''">
            and T.REPLAY_USER_CODE = #{req.replayUserCode}
        </if>
        <if test="req != null and req.replayUserName != null and req.replayUserName != ''">
            and T.REPLAY_USER_NAME = #{req.replayUserName}
        </if>
        <if test="req != null and req.missionBeginDate != null">
            <![CDATA[ and M.MISSION_BEGIN_DATE >= #{req.missionBeginDate} ]]>
        </if>
        <if test="req != null and req.missionEndDate != null">
            <![CDATA[ and M.MISSION_BEGIN_DATE < #{req.missionEndDate} ]]>+1
        </if>
        GROUP BY M.MISSION_ID,M.MISSION_CODE,M.MISSION_BEGIN_DATE,
        M.MISSION_END_DATE,
                 M.MISSION_NAME,
                 O.NAME,
                 O.NAME_PATH,
                 SM.BUSINESS_MANAGER,
                 U.NAME

    </select>

优化思路

要优化这个请求的性能，尤其是在SkyWalking中观察到的10325ms的总持续时间，我们可以从多个方面入手。以下是一些优化建议，分为代码和SQL查询两个方面：

一、代码优化

  1. 减少数据库调用次数：

    • 目前代码中对completeNum和optimizeNum的查询是针对每个mission的。可以考虑将这些查询合并到一次批量查询中，从而减少数据库的交互次数。

    示例：

    // 批量查询completeNum和optimizeNum
Map<Long, Integer> completeNums = archivesReplayDotMapper.batchCompleteNum(param, missionIds);
Map<Long, Integer> optimizeNums = archivesReplayDotMapper.batchOptimizeNum(param, missionIds);

Optional.ofNullable(replaySummaryReportList).orElse(Collections.emptyList())
    .forEach(mission -> {
        mission.setCompleteNum(completeNums.getOrDefault(mission.getMissionId(), 0));
        mission.setOptimizeNum(optimizeNums.getOrDefault(mission.getMissionId(), 0));
        // 其他计算逻辑...
    });

  2. 流处理：

    • 如果数据集较大，使用Java Streams时要注意性能。如果数据量大且操作复杂，考虑使用普通的循环来提高性能。

二、SQL查询优化

  1. 索引优化：

    • 确保在WHERE子句中使用的字段（例如MISSION_ID、ORG_ID等）上有适当的索引。索引可以大幅提高查询性能。

  2. 简化查询：

    • 查询中包含多个JOIN和UNION操作，这可能会增加查询的复杂性和执行时间。尝试拆分查询，减少使用UNION，或优化每个JOIN的逻辑。

  3. 避免SELECT DISTINCT：

    • 在某些情况下，DISTINCT可能会引起性能问题。如果业务逻辑允许，尽量避免使用。

  4. 子查询优化：

    • 检查子查询的性能，确保其有效。如果子查询返回大量数据，考虑将其转换为JOIN操作。

  5. 使用EXPLAIN分析查询：

    • 使用EXPLAIN命令分析SQL查询的执行计划，识别潜在的瓶颈和慢查询部分，并据此进行调整。

三、示例SQL优化

以下是对查询的部分优化示例：

  • 尝试减少UNION的使用，通过JOIN合并查询。
  • 简化查询条件，避免不必要的复杂性。
SELECT M.MISSION_ID, M.MISSION_CODE, COUNT(DISTINCT T.DOT_CODE) AS totalNum, ...
FROM ARCHIVES_CENTER.ARCHIVES_REPLAY_DOT T
LEFT JOIN ...
WHERE ...
GROUP BY M.MISSION_ID, M.MISSION_CODE

实践

优化后的Java代码

public List<ArchivesReplayDotSumRespDTO> replaySummaryList(IPage<ArchivesReplayDotSearchDTO> page, ArchivesReplayDotSearchDTO param) {
    CurrentUserInfo currentUserInfo = ServiceUserInfoUtil.getCurrentUserInfo();
    Assert.notNull(currentUserInfo, "用户信息不存在");
    PermissionDetailRespDTO dp = dataPermissionUtil.getCurrentUserOrgDataPerm();

    // 设置权限
    switch (dp.getAuthorizeStatus()) {
        case ALL_PERMITTED:
            param.setDataPermissimOrgIds(null);
            break;
        case WHITE_LIST:
            List<Long> orgIds = dp.getPerms().stream()
                .map(e -> Long.valueOf(e.getDataPk()))
                .collect(Collectors.toList());
            param.setDataPermissimOrgIds(orgIds);
            break;
        default:
            break;
    }

    // 批量获取数据
    List<ArchivesReplayDotSumRespDTO> replaySummaryReportList = archivesReplayDotMapper.replaySummaryReportList(page, param);
    if (replaySummaryReportList == null || replaySummaryReportList.isEmpty()) {
        return Collections.emptyList();
    }

    // 获取completeNum和optimizeNum的批量数据
    List<Long> missionIds = replaySummaryReportList.stream()
        .map(ArchivesReplayDotSumRespDTO::getMissionId)
        .collect(Collectors.toList());

    Map<Long, Integer> completeNums = archivesReplayDotMapper.batchCompleteNum(param, missionIds);
    Map<Long, Integer> optimizeNums = archivesReplayDotMapper.batchOptimizeNum(param, missionIds);

    // 处理数据
    for (ArchivesReplayDotSumRespDTO mission : replaySummaryReportList) {
        Integer totalNum = mission.getTotalNum().intValue();
        Integer completeNum = completeNums.getOrDefault(mission.getMissionId(), 0);
        Integer optimizeNum = optimizeNums.getOrDefault(mission.getMissionId(), 0);
        Integer incompleteNum = totalNum - completeNum;

        BigDecimal completeRate = new BigDecimal(completeNum)
            .divide(new BigDecimal(totalNum == 0 ? 1 : totalNum), 4, BigDecimal.ROUND_HALF_UP)
            .multiply(new BigDecimal(100));

        mission.setCompleteNum(completeNum);
        mission.setOptimizeNum(optimizeNum);
        mission.setIncompleteNum(incompleteNum);
        mission.setCompleteRate(completeRate);
    }

    return replaySummaryReportList;
}

batchCompleteNum和batchOptimizeNum是你在优化代码中提到的批量查询方法。这些方法的作用是一次性从数据库中获取多个missionId对应的completeNum和optimizeNum，而不是在循环中对每个missionId逐一执行查询。这样可以减少数据库的访问次数，提高性能。

这些方法的具体实现取决于你的数据库和查询需求。一般来说，它们可以通过一次查询批量返回多个missionId的completeNum和optimizeNum，然后在Java代码中进行处理。

  1. batchCompleteNum方法
    这个方法的作用是批量查询completeNum。可以根据多个missionId一次性查询所有相关数据，而不需要对每个missionId单独执行查询。

实现简写

public Map<Long, Integer> batchCompleteNum(ArchivesReplayDotSearchDTO param, List<Long> missionIds) {
    // 假设你已经有了对应的mapper
    return archivesReplayDotMapper.batchCompleteNum(param, missionIds);
}

Mapper中的SQL示例

<select id="batchCompleteNum" resultType="java.lang.Integer">
    SELECT M.MISSION_ID, COUNT(*) AS completeNum
    FROM ARCHIVES_CENTER.ARCHIVES_REPLAY_DOT T
    INNER JOIN ARCHIVES_CENTER.ARCHIVES_REPLAY_MISSION M ON M.MISSION_ID = T.MISSION_ID
    WHERE T.MISSION_ID IN
        <foreach collection="missionIds" item="missionId" open="(" separator="," close=")">
            #{missionId}
        </foreach>
    AND T.REPLAY_STATUS = 'completed'  <!-- 假设"completed"是完成的状态 -->
    GROUP BY M.MISSION_ID
</select>
  1. batchOptimizeNum方法
    batchOptimizeNum方法的作用是批量查询optimizeNum，可以类似地进行批量查询。

实现简写

public Map<Long, Integer> batchOptimizeNum(ArchivesReplayDotSearchDTO param, List<Long> missionIds) {
    // 假设你已经有了对应的mapper
    return archivesReplayDotMapper.batchOptimizeNum(param, missionIds);
}

Mapper中的SQL示例

<select id="batchOptimizeNum" resultType="java.lang.Integer">
    SELECT M.MISSION_ID, COUNT(*) AS optimizeNum
    FROM ARCHIVES_CENTER.ARCHIVES_REPLAY_DOT T
    INNER JOIN ARCHIVES_CENTER.ARCHIVES_REPLAY_MISSION M ON M.MISSION_ID = T.MISSION_ID
    WHERE T.MISSION_ID IN
        <foreach collection="missionIds" item="missionId" open="(" separator="," close=")">
            #{missionId}
        </foreach>
    AND T.REPLAY_STATUS = 'optimized'  <!-- 假设"optimized"是优化的状态 -->
    GROUP BY M.MISSION_ID
</select>

优化后的SQL查询

SELECT 
    M.MISSION_ID,
    M.MISSION_CODE,
    COUNT(DISTINCT T.DOT_CODE) AS totalNum,
    -- 其他字段
FROM 
    ARCHIVES_CENTER.ARCHIVES_REPLAY_DOT T
LEFT JOIN 
    ARCHIVES_CENTER.ARCHIVES_DOT_DIVISION DD ON T.DOT_ID = DD.DOT_ID
LEFT JOIN 
    ARCHIVES_CENTER.ARCHIVES_REPLAY_MISSION M ON M.MISSION_ID = T.MISSION_ID
LEFT JOIN 
    SYSTEM_CENTER.SYSTEM_ORG O ON O.ORG_ID = T.ORG_ID
LEFT JOIN 
    SYSTEM_CENTER.SYSTEM_USER U ON U.LOGIN_ID = M.BUSINESS_MANAGER
WHERE 
    T.SETS_OF_BOOKS_ID = 1
    AND M.BUSINESS_MANAGER IS NOT NULL
    AND T.DOT_DIVISION_ID IN (
        SELECT DOT_DIVISION_ID
        FROM (
            SELECT 
                ROW_NUMBER() OVER (PARTITION BY DOT_ID ORDER BY STATUS DESC, LAST_UPDATE_DATE DESC) rn,
                D.DOT_DIVISION_ID
            FROM ARCHIVES_CENTER.ARCHIVES_DOT_DIVISION D
            WHERE D.DOT_ID = T.DOT_ID
              AND D.ITEM_CLASS_IDS = T.ITEM_CLASS_IDS
              AND T.SETS_OF_BOOKS_ID = D.SETS_OF_BOOKS_ID
        )
        WHERE rn = 1
    )
    <if test="req != null and req.dataPermissimOrgIds != null and req.dataPermissimOrgIds.size() > 0">
        AND EXISTS (
            SELECT 1 FROM SYSTEM_CENTER.SYSTEM_ORG so 
            WHERE so.ORG_ID = T.ORG_ID
            START WITH so.ORG_ID IN (
                <foreach collection="req.dataPermissimOrgIds" item="orgTemp" open="(" separator="," close=")">
                    #{orgTemp}
                </foreach>
            )
            CONNECT BY PRIOR so.ORG_ID = so.PARENT_ID
        )
    </if>
    -- 其他查询条件
GROUP BY 
    M.MISSION_ID,
    M.MISSION_CODE

优化总结

  1. 减少了数据库调用次数：通过批量查询completeNum和optimizeNum，减少了对数据库的交互次数。
  2. 提高了代码可读性：将条件设置和数据处理逻辑进行了清晰的分离。
  3. SQL查询的简化：合并了一些JOIN，并消除了不必要的复杂性。

    转藏 分享 献花（0）

    来自： 流楚丶格念 > 《待分类》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多