인덱스 설계와 선택도

핵심 요약

대표적인 B-tree 인덱스는 브랜치 노드에서 탐색 범위를 좁히고 리프 엔트리에서 키와 행 위치를 찾는다. 인덱스가 유리한지는 “값 종류가 많다” 하나로 결정되지 않는다. 조건이 반환하는 행 비율, 복합 조건, 테이블 물리적 군집성, 정렬·조인 요구, DML 빈도와 인덱스 폭을 함께 본다. 복합 인덱스의 컬럼 순서는 가장 선택도가 높은 컬럼을 무조건 앞에 둔다는 규칙이 아니라 실제 SQL 묶음과 선두 컬럼 사용성을 기준으로 결정한다.

학습 목표

• B-tree 인덱스의 브랜치·리프·행 위치 구조를 설명한다.
• 카디널리티, NDV 비율, 조건 선택도를 구분하고 계산한다.
• 복합 인덱스의 선두 컬럼·등치·범위·정렬 조건을 분석한다.
• 중복 인덱스·과도한 포함 컬럼·DML 비용·군집성을 검토한다.

1. 인덱스의 구조와 비용

1.1 B-tree의 기본 구조

루트/브랜치 블록
  ├─ 키 범위 A → 하위 블록
  ├─ 키 범위 B → 하위 블록
  └─ 키 범위 C → 하위 블록
                 ↓
리프 블록: 정렬된 키 + 행 위치(또는 PK/포인터)

균형 트리이므로 일반적으로 모든 리프는 비슷한 깊이에 있다. 검색은 루트·브랜치를 거쳐 대상 리프 범위를 찾고, 필요한 경우 테이블 행을 추가로 읽는다. DBMS와 인덱스 종류에 따라 리프에 저장되는 행 위치·PK·포함 컬럼과 스캔 방식은 다르다.

1.2 이득

• 등치·범위 조건의 후보 행 축소
• 조인키 탐색
• 인덱스 순서를 활용한 정렬·최소/최대 조회
• 필요한 컬럼이 인덱스에 있을 때 테이블 접근 감소 가능
• 유일 인덱스를 통한 유일성 구현

1.3 비용

• 인덱스 저장공간(제품에 따라 세그먼트 등)과 캐시 공간
• INSERT 시 엔트리 추가와 블록 분할 가능성
• UPDATE 시 인덱스 키 변경
• DELETE 시 엔트리 제거·정리
• 통계 수집과 재구성·백업 시간
• 인덱스가 많을수록 옵티마이저 후보와 운영 복잡성 증가

조회 SQL 하나를 빠르게 하려고 모든 조건 컬럼에 인덱스를 만들면 전체 DML과 저장 비용이 악화될 수 있다.

2. 카디널리티와 선택도

용어는 교재·제품 문서마다 다르게 쓰일 수 있으므로 식을 먼저 확인한다. 이 단원에서는 다음처럼 구분한다.

2.1 행 수와 NDV

• 테이블 행 수 N: 전체 행 개수
• 컬럼 NDV(Number of Distinct Values): 서로 다른 값의 개수
• NDV 비율: NDV / N

주문번호가 1,000,000행에서 모두 유일하면 NDV=1,000,000, NDV 비율=1이다. 상태코드가 5종이면 NDV=5, NDV 비율=0.000005다.

2.2 조건 선택도

이 단원에서 조건 선택도(predicate selectivity)는 다음으로 정의한다.

조건 선택도 = 조건 예상 반환 행 수 / 전체 행 수

값이 작을수록 적은 행을 선택하므로 더 선택적인 조건이다.

예:

전체 1,000,000행
주문번호='O1' → 1행 예상 → 선택도 0.000001
상태='완료' → 900,000행 예상 → 선택도 0.9

일부 한국어 자료는 NDV/N 또는 그 역개념을 “선택도”라고 부르기도 한다. 시험이나 제품 문서에서는 수식과 ‘높을수록/낮을수록 유리’의 문맥을 반드시 확인한다.

2.3 균등 분포 가정의 한계

NDV가 100이라고 각 값이 1%씩 존재하는 것은 아니다. 상위 1개 값이 90%를 차지할 수 있다. 히스토그램·확장 통계·실제 표본 등 제품 기능을 활용해 편향과 컬럼 상관관계를 확인한다.

3. 복합 인덱스 설계

3.1 선두 컬럼과 접두 사용

인덱스 (A, B, C)는 A부터 정렬되고 그 안에서 B, C가 정렬된다. 일반적인 B-tree에서 선두 A 조건이 없으면 탐색 범위를 효율적으로 제한하기 어려울 수 있지만, 일부 DBMS는 스킵 스캔·비트맵 결합 등 다른 접근을 사용할 수 있다. 보편 규칙은 “선두 컬럼을 사용하는 SQL이 주요 워크로드인지”를 확인하는 것이다.

3.2 등치와 범위 조건

대표 경험칙은 여러 등치 조건을 앞쪽에, 첫 범위 조건을 그 뒤에 두는 것이다.

WHERE CUSTOMER_ID = :customer_id
  AND ORDER_DATE >= :from_date
  AND ORDER_DATE <  :to_date
ORDER BY ORDER_DATE DESC

후보:

(CUSTOMER_ID, ORDER_DATE)

고객 등치로 범위를 좁힌 뒤 주문일자 범위를 연속 탐색하고 정렬 요구도 일부 활용할 수 있다. 그러나 다른 핵심 SQL이 주문일자만 사용한다면 별도 또는 다른 순서가 필요할 수 있다.

