多关联带条件查询提速方法及Spring Data动态查询实现咨询

1. 数据库索引优化 #

• 给查询中频繁用于过滤、关联的字段添加针对性索引：
  • purchase_order表：创建(deleted, purchase_order_status, received_date)复合索引（覆盖WHERE核心过滤条件），同时给process_order_id外键加索引
  • items表：给supplier_id、item_group_id加单列索引
  • buyer_business表：给buyer_id、id加单列索引
  • business_supplier_mapping表：创建(supplier_id, buyer_business_id, deleted)复合索引
• 注意：索引需根据实际查询频率调整，避免过度索引导致写入性能下降。

2. 修复SQL拼接的性能与安全问题 #

原代码直接拼接参数到SQL字符串，存在SQL注入风险且无法复用数据库执行计划，改为参数绑定方式：

// 日期参数绑定示例
stringBuilder.append(" WHERE (purchase_order.deleted is null OR purchase_order.deleted is false) AND purchase_order.received_date BETWEEN :fromDate AND :toDate");
resultQuery.setParameter("fromDate", businessReportRequestDTO.getFromDate());
resultQuery.setParameter("toDate", businessReportRequestDTO.getToDate());

// ID列表参数绑定示例（避免字符串拼接）
if (businessReportRequestDTO.getItemGroupIdList() != null && !businessReportRequestDTO.getItemGroupIdList().isEmpty()) {
    stringBuilder.append(" and items.item_group_id in (:itemGroupIds)");
    resultQuery.setParameter("itemGroupIds", businessReportRequestDTO.getItemGroupIdList());
}

3. 分页逻辑优化 #

原代码先查询全量结果再分页，数据量大时性能极差，改为数据库级分页：

• 拆分查询：单独编写COUNT查询获取总条数，再执行带分页语法的查询（如MySQL用LIMIT ?, ?）
• 直接依托JPA分页API，自动处理分页与计数逻辑，避免手动捞取全量数据。

4. 优化JOIN与过滤条件 #

• 将LEFT JOIN business_supplier_mapping改为INNER JOIN：后续存在business_supplier_mapping.deleted is false的过滤条件，LEFT JOIN的无效数据会被过滤，改为INNER JOIN可减少关联数据量
• 修正GROUP BY逻辑：原SQL中GROUP BY items.supplier_id但SELECT了supplier.company_name，需保证items.supplier_id与supplier.id一一对应，或改为GROUP BY items.supplier_id, supplier.company_name避免数据库隐式排序开销

方法1：使用JpaSpecificationExecutor #

1. 让Repository继承JpaSpecificationExecutor（适配查询涉及的实体）
2. 编写Specification实现动态条件拼接：

public class TopSupplierSpecification implements Specification<Object[]> {
    private final BusinessReportRequestDTO request;
    private final Long buyerId;

    public TopSupplierSpecification(BusinessReportRequestDTO request, Long buyerId) {
        this.request = request;
        this.buyerId = buyerId;
    }

    @Override
    public Predicate toPredicate(Root<Object[]> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        List<Predicate> predicates = new ArrayList<>();

        // 基础过滤条件
        predicates.add(cb.or(cb.isNull(root.get("purchase_order").get("deleted")), cb.isFalse(root.get("purchase_order").get("deleted"))));
        predicates.add(cb.equal(root.get("purchase_order").get("purchase_order_status"), "PROCESSED"));
        predicates.add(cb.equal(root.get("buyer_business").get("buyer_id"), buyerId));
        predicates.add(cb.isFalse(root.get("business_supplier_mapping").get("deleted")));

        // 日期范围条件
        if (request.getFromDate() != null && request.getToDate() != null) {
            predicates.add(cb.between(root.get("purchase_order").get("received_date"), request.getFromDate(), request.getToDate()));
        }

        // 物料组ID列表条件
        if (request.getItemGroupIdList() != null && !request.getItemGroupIdList().isEmpty()) {
            predicates.add(root.get("items").get("item_group_id").in(request.getItemGroupIdList()));
        }

        // 其他条件（供应商ID、业务ID等）同理添加
        return cb.and(predicates.toArray(new Predicate[0]));
    }
}

3. Service层调用：

Page<Object[]> resultPage = yourRepository.findAll(new TopSupplierSpecification(request, buyerId), pageable);
List<TopSupplierItemsDTO> dtoList = resultPage.stream().map(this::setTopSupplierItemsDTO).collect(Collectors.toList());
return new PageImpl<>(dtoList, pageable, resultPage.getTotalElements());

