[DB] Pagination 실행계획 분석 (Offset Pagination vs Cursor Pagination)

토이프로젝트는 항상 신규 서비스를 만들다 보니, 메인페이지의 피드에 대한 구현 요구사항이 항상 있는 것 같습니다.

덕분에 1년동안 4개 서비스의 피드를 만들었네요.

아래 게시글은 제가 1년 조금 전에 처음으로 개발했던 피드에 대한 게시글입니다.

[DB] QueryDSL을 활용하여 SNS 피드 만들기(1) - 동적 쿼리로 Pagination 피드를 만들어보자.

[DB] QueryDSL을 활용하여 SNS 피드 만들기(2) - 더 정확한 페이징 쿼리 만들기 offset based, cursor based

1년이 지난 지금은 조금 더 다양한 관점에서의 분석이 가능할 것 같다는 생각이 들어서, 다음과 같은 내용을 중점으로 분석해보고자 합니다.

기능상 장단점은 무엇인가? 선택의 기준?
성능상 이점 (실행계획)
유명한 서비스들에서는 어떻게 사용되고 있을까?

MySQL Procedure 활용하여 테스트 환경 구축

DB: MySQL 8

프로시저로 더미데이터를 10만개 생성합니다.

# 유저 3명 생성
INSERT INTO USERS (id, nickname, profileImageUrl)
VALUES
    (1, 'user1', 'https://example.com/profiles/user1.jpg'),
    (2, 'user2', 'https://example.com/profiles/user2.jpg'),
    (3, 'user3', 'https://example.com/profiles/user3.jpg');

# 포스트 1만개 생성
DELIMITER //
CREATE PROCEDURE InsertData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO
            INSERT INTO post (post_id, created_at, updated_at, category, content, image_url, user_id)
            VALUES (i, NULL, NULL, NULL, NULL, NULL, FLOOR(RAND() * 3) + 1);
            SET i = i + 1;
        END WHILE;
END //
DELIMITER ;

CALL InsertData();

auto-increament PK 를 사용하게 되면, B+ Tree 가 다음과 같이 구성되게 됩니다.

클러스터링 인덱스(PK)의 순서가 게시글 업로드 순서를 보장하고, 최신 게시글이 가장 leaf 에 위치하게 됩니다.

Offset Pagination 특징

Offset Pagination은 일반적으로 관계형 데이터베이스에서 제공하는 offset 기능을 활용합니다.

offset은 결과 조회 시, N개의 결과를 건너뛰고 그 이후의 결과를 가져옵니다.

장점

특정 페이지로 건너뛰며 이동할 수 있습니다.

전통적인 게시글 방식에서 limit 만큼 보여주면서 limit 기준으로 page를 표기합니다.

단점

기본적으로 페이지를 알기 위해 앞의 데이터를 모두 탐색해야 합니다. 그래서 인덱스 풀스캔 또는 테이블 풀스캔을 하게 되기 때문에, 결과가 많다면 후반 페이지로 갈수록 조회 성능이 좋지 않습니다.

예시로 구글 또는 네이버같은 검색엔진에서도 이러한 문제들 때문에 표시 가능한 페이지를 제한하고 있습니다.

사실 일반적인 검색엔진에서 유저들에게는 15페이지 이상까지 탐색하지 않기 때문에 사용성에 큰 문제가 발생하지는 않을 것 같습니다.

또한 만약 앞 페이지의 게시글이 새로 추가되거나 삭제된다면, 특정 게시글이 사라지거나 중복된 게시글이 표시될 수 있습니다.

Cursor Pagination 특징

Cursor Pagination은 게시글 id 등의 고유 식별값을 입력으로 받고, 그 이후의 게시글을 불러옵니다.

일반적으로 식별 값은 auto increament 로 구성된 PK 를 사용해야 성능을 기대할 수 있습니다.

마지막 게시글의 id > post.id

장점

입력되는 식별자 값 부터 탐색하면 되기 때문에, 앞부분을 읽지 않아도 되며 offset pagination에 비해 성능상에 이점이 있습니다.

앞 페이지에 게시글이 추가되거나 삭제되어도 게시글 중복과 누락이 발생하지 않습니다.

단점

특정 페이지로 이동하는 기능을 제공할 수 없습니다.

대표적으로 facebook, twitter, instagram 등 소셜네트워크 앱에서 많이 사용됩니다.

다음 컨텐츠를 연속적으로 빠르게 불러와야하는 infinity scrolling 구현에 적합합니다.

실행 계획 살펴보기 - Offset Pagination

실제로는 피드에 정책이 들어가는 경우가 많습니다.

예를들어 유저가 차단하거나 숨긴 게시글은 보이지 않아야 한다는 조건이 있습니다.

