N+1 Select 문제

N+1 문제란,

연관 관계에서 발생하는 이슈로 연관 관계가 설정된 엔티티를 조회할 경우에 조회된 데이터 갯수(n) 만큼 연관관계의 조회 쿼리가 추가로 발생하여 데이터를 읽어오게 된다. 이를 N+1 문제라고 한다.

 

N+1이 발생하는 이유는?

jpaRepository에 정의한 인터페이스 메서드를 실행하면 JPA는 메서드 이름을 분석해서 JPQL을 생성하여 실행하게 된다. JPQL은 SQL을 추상화한 객체지향 쿼리 언어로서 특정 SQL에 종속되지 않고 엔티티 객체와 필드 이름을 가지고 쿼리를 한다. 그렇기 때문에 JPQL은 findAll()이란 메소드를 수행하였을 때 해당 엔티티를 조회하는 select * from Owner 쿼리만 실행하게 되는것이다. JPQL 입장에서는 연관관계 데이터를 무시하고 해당 엔티티 기준으로 쿼리를 조회하기 때문이다. 그렇기 때문에 연관된 엔티티 데이터가 필요한 경우, FetchType으로 지정한 시점에 조회를 별도로 호출하게 된다.

 

해결 방안

예시) 스터디 그룹을 조회할 때 스터디 그룹의 1:다 연관관계인 스킬, 지역 정보를 함께 조회하는 경우

기존 코드

 select
   	studygroup0_.study_group_id as study_gr1_3_,
     	studygroup0_.closed as closed2_3_,
        studygroup0_.contact_type as contact_3_3_,
        studygroup0_.content as content4_3_,
        studygroup0_.create_date as create_d5_3_,
        studygroup0_.created_by as created_6_3_,
        studygroup0_.duration as duration7_3_,
        studygroup0_.location_id as locatio15_3_,
        studygroup0_.meeting_type as meeting_8_3_,
        studygroup0_.number_of_members as number_o9_3_,
        studygroup0_.remaining_seats as remaini10_3_,
        studygroup0_.study_start_date as study_s11_3_,
        studygroup0_.study_type as study_t12_3_,
        studygroup0_.title as title13_3_,
        studygroup0_.update_date as update_14_3_ 
    from
        study_group studygroup0_ cross 
    join
        location location3_ 
    where
        studygroup0_.location_id=location3_.location_id 
        and (
            studygroup0_.closed=? 
            and (
                lower(studygroup0_.title) like ? escape '!'
            ) 
            or exists (
                select
                    1 
                from
                    study_group_skills skills1_,
                    skill skill2_ 
                where
                    studygroup0_.study_group_id=skills1_.study_group_study_group_id 
                    and skills1_.skills_skill_id=skill2_.skill_id 
                    and (
                        lower(skill2_.title) like ? escape '!'
                    )
            ) 
            or lower(location3_.local_name_of_city) like ? escape '!'
        )

Hibernate: 
    select
        skills0_.study_group_study_group_id as study_gr1_4_0_,
        skills0_.skills_skill_id as skills_s2_4_0_,
        skill1_.skill_id as skill_id1_2_1_,
        skill1_.title as title2_2_1_ 
    from
        study_group_skills skills0_ 
    inner join
        skill skill1_ 
            on skills0_.skills_skill_id=skill1_.skill_id 
    where
        skills0_.study_group_study_group_id=?
        
Hibernate: 
    select
        location0_.location_id as location1_1_0_,
        location0_.city as city2_1_0_,
        location0_.local_name_of_city as local_na3_1_0_,
        location0_.province as province4_1_0_ 
    from
        location location0_ 
    where
        location0_.location_id=?

skill과 location의 개수만큼 쿼리가 실행된다.

 

