인덱스·클러스터링·파티션

세 개념의 역할부터 구분하기

인덱스·클러스터링·파티션은 모두 물리 데이터베이스 설계에 사용되지만 해결하는 문제가 다르다.

세 구조가 함께 사용되면 질의 처리는 다음처럼 이해할 수 있다.

파티션 키 조건으로 읽지 않을 파티션 제거
                ↓
남은 파티션 안에서 인덱스로 후보 범위 탐색
                ↓
필요하면 테이블 행 접근
                ↓
클러스터링 상태가 좋으면 관련 행이 가까워 페이지 접근이 줄 수 있음

파티션 프루닝(Partition Pruning)과 인덱스 탐색은 같은 동작이 아니다. 프루닝은 파티션 경계와 조건을 비교해 조각 전체를 제외하고, 인덱스는 남은 객체 안에서 검색 키를 이용해 행 후보를 좁힌다.

이 이론에서 파티션은 한 DBMS 안의 테이블·인덱스 파티션을 중심으로 다룬다. 여러 노드에 데이터를 나누는 분산 데이터베이스의 분할·복제·투명성은 다음 이론의 범위다.

인덱스의 기본 원리

인덱스(Index)는 검색에 사용할 값인 검색 키(Search Key)와 행 위치 정보 또는 행 데이터를 조직한 접근 구조다. 여기서 검색 키는 후보키·기본키와 같은 관계 모델의 키일 필요가 없다. 중복이 많은 status도 인덱스 검색 키가 될 수 있다.

일반적인 보조 인덱스 탐색은 다음 과정을 거친다.

1. 조건에 맞는 검색 키 범위를 찾는다.
2. 인덱스 엔트리에서 행 위치 또는 행 식별자를 얻는다.
3. 필요한 열이 인덱스에 없으면 테이블 행을 추가로 읽는다.
4. 조건을 다시 확인하고 결과를 반환한다.

인덱스가 없거나 인덱스 사용 비용이 더 크면 DBMS는 테이블 전체 또는 넓은 범위를 순차적으로 읽을 수 있다. 인덱스가 존재한다고 반드시 사용되는 것은 아니다.

인덱스가 유리한 경우와 불리한 경우

인덱스가 유리할 가능성이 큰 조건은 다음과 같다.

• 큰 테이블에서 적은 수의 행을 찾는 등가 조건
• 연속된 일부 구간을 찾는 범위 조건
• 조인 열을 이용해 상대 행을 반복해서 찾는 경우
• 인덱스 순서가 ORDER BY 또는 그룹 처리 순서와 맞는 경우
• 질의에 필요한 열을 인덱스만으로 충족할 수 있는 경우

반대로 다음 상황에서는 전체 스캔이 더 저렴할 수 있다.

• 테이블이 매우 작음
• 대부분의 행을 반환함
• 인덱스에서 찾은 뒤 테이블의 여러 페이지를 무작위로 읽어야 함
• 조건식이 인덱스 구조와 맞지 않음
• 통계가 실제 분포를 반영하지 못함
• 인덱스 유지 비용이 읽기 이득보다 큼

인덱스는 SELECT만을 위한 구조가 아니다. UPDATE, DELETE, 조인 대상 탐색에도 활용될 수 있다. 그러나 행을 추가·변경·삭제할 때 관련 인덱스도 함께 갱신해야 하므로 쓰기 비용과 로그량이 늘어난다.

선택도와 카디널리티의 표현을 혼동하지 않기

인덱스 문제에서 선택도(Selectivity)라는 말은 자료마다 표현 방향이 달라 혼동을 일으킨다. 계산식을 먼저 확인하는 것이 안전하다.

선택 비율 = 조건을 만족하는 행 수 / 전체 행 수

100만 행에서 고유 이메일 한 건을 찾으면 다음과 같다.

1 / 1,000,000 = 0.000001 = 0.0001%

선택 비율이 매우 낮아 적은 행만 반환하므로 일반적으로 인덱스에 유리하다. 반면 상태값 두 종류 중 하나가 60만 행이라면 선택 비율은 60%이고, 테이블 전체를 읽는 계획이 더 저렴할 수 있다.

실무 문서의 “선택성이 높은 조건(highly selective predicate)”은 흔히 적은 행을 잘 골라내는 조건을 뜻한다. 이는 위 계산식의 수치가 높다는 뜻과 반대처럼 보일 수 있다. 따라서 시험에서는 다음을 구분한다.

• 선택 비율: 결과 행 수 ÷ 전체 행 수
• 서로 다른 값의 수: 열의 NDV 또는 열 카디널리티라고 부를 수 있음
• 실행계획의 카디널리티: 연산 결과로 예상되는 행 수를 뜻할 수 있음

