전국 단위의 약국, 공장, 사업장 등 수백만 건에 달하는 대용량 공공데이터를 RDBMS에 적재하여 서비스할 때, 뒷페이지로 이동할수록(OFFSET 100000 이상) DB CPU 점유율이 100%로 치솟거나 Response Time이 급증하는 성능 병목을 마주하게 됩니다.
이는 RDBMS 옵티마이저가 OFFSET 위치까지의 모든 레코드를 메모리로 읽어 들인 후 버리는 동작 방식 때문입니다. 이번 글에서는 커버링 인덱스(Covering Index)를 활용한 지연 조인(Deferred Join) 및 No-Offset(Seek Method) 페이징 설계를 통해 대용량 조회 성능을 극대화하는 방법을 다룹니다.
1. 대용량 오프셋 페이징(OFFSET/LIMIT)의 내부 동작 메커니즘
전국 사업자 정보 300만 건이 적재된 public_business 테이블을 가정해 보겠습니다. 사용자가 25,000번째 페이지(페이지당 20건)를 조회할 때 실행되는 전형적인 쿼리는 다음과 같습니다.
-- 일반적인 OFFSET 페이징 쿼리 (500,000번째 레코드부터 20건 조회)
SELECT id, business_name, owner_name, status, registered_at, address
FROM public_business
WHERE status = 'ACTIVE'
ORDER BY id ASC
LIMIT 20 OFFSET 500000;
이 쿼리를 실행했을 때 데이터베이스 옵티마이저가 500,000개의 레코드를 건너뛰고 바로 500,001번째 레코드부터 읽어올 것이라고 기대하기 쉽습니다. 그러나 InnoDB 스토리지 엔진의 동작 방식은 전혀 다릅니다.
- 모든 선행 레코드의 데이터 페이지 탐색: 데이터베이스 엔진은 정렬 조건과 조건절을 만족하는 레코드를 500,020건까지 순차적으로 읽어야 합니다.
- 불필요한 클러스터드 인덱스 역참조: SELECT 절에 포함된 컬럼(
business_name,address등)을 가져오기 위해, 보조 인덱스를 사용하더라도 매 레코드마다 기본키(PK)를 통해 클러스터드 인덱스의 실제 데이터 페이지를 조회(Random I/O)합니다. - 버퍼 풀 적중률 하락 및 메모리 오염: 읽어 들인 500,000건의 행은 클라이언트에게 전달되지 않고 즉시 폐기되지만, 이를 읽는 과정에서 버퍼 풀의 LRU 리스트가 오염되고 디스크 읽기가 강제됩니다.
2. 해결 패턴 1: No-Offset (Keyset Pagination) 설계
딥 페이징 문제를 근본적으로 해결하는 가장 효과적인 방법은 OFFSET 키워드 자체를 제거하는 No-Offset(또는 Keyset) 페이징입니다. 직전 페이지에서 확인한 마지막 레코드의 식별자(주로 자동 증가 PK 또는 고유 정렬 키)를 다음 페이지 요청 시 조건절로 전달하는 방식입니다.
-- No-Offset 페이징 쿼리 (직전 페이지의 마지막 id가 582410인 경우)
SELECT id, business_name, owner_name, status, registered_at, address
FROM public_business
WHERE status = 'ACTIVE'
AND id > 582410
ORDER BY id ASC
LIMIT 20;
이 방식의 성능 차이는 B+Tree 인덱스의 탐색 구조에서 비롯됩니다. 데이터베이스는 500,000개의 이전 레코드를 순차 스캔하는 대신, B+Tree 루트 노드에서부터 id = 582410인 위치를 즉시 탐색(Point Lookup)한 뒤, 인덱스 리프 노드의 연결 리스트를 따라 정확히 20개의 레코드만 읽어옵니다.
# FastAPI / SQLAlchemy 기반 No-Offset 구현 예시
from fastapi import APIRouter, Query, Depends
from sqlalchemy.orm import Session
from typing import List, Optional
router = APIRouter()
@router.get("/api/v1/public-business")
def get_businesses(
last_id: Optional[int] = Query(None, description="직전 페이지의 마지막 ID"),
limit: int = Query(20, le=100),
db: Session = Depends(get_db)
):
query = db.query(PublicBusiness).filter(PublicBusiness.status == "ACTIVE")
if last_id is not None:
query = query.filter(PublicBusiness.id > last_id)
items = query.order_by(PublicBusiness.id.asc()).limit(limit).all()
next_cursor = items[-1].id if len(items) == limit else None
return {
"items": items,
"next_cursor": next_cursor,
"has_more": next_cursor is not None
}
3. 해결 패턴 2: 커버링 인덱스 기반 지연 조인(Deferred Join)
관리자 콘솔이나 검색 화면 등 특정 페이지 번호를 직접 선택해야 하는 비즈니스 요구사항이 있는 경우에는 No-Offset을 도입하기 어렵습니다. 이때 사용할 수 있는 아키텍처 패턴이 커버링 인덱스(Covering Index)를 활용한 지연 조인(Deferred Join)입니다.
- 1단계 (커버링 인덱스 서브쿼리): SELECT 절에
id(PK)만 명시하고 보조 인덱스를 탐색합니다. 테이블의 데이터 블록을 전혀 건드리지 않고 메모리상의 인덱스 트리만 읽어 500,000건을 빠르게 건너뛴 뒤 대상 20건의id만 추출합니다. (실행 계획:Using index) - 2단계 (클러스터드 인덱스 조인): 서브쿼리에서 확정된 단 20개의
id를 메인 테이블과 조인하여 나머지 상세 컬럼을 가져옵니다. 실제 데이터 페이지 역참조 비용이 500,020회에서 단 20회로 줄어듭니다.
-- 복합 인덱스 생성: (status, id)
CREATE INDEX idx_business_status_id ON public_business(status, id);
-- 커버링 인덱스를 활용한 지연 조인 (Deferred Join) 쿼리
SELECT b.id, b.business_name, b.owner_name, b.status, b.registered_at, b.address
FROM public_business b
JOIN (
SELECT id
FROM public_business
WHERE status = 'ACTIVE'
ORDER BY id ASC
LIMIT 20 OFFSET 500000
) AS temp ON b.id = temp.id;
4. 실무 아키텍처 구축 시 핵심 체크리스트
- 전체 레코드 수(COUNT(*)) 쿼리의 분리: 페이지네이션 네비게이터를 그리기 위해 매번
SELECT COUNT(*)를 호출하면 페이징 최적화의 효과가 반감됩니다. 조건절에 일치하는 전체 카운트는 Redis 등의 인메모리 저장소에 TTL을 부여하여 캐싱하거나, 첫 페이지 조회 시에만 비동기로 계산하는 구조를 권장합니다. - 복합 인덱스 컬럼 순서 원칙 준수: 지연 조인을 사용할 경우 인덱스는
(필터링 컬럼 = 조건) -> (정렬 컬럼) -> (PK)순서로 배치되어야 인덱스 컨디션 푸시다운(ICP)과 정렬 생략(Filesort 방지)의 이점을 온전히 활용할 수 있습니다. - 최대 조회 페이지 상한(Limit Cap) 설정: 실무 시스템에서는 일반 사용자가 10,000페이지 이상을 직접 조회하는 행위는 비정상적인 데이터 스크래핑 시도일 확률이 높습니다. 비즈니스 정책상 최대 탐색 가능 페이지(예: 100페이지)를 제한하는 가드레일을 설정하는 것이 데이터베이스 보호에 효과적입니다.
5. 결론
수백만 건 규모의 공공데이터를 서비스할 때 OFFSET/LIMIT 기반의 단순 페이징은 시스템 자원을 비효율적으로 고갈시키는 요인이 됩니다. 실시간 피드나 데이터 수집·동기화 워커라면 No-Offset 패턴을 최우선으로 도입해야 하며, 관리자 페이지 등 번호 기반 페이지 네비게이션이 반드시 필요한 시스템이라면 커버링 인덱스를 활용한 지연 조인(Deferred Join)을 적용하여 쿼리 실행 계획을 개선하는 것이 필수적인 아키텍처 원칙입니다.
