java - 如何解决Spring Data JPA中的N+1问题？

Question

我使用 Spring Data JPA 作为持久层，并且面临 N+1 问题。我还使用规范 API，因为我发现很难解决 N+1 问题。请帮忙。

@Entity
public class PopulationHealth {

    @Id
    private int caseId;

    @OneToMany(mappedBy = "caseId", fetch = FetchType.LAZY)
    private List<CostSaving> costSavings;
}

public class CostSaving {

    @Id
    private int caseId;
}

@Transactional(readOnly = true)
public interface PopulationHealthRepository extends JpaRepository<PopulationHealth, Integer>, JpaSpecificationExecutor<PopulationHealth> {

    Page<PopulationHealth> findAll(Specification<PopulationHealth> spec, Pageable pageable);
}

在下面的类中，root.join() 方法，我稍后添加了它，这在查询中创建了左连接，但是，N+1 问题仍然发生。请引用下面的日志输出:

public class PopulationHealthSearchSpec implements Specification<PopulationHealth> {

    private List<PopulationHealthCriteriaDto> criteria;

    public PopulationHealthSearchSpec() {
        criteria = new ArrayList<>();
    }

    public void addCriteria(List<PopulationHealthCriteriaDto> criteria) {
        this.criteria.addAll(criteria);
    }

    @Override
    public Predicate toPredicate(Root<PopulationHealth> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        List<Predicate> predicates = new ArrayList<>();
        criteria.forEach(p -> {
            SearchOperation operation = p.getOperation();
            root.join("costSavings", JoinType.LEFT);
            switch (operation) {
                case GREATER_THAN:
                    predicates.add(cb.greaterThan(root.get(p.getKey().toString()), convertToDate(p.getValue())));
                    break;
                case GREATER_THAN_EQUAL:
                    predicates.add(cb.greaterThanOrEqualTo(root.get(p.getKey().toString()), convertToDate(p.getValue())));
                    break;
                case LESS_THAN:
                    predicates.add(cb.lessThan(root.get(p.getKey().toString()), convertToDate(p.getValue())));
                    break;
                case LESS_THAN_EQUAL:
                    predicates.add(cb.lessThanOrEqualTo(root.get(p.getKey().toString()), convertToDate(p.getValue())));
                    break;
                case EQUAL:
                    predicates.add(cb.equal(root.get(p.getKey().toString()), p.getValue()));
                    break;
                case NOT_EQUAL:
                    predicates.add(cb.notEqual(root.get(p.getKey().toString()), p.getValue()));
                    break;
                case IN:
                    predicates.add(getInPredicates(cb, root, p));
                    break;
                case NOT_IN:
                    predicates.add(getInPredicates(cb, root, p).not());
                    break;
            }
        });
        return cb.and(predicates.toArray(new Predicate[0]));
    }

使用 N+1 查询记录输出。第一个查询有 Left join 但仍然存在 N+1 问题。

2020-05-06 19:46:41,527 DEBUG org.hibernate.SQL : select population0_.CaseId as CaseId1_3_, population0_.CaseCreateDate as CaseCrea2_3_ from POPULATION_HEALTH_UNMASKED population0_ left outer join COST_SAVINGS costsaving1_ on population0_.CaseId=costsaving1_.CaseId where population0_.CaseId in (3098584 , 3098587 , 3098591) order by population0_.CaseCreateDate asc limit ?
2020-05-06 19:46:41,709 DEBUG org.hibernate.SQL : select costsaving0_.CaseId as CaseId1_1_0_, costsaving0_.CaseId as CaseId1_1_1_ from COST_SAVINGS costsaving0_ where costsaving0_.CaseId=?
2020-05-06 19:46:41,744 DEBUG org.hibernate.SQL : select costsaving0_.CaseId as CaseId1_1_0_, costsaving0_.CaseId as CaseId1_1_1_ from COST_SAVINGS costsaving0_ where costsaving0_.CaseId=?
2020-05-06 19:46:41,781 DEBUG org.hibernate.SQL : select costsaving0_.CaseId as CaseId1_1_0_, costsaving0_.CaseId as CaseId1_1_1_ from COST_SAVINGS costsaving0_ where costsaving0_.CaseId=?

在规范类中使用 fetch() 而不是 join() 后，我遇到以下问题:

2020-05-06 20:29:25,315 ERROR org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.cambia.mmt.cdqs.api.entity.PopulationHealth.costSavings,tableName=COST_SAVINGS,tableAlias=costsaving1_,origin=POPULATION_HEALTH_UNMASKED population0_,columns={population0_.CaseId ,className=com.cambia.mmt.cdqs.api.entity.CostSaving}}] [select count(generatedAlias0) from com.cambia.mmt.cdqs.api.entity.PopulationHealth as generatedAlias0 left join fetch generatedAlias0.costSavings as generatedAlias1 where generatedAlias0.caseCreateDate>:param0]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.cambia.mmt.cdqs.api.entity.PopulationHealth.costSavings,tableName=COST_SAVINGS,tableAlias=costsaving1_,origin=POPULATION_HEALTH_UNMASKED population0_,columns={population0_.CaseId ,className=com.cambia.mmt.cdqs.api.entity.CostSaving}}] [select count(generatedAlias0) from com.cambia.mmt.cdqs.api.entity.PopulationHealth as generatedAlias0 left join fetch generatedAlias0.costSavings as generatedAlias1 where generatedAlias0.caseCreateDate>:param0]] with root cause
org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.cambia.mmt.cdqs.api.entity.PopulationHealth.costSavings,tableName=COST_SAVINGS,tableAlias=costsaving1_,origin=POPULATION_HEALTH_UNMASKED population0_,columns={population0_.CaseId ,className=com.cambia.mmt.cdqs.api.entity.CostSaving}}]

Answer 1

我认为 Spring-Data 在这里不能做得更好，因为它尝试首先执行计数查询，以便提供 Page 对象中的总计数信息。您可以使用 Slice 来避免计数查询。

如果您想要更高级的东西，您可以查看 Blaze-Persistence integration with Spring-Data 。它将使用不同的分页机制，使其能够工作并且效率更高。使用实体 View 甚至会给您带来额外的性能提升。