方法2：使用QueryDSL（简洁的动态查询方案） #

1. 引入querydsl-jpa依赖，生成实体对应的Q类
2. 让Repository继承QuerydslPredicateExecutor
3. 编写动态查询逻辑：

BooleanBuilder builder = new BooleanBuilder();

// 基础条件
builder.and(QPurchaseOrder.purchaseOrder.deleted.isNull().or(QPurchaseOrder.purchaseOrder.deleted.isFalse()));
builder.and(QPurchaseOrder.purchaseOrder.purchaseOrder_status.eq("PROCESSED"));
builder.and(QBuyerBusiness.buyerBusiness.buyer_id.eq(buyerId));
builder.and(QBusinessSupplierMapping.businessSupplierMapping.deleted.isFalse());

// 日期范围
if (request.getFromDate() != null && request.getToDate() != null) {
    builder.and(QPurchaseOrder.purchaseOrder.received_date.between(request.getFromDate(), request.getToDate()));
}

// 物料组ID列表
if (request.getItemGroupIdList() != null && !request.getItemGroupIdList().isEmpty()) {
    builder.and(QItems.items.item_group_id.in(request.getItemGroupIdList()));
}

// 执行分页查询
Page<Object[]> resultPage = yourRepository.findAll(builder, pageable);
// 转换为DTO并返回

方法3：使用@Query结合SpEL表达式 #

适合简单动态场景，在Repository中定义带条件判断的查询：

@Query(value = "SELECT sum(amount), supplier.company_name " +
        "FROM purchase_order " +
        "LEFT JOIN process_order ON process_order.id=purchase_order.process_order_id " +
        "LEFT JOIN items ON items.id=process_order.items_id " +
        "LEFT JOIN item_group ON items.item_group_id=item_group.id " +
        "LEFT JOIN main_item_group ON item_group.main_item_group_id=main_item_group.id " +
        "LEFT JOIN supplier ON items.supplier_id=supplier.id " +
        "LEFT JOIN buyer_business ON process_order.buyer_business_id = buyer_business.id " +
        "INNER JOIN business_supplier_mapping on business_supplier_mapping.supplier_id=supplier.id and business_supplier_mapping.buyer_business_id=process_order.buyer_business_id " +
        "WHERE (purchase_order.deleted is null OR purchase_order.deleted is false) " +
        "AND purchase_order.purchase_order_status = 'PROCESSED' " +
        "AND buyer_business.buyer_id = :buyerId " +
        "AND business_supplier_mapping.deleted is false " +
        "AND (:#{#request.fromDate == null or #request.toDate == null} OR purchase_order.received_date BETWEEN :fromDate AND :toDate) " +
        "AND (:#{#request.itemGroupIdList == null or #request.itemGroupIdList.isEmpty()} OR items.item_group_id in (:itemGroupIds)) " +
        "GROUP BY items.supplier_id, supplier.company_name",
        countQuery = "SELECT count(DISTINCT items.supplier_id) " +
                "FROM purchase_order " +
                "LEFT JOIN process_order ON process_order.id=purchase_order.process_order_id " +
                "LEFT JOIN items ON items.id=process_order.items_id " +
                "INNER JOIN business_supplier_mapping on business_supplier_mapping.supplier_id=items.supplier_id and business_supplier_mapping.buyer_business_id=process_order.buyer_business_id " +
                "WHERE (purchase_order.deleted is null OR purchase_order.deleted is false) " +
                "AND purchase_order.purchase_order_status = 'PROCESSED' " +
                "AND buyer_business.buyer_id = :buyerId " +
                "AND business_supplier_mapping.deleted is false " +
                "AND (:#{#request.fromDate == null or #request.toDate == null} OR purchase_order.received_date BETWEEN :fromDate AND :toDate) " +
                "AND (:#{#request.itemGroupIdList == null or #request.itemGroupIdList.isEmpty()} OR items.item_group_id in (:itemGroupIds))",
        nativeQuery = true)
Page<Object[]> getTopSuppliers(@Param("request") BusinessReportRequestDTO request,
                               @Param("buyerId") Long buyerId,
                               @Param("fromDate") LocalDate fromDate,
                               @Param("toDate") LocalDate toDate,
                               @Param("itemGroupIds") List<Long> itemGroupIds,
                               Pageable pageable);

内容的提问来源于stack exchange，提问作者Najeeba Saidalavi