[MSSQL]최적화 단계에서 인덱스 선택(1)

1. Index Size 비교 테스트
계좌 정보 테이블의 샘플 100000개를 각 m, mCX, mNC 테이블에 넣는다.
SELECT top 100000 * INTO m FROM taccountmst 

SELECT top 100000 * INTO mCX FROM taccountmst

SELECT top 100000 * INTO mNC FROM taccountmst

mCX 에 클러스트 인덱스(CI)를, mNC 에 넌클러스트 인덱스(N-CI)를 만든다
CREATE UNIQUE CLUSTERED INDEX cx ON mCX (userno) 

CREATE UNIQUE INDEX cx ON mNC (userno)

GO


각 테이블을 SP_SPACEUSED 로 비교해보면

EXEC SP_SPACEUSED m

EXEC SP_SPACEUSED mCX

EXEC SP_SPACEUSED mNC

 

=>데이타 양이 많아 질수록 Non-cIustered 크기가 확연히 커진다.


2. 최적화기의 인덱스 선택 테스트

각 테이블마다 userno로 검색을 해봤을때 

SELECT * FROM m WHERE userno < 30
=> 인덱스가 없으므로 Table Full Scan
 예상 비용 : 1.09 

mNC 테이블과 같이 N-CI가 userno에 걸려 있는 경우 
다음 두개의 쿼리를 비교해보자 

SELECT * FROM mNC WHERE userno < 30
=> Index Seek 사용 
예상 비용 : 0.007
실제 행수 : 18 

SELECT * FROM mNC WHERE userno < 3000
=> Table Full Scan
예상 비용 : 1.1
실제 행수 : 1982 

위 두개의 SELECT 문은 선택도(Selectivity)에 따라 인덱스 사용 여부가 갈렸다.
18개의 결과 행 수인 첫번째 경우 인덱스를 사용했지만
1982개의 행수를 가져 오는 두번째 경우는 Table Full Scan이 이루어 졌다.

이 차이는 쿼리 실행 단계 전인 Optimizer가 통계 테이블의 밀도를 참조하여,
예상 결과 건수에 따라 인덱스 사용 여부가 달라진다.

mNC 테이블의 통계테이블을 SHOW_STATISTICS으로 확인 해보면
 
DBCC SHOW_STATISTICS(mNC, cx )

위 그림과 같이 All density(전체 밀도)는 1/100000 임을 알수 있다.

밀도(density) = 1/Distinct Value 개수

이고

분포도(cardinality) = 전체 행수 * 밀도(density)

가 된다. 따라서 mCX 테이블의 userno에 대한 분포도는 100000 * 1/100000 = 1 이다.
이 분포도로 예상 결과 행수 집계가 가능하다.

분포도는 직접 테이블을 조회해서도 알수 있다.

SELECT KEY, COUNT(*) FROM TABLE GROUP BY KEY

옵티마이저는 예상 결과 건수가 테이블 전체 행수의 0.43% 이하 일때,
인덱스를 사용하게 하고
그 이상 일 경우, Full Scan 하는 것이 낫다고 판단한다.

sql2000 서버에서 테스트 해본 결과,
0.41%에서 인덱스 사용 여부가 달라지는 것을 확인 했다.
이 값은 DB엔진에서 정한 값으로 관리자가 변경 할 수 없다고 한다.

물론 옵티마이저 판단에 상관없이 아래처럼 인덱스를 사용할 수 있다.

SELECT * FROM mNC WITH (INDEX (cx)) WHERE userno < 3000

하지만 데이터는 유기적으로 변동되기 때문에
확실히 써도 좋다는 판단과, 주기적으로 모니터링이 가능하다면 위와 같이 써도 좋다.

마지막으로 클러스터 인덱스 사용 시
SELECT * FROM mCX WHERE userno < 30
=> Clustered Index Seek 사용
 예상 비용 : 0.003

CI는 N-CI와 달리 항상 사용된다. 역시 조회 성능은 가장 우수