Open ymkNK opened 3 years ago
https://lllovol.com/posts/database/2020-10-30-query-test/
背景 需要查询到一批部门以及子部门，最终拿到这些部门下的人员信息，我们能够进行的操作如下 通过部门的目录前缀（前缀树）的方式，能够查询到所有子部门 能够通过部门查询到这个部门（不包含子部门）的所有人员 已经添加索引 方法一 思路 找到每一个需要排除的部门，以及每一个排除的部门的子部门，然后分别查出来需要排除的人,最后通过stream的flatMap进行聚合 return allDepartment.stream().flatMap(department -> { List<EmployeeDetail> allByDepartmentId = employeeRepo.findAllByDepartmentId(department.getDepartmentId()); return allByDepartmentId.stream(); }).collect(Collectors.toList()); 耗费时间 30616ms 方法二 思路 找到每一个需要排除的部门，将部门聚合，分别查询 List<Department> allDepartment = findAllChildDepartments(byId.get()); return employeeRepo.findAllByDepartmentIdIsIn(allDepartment.stream().map(Department::getDepartmentId).collect(Collectors.toList())); List<EmployeeDetail> excludeEmployees = excludeDepartments.stream().flatMap(department -> serviceV2.findAllEmployeesByDepartmentId(department.getDepartmentId(), 1).stream()).collect(Collectors.toList()); 耗费时间 20057ms 方法三 思路 将所有需要排除的部门聚合，统一查询 @Override public List<EmployeeDetail> findAllEmployeesByDepartmentsId(Iterable<String> departmentIds) { return employeeRepo.findAllByDepartmentIdIsIn(departmentIds); } List<Department> allExcludedDepartments = excludeDepartments.stream().flatMap(department -> serviceV2.findAllChildDepartments(department).stream()).collect(Collectors.toList()); List<EmployeeDetail> excludeEmployees = serviceV2.findAllEmployeesByDepartmentsId(allExcludedDepartments.stream().map(Department::getDepartmentId).collect(Collectors.toList())); 耗费时间 26058ms 时间耗费反而多了，因为对数据库的查询次数变多了
https://lllovol.com/posts/database/2020-10-30-query-test/
背景 需要查询到一批部门以及子部门，最终拿到这些部门下的人员信息，我们能够进行的操作如下 通过部门的目录前缀（前缀树）的方式，能够查询到所有子部门 能够通过部门查询到这个部门（不包含子部门）的所有人员 已经添加索引 方法一 思路 找到每一个需要排除的部门，以及每一个排除的部门的子部门，然后分别查出来需要排除的人,最后通过stream的flatMap进行聚合 return allDepartment.stream().flatMap(department -> { List<EmployeeDetail> allByDepartmentId = employeeRepo.findAllByDepartmentId(department.getDepartmentId()); return allByDepartmentId.stream(); }).collect(Collectors.toList()); 耗费时间 30616ms 方法二 思路 找到每一个需要排除的部门，将部门聚合，分别查询 List<Department> allDepartment = findAllChildDepartments(byId.get()); return employeeRepo.findAllByDepartmentIdIsIn(allDepartment.stream().map(Department::getDepartmentId).collect(Collectors.toList())); List<EmployeeDetail> excludeEmployees = excludeDepartments.stream().flatMap(department -> serviceV2.findAllEmployeesByDepartmentId(department.getDepartmentId(), 1).stream()).collect(Collectors.toList()); 耗费时间 20057ms 方法三 思路 将所有需要排除的部门聚合，统一查询 @Override public List<EmployeeDetail> findAllEmployeesByDepartmentsId(Iterable<String> departmentIds) { return employeeRepo.findAllByDepartmentIdIsIn(departmentIds); } List<Department> allExcludedDepartments = excludeDepartments.stream().flatMap(department -> serviceV2.findAllChildDepartments(department).stream()).collect(Collectors.toList()); List<EmployeeDetail> excludeEmployees = serviceV2.findAllEmployeesByDepartmentsId(allExcludedDepartments.stream().map(Department::getDepartmentId).collect(Collectors.toList())); 耗费时间 26058ms 时间耗费反而多了，因为对数据库的查询次数变多了