N+1 | Notion

실험 배경

<aside> 🚀

Member 수 : 3

Member 엔티티에 Team과 Major를 필드로 가지고 있음 ( ManyToOne 매핑 )

적은 Member 수 안에서 가장 극단적인 쿼리 수 차이를 유도하고자 모든 Team과 Major는 모두 고유하도록 함

</aside>

LAZY + findAll() (no access)

=============== findAll (연관 접근 X) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0

Member만 가져오고 Team과 Major는 프록시 상태 그대로 둠
추가적인 연관 엔티티 접근이 없기 때문에 추가 SQL이 없음
그래서 N + 1 문제 발생 안함

LAZY + jpql (no access)

SELECT m FROM Member m

=============== JPQL (연관 접근 X) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0

findAll() 상황과 동일
Member만 조회하기 때문에 N + 1 발생하지 않음

LAZY + fetch (no access)

SELECT m FROM Member m JOIN FETCH m.team JOIN FETCH m.major

=============== Fetch Join (연관 접근 X) ===============
Hibernate: 
    select
        m1_0.id,
        m2_0.id,
        m2_0.name,
        m1_0.name,
        t1_0.id,
        t1_0.name 
    from
        member m1_0 
    join
        team t1_0 
            on t1_0.id=m1_0.team_id 
    join
        major m2_0 
            on m2_0.id=m1_0.major_id

이후 getter로 인해 Team과 Major를 호출할지 하지 않을지 고려하지 않고 일단 관련 테이블을
모두 조인함
N + 1 발생하지 않음

LAZY + findAll() (with access)

=============== findAll (연관 접근 O) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?

최초에 Member를 가져오고 Team과 Major를 프록시로 둠
이후 DTO 변환 과정에서 getTeam().getName(), getMajor().getName() 호출
N + 1 발생 (1 + 3 + 3 = 7번)

LAZY + jpql (with access)

SELECT m FROM Member m

=============== JPQL (연관 접근 O) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?

findAll()과 접근 동일
Member만 가져왔으나 이후 Team과 Major의 getter에 의해 실제 엔티티 조회하게 됨
N + 1 발생

LAZY + fetch (with access)

SELECT m FROM Member m JOIN FETCH m.team JOIN FETCH m.major

=============== Fetch Join (연관 접근 O) ===============
Hibernate: 
    select
        m1_0.id,
        m2_0.id,
        m2_0.name,
        m1_0.name,
        t1_0.id,
        t1_0.name 
    from
        member m1_0 
    join
        team t1_0 
            on t1_0.id=m1_0.team_id 
    join
        major m2_0 
            on m2_0.id=m1_0.major_id

no access와 같은 이유로 N + 1 발생하지 않음
최초에 Team과 Major를 프록시가 아닌 실제 엔티티로 영속성 컨텍스트에 등록함

EAGER + findAll() (no access)

=============== findAll (연관 접근 X) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?

EAGER는 항상 즉시 로딩함
Member만 가져와도 연관 엔티티 Team과 Major를 추가 조회함
N + 1 발생

EAGER + jpql (no access)

SELECT m FROM Member m

=============== JPQL (연관 접근 X) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?

findAll과 똑같이 동작
N + 1 발생

EAGER + fetch (no access)

SELECT m FROM Member m JOIN FETCH m.team JOIN FETCH m.major

=============== Fetch Join (연관 접근 X) ===============
Hibernate: 
    select
        m1_0.id,
        m2_0.id,
        m2_0.name,
        m1_0.name,
        t1_0.id,
        t1_0.name 
    from
        member m1_0 
    join
        team t1_0 
            on t1_0.id=m1_0.team_id 
    join
        major m2_0 
            on m2_0.id=m1_0.major_id

EAGER/LAZY를 구분하지 않고 일단 관련 엔티티를 모두 조인
N + 1 발생하지 않음

EAGER + findAll() (with access)

=============== findAll (연관 접근 O) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?

no access와 같은 이유로 N + 1 발생

EAGER + jpql (with access)

SELECT m FROM Member m

=============== JPQL (연관 접근 O) ===============
Hibernate: 
    select
        m1_0.id,
        m1_0.major_id,
        m1_0.name,
        m1_0.team_id 
    from
        member m1_0
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?
Hibernate: 
    select
        m1_0.id,
        m1_0.name 
    from
        major m1_0 
    where
        m1_0.id=?
Hibernate: 
    select
        t1_0.id,
        t1_0.name 
    from
        team t1_0 
    where
        t1_0.id=?

findAll과 똑같이 동작
N + 1 발생

EAGER + fetch (with access)

SELECT m FROM Member m JOIN FETCH m.team JOIN FETCH m.major

=============== Fetch Join (연관 접근 O) ===============
Hibernate: 
    select
        m1_0.id,
        m2_0.id,
        m2_0.name,
        m1_0.name,
        t1_0.id,
        t1_0.name 
    from
        member m1_0 
    join
        team t1_0 
            on t1_0.id=m1_0.team_id 
    join
        major m2_0 
            on m2_0.id=m1_0.major_id

no access와 같은 이유로 N + 1 발생하지 않음

실험 결과 요약

조회 방법	Fetch 전략	연관 접근 여부	실행 쿼리 수
findAll()	LAZY	❌	1
findAll()	LAZY	⭕	1 + N(3) + N(3) = 7
findAll()	EAGER	❌	1 + N(3) + N(3) = 7
findAll()	EAGER	⭕	1 + N(3) + N(3) = 7
JPQL (SELECT m)	LAZY	❌	1
JPQL (SELECT m)	LAZY	⭕	1 + N(3) + N(3) = 7
JPQL (SELECT m)	EAGER	❌	1 + N(3) + N(3) = 7
JPQL (SELECT m)	EAGER	⭕	1 + N(3) + N(3) = 7
JPQL + Fetch Join	LAZY	❌	1
JPQL + Fetch Join	LAZY	⭕	1
JPQL + Fetch Join	EAGER	❌	1
JPQL + Fetch Join	EAGER	⭕	1