커버링 인덱스를 활용한 페이지네이션 성능 개선하기

들어가며

안녕하세요. 요즘에 플러피(fluffy)라는 온라인 시험 문제 제작 및 관리 서비스를 개발하고 있습니다. 플러피 서비스의 첫 화면에서 최근 출제된 시험 목록을 보여줍니다. 출제된 시험이 점차 늘어남에 따라 전체 목록을 조회하는 것은 성능적으로 느려질 수 있습니다. 이러한 문제를 해결하기 위해 페이지네이션을 도입하였습니다.

 

페이지네이션에는 전통적인 게시판에서 자주 사용하는 Offset 방식과 SNS의 무한 스크롤에서 사용하는 Cursor (No Offset)방식이 있습니다. Offset 방식은 페이지 번호를 기반으로 사용자가 원하는 특정 페이지로 이동할 수 있도록 하며, 각 페이지의 시작 위치를 지정하여 데이터를 가져옵니다. 반면, Cursor 방식은 사용자가 마지막으로 본 항목을 기준으로 다음 항목을 불러오는 방식입니다. 

 

플러피에서 적용한 Offset 방식 페이지네이션

 

플러피에서 출제된 시험 목록을 보여주는 부분은 위와 같이 Offset 방식의 페이지네이션을 사용하고 있습니다. Offset 방식은 Cursor 방식과 달리 특정 페이지의 데이터를 가져오기 위해 모든 이전 항목을 스캔해야합니다. 하지만 사용자가 페이지를 쉽게 인식하고, 특정 페이지로 직접 이동할 수 있는 장점이 있기 때문에 Offset 방식 도입을 선택했습니다.

 

관련 코드를 수정하던 중 현재 구현된 방식은 성능적으로 문제가 있다는 사실을 알게 되었습니다. 이 문제를 해결하기 위해서 특정 페이지의 시험 아이디들을 커버링 인덱스로 조회한 후, 해당 아이디들로 시험 정보를 조회하는 방식으로 변경해보았는데요. 이 방법을 기존 코드와 개선 코드로 비교하고, 예시 코드를 들어서 설명해보겠습니다.

개발 환경

현재 플러피 서비스는 다음과 같은 환경에서 개발되었습니다. 참고하시면 좋을 것 같습니다.

• Spring Boot 3.3.5
• Spring Data JPA
• Lombok
• Querydsl 5.1.0
• PostgreSQL 15.6

기존 코드

기존에 구현했던 "최근 출제된 시험 목록 조회 코드"를 보겠습니다.

@Repository
@RequiredArgsConstructor
public class ExamRepositoryImpl implements ExamRepositoryCustom {
	
    private final JPAQueryFactory queryFactory;

    @Override
    public Page<ExamSummaryDto> findPublishedExamSummaries(Pageable pageable) {
        JPAQuery<Long> countQuery = queryFactory.select(exam.count())
                .from(exam)
                .where(exam.status.eq(ExamStatus.PUBLISHED));

        List<ExamSummaryDto> content = queryFactory
                .select(new QExamSummaryDto(
                        exam.id,
                        exam.title.value,
                        exam.description.value,
                        exam.status,
                        AUTHOR_PROJECTION,
                        question.count(),
                        exam.createdAt,
                        exam.updatedAt
                ))
                .from(exam)
                .leftJoin(member).on(exam.memberId.eq(member.id))
                .leftJoin(exam.questionGroup.questions, question)
                .where(exam.status.eq(ExamStatus.PUBLISHED))
                .groupBy(
                        exam.id,
                        exam.title,
                        exam.description,
                        exam.status,
                        member.id,
                        member.name,
                        member.avatarUrl,
                        exam.createdAt,
                        exam.updatedAt
                )
                .orderBy(exam.updatedAt.desc())
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();

        return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
    }
}

기존에 구현했던 "최근 출제된 시험 목록 조회 코드"는 다음과 같은 방식으로 동작합니다.

 

우선, exam 테이블에서 member와 question 테이블을 조인하여 데이터를 가져옵니다. 이 과정에서 각 시험의 작성자 정보와 문제 수를 함께 반환합니다. 또한, 시험의 상태를 나타내는 ExamStatus 필드를 기준으로, Published 상태인 시험만을 WHERE 절을 통해 필터링합니다. 결과는 updatedAt 필드를 기준으로 내림차순으로 정렬되며, OFFSET과 LIMIT을 사용하여 페이지네이션을 구현합니다.

 

