데이터베이스 인덱스 완전 정리

인덱스의 기본 개념

인덱스(Index)는 데이터 검색속도 향상을 위하여 테이블에 저장된 로우(row)를 식별가능하도록 저장한 데이터베이스 오브젝트입니다1. 일반적으로 책을 볼 때 목차, 차례가 존재하는 것과 유사한 개념으로, 목차부터 찾으면 원하는 정보가 몇 페이지에 있는지 금방 알 수 있고 어느 정보가 어떻게 구성되어 있는지 전체적으로 파악하기도 용이합니다1. 인덱스는 테이블의 열에 대한 포인터를 제공하여 데이터를 더 빠르게 찾을 수 있도록 도와줍니다2.

인덱스의 주요 분류

1. 대응 밀집도에 따른 분류

밀집 인덱스 (Dense Index)

밀집 인덱스는 데이터 파일의 각 레코드마다 한 개의 데이터 엔트리를 가지며, 즉 모든 레코드를 인덱싱합니다3. 파일의 모든 레코드에 대해서 인덱스를 생성하며, 일반적으로 보조인덱스는 밀집인덱스로 구현됩니다4. 비클러스터링(non-clustering) 인덱스는 밀집인덱스에 해당합니다4.

희소 인덱스 (Sparse Index)

희소 인덱스는 각 데이터 블록마다 한 개의 데이터 엔트리를 가지며, 데이터 블록 중 하나의 레코드만 인덱싱합니다3. 테이블의 일부 레코드에 대해서만 인덱스를 생성하며, 희소인덱스는 각 블록마다 한 개의 탐색키에 대한 인덱스를 가집니다4. 일반적으로 기본키에 대한 기본인덱스는 희소인덱스로 구현되며, 클러스터링(clustering) 인덱스는 희소인덱스에 해당합니다4.

2. 클러스터링에 따른 분류

클러스터드 인덱스 (Clustered Index)

클러스터드 인덱스는 해당 키 값을 기반으로 테이블이나 뷰의 데이터 로우를 정렬하고 저장합니다5. 데이터의 레코드 순서가 Index 페이지의 정렬순서와 동일하거나 비슷하게 만들어진 index입니다1. 데이터 로우 자체는 한 가지 순서로만 저장될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 존재할 수 있습니다5.

비클러스터드 인덱스 (Non-Clustered Index)

비클러스터드 인덱스는 데이터 레코드의 물리적 순서가 Index의 엔트리 순서와 상관없이 저장되도록 구성된 Index입니다1. 인덱스 필드 값의 순서가 레코드들의 물리적 저장 위치와 무관하며, 반드시 밀집 인덱스 구조를 가집니다6. 비클러스터형 인덱스에는 데이터 로우와 별개의 구조가 있고, 각 키 값 항목에는 키 값이 포함된 데이터 로우에 대한 포인터가 있습니다5.

3. 구조에 따른 분류

B-Tree 인덱스

B-Tree 인덱스는 가장 일반적으로 사용되는 인덱스 종류입니다2. Balanced Tree 기반 인덱스로 Root 블록, Branch 블록, Leaf 블록으로 구성되어 있으며, 모든 Leaf 블록이 동일한 Depth를 유지하도록 하여 균형을 유지합니다1. 데이터의 정렬 순서를 유지하며, 범위 검색과 정렬된 결과 반환에 효율적입니다2.

B-Tree는 Binary search tree(이진 탐색 트리)를 업그레이드한 자료구조로, 관계형 DB의 Index에서 자주 사용되는 자료 구조입니다7. 최대 M개의 자식을 가질 수 있는 B 트리를 M차 B트리라고 하며, 노드에는 2개 이상의 데이터가 들어갈 수 있으며 항상 정렬된 상태로 저장됩니다8.

해시 인덱스 (Hash Index)

해시 인덱스는 해시 함수를 사용하여 데이터를 저장하고 검색합니다2. 해시 함수를 기반으로 인덱스 엔트리를 저장한 인덱스로, 인덱스 엔트리가 저장되는 공간인 버켓을 사용합니다6. 해시함수 값을 버켓 번호로 사용하며, 해시함수 값으로 인덱스 엔트리를 저장할 버켓을 결정합니다6.

해시 인덱스는 정확한 일치 검색에 효율적이지만, 범위 검색이나 정렬된 결과 반환에는 적합하지 않을 수 있습니다2. 동등 비교 조건을 갖는 검색에 유리하며, 해시 함수를 통해 직접 인덱스 엔트리를 포함하는 버켓을 찾습니다6.

