배경 및 목표
특정 조회 API의 응답이 13초까지 걸려, 해당 기능을 사용하기 어려웠습니다. 특정 테이블(2천만 건)을 JOIN하는 쿼리에서 불필요한 JOIN + 디스크 I/O가 병목이었습니다.
목표
- 2천만 건 JOIN 조회 API를 실사용 가능한 응답속도로 개선한다.
- 데이터 증가에도 재사용 가능한 조회 패턴을 확보한다.
해결 방법과 해결 후보군
후보군 비교
| 방식 | 설명 | 한계 |
|---|---|---|
| 인덱스만 추가 | 컬럼 인덱스 보강 | 불필요 JOIN·디스크 I/O 잔존 |
| 인프라 증설(scale up) | DB 스펙 상향 | 비용 증가, 데이터 늘면 재발 |
| JOIN 제거 + 커버링 인덱스 2단계 쿼리 (채택) | 쿼리 구조 개선 | 13초→1초, 대용량 조회 재사용 패턴 확보 |
1. EXPLAIN 기반 병목 분석
EXPLAIN으로 실행 계획을 확인한 결과, 두 가지 문제를 발견했습니다:
- 실제 조건에 필요하지 않은 테이블까지 JOIN하고 있었음
- WHERE 절의 필터링이 인덱스를 타지 못해 Full Table Scan이 발생
-- EXPLAIN 결과 (문제)
-- type: ALL, rows: 20,000,000, Extra: Using where2. 불필요 JOIN 제거
쿼리에 포함된 JOIN 중 실제 결과에 영향을 주지 않는 테이블들을 식별하여 제거했습니다. 이것만으로도 실행 시간이 절반 이하로 줄었습니다.
3. 커버링 인덱스 + 2단계 쿼리
남은 병목은 2천만 건 테이블의 디스크 I/O였습니다. 이를 해결하기 위해 2단계 쿼리 전략을 적용했습니다.
-- 1단계: 커버링 인덱스로 PK만 추출 (디스크 I/O 없음)
SELECT a.id FROM entity_a a
JOIN entity_b b ON b.entity_a_id = a.id
WHERE b.condition = ?
LIMIT 0, 100
-- 2단계: PK 리스트로 필요한 데이터만 조회
SELECT * FROM entity_a
WHERE id IN (1, 2, 3, ... 100)
커버링 인덱스란 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있어, 실제 데이터 페이지(디스크)에 접근하지 않고 인덱스만으로 결과를 반환하는 것입니다. 1단계에서 PK만 추출하면 커버링 인덱스가 적용되어 2천만 건이어도 매우 빠릅니다. 2단계에서는 이미 좁혀진 100건의 PK로 재조회하므로 부하가 거의 없습니다.
대용량 테이블에서 “PK를 먼저 좁히고 재조회”하는 이 패턴은 이후 다른 대용량 조회에서도 재사용했습니다.
결과
| 지표 | 기존 | 개선 |
|---|---|---|
| 응답시간 | 13초 | 1초 (92% 개선) |
- 개선 전 쿼리
- 개선 후 쿼리
모니터링
- 해당 API의 P95/P99 응답시간을 관측한다.
- 쿼리 실행 계획(EXPLAIN)의 스캔 타입·조회 행 수를 관측한다.