資料統計查詢最佳化

zgg1h發表於2024-07-28

資料統計查詢最佳化

當前專案中存在的問題

當前的資料統計模組中，營業額統計、使用者統計和訂單統計這三個介面的在業務層中的執行流程如下：

  1. 根據前端傳來的起止日期計算期間每一天的日期並存入日期集合。
  2. 遍歷日期集合得到每一天的日期，將該日期處理後再查詢資料庫中當天滿足條件的資料。
  3. 將每次查詢的結果進行處理後存入相應的結果集合。
  4. 將結果集合進行封裝後返回。

在第二步中，查詢的日期有幾天，就會查詢幾次資料庫，同時每次查詢的資料量有很小。而比起資料庫的查詢操作本身，與資料庫的連線非常耗費時間。這種做法無疑回導致效能大幅下降。

解決方案

每個介面都只與資料庫進行一次連線，在這一次連線中查詢出所有需要的資料，然後在Java中進行處理並返回。具體步驟如下：

  1. 根據前端傳來的起止日期計算期間每一天的日期並存入日期集合。
  2. 查詢起止日期範圍內所有滿足條件的資料。
  3. 將查詢到的資料進行處理並封裝返回。

程式碼開發

營業額統計

  • 在ReportServiceImpl中修改getTurnoverStatistics方法：
@Override
public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
    List<LocalDate> dateList = getDateList(begin, end); //當前集合用於存放從begin到end範圍內每天的日期
    BigDecimal[] turnoverList = new BigDecimal[dateList.size()]; //當前集合用於存放從begin到end範圍內每天的營業額

    Arrays.fill(turnoverList, BigDecimal.ZERO); //初始化營業額陣列

    //查詢從begin到end範圍內狀態為已完成的所有訂單資料
    Map map = new HashMap<>();
    map.put("begin", LocalDateTime.of(begin, LocalTime.MIN));
    map.put("end", LocalDateTime.of(end, LocalTime.MAX));
    map.put("status", Orders.COMPLETED);
    List<Orders> ordersList = orderMapper.getByMap(map);

    //把查出來的訂單的營業額加上
    for (Orders orders : ordersList) {
        LocalDate orderTime = orders.getOrderTime().toLocalDate();
        int period = Period.between(begin, orderTime).getDays();
        turnoverList[period] = turnoverList[period].add(orders.getAmount());
    }

    //將集合轉換為字串
    String dateListString = StringUtils.join(dateList, ",");
    String turnoverListString = StringUtils.join(turnoverList, ",");

    //構造TurnoverReportVO並返回
    TurnoverReportVO turnoverReportVO = TurnoverReportVO.builder()
            .dateList(dateListString)
            .turnoverList(turnoverListString)
            .build();
    return turnoverReportVO;
}
  • 在OrderMapper介面中宣告getByMap方法：
List<Orders> getByMap(Map map);
  • 在OrderMapper.xml中編寫getByMap方法的SQL語句：
<select id="getByMap" resultType="com.sky.entity.Orders">
    select * from orders
    <where>
        <if test="begin != null">
            and order_time &gt; #{begin}
        </if>
        <if test="end != null">
            and order_time &lt; #{end}
        </if>
        <if test="status != null">
            and status = #{status}
        </if>
    </where> order by order_time asc
</select>

使用者統計

  • 在ReportServiceImpl中修改getUserStatistics方法：
