설문 관리 서비스 질문 조회 최적화 사례

설문 생성·관리 서비스에서 특정 설문의 질문을 불러올 때, 삭제된 질문(is_deleted = 0)을 제외하고 빠르게 조회해야 했습니다.

또한, 본 서비스는 is_deleted 플래그로 소프트 삭제를 관리하므로 초기 설계 단계에서 “검색 조건마다 단일 인덱스를 걸어두면 충분하다”는 가정하에 survey_document_id와 is_deleted 컬럼에 각각 단일 인덱스를 추가했습니다.

그러나 운영 환경에서는 is_deleted 인덱스를 전혀 사용하지 않아, 대량 트래픽 상황에서 구조적 병목이 발생했습니다.

---

1. 왜 단일 인덱스를 걸었나 & 기대 효과

테이블: question_document

조회 조건:

• survey_document_id = 4
• is_deleted = 0

단일 인덱스

ALTER TABLE question_document
  ADD INDEX (survey_document_id),
  ADD INDEX (is_deleted);

1. survey_document_id

   • 이유: 설문별로 질문을 그룹핑하기 위한 FK 컬럼.
   • 기대: “survey_document_id = X” 조건에 대해 바로 B‑Tree 검색이 가능.

2. is_deleted

   • 이유: 삭제 여부 필터링이 빈번.
   • 기대: “is_deleted = 0” 조건에도 별도 인덱스로 빠른 필터링.

3. 가정:

   • MySQL 옵티마이저가 인덱스 머지(index merge) 기법으로
     survey_document_id와 is_deleted를 조합해 사용할 것이라 기대

2. 단일 인덱스 실행 계획 분석

EXPLAIN
SELECT *
  FROM question_document
 WHERE survey_document_id = 4
   AND is_deleted = 0;

id	table	type	possible_keys	key	rows	Extra
1	question_document	range	survey_document_id, is_deleted	survey_document_id	300	Using where

• possible_keys에 survey_document_id, is_deleted 모두 보이지만

• 실제 사용된 key는 survey_document_id만 사용

• is_deleted는 후처리(Using where)로만 적용되어 약 300건 중 52건을 필터링

• 원인:

  • MySQL 옵티마이저가 인덱스 머지 비용을 높게 평가해 단일 인덱스만 선택

  • is_deleted는 값이 0/1 두 가지뿐이라 선택도(selectivity)가 낮아, 단독 사용 가치가 작음

• rows examined: 300

• rows returned: 52

• actual time: 0.0457 – 0.25 ms (≈ 0.15 ms)
  → 절반 이상이 후처리 비용으로 소요

3. 복합 인덱스 설계 배경

• 문제: 단일 인덱스 조합이 불발 → 두 인덱스를 따로 스캔한 뒤 교집합(후처리)

• 해결: 두 컬럼을 한 번에 처리하는 복합 인덱스 필요

• 인덱스 머지 vs. 복합 인덱스

• 머지: 두 인덱스를 “합” 또는 “교집합” → 추가 I/O

• 복합: 하나의 인덱스에서 바로 두 조건을 모두 만족 → 최소 I/O

4. 복합 인덱스 설계 & 컬럼 순서 이유

두 조건을 한 번에 처리하도록 인덱스를 재설계합니다.

ALTER TABLE question_document
  DROP INDEX `is_deleted`,
  DROP INDEX `survey_document_id`,
  ADD INDEX idx_survey_deleted (survey_document_id, is_deleted);

1. survey_document_id 먼저

   • 높은 카디널리티(고유값 수): 수천 개 설문 중 하나

   • 먼저 대량 후보군을 좁힌 뒤

2. is_deleted 다음

   • 낮은 카디널리티(0/1): 추가 필터 단계

   • 이미 걸러진 설문 내에서 삭제 여부만 검사

만약 순서를 바꿔 (is_deleted, survey_document_id) 로 만들면

• is_deleted=0 조건에 걸린 모든 질문(수만 건)을 먼저 스캔 →
• 뒤편 survey_document_id 절이 후처리 →
  → 효율이 떨어집니다.

5. 복합 인덱스 후 실행 계획 & 성능

EXPLAIN ANALYZE
SELECT *
  FROM question_document
 WHERE survey_document_id = 4
   AND is_deleted = 0;

항목	복합 인덱스 전	복합 인덱스 후
rows examined	300	52
rows returned	52	52
actual time	0.0457 – 0.25 ms	0.0302 – 0.0699 ms
평균 응답 속도	≈ 0.15 ms	≈ 0.05 ms
성능 개선 비율	—	×3

• 300 → 52건만 스캔

• 0.15 ms → 0.05 ms, 약 3배 성능 개선

6. 마무리 및 권장 사항

• 본 서비스는 is_deleted 플래그로 소프트 삭제를 관리하므로, 삭제 여부를 자주 조회하는 모든 테이블에 복합 인덱스를 설정하였습니다.

• 복합 인덱스는 여러 검색 조건을 한 번에 처리해 후보군을 극소화합니다.

• 인덱스 순서는 동등 비교 컬럼을 앞에, 부가 필터 컬럼을 뒤에 배치하세요.

• 단일 인덱스가 사용되지 않는다면 EXPLAIN/EXPLAIN ANALYZE로 실제 실행 계획을 반드시 확인해야 합니다.

• 인덱스는 쓰기 비용을 증가시키므로, 진짜 병목 쿼리에만 적용해 운영 효율을 극대화하세요.

이처럼 실측 기반 분석으로 “어떤 인덱스를 왜 걸었고, 실제로 얼마나 개선됐는지”를 명확히 제시하면, 운영 중인 시스템에서도 자신 있게 성능 최적화를 진행할 수 있습니다.