taeknology/notes

[MySQL] 키셋(cursor) 페이지네이션: 복합 정렬에서 자주 깨지는 작성 패턴

개요

한 줄 요약: 키셋(cursor) 방식은 이전 페이지의 마지막 행 값을 WHERE 로 받아 시작점을 직접 짚으므로 응답 시간이 OFFSET 위치와 무관함. 단 복합 정렬에서는 작성 형태에 따라 OFFSET 보다도 느려질 수 있음.

키셋 페이지네이션은 무한 스크롤·“다음” 버튼 UI 의 표준 해법이 됨. 다만 단일 PK 정렬 외에 (created_at, id) 같은 비유일 + tie-breaker 정렬로 가는 순간 작성 형태 차이로 응답 시간이 백배·천배 갈리는 함정이 있음. 이 글은 MySQL 8.4.8 기준으로 키셋의 상수 비용 동작과 복합 정렬에서의 작성 규칙·한계를 정리함.

항목내용
검증 버전MySQL 8.4.8
적용 범위InnoDB, 단일 PK 정렬 + 복합 (created_at, id) 정렬
데이터1,280,000행, payload 100바이트, (created_at, id) 보조 인덱스

결론

단일 키 키셋 — 위치 무관 상수 시간

PK(id) 정렬 기준 — pivot 위치를 바꿔도 응답 시간은 거의 변하지 않음.

pivot 위치응답 시간 (warm)
~ 1,0001.5 ms
~ 100,0002.86 ms
~ 500,0008.42 ms
~ 1,000,0000.84 ms ~ 1.25 ms

warm 반복 호출 기준. 1M 측정 시점에는 버퍼풀이 가장 따뜻한 상태라 0.84~1.25 ms 로 잡혔음. 본질은 위치 무관 1 ms 안팎이며 500k 의 8.42 ms 는 해당 인덱스 페이지의 부분 콜드 영향임 — 표만 보면 1M 이 더 빠른 듯한 인상이 생기지만 OFFSET 측 선형 증가와 비교하면 모두 같은 자릿수 안에 들어가는 분산임.

복합 정렬 키셋 — 작성 규칙 두 가지

복합 정렬(ORDER BY created_at, id)에서 키셋이 빠르려면 다음 두 규칙을 둘 다 지켜야 함.

규칙위반 시
OR 형태로 작성 (튜플 비교 X)인덱스 풀 스캔 + 필터 → 37~41 초
SELECT 컬럼이 인덱스 커버링행마다 PK 룩업 → 0.78 ms → 527 ms

한계 — 키셋이 만능은 아님

  • 랜덤 점프 불가 — ?page=12345 같은 UX는 키셋만으로 못 만듦.
  • 정렬 옵션 늘어나면 인덱스·조건 부담 — 사용자 선택 정렬을 키셋으로 다 받으려면 인덱스 수가 빠르게 늘어남.
  • 커버링 안 되면 효과 반감 — 응답 컬럼이 인덱스 밖에 있으면 PK 룩업 비용으로 OFFSET 과 비슷해질 수 있음.

상세

단일 PK 키셋의 동작

키셋은 이전 페이지의 마지막 행 값을 받아 WHERE 로 시작점을 직접 짚음. 인덱스 B+Tree 에서 시작 키를 한 번 찾고 거기서 LIMIT N 만큼만 읽으므로 위치와 무관하게 비용이 일정함.

EXPLAIN ANALYZE
SELECT id, user_id, created_at
FROM pagination_test
WHERE id > 1146419        -- 이전 페이지의 마지막 id
ORDER BY id
LIMIT 20;
-> Limit: 20 row(s)  (actual time=0.84..0.95 rows=20 loops=1)
    -> Filter: (id > 1146419)
       -> Index range scan on pagination_test using PRIMARY
          (cost=4.5 rows=20) (actual time=0.83..0.94 rows=20 loops=1)

rows=20 — 옵티마이저가 정확히 N 개만 읽음. 이 패턴은 PK 뿐 아니라 인덱스가 걸린 임의의 단조 컬럼(생성 시각, 수치 ID 등)에 적용 가능함.

첫 콜드 호출 한정 7.64 ms 가 잡혔고 두 번째부터는 1 ms 미만으로 떨어짐. 버퍼풀에 인덱스 페이지가 올라온 뒤로는 위치를 바꿔도 응답 시간이 같음.

