Database Index와 Execution Plan

Overview

Index는 후보 Row를 줄이고 Execution Plan은 Optimizer가 선택한 접근 경로를 보여준다.

핵심 원리

Index가 있어도 선택도, 함수 적용, 형 변환, 통계에 따라 사용되지 않을 수 있다.

flowchart LR; Q[Query] --> O[Optimizer]; O --> I[Index Scan]; O --> T[Table Scan]

실무에서 발생하는 문제

낮은 선택도, 잘못된 복합 Index 순서, 오래된 통계는 Full Scan과 과도한 Row 접근을 만든다. 실행 계획의 예상 Row와 실제 수행 결과가 다른지도 확인한다.

Trade-off

Index는 읽기를 줄이지만 쓰기와 저장 공간, Buffer Pool을 소비한다. 모든 조건에 Index를 추가하지 말고 빈도 높은 Query와 정렬·범위 조건을 기준으로 설계한다.

흔한 오해

Index가 존재한다고 Optimizer가 반드시 선택하는 것은 아니다. 함수 적용, 암시적 형변환, 선행 Column 누락은 활용 범위를 바꾼다.

Interview Questions

• 이 기술이 해결하는 핵심 문제는 무엇인가?
• 내부에서는 어떤 순서로 동작하는가?
• 운영 환경에서 어떤 지표와 실패 모드를 확인해야 하는가?

왜 필요한가

Table 전체를 읽지 않고 필요한 Record 위치를 좁혀 I/O를 줄인다. 실행 계획은 Optimizer가 어떤 Index, Join 순서, 접근 방식을 선택했는지 검증하는 근거다.

내부 동작

InnoDB Secondary Index의 Leaf에는 Primary Key가 저장되어, 필요한 Column이 Index에 없으면 Clustered Index를 다시 조회한다. 복합 Index는 왼쪽 Prefix와 첫 범위 조건 이후 활용 범위를 고려한다. Cardinality 통계로 비용을 추정하므로 데이터 분포 변화가 계획을 바꿀 수 있다.

Example

WHERE tenant_id=? AND status=? ORDER BY created_at DESC LIMIT 20 조건은 (tenant_id, status, created_at) 후보를 검토한다. EXPLAIN ANALYZE로 예상 Row와 실제 Row, Loop, 시간을 비교하고 운영과 비슷한 분포에서 확인한다.

Production Considerations

Slow Query, rows examined/returned 비율, Buffer Pool Hit, Lock 대기를 함께 본다. 새 Index는 Online DDL 가능 여부와 Disk 여유, Replication Lag를 확인하고 중복·미사용 Index를 주기적으로 정리한다.

Related Topics

Database 기초, Transaction과 MVCC, 부하 테스트로 이어진다.