@Override
public UserReportVO getUserStatistics(LocalDate begin, LocalDate end) {
    List<LocalDate> dateList = getDateList(begin,end); //當前集合用於存放從begin到end範圍內每天的日期
    int[] totalUserList = new int[dateList.size()]; //當前集合用於存放從begin到end範圍內每天的總使用者數
    int[] newUserList = new int[dateList.size()]; //當前集合用於存放從begin到end範圍內每天新增的使用者數

    //查詢註冊時間在begin到end範圍內的所有使用者資料
    List<User> userList = userMapper.getByBeginAndEndTime(
            LocalDateTime.of(begin, LocalTime.MIN),
            LocalDateTime.of(end, LocalTime.MAX));

    //根據查詢到的使用者資料計算每天的新增使用者數
    for (User user : userList) {
        LocalDate createTime = user.getCreateTime().toLocalDate();
        int period = Period.between(begin, createTime).getDays();
        newUserList[period]++;
    }

    //查詢begin時間之前的總使用者數
    Map map = new HashMap<>();
    map.put("end", LocalDateTime.of(begin, LocalTime.MIN));
    Integer totalUser = userMapper.countByMap(map);

    //計算每天的總使用者數
    totalUserList[0] = totalUser + newUserList[0];
    for (int i = 1; i < dateList.size(); i++) {
        totalUserList[i] = totalUserList[i - 1] + newUserList[i];
    }

    //將集合轉換為字串
    String dateListString = StringUtils.join(dateList, ",");
    String totalUserListString = StringUtils.join(totalUserList, ',');
    String newUserListString = StringUtils.join(newUserList, ',');

    //構造UserReportVO並返回
    UserReportVO userReportVO = UserReportVO.builder()
            .dateList(dateListString)
            .totalUserList(totalUserListString)
            .newUserList(newUserListString)
            .build();
    return userReportVO;
}
  • 在OrderMapper介面中宣告getByBeginAndEndTime方法：
List<User> getByBeginAndEndTime(LocalDateTime begin, LocalDateTime end);
  • 在OrderMapper.xml中編寫getByBeginAndEndTime方法的SQL語句：
<select id="getByBeginAndEndTime" resultType="com.sky.entity.User">
    select * from user
    <where>
        <if test="begin != null">
            and create_time &gt; #{begin}
        </if>
        <if test="end != null">
            and create_time &lt; #{end}
        </if>
    </where>
</select>

訂單統計

  • 在ReportServiceImpl中修改getOrdersStatistics方法：
@Override
public OrderReportVO getOrdersStatistics(LocalDate begin, LocalDate end) {
    List<LocalDate> dateList = getDateList(begin, end); //當前集合用於存放從begin到end範圍內每天的日期
    int[] orderCountList = new int[dateList.size()]; //當前集合用於存放從begin到end範圍內每天的訂單數
    int[] validOrderCountList = new int[dateList.size()]; //當前集合用於存放從begin到end範圍內每天的有效訂單數

    //查詢從begin到end範圍內的所有訂單資料
    Map map = new HashMap<>();
    map.put("begin", LocalDateTime.of(begin, LocalTime.MIN));
    map.put("end", LocalDateTime.of(end, LocalTime.MAX));
    List<Orders> ordersList = orderMapper.getByMap(map);

    //把查出來的訂單數以及已完成的訂單數加上
    for (Orders orders : ordersList) {
        LocalDate orderTime = orders.getOrderTime().toLocalDate();
        int period = Period.between(begin, orderTime).getDays();
        orderCountList[period]++;
        if (orders.getStatus().equals(Orders.COMPLETED)) {
            validOrderCountList[period]++;
        }
    }

    Integer totalOrderCount = Arrays.stream(orderCountList).reduce(Integer::sum).getAsInt(); //計算訂單總數
    Integer validOrderCount = Arrays.stream(validOrderCountList).reduce(Integer::sum).getAsInt(); //計算有效訂單總數
    Double orderCompletionRate = totalOrderCount == 0 ? 0.0 : validOrderCount.doubleValue() / totalOrderCount; //計算訂單完成率

    //將集合轉換為字串
    String dateListString = StringUtils.join(dateList, ",");
    String orderCountListString = StringUtils.join(orderCountList, ',');
    String validOrderCountListString = StringUtils.join(validOrderCountList, ',');

    //構造OrderReportVO並返回
    OrderReportVO orderReportVO = OrderReportVO.builder()
            .dateList(dateListString)
            .orderCountList(orderCountListString)
            .validOrderCountList(validOrderCountListString)
            .totalOrderCount(totalOrderCount)
            .validOrderCount(validOrderCount)
            .orderCompletionRate(orderCompletionRate)
            .build();
    return orderReportVO;
}

功能測試

  • 透過介面文件測試方法的執行時間（測試時查詢近30日資料），如下表所示：
介面 最佳化前執行時間 最佳化後執行時間
營業額統計 98ms 37ms
使用者統計 117ms 4ms
訂單統計 112ms 9ms
  • 透過前後端聯調測試驗證了方法的正確性。

相關文章