커버링 인덱스를 활용하여 페이지네이션 조회 속도 개선하기

기획한 서비스를 어느 정도 개발하고 나서 우리의 작고 소중한 아이에게 수많은 데이터가 쌓였을 때 성능이 어떻게 나올지 궁금해졌다. 

 

우리가 가진 기능 중에서 지속적으로, 다른 서비스에 종속적이지 않게 쌓일 수 있는 데이터는 커피챗 도메인이었다. 또한 커피챗 서비스는 

 

Offset-based Pagination으로 구현되어 있어서 데이터가 많아졌을 때 조회 성능이 느려질 것으로 예측되는 기능이었다. 

Offset-based Pagination

DB의 limit, offset 쿼리를 사용하여 구분하여 ‘페이지’ 단위로 구분하여 요청/응답하게 구현

페이징을 구현하기 위해서는 전체 데이터 개수를 가져와서 전체 페이지를 계산해야하고, 현재 페이지가 첫번째 페이지인지, 마지막 페이지인지도 계산해야하고, 예상치 못한 페이지 범위를 요청받았을 때 예외처리도 해야한다. 이 데이터들을 얻으려면 최소 2번의 API 요청(데이터 요청, 데이터 카운트 콜)을 통해 데이터를 가져와야 한다.

 

만약 LIMIT A OFFSET B라고 한다면, DB 옵티마이저는 A+B만큼의 데이터를 테이블에서 읽어낸 뒤 불필요한 부분은 버리는 식으로 동작한다. DB가 인덱스 탐색을 수행할 때 오프셋 정보를 수직적 탐색에 이용할 수 없기 때문에 이와 같이 동작한다.

따라서 Offset 기반 페이지네이션은 페이지가 뒤로 갈수록 읽어야 할 데이터의 총량이 많아져 성능이 저하되는 특성을 갖는다.

 

 

Test Data 설정하기

언젠가는 우리 서비스에 커피챗 모집 글이 100만건이 넘을거라고 행복한 상상을 하면서.. 소박하게 30만건의 데이터를 대상으로 구현한 페이징 기능을 확인해보았다.

DROP PROCEDURE IF EXISTS insertCoffeeChat;

CREATE PROCEDURE insertCoffeeChat(IN start_id INT, IN count INT)
BEGIN
    DECLARE i INT DEFAULT start_id;

    WHILE i < start_id + count DO
            insert into coffee_chat (is_deleted, created_by, created_date, current_recruit_count, id, meet_date,
                                     total_recruit_count, view_count, content, open_chat_url, title, active_status)
            values (false, 1, now(), 0, i, '2024-08-06 19:30', 0, 0,
                    'Lorem ipsum dolor sit amet, consectetur adielit, sed do eiusmo',
                    'openkakao.dfkjwhf.wdjfhwkj', CONCAT('Coffee Chat Title ', i), 'OPEN');
            SET i = i + 1;
        END WHILE;
END;



CALL insertCoffeeChat(1, 300000); ## 삼십만

 

참고로 30만개 넣는데 걸리는 시간…..

 

findCoffeeChatList | ver1

public Page<CoffeeChatReaderInfo.FindCoffeeChatListResponse> findCoffeeChatList1(
		final Pageable pageable,
        final CoffeeChatCommand.FindCoffeeChatListRequest command) {
        
        List<CoffeeChatReaderInfo.FindCoffeeChatListResponse> content = jpaQueryFactory
            .select(new QCoffeeChatReaderInfo_FindCoffeeChatListResponse(
                coffeeChat.id,
                member.nickname,
                jobCategory.name,
                coffeeChat.title,
                coffeeChat.coffeeChatStatus,
                coffeeChat.meetDate,
                coffeeChat.createdDate,
                coffeeChat.totalRecruitCount,
                coffeeChat.currentRecruitCount,
                coffeeChat.viewCount))
            .from(coffeeChat)
            .join(member)
            .on(coffeeChat.member.eq(member))
            .join(jobCategory)
            .on(member.jobCategory.eq(jobCategory))
            .where(
                excludeDeleteCoffeeChat(),
                coffeeChatTitleContains(command.getKeyword()),
                memberJobCategoryEq(command.getJobCategory())
            )
            .orderBy(
                coffeeChatSort(command.getSort())
            )
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

        Long totalCount = jpaQueryFactory
            .select(coffeeChat.count())
            .from(coffeeChat)
            .join(member)
            .on(coffeeChat.member.eq(member))
            .where(
                excludeDeleteCoffeeChat(),
                coffeeChatTitleContains(command.getKeyword()),
                memberJobCategoryEq(command.getJobCategory())
            )
            .fetchOne();

   return new PageImpl<>(content, pageable, totalCount);
}

