代码之家  ›  专栏  ›  技术社区  ›  samshers

Spring Data Jpa/Hibernate:急切的加载正在触发n+1查询?

spring-data-jpa jpa hibernate spring java
  •  0
  • samshers  · 技术社区  · 3 年前

    这个问题是针对使用急加载时的n+1问题的。我看到的问题是,当子实体被定义为急切地加载时,n+1问题正在发生,这是意料之中的。

    实体关系如下所示。一个学生属于一个学院,一个学院可以有多个学生。因此,学生之间存在着多种关系-->大学,以及大学之间的一种单一关系-->大学生

    实体如下所示。 

    @Entity
public class College {

    @Id
    @GeneratedValue
    private int collegeId;

    private String collegeName;

    @OneToMany(targetEntity = Student.class, mappedBy = "college", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    //as you can see students is loaded eagerly.
    private List<Student> students;

    和 

    @Entity
public class Student {

    @Id
    @GeneratedValue
    private int studentId;

    private String studentName;

    @ManyToOne
    @JoinColumn(name = "collegeId_fk")
    private College college;

    现在当我在代码下面运行时, 

    collegeRepo.findAll().forEach( c -> System.err.println("college wit students: " + c.getStudents() ) );

    我看到触发了多个查询: 

    2022-02-21 07:12:32.242 TRACE 19824 --- [           main] o.s.t.i.TransactionInterceptor           : Getting transaction for [org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll]
Hibernate: 
    select
        college0_.college_id as college_1_1_,
        college0_.college_name as college_2_1_ 
    from
        college college0_
2022-02-21 07:12:32.497 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_1_1_] : [INTEGER]) - [1]
2022-02-21 07:12:32.500 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_2_1_] : [VARCHAR]) - [college1]
2022-02-21 07:12:32.501 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_1_1_] : [INTEGER]) - [2]
2022-02-21 07:12:32.501 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_2_1_] : [VARCHAR]) - [college2]
2022-02-21 07:12:32.501 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_1_1_] : [INTEGER]) - [3]
2022-02-21 07:12:32.501 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_2_1_] : [VARCHAR]) - [college3]
2022-02-21 07:12:32.501 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_1_1_] : [INTEGER]) - [4]
2022-02-21 07:12:32.502 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_2_1_] : [VARCHAR]) - [college4]
Hibernate: 
    select
        students0_.college_id_fk as college_3_12_0_,
        students0_.student_id as student_1_12_0_,
        students0_.student_id as student_1_12_1_,
        students0_.college_id_fk as college_3_12_1_,
        students0_.student_name as student_2_12_1_ 
    from
        student students0_ 
    where
        students0_.college_id_fk=?
2022-02-21 07:12:32.510 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [4]
Hibernate: 
    select
        students0_.college_id_fk as college_3_12_0_,
        students0_.student_id as student_1_12_0_,
        students0_.student_id as student_1_12_1_,
        students0_.college_id_fk as college_3_12_1_,
        students0_.student_name as student_2_12_1_ 
    from
        student students0_ 
    where
        students0_.college_id_fk=?
2022-02-21 07:12:32.744 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [3]
Hibernate: 
    select
        students0_.college_id_fk as college_3_12_0_,
        students0_.student_id as student_1_12_0_,
        students0_.student_id as student_1_12_1_,
        students0_.college_id_fk as college_3_12_1_,
        students0_.student_name as student_2_12_1_ 
    from
        student students0_ 
    where
        students0_.college_id_fk=?