3.3 “고선택도 우선”의 한계

컬럼 순서는 다음을 함께 본다.

1. 어떤 조건 조합이 가장 자주 사용되는가?
2. 등치·범위·LIKE·IS NULL 중 어떤 연산인가?
3. 선두 컬럼만 사용하는 다른 SQL이 있는가?
4. 조인·정렬·GROUP BY 순서를 활용할 수 있는가?
5. 반환 건수와 데이터 편향은 어떤가?
6. 컬럼 폭과 DML 변경 빈도는 어떤가?

유일한 주문번호를 모든 복합 인덱스의 선두로 둘 필요는 없다. 주문번호 단건 조회는 PK 인덱스로 해결되고, 고객별 기간 조회는 (고객번호, 주문일자)가 별도 목적을 가진다.

3.4 포함·커버링과 폭

조회에 필요한 컬럼을 인덱스가 모두 보유하면 테이블 접근을 줄일 수 있다. 그러나 포함 컬럼이 많으면 인덱스 크기·캐시·DML 비용이 증가한다. 제품별 INCLUDE 문법과 인덱스 전용 스캔 조건은 다르므로 기능 예시는 보편 규칙과 구분한다.

4. 스캔 방식과 군집성

군집성(clustering)은 인덱스 키 순서와 테이블 행의 물리 배치가 얼마나 가까운지를 뜻한다. 같은 키 범위의 행이 가까우면 범위 조회에서 테이블 블록 재방문이 줄 수 있다. Oracle의 clustering factor처럼 특정 수치는 제품별 방향과 범위가 있으므로 “값이 높으면 무조건 좋다”라고 일반화하지 않는다.

5. 중복·유사 인덱스 검토

다음 후보가 있다고 하자.

I1 (고객번호)
I2 (고객번호, 주문일자)
I3 (고객번호, 주문일자, 주문상태코드)

I2·I3가 I1의 용도를 대체할 수 있는지 검토한다. 하지만 다음 때문에 자동 삭제할 수는 없다.

• I1이 훨씬 작아 특정 단건·조인에서 유리할 수 있음
• I2·I3의 통계·폭·클러스터링이 다름
• 유일성 또는 FK 지원 목적이 다름
• 제품의 옵티마이저·압축·포함 컬럼 동작 차이

핵심 SQL 실행계획, 논리·물리 I/O, DML 비용으로 검증한 뒤 제거한다.

6. 인덱스 설계 절차

상위 SQL·목표·호출빈도 수집
  ↓
조건·조인·정렬·반환컬럼·예상행 분석
  ↓
N·NDV·편향·컬럼 상관·증가율 확인
  ↓
단일/복합 인덱스와 컬럼 순서 후보 작성
  ↓
기존 PK·UK·FK·유사 인덱스와 중복 검토
  ↓
읽기 I/O·응답시간 + DML·공간 비용 측정
  ↓
실제 부하·데이터 변화 후 재검증

7. 사례

테이블 ORDERS 10,000,000행:

• ORDER_ID: 유일
• CUSTOMER_ID: 500,000개 값
• ORDER_STATUS_CODE: 5개 값, 완료가 92%
• ORDER_DATE: 3년 분포

핵심 SQL:

SELECT ORDER_ID, ORDER_DATE, ORDER_AMOUNT
FROM ORDERS
WHERE CUSTOMER_ID = :customer_id
  AND ORDER_DATE >= :from_date
  AND ORDER_DATE < :to_date
ORDER BY ORDER_DATE DESC;

후보 (CUSTOMER_ID, ORDER_DATE)는 고객 등치와 날짜 범위·정렬에 맞는다. ORDER_STATUS_CODE가 값 종류가 적다는 이유만으로 무조건 제외하는 것도, 선두에 두는 것도 옳지 않다. “고객의 미완료 주문만” 자주 조회하고 미완료가 매우 적다면 (CUSTOMER_ID, ORDER_STATUS_CODE, ORDER_DATE) 또는 제품별 조건부 인덱스를 비교할 수 있다. 실제 분포와 SQL 묶음으로 검증한다.

8. 비교와 구분

시험 판단 포인트

• 인덱스는 읽기 성능과 저장·DML 비용의 교환이다.
• 조건 선택도는 정의식에 따라 높고 낮음의 의미가 달라질 수 있으므로 식을 확인한다.
• 복합 인덱스는 주요 SQL의 선두 컬럼 사용, 등치·범위, 정렬·조인, 분포를 함께 본다.
• 낮은 NDV 컬럼도 다른 조건과 결합되거나 특정 제품의 읽기 중심 구조에서는 유용할 수 있다.
• WHERE 절의 모든 컬럼을 인덱스에 넣는 것이 목표가 아니다.
• 유사 인덱스 제거는 실행계획·I/O·DML 비용을 측정한 뒤 결정한다.

자주 틀리는 부분

• “선택도가 높다/낮다”는 표현만 외우고 계산식을 확인하지 않는다.
• 가장 유일한 컬럼을 모든 복합 인덱스의 첫 컬럼으로 둔다.
• 컬럼 순서를 SQL WHERE 절 작성 순서와 동일하게 해야 한다고 본다.
• 낮은 NDV라는 이유만으로 어떠한 조합에서도 인덱스가 쓸모없다고 단정한다.
• 조회 성능만 측정하고 대량 INSERT·UPDATE·DELETE 비용을 제외한다.
• 인덱스 전용 스캔·스킵 스캔·비트맵을 모든 DBMS의 동일 기능으로 설명한다.