[MSSQL] 18. Index 정리

18. Index 정리

 

(1) SQL Server 인덱스 B-Tree

SQL Server의 인덱스는 기본적으로 B-Tree(정확히는 B+Tree) 구조를 가진다.

최상위 레벨 Root Level , 최하위 레벨 Leaf Level, 중간 레벨 NonLeaf Level 각 레벨 페이지들은 더블 링크드 리스트로 연결 된다.

 

 

 

(2) 힙, 클러스터드 인덱스, 비클러스터드 인덱스

 

- 힙 : 테이블에 클러스터드 인덱스가 없으면 힙이다.  데이터는 추가된 순서대로 쌓이게 된다.

 

- 클러스터드 인덱스 : 데이터가 클러스터드 인덱스의 키로 정렬이 된다. 테이블 자체가 B-Tree 인덱스 구조를 이룬다.

                             테이블은 하나의 순서로만 정렬 가능하므로 클러스터드 인덱스는 한개만 가능하다.

 

- 비클러스터드 인덱스 : 테이블 구조가 변경 되는 것이 아니고, 테이블과 별개로 인덱스 키로만 구성된 별도의 B-Tree 인덱스 영역이 새로 생긴다. 

SQL Server 2005 까지는 349개, 2008 부터는 999개 까지 가능하다.

비클러스터드 인덱스는 생성된 테이블의 구조에 따라 아래와 같이 조금 차이가 있다.

 

* 힙 테이블에서 비클러스터 인덱스 생성한 경우

=> 인덱스 페이지는 데이타의 위치를 가르키는 RID 값을 가지고 있고 이 RID 값으로 힙 영역에서 인덱스 레벨이 아닌 컬럼을 검색한다.

이것이 바로 RID Lookup이다.

 

* 클러스터드 인덱스가 있는 테이블에서 비클러스터드 인덱스를 생성한 경우

=> RID가 아닌 클러스터드 인덱스의 키가 기록된다. 그래서 이 넌클러스터드 인덱스로 데이터를 찾을 경우에는 넌클러스터드 인덱스의 리프레벨에서 클러스터드 인덱스의 키를 확인한 후 이 키를 이용해서 데이터를 찾는 "Key Lookup"이 발생합니다.

 

* Lookup

Lookup은 인덱스 페이지의 주소값을 이용한 랜덤 액세스 방식으로 실제 I/O 비용이 많이 발생하므로 주의하여 작성해야함.

 

 

(3) Index Scan과 Index Seek

 

- Index Scan

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.

 

인덱스 Scan은 맞는 인덱스를 찾기 위해 전체 인덱스를 읽는 것. 소요 시간은 인덱스 크기에 비례한다.

테이블 전체 또는 인덱스의 리프 레벨을 모두 읽는다는 것. 

 

- Index Seek

An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records  - time taken is only proportional to the number of matching records.

 

인덱스 Seek는 B-Tree 구조에 맞는 인덱스를 바로 찾는 것. 소요시간은 일치하는 레코드의 수.

인덱스 트리를 타고 단 하나의 값을 찍어 검색하는 방식. 인덱스를 탄다. 루트->중간->리프로 Seek 

 

• In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
• Note however that in certain situations an index scan can be faster than an index seek (sometimes significantly faster) - usually when the table is very small, or when a large percentage of the records match the predicate.

(4) 인클루드 인덱스, 필터드 인덱스, 커버드 인덱스

 

- 인클루드 인덱스

인클루드 인덱스는 비클러스터드 인덱스 생성 시 인덱스 키가 아닌 컬럼도 리프 레벨에 같이 추가하는 것이다.

SQL Server 2005부터 가능하며 INCLUDE 절로 생성.

장점은 키가아닌 컬럼도 인덱스 수준에서 검색이 가능하므로 Lookup을 하지 않아 쿼리 성능이 향상되며, 리프 레벨에만 구성하므로 인덱스 크기에는 영향이 미미하다.

 

- 필터드 인덱스

SQL Server 2008부터 가능하며 특정 집합, 어떤 조건에 대해서만 인덱스를 생성한다.

인덱스 생성 시 where 조건을 주어서 특정 조건을 가진 인덱스이다.

장점은 인덱스 사이즈가 줄어들고 쿼리성능 향상, 인덱스 관리 비용이 줄어든다.

 

 

 - 커버드 인덱스

커버드 인덱스는 쿼리에 사용된 모든 컬럼이 인덱스로 구성되었을때 그 인덱스를 커버드 인덱스라고 한다.