[DB] 깊은 페이징 성능 문제: OFFSET이 느려지는 이유와 커서 기반 페이징(no-offset)

[DB] 깊은 페이징 성능 문제: OFFSET이 느려지는 이유와 커서 기반 페이징(no-offset)

목록 API가 1페이지에선 1ms인데 뒤로 갈수록 느려지다, 관리자가 "전체 내보내기"로 수만 페이지를 훑는 순간 DB가 비명을 질러요. LIMIT 20 OFFSET 100000의 구조적 비용이에요. OFFSET은 "건너뛰기"처럼 보이지만 실제로는 다 읽고 버리기거든요.

이 글은 OFFSET이 왜 선형으로 느려지는지, 커서 기반 페이징(no-offset)의 동작과 구현 디테일(유니크 커서·인덱스 설계), 그리고 어디까지 커서로 가고 어디는 OFFSET을 둘지 가려볼게요. 인덱스 원리는 B+Tree 편 위에 서 있는 이야기예요.

01. OFFSET은 점프가 아니라 "읽고 버리기"예요

offset 기반 Querydsl 페이징 구현(이 글의 비교 대상)은 Querydsl에 pageable을 적용하며(2020)에 있어요.

SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 100000;
-- 실제 동작: 정렬 순서대로 100,020건을 만들어 100,000건을 버리고 20건 반환

DB는 "100,000번째"가 어디인지 모르니까, 앞에서부터 세면서 와야 해요. B+Tree는 "값으로 찾기"는 잘하지만 "N번째로 가기"는 못 하거든요. 그래서 OFFSET이 클수록 읽고 버리는 양이 선형으로 늘어요. 정렬·조인·조건이 끼면 버릴 100,000건을 전부 만들어놓고 버리는 셈이라 더 비싸요.

이 함정의 고약한 점은 평소엔 안 보인다는 거예요. 사용자 대부분은 1~5페이지만 보니까요. 그러다 ① 크롤러 봇이 ?page=99999를 순회하거나 ② 관리자 화면에서 전체 데이터를 페이지 루프로 내보내거나 ③ 배치가 페이징으로 전체 테이블을 훑을 때 — 갑자기 슬로우 쿼리가 쏟아져요. "목록 API가 가끔 수십 초"의 흔한 정체예요.

02. 커서 페이징, "마지막으로 본 것 다음부터"

발상을 바꿔요. "몇 번째부터 20개"가 아니라 "내가 마지막으로 본 것 다음부터 20개"로요.

-- 첫 페이지
SELECT * FROM orders ORDER BY id DESC LIMIT 20;
-- 다음 페이지: 응답의 마지막 id(예: 99980)를 커서로
SELECT * FROM orders WHERE id < 99980 ORDER BY id DESC LIMIT 20;

WHERE id < ?는 인덱스가 가장 잘하는 일이에요 — 시작점으로 직행해서 옆으로 20개. 1페이지든 100만 번째 구간이든 항상 같은 비용이에요. 응답에 마지막 항목의 커서를 실어주고(nextCursor), 클라이언트가 다음 요청에 들고 오는 구조고요. 무한 스크롤·"더보기" UI와 정확히 맞는 모델이에요.

03. 구현 디테일 두 가지

① 커서 키는 유니크해야 해요

최신순(created_at) 정렬에서 WHERE created_at < ?로만 자르면, 같은 시각에 생성된 행들에서 누락이나 중복이 생겨요. 경계의 동점을 가를 수 없으니까요. 그래서 유니크한 보조 키(보통 id)를 붙인 복합 커서를 써요.

-- (created_at, id) 복합 커서 — 동점은 id로 가른다
SELECT * FROM orders
WHERE (created_at < :lastCreatedAt)
   OR (created_at = :lastCreatedAt AND id < :lastId)
ORDER BY created_at DESC, id DESC
LIMIT 20;

참고로 행 비교 문법 (created_at, id) < (:lastCreatedAt, :lastId)도 문법상으로는 되는데, MySQL은 이걸 인덱스 접근 조건으로 못 써서 커서 페이징의 핵심인 인덱스 직행이 깨져요. MySQL에서는 위의 OR 형태를 유지해요. 행 비교가 인덱스를 타는 건 PostgreSQL 쪽이에요.

② 커서·정렬 컬럼이 인덱스를 타야 성립해요

커서 페이징의 빠름은 전적으로 인덱스 직행에서 와요. 조회 조건까지 포함해 (user_id, created_at, id)처럼 복합 인덱스를 정렬 방향과 맞춰 설계해야 해요. 인덱스 못 타는 커서 쿼리는 OFFSET보다 나을 게 없어요.

04. 커서의 대가, 그리고 절충

• 임의 페이지 점프가 없어요 — "37페이지로 가기"는 커서로 표현이 안 돼요. 무한 스크롤이면 아예 문제가 안 되고, 페이지 번호 UI가 꼭 필요한 화면(어드민 등)은 OFFSET을 유지하되 최대 페이지를 제한(예: 200페이지)하는 절충이 현실적이에요. 그 너머는 검색·필터로 좁히게 유도하고요.
• 전체 카운트도 같은 부류의 비용이에요 — "총 1,234,567건 / 61,728페이지" 표시를 위한 COUNT(*)는 깊은 OFFSET만큼 비싸요. "더보기" UI는 카운트 자체를 없애주고, 꼭 필요하면 근사치·캐시로 풀어요. JPA Page가 매번 날리는 count 쿼리가 무거운 것도 같은 이야기예요.
• 배치의 전체 순회는 무조건 커서로 — 페이지 루프로 테이블을 훑는 배치는 뒤로 갈수록 느려지다 타임아웃 나요. "id 기준으로 잘라서 진행"(키셋 순회)이 배치의 정석이에요. 진행 위치(마지막 id)를 저장해두면 중단 후 이어하기도 공짜고요.

05. 자주 만나는 문제

목록 API가 뒤 페이지에서만 느려요

깊은 OFFSET이에요. 무한 스크롤형이면 커서로 전환하고, 페이지형이면 페이지 상한 + 검색 유도로 절충해요.

커서 페이징인데 항목이 빠지거나 중복돼요

커서 키가 유니크하지 않은 거예요. (정렬키, id) 복합 커서로 바꿔요.

크롤러가 ?page=99999를 돌면서 DB가 힘들어해요

페이지 상한 검증(400 응답)부터 거는 게 응급처치예요. 구조적으로는 커서 전환이고요.

배치가 갈수록 느려지다 죽어요

페이지 루프 순회예요. 키셋(마지막 id) 기반 순회로 바꾸면 일정한 속도로 끝까지 가요.

한 줄 점검

OFFSET은 점프가 아니라 읽고 버리기라서 깊어질수록 선형으로 느려져요. 답은 커서(no-offset) — "마지막으로 본 것 다음부터"를 인덱스로 직행하는 방식이고, 커서 키는 유니크하게(복합 커서), 인덱스는 정렬과 맞춰 설계해요. 무한 스크롤·배치 순회는 커서가 정답이고, 페이지 번호가 꼭 필요한 화면만 상한 걸린 OFFSET으로 절충해요.

그래서 지금 우리 목록 API에 던질 질문은 하나예요 — 뒤 페이지로 갈수록 느려진다면, 그건 데이터가 많아서가 아니라 OFFSET을 쓰고 있어서가 아닐까? 다음 함정은 무중단 배포, 배포하는 순간에만 터지는 버그예요.

출처: MySQL — LIMIT Query Optimization · Use The Index, Luke — No-Offset