left(outer) join 적용

   select
        studygroup0_.study_group_id as study_gr1_3_,
        studygroup0_.closed as closed2_3_,
        studygroup0_.contact_type as contact_3_3_,
        studygroup0_.content as content4_3_,
        studygroup0_.create_date as create_d5_3_,
        studygroup0_.created_by as created_6_3_,
        studygroup0_.duration as duration7_3_,
        studygroup0_.location_id as locatio15_3_,
        studygroup0_.meeting_type as meeting_8_3_,
        studygroup0_.number_of_members as number_o9_3_,
        studygroup0_.remaining_seats as remaini10_3_,
        studygroup0_.study_start_date as study_s11_3_,
        studygroup0_.study_type as study_t12_3_,
        studygroup0_.title as title13_3_,
        studygroup0_.update_date as update_14_3_ 
    from
        study_group studygroup0_ 
    left outer join
        study_group_skills skills1_ 
            on studygroup0_.study_group_id=skills1_.study_group_study_group_id 
    left outer join
        skill skill2_ 
            on skills1_.skills_skill_id=skill2_.skill_id cross 
    join
        location location5_ 
    where
        studygroup0_.location_id=location5_.location_id 
        and (
            studygroup0_.closed=? 
            and (
                lower(studygroup0_.title) like ? escape '!'
            ) 
            or exists (
                select
                    1 
                from
                    study_group_skills skills3_,
                    skill skill4_ 
                where
                    studygroup0_.study_group_id=skills3_.study_group_study_group_id 
                    and skills3_.skills_skill_id=skill4_.skill_id 
                    and (
                        lower(skill4_.title) like ? escape '!'
                    )
            ) 
            or lower(location5_.local_name_of_city) like ? escape '!'
        )
Hibernate: 
    select
        skills0_.study_group_study_group_id as study_gr1_4_0_,
        skills0_.skills_skill_id as skills_s2_4_0_,
        skill1_.skill_id as skill_id1_2_1_,
        skill1_.title as title2_2_1_ 
    from
        study_group_skills skills0_ 
    inner join
        skill skill1_ 
            on skills0_.skills_skill_id=skill1_.skill_id 
    where
        skills0_.study_group_study_group_id=?
Hibernate: 
    select
        location0_.location_id as location1_1_0_,
        location0_.city as city2_1_0_,
        location0_.local_name_of_city as local_na3_1_0_,
        location0_.province as province4_1_0_ 
    from
        location location0_ 
    where
        location0_.location_id=?

left(outer) join을 적용하여도, 이전과 동일하게 쿼리가 실행된다.

left join은 오른쪽 테이블에 데이터가 없더라도 왼쪽 테이블의 데이터를 모두 보여준다.

즉, 동일한 동일한 스터디 그룹에 대한 skill, location의 갯수만큼 여러 번 조회된다.

 

Fetch Join 적용

Hibernate: 
    select
        studygroup0_.study_group_id as study_gr1_3_0_,
        skill2_.skill_id as skill_id1_2_1_,
        studygroup0_.closed as closed2_3_0_,
        studygroup0_.contact_type as contact_3_3_0_,
        studygroup0_.content as content4_3_0_,
        studygroup0_.create_date as create_d5_3_0_,
        studygroup0_.created_by as created_6_3_0_,
        studygroup0_.duration as duration7_3_0_,
        studygroup0_.location_id as locatio15_3_0_,
        studygroup0_.meeting_type as meeting_8_3_0_,
        studygroup0_.number_of_members as number_o9_3_0_,
        studygroup0_.remaining_seats as remaini10_3_0_,
        studygroup0_.study_start_date as study_s11_3_0_,
        studygroup0_.study_type as study_t12_3_0_,
        studygroup0_.title as title13_3_0_,
        studygroup0_.update_date as update_14_3_0_,
        skill2_.title as title2_2_1_,
        skills1_.study_group_study_group_id as study_gr1_4_0__,
        skills1_.skills_skill_id as skills_s2_4_0__ 
    from
        study_group studygroup0_ 
    left outer join
        study_group_skills skills1_ 
            on studygroup0_.study_group_id=skills1_.study_group_study_group_id 
    left outer join
        skill skill2_ 
            on skills1_.skills_skill_id=skill2_.skill_id cross 
    join
        location location5_ 
    where
        studygroup0_.location_id=location5_.location_id 
        and (
            studygroup0_.closed=? 
            and (
                lower(studygroup0_.title) like ? escape '!'
            ) 
            or exists (
                select
                    1 
                from
                    study_group_skills skills3_,
                    skill skill4_ 
                where
                    studygroup0_.study_group_id=skills3_.study_group_study_group_id 
                    and skills3_.skills_skill_id=skill4_.skill_id 
                    and (
                        lower(skill4_.title) like ? escape '!'
                    )
            ) 
            or lower(location5_.local_name_of_city) like ? escape '!'
        )

