Cursor vs Offset Pagination의 성능 비교 및 최적화

개요

• SELECT 문으로 다수의 레코드를 가져올 때, 한 화면에 많은 양의 레코드를 한번에 보여줄 수 없을 때가 있습니다. 이 때 데이터 전체가 아닌 일부만 가져오게 되는데, 크게 페이지네이션과 무한스크롤 방식이 있습니다.

💡 페이지네이션(Pagination) - Offset Pagination
페이지네이션은 디지털 콘텐츠를 웹사이트의 또 다른 페이지들로 분리하는 방법입니다. 사용자는 페이지 하단에 있는 숫자 형식의 링크를 클릭하여 페이지들을 탐색할 수 있습니다. 페이지네이션된 콘텐츠 일반적으로 몇 가지 공통된 주제 혹은 목적들을 지니고 있습니다.
무한 스크롤(Infinite scroll) - Cursor Pagination
무한 스크롤은 사용자가 페이지 하단에 도달했을 때, 콘텐츠가 계속 로드되는 사용자 경험(UX, User Experience) 방식입니다. 이는 마치 끝이 없는 단일 페이지에서 끝없는 정보의 흐름을 경험하게 만듭니다.

 

페이지네이션 VS 무한스크롤 : 자사에 적합한 UX 고르는 법 /2 - HelloDigital

• 페이지네이션은 사용자가 특정 정보를 검색할 때 유용하고, 무한 스크롤은 사용자가 목적없이 특정 컨텐츠를 조회할 때 유용한 기능입니다.
• 이번 포스팅에서는 페이지네이션과 무한 스크롤의 SQL 관점에서의 성능 비교를 해볼 것입니다.

OFFSET 쿼리

• OFFSET 페이지네이션을 사용할 시 쿼리는 크게 두가지 쿼리를 거쳐 수행됩니다.
  1. 실제 데이터를 가져오는 SELECT 쿼리, 특정 갯수를 제한해 갖고옵니다.
  2. 조회된 데이터의 총 갯수를 구하는 SELECT Count 쿼리. 몇 페이지까지 있는지 계산하기 위해 필요한 쿼리입니다.
• 아래는 OFFSET 페이지네이션을 구현한 실제 예시입니다

@Override
public List<ProductDto.Preview> search(ProductDto.SearchCondition searchCondition, Pageable pageable) {

    JPAQuery<ProductDto.Preview> query = jpaQueryFactory.select(Projections.constructor(
            ProductDto.Preview.class,
                    product.uid,
                    product.name,
                    product.price,
                    product.thumbnailImageUrl))
            .from(product);

    setUpWherePredicationQueries(query, searchCondition);

    setUpPagenationQuries(query, pageable, searchCondition.getSortOption());

    return query.fetch();

}

@Override
public Long getCount(ProductDto.SearchCondition searchCondition) {
    JPAQuery<Long> query = jpaQueryFactory.select(product.count())
            .from(product);

    setUpWherePredicationQueries(query, searchCondition);

    return query.fetchOne();
}

private void setUpPagenationQuries(JPAQuery query, Pageable pageable, SortOption sortOption) {
    query.offset(pageable.getOffset());
    query.limit(pageable.getPageSize());

    switch (sortOption){
        case BY_DATE -> query.orderBy(product.createdAt.desc());
        case BY_NAME -> query.orderBy(product.name.asc());
        case BY_PRICE -> query.orderBy(product.price.asc());
    }
}

 

• 이 코드를 기반으로, 실제 DB에 날아가는 SELECT Query의 예시는 다음과 같습니다.

select 
	p1_0.product_uid,
	p1_0.name,
	p1_0.price,
	p1_0.thumbnail_image_url 
from products p1_0
order by p1_0.created_at desc 
limit x, y;

 

• 여기서 MYSQL의 LIMIT 절에는 단점이 하나 있는데, 바로 검색된 데이터를 0번~X+Y번째 데이터를 모두 읽어 들여서 메모리에 올린 후, 처음 X개의 데이터는 버리고 X+1~X+Y 번째의 데이터만 갖고 온다는 것입니다. 이는 X가 클수록 가져와야하는 갯수가 많아지기 때문에 Disk I/O가 많이 발생하게 될 것임을 의미합니다.

OFFSET 쿼리 시간 측정

• 아래의 조건에서 테스트를 진행했습니다.
• 검색 조건 X, offset 100개를 기준으로 1페이지 ~ 10페이지 순차 조회
• 인덱스 미적용
• Count 쿼리는 제외

