[DB 튜닝] 효과적인 인덱스(Index) 설계를 위한 6가지 핵심 원칙
인덱스 설계 시 고려해야 할 기준과 성능 최적화를 위한 핵심 원칙을 정리한 내용입니다.
1. 높은 카디널리티(Cardinality) 확보
인덱스의 효율성은 데이터의 식별력에 달려 있습니다. 데이터의 중복도가 낮고 고유한 값(Unique)이 많을수록 인덱스의 효과가 좋습니다.
- 효과적인 컬럼: 주민번호, ID, 이메일, 전화번호 (중복이 적어 특정 데이터를 빠르게 식별 가능)
- 비효과적인 컬럼: 성별, 가입여부 (중복도가 높아 인덱스를 거쳐도 다량의 데이터를 읽어야 함)
- 권장 사항: 데이터 분포도(Selectivity)가 10~15% 이내인 컬럼에 적용하는 것이 효율적입니다.
2. 복합 인덱스(Composite Index)의 컬럼 순서 결정
여러 컬럼으로 구성된 인덱스에서는 컬럼의 배치 순서가 성능에 결정적인 영향을 미칩니다. 다음 우선순위를 따르는 것이 좋습니다.
- 1순위:
WHERE절에서 동등 비교(=)로 사용되는 컬럼 - 2순위:
WHERE절에서 범위 조건(>,<,LIKE,BETWEEN)으로 사용되는 컬럼 - 3순위: 두 조건이 모두 동등 비교(
=)일 경우, 카디널리티가 높은(고유한 값이 많은) 컬럼을 선행
예시:
WHERE 부서='개발팀' AND 입사일 >= '20240101'
- 권장:
(부서, 입사일)- 특정 부서를 먼저 필터링한 후 날짜 범위를 검색하므로 효율적임.- 비권장:
(입사일, 부서)- 날짜 범위로 데이터를 먼저 검색한 후 부서를 필터링하므로 비효율적임.
3. 조인(JOIN) 쿼리 수행 시 테이블 역할에 따른 전략
조인 수행 시 각 테이블의 역할(Driving/Driven)에 따라 인덱스 전략을 다르게 수립해야 합니다.
- 선행 테이블 (Driving Table):
- 먼저 액세스되는 테이블입니다.
WHERE절 컬럼에 인덱스를 적용하여 스캔 범위를 최소화하는 것이 중요합니다. - 조인 컬럼의 인덱스 여부는 성능에 큰 영향을 미치지 않습니다.
- 후행 테이블 (Driven Table):
- 나중에 액세스되는 테이블입니다. 조인 연결 컬럼(ON 절)에 반드시 인덱스가 존재해야 합니다.
- 인덱스가 없을 경우 풀 스캔(Full Scan)이 발생하여 성능이 저하됩니다.
조인 컬럼+WHERE 컬럼순으로 복합 인덱스를 구성하면 효율이 극대화됩니다.
4. 커버링 인덱스(Covering Index) 활용
쿼리 성능 개선이 필요한 경우, SELECT 절에 포함된 컬럼까지 인덱스에 포함시키는 방법을 고려할 수 있습니다.
- 원리: 실제 데이터 블록(Table)을 읽지 않고, 인덱스만 스캔하여 필요한 데이터를 모두 조회합니다.
- 효과: 랜덤 I/O(Table Access)가 제거되어 조회 속도가 크게 향상됩니다.
- 주의: 과도하게 많은 컬럼을 인덱스에 포함할 경우 인덱스 크기가 비대해질 수 있으므로, 필수적인 컬럼만 선별하여 적용해야 합니다.
5. 데이터 변경(CUD) 성능 고려
인덱스는 조회(SELECT) 성능을 향상시키지만, 데이터 변경(INSERT, UPDATE, DELETE) 시에는 성능 부하를 유발합니다.
- 원인: 데이터가 변경될 때마다 인덱스의 정렬 상태를 유지하고 위치를 갱신하는 비용이 발생합니다.
- 권장 사항:
- 테이블당 인덱스 개수는 3~5개 내외가 적당합니다.
- 조회 성능과 변경 성능 사이의 균형(Trade-off)을 고려해야 합니다.
- 사용 빈도가 낮거나 불필요한 인덱스는 주기적으로 모니터링하여 삭제하는 것이 좋습니다.
6. 인덱스 사용 시 주의사항 (인덱스 미적용 사례)
다음과 같은 쿼리 패턴은 인덱스를 정상적으로 활용하지 못하므로 주의가 필요합니다.
- 좌변 컬럼 가공:
WHERE UPPER(이름) = 'KIM'과 같이 컬럼을 함수로 감싸거나 연산하면 인덱스를 사용할 수 없습니다. (WHERE 이름 = 'Kim'으로 변경 필요) - 부정형 조건 사용:
!=,<>등의 부정형 조건은 인덱스 효율이 떨어져 풀 스캔을 유발할 수 있습니다. - LIKE 검색 시 와일드카드 선행:
%단어형태의 검색은 인덱스를 타지 않습니다. (단어%형태만 가능) - 데이터 타입 불일치: 문자열 컬럼을 숫자로 비교하는 등 타입이 다를 경우 내부 형변환이 발생하여 인덱스가 적용되지 않습니다.
핵심 요약 체크리스트
인덱스 설계 및 검토 시 다음 사항을 점검하십시오.
- 해당 컬럼의 데이터 중복도가 낮은가? (카디널리티)
-
WHERE절의 동등 비교(=) 조건이 인덱스의 선두에 위치하는가? (순서) - 조인되는 테이블의 외래키(FK) 컬럼에 인덱스가 존재하는가? (조인)
- 잦은 조회가 발생하는 쿼리에 커버링 인덱스 적용이 가능한가? (커버링)
- 테이블에 과도한 수의 인덱스가 생성되어 있지 않은가? (CUD 성능)
