대용량에서 게시글 목록 조회에 대해 생각해보았습니다. 페이징을 처리하려면 서버 어플리케이션 내의 메모리로 디스크에 저장된 모든 데이터를 가져오고 특정 페이지만 추출하는것은 비효율 적입니다. 디스크 접근은 일반적으로 메모리 접근보다 느립니다. 디스크에 저장된 데이터는 메모리 용량을 초과 할수 있습니다. 데이터베이스에서 특정 페이지의 데이터만 바로 추출하는 방법이 필요한데 일반적으로 '페이징 쿼리'를 통해 뽑아 냅니다. 일반적으로 페이지번호, 무한크롤 방식이 있습니다.
페이지 번호
이동할 페이지를 지정하여 원하는 페이지로 이동
무한 스크롤
스크롤을 누르면 필요에 따라 데이터를 가져오는 방법
실습을 위해서 1,200만 개 의 데이터를 미리 넣어두었습니다.
페이지 번호 방식
n번 페이지에서 m개의 게시글
SQL offset, limit 을 활용하여 offset 부터 limit까지 조회 해보자 .
하지만 30건을 조회하는데 7s나 걸림을 확인.
Explain 명령어를 통해서 확인 해본 결과 아래 결과를 확인해보았습니다.
1 SIMPLE article ALL 6322381 10.0 Using where; Using filesortTYPE - ALL : 테이블 전체를 읽은 풀 스캔이 적용되었음을 확인
Extra Using where, Using filesort : where 절로 필터링 , filesort는 데이터가 많기 때문에 file에서 정렬을 수행 되었음을 확인
-> 전체 데이터에 대해 필터링 및 정렬하기 때문에 조회가 오래 걸렸습니다.
이러한 것을 해결하기 위해 `인덱스`라는 개념이 있습니다.
인덱스
인덱스 관리를 위해 부가적인 쓰기 작업과 공간이 필요 하고
다양한 데이터 특성과 쿼리를 지원하는 다양한 자료구조 B+tree, Hash , LSM tree , R tree , Bitmap 등이 있습니다.
B+tree
관계형 데이터베이스에서는 보통 B+tree 가 사용되고 데이터가 정렬된 상태로 저장이 됩니다.
따라서 인덱스를 추가하면, 쓰기 시점에 정렬된 상태의 데이터를 생성한다.
게시판별 최신순 조회를 위한 index를 만들어 조회를 개선.
- 게시판별로 생성시간 순으로 정렬된 상태의 데이터로 만들어진다.
- 대규모 분산 환경에서는 게시글이 동시에 생성될 수 있다. 동일 시간에 갖는 데이터가 많이 있을수 있습니다.
create index idx_board_id_artice_id on article(board_id asc,article_id desc);따라서 create_at 을 정렬 조건으로 사용하면 안되고, 시간 기반의 UUID로 구현놓은 ID를 기준으로 INDEX를 구성했습니다.
article_id
explain 결과를 보면 해당 index를 사용하여 조회가 됨을 확인하였고, 초도 0.04s 로 개선됨을 확인했습니다.
1 SIMPLE article ref idx_board_id_artice_id idx_board_id_artice_id 8 const 4653863 100.0
하지만 90개에서 개수를 늘리면 다시 6s 이상걸림을 확인 할수 있습니다.
이를 알아보기 위해서는 인덱스의 종류를 알아야 합니다. Clustered Index , Secondary Index 의 종류가 있습니다.
MYSQL의 기본 스토리지 엔진은 InnoDB 라는 스토리지 엔진을 사용하고 있습니다.
InnoDB는 Clustered Index로 관리가 됩니다. Pirmary Key를 기준으로 정렬된 Clustered Index 의 그림은 아래와 같습니다.
Clustered Index
직접 생성한 인덱스는 Secondary Index라고 부릅니다.
직접 생성한 인덱스는 Secondary Index를 적용한 뒤 다시 Clust Index를 적용하여 조회가 됩니다. 즉 OFFSET 처리를 위해서 앞에 1499980번의 불필요한 SCAN이 필요합니다.
select* from article where
board_id = 1
order by article.article_id desc
limit 30 offset 1499980;
위 쿼리와 다르게 조회하는 컬럼만 바꿨는데 7s 걸리던 조회가 0.1s 걸리는 쿼리가 됩니다.
select board_id,article_id from article where
board_id = 1
order by article.article_id desc
limit 30 offset 1499980;
Covering Index : 인덱스만으로 쿼리의 모든 데이터를 처리할수 있는 인덱스로 데이터를 읽지 않고 인덱스에 포함된 정보만으로 쿼리가 가능한 인덱스를 말합니다.
select * from (
select board_id,article_id from article where
board_id = 1
order by article.article_id desc
limit 30 offset 1499980
) t left join article on t.article_id = article.article_id;
요약: 대용량 페이징 최적화 로드맵
- 문제 상황: 데이터가 많아질수록 OFFSET 방식은 앞선 데이터를 모두 읽어야 하므로 느려짐 (Full Table Scan).
- 1차 개선 (Index): 정렬된 인덱스(B+Tree)를 생성해 탐색 범위를 줄임 (0.04s로 개선).
- 한계 발생: 페이지 번호가 뒤로 갈수록(큰 OFFSET), 인덱스를 타더라도 실제 데이터를 가져오기 위해 디스크(Clustered Index)를 뒤지는 비용이 커짐.
- 2차 개선 (Covering Index): 인덱스에 포함된 정보만 먼저 빠르게 조회하여 데이터 접근 최소화.
- 최종 전략: 커버링 인덱스로 PK만 먼저 뽑고, 필요한 30건에 대해서만 실제 테이블에 조인(Join)함.
💡 주요 개념 이해하기
1. 왜 큰 페이지(High Offset)에서 다시 느려질까?
인덱스(Secondary Index)를 타더라도, SELECT *를 하게 되면 결국 실제 전체 데이터를 보기 위해 Clustered Index(데이터 본체)로 찾아 들어가는 과정이 필요합니다. 이를 '랜덤 I/O'라고 합니다.
- 작은 Offset: 30개만 읽고 30개만 데이터 본체에서 찾아오면 됨. (빠름)
- 큰 Offset: 1,500,000개를 다 읽어서 버리고 마지막 30개를 찾아야 함. 150만 번의 데이터 본체 접근 시도가 발생하므로 다시 6~7초가 걸리는 것입니다.
2. Clustered Index vs Secondary Index
- Clustered Index: 책으로 치면 '페이지 번호' 그 자체입니다. 데이터가 PK 순서대로 물리적으로 정렬되어 저장됩니다. (MySQL InnoDB에서는 PK가 곧 클러스터 인덱스)
- Secondary Index: 책 뒷면의 '찾아보기'와 같습니다. 특정 키워드(board_id 등)와 그 데이터가 있는 페이지 번호(PK)만 적혀 있습니다.
3. 커버링 인덱스 (Covering Index)
쿼리를 실행할 때 "인덱스에 적힌 정보만으로 답변이 가능할 때"를 말합니다.
- SELECT * : 인덱스 보고 -> 다시 실제 데이터 판 보러 가야 함 (느림)
- SELECT board_id, article_id : 인덱스만 봐도 답이 나옴 -> 실제 데이터 판 안 가도 됨 (매우 빠름)
🚀 최종 실행 계획 분석
SELECT * FROM (
SELECT article_id FROM article
WHERE board_id = 1
ORDER BY article_id DESC
LIMIT 30 OFFSET 1499980 -- (1) 인덱스만 보고 빠르게 PK 30개 추출
) t
LEFT JOIN article ON t.article_id = article.article_id; -- (2) 추출된 30개만 실제 데이터 조회
- 내부 쿼리: 커버링 인덱스를 사용하여 150만 번째 데이터를 찾습니다. 이때는 실제 데이터(본체)를 건드리지 않고 인덱스만 훑으므로 매우 빠릅니다. (0.1s)
- 외부 조인: 내부에서 딱 걸러진 30개의 ID에 대해서만 실제 데이터 본체에 접근합니다.
'인프런' 카테고리의 다른 글
| 비관적 락 vs 낙관적 락 설명 (0) | 2026.05.26 |
|---|