taeknology/notes

[MySQL] OFFSET-LIMIT 페이지네이션 비용: 깊은 페이지가 선형으로 느려지는 이유

개요

한 줄 요약: LIMIT N OFFSET M 은 옵티마이저가 M + N 행을 만든 뒤 앞 M 행을 버리는 모델이라, 응답 시간이 OFFSET 위치에 선형 비례함.

웹·앱의 무한 스크롤이나 관리자 도구에서 가장 흔한 페이지네이션이 ORDER BY ... LIMIT N OFFSET M 임. 초반 몇 페이지에서는 수 ms 안쪽이라 비용이 잘 안 보이지만, 검색·관리자 도구처럼 수십만~백만 OFFSET 까지 가는 경로가 한 번이라도 열리면 응답 시간이 수백 ms~수 초로 튐. 이 글은 MySQL 8.4.8 기준으로 OFFSET-LIMIT 의 실제 비용 곡선을 측정하고, 인덱스가 있어도 무력해지는 함정과 운영에서 버틸 수 있는 대응 패턴을 정리함.

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

결론

응답 시간이 OFFSET 에 선형으로 증가함

PK(id) 단일 정렬 기준 — OFFSET 위치를 5자리수에 걸쳐 바꾼 측정값. 요청은 항상 LIMIT 20.

OFFSET 위치응답 시간 (warm)
00.06 ms
1,0001.91 ms
100,00071.6 ms
500,000147 ms
1,000,000466 ms

표는 warm 반복 측정값으로 통일. 1M 위치는 cold 첫 호출 335 ms 후 두 번째 호출부터 466 ms 로 안정. cold 가 더 빨라 보이는 건 1회 측정 노이즈로 보고 정상 상태인 warm 을 비교 기준으로 둠.

OFFSET 100배 → 응답 100배 가까이. 인덱스가 있어도 이 곡선은 평탄해지지 않음 — 옵티마이저가 정렬 순서로 M + N 행을 만들어내고 앞 M 행을 버리는 모델이기 때문.

운영 대응

  • 첫 수는 깊은 OFFSET 차단. UI에서 “다음” 버튼만 노출하거나, API에서 OFFSET 상한(예: 10,000)을 두면 비용 폭주 자체가 안 일어남.
  • 랜덤 점프가 꼭 필요한 화면이라면 OFFSET 대신 범위 분할. 보고서·CSV export 같은 일괄 처리에는 WHERE created_at BETWEEN ... 으로 잘라서 페이지화 비용 자체를 회피.
  • 순차 다음/이전만 필요한 UI 라면 키셋(cursor). 마지막 행의 정렬 키를 WHERE col > ? 로 받아 페이지마다 인덱스 한 번 탐색으로 끝남 — 위치가 깊어져도 비용이 일정함.

상세

OFFSET-LIMIT 의 처리 모델

LIMIT N OFFSET M 은 정렬된 결과에서 앞 M 개를 건너뛰고 그다음 N 개를 반환함. 실행기 입장에서는 M + N 개를 정렬 순서대로 만들어내고 앞 M 개를 소비하지 않고 폐기함. 즉 OFFSET 이 커지면 실제로 읽고 버리는 행 수가 그만큼 늘어남.

EXPLAIN ANALYZE
SELECT id, user_id, created_at
FROM pagination_test
ORDER BY id
LIMIT 20 OFFSET 1000000;
-> Limit/Offset: 20/1000000 row(s)  (actual time=466..466 rows=20 loops=1)
    -> Index scan on pagination_test using PRIMARY
       (cost=130114 rows=1.27e+6) (actual time=0.04..364 rows=1000020 loops=1)

actual time=...rows=1000020 — 인덱스 스캔으로 1,000,020 행을 읽은 뒤 1,000,000 행을 버림. 인덱스가 있어 filesort 는 회피했지만 그래도 끝까지 읽음.

이 비용이 비싼 진짜 이유는 단순히 행을 많이 읽기 때문이 아니라, 무조건 선형이라 캐시·인덱스로 우회할 방법이 없다는 것. 깊은 페이지 한 번이 다른 트래픽까지 같이 끌고 내려감.

순차 페이징의 누적 비용 — 같은 행을 여러 번 스캔

같은 사용자가 1페이지부터 순서대로 넘겨봄. 각 요청마다 옵티마이저는 그 페이지의 OFFSET 위치까지 매번 다시 스캔함. 즉 앞쪽 행일수록 더 자주 스캔됨.

xychart-beta
    title "행 구간별 스캔 횟수 (10페이지 순차 조회)"
    x-axis ["0-20", "20-40", "40-60", "60-80", "80-100", "100-120", "120-140", "140-160", "160-180", "180-200"]
    y-axis "스캔 횟수" 0 --> 12
    bar [10, 9, 8, 7, 6, 5, 4, 3, 2, 1]

앞쪽 행 구간일수록 스캔 횟수가 늘어남 — 0-20 구간은 어떤 페이지를 조회해도 매번 통과하고, 180-200 구간은 마지막 페이지에서만 한 번 스캔됨.

이를 페이지 단위로 합쳐 누적으로 보면 실제 스캔 행수가 결과 행수를 훨씬 앞지름.