“선택도가 높으면 무조건 인덱스가 유리하다”처럼 정의 없이 방향만 단정한 선지는 주의해야 한다.

B-tree 계열과 주요 인덱스 유형

B-tree와 B+ tree

B-tree 계열은 키를 정렬 상태로 유지하는 균형 다분기 트리다. 루트에서 내부 노드를 거쳐 리프까지 내려가므로 트리 높이가 과도하게 치우치지 않는다.

B+ tree는 일반적으로 내부 노드에는 탐색용 키와 자식 포인터를 두고, 데이터 엔트리는 리프에 모으며 리프를 순서대로 연결한다. 리프 연결은 범위 스캔과 순차 탐색에 유리하다. 제품 문서가 구조 전체를 관용적으로 B-tree라고 부르면서 실제 rowstore 구현을 B+ tree로 설명하기도 하므로, 시험 개념과 제품 용어를 구분한다.

해시 인덱스가 있다고 BETWEEN, <, ORDER BY를 같은 방식으로 처리할 수 있는 것은 아니다. 비트맵 인덱스는 서로 다른 값의 비율이 낮거나 중간인 열을 여러 조건으로 결합하는 읽기 중심 분석 업무에서 유리할 수 있지만, OLTP에서 변경이 빈번하면 적합하지 않을 수 있다.

밀집·희소 인덱스

고전적인 파일 조직 관점에서는 인덱스 엔트리의 촘촘함에 따라 다음처럼 구분한다.

• 밀집 인덱스(Dense Index): 모든 레코드 또는 모든 검색 키 값에 대응하는 엔트리를 촘촘히 둔다.
• 희소 인덱스(Sparse Index): 일부 경계 키나 블록 대표 키만 엔트리로 둔다.

희소 인덱스는 엔트리 수를 줄일 수 있지만, 대표 엔트리로 이동한 뒤 해당 블록이나 구간을 추가 탐색해야 한다. 또한 검색 키 순서로 데이터가 조직되어 있어야 구간을 찾아갈 수 있다. 중복 키의 포인터 목록·버킷 처리처럼 세부 구조는 교재와 DBMS 구현에 따라 달라질 수 있다.

좌우로 이동해 그림을 확인하세요.그림 크게 보기

B+ 트리의 탐색과 리프 연결

루트의 분리 키 40·70은 오른쪽 자식의 최소값으로 가정한다. 리프 10·20·30, 40·50·60, 70·80·90은 각각 k<40, 40≤k<70, 70≤k 구간이다. 키 50은 가운데 리프에서 찾고 45≤k<80은 리프 연결을 따라 50·60·70을 얻는다. 모든 리프 깊이는 같다.

복합 인덱스의 열 순서

복합 인덱스는 여러 열을 순서대로 묶는다. (customer_id, ordered_at)은 고객 번호로 먼저 정렬하고 같은 고객 안에서 주문일을 정렬하는 사전식 순서다.

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, ordered_at);

선두 열과 등가·범위 조건을 함께 확인한다. 선두 열이 없으면 모든 DBMS에서 절대 사용할 수 없다는 식의 단정은 피하되, 예외 최적화보다 기본적인 정렬 순서를 먼저 이해한다. 열 순서는 값의 종류 수뿐 아니라 주요 WHERE·JOIN·ORDER BY 조건을 보고 결정한다.

질의에 필요한 열이 모두 인덱스에 들어 있으면 테이블의 추가 읽기를 줄일 수 있으며 이를 해당 질의를 커버한다고 한다. 커버링은 특정 질의에 필요한 열을 충족한다는 뜻이지 별도의 트리 구조 이름은 아니다.

클러스터링

클러스터링은 같은 키값 또는 함께 조회할 관련 행을 물리적으로 가까이 저장하는 것이다. 범위나 여러 관련 행을 읽을 때 페이지 접근을 줄일 수 있다. 데이터 배치를 바꾸므로 삽입·갱신과 유지 비용도 고려한다.

고전적인 정렬 파일 용어에서 기본 인덱스는 기본키 정렬, 클러스터링 인덱스는 비키 정렬 필드, 보조 인덱스는 물리 정렬과 다른 검색 필드에 대한 인덱스를 뜻한다. 이것을 모든 제품의 PRIMARY KEY·CLUSTER 명령과 일대일로 동일시하지 않는다.

제품마다 재배치 후 순서를 계속 유지하는 방식이 다르다. 관련 행이 가깝게 저장되어도 ORDER BY 없는 결과 순서를 보장하지 않는다. 기본키라는 논리적 식별 규칙과 물리적인 클러스터링 기준도 별개다.