2022-02-21 07:12:32.975 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [2]
2022-02-21 07:12:33.207 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_1_] : [INTEGER]) - [1]
2022-02-21 07:12:33.207 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_1_] : [INTEGER]) - [2]
2022-02-21 07:12:33.207 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_2_12_1_] : [VARCHAR]) - [student1]
2022-02-21 07:12:33.207 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_0_] : [INTEGER]) - [2]
2022-02-21 07:12:33.208 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_0_] : [INTEGER]) - [1]
2022-02-21 07:12:33.211 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_1_] : [INTEGER]) - [2]
2022-02-21 07:12:33.211 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_1_] : [INTEGER]) - [2]
2022-02-21 07:12:33.211 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_2_12_1_] : [VARCHAR]) - [student2]
2022-02-21 07:12:33.211 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_0_] : [INTEGER]) - [2]
2022-02-21 07:12:33.211 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_0_] : [INTEGER]) - [2]
2022-02-21 07:12:33.211 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_1_] : [INTEGER]) - [3]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_1_] : [INTEGER]) - [2]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_2_12_1_] : [VARCHAR]) - [student3]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_0_] : [INTEGER]) - [2]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_0_] : [INTEGER]) - [3]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_1_] : [INTEGER]) - [4]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_1_] : [INTEGER]) - [2]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_2_12_1_] : [VARCHAR]) - [student4]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_0_] : [INTEGER]) - [2]
2022-02-21 07:12:33.212 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_0_] : [INTEGER]) - [4]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_1_] : [INTEGER]) - [5]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_1_] : [INTEGER]) - [2]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_2_12_1_] : [VARCHAR]) - [student5]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_0_] : [INTEGER]) - [2]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_0_] : [INTEGER]) - [5]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_1_] : [INTEGER]) - [6]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_1_] : [INTEGER]) - [2]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_2_12_1_] : [VARCHAR]) - [student6]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([college_3_12_0_] : [INTEGER]) - [2]
2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_0_] : [INTEGER]) - [6]
Hibernate: 
    select
        students0_.college_id_fk as college_3_12_0_,
        students0_.student_id as student_1_12_0_,
        students0_.student_id as student_1_12_1_,
        students0_.college_id_fk as college_3_12_1_,
        students0_.student_name as student_2_12_1_ 
    from
        student students0_ 
    where
        students0_.college_id_fk=?
2022-02-21 07:12:33.214 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [1]
2022-02-21 07:12:33.449 TRACE 19824 --- [           main] o.s.t.i.TransactionInterceptor           : Completing transaction for [org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll]
college wit students: []
2022-02-21 07:12:33.450 TRACE 19824 --- [           main] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.demo.MyRunner.run]
2022-02-21 07:12:33.450 DEBUG 19824 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction commit
2022-02-21 07:12:33.450 DEBUG 19824 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Committing JPA transaction on EntityManager [SessionImpl(8781131<open>)]
college wit students: [Student [studentId=1, studentName=student1, college=College [collegeId=2, collegeName=college2]], Student [studentId=2, studentName=student2, college=College [collegeId=2, collegeName=college2]], Student [studentId=3, studentName=student3, college=College [collegeId=2, collegeName=college2]], Student [studentId=4, studentName=student4, college=College [collegeId=2, collegeName=college2]], Student [studentId=5, studentName=student5, college=College [collegeId=2, collegeName=college2]], Student [studentId=6, studentName=student6, college=College [collegeId=2, collegeName=college2]]]
college wit students: []
college wit students: []
2022-02-21 07:12:33.919 DEBUG 19824 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Closing JPA EntityManager [SessionImpl(8781131<open>)] after transaction

    我知道使用 JOIN FETCH 或 NamedEntityGraph 这是可以解决的。
    然而,我更想了解的是,如果急于加载(不仅仅是惰性加载)也会创建n+1查询。

    0 回复  |  直到 3 年前
        1
  •  0
  •   Ken Chan    3 年前

    即时加载只定义 什么时候 获取相关实体,但不获取 怎样 获取实体(即,它们是通过联接SQL获取的,还是通过逐个选择SQL获取的)。

    默认行为是,它将逐个加载需要select SQL抓取的实体。因此,它可能会导致N+1查询问题,这在您的案例中得到了证明。

    加载学院列表后,即时加载生效,通过select SQL逐个加载每个学院的学生。

    推荐文章
    Nathan  ·  如何从EclipseLink的“SingularAttributeImpl”中获得瞬态场?
    2 年前
    TheCrunchyPotato  ·  JPA查询找不到正确的构造函数
    2 年前
    Maiko Kingma  ·  hibernate何时加载映射关系
    7 年前
    OOvic  ·  使用JoinTable实现多个关系中的JPQL查询
    7 年前
    Jovan0042  ·  在H2中持久化多个实体引发异常
    7 年前
    Salih Erikci  ·  在Hibernate中使用序列时,是什么导致了唯一约束冲突?
    7 年前
    quantumbutterfly  ·  JPA EntityManager不再查找实体,即使恢复为以前工作的代码
    7 年前
    Jako  ·  JPA NamedQuery不在Glassfish4中(但在Glassfish3中可用)
    7 年前
    Daniel Haughton  ·  Hibernate Cascade:保存父实体而不保存子实体时删除子实体
    7 年前
    Nemeth Attila  ·  如何编写多对多关联表的查询
    7 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号