xychart-beta
    title "K 페이지까지 누적 — 결과 행수(막대) vs 실제 스캔 행수(선)"
    x-axis "조회한 페이지 수 K" 1 --> 10
    y-axis "행 수" 0 --> 1200
    bar [20, 40, 60, 80, 100, 120, 140, 160, 180, 200]
    line [20, 60, 120, 200, 300, 420, 560, 720, 900, 1100]

막대(결과 행수)는 K 에 선형으로 비례하는 반면 선(누적 스캔 행수)은 위로 휘어짐. 두 그래프 사이의 간격이 곧 버려진 스캔임 — 10 페이지를 다 본 시점에서는 결과 200 행을 받기 위해 1,100 행을 스캔한 셈.

일반화하면 K 페이지를 순차 조회할 때 누적 스캔량은 N·K(K+1)/2 로 K 에 제곱 비례함. 페이지가 두 자릿수만 넘어도 불필요 스캔이 결과의 수십 배가 되고, 깊어질수록 동일 인덱스 페이지가 버퍼풀에서 반복 히트되며 다른 워크로드의 캐시 적중률까지 같이 갉아먹음.

보조 인덱스가 무력해지는 함정 — 복합 정렬 OFFSET

(created_at, id) 처럼 비유일 컬럼 + tie-breaker 로 정렬할 때, 인덱스가 있는데도 옵티마이저가 table scan + filesort 를 고를 수 있음.

EXPLAIN ANALYZE
SELECT id, user_id, created_at
FROM pagination_test
ORDER BY created_at, id
LIMIT 20 OFFSET 500000;
-> Limit/Offset: 20/500000 row(s)  (actual time=951..951 rows=20 loops=1)
    -> Sort: pagination_test.created_at, pagination_test.id, limit input to 500020 row(s)
       -> Table scan on pagination_test (cost=130114 rows=1.27e+6)
          (actual time=0.05..720 rows=1.28e+6 loops=1)

같은 위치에서 PK 단일 정렬 OFFSET(147 ms) 보다 6배 이상 느려졌음. 이유는 보조 인덱스로 정렬 순서를 따라가도 행마다 PK 룩업이 발생하므로, 500k 행 기준으로는 그냥 다 읽고 정렬하는 비용이 더 싸다고 옵티마이저가 판단한 결과. 즉 깊은 OFFSET + 복합 정렬 조합은 인덱스로 살릴 방법이 없다고 봐야 함.

그래도 OFFSET 을 써야 하는 경우

랜덤 점프(?page=12345) UX, 사전에 페이지 수를 노출해야 하는 화면 등은 OFFSET 외에 깔끔한 대안이 없음. 이 경우 다음 두 장치를 같이 둠.

  • 상한. 애플리케이션 단에서 OFFSET ≤ N 으로 제한. 한도를 넘으면 검색 조건을 더 좁히도록 안내.
  • 인덱스 점검. 단일 정렬이 가능하면 PK 단독 또는 단일 컬럼 인덱스를 정렬키로 둠. 복합 정렬이 불가피하면 보조 인덱스 + FORCE INDEX 로 옵티마이저 선택을 강제할지 검토.

테스트

환경

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

테이블·인덱스

CREATE TABLE pagination_test (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  payload VARCHAR(200) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_created_at (created_at, id)
) ENGINE=InnoDB;

데이터 생성

10k 행을 재귀 CTE 로 시드한 뒤 self-INSERT 를 7회 반복해 1.28M 까지 두 배씩 늘림.

SET cte_max_recursion_depth = 100000;
INSERT INTO pagination_test (user_id, status, created_at, payload)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT
  FLOOR(1 + RAND() * 1000),
  IF(RAND() < 0.95, 1, 0),
  TIMESTAMPADD(SECOND, FLOOR(RAND() * 30000000), '2025-01-01 00:00:00'),
  REPEAT('x', 100)
FROM seq;
 
-- 두 배씩 7회 반복 → 10k → 20k → 40k → ... → 1.28M
INSERT INTO pagination_test (user_id, status, created_at, payload)
SELECT user_id, status, created_at, payload FROM pagination_test;

OFFSET 응답 시간 측정

각 위치에서 EXPLAIN ANALYZE 를 콜드(첫 호출)·웜(2회차) 모두 측정함. 결론 표의 시간이 모두 같은 절차에서 캡처한 값임. actual time 의 두 번째 값(누적)을 비교 기준으로 사용.

EXPLAIN ANALYZE SELECT id, user_id, created_at FROM pagination_test ORDER BY id LIMIT 20 OFFSET 100000;
EXPLAIN ANALYZE SELECT id, user_id, created_at FROM pagination_test ORDER BY id LIMIT 20 OFFSET 500000;
EXPLAIN ANALYZE SELECT id, user_id, created_at FROM pagination_test ORDER BY id LIMIT 20 OFFSET 1000000;

MySQL 8.4.8 에서 직접 확인 — payload 컬럼이 행 폭을 늘려 행 수 대비 페이지 수가 충분히 커지도록 했음. 좁은 행으로 1.28M 을 채우면 인덱스가 너무 작아져 OFFSET 이 비싸 보이지 않을 수 있음.

참고

관련 글