[MS-SQL] 인덱스 (INDEX)란?

이번 시간에는 인덱스에 대해서 공부해보는 시간을 가져보겠습니다.

 

 

INDEX란?

인덱스는 무작위로 저장된 데이터 집합에서 원하는 데이터를 쉽고 빠르게 찾을 수 있도록 제공되는 하나의 오브젝트입니다.

 

조건에 맞춰서 테이블을 검색할 때 테이블 전체의 데이터를 읽어가며 찾아내는 것이 아니라,

인덱스의 키로 조건을 탐색하고 함께 저장된 행의 주소값을 통해 테이블의 다른 열을 참조하는 방식으로 수행합니다.

 

예를 들어, 책의 목차로 비유하면 책 속의 원하는 내용을 찾을 때 목차를 통해 페이지 번호를 쉽게 알아낼 수 있습니다.

(목차가 세분화될수록 원하는 페이지를 정확하게 찾아낼 수 있음)

 

위의 예시와 마찬가지로 인덱스도 상세할 수록 데이터를 더 정확히 찾아낼 수 있습니다.

 

인덱스를 생성하는 기본 구문은 아래와 같습니다.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명)

 

 

인덱스는 테이블마다 관리되며, CREATE INDEX 구문을 통해 생성할 수 있습니다.

이때, 특정 열들을 지정해야하는데 이 열이 인덱스의 키값이 됩니다.

이렇게 정의되는  키값은 쿼리 구문을 통해 인덱스가 참조될 때 조건절을 탐색하는 기준으로 사용됩니다.

 

생성된 인덱스는 B-Tree ( Balanced-Tree ) 구조로 저장되며 아래와 같습니다.

 

인덱스의 페이지 구조는 Root Level, Intermediate Level, Leaf Level 의 3단계로 구분됩니다.

 

Leaf Level의 페이지는 인덱스 키값을 기준으로 정렬된 데이터를 저장합니다.

Intermediate Level과 Root Level은 각 하위 Level 페이지의 첫 번째 인덱스 키값과 함께 해당 페이지의 위치를 나타내는 포인터 역할을 합니다.

 

 

인덱스의 종류

인덱스의 종류는

• 클러스터형 인덱스 (Clustered Index)
• 비클러스터형 인덱스 ( Non-Clustered Index)로 구분됩니다.

 

클러스터형 인덱스

• 테이블 자체를 인덱스로 만드는 형태
  → 인덱스의 리프(Leaf) 페이지가 곧 데이터 페이지가 됩니다.
• 키값으로 정의된 열을 기준으로 정렬된 상태가 유지되며 테이블의 구조가 변경되는 형태
  → 테이블마다 1개만 생성할 수 있음.

[예시]

[Birth]열을 키로 생성한 클러스터형 인덱스

Birth열을 기준으로 정렬하였으며,
[Birth] 열을 제외한 나머지 열은 정렬되지 않은 상태로 저장됩니다.

 

 

비클러스터형 인덱스

• 테이블과는 독점적으로 생성 → 정의된 키값과 함께 RID를 저장하여 관리합니다.
• 키값을 기준으로 정렬된 상태가 유지되며 최대 999개의 비 클러스터형 인덱스를 생성할 수 있습니다.

[예시]

[ID]열을 키로 생성한 비 클러스터형 인덱스와 테이블

비 클러스터형 인덱스는 테이블과는 독립적으로 생성되기 때문에

정의한 키 열과 함께 테이블 각행의 포인터가 되는 RID 값이 저장됩니다.

 

 

인덱스를 통한 스캔 방식

데이터를 조회하는 방법으로 Scan방식과 Seek 방식이 있습니다.

• Scan 방식은 테이블 전체 혹은 인덱스 전체를 읽어내는 방식이며,
• Seek 방식은 인텍스를 통해 조건에 해당하는 특정 범위만을 읽어내는 방식입니다.

 

 

Table Scan

table scan은 클러스터형 인덱스가 아닌 테이블 전체를 읽어내면서 조회하는 방식입니다.

 

Clustered Index Scan

Clustered Index Scan은 클러스터형 인덱스의 키로 지정된 열을 탐색의 조건으로 사용할 수 없는 경우에 모든 행을 읽어내면서 조회하는 방식입니다.

