[MySQL] OFFSET 100000 지연 원인과 복합 키셋 페이징 전환 방법

OFFSET 페이징: 실무적 정의와 운영상 중요성

MySQL에서 OFFSET 페이징은 정렬된 결과의 앞부분을 건너뛴 뒤 지정한 행 수만 반환하는 방식이다. LIMIT 20 OFFSET 100000은 100001번째 행으로 즉시 이동하는 명령이 아니다. MySQL은 정렬 순서에 맞는 선행 100000행을 확인하고 버린 뒤 다음 20행을 반환해야 한다.

인덱스를 사용하더라도 건너뛸 행을 탐색하는 비용은 사라지지 않는다. 페이지가 뒤로 갈수록 스캔하는 인덱스 레코드와 CPU 사용량이 증가하며, 필요한 컬럼을 인덱스가 포함하지 않으면 테이블 접근까지 반복될 수 있다. 동시 요청이 집중되는 운영 환경에서는 버퍼 풀 경쟁과 디스크 I/O 병목으로 이어진다.

OFFSET 100000 이후의 목록 조회는 마지막 행의 정렬 키를 다음 요청에 전달하는 키셋 페이징(Keyset Pagination)으로 전환해야 한다. 핵심은 페이지 번호가 아니라 이전 페이지의 마지막 위치를 검색 조건으로 사용하는 것이다.

Part 1. LIMIT OFFSET 기본 문법 및 사용법 (Basic Syntax)

다음 목록은 최신 게시물을 먼저 보여주며 한 페이지에 20행을 반환한다. 정렬 기준은 created_at DESC, id DESC이다.

SELECT id, created_at, title, status
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

위 쿼리는 반환 행이 20개여도 최소한 OFFSET과 LIMIT에 해당하는 정렬 구간을 통과해야 한다. created_at만 정렬 기준으로 사용하면 같은 시각을 가진 행의 상대 순서가 보장되지 않는다. 페이지 요청 사이에 실행 계획이나 데이터 상태가 달라지면 동일 행이 다시 나타나거나 일부 행이 빠질 수 있다.

정렬 순서를 유일하게 만들기 위해 기본 키인 id를 마지막 정렬 컬럼으로 추가한다. 필터와 정렬을 함께 지원하는 인덱스도 동일한 컬럼 순서를 따라야 한다.

CREATE INDEX ix_posts_status_created_id
ON posts (status, created_at DESC, id DESC);

status는 등가 조건이고 created_at과 id는 정렬 및 범위 탐색에 사용된다. 등가 조건을 선행 컬럼에 배치하고 범위 검색 컬럼을 뒤에 두는 이유는 MySQL 복합 인덱스 컬럼 순서 설계 원칙에서 설명한 접근 방식과 동일하다.

Part 2. 내부 동작 메커니즘과 안티 패턴

옵티마이저가 ix_posts_status_created_id를 선택하면 정렬을 위한 filesort를 피할 수 있다. 그러나 OFFSET 100000은 인덱스의 앞선 엔트리를 순서대로 읽고 폐기하는 작업을 유발한다. 인덱스가 없는 경우에는 조건에 맞는 행을 읽고 정렬한 후 선행 결과를 버려야 하므로 비용이 더 커진다.

조회 컬럼이 인덱스에 포함되지 않으면 상황이 악화될 수 있다. MySQL은 후보 인덱스 엔트리에 대응하는 테이블 레코드를 확인해야 하며, 깊은 OFFSET에서는 실제 반환하지 않을 행에 대해서도 접근 비용이 발생할 수 있다. 모든 조회 컬럼을 무조건 인덱스에 추가하는 것도 해결책은 아니다. 커버링 인덱스의 효과와 쓰기 비용은 MySQL 커버링 인덱스 설계 기준에 따라 별도로 판단해야 한다.

조회 방식	반환 행	실행 시간	examined rows	판단 기준
OFFSET 100	20	0.6 ms	120	얕은 페이지의 기준값
OFFSET 100000	20	110.4 ms	100020	페이지 깊이에 따른 폐기 비용 확인
키셋 created_at + id	20	122.7 ms	100020	마지막 키 이후의 범위만 탐색

