1000만 건 테이블 13초를 0.2초로 줄인 페이징 성능 최적화

대규모 트래픽을 처리하는 게시판 서비스에서 게시글 목록 조회는 가장 빈번하게 호출되는 핵심 API입니다. 특히 페이지 번호 기반 페이징에서 offset이 커질수록 느려지는 문제는 많은 개발자가 겪는 이슈입니다. 해당 내용은 인프런 강의 내용을 듣고 작성한 내용으로 정말 제가 하는 프로젝트에서 도움이 많이 되어 글로 정리하고자 합니다. DB는 miaradb11입니다.

1. 페이지 번호 방식의 문제점

총 게시글 1000만 건, 페이지당 30개 , 4 페이지 조회 (offset = 90)

select * from article
where board_id = 1
order by created_at desc
limit 30 offset 90;

조회 속도: 13s 456ms
EXPLAIN: type = ALL, Extra = Using where; ; Using filesort

즉 전체 테이블 스캔이 발생했다. create_at에 인덱스가 없어 DB는 모든 데이터를 모두 읽은 뒤 디스크 기반 정렬(filesort)을 수행하게 되어 조회 속도가 크게 느려졌다.

2. 인덱스 추가

create index idx_board_id_article_id on article(board_id asc, article_id desc);

select * from article
where board_id = 1
order by article_id desc
limit 30 offset 90;

조회 속도: 132ms 
EXPLAIN: type = range, Extra = Using index condition, key = idx_board_id_article_id

즉 인덱스를 사용했다. 정렬 기준을 created_at 대신 article_id로 변경한 이유는 분산 환경에서는 여러 서버에서 동시에 게시글 생성 시 동일 timestamp가 발생하며 정렬 순서가 불안정해진다. 목록 조회 시 순서가 바뀌거나 누락되는 문제가 생길 수 있다. Snowflake ID는 시간 기반 + 일련번호 조합, 전역 오름 차순 보장, 출동 가능성이 희박함으로 article_id로 정렬하는 것이 더 안전하다.

3. offset이 커지면 다시 느려진다

예를 들어 offset 1,455,570를 조회한다.

select * from article
where board_id = 1
order by article_id desc
limit 30 offset 1455570;

조회 속도: 1s 410ms
EXPLAIN: type = range, Extra = Using index condition, key = idx_board_id_article_id

여전히 인덱스를 사용 중 하지만 속도는 느려졌다. 왜 이런 일이 발생했을까?

 

먼저 offset 방식은 아래처럼 동작한다. offset + limit 만큼의 인덱스 데이터를 읽고 offset개는 버리고 마지막 limit개만 반환한다. 즉 offset = 90 → 120개 스캔, offset = 1,455,570 → 1,455,600개 스캔 그리고 여기서 중요한 개념이 아래 등장한다.

---

Clustered Index와 Secondary Index 개념

Clustered Index

primary key 기반으로 생성되는 인덱스이다. 구조적으로 인덱스의 가장 말단 노드인 leaf node에 실제 행 데이터 전체가 저장된다. 즉 PK를 이용해 데이터를 조회하는 것은 인덱스 경로를 따라 최종적으로 데이터 파일 자체를 직접 읽는 것과 같습니다.

Secondary Index(보조 인덱스)

PK가 아닌 다른 컬럼에 대해 사용자가 별도로 생성한 인덱스이다. 보조 인덱스의 리프 노드에는 다음 세 가지 정보만 저장한다. 인덱스를 구성하는 컬럼 값 여기선 board_id, article_id, 해당 행의 primary key(PK) 값 여기서는 article_id을 말한다. where 조건에 맞는 행을 찾기 위해 보조 인덱스 트리를 탐색한다. 보조 인덱스 리프 노드에서 해당  행의 PK추출한다. 추출한 PK 값을 이용하여 클러스터드 인덱스 트리를 다시 한번 탐색하고 최종적을 실제 행 데이터 전체를 가져옵니다.

개념	Secondary Index	Covering Index
의미	PK 외 컬럼에 추가한 인덱스	쿼리에 필요한 모든 컬럼이 들어 있는 인덱스
목적	빠르게 PK를 찾기 위한 보조 인덱스	PK 재조회 없이 인덱스만으로 쿼리 해결
데이터 조회	반드시 PK를 따라가야 함	PK 찾아갈 필요 없음(더 빠름)
leaf node	인덱스 컬럼 + PK만 포함	인덱스 컬럼 + (필요한 모든 컬럼)

Secondary Index는 인덱스 '종류'이고, Covering Index는 인덱스 사용 '패턴' 또는 '상태'이다. 커버링 인덱스는 보조 인덱스를 어떻게 활용하여 성능을 극대화 했는지에 대한 정의이다.

---

커버링 인덱스 활용으로 성능 개선

인덱스의 데이터만으로 조회하면 secondary index만 보고 결과를 만들 수 있다.

select board_id, article_id 
from article
where board_id = 1
order by article_id desc
limit 30 offset 1455570;

조회 속도: 292ms
Extra = Using index (커버링 인덱스)

secondary index에 이미 board_id, article_id가 있으므로 clustered index를 읽지 않아도 된다. 

서브쿼리 + 조인

select *
from (
        select board_id, article_id
        from article
        where board_id = 1
        order by article_id desc
        limit 30 offset 1455570
     ) t
left join article a on t.article_id = a.article_id;

조회 속도: 약 278ms

서브쿼리가 커버링 인덱스로 빠르게 30개 article_id만 추출 그 30개에 대해서만 PK 기반으로 테이블을 읽는다.
(북마크 룩업 1,455,600번 → 30번으로 감소) 따라서 select * 이지만 매우 빠르게 동작한다.

 

OFFSET 은 근본적으로 느려진다.

offset = 1455570  → 278ms
offset = 6455570  → 886ms

커버링 인덱스를 사용해도 스캔 개수에 비례하여 느려지는 구조는 변하지 않습니다. 

 

북마크 룩업이란, 보조 인덱스를 통해 원하는 레코드의 위치를 찾은 후 실제 행 데이터를 가져오기 위해 클러스터드 인덱스(Primary Key)를 다시 한번 찾아가는 과정을 말한다.

---

대규모 서비스들은 실무에서의 해결책

테이블 분리 또는 "정상적인 사용자는 300,000페이지를 조회하지 않는다" 정책으로 최대 10,000페이지까지만 허용하여 어뷰저 트래픽 방지한다. 등 여러 방법으로 문제를 해결할 것 같다.

 

인프런 강의

https://www.inflearn.com/course/%EC%8A%A4%ED%94%84%EB%A7%81%EB%B6%80%ED%8A%B8%EB%A1%9C-%EB%8C%80%EA%B7%9C%EB%AA%A8-%EC%8B%9C%EC%8A%A4%ED%85%9C%EC%84%A4%EA%B3%84-%EA%B2%8C%EC%8B%9C%ED%8C%90/dashboard