루트 페이지를 통해 제일 좌측에 존재하는 리프 페이지를 찾고, 이후 모든 행을 읽어내며 찾습니다.

위의 인덱스는 [Birth]열을 기준으로 정렬되어 있기 때문에 [ID] 열의 조건인 ‘B02’값의 위치를 탐색할 수 없습니다.

그래서 모든 데이터를 읽어야만 최종 결과를 찾을 수 있습니다.

 

 

Clustered Index Seek

Clustered Index Seek는 클러스터형 인덱스의 키값을 통해 조건을 탐색하여 만족하는 범위만을 읽어내는 방식입니다.

루트 페이지부터 조건의 시작점인 ‘900601’데이터의 위치를 탐색합니다.

최종 리프 페이지에 도달하면 각 행의 값을 비교하게 되고,

마지막 범위 값인 ‘900701’을 넘어가는 데이터를 만나는 순간 탐색을 종료합니다.

 

Non-Clustered Index Scan

Non-Clustered Index Scan은 구문에서 요구하는 열들이 비 클러스터형 인덱스에 모두 포함되어 있지만

키로 정의된 열이 탐색의 조건으로 사용될 수 없는 경우에 모든 행을 읽어내면서 조회하는 방식입니다.

Clustered Index Scan과 같이 Non-Clustered Index Scan도 루트 페이지를 통해

제일 좌측에 존재하는 리프 페이지를 찾고 이후 모든 행을 읽어내게 됩니다.

 

인덱스의 키로 정의된 [ID] 열이 조건으로 사용됐지만, 조건절이 [ID] + ‘p’ 형태로 이루어져서

탐색의 조건으로 사용되지 못하고 모든 행을 읽게 됐습니다.

 

 

Non-Clustered Index Seek

Non-Clustered Index Seek는 구문에서 요구하는 열들이 비 클러스터형 인덱스에 모두 포함되어 있으면서

키로 지정한 열이 탐색의 조건으로 사용된 경우에 수행되는 방식입니다.

조건으로 사용되는 열이 인덱스 키와 동일하기 때문에 특정 범위만을 탐색하게 됩니다.

루트 페이지부터 조건에 해당되는 ‘B01’ 데이터의 위치를 찾고 리프 페이지의 행을 비교하면서 조건과 일치하지 않는 행을 만날 때 탐색을 종료합니다.

 

 

RID Lookup : Non-Clustered Index + Heap Table

RID Lookup은 비 클러스터형 인덱스를 읽은 후에 참조해야 하는 열 데이터가 부족하여 테이블로 조인하는 과정입니다.

쿼리를 보면 ID = ‘C02’ 의 조건에 해당하는 [ENAME] 열을 찾는 것입니다.

비 클러스터형 인덱스로 [ID] 열을 탐색하지만 인덱스에 존재하지 않는 [EName] 열은 테이블로부터 가져와야만 합니다.

이때 RID Lookup이 수행되는데, 비클러스터형 인덱스에는 테이블의 각 행을 구분할 수 있는 RID 값이 포함되어 있기 때문에 Lookup을 통해 테이블과 조인하여 [ENAME] 열을 출력하는 것입니다.

 

 

Key Lookup : Non-Clustered Index + Clustered Index

Key Lookup은 비 클러스터형 인덱스에 필요한 열 데이터가 부족하여 조인을 통해 열을 가져오는 과정을 가진다. ( == RID Lookup과 동일함.)

다만 Key Lookup은 테이블이 클러스터형 인덱스로 구성되어 있고,

이로 인해 비 클러스터형 인덱스에는 RID 값이 아닌 클러스터형 인덱스의 Key값을 포함하고 있는 차이가 있습니다.

비 클러스터형 인덱스로 [ID] 열을 탐색하고 인덱스에 존재하지 않는 [ENAME] 열은 클러스터형 인덱스로부터 가져옵니다.

 

이때 비 클러스터형 인덱스에 포함되어 있는 클러스터형 인덱스의 Key를 통해 Lookup을 수행하고

클러스터형 인덱스를 탐색하여 최종 [ENAME] 열을 출력합니다.

 

 

 

 

[참고 서적]

SQL SERVER 튜닝 가이드