북마크 룩업 최소화 기법(1) - 결합 인덱스

북마크 룩업(Bookmark Lookup) 이란, 인덱스 엔트리에 저장된 rid 값으로 테이블 로우를 액세스하는 오퍼레이션이다. 

만약 클러스터형 인덱스가 있다면 RID LOOKUP 값 대신 클러스터형 인덱스의 키 값으로 테이블 로우를 찾아가는데(KEY LOOKUP), 이것도 넓게 보면 북마크 룩업이라고 할 수 있다.

북마크 룩업은 랜덤 액세스 방식으로 수행되며 SQL 성능에 큰 영향을 미친다. 필요한 페이지가 메모리 풀의 '버퍼 캐시'에 모두 있더라도, 랜덤 액세스 횟수가 증가하면 SQL 성능은 떨어질 수밖에 없다. 

필요한 페이지가 버퍼 캐시에 하나도 없는 경우가 최악인데, 이때는 랜덤 액세스 횟수만큼 물리적 디스크 I/O 가 발생할 수 있다.

 

 

아래의 예시를 보면서 이해해보자.(TBLINSATEST 테이블을 사용할 것이다.)

현재는 PK (CLUSTERED INDEX) 가 num 컬럼에 지정되어 있는 상태이고 다른 어떠한 인덱스도 없는 상황이다.

CREATE INDEX IDX__TBLINSATEST__BUSEO ON dbo.TBLINSATEST (buseo)

SELECT 
	* 
FROM dbo.TBLINSATEST WITH(NOLOCK,FORCESEEK) 
WHERE buseo = N'개발부'
AND jikwi = N'사원'

위와 같이 buseo 컬럼에만 인덱스를 생성해주고 WHERE 조건에 buseo,jikwi 를 사용하여 쿼리를 해주면 아래와같은

실행계획을 볼 수 있다.

 

 

총 논리적 읽기는 14813회 발생하였으며, IDX__TBLINSATEST__BUSEO 인덱스를 거쳐 룩업을 7168번 수행했는데 그중 4608회만 조건을 만족하였다. 즉, 현재 아래와 같이 인덱스를 액세스하는 과정을 수행하고 있다.

 

즉 해당 쿼리의 결과에서 보듯이 IDX__TBLINSATEST__BUSEO 인덱스는 현재 쿼리의 buseo 와 jikwi 두가지를 필터링

해줄 경우에는 변별력이 좋다고 볼수는 없다. 이때, 변별력을 좋게 만들어주는 방법은 결합인덱스를 만드는 방법이다.

 

DROP INDEX IDX__TBLINSATEST__BUSEO ON dbo.TBLINSATEST
CREATE INDEX IDX__TBLINSATEST__BUSEO__JIKWI ON dbo.TBLINSATEST (buseo,jikwi)


SELECT 
	* 
FROM dbo.TBLINSATEST WITH(NOLOCK,FORCESEEK) 
WHERE buseo = N'개발부'
AND jikwi = N'사원'

 

 

총 논리적 읽기는 9531회로 결합인덱스를 사용하지 않을때보다 논리적 읽기수가 줄어들었고,

IDX__TBLINSATEST__BUSEO__JIKWI 인덱스를 거쳐 룩업을 4608번 수행해서 정확하게 4608개의 데이터를 

가져오는것을 볼 수 있다. 즉, 결합인덱스를 사용함으로써 인덱스의 변별력이 높아졌다고 볼 수 있다.

 

즉, 결합인덱스를 사용하여 테이블을 액세스하는 방식은 아래와 같다.

 

 

이와 같이 결합인덱스를 사용하게 되면 쿼리성능의 개선효과가 나타나지만, 인덱스가 많아질수록

INSERT, UPDATE, DELETE 등 DML 작업에 의한 부하가 증가하게 된다. 

필요할 때마다 인덱스를 추가하는 것은 누구나 쉽게 생각할 수 있는 1차원적인 방법이다. 

SQL 튜닝 전문가라면 기존 인덱스를 최대한 활용하여 성능을 향상하는 방법을 고민해야 한다.