기존 상황
먼저 N+1 문제가 발생하는 기능 중 하나는
룸메이트 매칭 목록 조회 기능 중에 '로그인한 유저가 좋아요 한 매칭정보 목록을 조회하는 기능'입니다.
기존의 코드는 아래와 같습니다.
더보기
MatchingMateService.java
@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
public class MatchingMateService implements MatchingMateUseCase {
private final LoadMemberPort loadMemberPort;
private final LoadMatchingInfoPort loadMatchingInfoPort;
private final SaveMatchingMatePort saveMatchingMatePort;
@Override
public List<MatchingMateResponse> findFavoriteMates(final AuthResponse auth) {
Member member = loadMemberPort.loadMember(auth.getId());
return convertMateResponses(member.getFavoriteMates());
}
private List<MatchingMateResponse> convertMateResponses(final List<MatchingMate> mates) {
List<MatchingMateResponse> responses = mates.stream()
.map(MatchingMate::getMatchingInfoForTarget)
.map(MatchingMateResponse::from)
.toList();
return responses;
}
}
Member.java
@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Member {
//...
@OneToMany(mappedBy = "owner", fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST, CascadeType.REMOVE},
orphanRemoval = true)
private List<MatchingMate> matchingMates = new ArrayList<>();
//...
public List<MatchingMate> getFavoriteMates() {
List<MatchingMate> results = this.matchingMates.stream()
.filter(MatchingMate::isPublic)
.filter(MatchingMate::isFavorite)
.collect(Collectors.toUnmodifiableList());
return results;
}
}
위의 코드를 바탕으로 더미 데이터를 넣어서 실행시켰을 때 나가는 쿼리는 아래와 같습니다.
* 더미데이터 설명:
로그인한 유저 PK: 1
유저가 좋아요 한 메이트 식별자: 2~501 (500개)
2024-04-03 18:11:33.764 INFO 8083 --- [nio-8080-exec-3] i.i.c.performance.QueryCountInterceptor :
===========RESULT==========
URL: GET /api/v1/matching/mates/favorite
TIME: 371
COUNT : 504
// 로그인한 회원 조회 : 쿼리 한 번 발생
select
member0_.member_id as member_i1_5_,
member0_.email as email2_5_,
member0_.matching_info_id as matching9_5_,
member0_.member_status as member_s3_5_,
member0_.nickname_updated_at as nickname4_5_,
member0_.nickname as nickname5_5_,
member0_.password as password6_5_,
member0_.profile_photo_url as profile_7_5_,
member0_.role_type as role_typ8_5_,
member0_.team_id as team_id10_5_
from
member member0_
where
member0_.member_id=1 and member0_.member_status='ACTIVE'
// 로그인 유저가 지정한 MatchingMate 조회 (결과 500개) : 쿼리 한 번 발생
select
matchingma0_.member_id as member_i4_4_1_,
matchingma0_.matching_mate_id as matching1_4_1_,
matchingma0_.matching_mate_id as matching1_4_0_,
matchingma0_.matching_info_id as matching3_4_0_,
matchingma0_.member_id as member_i4_4_0_,
matchingma0_.preference_type as preferen2_4_0_
from
matching_mate matchingma0_
where
matchingma0_.member_id=1
// 위의 쿼리 결과인 matching_info_id가 2~501인 matching_info 500개를 한번에 조회 : 쿼리 1번 발생
select
matchingin0_.matching_info_id as matching1_3_0_,
matchingin0_.dorm_category as dorm_cat2_3_0_,
matchingin0_.gender as gender3_3_0_,
matchingin0_.join_period as join_per4_3_0_,
matchingin0_.is_public as is_publi5_3_0_,
matchingin0_.age as age6_3_0_,
matchingin0_.is_allowed_food as is_allow7_3_0_,
matchingin0_.is_grinding as is_grind8_3_0_,
matchingin0_.is_smoking as is_smoki9_3_0_,
matchingin0_.is_snoring as is_snor10_3_0_,
matchingin0_.is_wear_earphones as is_wear11_3_0_,
matchingin0_.shared_url as shared_12_3_0_,
matchingin0_.clean_up_status as clean_u13_3_0_,
matchingin0_.mbti as mbti14_3_0_,
matchingin0_.shower_time as shower_15_3_0_,
matchingin0_.wake_up_time as wake_up16_3_0_,
matchingin0_.wish_text as wish_te17_3_0_
from
matching_info matchingin0_
where
matchingin0_.matching_info_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
// 위의 결과 N개를 바탕으로 OneToOne 관계인 Member 조회에 대한 지연 로딩 발생 N번 : 쿼리 500번 발생
// matching_info에 member_id가 없기 때문에, member_id를 찾기 위해 member 테이블 뒤진다.
select
member0_.member_id as member_i1_5_0_,
member0_.email as email2_5_0_,
member0_.matching_info_id as matching9_5_0_,
member0_.member_status as member_s3_5_0_,
member0_.nickname_updated_at as nickname4_5_0_,
member0_.nickname as nickname5_5_0_,
member0_.password as password6_5_0_,
member0_.profile_photo_url as profile_7_5_0_,
member0_.role_type as role_typ8_5_0_,
member0_.team_id as team_id10_5_0_
from
member member0_
where
member0_.matching_info_id=?
동일한 select 쿼리문이 2개 이상 발생합니다.
N+1 문제 혹은 로직 개선이 필요해 보입니다.
select
member0_.member_id as member_i1_5_0_,
member0_.email as email2_5_0_,
member0_.matching_info_id as matching9_5_0_,
member0_.member_status as member_s3_5_0_,
member0_.nickname_updated_at as nickname4_5_0_,
member0_.nickname as nickname5_5_0_,
member0_.password as password6_5_0_,
member0_.profile_photo_url as profile_7_5_0_,
member0_.role_type as role_typ8_5_0_,
member0_.team_id as team_id10_5_0_
from
member member0_
where
member0_.matching_info_id=?
실제로 마지막에 나간 쿼리인 위의 쿼리는 500번(N) 발생했습니다.
쿼리가 N번 발생한 이유는 다음과 같습니다.
최초로 MatchingInfo를 조회해 올 때 Member의 정보는 Proxy 객체로 가지고 있습니다. (실제론 없다는 뜻)
그렇기 때문에 실제로 getMember().getId()를 통해 MatchingInfo의 주인(Member)을 조회하려고 할 때 SQL을 수행합니다.
만약 이전에 가져온 해당 PK의 Member 정보가 있다면 1차 캐시인 영속성 컨텍스트에 올라가 있기 때문에 더 조회할 필요가 없지만,
MatchingInfo를 N개 조회하게 되었을 때 최대 N + 1번의 Member 조회 쿼리가 수행될 수 있습니다.
문제 정의
- 위의 N개 발생한 쿼리를 단일 쿼리로 나가도록 개선한다.
- 매칭 API 내 조회 기능에 페이징을 적용한다.
N+1 문제 해결 : MatchingInfo 조회 (1) -> 지연 로딩 Member (N)
개선한 코드
더보기
MatchingMateService.java
@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
public class MatchingMateService implements MatchingMateUseCase {
private final LoadMemberPort loadMemberPort;
private final LoadMatchingInfoPort loadMatchingInfoPort;
private final SaveMatchingMatePort saveMatchingMatePort;
@Override
public List<MatchingMateResponse> findFavoriteMates(final AuthResponse auth) {
Member member = loadMemberPort.loadMember(auth.getId());
return loadMatchingInfoPort.loadByMatePreference(member, MatePreferenceType.FAVORITE);
}
}
MatchingInfoRepositoryCustomImpl.java
@RequiredArgsConstructor
public class MatchingInfoRepositoryCustomImpl implements MatchingInfoRepositoryCustom {
private final JPAQueryFactory queryFactory;
@Override
public List<MatchingMateResponse> findByMatePreference(Member loginMember, MatePreferenceType preferenceType) {
return queryFactory
.select(Projections.constructor(MatchingMateResponse.class,
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member).fetchJoin()
.leftJoin(matchingMate).on(matchingInfo.eq(matchingMate.matchingInfoForTarget))
.where(matchingMate.preferenceType.eq(preferenceType),
matchingMate.owner.eq(loginMember))
.fetch();
}
}
위의 코드에서는
JPA에서 성능 최적화를 위해 제공하는 기능인 JPQL의 페치 조인을 사용하여 개선하였습니다.
페치 조인은 연관된 엔티티나 컬렉션을 SQL 한 번에 함께 조회하는 기능으로 '한방 쿼리'라고도 불립니다.
페치 조인 :: = [LEFT [OUTER] | INNER ] JOIN FETCH 조인 경로
페치 조인으로 조회 당시에 Proxy 객체가 아닌 실제 엔티티가 담기게 되기 때문에, 지연 로딩 없이 바로 사용이 가능합니다.
위의 코드 실행 시, 나가는 쿼리는 아래와 같습니다.
2024-04-03 21:07:19.203 INFO 15317 --- [nio-8080-exec-4] i.i.c.performance.QueryCountInterceptor :
===========RESULT==========
URL: GET /api/v1/matching/mates/favorite
TIME: 58
COUNT : 2
// 로그인한 유저 조회
select
member0_.member_id as member_i1_5_,
member0_.email as email2_5_,
member0_.matching_info_id as matching9_5_,
member0_.member_status as member_s3_5_,
member0_.nickname_updated_at as nickname4_5_,
member0_.nickname as nickname5_5_,
member0_.password as password6_5_,
member0_.profile_photo_url as profile_7_5_,
member0_.role_type as role_typ8_5_,
member0_.team_id as team_id10_5_
from
member member0_
where
member0_.member_id=1
and member0_.member_status='ACTIVE'
// 단일 쿼리로 MatchingInfo 목록과 그에 연관된 Member 한 번에 조회
select
matchingin0_.matching_info_id as col_0_0_,
member1_.member_id as col_1_0_,
member1_.member_id as member_i1_5_1_,
matchingin0_.matching_info_id as matching1_3_0_,
matchingin0_.dorm_category as dorm_cat2_3_0_,
matchingin0_.gender as gender3_3_0_,
matchingin0_.join_period as join_per4_3_0_,
matchingin0_.is_public as is_publi5_3_0_,
matchingin0_.age as age6_3_0_,
matchingin0_.is_allowed_food as is_allow7_3_0_,
matchingin0_.is_grinding as is_grind8_3_0_,
matchingin0_.is_smoking as is_smoki9_3_0_,
matchingin0_.is_snoring as is_snor10_3_0_,
matchingin0_.is_wear_earphones as is_wear11_3_0_,
matchingin0_.shared_url as shared_12_3_0_,
matchingin0_.clean_up_status as clean_u13_3_0_,
matchingin0_.mbti as mbti14_3_0_,
matchingin0_.shower_time as shower_15_3_0_,
matchingin0_.wake_up_time as wake_up16_3_0_,
matchingin0_.wish_text as wish_te17_3_0_,
member1_.email as email2_5_1_,
member1_.matching_info_id as matching9_5_1_,
member1_.member_status as member_s3_5_1_,
member1_.nickname_updated_at as nickname4_5_1_,
member1_.nickname as nickname5_5_1_,
member1_.password as password6_5_1_,
member1_.profile_photo_url as profile_7_5_1_,
member1_.role_type as role_typ8_5_1_,
member1_.team_id as team_id10_5_1_
from
matching_info matchingin0_
left outer join
member member1_
on matchingin0_.matching_info_id=member1_.matching_info_id
left outer join
matching_mate matchingma2_
on (
matchingin0_.matching_info_id=matchingma2_.matching_info_id
)
where
matchingma2_.preference_type='FAVORITE'
and matchingma2_.member_id=1
페치 조인을 적용하고 프로젝션 대상을 dto로 설정하여 matchingInfo와 필요한 member 필드를 반환하는 방식으로 수정하였더니,
실제로 나가는 쿼리가 한 개로 감소했습니다. (*첫 번째 쿼리는 현재 내용과는 관련 없는 쿼리입니다.)
페이징 처리
1) 기존 코드: 페이징 미적용
코드는 아래와 같습니다.
더보기
@RequiredArgsConstructor
public class MatchingInfoRepositoryCustomImpl implements MatchingInfoRepositoryCustom {
private final JPAQueryFactory queryFactory;
@Override
public List<MatchingMateResponse> findByMatePreference(final Member loginMember,
final MatePreferenceType preferenceType) {
return queryFactory
.select(Projections.constructor(MatchingMateResponse.class,
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member).fetchJoin()
.leftJoin(matchingMate).on(matchingInfo.eq(matchingMate.matchingInfoForTarget))
.where(matchingMate.preferenceType.eq(preferenceType),
matchingMate.owner.eq(loginMember))
.fetch();
}
@Override
public List<MatchingMateResponse> findByBasicConditions(final Member loginMember) {
return queryFactory
.select(Projections.constructor(MatchingMateResponse.class,
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member, member).fetchJoin()
.where(basicCond(loginMember.getId(), loginMember.getMatchingInfo()))
.fetch();
}
@Override
public List<MatchingMateResponse> findBySpecialConditions(final Member loginMember,
final MatchingMateFilterRequest request) {
return queryFactory
.select(Projections.constructor(MatchingMateResponse.class,
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member, member).fetchJoin()
.where(specialCond(loginMember.getId(), loginMember.getMatchingInfo(), request))
.fetch();
}
private BooleanExpression basicCond(Long memberId, MatchingInfo matchingInfo) {
return matchingPublic()
.and(memberNotEq(memberId))
.and(dormEq(matchingInfo.getDormInfo().getDormCategory()))
.and(joinPeriodEq(matchingInfo.getDormInfo().getJoinPeriod()))
.and(genderEq(matchingInfo.getDormInfo().getGender()));
}
private BooleanExpression specialCond(Long memberId, MatchingInfo matchingInfo, MatchingMateFilterRequest request) {
return basicCond(memberId, matchingInfo)
.and(isSnoringEq(request.isSnoring()))
.and(isSmokingEq(request.isSmoking()))
.and(isGrindingEq(request.isGrinding()))
.and(isWearEarphonesEq(request.isWearEarphones()))
.and(isAllowedFoodEq(request.isAllowedFood()))
.and(ageGoe(request.minAge()))
.and(ageLoe(request.maxAge()));
}
// ...
}
이번에 예시로 든 기능은,
로그인한 유저가 '필터링한 정보를 바탕으로 룸메이트 MatchingInfo 목록을 조회하는 기능' 입니다.
현재 매핑 관계는 다음과 같습니다.
- Member(owner)와 MatchingMate 관계는 OneToMany 관계
- MatchingMate와 타겟이 되는 MatchingInfo 관계는 ManyToOne 관계
- 타겟이 되는 MatchingInfo와 MatchingInfo의 주인인 Member는 OneToOne 관계
다음은 위의 코드를 실행했을 때 발생되는 쿼리입니다.
더보기
===========RESULT==========
URL: POST /api/v1/matching/mates/filter
TIME: 578
COUNT : 3
//1. 로그인한 유저 조회
select
member0_.member_id as member_i1_5_,
member0_.email as email2_5_,
member0_.matching_info_id as matching9_5_,
member0_.member_status as member_s3_5_,
member0_.nickname_updated_at as nickname4_5_,
member0_.nickname as nickname5_5_,
member0_.password as password6_5_,
member0_.profile_photo_url as profile_7_5_,
member0_.role_type as role_typ8_5_,
member0_.team_id as team_id10_5_
from
member member0_
where
member0_.member_id=1
and member0_.member_status='ACTIVE'
//2. 로그인한 유저의 MatchingInfo 조회
select
matchingin0_.matching_info_id as matching1_3_0_,
matchingin0_.dorm_category as dorm_cat2_3_0_,
matchingin0_.gender as gender3_3_0_,
matchingin0_.join_period as join_per4_3_0_,
matchingin0_.is_public as is_publi5_3_0_,
matchingin0_.age as age6_3_0_,
matchingin0_.is_allowed_food as is_allow7_3_0_,
matchingin0_.is_grinding as is_grind8_3_0_,
matchingin0_.is_smoking as is_smoki9_3_0_,
matchingin0_.is_snoring as is_snor10_3_0_,
matchingin0_.is_wear_earphones as is_wear11_3_0_,
matchingin0_.shared_url as shared_12_3_0_,
matchingin0_.clean_up_status as clean_u13_3_0_,
matchingin0_.mbti as mbti14_3_0_,
matchingin0_.shower_time as shower_15_3_0_,
matchingin0_.wake_up_time as wake_up16_3_0_,
matchingin0_.wish_text as wish_te17_3_0_
from
matching_info matchingin0_
where
matchingin0_.matching_info_id=1
//3. 유저의 MatchingInfo를 바탕으로 조건에 부합한 '1. MatchingInfo 2. MatchingInfo의 주인 Member'를 한 번에 조회한다.
// 이때, MatchingInfo-Member (OneToOne) 관계의 주인은 Member이기 때문에, FK는 Member 테이블에만 존재한다.
select
matchingin0_.matching_info_id as col_0_0_,
member1_.member_id as col_1_0_,
member1_.member_id as member_i1_5_1_,
matchingin0_.matching_info_id as matching1_3_0_,
matchingin0_.dorm_category as dorm_cat2_3_0_,
matchingin0_.gender as gender3_3_0_,
matchingin0_.join_period as join_per4_3_0_,
matchingin0_.is_public as is_publi5_3_0_,
matchingin0_.age as age6_3_0_,
matchingin0_.is_allowed_food as is_allow7_3_0_,
matchingin0_.is_grinding as is_grind8_3_0_,
matchingin0_.is_smoking as is_smoki9_3_0_,
matchingin0_.is_snoring as is_snor10_3_0_,
matchingin0_.is_wear_earphones as is_wear11_3_0_,
matchingin0_.shared_url as shared_12_3_0_,
matchingin0_.clean_up_status as clean_u13_3_0_,
matchingin0_.mbti as mbti14_3_0_,
matchingin0_.shower_time as shower_15_3_0_,
matchingin0_.wake_up_time as wake_up16_3_0_,
matchingin0_.wish_text as wish_te17_3_0_,
member1_.email as email2_5_1_,
member1_.matching_info_id as matching9_5_1_,
member1_.member_status as member_s3_5_1_,
member1_.nickname_updated_at as nickname4_5_1_,
member1_.nickname as nickname5_5_1_,
member1_.password as password6_5_1_,
member1_.profile_photo_url as profile_7_5_1_,
member1_.role_type as role_typ8_5_1_,
member1_.team_id as team_id10_5_1_
from
matching_info matchingin0_
left outer join
member member1_
on matchingin0_.matching_info_id=member1_.matching_info_id
where
matchingin0_.is_public=true
and member1_.member_id<>1
and matchingin0_.dorm_category='DORM1'
and matchingin0_.join_period='JOINPERIOD16'
and matchingin0_.gender='FEMALE'
and matchingin0_.is_snoring<>true
and matchingin0_.is_smoking<>true
and matchingin0_.is_grinding<>true
and matchingin0_.is_wear_earphones<>true
and matchingin0_.is_allowed_food<>true
and matchingin0_.age>=20
and matchingin0_.age<=100마지막 쿼리에 대한 해석은 다음과 같습니다.
- MatchingInfo 테이블 내에서 여러 조건들에 부합한 row들 반환
- 이때, 조인한 Member 테이블에서 해당되는 MatchingInfo를 가진 Member의 PK를 같이 반환
OneToMany 관계(컬렉션)를 페치 조인하게 되면, 데이터 뻥튀기 발생으로 데이터 정합성 문제를 발생시킬 수 있고, 하이버네이트는 경고 로그를 남기고 메모리에서 페이징하는 매우 위험한 문제가 발생합니다. (컬렉션 페치 조인 시, 페이징 가급적 사용 금지)
그러나 일대일, 다대일 같은 단일 값 연관 필드들은 페치 조인해도 데이터 뻥튀기가 없기 때문에 페이징이 가능합니다. 해당 케이스는 이 경우에 해당되므로 문제 없이 페이징을 진행해보겠습니다.
2) 페이징 적용: 한 번에 단순 조회 방식
스프링 데이터의 Page, Pageable을 활용해서 페이징을 해보겠습니다.
먼저, 전체 카운트를 한번에 조회하는 단순한 방법을 적용했을 때는 다음과 같습니다.
더보기
@RequiredArgsConstructor
public class MatchingInfoRepositoryCustomImpl implements MatchingInfoRepositoryCustom {
private final JPAQueryFactory queryFactory;
@Override
public Page<MatchingMateResponse> findByMatePreference(final Member loginMember,
final MatePreferenceType preferenceType, Pageable pageable) {
QueryResults<MatchingMateResponse> result = queryFactory
.select(new QMatchingMateResponse(
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member).fetchJoin()
.leftJoin(matchingMate).on(matchingInfo.eq(matchingMate.matchingInfoForTarget))
.where(matchingMate.preferenceType.eq(preferenceType),
matchingMate.owner.eq(loginMember))
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetchResults();
List<MatchingMateResponse> content = result.getResults();
long count = result.getTotal();
return new PageImpl<>(content, pageable, count);
}
}
발생한 쿼리는 다음과 같습니다. (페이징 조건 : 50개 조회)
select
member0_.member_id as member_i1_5_,
member0_.email as email2_5_,
member0_.matching_info_id as matching9_5_,
member0_.member_status as member_s3_5_,
member0_.nickname_updated_at as nickname4_5_,
member0_.nickname as nickname5_5_,
member0_.password as password6_5_,
member0_.profile_photo_url as profile_7_5_,
member0_.role_type as role_typ8_5_,
member0_.team_id as team_id10_5_
from
member member0_
where
member0_.member_id=?
and member0_.member_status=?
select
count(matchingin0_.matching_info_id) as col_0_0_
from
matching_info matchingin0_
left outer join
member member1_
on matchingin0_.matching_info_id=member1_.matching_info_id
left outer join
matching_mate matchingma2_
on (
matchingin0_.matching_info_id=matchingma2_.matching_info_id
)
where
matchingma2_.preference_type=?
and matchingma2_.member_id=?
select
matchingin0_.matching_info_id as col_0_0_,
member1_.member_id as col_1_0_,
member1_.member_id as member_i1_5_1_,
matchingin0_.matching_info_id as matching1_3_0_,
matchingin0_.dorm_category as dorm_cat2_3_0_,
matchingin0_.gender as gender3_3_0_,
matchingin0_.join_period as join_per4_3_0_,
matchingin0_.is_public as is_publi5_3_0_,
matchingin0_.age as age6_3_0_,
matchingin0_.is_allowed_food as is_allow7_3_0_,
matchingin0_.is_grinding as is_grind8_3_0_,
matchingin0_.is_smoking as is_smoki9_3_0_,
matchingin0_.is_snoring as is_snor10_3_0_,
matchingin0_.is_wear_earphones as is_wear11_3_0_,
matchingin0_.shared_url as shared_12_3_0_,
matchingin0_.clean_up_status as clean_u13_3_0_,
matchingin0_.mbti as mbti14_3_0_,
matchingin0_.shower_time as shower_15_3_0_,
matchingin0_.wake_up_time as wake_up16_3_0_,
matchingin0_.wish_text as wish_te17_3_0_,
member1_.email as email2_5_1_,
member1_.matching_info_id as matching9_5_1_,
member1_.member_status as member_s3_5_1_,
member1_.nickname_updated_at as nickname4_5_1_,
member1_.nickname as nickname5_5_1_,
member1_.password as password6_5_1_,
member1_.profile_photo_url as profile_7_5_1_,
member1_.role_type as role_typ8_5_1_,
member1_.team_id as team_id10_5_1_
from
matching_info matchingin0_
left outer join
member member1_
on matchingin0_.matching_info_id=member1_.matching_info_id
left outer join
matching_mate matchingma2_
on (
matchingin0_.matching_info_id=matchingma2_.matching_info_id
)
where
matchingma2_.preference_type=?
and matchingma2_.member_id=? limit ?
카운트 쿼리가 추가되어 총 3번 쿼리가 나가게 됩니다.
3) 페이징 적용: '데이터 내용'과 '카운트 쿼리' 별도 조회 방식
이번에는 데이터 내용과 전체 카운트를 별도로 조회해서 조금 더 최적화 해보겠습니다.
코드는 아래와 같습니다.
더보기
@RequiredArgsConstructor
public class MatchingInfoRepositoryCustomImpl implements MatchingInfoRepositoryCustom {
private final JPAQueryFactory queryFactory;
@Override
public Page<MatchingMateResponse> findByMatePreference(final Member loginMember,
final MatePreferenceType preferenceType, Pageable pageable) {
List<MatchingMateResponse> content = queryFactory
.select(new QMatchingMateResponse(
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member).fetchJoin()
.leftJoin(matchingMate).on(matchingInfo.eq(matchingMate.matchingInfoForTarget))
.where(matchingMate.preferenceType.eq(preferenceType),
matchingMate.owner.eq(loginMember))
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
long count = queryFactory
.selectFrom(matchingInfo)
.leftJoin(matchingMate).on(matchingInfo.eq(matchingMate.matchingInfoForTarget))
.where(matchingMate.preferenceType.eq(preferenceType),
matchingMate.owner.eq(loginMember))
.fetchCount();
return new PageImpl<>(content, pageable, count);
}
발생한 쿼리 (이전과 같이 50개 조회)
이전 기본 페이징에는 fetchResults()를 사용했지만,
- 카운트 쿼리와 분리할 때는 fetch()를 사용해서 content만 바로 가져옵니다.
- totalCount는 fetchCount()를 이용해서 따로 가져옵니다.
이렇게 전체 카운트 조회하는 방법을 분리하면 성능 향상 효과를 기대할 수 있습니다. 조인을 좀 줄여도 되거나, 다른 곳에 물려 있거나 하는 경우 별도로 작성해서 성능 향상을 기대할 수 있습니다.
그러나 해당 기능에서는 그닥 성능 차이가 없었습니다. 이런 경우에는 오히려 카운트 쿼리를 분리하기 보단 1번 방식을 사용하는 것이 더 좋을 것 같습니다.
4) 페이징 적용: PageableExecutionUtils.getPage()로 최적화
코드는 아래와 같습니다.
@RequiredArgsConstructor
public class MatchingInfoRepositoryCustomImpl implements MatchingInfoRepositoryCustom {
private final JPAQueryFactory queryFactory;
@Override
public Page<MatchingMateResponse> findByMatePreference(final Member loginMember,
final MatePreferenceType preferenceType, Pageable pageable) {
List<MatchingMateResponse> content = queryFactory
.select(new QMatchingMateResponse(
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member).fetchJoin()
.leftJoin(matchingMate).on(matchingInfo.eq(matchingMate.matchingInfoForTarget))
.where(matchingMate.preferenceType.eq(preferenceType),
matchingMate.owner.eq(loginMember))
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
JPAQuery<MatchingInfo> countQuery = queryFactory
.selectFrom(matchingInfo)
.leftJoin(matchingMate).on(matchingInfo.eq(matchingMate.matchingInfoForTarget))
.where(matchingMate.preferenceType.eq(preferenceType),
matchingMate.owner.eq(loginMember));
return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchCount);
}
}
- 스프링 데이터 라이브러리가 제공
- count 쿼리가 생략 가능한 경우 생략해서 처리
- 페이지 시작이면서 컨텐츠 사이즈가 페이지보다 작을 때
- 마지막 페이지 일 때 offset + 컨텐츠 사이즈 더해서 전체 사이즈 구함
동적 쿼리 처리 : where 절 파라미터 방식 사용
매칭 조회 기능에는 '기본 조건에 부합하는 메이트 목록 조회 기능'과 '유저가 요청한 필터링 조건에 부합하는 메이트 목록 조회 기능'이 있습니다.
조건이 다소 복잡하기 때문에, where 절에 파라미터 방식을 사용하여 조건 재사용이 가능하게 해주면서 가독성을 높이는 해당 방식을 사용하였습니다.
@RequiredArgsConstructor
public class MatchingInfoRepositoryCustomImpl implements MatchingInfoRepositoryCustom {
private final JPAQueryFactory queryFactory;
@Override
public List<MatchingMateResponse> findByBasicConditions(final Member loginMember) {
return queryFactory
.select(Projections.constructor(MatchingMateResponse.class,
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member, member).fetchJoin()
.where(basicCond(loginMember.getId(), loginMember.getMatchingInfo()))
.fetch();
}
@Override
public List<MatchingMateResponse> findBySpecialConditions(final Member loginMember,
final MatchingMateFilterRequest request) {
return queryFactory
.select(Projections.constructor(MatchingMateResponse.class,
matchingInfo,
matchingInfo.member.id.as("memberId"))
)
.from(matchingInfo)
.leftJoin(matchingInfo.member, member).fetchJoin()
.where(specialCond(loginMember.getId(), loginMember.getMatchingInfo(), request))
.fetch();
}
private BooleanExpression basicCond(Long memberId, MatchingInfo matchingInfo) {
return matchingPublic()
.and(memberNotEq(memberId))
.and(dormEq(matchingInfo.getDormInfo().getDormCategory()))
.and(joinPeriodEq(matchingInfo.getDormInfo().getJoinPeriod()))
.and(genderEq(matchingInfo.getDormInfo().getGender()));
}
private BooleanExpression specialCond(Long memberId, MatchingInfo matchingInfo, MatchingMateFilterRequest request) {
return basicCond(memberId, matchingInfo)
.and(isSnoringEq(request.isSnoring()))
.and(isSmokingEq(request.isSmoking()))
.and(isGrindingEq(request.isGrinding()))
.and(isWearEarphonesEq(request.isWearEarphones()))
.and(isAllowedFoodEq(request.isAllowedFood()))
.and(ageGoe(request.minAge()))
.and(ageLoe(request.maxAge()));
}
private BooleanExpression matchingPublic() {
return matchingInfo.isPublic.eq(true);
}
private BooleanExpression memberNotEq(Long memberId) {
return isEmpty(memberId) ? null : member.id.ne(memberId);
}
private BooleanExpression dormEq(DormCategory dormCategory) {
return isEmpty(dormCategory) ? null : matchingInfo.dormInfo.dormCategory.eq(dormCategory);
}
private BooleanExpression joinPeriodEq(JoinPeriod joinPeriod) {
return isEmpty(joinPeriod) ? null : matchingInfo.dormInfo.joinPeriod.eq(joinPeriod);
}
private BooleanExpression genderEq(Gender gender) {
return isEmpty(gender) ? null : matchingInfo.dormInfo.gender.eq(gender);
}
private BooleanExpression isSnoringEq(Boolean isSnoring) {
return isEmpty(isSnoring) ? null : matchingInfo.preferenceInfo.isSnoring.ne(isSnoring);
}
private BooleanExpression isSmokingEq(Boolean isSmoking) {
return isEmpty(isSmoking) ? null : matchingInfo.preferenceInfo.isSmoking.ne(isSmoking);
}
private BooleanExpression isGrindingEq(Boolean isGrinding) {
return isEmpty(isGrinding) ? null : matchingInfo.preferenceInfo.isGrinding.ne(isGrinding);
}
private BooleanExpression isWearEarphonesEq(Boolean isWearEarphones) {
return isEmpty(isWearEarphones) ? null : matchingInfo.preferenceInfo.isWearEarphones.ne(isWearEarphones);
}
private BooleanExpression isAllowedFoodEq(Boolean isAllowedFood) {
return isEmpty(isAllowedFood) ? null : matchingInfo.preferenceInfo.isAllowedFood.ne(isAllowedFood);
}
private BooleanExpression ageGoe(Integer ageGoe) {
return isEmpty(ageGoe) ? null : matchingInfo.preferenceInfo.age.goe(ageGoe);
}
private BooleanExpression ageLoe(Integer ageLoe) {
return isEmpty(ageLoe) ? null : matchingInfo.preferenceInfo.age.loe(ageLoe);
}
}
관련 PR 링크
https://github.com/idorm/idorm-server/pull/59
[Refactor] 룸메이트 매칭 조회 기능 쿼리 튜닝 및 페이징 적용 by nahyeon99 · Pull Request #59 · idorm/idorm-
❗️ 이슈 번호 close #57 📝 구현 내용 N+1 문제 단건 쿼리로 조회한 N개의 MatchingInfo를 바탕으로, N번의 쿼리를 통해 MatchingInfo의 주인인 Member를 조회하는 쿼리가 N번 나가는 N+1 문제가 있었습니다.
github.com
참고 자료
실전! Querydsl: 6. 실무 활용 - 스프링 데이터 JPA와 Querydsl
실전! 스프링 부트와 JPA 활용 2편: API 개발 고급 - 컬렉션 조회 최적화
'Spring' 카테고리의 다른 글
| [Spring] 이메일 인증 기능 비동기 처리와 주의사항: @Async, @EnableAsync (0) | 2024.04.10 |
|---|---|
| [Spring] 프록시 방식의 AOP 한계: self-invocation 이슈 (0) | 2024.04.09 |
| [Spring] 쿼리 카운터 개발기: JPA N+1문제를 찾아서 (0) | 2024.03.28 |
| [Spring] 헥사고날 아키텍처 도입기: 계층형에서 헥사고날 아키텍처로 (0) | 2024.03.22 |
| [Spring] 컨트롤러 내 회원 인증 중복 코드 개선기 (2) | 2024.03.12 |