현재 프로젝트를 진행하던 중에 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를 사용한 검색조건 🔼
- keywordEqTitle : 스터디의 제목 검색
- keywordEqTag : 스터디의 태그 검색
- keywordEqZone : 스터디의 지역 검색
- 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 '!'
)
)
)
Live the moment for the moment.