데이터베이스에 사용되는 인덱스(Index)에 대해 알아보겠습니다.

Ⅰ. 인덱스(Index) 

 데이터베이스 인덱스는 테이블 내 데이터를 효율적으로 검색하기 위한 보조 자료구조로, 특정 컬럼의 값을 기준으로 해당 레코드의 물리적 주소를 함께 저장하고 정렬하여 관리합니다. 이는 도서 색인(Index)과 유사하게, 검색 속도 향상을 위한 구조적 수단이며, 인덱스가 없는 경우에는 전체 테이블을 순차 탐색(Full Table Scan)해야 하므로 대용량 데이터에서 O(N)의 시간 복잡도로 인해 심각한 성능 저하를 유발할 수 있습니다.

 

ⅰ. 인덱스(Index)  내부구조 : B-tree

 대부분의 관계형 데이터베이스(RDBMS)에서 인덱스는 B-Tree(Balanced Tree) 또는 변형 구조인 B+Tree를 사용합니다. B-Tree는 루트 노드, 중간 노드, 리프 노드로 구성되며, 각 노드는 정렬된 키 값과 자식 노드 포인터를 가집니다. 검색 시 루트 노드에서 시작하여 키 값을 비교하면서 적절한 하위 노드로 이동하고, 리프 노드에 도달하면 해당 키에 대한 레코드 위치(ROWID 또는 페이지 포인터)를 반환합니다.

B-Tree 구조는 균형 잡힌 트리 구조로 O(log N)의 검색 성능을 제공하며, 이는 인덱스를 사용하는 주요 이유 중 하나입니다.

 

ⅱ. 인덱스(Index)  장·단점

구분	장점	단점
성능	WHERE, JOIN, ORDER BY, GROUP BY 등 다양한 SQL 구문에서 성능 향상	DML(INSERT, UPDATE, DELETE) 연산 시 인덱스 유지 비용 발생
무결성	PRIMARY KEY 및 UNIQUE 제약 조건의 효율적인 무결성 검사	과도한 인덱스 생성 시 오히려 성능 저하 유발 가능
응답 속도	데이터 접근 시간 단축으로 시스템 응답성 향상	추가적인 저장 공간 요구

Ⅱ. 클러스터드 인덱스(Clustered Index)  란?

  테이블의 실제 데이터가 인덱스 키의 순서에 따라 물리적으로 정렬되어 저장되는 인덱스입니다. 따라서 클러스터드 인덱스의 리프 노드는 인덱스 엔트리가 아닌 실제 데이터 자체를 포함합니다. 테이블당 하나의 클러스터드 인덱스만 존재할 수 있으며, 일반적으로 PRIMARY KEY 제약을 지정하면 클러스터드 인덱스로 자동 생성됩니다.

 

ⅰ. 클러스터드 인덱스(Clustered Index)  구조

 클러스터드 인덱스도 B-Tree 구조를 따르며, 인덱스 노드는 키와 데이터 페이지 포인터를 포함합니다. 리프 노드는 전체 테이블 데이터를 키 순서로 저장하며, 따라서 별도의 테이블 힙 영역이 존재하지 않습니다.

 

ⅱ. 클러스터드 인덱스(Clustered Index) 성능 특성

 1) 범위 검색(BETWEEN, >, < 등)에 매우 효율적

 2) 정렬 연산(ORDER BY, GROUP BY)에서 추가 작업 없이 빠른 성능

 3) 대량 데이터 처리에 유리

그러나 데이터 삽입 시 정렬 순서 유지를 위한 페이지 분할(Split), 재배치가 발생할 수 있으며 이는 쓰기 성능 저하로 이어집니다.

 

ⅲ. 클러스터드 인덱스(Clustered Index) PRIMARY KEY 설계 전략

 

일반적으로 AUTO_INCREMENT 또는 SEQUENCE 기반의 정수형 ID를 클러스터드 인덱스의 기준 키로 사용합니다. 이는 순차적인 데이터 삽입을 유도하여 페이지 분할을 최소화하고, 삽입 성능을 높이는 효과가 있습니다. 반대로 이메일 주소나 UUID 등 비정형 문자열을 키로 설정하면 불균형 분산과 잦은 페이지 분할이 발생하여 성능 저하를 유발할 수 있습니다.

