Querydsl 검색조건에서의 N+1문제 해결 로그

김건우·2023년 2월 8일
0

QueryDsl

목록 보기
8/8
post-thumbnail

현재 프로젝트를 진행하던 중 Querydsl로 검색을 하는 기능을 만들었는데, 검색에는 스터디의 제목, 스터디의 지역, 스터디 관련 태그로 검색을 진행 할 수 있다. 하지만 문제는 바로 검색을 하면 쿼리의 N+1문제가 발생하는 것이다.
이는 스터디(Study)의 연관관계에 태그(tags)와 지역(zones)가 연관되어있기 때문이다. N+1 문제를 해결하기 위해서는 기존에는 Fetch Join , @EntityGraphe를 사용할 수 있는데 Querydsl를 사용하면서 N+1문제를 해결한 방법을 소개해보자 한다.

N+1문제가 발생한 Querydsl

아래의 코드를 보면 BooleanExpression를 사용하여 조건에 해당 스터디의 제목, 스터디의 관심태그, 스터디의 지역으로 검색할 수 있다.

public class StudyRepositoryExtensionImpl extends QuerydslRepositorySupport implements StudyRepositoryExtension {

    // 상위클래스(QuerydslRepositorySupport)에 기본생성자가 없기 때문에 🔽 만들어주자
    public StudyRepositoryExtensionImpl() {
        super(Study.class);
    }

    @Override
    public List<Study> findByKeyword(String keyword) {

        JPQLQuery<Study> query = from(study).where(
                study.published.isTrue()
                        .and(keywordEqTitle(keyword))
                        .or(keywordEqTag(keyword))
                        .or(keywordEqZone(keyword))
        );
        return query.fetch();
    }
    
     private BooleanExpression keywordEqTitle(String keyword) {
        if (StringUtils.hasText(keyword)) {
            return study.title.containsIgnoreCase(keyword);
        }
        return null;
    }

    private BooleanExpression keywordEqTag(String keyword) {
        if (StringUtils.hasText(keyword)) {
            return study.tags.any().title.containsIgnoreCase(keyword);
        }
        return null;
    }

    private BooleanExpression keywordEqZone(String keyword) {
        if (StringUtils.hasText(keyword)) {
            return study.zones.any().localNameOfCity.containsIgnoreCase(keyword);
        }
        return null;
    }
}

BooleanExpression를 사용한 검색조건 🔼

  1. keywordEqTitle : 스터디의 제목 검색
  2. keywordEqTag : 스터디의 태그 검색
  3. keywordEqZone : 스터디의 지역 검색
  4. study.published.isTrue() : 오픈된 스터디

하지만, N+1 이슈가 발생하는 코드이다.

console를 확인해보면 아래와 같이 스터디의 갯수만큼 쿼리가 나가는 것을 확인할 수있고 N+1이슈가 발생하고있다. (📢이슈)각각의 스터디마다 쿼리가 3개가 나가고있다. 조회된 스터디의 갯수만큼 아래와 같은 쿼리가 나간다.

  select
        study0_.id as id1_7_,
        study0_.closed as closed2_7_,
        study0_.closed_date_time as closed_d3_7_,
        study0_.deleted as deleted4_7_,
        study0_.full_description as full_des5_7_,
        study0_.image as image6_7_,
        study0_.member_count as member_c7_7_,
        study0_.path as path8_7_,
        study0_.published as publishe9_7_,
        study0_.published_date_time as publish10_7_,
        study0_.recruiting as recruit11_7_,
        study0_.recruiting_updated_date_time as recruit12_7_,
        study0_.short_description as short_d13_7_,
        study0_.title as title14_7_,
        study0_.use_banner as use_ban15_7_ 
    from
        study study0_ 
    where
        (
            study0_.deleted = 0
        ) 
        and (
            study0_.published=? 
            and (
                lower(study0_.title) like ? escape '!'
            ) 
            or exists (
                select
                    1 
                from
                    study_tags tags1_,
                    tag tag2_ 
                where
                    study0_.id=tags1_.study_id 
                    and tags1_.tags_id=tag2_.id 
                    and (
                        tag2_.deleted = 0
                    ) 
                    and (
                        lower(tag2_.title) like ? escape '!'
                    )
            ) 
            or exists (
                select
                    1 
                from
                    study_zones zones3_,
                    zone zone4_ 
                where
                    study0_.id=zones3_.study_id 
                    and zones3_.zones_id=zone4_.id 
                    and (
                        lower(zone4_.local_name_of_city) like ? escape '!'
                    )
            )
        )
Hibernate: 
    select
        count(notificati0_.id) as col_0_0_ 
    from
        notification notificati0_ 
    where
        notificati0_.account_id=? 
        and notificati0_.checked=?
Hibernate: 
    select
        tags0_.study_id as study_id1_10_0_,
        tags0_.tags_id as tags_id2_10_0_,
        tag1_.id as id1_12_1_,
        tag1_.deleted as deleted2_12_1_,
        tag1_.title as title3_12_1_ 
    from
        study_tags tags0_ 
    inner join
        tag tag1_ 
            on tags0_.tags_id=tag1_.id 
            and (
                tag1_.deleted = 0
            ) 
    where
        tags0_.study_id=?
        
     

🚑 left(outer) join + fetchJoin + distinct로 해결.

