목록 조회 시 페이징 쿼리 최적화를 통한 최소 10배 성능 개선
여러 데이터를 목록 조회 시 페이징 쿼리를 작성하여 데이터를 쿼리하는 작업을 진행했다.
나의 경우 게시글 목록을 불러오는데, 이때 커버링 인덱스를 활용하여 페이징 쿼리 최적화를 통해 성능을 개선했던 방법에 대해 작성해보려 한다.
배경
위와 같이 메인페이지에서 스터디 목록을 조회할 수 있어야 한다.
이때 스터디 목록 조회 시 다양한 필터링(카테고리, 진행 방식, 모집 방식, 모집 여부, 좋아요 등)이 있을 수 있으면 가장 최신 순으로 데이터가 보여져야 하는 흔한 페이징 쿼리가 필요했다.
초기 구현 - 단순 페이징 쿼리
페이징 쿼리를 작성할 때, 여러 필터 옵션들에 대해 동적으로 쿼리를 생성해야 했다. 이때 여러가지 방법이 있겠지만, 타입 안정성을 보장 받을 수 있는데 QueryDsl을 선택하여 구현했다.
방식은 Spring이 제공하는 Pageable 객체와 필요한 검색 필터를 QueryParameter로 전달 받고 이를 통해 동적 쿼리를 생성했다.
public Page<StudiesResponseDto> findStudies(Pageable pageable, String categoryCond, String meetingTypeCond, String recruitmentStatusCond, String progressStatusCond) {
List<StudiesResponseDto> results = jpqlQueryFactory
.select(Projections.constructor(StudiesResponseDto.class,
study.id,
study.title,
study.category,
study.meetingType,
study.progressStatus,
study.recruitmentStatus,
study.recruitmentEndDate,
JPAExpressions
.select(studyMember.count())
.from(studyMember)
.where(studyMember.study.eq(study)
.and(studyMember.status.in(Arrays.asList(ParticipationStatus.ACTIVATED, ParticipationStatus.COMPLETED)))
),
study.maxStudyMemberCount,
study.viewCount,
study.tags))
.from(study)
.where(
categoryEq(categoryCond),
meetingTypeEq(meetingTypeCond),
recruitmentStatusEq(recruitmentStatusCond),
progressStatusCond(progressStatusCond)
)
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.orderBy(getOrderSpecifiers(pageable))
.fetch();
JPQLQuery<Study> countQuery = jpqlQueryFactory
.select(study)
.from(study)
.where(
categoryEq(categoryCond),
meetingTypeEq(meetingTypeCond),
recruitmentStatusEq(recruitmentStatusCond),
progressStatusCond(progressStatusCond)
);
return PageableExecutionUtils.getPage(results, pageable, countQuery::fetchCount);
}
위와 같이 일반적인 페이징 쿼리를 쿼리를 구현하였고 쿼리 시간을 측정했을 때 다음과 같은 결과가 나왔다.
테스트 환경 1
- 전체 데이터 : 600
- 카테고리 수 : 6
- 각 케티고리 별 데이터 수 : 100
- pageSize : 12
- pageNum : 6
- 카테고리 필터링 여부 : True (1개 값으로 필터링)
위 환경에서 데이터를 조회했을 때 약 330ms의 결과가 나왔다.
음.. 많지 않은 데이터지만 그닥 빠르다고 느낄 수는 없는 성능이었다. 따라서 극단적으로 데이터의 수를 늘리고 테스트를 다시 진행해 보았다.
테스트 환경 2 - 대용량 데이터
- 전체 데이터 : 1000만
- 카테고리 수 : 6
- 각 카테고리 별 데이터 수 : 약 1666666
- pageSize : 12
- pageNum : 50000
- 카테고리 필터링 여부 : True (1개 값으로 필터링)
위와 같이 데이터를 많이 채워넣은 상황에서 테스트 시 쿼리 시간이 무려 약 4262ms가 나타났다...
작은 데이터 규모일 때는 그래도 봐줄만 했던 쿼리 성능이 데이터가 늘어남에 따라 쿼리 시간이 기하급수적으로 증가하는 문제가 발생했다.
물론 우리 프로젝트에서는 테스트 환경에서 만큼의 대용량 데이터를 처리할 일은 없을 것이지만, 데이터가 적은 수에서도의 성능은 그다지 만족스럽지 못해 개선이 필요했다.
문제 파악
기존 페이징 쿼리가 느린 이유률 찾기 위해 query 실행 계획을 살펴보았다.
일단 내가 설계한 index를 적절하게 잘 사용한 게 보이며(key) 2897680개라는 상당히 많은 로우를 가지고 있다.
여기서 주의깊게 볼 부분은 Extra에서 using Where와 Backward index scan가 존재하는데 이런 상황에서는 데이터를 가져오기 위해 실제 테이블 데이터를 조회(lookup) 과정에서 속도가 떨어질 수 있다.
그 이유는 인덱스 구조에 대해 살펴보면 알 수 있다.
1. Clustered Index
기본적으로 InnoDB는 테이블마다 Clustered Index를 자동 생성한다. Clustered Index란 Primary Key로 생성되는 인덱스를 의미한다.
Clustered Index의 leaf node는 key로 pk를 가지며 value에 데이터를 가지고 있는 형태가 된다.
2. Secondary Index
Secondary Index란 우리가 생성하는 인덱스로 보조 인덱스라고도 부른다. Non-clustered Index라고도 한다.
Secondary Index는 leaf node에 데이터에 접근하기 위한 포인터를 가지고 있다. 이 말이 무슨 의미냐면 위 그림처럼 데이터가 존재하는 위치는 Clustered Index의 value에 Data가 들어가 있다. 따라서 Secondary Index는 leaf node에 해당 데이터가 위치한 Clustered Index의 포인터, 즉 Primary Key를 가지고 있는 것이다.
Clustered Index의 leaf node -> pk를 가짐
기존 내 페이징 쿼리는 Secondary Index를 활용하는 방식이다.(key: idx_category_created_at)
이때 데이터를 탐색하는 과정을 위 그림과 함께 살펴보면 데이터가 많아질 때 속도가 느려지는 이유를 쉽게 알 수 있었다.
내가 조회할 필드는 다음과 같다.
(편의상 서브 쿼리는 생략)
- study.id
- study.title
- study.category
- study.meetingType
- study.progressStatus
- study.recruitmentStatus
- study.recruitmentEndDate
- study.maxStudyMemberCount
- study.viewCount
- study.tags
List<Long> findStudyId = jpqlQueryFactory
.select(study.id)
.from(study)
.where(
categoryEq(categoryCond),
meetingTypeEq(meetingTypeCond),
recruitmentStatusEq(recruitmentStatusCond),
progressStatusCond(progressStatusCond)
)
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.orderBy(getOrderSpecifiers(pageable))
.fetch();
List<StudiesResponseDto> results = jpqlQueryFactory
.select(Projections.constructor(StudiesResponseDto.class,
study.id,
study.title,
study.category,
study.meetingType,
study.progressStatus,
study.recruitmentStatus,
study.recruitmentEndDate,
JPAExpressions
.select(studyMember.count())
.from(studyMember)
.where(studyMember.study.eq(study)
.and(studyMember.status.in(Arrays.asList(ParticipationStatus.ACTIVATED, ParticipationStatus.COMPLETED)))
),
study.maxStudyMemberCount,
study.viewCount,
study.tags))
.from(study)
.where(study.id.in(findStudyId))
.orderBy(getOrderSpecifiers(pageable))
.fetch();
JPQLQuery<Study> countQuery = jpqlQueryFactory
.select(study)
.from(study)
.where(
categoryEq(categoryCond),
meetingTypeEq(meetingTypeCond),
recruitmentStatusEq(recruitmentStatusCond),
progressStatusCond(progressStatusCond)
);
return PageableExecutionUtils.getPage(results, pageable, countQuery::fetchCount);이때 Secondary Index를 활용해서 데이터 탐색을 진행하면 Secondary Index에는 category, created_at, pk밖에 존재하지 않는다. 따라서 study.id, study.title 등 필요한 컬럼을 가져오기 위해서 데이터 전체 조회가 필요하게 된다.
데이터 전체 조회를 하기 위해서는 Secondary Index의 leaf node에 있는 pk를 통해 Clustered Index를 탐색하고 data를 조회한다.
Secondary Index 탐색 (pk 추출) -> Clustered Index 탐색 -> 데이터 조회
DB가 조건에 맞는 행들을 찾는 도중 옵타마이저가 정렬 과정에서 일부 컬럼을 추가로 읽는, lookup 과정이 발생한다면 위 과정을 통해 데이터를 조회하게 되어 성능이 떨어지게 된다.
커버링 인덱스를 활용한 성능 개선
따라서 나는 성능을 개선시키기 위해 커버링 인덱스(Convering Index)를 사용했는데, 커버링 인덱스란 쿼리에서 요구하는 컬럼을 인덱스 자체 포함시켜서 실제 테이블 데이터에 접근하지 않아도 쿼리 결과를 반환할 수 있게 하는 인덱스이다.
예를 들어 위에서 다양한 필드를 조회하는데, 만약 내가 study.id만 조회한다면 내 Secondary Index의 leaf node에는 study.id를 갖고 있기 때문에 따로 데이터를 조회할 필요가 없는 겂이다.
이것을 이용해 커버링 인덱스를 통해 먼저 조건에 맞는 id를 조회 후 해당 id에 해당하는 데이터만 불러오는 방식으로(two-step) 쿼리를 수정했다.
그리고 테스트 진행 시 다음과 같은 결과를 받았다.
테스트 환경 1
- 전체 데이터 : 600
- 카테고리 수 : 6
- 각 케티고리 별 데이터 수 : 100
- pageSize : 12
- pageNum : 6
- 카테고리 필터링 여부 : True (1개 값으로 필터링)
위 환경에서 데이터를 조회했을 때 약 11ms의 결과가 나왔다. 데이터가 적은 상황에서도 기존 330ms보다 훨씬 빠른 속도로 개선됬다.
또한 대용량 데이터에서도 효과 컸다.
테스트 환경 2 - 대용량 데이터
- 전체 데이터 : 1000만
- 카테고리 수 : 6
- 각 카테고리 별 데이터 수 : 약 1666666
- pageSize : 12
- pageNum : 50000
- 카테고리 필터링 여부 : True (1개 값으로 필터링)
여기서는 479ms로 기존 4262ms 보다 개선되어 약 10배 정도의 성능 개선 효과가 나타났다.