Ⅲ. 논클러스터드 인덱스 (Non-Clustered Index)

 ⅰ. 논클러스터드 인덱스 (Non-Clustered Index) 란?

 논클러스터드 인덱스는 테이블의 물리적 저장 순서와는 무관하게 별도로 존재하는 보조 인덱스입니다. 인덱스는 키 값과 함께 실제 데이터 행의 포인터(RID 또는 클러스터드 인덱스 키)를 저장하며, 테이블당 여러 개의 논클러스터드 인덱스를 생성할 수 있습니다.

 

 ⅱ. 논클러스터드 인덱스 (Non-Clustered Index) 구조

논클러스터드 인덱스의 리프 노드는 키 값과 함께 실제 데이터 위치를 가리키는 포인터 또는 포함된 컬럼(Included Columns)을 포함합니다.

 1) Key Lookup: 인덱스에서 키를 찾은 후, 테이블 또는 클러스터드 인덱스를 다시 참조해 나머지 컬럼을 읽는 추가 접근

 2) Covering Index: 쿼리에서 필요한 모든 컬럼이 인덱스에 포함된 경우 Key Lookup 없이 인덱스만으로 처리 가능

ⅲ. 논클러스터드 인덱스 (Non-Clustered Index) 성능 및 고려 사항

 1) 다양한 쿼리 조건에 대한 빠른 대응

 2) 과도한 Key Lookup이 발생할 경우 성능 저하 우려

 3) 빈번하게 사용되는 쿼리 조건 컬럼을 중심으로 설계

Ⅳ. 클러스터드(Clustered Index) vs 논클러스터드(Non-Clustered Index) 인덱스 비교

항목	클러스터드 인덱스	논클러스터드 인덱스
데이터 정렬 방식	인덱스 키 기준으로 물리적 정렬	물리적 순서와 무관
리프 노드	실제 데이터	키 + 데이터 위치 포인터
생성 가능 개수	1개	다수 가능
쓰기 성능	상대적으로 낮음	상대적으로 높음
범위 검색	우수	제한적
포인터 접근	불필요	필요 (Key Lookup 발생 가능)

※ 선택 기준

    ⅰ. 범위 검색, 정렬 중심의 쿼리: 클러스터드 인덱스

    ⅱ. 다양한 조건 검색, 읽기 성능 위주 환경: 논클러스터드 인덱스

Ⅴ. 고급 인덱스 최적화 전략

ⅰ. 복합 인덱스(Index) 설계

  1) 다중 컬럼 조건을 고려하여 복합 인덱스 구성

  2) 컬럼 순서에 따라 활용 가능성이 달라짐 (Left-most prefix rule)

  3) 선택도(Selectivity)가 높은 컬럼을 우선 배치

 

ⅱ. 인덱스 (Index) 설계 고려사항

  1) 등가 비교 조건에 자주 사용되는 컬럼

  2) 데이터 변경이 적고 고유값이 많은 컬럼

  3) 쿼리 실행 빈도가 높은 컬럼

  4) 정렬 조건이나 GROUP BY에 자주 사용되는 컬럼

 

ⅲ. 인덱스(Index) 성능 모니터링

 1) PostgreSQL: pg_stat_user_indexes, SQL Server: DMV(dynamic management view)

 2) 실행 계획(Execution Plan) 분석을 통한 인덱스 적절성 확인

 3) Key Lookup 비율 확인 및 커버링 인덱스 적용 여부 판단

 4) 인덱스 단편화(Fragmentation) 감지

 

ⅳ. 인덱스(Index) 관리

 1) REBUILD 또는 REORGANIZE를 통한 단편화 최소화

 2) 사용 빈도가 낮은 인덱스 정리 (DROP INDEX)

 3) 모니터링 자동화 및 비활성 시간대에 배치