이 코드를 실행했을 때 생성되는 SQL 쿼리를 살펴보겠습니다.

select
    e1_0.id,
    e1_0.title,
    e1_0.description,
    e1_0.status,
    m1_0.id,
    m1_0.name,
    m1_0.avatar_url,
    count(q1_0.id),
    e1_0.created_at,
    e1_0.updated_at 
from
    exam e1_0 
left join
    member m1_0 
        on e1_0.member_id=m1_0.id 
left join
    question q1_0 
        on e1_0.id=q1_0.exam_id 
where
    e1_0.status=? 
group by
        e1_0.id,
        e1_0.title,
        e1_0.description,
        e1_0.status,
        m1_0.id,
        m1_0.name,
        m1_0.avatar_url,
        e1_0.created_at,
        e1_0.updated_at 
order by
    e1_0.updated_at desc 
offset
    ? rows 
fetch
    first ? rows only

개선 방향

Offset 방식은 offset과 limit을 사용합니다. 예를 들어, limit이 100이고, 1,000번째 페이지를 조회하고 싶다면 offset은 100,000이 되어야 합니다. 이렇게 되면 100,000개의 데이터를 스캔해야 하므로 성능적으로 매우 비효율적입니다. 

 

그렇기 때문에 인덱스(Index) 사용이 필수입니다. 인덱스는 데이터의 검색을 빠르게 검색하고 조회할 수 있도록 도와주는 자료구조입니다. 인덱스 중 커버링 인덱스는 특정 쿼리에 필요한 모든 정보를 인덱스 자체에서 제공할 수 있는 것을 의미합니다. 즉, SELECT, WHERE, GROUP BY, ORDER BY, LIMIT 등에서 사용되는 모든 컬럼이 인덱스에 포함되어 있음을 의미합니다.

 

위 코드에서 사용되는 모든 컬럼을 다 인덱스에 넣으면 될까요? 모든 값을 인덱스에 넣을 경우 인덱스의 크기가 커져서 저장 공간을 많이 차지하게 됩니다. 또한, 새로운 데이터가 추가될 때마다 인덱스를 업데이트해야 하므로 쓰기 성능이 저하될 수 있습니다.

 

그렇다면, 어떤 식으로 인덱스를 사용할 수 있을까요?

 

먼저, JOIN을 한 후 페이징을 진행하므로, JOIN된 데이터의 양이 많을 경우, 전체 결과를 메모리에 적재해야 하므로 성능 저하가 발생할 수 있습니다. 위 코드의 경우 WHERE, GROUP BY, ORDER BY에 JOIN된 결과가 필요하지 않으므로 페이징 이후에 JOIN하는 것으로 개선할 수 있습니다.

 

페이지네이션을 위해서 필요한 것은 ExamStatus가 Published인지 여부와 정렬을 위한 updatedAt, 결과 반환을 위한 Exam ID입니다. 이것을 커버링 인덱스로 조회하고, 이후에 JOIN 등을 거쳐, 특정 페이지의 출제된 시험 목록을 조회할 수 있습니다.

 

핵심적인 부분만 간추려서 SQL 쿼리로 표현해보겠습니다.

SELECT 다양한 컬럼들
FROM exam e
LEFT JOIN member m ON e.member_id = m.id
LEFT JOIN question q ON e.id = q.exam_id
WHERE e.id IN (
    SELECT id 
    FROM exam 
    WHERE status = 'PUBLISHED' 
    ORDER BY updated_at DESC 
    OFFSET :offset LIMIT :pageSize
)
GROUP BY 다양한 컬럼들
ORDER BY e.updated_at DESC;

WHERE e.id IN ( ... )은 출제된 시험을 updated_at으로 내림차순 하고 페이지네이션 한 후 시험 ID를 가져오는 SQL 쿼리입니다. 이 부분을 커버링 인덱스를 사용해서 빠르게 조회할 수 있습니다. 이후에 JOIN과 GROUP BY, ORDER BY를 거쳐 출제된 시험 목록을 조회합니다.