핵심은 fetchJoin 메서드이다. 조인하는 대상 데이터를 즉시 로딩해서 가져온다. 다만 이 과정에서 중복 데이터가 발생할 수 있기 때문에 distinct 메서드를 추가한다. 또한 fetch join을 하려면 left join을 먼저 해줘야 하기 때문에 먼저 left join을 해주고 fetch join를 해야한다.

  • publicQ leftJoin(EntityPathtarget, Pathalias)
    : join을 걸고싶은 Entity의 연관관계 대상과 alias를 적어준다. (물론 QueryDsl의 Q객체들)

N+1 이슈를 해결한 Querydsl 코드 🔽

   @Override
    public List<Study> findByKeyword(String keyword) {

        JPQLQuery<Study> query = from(study).where(
                study.published.isTrue()
                        .and(keywordEqTitle(keyword))
                        .or(keywordEqTag(keyword))
                        .or(keywordEqZone(keyword)))
                .leftJoin(study.tags, QTag.tag).fetchJoin()
                .leftJoin(study.zones, QZone.zone).fetchJoin()
                .leftJoin(study.members, QAccount.account).fetchJoin()
                .distinct();
        
        return query.fetch();
    }

기존에 N+1개의 쿼리가 나가던 것이 한 개의 쿼리로 조회가 된다.(left(outer) join + fetchJoin + distinct 사용)

select
        distinct study0_.id as id1_7_0_,
        tag2_.id as id1_12_1_,
        zone4_.id as id1_13_2_,
        account6_.id as id1_0_3_,
        study0_.closed as closed2_7_0_,
        study0_.closed_date_time as closed_d3_7_0_,
        study0_.deleted as deleted4_7_0_,
        study0_.full_description as full_des5_7_0_,
        study0_.image as image6_7_0_,
        study0_.member_count as member_c7_7_0_,
        study0_.path as path8_7_0_,
        study0_.published as publishe9_7_0_,
        study0_.published_date_time as publish10_7_0_,
        study0_.recruiting as recruit11_7_0_,
        study0_.recruiting_updated_date_time as recruit12_7_0_,
        study0_.short_description as short_d13_7_0_,
        study0_.title as title14_7_0_,
        study0_.use_banner as use_ban15_7_0_,
        tag2_.deleted as deleted2_12_1_,
        tag2_.title as title3_12_1_,
        tags1_.study_id as study_id1_10_0__,
        tags1_.tags_id as tags_id2_10_0__,
        zone4_.city as city2_13_2_,
        zone4_.local_name_of_city as local_na3_13_2_,
        zone4_.province as province4_13_2_,
        zones3_.study_id as study_id1_11_1__,
        zones3_.zones_id as zones_id2_11_1__,
        account6_.bio as bio2_0_3_,
        account6_.deleted as deleted3_0_3_,
        account6_.email as email4_0_3_,
        account6_.email_check_token as email_ch5_0_3_,
        account6_.email_check_token_generated_at as email_ch6_0_3_,
        account6_.email_verified as email_ve7_0_3_,
        account6_.joined_at as joined_a8_0_3_,
        account6_.location as location9_0_3_,
        account6_.nickname as nicknam10_0_3_,
        account6_.occupation as occupat11_0_3_,
        account6_.password as passwor12_0_3_,
        account6_.profile_image as profile13_0_3_,
        account6_.study_created_by_email as study_c14_0_3_,
        account6_.study_created_by_web as study_c15_0_3_,
        account6_.study_enrollment_result_by_email as study_e16_0_3_,
        account6_.study_enrollment_result_by_web as study_e17_0_3_,
        account6_.study_updated_by_email as study_u18_0_3_,
        account6_.study_updated_by_web as study_u19_0_3_,
        account6_.url as url20_0_3_,
        members5_.study_id as study_id1_9_2__,
        members5_.members_id as members_2_9_2__ 
    from
        study study0_ 
    left outer join
        study_tags tags1_ 
            on study0_.id=tags1_.study_id 
    left outer join
        tag tag2_ 
            on tags1_.tags_id=tag2_.id 
            and (
                tag2_.deleted = 0
            ) 
    left outer join
        study_zones zones3_ 
            on study0_.id=zones3_.study_id 
    left outer join
        zone zone4_ 
            on zones3_.zones_id=zone4_.id 
    left outer join
        study_members members5_ 
            on study0_.id=members5_.study_id 
    left outer join
        account account6_ 
            on members5_.members_id=account6_.id 
            and (
                account6_.deleted = 0
            ) 
    where
        (
            study0_.deleted = 0
        ) 
        and (
            study0_.published=? 
            and (
                lower(study0_.title) like ? escape '!'
            ) 
            or exists (
                select
                    1 
                from
                    study_tags tags7_,
                    tag tag8_ 
                where
                    study0_.id=tags7_.study_id 
                    and tags7_.tags_id=tag8_.id 
                    and (
                        tag8_.deleted = 0
                    ) 
                    and (
                        lower(tag8_.title) like ? escape '!'
                    )
            ) 
            or exists (
                select
                    1 
                from
                    study_zones zones9_,
                    zone zone10_ 
                where
                    study0_.id=zones9_.study_id 
                    and zones9_.zones_id=zone10_.id 
                    and (
                        lower(zone10_.local_name_of_city) like ? escape '!'
                    )
            )
        )
profile
Live the moment for the moment.

0개의 댓글