비트맵 인덱스 (Bitmap Index)

비트맵 인덱스는 비트를 이용하여 컬럼값을 저장하고 ROWID를 자동으로 생성합니다1. 데이터에 해당하는 0, 1로 구성된 비트맵을 구성하고 있고, 비트맵의 조합에 의해서 데이터를 매핑하는 방식의 인덱스입니다9. 키 값을 가질 수 있는 각 값에 대해 하나의 비트맵을 구성합니다9.

비트맵 인덱스는 데이터 분포도가 나쁜 컬럼에 적합하며, 적은 분포도를 갖는 테이블에 적합합니다9. 비트 연산으로 OR연산, NULL값 비교 등이 가능하며, 다중 조건을 만족하는 데이터 검색 시에 유리합니다9.

4. 다단계 인덱스 (Multi-level Index)

다단계 인덱스는 단일 단계 인덱스의 경우 인덱스 자체가 너무 커지면 탐색하는데 너무 오래걸리는 문제를 해결하기 위해 개발되었습니다10. 인덱스 엔트리 탐색 시간을 줄이기 위해 단일 단계 인덱스에 대해 다시 인덱스를 정의하는 것입니다10.

다단계 인덱스는 가장 상위 단계의 인덱스가 한 블록이 들어갈 때까지 이 과정을 반복하며, 가장 상위 단계의 인덱스를 마스터 인덱스라 합니다10. 마스터 인덱스는 한 블록에 불과하기에 주 메모리에 상주할 수 있습니다10. 다단계 인덱스는 트리 형태로 구성되며, B+ 트리를 사용합니다10.

기타 인덱스 유형

유일성에 따른 분류

Unique Index: Index 키는 연관된 테이블의 하나의 행만 가리킵니다1
Non-Unique Index: Index 키는 연관된 테이블의 여러 행을 가리킬 수 있습니다1

구성컬럼 수에 따른 분류

Single Index: 하나의 컬럼으로 구성된 Index입니다1
Composite Index: 여러 개의 컬럼 조합으로 생성된 Index로, 최대 32개까지 조합 가능합니다1

함수 기반 인덱스 (Function-Based Index)

함수 기반 인덱스는 함수나 표현식의 계산값으로 인덱스를 생성하는 방식입니다1. 특정 열이나 표현식의 값을 기반으로 인덱스를 생성하는 기술로, 데이터베이스 성능을 향상하고 쿼리의 실행 속도를 향상하는 데 도움을 줄 수 있습니다11.

함수 기반 인덱스는 컬럼에 함수를 적용한 상태로 생성한 인덱스로, 어플리케이션 수정 없이 성능향상이 가능하다는 장점이 있습니다1. 하지만 데이터 변경 시 처리비용이 크므로 자주 변경되는 컬럼에는 적용하지 않는 것이 좋습니다1.

인덱스 스캔 방법

주요 인덱스 스캔 유형

Index Range Scan: 인덱스 루트에서 리프 블록까지 수직적 탐색을 하고, 필요한 범위만큼 수평적 탐색하는 스캔 방식입니다12
Index Unique Scan: 수직적 탐색으로만 데이터를 찾는 스캔 방식으로, Unique 인덱스를 ‘=’ 조건으로 탐색하는 경우에 동작합니다12
Index Full Scan: 인덱스 전체를 탐색하는 방식으로, 사용컬럼들이 모두 하나의 인덱스에 존재할 때 사용됩니다1

인덱스 설계 고려사항

인덱스는 데이터베이스의 성능을 향상시키는데 중요한 역할을 하지만, 적절히 설계해야 하며 인덱스를 과도하게 생성하는 것은 오히려 성능을 저하시킬 수 있습니다2. 인덱스를 설계할 때는 데이터의 특성과 접근 패턴을 고려하여 적절한 열 또는 열의 조합을 선택해야 합니다2.

카디널리티가 매우 낮은 컬럼이라면 인덱스를 지정하지 않는 것이 더 나을 수도 있으며13, 인덱스는 조회 성능(SELECT)을 극대화하기 위해 만든 객체이지만 너무 많이 만들면 INSERT, UPDATE, DELETE 시 부하가 발생해 전체적인 데이터베이스 성능을 저하하게 만들 수 있습니다14.