커서 기반 페이징 vs OFFSET, 느려지는 이유

작성자:

글쓴이: JAVA/Spring Boot 백엔드 개발 10년차. SI로 시작해 현재는 자사 서비스 백엔드를 맡고 있다.

2022년 12월, 판매자센터의 주문 내역 화면이 느리다는 문의가 한 주에 열한 건 들어왔다. 내용은 거의 같았다. 1페이지는 바로 뜨는데 마지막 페이지를 누르면 한참 돌다가 504가 난다는 것이었다. 같은 주에 정산팀은 “주문 내역 엑셀 받는 데 40분 걸린다”는 메일을 따로 보냈다. 둘 다 원인이 같았다는 건 일주일 뒤에야 알았다.

이 글은 OFFSET과 커서 페이징의 차이를 설명하는 글이 아니다. 3,800만 행짜리 주문 테이블에서 OFFSET을 걷어 내면서 무엇을 먼저 시도했고, 어디서 데이터를 빠뜨렸고, 결국 화면마다 다른 방식을 고른 이유를 순서대로 적은 기록이다. 중간에 한 번은 고친 줄 알았는데 정산 건수가 212건 비는 사고를 냈다.

마지막 페이지가 14초 걸리던 주문 내역

주문 내역 API는 MyBatis 매퍼 하나였다. 판매자 ID로 거르고 주문일 역순으로 정렬해 20건씩 끊는, 어디에나 있는 쿼리였다. 인덱스도 (seller_id, created_at)로 제대로 걸려 있었다.

<select id="findOrders" resultType="OrderRow">
  SELECT id, order_no, buyer_name, amount, status, created_at
  FROM orders
  WHERE seller_id = #{sellerId}
  ORDER BY created_at DESC
  LIMIT #{size} OFFSET #{offset}
</select>

대부분의 판매자는 주문이 수천 건이라 아무 문제가 없었다. 문의를 넣은 곳은 주문이 92만 건 쌓인 대형 판매자 세 곳이었다. 그쪽 담당자는 오래된 주문을 찾을 때 마지막 페이지부터 거꾸로 넘기는 습관이 있었다. 마지막 페이지는 OFFSET 919980이었고, 슬로우 쿼리 로그에 찍힌 시간은 14.2초였다.

엑셀 다운로드는 더 나빴다. 배치가 같은 쿼리를 1,000건 단위로 OFFSET만 늘려 가며 돌렸다. 첫 요청은 30밀리초였는데 뒤로 갈수록 느려져서, 마지막 몇십 번은 요청 하나에 10초를 넘겼다. 920번 반복하는 동안 DB는 앞쪽 행을 수억 번 읽고 버렸다. 그 40분 동안 같은 DB를 쓰는 주문 API 응답도 함께 느려졌다.

EXPLAIN은 멀쩡해 보였다

처음엔 인덱스를 의심했다. 그런데 EXPLAIN을 찍어 보면 인덱스를 정상적으로 탔다. type은 ref, 정렬도 인덱스 순서로 해결돼서 filesort가 없었다. 실행계획만 보면 고칠 곳이 없는 쿼리였다.

함정은 OFFSET이 동작하는 방식에 있었다. DB는 OFFSET 919980을 “거기로 바로 가라”로 이해하지 않는다. 인덱스를 처음부터 따라가며 91만 9,980행을 실제로 읽고, 행마다 테이블 본문까지 찾아가 컬럼을 꺼낸 뒤 버리고, 그다음 20건을 돌려준다. 인덱스는 순서대로 읽게 해 줄 뿐 건너뛰게 해 주지는 않는다. 그래서 1페이지는 20행을 읽고, 마지막 페이지는 92만 행을 읽는다. 걸리는 시간이 페이지 번호에 그대로 비례한다.

OFFSET 페이징과 커서 페이징의 동작 비교 — OFFSET 990000은 앞의 99만 행을 전부 읽고 버리지만, 커서 방식은 인덱스로 해당 위치까지 곧바로 seek해 읽는 양이 일정하다
OFFSET은 앞 행을 읽고 버리지만, 커서는 곧바로 seek 한다

