记录一次代码重构，接口响应由25秒到3秒以内，内附List「Map」里的某一个Key的排序

最新推荐文章于 2022-11-14 15:37:11 发布

赵利伟@

最新推荐文章于 2022-11-14 15:37:11 发布

阅读量205

点赞数 1

文章标签： java mysql 大数据

本文链接：https://blog.csdn.net/weixin_43952697/article/details/107084753

版权

背景：

从300w和40w的两张表内查询数据，做业务处理，接口响应返回25s，(⊙o⊙)…看到这个响应内心是奔溃的。

直接上优化前的代码

public Map<String, Object> getSoftwareUsageComparisons(String beginDate, String endDate) {
    //定义结果集
    Map<String, Object> resultMap = new HashMap<>(2);

    try {
        //格式化xx软件名称串，逗号分隔
        List<String> seewoSoftwareNameList = Arrays.asList(seewoSoftwareComparisonName.split(","));
        //格式化yy软件名称串，逗号分隔
        List<String> hhtSoftwareNameList = Arrays.asList(hhtSoftwareComparisonName.split(","));

        //封装查询参数
        Map<String, Object> param = new HashMap<>(6);
        //为了保证线程安全使用如下方法
        param.put("beginDate", beginDate.concat(" 00:00:00"));
        param.put("endDate", endDate.concat(" 23:59:59"));
        param.put("deviceClass", "HIIP");


        /**
         * 根据软件名称，查询此时间段此软件的安装的设备数量
         */
        List<Map<String, Object>> hhtSoftwareInfoList = this.getSoftwareUseInfo(hhtSoftwareNameList, param);

        //查询希沃的软件使用情况
        List<Map<String, Object>> seewoSoftwareInfoList = this.getSoftwareUseInfo(seewoSoftwareNameList, param);

        //封装两个集合数据
        resultMap.put("hhtSoftwareInfoList", hhtSoftwareInfoList);
        resultMap.put("swSoftwareInfoList", seewoSoftwareInfoList);
    } catch (Exception e) {
        //返回异常处理结果
        logMsg.setE(e);
        logMsg.setMsg("获取软件使用对比异常");
        LogUtil.error(logMsg);
        //resultMap = null;
        return resultMap;
    }

    //返回结果
    return resultMap;
}

子类方法

private List<Map<String, Object>> getSoftwareUseInfo(List<String> softwareNameList, Map<String, Object> param) {
    logMsg.setOperation("根据软件名称串获取软件使用的相关数据:getSoftwareUseInfo");
    //格式化小数，保留两位
    NumberFormat nf = NumberFormat.getNumberInstance();
    nf.setMaximumFractionDigits(1);
    // 需要四舍五入
    nf.setRoundingMode(RoundingMode.UP);
    //查询软件使用情况
    List<Map<String, Object>> softwareInfoList = new ArrayList<>();
    try {
        //查询软件数据
        for (int i = 0; i < softwareNameList.size(); i++) {
            Map<String, Object> softwareInfoMap = new HashMap<>(4);
            //解析软件名称
            String softwareName = softwareNameList.get(i);
            param.put("softwareName", softwareName);
            //查询数据库此软件的安装的设备数量
            double installDeviceNumber = deviceInfoDao.getInstallDeviceNumber(param);
            //查询数据库此软件的使用时长
            double softwareUseDuration = deviceInfoDao.getSoftwareUseDuration(param);
            //计算平均使用时长 softwareUseDuration / installDeviceNumber
            double averageDuration;
            //分母不得为0
            if (0 == installDeviceNumber) {
                averageDuration = 0.0;
            } else {
                averageDuration = Double.parseDouble(nf.format(softwareUseDuration / installDeviceNumber / 30).replace(",", ""));
            }

            //封装本次结果
            softwareInfoMap.put("softwareName", softwareName);
            softwareInfoMap.put("averageDuration", averageDuration);
            softwareInfoMap.put("installDeviceNumber", installDeviceNumber);
            softwareInfoMap.put("softwareUseDuration", softwareUseDuration);

            softwareInfoList.add(softwareInfoMap);
        }
    } catch (NumberFormatException e) {
        logMsg.setE(e);
        logMsg.setMsg("解析处理软件时长异常");
        LogUtil.error(logMsg);
    }

    try {
        //对结果进行排序，增长率降序排列
        for (int i = 0; i < softwareInfoList.size(); i++) {
            for (int j = 0; j < softwareInfoList.size() - 1; j++) {
                if (Double.valueOf(softwareInfoList.get(i).get("averageDuration").toString()) > Double.valueOf(softwareInfoList.get(j).get("averageDuration").toString())) {
                    Map<String, Object> tempi = softwareInfoList.get(i);
                    Map<String, Object> tempj = softwareInfoList.get(j);
                    softwareInfoList.set(i, tempj);
                    softwareInfoList.set(j, tempi);
                }
            }
        }
    } catch (Exception e) {
        logMsg.setE(e);
        logMsg.setMsg("解析处理软件时长排序异常");
        LogUtil.error(logMsg);
    }

    return softwareInfoList;
}

看到这些，各位内心奔溃吗？一个for循环去查询数据库，数据库的保存量在百万级别，能不慢吗？

优化后：

