페이징 성능 개선하기 - No Offset, Covering Index, Non Clustered Index 사용
2024. 1. 16. 15:50ㆍProject
728x90
개요
현재 진행하고 있는 프로젝트에 단어 테스트 결과 목록 조회는 무한 스크롤 방식으로 구현할 예정이기 때문에 페이지가 뒤로 갈수록 느려지는 기존 페이징 방식 대신 아무리 페이지가 뒤로 가더라도 처음 페이지를 읽는 것과 같은 No Offset 방식을 사용하게 되었습니다.
단어 테스트 결과 목록 조회를 하기 위한 VocabularyTest, User, VocabularyLearning 테이블에 각 1,000,000개의 임의 데이터를 저장 후 테스트를 진행하였습니다.
Before 코드
/* Controller */
@GetMapping("/admin/vocabulary-book/test/result/list")
public ResponseEntity vocabularyTestResultLists(Authentication admin, @PageableDefault(size = 10, sort = "modifiedAt", direction = Sort.Direction.DESC) Pageable pageable) {
List<VocabularyTestResultListResponse> response = adminService.vocabularyTestResultLists(admin, pageable);
return ResponseEntity.ok().body(new BaseResponse(200, "단어 테스트 결과 목록 조회에 성공했습니다.", response));
}
/* Serivce */
@Override
public List<VocabularyTestResultListResponse> vocabularyTestResultLists(Authentication admin, Pageable pageable) {
User adminUser = (User) admin.getPrincipal();
String className = adminUser.getClassName();
List<VocabularyTestResultListResponse> responses = vocabularyTestRepository.findByTestResultList(pageable, className.equals("master") ? null : className);
return responses;
}
/* Repository */
@Query("select new backend.VocaProject.admin.dto.VocabularyTestResultListResponse(" +
"a.user.username, a.testCount, a.result, a.record, a.vocabularyBookCategory.name, a.firstDay, a.lastDay, a.modifiedAt, " +
"(select b.learningTime " +
"from VocabularyLearning b " +
"where b.user = a.user AND b.vocabularyBookCategory = a.vocabularyBookCategory AND b.firstDay = a.firstDay AND b.lastDay = a.lastDay" +
") as learningTime" +
") " +
"from VocabularyTest a " +
"where (:className IS NULL OR a.user.className = :className)")
List<VocabularyTestResultListResponse> findByTestResultList(Pageable pageable, @Param("className") String className);
After 코드
- 주요 변경사항:
- RequestParameter를 Pageable → size(페이지 사이즈), lastModifiedAt(마지막 조회 modifiedAt) 변경
- JPQL → QueryDSL 변경
- VocabularyTest Entity에 Non Clustered Index 설정 코드 추가
- QueryDSL 쿼리에 Covering Index 적용
/*
Controller
1. @RequestParameter Pageable -> size(페이지 사이즈), lastModifiedAt(마지막 조회 modifiedAt) 변경
*/
@GetMapping("/admin/vocabulary-book/test/result/list")
public ResponseEntity vocabularyTestResultLists(Authentication admin, @RequestParam int size, @RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime lastModifiedAt) {
List<VocabularyTestResultListResponse> response = adminService.vocabularyTestResultLists(admin, size, lastModifiedAt);
return ResponseEntity.ok().body(new BaseResponse(200, "단어 테스트 결과 목록 조회에 성공했습니다.", response));
}
/*
Serivce
1. Pageable -> size(페이지 사이즈), lastModifiedAt(마지막 조회 modifiedAt) 변경
2. vocabularyTestRepository(JPQL Repository)
⬇︎
vocabularyTestCustomRepository(QueryDSL Repository) 변경
*/
@Override
public List<VocabularyTestResultListResponse> vocabularyTestResultLists(Authentication admin, int size, LocalDateTime lastModifiedAt) {
User adminUser = (User) admin.getPrincipal();
String className = adminUser.getClassName();
List<VocabularyTestResultListResponse> responses = vocabularyTestCustomRepository.findByTestResultList(size, lastModifiedAt, className.equals("master") ? null : className);
return responses;
}
/*
VocabularyTest Entity
3. 아래 Non Clustered Index 설정 추가
*/
@Table(name = "vocabularyTest", indexes = @Index(name = "idxModifiedAt", columnList = "modifiedAt"))
/*
Repository
2. JPQL -> QueryDSL로 변경
4. Covering Index 적용
*/
private final JPAQueryFactory queryFactory;
public VocabularyTestCustomRepositoryImpl(JPAQueryFactory jpaQueryFactory) {
this.queryFactory = jpaQueryFactory;
}
@Override
public List<VocabularyTestResultListResponse> findByTestResultList(int size, LocalDateTime lastModifiedAt, String className) {
QVocabularyTest vocabularyTest = QVocabularyTest.vocabularyTest;
QVocabularyLearning vocabularyLearning = QVocabularyLearning.vocabularyLearning;
// 4) 커버링 인덱스로 대상 조회
List<Long> ids = queryFactory
.select(vocabularyTest.id)
.from(vocabularyTest)
.where(eqClassName(className), ltModifiedAt(lastModifiedAt))
.orderBy(vocabularyTest.modifiedAt.desc())
.limit(size)
.fetch();
// 4-1) 대상이 없을 경우 추가 쿼리 수행 할 필요 없이 바로 반환
if (CollectionUtils.isEmpty(ids)) {
return new ArrayList<>();
}
return queryFactory
.select(Projections.constructor(VocabularyTestResultListResponse.class,
vocabularyTest.user.username,
vocabularyTest.testCount,
vocabularyTest.result,
vocabularyTest.record,
vocabularyTest.vocabularyBookCategory.name,
vocabularyTest.firstDay,
vocabularyTest.lastDay,
vocabularyTest.modifiedAt,
JPAExpressions.select(vocabularyLearning.learningTime)
.from(vocabularyLearning)
.where(
vocabularyLearning.user.eq(vocabularyTest.user),
vocabularyLearning.vocabularyBookCategory.eq(vocabularyTest.vocabularyBookCategory),
vocabularyLearning.firstDay.eq(vocabularyTest.firstDay),
vocabularyLearning.lastDay.eq(vocabularyTest.lastDay)
)
))
.from(vocabularyTest)
.where(vocabularyTest.id.in(ids))
.orderBy(vocabularyTest.modifiedAt.desc())
.fetch();
}
private BooleanExpression eqClassName(String className) {
if (className == null) {
return null;
}
return vocabularyTest.user.className.eq(className);
}
private BooleanExpression ltModifiedAt(LocalDateTime modifiedAt) {
if (modifiedAt == null) {
return null;
}
return vocabularyTest.modifiedAt.lt(modifiedAt);
}
Postman 속도
No Offset, Covering Index, Non Clustered Index 적용 전
마지막 페이지 조회(9.85초)
No Offset, Covering Index, Non Clustered Index 적용 후
마지막 페이지 조회(기존 9.85초 → 0.014초 개선)
JPQL → QueryDSL로 변경 후 No Offset, Covering Index를 적용 하였더니,
마지막 페이지 조회 속도가 9.85초 → 0.014초로 많이 개선이 되었습니다.
MySQL 쿼리
No Offset, Non Clustered Index 적용 전
마지막 페이지 조회(0.65초)
select a.user_id, a.test_count, a.result, a.record, a.category_id, a.first_day, a.last_day, date_format(a.modified_at, '%Y-%m-%d'),
(select b.learning_time
from vocabulary_learning b
where b.user_id = a.user_id AND b.category_id = a.category_id AND b.first_day = a.first_day AND b.last_day = a.last_day
) as learning_time
from vocabulary_test a
order by a.modified_at desc limit 10 offset 1000000;
No Offset, Non Clustered Index 적용 후
마지막 페이지 조회(0.65초 → 0.00초 개선)
select a.user_id, a.test_count, a.result, a.record, a.category_id, a.first_day, a.last_day, date_format(a.modified_at, '%Y-%m-%d') as modified_at,
(select b.learning_time
from vocabulary_learning b
where b.user_id = a.user_id AND b.category_id = a.category_id AND b.first_day = a.first_day AND b.last_day = a.last_day
) as learning_time
from vocabulary_test a
where a.modified_at < '1910-12-10 12:30'
order by a.modified_at desc limit 10;
modified_at을 Non Clustered Index로 설정하고 No Offset 방식으로 쿼리를 변경하였더니
마지막 페이지 조회 속도가 0.65초 → 0.00초로 향상된 것을 볼 수 있습니다.
jmeter 테스트
No Offset, Covering Index, Non Clustered Index 적용 전
마지막 페이지 조회
1명의 유저가 1초에 한번 요청을 보냈을 때
50명의 유저가 1초에 한번 요청을 보냈을 때
DataAccessResourceFailureException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection 오류 발생
100명의 유저가 1초에 한번 요청을 보냈을 때
DataAccessResourceFailureException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection 오류 발생
No Offset, Covering Index, Non Clustered Index 적용 후
마지막 페이지 조회
1명의 유저가 1초에 한번 요청을 보냈을 때
50명의 유저가 1초에 한번 요청을 보냈을 때
100명의 유저가 1초에 한번 요청을 보냈을 때
5000명의 유저가 1초에 한번 요청을 보냈을 때
No Offset, Covering Index, Non Clustered Index 적용 전에는
50명, 100명 유저가 1초에 한 번 요청을 보냈을 때 DataAccessResourceFailureException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection 오류 발생 했었는데,
No Offset, Covering Index, Non Clustered Index 적용 후에는 50명, 100명 유저가 1초에 한 번 요청을 보냈을 때 오류가 발생하지 않고 1,000명 유저가 1초에 한 번 요청을 보냈을 때도 오류가 발생하지 않게 되었습니다.
728x90