같은 해 여름에 클라우드 DB 지연을 석 달 동안 잡으면서 인덱스와 실행계획은 꽤 들여다봤다고 생각했다. 그 과정은 MySQL 쿼리 지연 해결: 3개월 클라우드 DB 분투기에 적었다. 그런데 이 문제는 인덱스를 잘 거는 것과는 다른 종류였다. 읽는 방법 자체가 느린 것이라 인덱스를 아무리 바꿔도 소용이 없었다.

첫 번째 시도: 지연 조인으로 14초를 2초로

쿼리 모양을 최대한 유지하면서 고치는 방법부터 찾았다. 많이 알려진 방법이 지연 조인(deferred join)이다. OFFSET으로 건너뛰는 일은 인덱스만 읽어도 되는 서브쿼리에서 끝내고, 테이블 본문은 마지막 20건만 조회한다.

SELECT o.id, o.order_no, o.buyer_name, o.amount, o.status, o.created_at
FROM orders o
JOIN (
    SELECT id FROM orders
    WHERE seller_id = #{sellerId}
    ORDER BY created_at DESC
    LIMIT #{size} OFFSET #{offset}
) t ON t.id = o.id
ORDER BY o.created_at DESC;

InnoDB의 보조 인덱스에는 PK가 함께 들어 있어서 서브쿼리는 테이블 본문을 한 번도 건드리지 않는다. 마지막 페이지가 14.2초에서 2.1초로 줄었다. 일단 배포했고 판매자 문의도 멈췄다.

하지만 그건 상수를 줄인 것이지 구조를 바꾼 게 아니었다. 92만 행을 읽는 건 그대로이고 한 행을 읽는 비용만 싸졌을 뿐이다. 그 판매자의 주문이 늘면 다시 느려진다. 엑셀 배치는 40분에서 11분이 됐지만, 여전히 끝으로 갈수록 느려지는 모양이었다. 같은 시기에 “100페이지까지만 보여 주자”는 안도 냈다가 기획에서 바로 반려됐다. 판매자가 1년 전 주문을 찾아야 하는 경우가 실제로 있었기 때문이다.

두 번째 시도: 커서로 바꿨더니 212건이 사라졌다

엑셀 배치부터 커서 방식으로 바꿨다. 배치는 페이지 번호가 필요 없고 처음부터 끝까지 순서대로 읽기만 하면 되니 가장 쉬운 대상이었다. 직전 묶음의 마지막 주문일을 기억했다가, 다음 묶음은 그보다 이전 주문만 읽게 했다.

-- 처음 배포한 버전 (버그 있음)
SELECT id, order_no, buyer_name, amount, status, created_at
FROM orders
WHERE seller_id = #{sellerId}
  AND created_at < #{lastCreatedAt}
ORDER BY created_at DESC
LIMIT 1000;

결과는 놀라웠다. 묶음마다 20~40밀리초로 일정했고, 11분 걸리던 엑셀이 52초에 끝났다. DB 부하 그래프에서도 그 시간대의 봉우리가 사라졌다. 그렇게 두 주가 지났다.

그다음 달 정산 대사에서 문제가 터졌다. 한 판매자의 엑셀 행 수가 정산 시스템 건수보다 212건 적었다. 빠진 주문을 뽑아 보니 전부 같은 초에 들어온 주문들이었다. 그 판매자는 B2B 거래처 주문을 매일 밤 파일로 일괄 등록했고, 수백 건이 created_at까지 똑같았다.

원인은 단순했다. 1,000건째 묶음 경계가 같은 초의 주문들 한가운데에서 끊기면, 다음 쿼리의 created_at < 마지막값 조건이 그 초에 남은 주문을 통째로 건너뛴다. 커서 값이 행 하나를 정확히 가리키지 못한 것이다. OFFSET 방식에서는 겪지 않던 문제라 테스트 데이터에도 같은 시각의 주문이 없었다.

