DB 성능 병목 해결의 최적화 전략: 인덱스 설계부터 실행 계획 분석까지

안녕하세요. 이 글에서는 RDBMS 인덱스를 활용한 성능 개선 방법을 설명합니다. MySQL, PostgreSQL, Oracle 등 주요 RDBMS의 공식 개발 문서를 기준으로 인덱스의 동작 원리와 실행 계획 분석 방법, 그리고 실무에서 활용 가능한 인덱스 선택 전략을 정리합니다.

 

인덱스는 단순한 “추가 옵션”이 아니라, 쿼리 성능과 시스템 안정성을 좌우하는 핵심 설계 요소입니다. 이 글에서는 실무와 기술 면접에서 반드시 이해해야 하는 인덱스 개념을 실행 계획 중심으로 설명합니다.

 

---

 

소개 · 배경

 

인덱스는 RDBMS에서 쿼리 성능을 결정하는 가장 중요한 요소 중 하나입니다. MySQL 공식 Reference Manual에 따르면, 인덱스는 테이블의 특정 컬럼 값을 기준으로 데이터를 빠르게 찾기 위해 사용하는 자료구조입니다. 적절한 인덱스가 없으면 대용량 테이블에서 Full Table Scan이 발생하며, 이는 디스크 I/O 증가로 이어져 성능 저하의 직접적인 원인이 됩니다.

 

실무와 기술 면접에서 자주 오해되는 부분은 다음과 같습니다. 인덱스가 존재한다고 해서 항상 사용되는 것은 아닙니다. RDBMS 옵티마이저는 비용 기반(Cost-Based Optimizer)으로 실행 계획을 수립하며, 인덱스를 사용하는 것이 더 비효율적이라고 판단하면 Full Table Scan을 선택합니다. 또한 인덱스는 읽기 성능을 향상시키지만, 쓰기 성능에는 부정적인 영향을 미칩니다. 인덱스가 많을수록 INSERT, UPDATE 시 인덱스 유지 비용이 증가합니다.

 

---

 

실행 계획과 SQL 성능 분석의 출발점

실행 계획의 중요성

MySQL 공식 Reference Manual에 따르면, 실행 계획(Execution Plan)은 옵티마이저가 선택한 쿼리 실행 전략을 의미합니다. 실행 계획을 확인하지 않고 인덱스를 추가하거나 쿼리를 수정하는 것은 근거 없는 튜닝에 가깝습니다.

 

MySQL에서는 EXPLAIN을 통해 실행 계획을 확인할 수 있으며, MySQL 8.0부터는 EXPLAIN ANALYZE를 통해 예상 값이 아닌 실제 실행 결과를 확인할 수 있습니다. 이는 옵티마이저가 추정한 rows와 실제 처리된 행 수의 차이를 분석하는 데 매우 중요합니다.

 

실행 계획에서 확인해야 할 주요 항목은 다음과 같습니다.

• type: 접근 방식이며, ALL은 Full Table Scan, ref나 range는 인덱스 접근을 의미합니다.
• key: 실제로 사용된 인덱스 이름입니다. NULL이면 인덱스를 사용하지 않았음을 의미합니다.
• rows: 옵티마이저가 예상한 검사 행 수입니다.
• Extra: 추가 정보이며, Using index는 커버링 인덱스를 의미합니다.

---

 

DB별 실행 계획 확인 방식

실행 계획의 개념은 모든 RDBMS에서 동일하지만, 표현 방식은 다릅니다.

• MySQL: EXPLAIN, EXPLAIN ANALYZE
• PostgreSQL: EXPLAIN, EXPLAIN (ANALYZE, BUFFERS)
• Oracle: EXPLAIN PLAN, DBMS_XPLAN.DISPLAY

공식 문서에 따르면, PostgreSQL과 Oracle 역시 비용 기반 옵티마이저를 사용하며, 실행 계획 분석은 SQL 튜닝의 출발점이라는 점에서 동일한 원칙을 따릅니다.

 

---

 

Full Table Scan과 Index Scan

PostgreSQL 공식 Documentation에 따르면, 옵티마이저는 테이블 크기와 데이터 분포, 통계 정보를 기반으로 Full Table Scan과 Index Scan 중 비용이 낮은 방식을 선택합니다.

 

인덱스 Scan은 인덱스를 먼저 읽은 후, 해당 ROWID(또는 Tuple ID)를 통해 테이블을 다시 접근합니다. 이 과정에서 랜덤 I/O가 발생합니다. 반면 Full Table Scan은 순차 I/O로 테이블 전체를 읽습니다. 따라서 조회 대상 행이 테이블의 상당 부분을 차지하는 경우, 인덱스 Scan보다 Full Table Scan이 더 효율적일 수 있습니다.

SELECT * FROM orders WHERE status = 'PENDING';

 

status 컬럼에 인덱스가 있더라도, 해당 값이 테이블의 큰 비율을 차지한다면 옵티마이저는 Full Table Scan을 선택할 수 있습니다. 이는 규칙이 아니라 비용 기반 판단 결과입니다.

 

 

---

 

인덱스 자료구조와 동작 원리

B-Tree와 B+Tree

MySQL 공식 Reference Manual에 따르면, InnoDB 스토리지 엔진은 B+Tree 구조를 사용하여 인덱스를 구현합니다. B+Tree는 모든 실제 데이터가 리프 노드에 저장되며, 내부 노드는 키 정보만을 가지는 구조입니다.

 

이 구조의 장점은 다음과 같습니다. 리프 노드가 연결 리스트로 구성되어 있어 범위 검색이 효율적입니다. 내부 노드에 데이터를 저장하지 않기 때문에 한 노드에 더 많은 키를 저장할 수 있고, 이는 트리 높이를 낮춰 디스크 I/O 횟수를 줄입니다.

 