파티션의 구조와 유형

테이블 파티셔닝(Table Partitioning)은 하나의 논리적 테이블을 파티션 키 규칙에 따라 여러 물리 조각으로 나누는 방법이다. 애플리케이션은 일반적으로 부모 또는 논리 테이블을 대상으로 질의하지만 DBMS는 각 행을 알맞은 파티션에 저장한다.

범위 경계는 반열린 구간으로 확인하기

월별 파티션을 다음처럼 정의하면 9월 파티션은 시작값을 포함하고 종료값은 포함하지 않는다.

2026-09 파티션: 2026-09-01 이상, 2026-10-01 미만
2026-10 파티션: 2026-10-01 이상, 2026-11-01 미만

이 방식은 경계 날짜가 두 파티션에 중복되거나 어느 파티션에도 들어가지 않는 문제를 줄인다. 정확한 경계 포함 규칙, 기본 파티션, 범위 밖 INSERT 처리와 파티션 키 변경 시 행 이동은 DBMS 문법을 확인한다.

파티션 프루닝과 설계 기준

파티션 프루닝은 질의 조건과 경계를 보고 필요 없는 파티션을 읽기 대상에서 제외하는 것이다. 월별 파티션에서 9월만 조회하면 다른 달 파티션을 건너뛸 수 있다. 프루닝 뒤 남은 파티션에서 인덱스를 사용할지는 별도 판단이다.

프루닝은 파티션 키와 경계에 기반하므로 인덱스가 없다고 반드시 불가능한 것은 아니다. 반대로 파티션을 나누었다고 모든 질의에서 프루닝되는 것도 아니다.

파티션 키는 조회 조건, 보관·삭제 단위, 데이터 분포와 증가율을 보고 선택한다. 파티션 수가 지나치게 많으면 계획·관리 비용이 늘고, 한 파티션에 데이터가 집중되면 분산 효과가 줄어든다. 파티션과 여러 서버에 데이터를 배분하는 샤딩을 같은 뜻으로 단정하지 않는다.

좌우로 이동해 그림을 확인하세요.그림 크게 보기

파티션 프루닝과 인덱스는 다른 단계

2026-02-01 이상 2026-03-01 미만 조건은 1·3월 파티션을 제외하고 2월을 남긴다. 그 안에서 전체 스캔과 인덱스 비용을 비교한다. 프루닝에 인덱스가 반드시 필요한 것은 아니다.

비트맵·해시·파티션의 선택 조건

비트맵 인덱스는 속성 값별로 해당 행 여부를 비트열로 표현한다. 서로 다른 값의 수가 비교적 적고 읽기·분석 중심인 환경에서 여러 조건의 비트맵을 AND·OR로 결합하는 데 적합할 수 있다. 그러나 카디널리티가 낮다는 조건 하나만으로 모든 업무에 최적이라고 판단하지 않는다. 빈번한 갱신과 동시 쓰기에서는 유지·경합 비용도 확인한다.

NULL이 없고 각 값별로 모든 행에 1비트씩 두는 비압축 비트맵이라면 순수 비트 수는 값 종류 수×행 수다. 헤더·압축·행 식별자 등 부가 정보는 별도다.

비트열은 동일한 행 순서에 맞추어 읽는다. 예를 들어 A조건 1001, B조건 0110, 활성조건 1100이면 (A OR B) AND 활성은 1100으로 1·2번 행을 남긴다. OR는 한쪽이라도 1, AND는 양쪽 모두 1인 위치를 남긴다.

해시 함수 h(k)=k mod 4에서 6과 10은 모두 버킷 2에 대응한다. 이는 해시 충돌이지 두 원래 키가 같은 뜻이 아니다. 반대로 해시 순서가 원래 키 순서를 유지한다고도 보장하지 않는다.

목록 파티션은 지역 코드처럼 열거한 값 집합을 기준으로 분할한다. 범위 파티션은 경계 구간, 해시 파티션은 해시 결과를 기준으로 한다. Oracle의 로컬 파티션 인덱스는 각 인덱스 파티션이 대응 테이블 파티션의 행을 다루도록 구성된다. 글로벌 인덱스는 테이블 파티션 경계를 가로지를 수 있다는 구분이 핵심이며 모든 제품의 인덱스 이름을 같다고 일반화하지 않는다.

관련 행을 물리적으로 가까이 배치하는 데이터 클러스터링과 여러 서버의 가용성을 위한 클러스터 구성은 문맥이 다르다. 조건 결과 비율이 100÷100000=0.1%처럼 작으면 인덱스를 검토할 근거는 되지만, 인덱스가 반드시 선택되거나 항상 가장 빠르다는 결론은 아니다.