No Offset 으로 페이징 성능 개선하기

문제상황

jpashop 서비스에는 상품목록을 조회할수있는 api를 제공하고 있습니다.

데이터가 백만건이 넘는상황에서도 원하는 성능으로 잘 작동하는지 보겠습니다.

10만번째 페이지를 요청시, 실행시간이 약 600ms가 소요되며, 매우 느린상황입니다.

was의 로그에 찍힌 실행시간

db에서 쿼리 실행시 실행시간

원인분석

쿼리 dsl 코드, 실행되는 sql 쿼리는 다음과 같습니다.

public List<Item> findAllByQueryDsl(int pageNum) {
    return query
            .select(item)
            .from(item)
            .orderBy(item.id.desc())
            .offset(pageNum * 10)
            .limit(10)
            .fetch();
}

SELECT item_id, name, price, stock_quantity, artist, etc, author, isbn, actor, director, dtype
FROM item
ORDER BY item_id DESC
LIMIT 1000000, 10;

참고로 limit의 첫번째 인자가 offset을 의미합니다.

해당 쿼리가 느린 이유는 offset의 작동방식 때문입니다. mysql은 해당 쿼리를 수행하기 위해 백만 + 10개의 row를 읽은후,  필요없는 백만개의 행을 버리고, 남은 10개의 행을 반환합니다.

'-> Limit/Offset: 10/1000000 row(s)  (cost=94968 rows=0) 
(actual time=605..605 rows=8 loops=1)    -> 
Index scan on item using PRIMARY (reverse)  
(cost=94968 rows=941268) (actual time=0.0494..580 rows=1e+6 loops=1)'

좀더 자세히 실행계획을 보겠습니다. order by를 위해 역순으로 인덱스를 탐색하고, 버퍼풀에 가격, 재고같은 필드를 포함한 100만 + 10개의 데이터를 올리고, 100만개를 버린후 남은 행 10개를 반환합니다. 이때, 불필요한 디스크 I/O가 일어나게 됩니다.

그림으로 보겠습니다.

힙은 실제로는 무작위 입니다.

 

해결방법1

버퍼풀에 데이터를 올릴때, id만 올리는 방법으로 개선해 보겠습니다. 어차피 버릴 필드들은 올릴 필요가 없습니다.

이후 in절을 사용하여 필요한 필드를 가져오는 방법으로 개선해 보겠습니다. 쿼리dsl 코드로 보겠습니다.

public List<Item> findAllByQueryDslWithIdsAndInClause(int pageNum) {
    // 1. ID 목록만 먼저 조회
    List<Long> itemIds = query
            .select(item.id)
            .from(item)
            .orderBy(item.id.desc())
            .offset(pageNum * 10)
            .limit(10)
            .fetch();

    // 2. ID 목록이 비어있으면 빈 리스트 반환
    if (itemIds.isEmpty()) {
        return Collections.emptyList();
    }

    // 3. IN절을 사용하여 실제 데이터를 조회
    return query
            .select(item)
            .from(item)
            .where(item.id.in(itemIds))
            .orderBy(item.id.desc())
            .fetch();
}

그림으로 표현하면 다음과 같습니다.

실행시간을 보겠습니다. 약 230ms로 감소했습니다. 전체 실행시간은 감소했으나, db에 보내지는 쿼리 횟수는 1회 증가했습니다.

was에서 시간측정

db에서 시간측정

발생되는 쿼리1

발생되는 쿼리2

 

해결방법2

마지막 해결방법인 no-offset 방법에대해 보겠습니다. 아래의 그림처럼 where절을 사용하여 페이징을 하면 offset으로 인해 버려지는 row가 없어지게 됩니다. 바로 구현해보겠습니다.

하지만, no-offset기반 페이징은 클라이언트로부터 페이징이 시작될 itemId를 받아와야한다는 단점이 있습니다.

public List<Item> findAllByQueryDsl(Long itemId, int pageSize) {
    return query
            .select(item)
            .from(item)
            .where(ltBookId(itemId))
            .orderBy(item.id.desc())
            .limit(pageSize)
            .fetch();
}

private BooleanExpression ltBookId(Long itemId) {
    if (itemId == null) {
        return null;
    }
    return item.id.lt(itemId);
}

SELECT item_id, name, price, stock_quantity, artist, etc, author, isbn, actor, director, dtype
from item
where item_id < 10
order by item_id desc;

실행시간은 압도적으로 빠릅니다. pk기반 인덱스이고 딱 필요한 10개행만 탐색하므로 3ms 이내로 응답을 받아 올수 있었습니다.

was에서 실행시간 측정

 

정리

테스트 DB 로컬 VM (8GB RAM, 4Core)

테스트 테이블

• 1백만 row
• 4개 컬럼

1,2,3... 과 같이 전통적인 페이징이 요구되는 상황이 아니라면, no-offset기반 페이징을 선택할것 같습니다.

전통적인 페이징이 반드시 필요하다면, id기반 offset방법을 선택할것 같습니다. 추가적인 성능개선이 필요하다면 커버링 인덱스 도입을 검토 할 수 있을것 같습니다.

감사합니다.

 

 

레퍼런스

 

https://use-the-index-luke.com/sql/partial-results/fetch-next-page

OFFSET is bad for skipping previous rows

https://stackoverflow.com/questions/1243952/how-can-i-speed-up-a-mysql-query-with-a-large-offset-in-the-limit-clause?utm_source=chatgpt.com

How can I speed up a MySQL query with a large offset in the LIMIT clause?