시험 ID들을 조회할 때와 시험 ID들을 사용해서 전체 결과를 얻을 때 모두 ORDER BY를 사용하는 이유는 WHERE IN을 통해서 정렬이 보장되지 않기 때문입니다. 참고해주세요.

개선 코드

실제로 개선한 코드는 다음과 같습니다

@Repository
@RequiredArgsConstructor
public class ExamRepositoryImpl implements ExamRepositoryCustom {

    private final JPAQueryFactory queryFactory;

    @Override
    public Page<ExamSummaryDto> findPublishedExamSummaries(Pageable pageable) {
        JPAQuery<Long> countQuery = queryFactory.select(exam.count())
                .from(exam)
                .where(exam.status.eq(ExamStatus.PUBLISHED));

        List<Long> examIds = getPagedPublishedExamIds(pageable);
        
        if(examIds.isEmpty) {
            return PageableExecutionUtils.getPage(List.of(), pageable, countQuery::fetchOne);
        }

        List<ExamSummaryDto> content = queryFactory
                .select(new QExamSummaryDto(
                        exam.id,
                        exam.title.value,
                        exam.description.value,
                        exam.status,
                        AUTHOR_PROJECTION,
                        question.count(),
                        exam.createdAt,
                        exam.updatedAt
                ))
                .from(exam)
                .leftJoin(member).on(exam.memberId.eq(member.id))
                .leftJoin(exam.questionGroup.questions, question)
                .where(exam.id.in(examIds))
                .groupBy(
                        exam.id,
                        exam.title,
                        exam.description,
                        exam.status,
                        member.id,
                        member.name,
                        member.avatarUrl,
                        exam.createdAt,
                        exam.updatedAt
                )
                .orderBy(exam.updatedAt.desc())
                .fetch();

        return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
    }

    private List<Long> getPagedPublishedExamIds(Pageable pageable) {
        return queryFactory.select(exam.id)
                .from(exam)
                .where(exam.status.eq(ExamStatus.PUBLISHED))
                .orderBy(exam.updatedAt.desc())
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();
    }
}

커버링 인덱스를 적용시키기 위해, 조건에 맞게 인덱스를 생성해보겠습니다.

CREATE INDEX ix_exam_status_updated_at
    ON exam (status, updated_at DESC, id);

MySQL의 기본 키는 클러스터링 인덱스이기 때문에 (status, updated_at DESC)로 인덱스를 생성하면 (status, updated_at DESC, id)로 인덱스를 생성하는 것과 동일하다.

하지만, PostgreSQL은 모든 키가 보조 키이기 때문에 (status, updated_at DESC, id)로 id를 추가해주어야 한다.

 

PostgreSQL과 MySQL의 실행 계획을 확인하면, 모두 커버링 인덱스가 사용되었음을 알려줍니다.

참고로 PostgreSQL에서 Index Only Scan이 커버링 인덱스입니다.

postgresql의 실행 계획 - Index Only Scan (Covering Index)

mysql의 실행계획 - Covering Index

성능 비교

성능 테스트를 위해서 exam을 1000만개 정도 추가하고 기존의 방식과 개선된 방식을 비교해보았습니다.

테스트한 코드는 다음과 같습니다.

@SpringBootTest
class ExamRepositoryImplTest {

    @Autowired
    private ExamRepository examRepository;

    @Test
    void testFindPublishedExamSummaries() {
        Pageable pageable = PageRequest.of(10, 10);
        Page<ExamSummaryDto> publishedExamSummaries = examRepository.findPublishedExamSummaries(pageable);
        
        assertThat(publishedExamSummaries.getContent()).hasSize(10);
    }
}

기존의 방식은 1m 2s이 걸렸고, 개선된 방식은 6s 230ms가 걸렸습니다. 기존 방식에 비해 시간이 많이 개선되었음을 알 수 있습니다. 하지만 다른 부분에 대해서 인덱스를 걸지 않기도 하고, GROUP BY를 통해 문제 수를 구하는 부분이 성능적으로 문제가 있기 때문에 6초라는 느린 조회 속도가 나온 것 같습니다. 추후에 이 부분을 개선하는 글을 작성해보겠습니다.

 

봐주셔서 감사합니다! :D

참고

• https://jojoldu.tistory.com/529
• https://jojoldu.tistory.com/481