오른쪽 위에 3.27s.....

해당 API를 실행해보면 3.27s가 걸린다. 흠...

 

이렇게 만들어진 쿼리의 실행 계획을 확인해보면 다음과 같다. 

테이블의 데이터를 어떻게 찾을지에 대한 정보를 제공하는 type column에 메인이 되는 테이블인 coffeechat 테이블이 ALL 로 설정되고 데이터를 찾는데 사용한 key가 null 인 것을 통해서 테이블을 처음부터 끝까지 읽는 테이블 풀 스캔 방식을 사용할 것이라는 것을 알 수 있었다. 

커피챗 목록 조회 쿼리가 커피챗 테이블을 풀 스캔하고 있어 대규모 데이터셋(300,000건 기준) 에서 현저히 느린 속도를 보이고 있다 

 

findCoffeeChat ver1이 테이블 풀 스캔을 하게 된 원인

테이블 풀 텍스트의 원인

1. LIKE 연산자: 검색 시에 와일드카드 %로 인해 인덱스를 사용하지 않음.
2. 필터 조건: 인덱스가 걸리지 않은 컬럼에 대해 필터링 하는 조건들로 구성되어 있음

성능 개선이 필요하다.

---

결론적으로 커버링 인덱스를 사용하여 페이지네이션 속도를 개선했다. 

우리가 찾아낸 페이지네이션의 성능 개선 방법에는 다음의 세가지 방법이 있었다. 

1. No-offset 페이지네이션으로 변경하기
2. 페이지 건수 고정하기
3. 커버링 인덱스 적용하기

No-Offset 페이지네이션으로 변경

우선 No-Offset 페이지네이션으로 변경하는 방식은 UI 자체가 이미 Offset-based Pagination으로 구성되어 있으며, 자신이 원하는 컨텐츠의 커피챗을 탐색하는 기능의 특성 상 사용자에게 무한스크롤보다는 페이지네이션을 제공하는 것이 적절하다고 판단했다.

 

페이지 건수 고정하기

다음으로 페이지 건수 고정하기는 페이징 기능에서 데이터 조회와 함께 매번 수행되는 count 쿼리를 줄이는 방법이다. count 쿼리는 조건에 따라 조회되는 결과 건수를 pageSize로 나누어 pageNo를 노출하기 위해 필요한데 총 몇 건인지 확인하기 위해 전체를 확인해야 하므로 데이터 조회만큼 오래 걸리기도 한다.

 

페이지 건수를 고정하는 방법에도 두가지가 있다. 

 

1. 검색 버튼 사용 시 페이지 건수 고정하기

대부분의 조회 요청이 검색 버튼 클릭 (즉, 첫 조회)에서 발생하고 페이지 버튼을 통한 조회 요청이 소수일 경우에 고려할만한 방법이다.

즉, 다음 페이지로 이동하기 위해 페이지 버튼을 클릭했을 때만 실제 페이지 count 쿼리를 발생시켜 정확한 페이지수를 사용하고, 대부분의 요청이 발생하는 검색 버튼 클릭 시에는 count 쿼리를 발생시키지 않는 것이다.

 

2. 첫 번째 쿼리의 결과를 Cache하기

조회 요청이 검색 버튼과 페이지 버튼 모두에서 골고루 발생하고 실시간으로 데이터 적재 되지 않고, 마감된 데이터를 사용할 경우에 고려할 만한 방법이다. 
처음 검색시 조회된 count 결과를 응답결과로 내려주어 JS에서 이를 캐싱하고, 매 페이징 버튼마다 count 결과를 함께 내려준다. Repository에서는 요청에 넘어온 항목 중, 캐싱된 count값이 있으면 이를 재사용하고, 없으면 count 쿼리를 수행한다. 

 

하지만 아직 사용자의 경향성을 알 수 없는 상황에서 도입하기에는 섣부른 선택이라고 생각했다.

 

커버링 인덱스 적용하기

커버링 인덱스란 쿼리를 충족시키는데 필요한 모든 데이터를 가지고 있는 인덱스로 select, where, order by, limit, group by 등에서 사용되는 모든 컬럼이 Index 컬럼안에 다 포함되는 것을 말한다. 

다른 두가지 방법이 현재 우리의 상황에 적절하지 않다고 생각했기 때문에 제쳐두고 커버링 인덱스를 도입하기로 했다.

---

커버링 인덱스 도입기

커버링 인덱스 설정하기

 