정렬 키를 (created_at, id)로 바꿔 커서가 항상 유일하게 만들었다. 처음에는 행 생성자 비교 (created_at, id) < (?, ?)로 썼는데, 우리가 쓰던 MySQL 5.7에서 이 형태는 실행계획이 기대처럼 범위 스캔으로 나오지 않았다. 그래서 조건을 풀어서 썼다.

<select id="findOrdersAfter" resultType="OrderRow">
  SELECT id, order_no, buyer_name, amount, status, created_at
  FROM orders
  WHERE seller_id = #{sellerId}
  <if test="cursor != null">
    AND (created_at &lt; #{cursor.createdAt}
         OR (created_at = #{cursor.createdAt} AND id &lt; #{cursor.id}))
  </if>
  ORDER BY created_at DESC, id DESC
  LIMIT #{size}
</select>

인덱스는 따로 손대지 않았다. InnoDB 보조 인덱스 (seller_id, created_at)의 끝에는 PK인 id가 붙어 있어서 ORDER BY created_at DESC, id DESC를 인덱스 순서 그대로 읽는다. 대신 이 사실에 기대고 있다는 걸 매퍼 주석에 적어 두었다. 나중에 누가 PK를 바꾸면 조용히 느려질 수 있기 때문이다. 수정 뒤에는 같은 시각 주문 5,000건을 넣은 테스트를 추가했고, 빠졌던 212건은 정산팀에 별도 파일로 보냈다.

화면은 결국 둘 다 쓰기로 했다

배치가 안정되자 주문 내역 화면도 커서로 바꾸자는 이야기가 나왔다. 여기서 기획과 두 번 회의를 했다. 커서 방식은 “다음”과 “이전”만 된다. 7페이지에서 50페이지로 바로 갈 수 없고, 전체 몇 페이지인지도 보여 줄 수 없다. 판매자센터 화면 아래의 1 2 3 … 46,000 페이지 번호를 없애야 한다는 뜻이었다.

결론은 화면마다 다르게 가는 것이었다. 실제 사용 로그를 열어 보니 판매자의 97%는 앞쪽 5페이지 안에서 원하는 주문을 찾았다. 마지막 페이지부터 넘기던 사람들은 오래된 주문을 찾으려던 것이었고, 그건 기간 필터가 더 맞는 해법이었다.

  • 판매자센터 주문 목록: 기본 조회 기간을 최근 3개월로 두고 페이지 번호(OFFSET + 지연 조인)를 유지했다. 대신 최대 50페이지까지만 이동할 수 있고, 그보다 오래된 주문은 기간을 바꿔 찾게 했다.
  • 앱의 주문·알림 피드: 무한 스크롤이라 처음부터 커서 방식을 썼다. 피드는 새 주문이 계속 쌓여서 OFFSET이었다면 스크롤하는 동안 같은 주문이 두 번 보였을 것이다.
  • 엑셀 다운로드와 정산 배치: 커서 방식만 쓴다. 전체 건수가 맞는지 대사로 확인하는 테스트를 같이 둔다.

전체 건수 표시도 손봤다. 페이지 번호를 그리려고 매번 돌던 COUNT(*)가 대형 판매자 기준 1.8초였다. 3개월 필터를 건 뒤로는 0.2초 안쪽이 됐고, 1만 건이 넘으면 “10,000건 이상”으로만 표시하게 했다. 목록과 건수 쿼리를 함께 관리한 방식은 MyBatis WHERE·SELECT 공통화 실전 패턴에 정리했다.

커서 값을 그대로 내보냈다가 생긴 일

앱 피드 API의 첫 버전은 다음 요청용 커서로 ?lastCreatedAt=...&lastId=...를 그대로 받았다. 일주일 만에 앱 개발자가 정렬을 결제금액순으로 바꾼 화면에서 주문일 커서를 그대로 넘기는 버그를 만들었다. 서버는 아무 오류 없이 엉뚱한 목록을 돌려줬다.

그 뒤로 커서는 서버만 해석할 수 있는 토큰으로 바꿨다. 정렬 기준과 마지막 행의 키를 JSON으로 묶어 Base64로 인코딩하고, 서명을 붙여 위조를 막았다. 토큰 안의 정렬 기준이 요청한 정렬과 다르면 400을 돌려준다. 금액순처럼 값이 자주 겹치는 정렬도 같은 원칙으로 (amount, id)를 커서로 썼다.

public record OrderCursor(String sort, LocalDateTime createdAt, long amount, long id) {

    public String encode(CursorSigner signer) {
        String json = Json.write(this);
        return signer.sign(Base64.getUrlEncoder().withoutPadding()
                .encodeToString(json.getBytes(StandardCharsets.UTF_8)));
    }

    public static OrderCursor decode(String token, String requestedSort, CursorSigner signer) {
        String body = signer.verify(token);   // 서명 불일치면 InvalidCursorException
        OrderCursor c = Json.read(Base64.getUrlDecoder().decode(body), OrderCursor.class);
        if (!c.sort().equals(requestedSort)) {
            throw new InvalidCursorException("정렬 기준이 바뀌면 처음부터 다시 조회해야 합니다");
        }
        return c;
    }
}

조건을 동적으로 조립하는 부분은 나중에 JPA로 옮긴 서비스에서 QueryDSL로 다시 만들었다. 타입 안전하게 커서 조건을 붙이는 방식은 JPA N+1 해결, Fetch Join·EntityGraph·QueryDSL 비교에서 다룬 QueryDSL 설정을 그대로 썼다.

바꾸고 나서 숫자

항목처음지연 조인 후최종
주문 목록 마지막 페이지14.2초2.1초최대 50페이지 제한, 0.3초 이하
앱 주문 피드 (스크롤 위치 무관)––20~40ms 일정
대형 판매자 엑셀 다운로드40분11분52초
COUNT(*) (대형 판매자)1.8초1.8초0.2초 이하 (3개월 필터)
누락·중복스크롤 중 중복 발생동일0건 (복합 커서 + 대사 테스트)

가장 효과가 컸던 건 커서 자체보다 “누가 정말로 마지막 페이지가 필요한가”를 로그로 확인한 일이었다. 커서 방식이 빠르다는 건 처음부터 알았다. 알기 어려웠던 건 어느 화면에서 페이지 번호를 포기해도 되는지였다.

다시 한다면 처음부터 할 것

  • OFFSET은 데이터가 적을 때만 공짜다. 목록 API를 만들 때 “이 테이블이 100배가 되면 마지막 페이지는 몇 행을 읽는가”를 리뷰에서 먼저 묻는다.
  • 커서 정렬 키는 처음부터 유일하게 만든다. 주문일처럼 겹칠 수 있는 값 뒤에는 항상 PK를 붙인다. 같은 시각 데이터를 일부러 넣은 테스트가 없으면 이 버그는 운영에서 처음 보게 된다.
  • 배치와 내보내기는 무조건 커서로 읽는다. 사람이 보는 화면이 아니라 페이지 번호가 필요 없고, OFFSET 반복의 비용은 끝으로 갈수록 커진다.
  • 커서는 불투명한 토큰으로 내보낸다. 정렬 기준을 토큰 안에 넣고 서명한다. 클라이언트가 커서 값을 조립하게 두면 언젠가 잘못 조립한다.
  • 페이지 번호를 없애기 전에 사용 로그부터 본다. 우리는 기간 필터 하나로 마지막 페이지 요구를 대부분 없앴다.

지금도 새 목록 API 리뷰에서 가장 먼저 보는 줄은 LIMIT ... OFFSET이다. OFFSET이 잘못된 게 아니다. 그 줄이 2년 뒤 몇 행을 읽게 될지 아무도 계산하지 않은 채 배포되는 게 문제였다.

참고: MySQL 공식 문서 — LIMIT Query Optimization

“커서 기반 페이징 vs OFFSET, 느려지는 이유”에 대한 1개의 생각

  1. 핑백: Spring Batch 312만 건 소멸 배치, 절반만 처리되고 끝난 이유 - Card News Note

댓글 남기기