복합 정렬 함정 ① 튜플 비교는 range scan 으로 풀리지 않음

(created_at, id) 정렬에서 가장 직관적인 키셋 표현이 다음 형태임.

SELECT id, user_id, created_at
FROM pagination_test
WHERE (created_at, id) > ('2025-06-15 12:00:00', 640000)
ORDER BY created_at, id
LIMIT 20;

그러나 8.4 옵티마이저는 이 형태를 range scan 으로 풀어주지 않고 인덱스 풀 스캔 + 필터로 처리함. 같은 데이터·인덱스에서 37~41초가 걸렸음.

-> Limit: 20 row(s)  (actual time=37194..37194 rows=20 loops=1)
    -> Filter: (created_at,id) > (...)
       -> Index scan on pagination_test using idx_created_at
          (actual time=... rows=500020 loops=1)

공식 문서의 Range Optimization of Row Constructor Expressions 절은 row constructor 가 range scan 으로 풀리는 조건을 IN() 폼만 명시함. >, < 같은 비교 폼은 대상에서 빠져 있어, 위 결과가 바로 그 결과임.

복합 정렬 함정 ② OR 폼으로 풀어쓰면 정상 range scan

같은 의미의 조건을 옵티마이저가 알아듣는 형태로 풀어쓰면 정상적으로 인덱스 범위 스캔을 탐.

EXPLAIN ANALYZE
SELECT id, created_at
FROM pagination_test
WHERE created_at > '2025-06-15 12:00:00'
   OR (created_at = '2025-06-15 12:00:00' AND id > 640000)
ORDER BY created_at, id
LIMIT 20;
-> Limit: 20 row(s)  (actual time=0.51..0.78 rows=20 loops=1)
    -> Index range scan on pagination_test using idx_created_at
       over (created_at = '2025-06-15 12:00:00' AND 640000 < id) OR ('2025-06-15 12:00:00' < created_at)
       (cost=9.0 rows=20) (actual time=0.50..0.77 rows=20 loops=1)

37초 → 0.78 ms — 표현만 바꿨는데 비용이 5만배 차이남. 동일 의미를 담은 두 SQL 의 옵티마이저 처리가 이렇게 갈린다는 점 자체가 운영 함정.

복합 정렬 함정 ③ 커버링 인덱스가 없으면 절반의 효과

위 OR 폼 키셋도 SELECT 컬럼이 보조 인덱스에 없는 컬럼을 포함하면 행마다 PK 룩업이 발생함. SELECT 를 (id, created_at) (인덱스 커버) 에서 (id, created_at, user_id) (PK 룩업 필요) 로만 바꿔도 응답이 0.78 ms → 527 ms 로 늘어남.

키셋의 장점을 살리려면:

  • 응답 컬럼을 인덱스 안으로 좁히거나
  • 보조 인덱스에 자주 쓰는 컬럼을 같이 포함시키거나
  • 페이지네이션 쿼리는 id 만 가져오고 상세는 별도 PK 조회로 분리(이때 PK 룩업 N 회는 어차피 필요하지만, 페이지 응답 자체는 빠르게 줄 수 있음).

테스트

환경

항목값
엔진MySQL 8.4.8 (SELECT VERSION() 확인)
행 수1,280,000
페이로드payload VARCHAR(200) 에 100바이트 채움
보조 KEYidx_created_at (created_at, id)

단일 PK 키셋 측정

EXPLAIN ANALYZE SELECT id, user_id, created_at FROM pagination_test WHERE id > 1146419 ORDER BY id LIMIT 20;

위 결과가 콜드 7.64 ms / 웜 0.84~0.95 ms. 다른 pivot 위치에서도 동일한 패턴.

복합 정렬 — 형태별 비교

작성 형태응답 시간 (warm)
튜플 비교 (created_at, id) > (?, ?)37 ~ 41 초
OR 폼 + SELECT 가 인덱스 커버0.51 ~ 0.78 ms
OR 폼 + SELECT 에 비커버 컬럼 (user_id) 포함 → PK 룩업 20회 추가527 ~ 535 ms

MySQL 8.4.8 에서 직접 확인 — 같은 의미의 조건이라도 옵티마이저 처리가 갈리므로 새 키셋 쿼리를 도입할 때는 EXPLAIN ANALYZE 로 range scan 여부를 반드시 확인.

참고

관련 글