CleanBreath
안양시 금연·흡연구역을 지도 폴리곤으로 시각화하는 웹 서비스 (6인 팀 프로젝트, PL/백엔드 담당)
87%↓
쿼리 응답 개선
95%↓
API 호출 절감
74→91
Lighthouse 성능
6인
팀 규모
쿼리 최적화 - 문서
서비스 출시 후 발견한 풀 스캔 병목의 원인 분석과 복합 인덱스 재설계 과정
Problem
→ 서비스 출시 후 특정 지역 조회에서 응답 지연을 감지했고, 원인은 인덱스 누락으로 인한 풀 스캔이었습니다.
서비스 출시 후 안양시 외곽 지역에서 금연/흡연 구역을 조회할 때 응답이 눈에 띄게 느려지는 현상을 감지했습니다. 데이터 건수가 수백 건 이상인 구역에서 특히 지연이 심했습니다. 처음에 인덱스를 걸지 않은 이유는 솔직하게 말하면, 로컬 개발 환경의 데이터가 적어서(수십 건) 느림을 체감하지 못했고, JPA 엔티티 설계 시 위도/경도 컬럼에 인덱스를 고려하지 않았기 때문입니다. 이 경험에서 배포 전 실제 규모의 데이터로 테스트해야 한다는 것을 배웠습니다.
- ▸로컬 개발 환경(수십 건)에서는 느림을 체감하지 못했으나, 프로덕션(수백 건)에서 응답 지연 발생
- ▸JPA 엔티티 설계 시 위도/경도 컬럼에 인덱스를 고려하지 않음
- ▸EXPLAIN 결과 type: ALL(풀 스캔) — 위도/경도 범위 조건에 인덱스 미사용
- ▸일반 구역 조회 API 응답 속도 180~185ms로 사용자 체감 지연
Trade-off
→ EXPLAIN 분석으로 풀 스캔 원인을 파악하고, 위도/경도 복합 인덱스 + FK 인덱스로 해결했습니다.
장점
- 구현이 간단함
- 기존 쿼리 변경 불필요
단점
- 위도/경도 동시 범위 조건에서 하나의 인덱스만 사용 — 나머지는 풀 스캔
- 복합 조건 최적화 불가
- EXPLAIN에서 여전히 rows 수가 높게 나옴
장점
- 위도/경도 동시 범위 조건에 최적화 — 두 컬럼 모두 인덱스 활용
- EXPLAIN에서 type: ALL → ref/range로 전환 확인 가능
- FK 인덱스로 JOIN 성능도 함께 개선
- 컬럼 순서(위도 먼저)를 쿼리 패턴에 맞게 설계
단점
- 인덱스 설계 시 컬럼 순서 결정에 도메인 이해 필요
- INSERT/UPDATE 시 인덱스 유지 비용 소폭 증가
의사결정
위도/경도 조건이 범위 조회(BETWEEN)이기 때문에 복합 인덱스의 컬럼 순서가 중요합니다. 위도를 첫 번째 컬럼으로 설정한 이유는, 위도 범위가 경도 범위보다 좁아 첫 번째 컬럼에서 더 많은 행을 필터링할 수 있기 때문입니다. FK 인덱스도 함께 추가하여 구역 타입별 JOIN 성능을 개선했습니다.
Trade-off: 복합 인덱스 추가로 INSERT/UPDATE 시 인덱스 유지 비용이 소폭 증가하지만, 금연/흡연 구역 데이터는 변경 빈도가 매우 낮아(월 1회 미만) 조회 성능 개선 효과가 압도적으로 큽니다.
Architecture
→ EXPLAIN 기반 병목 분석 → 복합 인덱스 설계(위도 우선) → FK 인덱스 추가 → 쿼리 리팩터링 순서로 진행했습니다.
EXPLAIN 결과에서 type: ALL, rows: 전체 행 수를 확인하여 풀 스캔이 발생하는 원인을 파악했습니다. 위도/경도 범위 조건에 복합 인덱스를 설계하고, 구역 타입 FK에도 인덱스를 추가했습니다. WHERE 조건을 인덱스를 잘 탈 수 있도록 리팩터링하고, 아파트/일반 구역 구분으로 쿼리 패턴을 분리 최적화했습니다.
구현 흐름
- 1EXPLAIN 실행: type: ALL, rows: 전체 행 수 확인 → 풀 스캔 발생 원인 파악
- 2복합 인덱스 설계: (latitude, longitude) 순서 — 위도 범위가 좁아 첫 번째 컬럼에서 필터링 효과 극대화
- 3FK 인덱스 추가: 구역 타입(zone_type_id) 외래키에 인덱스 추가 → JOIN 성능 개선
- 4쿼리 리팩터링: WHERE 조건을 인덱스 활용에 최적화된 형태로 정리
- 5아파트/일반 구역 구분으로 쿼리 패턴 분리 → 각각에 최적화된 실행 계획 유도
- 6인덱스 적용 후 EXPLAIN 재실행: type: ALL → ref/range 전환 확인
Verification
→ 일반 구역 조회 180ms → 23ms(87%↓), 아파트 구역 조회 40ms → 8ms(80%↓)를 달성했습니다.
인덱스 추가 전후 EXPLAIN 결과를 비교하여 풀 스캔이 인덱스 스캔으로 전환된 것을 확인하고, 동일 조건으로 응답 시간을 반복 측정했습니다. 금연구역 226건 + 흡연구역 12건 기준, 안양시 동안구 전체 범위 조회 조건으로 측정했습니다.
180ms → 23ms
일반 구역 조회
87% 응답 시간 단축 (226건 기준)
40ms → 8ms
아파트 구역 조회
80% 응답 시간 단축
ALL → ref/range
EXPLAIN type 변화
풀 스캔 → 인덱스 스캔으로 전환 확인
- EXPLAIN 적용 전: type: ALL, rows: 전체 행 수 — 풀 스캔 발생
- EXPLAIN 적용 후: type: ref/range, key: idx_lat_lng — 복합 인덱스 사용 확인
- 동일 조건(안양시 동안구 전체 범위) 반복 측정으로 수치 검증
- 팀 체크리스트 수립: 조회 조건 컬럼 인덱스 검토를 설계 단계에서 수행하도록 프로세스화
Retrospective
→ 현재 위도/경도 범위 조건으로 처리 중이며, 서비스 확장 시 공간 인덱스 도입을 검토합니다.
한계점
현재 LIKE 검색이나 공간 인덱스(ST_Contains)를 쓰지 않고 위도/경도 범위 조건(BETWEEN)으로 처리 중입니다. 안양시 규모에서는 충분하지만, 전국 규모로 확장되면 데이터 건수 증가에 따라 범위 조건만으로는 성능 한계에 부딪힐 수 있습니다.
보완 방향
서비스가 전국 규모로 확장될 경우 MySQL의 SPATIAL INDEX(R-Tree 기반)를 도입하여 ST_Contains, ST_Within 등 공간 함수 기반 쿼리로 전환하거나, PostGIS 같은 공간 DB로 이관을 검토합니다.