public Map<String, Object> getSoftwareUsageComparison(String beginDate, String endDate) {

    //封装查询参数
    Map<String, Object> param = new HashMap<>(6);
    //为了保证线程安全使用如下方法
    param.put("beginDate", beginDate.concat(" 00:00:00"));
    param.put("endDate", endDate.concat(" 23:59:59"));
    param.put("deviceClass", "HIIP");

    //定义结果集
    Map<String, Object> resultMap = new LinkedHashMap<>(2);
    logMsg.setOperation("根据软件名称串获取软件使用的相关数据:getSoftwareUseInfo");
    //格式化小数，保留两位
    NumberFormat nf = NumberFormat.getNumberInstance();
    nf.setMaximumFractionDigits(1);
    // 需要四舍五入
    nf.setRoundingMode(RoundingMode.UP);
    //查询软件使用情况

    List<Map<String, Object>> hhtInfoList = new ArrayList<>();

    List<Map<String, Object>> seewoInfoList = new ArrayList<>();

    //格式化希沃软件名称串，逗号分隔
    List<String> seewoSoftwareNameList = Arrays.asList(seewoSoftwareComparisonName.split(","));
    //格式化鸿合软件名称串，逗号分隔
    List<String> hhtSoftwareNameList = Arrays.asList(hhtSoftwareComparisonName.split(","));

    try {
        //计算平均使用时长 softwareUseDuration / installDeviceNumber
        double averageDuration;
        //查询数据库此软件的安装的设备数量
        List<Map<String, Object>> installDeviceNumber = deviceInfoDao.getInstallDeviceNumbers(param);
        //查询数据库此软件的使用时长
        List<Map<String, Object>> softwareUseDuration = deviceInfoDao.getSoftwareUseDurations(param);
        for (int i = 0; i < installDeviceNumber.size(); i++) {
            Map<String, Object> seewoMap = new LinkedHashMap<>();
            Map<String, Object> hhtMap = new LinkedHashMap<>();
            double totalCount = Double.parseDouble(installDeviceNumber.get(i).get("count").toString());
            String totalSoftwareName = installDeviceNumber.get(i).get("softwareName").toString();
            for (int j = 0; j < softwareUseDuration.size(); j++) {
                double useDuration = Double.parseDouble(softwareUseDuration.get(j).get("useDuration").toString());
                String durationSoftwareName = softwareUseDuration.get(j).get("softwareName").toString();
                if (totalSoftwareName.equals(durationSoftwareName)) {
                    //分母不得为0
                    if (0 == totalCount) {
                        averageDuration = 0.0;
                    } else {
                        averageDuration = Double.parseDouble(nf.format(useDuration / totalCount / 30).replace(",", ""));
                    }
                    if (seewoSoftwareNameList.contains(totalSoftwareName)) {
                        seewoMap.put("averageDuration", averageDuration);
                        seewoMap.put("installDeviceNumber", totalCount);
                        seewoMap.put("softwareName", totalSoftwareName);
                        seewoMap.put("softwareUseDuration", useDuration);
                        seewoInfoList.add(seewoMap);
                    }
                    if (hhtSoftwareNameList.contains(totalSoftwareName)) {
                        hhtMap.put("averageDuration", averageDuration);
                        hhtMap.put("installDeviceNumber", totalCount);
                        hhtMap.put("softwareName", totalSoftwareName);
                        hhtMap.put("softwareUseDuration", useDuration);
                        hhtInfoList.add(hhtMap);
                    }
                }
            }
        }
        //对List<Map>里的某一项进行排序
        List<Map<String, Object>> orderBySeewoInfoList = orderBy(seewoInfoList);
        List<Map<String, Object>> orderByHhtInfoList = orderBy(hhtInfoList);
        //封装两个集合数据
        resultMap.put("hhtSoftwareInfoList", orderByHhtInfoList);
        resultMap.put("swSoftwareInfoList", orderBySeewoInfoList);

    } catch (NumberFormatException e) {
        logMsg.setE(e);
        logMsg.setMsg("解析处理软件时长异常");
        LogUtil.error(logMsg);
    }
    return resultMap;
}

一次查询，针对查询的结果在代码中进行处理，处理的具体思路就不过多表述了，详细说一下对List<Map<String,Object>>,Map内的某一个key进行降序排列

//对结果进行排序，增长率降序排列

for (int i = 0; i < softwareInfoList.size(); i++) {

for (int j = 0; j < softwareInfoList.size() - 1; j++) {

if (Double.valueOf(softwareInfoList.get(i).get("averageDuration").toString()) > Double.valueOf(softwareInfoList.get(j).get("averageDuration").toString())) { Map<String, Object> tempi = softwareInfoList.get(i); Map<String, Object> tempj = softwareInfoList.get(j);

softwareInfoList.set(i, tempj); softwareInfoList.set(j, tempi);

}

不要在for循环内去重复查询数据库，特别是是数据量大的情况下

赵利伟@

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
记录一次代码重构，接口响应由25秒到3秒以内，内附List「Map」里的某一个Key的排序

背景：从300w和40w的两张表内查询数据，做业务处理，接口响应返回25s，(⊙o⊙)…看到这个响应内心是奔溃的。直接上优化前的代码public Map<String, Object> getSoftwareUsageComparisons(String beginDate, String endDate) { //定义结果集 Map<String, Object> resultMap = new HashMap<>(2); try
复制链接

扫一扫