INDEX 2

1. Clustered Index / Nonclustered Index

ACTION	Clustered Index	Nonclustered Index
Index 생성 후 크기 증가	테이블의 1~5%	테이블의 10~20%
Point Query 실행시	상당히 빠르다.	Clustered index보다 약간느리다.
Range Search 실행시	빠르다.	일반적으로 Table Scan보다 느리다.
DML문 실행시	과부하 걸린다.	Clustered Index보다 부하가 적다.
Covered Index의 경우	도움이 안된다.	매우 도움이 된다. 대단히 빠르다.
테이블당 생성 개수	오직 1개	249개까지 가능

 

 

Clustered Index	Nonclustered Index
1.     인덱스 Leaf 페이자가 바로 데이터 페이지 2.     데이터 페이지는 인덱스가 생성된 컬럼을 기준으로 항상 정렬 3.     Range Seach 빠른 성능	1.     인덱스의 Leaf 페이지는 데이터페이즈를 가리키는 Data Page번호+RowID 2.     인덱스 페이지 항상정렬이지만 데이터 페이지는 그렇지 않다 3.     특정한 값을 찾는데 빠르게 동작   4.     DML문의 작업은 Clustered 인덱스 보다 빠르게 동작

 

인덱스 검색 방법

 

▶ Table Scan : 데이터 페이지를 처음부터 끝까지 검색하는 것

▶ Index Seek : Nonclustered Index에서 데이터를 검색하는 것

▶ Clustered Index Seek : Clustered Index에서 데이터를 검색하는 것

▶ Clustered Index Scan : Table Scan과 동일 , Clustered Index의 리프레벨은 결국 데이터페이지

 

쿼리 성능 검사 명령어

명령어	설명
SET STATISTICS IO ON/OFF	페이지 I/O가 얼마나 발생했는지 보여준다. 주어지 Query로 인해서 얼마나 많은 Page I/O가 발생하는가를 보여준다. Page I/0가 많다는 것은 속도가 느리다는 것이다.
SET STATISTICS TIME ON/OFF	실행하는데 걸린 시간을 보여준다.
실제 실행 계획 포함 (Ctrl +M)	Query문의 실행계획을 그래픽으로 보여준다
DBCC DROPCLEANBUFFERS	DBCC DROPCLEANBUFFERS를 사용하면 서버를 다시 시작 하지 않아도 완전히 빈 버퍼캐시를 사용하여 Query를 테스트 할 수 있다.

 

TIP

▶ Range Search(범위안에 모든 값을 가져오는 것)에서는 Nonclustered Index보다 Clustered Index

   가 효과적이다. Nonclustered Index의 경우 리프레벨에서 검색 후 존재하지 않으면 데이타 페이지

   를 검색해야 하기 떄문이다

     큰 범위 검색에서는  Nonclustered Index보다는 Index를 사용하지 않고 Full Scan하는 방법

   이 더 낫다. 따라서 빈번히 영역찾기를 하는 컬럼이 있다면 Clustered Index를 지정해주는 것이 좋

   다.

▶ Covered Query

    질의에 나타난 조건 컬럼과 질의 대상 컬럼이 모두 Nonclustered Index에 복합키(Composite Key)

  에 포함되어 있는 경우, Covered Index라고도 한다.

    이런 경우에는 Clustered Index보다 월등히 빠른 검색 속도를 나타낸다. 또한 Point Query(한 행만

  검색하는 경우)뿐만 아니라 Ranger Search에도 매우 빠른 성늘을 나타낸다.

 

2. Composite Index

    하나의 컬럼에만  Index를 만든 것은  Single Column Index이라고 하고, 둘 이상의 컬럼에 Index를

  만드는 것을 Composite Index라고 한다.

 1) where 절에서 Index의 순서, 또는 그 부분으로만 찾는 경우.

    ex) ('부서명'컬럼+'이름'컬럼)의 Index 생성시

          --> 반드시 where절에 Index의 첫 번재 위치한 컬럼(선행컬럼)인 '부서명' 컬럼이 있어야

                성능 향상에 도움이 된다.

 2) 가급적 컬럼의 총 크기가 20 Byte가 넘지 않아야

 3) Unique한 컬럼을 선행 컬럼으로.

 

3. Index 생성시 고려 상황

▶ 인덱스 생성해야 하는 경우

 1) where 조건절에 자주 사용되는 컬럼

 2) select 절에서 자주 검색되는 컬럼

 3) 선택도가 좋은 컬럼

 4) Foreign Key 컬럼

 5) Join 조건절에 자주 사용되어지는 컬럼

 6) Ordey by절에 자주 사용되는 컬럼은 Clustered Index를 생성해야 좋다.

 7) Range Search 잦은 컬럼도 Clustered Index를 생성해야 좋다.

 8) 특정한 하나의 값은 Nonclustered Index를 생성해야 좋다.

 

▶ 인덱스를 생성하지 말아야 하는 경우 

 1) 자주 검색되지 않는 컬럼

 2) where 조건절에 자주 사용되더라도 선택도가 좋지 않은 컬럼

 3) DML문의 실행이 빈번하면서 select보다 DML문의 속도가 중요할 때

 

▶ Clustered Index가 테이블에 아예 없어야 좋은 경우

  Web 상에서 회원테이블 처럼 Insert가 빈번한 컬럼은 Nonclustered Index로만 주는 것이 오

    히려 성능이 좋아진다.