북마크 룩업 최소화 기법(2) - Clustered Index

인덱스를 효율적으로 액세스하더라도 테이블 랜덤 액세스에 의한 부하가 심각하다면, 

해당 인덱스를 클러스터형 인덱스(Clustered Index)로 변경하는 방법을 고려할 수 있다.

클러스터형 인덱스는 인덱스 키 컬럼과 나머지 컬럼을 리프페이지에 같이 저장하기 때문에 테이블 랜덤 액세스를 획기적으로 

줄일 수 있다. 단, 클러스터형 인덱스는 넌클러스터형 인덱스의 효율성에 큰 영향을 미치므로 테이블 전체적인 관점에서 사용 여부를 결정해야한다.

 

좀더 자세한 실습을 위해 기존에 만들어 두었던 인덱스를 제거해보자.

또한 새로운 인덱스는 ibsaDate 컬럼(입사한 날짜)에 걸어주어 WHERE 절에 해당 조건을 필터링 하여 쿼리해보자. 

DROP INDEX IDX__TBLINSATEST__BUSEO__JIKWI ON dbo.TBLINSATEST

CREATE INDEX IDX__TBLINSATEST__IBSADATE ON dbo.TBLINSATEST (ibsaDate)

SELECT * FROM dbo.TBLINSATEST WITH(NOLOCK) WHERE ibsaDate BETWEEN '2010-01-01' AND '2015-01-01'

IDX__TBLINSATEST__IBSADATE 인덱스를 만들어줬음에도 불구하고 FORCESEEK 힌트를 사용하지 않으면 

INDEX SEEK 가 아닌 CLUSTERED INDEX SCAN 을 사용하는 것을 볼 수 있다.

 

이처럼 검색 조건에 맞는 인덱스가 있더라도, 넓은 범위를 액세스하는 쿼리는 인덱스 탐색 대신 

테이블 스캔(TABLE SCAN , CLUSTERED INDEX SCAN) 방식으로 수행될 가능성이 크다.

 

이번에는 강제로 INDEX 를 사용해서 쿼리하게 만들어보자.

SELECT * FROM dbo.TBLINSATEST WITH(NOLOCK,INDEX(IDX__TBLINSATEST__IBSADATE)) WHERE ibsaDate BETWEEN '2010-01-01' AND '2015-01-01'

 

 

예상대로 SQL SERVER는 테이블 스캔 (TABLE SCAN) 을 한 경우에 IDX__TBLINSATEST__IBSADATE 인덱스를 사용하여 탐색하는 것보다 부하가 적게 드는것을 알 수 있다.

 

IDX__TBLINSATEST__IBSADATE 인덱스가 어떤 방식으로 테이블을 액세스 하는지 확인해보자.

위의 그림을 보면 인덱스에서 추출된 20992건이 테이블을 액세스 하고 나서 한 건도 버려지지 않았다.  따라서 인덱스 구성과 인덱스 탐색 과정에는 비효율이 없다고 할 수 있다. 문제는 KEY ROOKUP에 의한 랜덤 액세스가 과다하게 발생한다는 데 있다. 

SQL 성능 문제의 대부분은 과다한 랜덤 액세스 또는 테이블 전체 스캔 때문에 발생하므로, 
해당 쿼리는 과다하게 랜덤액세스가 발생하는 INDEX SEEK 방식 보단 TABLE SCAN 을 이용하여 쿼리하는게 성능적으로 더 좋다고 볼 수 있다.

위 쿼리만 놓고 보면 TBLINSATEST 테이블을 전체 스캔하는게 IDX__TBLINSATEST__IBSADATE 인덱스를 거치는 것보다 효율적이지만,  이런 검색조건이 자주 사용된다면 테이블을 매번 전체 스캔하기 때문에 성능적으로 부담스러워질 수 있다.
이럴 때 해당 인덱스를 클러스터형 인덱스로 바꾸면 큰 효과를 볼수 있는데, 인덱스 탐색을 통해 필요한 범위만 액세스할 뿐만 아니라 KEY LOOKUP에 의한 랜덤 액세스 부하까지 없앨 수 있기 때문이다.

ibsaDate 컬럼을 클러스터형 인덱스( Clustered index)로 생성해보자.

 

-- 기존인덱스 제거
DROP INDEX IDX__TBLINSATEST__IBSADATE ON dbo.TBLINSATEST 
-- 기존 PK 제거
ALTER TABLE dbo.TBLINSATEST DROP CONSTRAINT PK__TBLINSATEST__NUM
-- 새로운 PK 생성
ALTER TABLE dbo.TBLINSATEST ADD CONSTRAINT PK__TBLINSATEST__IBSADATE__NUM PRIMARY KEY (ibsaDate,num)


SELECT * FROM dbo.TBLINSATEST WITH(NOLOCK) WHERE ibsaDate BETWEEN '2010-01-01' AND '2015-01-01'

 

 

PK__TBLINSATEST__IBSADATE__NUM 클러스터형 인덱스가 어떤 방식으로 테이블을 액세스 하는지 확인해보자.

 

클러스터형 인덱스의 리프 페이지는 곧 데이터 페이지이므로, 인덱스만 탐색하면 해당 테이블의 모든 컬럼 값을 곧바로 얻을 수 있기 때문에, INDEX SEEK + KEY LOOKUP 방식보다 성능적으로 더 좋다고 볼 수 있다.

 

위의 클러스터형으로 검색한 결과와 INDEX SEEK 로 검색한 결과를 비교해보면 20992개의 KEY LOOKUP 이 없어진 게 가장 큰 차이점이자 개선 효과다. 그러나 리프 페이지에 (인덱스 키 컬럼뿐만 아니라 나머지) 모든 컬럼을 저장하다 보니 인덱스 탐색 비용이 약간 증가할 수 있다.

 

 

CLUSTERED INDEX SEEK 오퍼레이션이 가장 효율이 좋으며 논리적 읽기 수는 237개이다. 

테이블 스캔의 논리적읽기수는 343개, INDEX SEEK 의 논리적 읽기수는 43342개 이다.
즉, CLUSTERED INDEX SEEK 오퍼레이션의 논리적 읽기량이 INDEX SEEK 대비 0.55% 정도이고 TABLE SCAN 대비 70% 수준으로 성능이 훨씬 좋다고 볼 수 있다.