표의 수치는 동일한 데이터와 세션 조건에서 직접 측정해야 한다. 실행 시간은 워밍업 여부, 버퍼 풀 상태, 하드웨어와 동시 부하에 따라 달라진다. examined rows는 세션의 Rows_examined 차이 또는 성능 스키마의 문장 통계로 확인하고, 실행 계획은 EXPLAIN ANALYZE로 실제 행 흐름을 함께 검증해야 한다.

가장 흔한 안티 패턴은 프런트엔드가 임의의 페이지 번호 이동을 요구한다는 이유만으로 모든 구간에 OFFSET을 유지하는 것이다. 키셋 페이징은 이전 페이지와 다음 페이지 이동에는 적합하지만 5001페이지로 즉시 이동하는 기능을 그대로 제공하지 않는다. 임의 페이지 이동이 필수라면 검색 조건을 좁히거나, 구간별 앵커 키를 별도로 관리하거나, 제한된 깊이까지만 페이지 번호 이동을 허용해야 한다.

Part 3. 성능 최적화 및 튜닝 전략

첫 페이지에는 커서가 없으므로 기존과 같은 정렬 조건으로 LIMIT만 적용한다. 조회 결과의 마지막 행에 있는 created_at과 id를 다음 요청의 커서로 반환한다.

SELECT id, created_at, title, status
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC, id DESC
LIMIT 20;

예를 들어 첫 페이지 마지막 행의 키가 ('2026-08-31 09:15:00', 78125)라면 다음 페이지는 그 키보다 뒤에 있는 행만 조회한다. 내림차순 정렬이므로 비교 연산자는 <를 사용한다.

SELECT id, created_at, title, status
FROM posts
WHERE status = 'PUBLISHED'
  AND (created_at, id) < ('2026-08-31 09:15:00', 78125)
ORDER BY created_at DESC, id DESC
LIMIT 20;

MySQL의 행 생성자 비교는 첫 번째 값부터 사전식으로 비교한다. 위 조건은 다음 논리 조건과 같은 의미를 가진다.

AND (
       created_at < '2026-08-31 09:15:00'
       OR (created_at = '2026-08-31 09:15:00' AND id < 78125)
    )

created_at만 커서로 전달하는 방식은 가장 치명적인 안티 패턴이다. 같은 타임스탬프를 가진 행이 20개를 초과하면 다음 페이지 조건에서 일부 행이 제외될 수 있다. 정렬 컬럼과 커서 컬럼, 인덱스 컬럼은 모두 created_at, id 순서로 일치해야 한다.

1. 목록의 고정 필터와 정렬 규칙을 확정한다. 사용자마다 tenant_id 조건이 있다면 인덱스 선두에도 해당 등가 조건을 포함한다.
2. 정렬 결과가 유일해지도록 기본 키를 마지막 정렬 컬럼으로 추가한다. NULL 가능 컬럼은 비교 결과가 UNKNOWN이 될 수 있으므로 NOT NULL을 사용하거나 NULL 정렬 규칙을 별도로 설계한다.
3. 첫 페이지 결과의 마지막 복합 키를 불투명한 커서 토큰으로 전달한다. 클라이언트가 값이나 정렬 방향을 임의로 변경하지 못하도록 서버에서 검증한다.
4. 다음 페이지에서는 OFFSET을 제거하고 복합 키 범위 조건과 LIMIT만 적용한다. 페이지 크기는 서버에서 상한을 설정해야 한다.
5. EXPLAIN ANALYZE로 선택 인덱스, 실제 반환 행, 반복 횟수와 정렬 발생 여부를 확인한다. 예상 행과 실제 행 차이가 크다면 MySQL EXPLAIN ANALYZE 카디널리티 진단 절차도 함께 적용한다.

필터 조건이 추가되는 경우

커서는 필터 집합과 분리할 수 없다. 첫 요청이 status='PUBLISHED'와 특정 카테고리를 사용했다면 다음 요청도 같은 조건을 유지해야 한다. 중간 요청에서 필터가 바뀌면 기존 커서의 위치는 의미를 잃으므로 첫 페이지부터 다시 조회해야 한다.

SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
  AND status = 'PUBLISHED'
  AND category_id = 7
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

이 경우 후보 인덱스는 (tenant_id, status, category_id, created_at DESC, id DESC)이다. 단, 선택도가 낮은 필터와 인덱스 개수는 INSERT·UPDATE 비용을 증가시킨다. 실제 쿼리 빈도와 실행 계획을 확인한 뒤 기존 인덱스와의 중복 여부를 판단해야 한다.