커피챗 목록 조회 기능은 기본적으로 커피챗 게시글을 최근에 생성된 순서로 정렬하고, 작성자가 삭제한 커피챗은 화면에 보여주지 않습니다.  그리고 추가적으로 제목에 대해서 키워드를 검색하거나, 조회수 순으로 정렬하거나, 커피챗 게시글 작성자의 직군 타입에 따라 필터링할 수 있다. 

그래서 위의 Table 처럼 많은 컬럼을 가지고 있다. 우리가 화면에 보여줘야할 커피챗의 데이터는 커피챗 테이블의 거의 모든 컬럼과, 커피챗을 작성한 유저의 아이디까지인데, 그럼 모두 select에 들어가니 이 많은 컬럼들을 모두 인덱스에 포함해야할까?

 

아마 그렇다면 커피챗을 INSERT, UPDATE, DELETE 할 때마다 인덱스를 생성하고, 정렬하는데 더 많은 리소스가 들 것이다. 그래서 우리는 최소한으로 이 동적 쿼리내에서 항상 사용되는 컬럼만 모아서 커버링 인덱스를 생성하고,

 

사용자가 많아지면, 사용자의 경향성이 나오면ㅎㅎ

조회에 많이 사용되는 컬럼에 대해서 인덱스를 다시 설정하는 것이 맞지 않겠냐는 걸정을 했다. 

 

그래서 기본적인 조회에 사용되는 is_deleted(게시글의 삭제 여부), created_date(생성날짜로 기본 정렬에 쓰임), id(PK) 를 대상으로 커버링 인덱스를 사용했다. 또한 삭제된 게시글은 애초에 화면에 보여줄 필요가 없기 때문에 is_deleted를 선행시켰다. 

 

 

findCoffeeChatList  |  ver2

먼저 커버링 인덱스를 활용해 조회 대상의 인덱스를 조회한다. 

조회된 인덱스 리스트를 기반으로 필요한 컬럼 항목들을 SELECT 한다. 

• 해당 PK로 필요한 컬럼항목들 조회
• 해당 PK로 필요한 컬럼항목들 조회

쿼리에서 오래걸리는 페이징 작업까지는 커버링 인덱스로 빠르게 처리후, 마지막 필요한 컬럼들만 별도로 가져오도록 개선했다.

 

public Page<CoffeeChatReaderInfo.FindCoffeeChatListResponse> findCoffeeChatList3(
		final Pageable pageable,
        final CoffeeChatCommand.FindCoffeeChatListRequest command) {

        List<Long> ids = jpaQueryFactory
            .select(coffeeChat.id)
            .from(coffeeChat)
            .where(
                excludeDeleteCoffeeChat(),
                coffeeChatTitleContains(command.getKeyword()),
                memberJobCategoryEq(command.getJobCategory())
            )
            .orderBy(
                coffeeChatSort(command.getSort())
            )
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

        List<CoffeeChatReaderInfo.FindCoffeeChatListResponse> content = jpaQueryFactory
            .select(new QCoffeeChatReaderInfo_FindCoffeeChatListResponse(
                coffeeChat.id,
                member.nickname,
                jobCategory.name,
                coffeeChat.title,
                coffeeChat.coffeeChatStatus,
                coffeeChat.meetDate,
                coffeeChat.createdDate,
                coffeeChat.totalRecruitCount,
                coffeeChat.currentRecruitCount,
                coffeeChat.viewCount))
            .from(coffeeChat)
            .join(member)
            .on(coffeeChat.member.eq(member))
            .join(jobCategory)
            .on(member.jobCategory.eq(jobCategory))
            .where(coffeeChat.id.in(ids))
            .orderBy(
                coffeeChatSort(command.getSort())
            )
            .fetch();

        Long totalCount = jpaQueryFactory
            .select(coffeeChat.count())
            .from(coffeeChat)
            .where(
                excludeDeleteCoffeeChat(),
                coffeeChatTitleContains(command.getKeyword()),
                memberJobCategoryEq(command.getJobCategory())
            )
            .fetchOne();

        return new PageImpl<>(content, pageable, totalCount);
    }

 

그리고 이렇게 쿼리, API를 실행해보았을 때 확연히 빨라진 속도를 확인할 수 있었다. 

 

여전히 남아있는 개선점

키워드나 jobcategory가 선택되었을 때 커버링 인덱스를 사용하지 못하기 때문에 이 상황에 대해서 개선이 필요하다.

커버링 인덱스의 단점

• 여기서 데이터가 더 늘어나면 여전히 페이지 번호가 뒤로 갈수록 느려진다는 문제가 존재하며 인덱스로 인한 메모리도 신경써야한다.

 

여기서 더 최적화를 하고자 한다면, 미뤄뒀던 사용자 경향성에 따라 count 를 고정하거나, 커버링 인덱스의 종류를 추가할 수 있겠다.