背景:BI项目,查询导购的等级,前端访问调接口太慢了,排查了其他sql,发现是自己把查询放在循环里的原因
错误示范:
修改方案:
//最终的结果
List<ResGuiderMonthSaleDTO> result = new ArrayList<>();
//个人
if (Objects.equals(groupType, "personal")) {
//月销售
result = biGuiderSaleMapper.getMonthSalePersonal(101L, startDate, endDate,lastStartDate, groupId, personalName,year,month,lastyear,lastmonth);
//匹配粉丝数和日净销售
List<ResFanNumDTO> fanNums = biGuiderSaleMapper.getFanNums(101L, startDate, endDate,lastStartDate, "guiderId");
List<BiGetValueDTO> daySalePrice = biGuiderSaleMapper.getDaySalePrice(101L, startDate, endDate, "guiderId");
//查询导购等级
List<BiGetValueDTO> allGuiderLevel = this.biGuiderSaleMapper.getAllGuiderLevel();
//根据id分
Map<Long, ResFanNumDTO> fanNumsMap = fanNums.stream()
.collect(Collectors.toMap(ResFanNumDTO::getId, Function.identity()));
Map<Long, BiGetValueDTO> daySalePriceMap = daySalePrice.stream()
.collect(Collectors.toMap(BiGetValueDTO::getId, Function.identity()));
Map<Long, BiGetValueDTO> guiderLevelMap = allGuiderLevel.stream()
.collect(Collectors.toMap(BiGetValueDTO::getId, Function.identity()));
for (ResGuiderMonthSaleDTO one : result) {
if (fanNumsMap.containsKey(one.getGuiderId())) {
one.setTotalFanNums(fanNumsMap.get(one.getGuiderId()).getTotalFanNums());//总粉丝数 endDate之前加的所有好友
one.setLastTotalFanNums(fanNumsMap.get(one.getGuiderId()).getLastTotalFanNums());//上期总粉丝数
one.setLastLastTotalFanNums(fanNumsMap.get(one.getGuiderId()).getLastLastTotalFanNums());//环比上期总粉丝数
one.setFriendsCount(fanNumsMap.get(one.getGuiderId()).getFriendsCount());//总好友数 当月及当月之前加的好友
one.setTotalAddSum(fanNumsMap.get(one.getGuiderId()).getTotalAddSum());//总加粉数 当月一共加了多少粉丝
}
if (daySalePriceMap.containsKey(one.getGuiderId())) {
one.setDaySalePrice(daySalePriceMap.get(one.getGuiderId()).getDecimal());
}
if (guiderLevelMap.containsKey(one.getGuiderId())) {
one.setGuiderLevelName(guiderLevelMap.get(one.getGuiderId()).getValue());
}
}
}
总结:尽量减少访问数据库的请求,导购数据本就不多,几十条记录,一次性把数据全拿出来,把对数据的处理放到内存中,会快很多。