그래서 이번 테스트 쿼리에서는 1번 유저가 차단된 유저라고 생각하고 다음 조건을 넣어보겠습니다.

p1_0.user_id != 1

(접기) QueryDSL 코드

@Override
public List<FeedResponseDto> getFeedOffset() {
    return queryFactory
            .select(new QFeedResponseDto(
                    post.id,
                    post.content,
                    post.imageUrl,
                    post.category,
                    post.createdAt,
                    post.updatedAt,
                    user.id,
                    user.nickname,
                    user.profileImageUrl
            ))
            .from(post)
            .join(post.user, user)
            .where(
                    post.user.id.ne(1L) // some condition
            )
            .orderBy(post.id.desc())
            .offset(30000L) // offset pagination
            .limit(10)
            .fetch();
}

# offset

explain SELECT p1_0.post_id,
       p1_0.content,
       p1_0.image_url,
       p1_0.category,
       p1_0.created_at,
       p1_0.updated_at,
       p1_0.user_id,
       u1_0.nickname,
       u1_0.profile_image_url
FROM   post p1_0
           JOIN users u1_0
                ON u1_0.user_id = p1_0.user_id
WHERE  p1_0.user_id != 1
ORDER  BY p1_0.post_id DESC
LIMIT 60000, 10;

먼저 offset 방식입니다.

게시글 번호가 100,000까지 있고, 1번의 게시글(전체 비중의 33.3%)이 조회되지 않는다고 하면, offset 60000은 거의 마지막에 가까운 게시글입니다.

key: PRIMARY , type: index 인 것을 보아 PK를 인덱스 스캔을 했습니다.
그런데 사실 PK가 데이터가 실제 저장된 구조와 같기 때문에, PK의 인덱스 스캔은 사실상 풀테이블 스캔과 동작이 동일합니다.
인덱스(PK)를 역탐색 하기 때문에 Backward index scan이 표기됩니다.

혹시라도 PK가 아니라 일반 인덱스를 역순으로 탐색하기 위해서는, 인덱스 자체를 역순으로 생성해야 인덱스의 도움을 받을 수 있습니다.

실제 동작을 살펴보면..

PK 에 Order By DESC 가 걸려있으니 가장 마지막 PK를 찾아갑니다. (Backward index scan)
위의 그림에서 offset 이 4라고 가정하면, 조건에 맞는 데이터를 4개 만날 때 까지
offset 탐색이 끝나면 버퍼에 결과를 쌓습니다.
limit 을 채울 때까지 탐색하다가, 충족되면 탐색을 끝냅니다.

실행 계획 살펴보기 - Cursor Pagination

# cursor
explain SELECT p1_0.post_id,
       p1_0.content,
       p1_0.image_url,
       p1_0.category,
       p1_0.created_at,
       p1_0.updated_at,
       p1_0.user_id,
       u1_0.nickname,
       u1_0.profile_image_url
FROM   post p1_0
           JOIN users u1_0
                ON u1_0.user_id = p1_0.user_id
WHERE  p1_0.post_id < 1000
  AND p1_0.user_id != 1
ORDER  BY p1_0.post_id DESC
LIMIT  10;

(접기) QueryDSL 코드

@Override
public List<FeedResponseDto> getFeedCursor() {
    return queryFactory
            .select(new QFeedResponseDto(
                    post.id,
                    post.content,
                    post.imageUrl,
                    post.category,
                    post.createdAt,
                    post.updatedAt,
                    user.id,
                    user.nickname,
                    user.profileImageUrl
            ))
            .from(post)
            .join(post.user, user)
            .where(
                    post.id.lt(1000L), // cursor pagination
                    post.user.id.ne(1L) // some condition
            )
            .orderBy(post.id.desc())
            .limit(10)
            .fetch();
}

커서 페이징의 실행 계획도 한 번 살펴보겠습니다.

마찬가지로 마지막 쯤에 있는 게시글을 탐색했습니다.

type: range , row: 999 입니다. 인덱스 레인지 스캔을 통해 탐색 시작 컬럼을 바로 찾아갔습니다.
그 외 특징은 offset과 동일합니다.

인덱스를 통해 탐색을 시작할 위치를 바로 찾아갑니다. (Index Range Scan)
limit 을 채울 때까지 탐색하다가, 충족되면 탐색을 끝냅니다.

위처럼 offset 처럼 앞의 게시글들을 탐색할 필요가 없으니, index 탐색의 뒷쪽에 있는 컨텐츠를 탐색할수록 cursor pagination 의 장점이 부각되는 것 같습니다.

저작자표시 비영리 변경금지

Contents

새소식