Covered Index VS Include index(100512)

목적 : SQL SERVER 2005 에 새롭게 추가된 Include Index와 기존의 Convered Index와의 성능 및 차이점을 알아보기 위한 실습

 

먼저 Include Index에 대한 정의를 알아보자(MSDN 참조)

 

MSDN의 설명

쿼리의 모든 열이 키 또는 키가 아닌 열로 인덱스에 포함될 때 키가 아닌 포괄 열이 있는 인덱스는 쿼리 성능을 상당히 향상시킬 수 있습니다. 성능이 향상되는 이유는 쿼리 최적화 프로그램이 테이블 또는 클러스터형 인덱스 데이터에 액세스하지 않고 인덱스 내에서 모든 열 값을 찾을 수 있으므로 디스크 I/O 작업을 줄어들기 때문입니다.

 

 Include Index의 장점

1. 인덱스 키 열로 허용되지 않는 데이터 형식(varchar(max)등)을 포괄 열에는 포함가능

   즉 포괄 열 인덱스에는 text, ntext, image 데이터 형식을 제외한 모든 데이터 형식이 허용(넌 클러스터드 Index는 900Byte로 제한)

2. 비클러스터형 인덱스를 구성하는 인덱스 키 열의 개수 또는 인덱스 키의 크기의 계산에서 제외

 

 

비교

AdventureWorks DB의 Sales.SalesOrderDetail Table 을 Cross Join 으로 약 600만건의 Dummy Table 생성

 

Table 3 -> Include Index 생성

CREATE INDEX IN_NC ON TEST3 (SalesOrderID)      

INCLUDE (ProductID)

 

 

Table 4-> Covered Index 생성

CREATE INDEX NC_CV ON TEST4 (SalesOrderID, ProductID)      

 

Table 5 -> Index 없음

 

SELECT 구문 (결과 약 15만건)

select SalesOrderID,ProductID FROM TESTX

where  SalesOrderID  > 66912

AND ProductID=50

 

 

 

결과 : Include Index 가 역시 Index의 leaf Level에만 포괄열이 복사되어서 읽기 성능이 가장 좋음

 

Insert 구문 (While문으로 약 2만건)

 

 

결과 : Index가 없는 Test5의 Write가 가장 적으나 Split가 없는 상태에서는 index여부와 큰 차이 없음

 

update 구문 (약 12만건)

 

 

결과 : Index Root 부터 leaf 까지 Update 가 되야하는 Covered Index 가 가장 많은 비용 소요, Index가 없는 Table5와 Covered Indes가 걸린 Table4는

         Duration및 CPU에서 거의 5배이상의 차이

         무분별한 Index가 얼마나 update에서 성능상 부하의 원인이 되는지 확인

 

 결과 적으로 Select , Insert, Update 문 모두 Covered Index 보다 Include Index가 더 낳은 성능을 보이고 있다.

 그럼 모든 인덱스 생성시 모두 Include를 지정해야 하나?

 하기의 쿼리를 보자.

 

 

 

만약 사용자가 SalesOrderID 별 ProductID를 순서대로 보고싶다고 가정할때 Include로는 정렬이 불가능하다.

즉 Order by를 반드시 명시해야 한다.

하지만 Covered Index의 경우는 SORT가 필요 치 않다.

대용량일 수록  Sort가 상당한 부하를 가져다 주는건 당연한 일이고 만약 대용량 조인시 SortMerge조인으로 풀릴 기회가

없어지는 경우가 생긴다.

 

결론은 그떄 그떄 상황에 맞는 인덱스가 필요함.