page	SQL Time
1	18,152 ms
2	19,579 ms
3	21,035 ms
4	20,998 ms
5	21,983 ms
6	22,861 ms
7	23,456 ms
8	25,000 ms
9	25,632 ms
10	26,086 ms
합계	224,782 ms

 

• 위 결과에 따르면 페이지가 증가함에 따라 소요되는 SQL 시간도 선형적으로 증가하는 추세임을 확인할 수 있습니다.

OFFSET 최적화 - 커버링 인덱스

• 페이지네이션 기능을 사용할 경우 적용할 수 있는 첫번째 방법은 커버링 인덱스입니다. 이전에 언급한 LIMIT절의 단점은 OFFSET에 의존해 필요하지 않은 많은 데이터를 디스크에서 읽어온다는 건데, 커버링 인덱스를 사용하면 가져오는 데이터의 크기가 달라지기 때문에 읽는 속도가 향상됩니다.

page	SQL Time
1	7,252 ms
2	8,113 ms
3	8,457 ms
4	9,048 ms
5	9,663 ms
6	10,507 ms
7	12,219 ms
8	15,012 ms
9	12,666 ms
10	13,983 ms
합계	106,920 ms

 

CURSOR 쿼리

• CURSOR 페이지네이션 방식을 구현한 코드는 다음과 같습니다.

@Override
public List<ProductDto.Preview> search(ProductDto.SearchCondition searchCondition, Optional<String> cursorUid, int limit) {
    JPAQuery<ProductDto.Preview> query = jpaQueryFactory.select(Projections.constructor(
            ProductDto.Preview.class,
            product.uid,
            product.name,
            product.price,
            product.thumbnailImageUrl))
            .from(product);

    setUpWherePredicationQueries(query, searchCondition);

    setUpPaginationQueries(query, cursorUid, limit);

    return query.fetch();

}

private void setUpPaginationQueries(JPAQuery query, Optional<String> cursorOptional, int limit, SortOption sortOption) {
    switch (sortOption){
        case BY_DATE:
            setUpDatePagination(query, cursorOptional);
            break;
        case BY_NAME:
            setUpNamePagination(query, cursorOptional);
            break;
        case BY_PRICE:
            setUpPricePagination(query, cursorOptional);
            break;
    }

    query.limit(limit);
    query.offset(0L);
}

private void setUpDatePagination(JPAQuery query, Optional<String> cursorOptional) {
    query.orderBy(product.createdAt.desc()).orderBy(product.uid.desc());

    if(cursorOptional.isEmpty()){
        return;
    }

    JPAQuery<LocalDateTime> subQuery = new JPAQuery<>().select(product.createdAt)
            .from(product)
            .where(product.uid.eq(cursorOptional.get()));

    query
            .where(product.createdAt.before(subQuery))
            .where(product.uid.lt(cursorOptional.get()));

}

• 해당 코드는 위의 OFFSET Pagination 방식에서 페이지네이션 설정 방식만 변경된 것으로, OFFSET이 아닌 WHERE productUid > ??? 검색절이 추가되었습니다.
• 추가적으로 몇페이지까지 있는지 계산할 필요가 없기 때문에 Count 쿼리는 필요하지 않습니다.
• 추가적으로 Cursor Pagination을 정상적으로 적용하기 위해선 index sort를 꼭 적용해야 합니다. 그렇지 않으면 filesort 방식으로 정렬하기 때문에 모든 행을 가져와야 해서 성능 개선이 정상적으로 이루어 지지 않습니다!
• 해당 코드에 대한 SQL문은 다음과 같습니다.

SELECT p1_0.product_uid, p1_0.name, p1_0.price, p1_0.thumbnail_image_url
FROM products AS p1_0
WHERE p1_0.created_at < (SELECT created_at FROM product WHERE uid = :cursorUid)
LIMIT ?
ORDER BY created_at DESC, uid DESC;

CURSOR 방식 시간 측정

• 아래의 조건에서 테스트를 진행했습니다.
• 검색 조건 X, 100개씩 데이터를 조회
• 검색조건 cursorUid에 100번째 이름 지정
• 인덱스 적용

page	SQL Time
1	105ms
2	24ms
3	5ms
4	6ms
5	4ms
6	5ms
7	4ms
8	5ms
9	4ms
10	5ms
합계	167ms

 

• 일단 인덱스를 적용하지 않은 OFFSET 페이지네이션과 비교했을 때, 순차적으로 100개씩 10번 데이터를 읽어들이는 부분에서 시간은 유사하였습니다.
• 또 페이지가 증가함에 따라 시간이 선형적으로 증가하는 문제 또한 발생하지 않았습니다.

참고 자료

https://jeong-pro.tistory.com/244

https://taegyunwoo.github.io/tech/Tech_DBPagination