Fetch Join을 적용하여 스터디 그룹 정보를 가져올 때 skill 정보도 함께 가져오게 된다.

 

 

Fetch Join 사용 시주의할 점

Fetch Join은 JPQL을 사용하여 JOIN문을 호출한다. 또한, 카테시안 곱(Cartesian Product)이 발생하여 Study Group의 수만큼 Skill의 중복 데이터가 존재할 수 있다. 그러므로 중복된 데이터가 컬렉션에 존재하지 않도록 주의해야 한다.

 

distinct 적용

Hibernate: 
    select
        distinct studygroup0_.study_group_id as study_gr1_3_0_,
        skill2_.skill_id as skill_id1_2_1_,
        location3_.location_id as location1_1_2_,
        studygroup0_.closed as closed2_3_0_,
        studygroup0_.contact_type as contact_3_3_0_,
        studygroup0_.content as content4_3_0_,
        studygroup0_.create_date as create_d5_3_0_,
        studygroup0_.created_by as created_6_3_0_,
        studygroup0_.duration as duration7_3_0_,
        studygroup0_.location_id as locatio15_3_0_,
        studygroup0_.meeting_type as meeting_8_3_0_,
        studygroup0_.number_of_members as number_o9_3_0_,
        studygroup0_.remaining_seats as remaini10_3_0_,
        studygroup0_.study_start_date as study_s11_3_0_,
        studygroup0_.study_type as study_t12_3_0_,
        studygroup0_.title as title13_3_0_,
        studygroup0_.update_date as update_14_3_0_,
        skill2_.title as title2_2_1_,
        skills1_.study_group_study_group_id as study_gr1_4_0__,
        skills1_.skills_skill_id as skills_s2_4_0__,
        location3_.city as city2_1_2_,
        location3_.local_name_of_city as local_na3_1_2_,
        location3_.province as province4_1_2_ 
    from
        study_group studygroup0_ 
    left outer join
        study_group_skills skills1_ 
            on studygroup0_.study_group_id=skills1_.study_group_study_group_id 
    left outer join
        skill skill2_ 
            on skills1_.skills_skill_id=skill2_.skill_id 
    left outer join
        location location3_ 
            on studygroup0_.location_id=location3_.location_id 
    where
        studygroup0_.closed=? 
        and (
            lower(studygroup0_.title) like ? escape '!'
        ) 
        or exists (
            select
                1 
            from
                study_group_skills skills4_,
                skill skill5_ 
            where
                studygroup0_.study_group_id=skills4_.study_group_study_group_id 
                and skills4_.skills_skill_id=skill5_.skill_id 
                and (
                    lower(skill5_.title) like ? escape '!'
                )
        ) 
        or lower(location3_.local_name_of_city) like ? escape '!'

스터디 그룹을 조회할 때, skill과 location 정보를 모두 가져오게 되고, 중복되는 것을 없애주었다.

 

 

참고

https://www.inflearn.com/course/%EC%8A%A4%ED%94%84%EB%A7%81-JPA-%EC%9B%B9%EC%95%B1/dashboard

https://incheol-jung.gitbook.io/docs/q-and-a/spring/n+1