INDEX TUNNING

 

인덱스(Index) 
   - 정의: 인덱스는 데이터베이스 테이블의 검색 성능을 향상시키기 위해 사용하는 데이터 구조이다.
   - 역할: 인덱스를 사용하면 테이블의 특정 열에서 데이터를 빠르게 검색할 수 있다. 인덱스가 없으면 데이터베이스는 모든 행을 검사해야 하므로 시간이 많이 걸린다.

 인덱스는 데이터를 저장할 때 삽입되는 순서대로 저장되는 것이 일반적이지만, 인덱스를 사용하면 특정 열을 기준으로 데이터를 빠르게 탐색할 수 있다. 인덱스를 생성하면, 데이터는 이진 트리를 확장하여 하나의 노드가 가질 수 있는 자식 노드의 최대 숫자가 2보다 큰 B-트리 구조를 따르게 된다.

 

 인덱스의 종류
- 클러스터 인덱스 (Clustered Index)
   테이블의 실제 데이터가 리프 노드에 저장되는 인덱스. 데이터가 물리적으로 정렬되고 하나의 테이블에 하나만 존재할 수 있다.

 

- 넌클러스터 인덱스 (Non-Clustered Index)
  실제 데이터와 독립적으로 생성되며, 인덱스가 리프 노드에 실제 데이터의 위치를 가리키는 포인터를 포함한다. 하나의 테이블에 여러 개 생성할 수 있고 물리적으로 정렬되지 않는다.

 

- 클러스터와 넌클러스터 인덱스의 복합 구조

클러스터와 넌클러스터 인덱스가 함께 존재하는 경우, 넌클러스터 인덱스의 리프 노드는 클러스터 키를 가진다. 넌클러스터 인덱스의 루트에서 시작하여 리프 노드까지 탐색하면 클러스터 키를 통해 클러스터 인덱스의 리프 노드까지 접근하여 데이터를 찾게 된다.

 

인덱스를 통한 스캔 

 

-테이블 스캔 (Table Scan)
인덱스가 존재하지 않거나 넌클러스터 인덱스만 존재하고, 전체 테이블을 스캔하는 것이 더 효율적일 때 사용된다. 모든 데이터를 순차적으로 읽어 조건에 맞는 데이터를 추출한다. 데이터가 정렬되어 있지 않기 때문에 마지막 페이지까지 모든 데이터를 읽어야 한다.

SELECT * FROM table WHERE id = 'a03';

 

-클러스터 인덱스 스캔 (Clustered Index Scan)
클러스터 인덱스가 존재할 때 특정 범위를 스캔해야 하는 경우 사용된다. 인덱스가 특정 열로 정렬되어 있지 않으면 마지막 페이지까지 읽어야 한다.

SELECT * FROM table WHERE id BETWEEN 'a01' AND 'a05';

 

 

 

- Clustered Index Seek
 클러스터 인덱스가 존재하고 검색 조건이 인덱스 열과 일치할 때 사용되며 루트 노드부터 리프 노드까지 탐색하여 필요한 데이터를 빠르게 찾는다.

 SELECT * FROM table WHERE birth_date = '1990-01-01';

- Non-Clustered Index Scan
넌클러스터 인덱스만 존재하고, 인덱스 열이 아닌 조건을 가질 때 사용된다. 넌클러스터 인덱스 전체를 스캔하여 조건에 맞는 데이터를 찾는다. 인덱스 컬럼을 가공하거나 선두 컬럼에 대한 검색조건이 없을 때 발생한다.


- Non-Clustered Index Seek
   넌클러스터 인덱스가 존재하고, 검색 조건이 인덱스 열과 일치할 때 사용된다.루트 노드부터 리프 노드까지 필요한 페이지만 수직적 탐색하여 데이터를 빠르게 찾는다.

-Non-Clustered Index Seek Without RID Lookup
   인덱스에 포함된 열만을 조회할 때 사용된다. RID 룩업이 발생하지 않으며 리프 노드에서 필요한 값을 반환한다.


Non-Clustered Index Seek with RID Lookup
 인덱스에 포함되지 않은 열을 조회할 때 사용된다. RID 룩업이 발생한다. 리프 노드에서 RID를 찾고, 실제 데이터 페이지를 조회하여 필요한 값을 반환한다.

 SELECT name FROM table WHERE id = 'a03'

 

 

클러스터와 넌클러스터 인덱스가 함께 사용되는 경우

클러스터 인덱스와 넌클러스터 인덱스가 함께 사용되는 경우, 검색 조건에 따라 먼저 넌클러스터 인덱스를 탐색하고, 이후 클러스터 인덱스를 통해 데이터를 찾는다. id 열에 넌클러스터 인덱스가 있고, birth_date 열에 클러스터 인덱스가 있는 경우 검색 조건이 id = 'a03'이라면 넌클러스터 인덱스를 통해 id를 찾고, 리프 노드에서 클러스터 키를 얻은 후 클러스터 인덱스를 통해 실제 데이터를 찾는다.

- 조회하는 열이 인덱스에 포함되지 않으면 실제 데이터 페이지까지 탐색하여 필요한 값을 반환한다.

 

 

포괄 인덱스와 인덱스 조화

포괄 인덱스는 넌클러스터 인덱스의 키가 아닌 컬럼을 포함하는 인덱스이다. 다시 말하면 넌클러스터 인덱스의 일부로, 키로 지정되지 않은 컬럼을 포함하는 인덱스이며 인덱스 키로 사용할 수 없는 데이터 형식의 컬럼을 인덱스에 포함시킬 수 있다(예: 텍스트, 텍스트 이미지 등 제외).

  
root, intermediate node는 키로 지정된 컬럼만 포함 하고 leaf node는 키로 지정된 컬럼 외에도 포함된 컬럼들이 추가된다.

 

페이지 스플릿(page split)을 최소화할 수 있는 장점과 리프 레벨에서 탐색을 종료할 수 있어 RID 룩업이나 키 업과 같은 추가 비용을 제거할 수 있다.

 

CREATE NONCLUSTERED INDEX idx_id_name ON SampleTable(id) INCLUDE (name);