数据统计查询优化
数据统计查询优化
当前项目中存在的问题
当前的数据统计模块中，营业额统计、用户统计和订单统计这三个接口的在业务层中的运行流程如下：
- 根据前端传来的起止日期计算期间每一天的日期并存入日期集合。
- 遍历日期集合得到每一天的日期，将该日期处理后再查询数据库中当天满足条件的数据。
- 将每次查询的结果进行处理后存入相应的结果集合。
- 将结果集合进行封装后返回。
在第二步中，查询的日期有几天，就会查询几次数据库，同时每次查询的数据量有很小。而比起数据库的查询操作本身，与数据库的连接非常耗费时间。这种做法无疑回导致性能大幅下降。
解决方案
每个接口都只与数据库进行一次连接，在这一次连接中查询出所有需要的数据，然后在Java中进行处理并返回。具体步骤如下：
- 根据前端传来的起止日期计算期间每一天的日期并存入日期集合。
- 查询起止日期范围内所有满足条件的数据。
- 将查询到的数据进行处理并封装返回。
代码开发
营业额统计
- 在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 > #{begin}
</if>
<if test="end != null">
and order_time < #{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 > #{begin}
</if>
<if test="end != null">
and create_time < #{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 |
- 通过前后端联调测试验证了方法的正确性。
浙公网安备 33010602011771号