모범답안 — DB 인덱스 심화·쿼리 최적화, 실행계획, 복합 인덱스

난이도: 중

핵심 답변

B-tree가 빠른 이유: 균형 잡힌 정렬 트리라 깊이가 O(log N)으로 매우 얕다(수천만 행도 3~4단계). 키로 루트→리프까지 내려가면 원하는 값에 바로 도달한다. 키가 정렬돼 있으므로 (1) 등호뿐 아니라 범위 검색(>=, BETWEEN)도 시작점만 찾아 리프를 순차 스캔하면 되고, (2) 인덱스 순서 자체가 정렬 순서라 ORDER BY를 추가 정렬 없이 만족시킬 수 있다(LIMIT과 결합 시 상위 N개만 읽고 멈춤).
복합 인덱스 컬럼 순서: (a, b) 인덱스는 "a로 먼저 정렬, 같은 a 안에서 b로 정렬"된 구조다. 그래서 왼쪽부터 연속으로 조건을 줘야 인덱스를 효율적으로 탄다(leftmost prefix). WHERE a=? AND b>?는 a로 위치를 잡고 b 범위를 스캔 → 탄다. WHERE b=?는 선행 컬럼 a가 빠져 b만으로는 인덱스가 정렬돼 있지 않으므로 효율적으로 못 탄다(전체/인덱스 풀 스캔).
커버링 인덱스: 쿼리가 필요로 하는 모든 컬럼이 인덱스 안에 들어 있어, 인덱스만 읽고 테이블 본체(데이터 행)를 다시 조회하지 않아도(no lookup) 되는 인덱스. 디스크 랜덤 액세스를 줄여 빠르다.
실행계획 핵심 지표: 풀 테이블 스캔 여부, 실제 사용된 인덱스(key), 예상/실제 처리 행 수(rows/filtered), 정렬/임시테이블 사용(Using filesort/temporary), 조인 방식.

깊이 있는 설명 (왜, 트레이드오프)

인덱스는 "정렬된 순서"를 파는 자료구조다. 그래서 인덱스로 가속되는 연산은 "정렬 순서를 활용할 수 있는 것"으로 한정된다: 등호, 범위, 선두 일치 LIKE('foo%'), ORDER BY/GROUP BY, MIN/MAX. 반대로 정렬 순서를 깨는 연산(컬럼에 함수 적용 WHERE YEAR(date)=..., 선두 와일드카드 '%foo%', 형변환)은 인덱스를 못 탄다. 이 직관 하나로 대부분의 "왜 인덱스를 안 타나요?"를 설명할 수 있다.

복합 인덱스 설계 원칙. 일반적으로 "등호 조건 컬럼을 앞에, 범위/정렬 컬럼을 뒤에" 둔다. WHERE a=? AND b>? ORDER BY c라면 등호 a가 맨 앞, 그다음 정렬·범위가 충돌하지 않게 배치한다. 범위 조건이 들어간 컬럼 이후의 컬럼은 인덱스 정렬을 더는 활용하지 못한다는 점이 핵심 함정이다. 또 카디널리티(고유값 수)가 높아 선택도가 좋은 컬럼이 앞에 오면 더 일찍 후보를 좁힌다.

커버링 인덱스의 본질. 보조 인덱스로 행을 찾으면 보통 클러스터 키로 한 번 더 테이블을 조회(lookup)한다 — 이게 랜덤 I/O를 유발한다. 필요한 컬럼을 인덱스에 모두 포함시키면(또는 INCLUDE 컬럼으로) lookup을 없애 순차적 인덱스 스캔만으로 끝난다. 단 인덱스가 넓어져 쓰기·공간 비용이 는다. 읽기 가속과 쓰기 비용의 전형적 트레이드오프(문제 2의 인덱스 원리 연장).

옵티마이저와 통계. DB는 통계(히스토그램, 카디널리티 추정)를 보고 실행계획을 고른다. 통계가 낡거나(분포가 바뀜) 데이터가 급증하면 옵티마이저가 인덱스 대신 풀 스캔을 택하는 등 "갑자기 느려짐"이 생긴다.

응용/실무 연결 (게임서버에서)

(a) Q1 인덱스 설계: WHERE guild_id = ? ORDER BY score DESC LIMIT 20 → 복합 인덱스 (guild_id, score)(score는 DESC로 두거나 엔진이 역방향 스캔 지원). 동작: 인덱스에서 guild_id 등호로 구간을 잡으면, 그 구간 안은 이미 score 순으로 정렬돼 있다. 따라서 추가 정렬(filesort) 없이 끝에서부터 20개만 읽고 멈춘다 → ORDER BY와 LIMIT이 동시에 인덱스로 해결된다. (guild_id)만 인덱스하면: guild_id로 멤버를 다 찾은 뒤 **그 전부를 메모리에서 score로 정렬(filesort)**해 상위 20을 골라야 한다. 멤버가 많은 길드일수록 느려진다. 점수까지 커버하려면 (guild_id, score, id) 식으로 커버링을 노릴 수도 있다.

(b) Q3 LIKE '%foo%': 선두 와일드카드라 "어디서 시작하는지"를 알 수 없어 B-tree의 정렬 순서를 활용할 수 없다 → 인덱스 풀 스캔/테이블 스캔이 된다. 대안:

전문검색 인덱스(MySQL FULLTEXT, PostgreSQL GIN/trigram pg_trgm)로 부분 문자열 검색을 인덱싱.
Elasticsearch/검색엔진 분리(닉네임 검색, 자동완성).
접두 검색만 필요하면 'foo%'로 제약해 일반 인덱스 활용.
n-gram 인덱스로 부분 매칭 지원.

(c) "운영에서 갑자기 느려짐" 진단

EXPLAIN/EXPLAIN ANALYZE로 실제 실행계획 확인: 풀 스캔으로 바뀌었는지, 예상 rows가 폭증했는지, Using filesort/temporary가 생겼는지.
통계 갱신(ANALYZE TABLE): 낡은 통계로 옵티마이저가 오판하면 인덱스를 버린다 → 재수집으로 회복.
slow query log/모니터링으로 느린 쿼리·풀 스캔 비율 추적.
흔한 원인: (1) 데이터 증가로 인덱스 없는 쿼리가 표면화, (2) 통계 노후화로 잘못된 플랜, (3) 카디널리티 낮은 컬럼 인덱스가 무용, (4) 함수/형변환으로 인덱스 무력화, (5) 인덱스가 메모리에 안 올라와 디스크 I/O 폭증, (6) 락 경합/잠금 대기(인덱스와 무관한 느림).

흔한 오답·함정

"인덱스만 걸면 무조건 빨라진다": 선두 와일드카드·함수 적용·낮은 카디널리티에선 무용하거나 옵티마이저가 무시한다.
복합 인덱스 컬럼 순서를 임의로: leftmost prefix와 등호-먼저/범위-나중 원칙을 어기면 정렬·범위 최적화를 잃는다.
WHERE b=?가 (a,b)를 탄다고 단정: 선행 a가 없으면 효율적으로 못 탄다.
ORDER BY를 인덱스로 못 없애는 설계: filesort가 대용량에서 병목이 된다.
커버링을 위해 인덱스에 컬럼 과다 포함: 쓰기/공간 비용이 폭증한다.

꼬리질문 대비