중복·누락 검증 절차

전환 후에는 첫 페이지부터 마지막 페이지까지 커서를 연속 적용해 수집한 id를 검증한다. 수집 건수, COUNT(DISTINCT id), 기준 시점의 조건 일치 건수를 비교하면 중복과 누락을 구분할 수 있다. 검증 도중 데이터가 변경되면 비교 기준 자체가 달라지므로 읽기 전용 복제본, 일관된 스냅샷 또는 고정된 상한 키를 사용해야 한다.

-- 검증 시작 시점의 상한 키를 고정한다.
SELECT created_at, id
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC, id DESC
LIMIT 1;

-- 전체 순회 후 수집 결과에서 확인할 항목
SELECT COUNT(*) AS collected_rows,
       COUNT(DISTINCT id) AS distinct_rows
FROM collected_page_ids;

새 행이 목록 선두에 삽입되는 상황에서는 키셋 페이징이 OFFSET보다 안정적이다. 이미 받은 마지막 키보다 앞에 추가된 행은 다음 페이지 범위에 들어오지 않으므로 기존 행을 뒤로 밀어 중복시키지 않는다. 반면 조회 중인 행의 created_at이 수정되거나 행이 삭제되면 위치 이동이나 누락이 발생할 수 있다. 변경 가능한 컬럼을 정렬 키로 사용한다면 기준 시각 조건을 추가하거나 변경 불가능한 순차 키를 채택해야 한다.

이전 페이지와 임의 페이지 이동

이전 페이지는 정렬 방향과 비교 연산자를 반대로 적용한 뒤 결과를 애플리케이션에서 다시 역순으로 정렬한다. 단순히 같은 SQL의 연산자만 바꾸면 화면 순서가 반대가 되므로 주의가 필요하다.

SELECT id, created_at, title, status
FROM posts
WHERE status = 'PUBLISHED'
  AND (created_at, id) > (?, ?)
ORDER BY created_at ASC, id ASC
LIMIT 20;

페이지 번호가 반드시 필요한 관리 화면은 얕은 구간에만 OFFSET을 허용하는 혼합 방식이 현실적이다. 대규모 공개 목록과 무한 스크롤은 키셋 방식을 사용하고, 전체 건수와 임의 페이지 이동이 중요한 소규모 결과에는 OFFSET을 유지한다. 검색 결과가 충분히 좁아져 OFFSET 대상 행이 작다면 구조 변경의 이익도 제한적이다.

FAQ

id 하나만 마지막 키로 사용해도 되는가?

ORDER BY id DESC처럼 실제 정렬이 id 하나뿐이면 가능하다. 화면 정렬이 created_at DESC인데 커서는 id만 사용하면 두 순서가 일치하지 않으므로 중복과 누락 위험이 발생한다.

전체 페이지 수는 어떻게 표시하는가?

키셋 페이징은 전체 페이지 번호보다 다음 커서 존재 여부를 제공하는 방식에 적합하다. 전체 건수가 반드시 필요하면 별도 COUNT 쿼리나 비동기 집계값을 사용해야 하며, 목록 조회 쿼리에 깊은 OFFSET을 다시 도입해서는 안 된다.

LIMIT보다 한 행 더 조회하는 이유는 무엇인가?

페이지 크기가 20이면 21행을 조회해 다음 페이지 존재 여부를 판단할 수 있다. 응답에는 앞의 20행만 포함하고 21번째 행이 존재할 때 다음 커서를 제공한다. 별도의 COUNT 쿼리를 실행하지 않아도 된다.

성능 최적화의 핵심은 OFFSET으로 버리는 행을 줄이는 것이 아니라 폐기 작업 자체를 제거하는 것이다. 고정 필터, 유일한 복합 정렬 키, 동일한 순서의 인덱스와 범위 검색 조건을 함께 적용해야 한다. DBA는 페이지 깊이별 실행 시간과 examined rows를 실측하고, 실행 계획에서 범위 접근과 정렬 제거 여부를 확인한 뒤 운영 환경에 반영해야 한다.

측정 환경

이 글의 측정값은 로컬 개발 환경에서 직접 실행한 결과입니다. MySQL 8.4.3, InnoDB 버퍼 풀 128MB, 정렬 버퍼 256KB, 각 쿼리 3회 실행 후 중앙값입니다. 운영 서버의 사양과 데이터 분포에 따라 값은 달라집니다.