之前自己写的项目数据量都比较小,对数据库的操作只要可以跑通就不会有太大的性能问题。最近在实习中学习到了两种巧妙的查询优化小技巧,记录一下以备后用~
in查询+HashMap避免重复查询数据库
使用背景:在某一查询中,需要根据从企业信息表查询到的每个企业的更新人id,到员工表中查询更新人的姓名,一起返回到前端。
按照普通的思路,那肯定就是先查询企业信息表得到一个list,再对list进行for循环,为每一个元素查询对应的更新人id。这种做法在for循环中进行了数据库查询,多次访问数据库,会导致查询效率降低且增加了数据库压力。
优化后的做法:在查询企业信息表后,统一提取list中的更新人id字段,到员工表中进行in查询,一次性得到会用到的所有员工,再根据员工的id和姓名构建hashmap,使用for循环遍历企业信息,根据hashmap中的信息来添加更新人姓名。这种做法只查询了一次数据库,并通过hashmap的映射,将添加更新人姓名的时间复杂度控制在O(n)。
List<Company> companyList = companyRepo.getList(); List<String> updateIds = companyList.stream().filter(x->{ StringUtils.isNotBlank(x.getUpdateId()); }).map(Company::getUpdateId).collect(Collectors.toList); //使用in查询一次得到所有需要根据id返回的员工信息 List<Staff> staffs = staffService.listStaffByStaffNos(updateIds); //将list转化为map,key为更新人id,value为更新人姓名 Map<String,String> updateIdsToName = staffs.stream().collect(Collectors.toMap(Staff::getStaffNo,Staff::getStaffName)); companyList.forEach(conmany->{ company.setUpdateName(updateIdsToName.get(company,getUpdateId())) });
通过主键索引进行分页查询
使用背景:在一个行数5位数的表中进行分页查询。
按照普通的思路,使用SELECT * FROM table_name LIMIT M,N
即可,但是这种方式的实现是通过全表扫描,速度会很慢,且有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)。
优化后的做法:对数据进行升序排序(ASC),每次分页记录当前最大的id记为startId,进行下一次分页时筛选出id比startId更大的数据,再拼接LIMIT来实现分页。这样既使用了主键索引提高了查询效率,又能保证数据库结果集返回稳定。
public void exec() { Long startId = 0L; while(true){ List<Case> list = queryByGtId(startId); if(CollectionUtils.isEmpty(list)){ break; } startId = list.get(list.size() - 1).getId(); } } public List<CaseEvidencePo> queryByGtId(Long id) { LambdaQueryWrapper<Case> queryWapper = new LambdaQueryWrapper<>(); queryWapper.gt(Objects.nonNull(id),Case::getId,id) .orderByAsc(Case::getId) .last("LIMIT 1000"); return baseMapper.selectList(queryWrapper); }
之前看到SQL的优化总是没什么感触,这次在业务逻辑中见到了SQL优化的实际应用,感觉还是非常巧妙的。后续还是要多在实践中学习才是。