인덱스 탐색 과정

Oracle Database 공식 문서에 따르면, 인덱스 탐색은 루트 노드에서 시작하여 브랜치 노드를 거쳐 리프 노드로 내려가는 과정입니다. 리프 노드에서 키에 해당하는 ROWID를 찾고, 이를 이용해 테이블의 실제 데이터를 조회합니다.

 

인덱스 높이가 3인 경우, 수백만 건의 데이터도 소수의 디스크 I/O로 탐색할 수 있습니다. 이는 대용량 테이블에서 인덱스가 필수적인 이유입니다.

 

---

 

카디널리티와 옵티마이저 판단 기준

카디널리티와 선택도

MySQL 공식 Reference Manual에 따르면, 카디널리티(Cardinality)는 인덱스된 컬럼이 가지는 고유 값의 개수를 의미합니다. 선택도(Selectivity)는 카디널리티를 전체 행 수로 나눈 값이며, 인덱스 효율성을 판단하는 핵심 지표입니다.

 

카디널리티가 높은 컬럼은 인덱스를 사용할 가능성이 높습니다. 예를 들어 user_id는 대부분 고유하므로 인덱스 효율이 높습니다. 반면 gender와 같이 값의 종류가 적은 컬럼은 인덱스를 사용하지 않을 가능성이 큽니다.

 

통계 정보의 중요성

PostgreSQL 공식 Documentation에 따르면, 옵티마이저는 통계 정보에 크게 의존합니다. 통계 정보가 오래되었거나 부정확하면 잘못된 실행 계획이 선택될 수 있습니다. 따라서 ANALYZE를 통해 통계 정보를 최신 상태로 유지하는 것이 중요합니다.

 

---

 

인덱스 선택 전략과 실전 예시

단일 인덱스 vs 복합 인덱스

MySQL 공식 Reference Manual에 따르면, 복합 인덱스는 여러 컬럼을 하나의 인덱스로 구성한 것입니다. 복합 인덱스는 왼쪽부터 순서대로만 활용됩니다.

SELECT * FROM orders
WHERE customer_id = 123
  AND status = 'PENDING'
ORDER BY created_at DESC;

 

이 쿼리에 대해 (customer_id, status, created_at) 인덱스를 생성하면 WHERE 조건과 ORDER BY를 모두 처리할 수 있습니다.

 

---

 

컬럼 순서 결정 기준

복합 인덱스 컬럼 순서는 다음 기준을 따릅니다.

1. 카디널리티가 높은 컬럼을 앞에 배치합니다.
2. 등호 조건 컬럼을 범위 조건 컬럼보다 앞에 둡니다.
3. ORDER BY, GROUP BY 사용 여부를 고려합니다.

이는 MySQL과 PostgreSQL 공식 문서에서 공통적으로 설명하는 인덱스 설계 원칙입니다.

 

---

 

커버링 인덱스 활용

커버링 인덱스는 쿼리에 필요한 모든 컬럼이 인덱스에 포함된 경우를 의미합니다.

SELECT customer_id, status
FROM orders
WHERE customer_id = 123 AND status = 'PENDING';

CREATE INDEX idx_orders_cover
ON orders (customer_id, status);

 

이 경우 MySQL에서는 Using index, PostgreSQL에서는 Index Only Scan이 발생할 수 있으며, 테이블 접근을 피할 수 있어 성능이 크게 향상됩니다.

 

---

 

인덱스가 사용되지 않는 대표 사례

다음과 같은 경우 인덱스가 있어도 사용되지 않습니다.

SELECT * FROM orders WHERE YEAR(created_at) = 2024;

 

컬럼에 함수를 적용하면 인덱스를 사용할 수 없습니다. 대신 범위 조건으로 작성해야 합니다.

 

SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

 

또한 LIKE '%keyword%'처럼 와일드카드로 시작하는 패턴도 인덱스를 사용할 수 없습니다.

 

---

 

실무적 고려사항 및 운영 팁

인덱스는 만능이 아닙니다

MySQL 공식 문서에 따르면, 인덱스는 읽기 성능과 쓰기 성능 사이의 트레이드오프입니다. 읽기가 많은 테이블과 쓰기가 많은 테이블은 서로 다른 인덱스 전략이 필요합니다.

 

운영 환경 인덱스 변경 시 주의사항

MySQL InnoDB에서는 대부분의 경우 온라인 DDL을 지원하지만, 모든 인덱스 변경이 항상 LOCK=NONE으로 수행되는 것은 아닙니다. 테이블 구조와 인덱스 유형에 따라 락이 발생할 수 있으므로 사전 검증이 필요합니다.

 

인덱스 변경 전후에는 반드시 실행 계획을 비교하여 실제 성능 개선 여부를 확인해야 합니다.

 

---

 

맺음말 및 요약

이 글에서는 RDBMS 인덱스를 활용한 성능 개선 방법을 공식 문서를 기준으로 정리했습니다. 인덱스는 단순히 추가하는 것이 아니라, 실행 계획 분석과 데이터 분포 이해를 바탕으로 설계해야 합니다.

 

핵심 포인트는 다음과 같습니다. 실행 계획은 SQL 튜닝의 출발점입니다. 카디널리티와 선택도는 인덱스 효율을 판단하는 기준입니다. 복합 인덱스는 컬럼 순서가 중요하며, 커버링 인덱스를 활용하면 큰 성능 향상을 얻을 수 있습니다. 인덱스는 읽기 성능과 쓰기 성능 사이의 균형 문제입니다.

 

공식 문서를 기반으로 인덱스를 이해하고 실행 계획을 분석한다면, 실무에서도 안정적이고 예측 가능한 성능 개선을 달성할 수 있습니다.