북마크 룩업 최소화 기법(3) - Covered Index

북마크 룩업에 의한 성능 문제를 해결하는 방법 중 하나가 

 

해당되는 컬럼 자체를 클러스터형 인덱스로 만들어주는 것이다. 

 

모든 인덱스를 클러스터형 인덱스로 만들면 좋겠지만,

 

 클러스터형 인덱스는 테이블당 한개만 만들 수 있다.

 

그렇다면, 이미 클러스터형 인덱스가 존재하는 상태에서

 

넓은 범위를 액세스하는 또 다른 검색 조건이 성능 문제를 일으킨다면 어떻게 해결해야 할까? 

 

해당 검색조건이 자주 사용되고 빠른 성능을 요구한다면,

 

북마크 룩업이 아예 발생하지 않도록 필요한 컬럼을 인덱스에 모두 포함하는 방법을 고려할 수 있다.

이 처럼 쿼리 수행에 필요한 컬럼이 모두 포함된 인덱스를 '커버된 인덱스'(Covered index) 라고 부른다.

 

 

커버드인덱스를 실습하기 위해서 기존에 테이블의 인덱스를 모두 지워주고 새로운 인덱스를 생성해보자.

-- 기존 PK 제거
ALTER TABLE dbo.TBLINSATEST DROP CONSTRAINT PK__TBLINSATEST__IBSADATE__NUM
-- 새로운 PK 생성
ALTER TABLE dbo.TBLINSATEST ADD CONSTRAINT PK__TBLINSATEST__NUM PRIMARY KEY (num)
-- buseo 컬럼에 대한 인덱스 생성
CREATE INDEX IDX__TBLINSATEST__BUSEO ON dbo.TBLINSATEST (buseo)

SELECT
	jikwi
,	city
,	buseo
FROM dbo.TBLINSATEST WITH(NOLOCK)
WHERE buseo = N'개발부'

위의 쿼리에 대한 실행계획는 아래와 같다.

IDX__TBLINSATEST__BUSEO 인덱스를 거쳐 KEY ROOKUP을 7168회 수행했고, 

 

총 논리적 읽기는 14813가 발생했다. 

 

만약 SELECT 쿼리에 jikwi,city 란 컬럼이 포함되지 않았다면, INDEX SEEK 만으로 깔끔하게 처리되겠지만,

인덱스에 포함되지 않은 컬럼들이 존재하므로 KEY ROOKUP을 많이 수행하고 있다. (랜덤 액세스)

 

 

클러스터형 인덱스가 있으면 넌클러스터형 인덱스에 저장된 클러스터형 인덱스의 키 값으로 

 

클러스터형 인덱스를 다시 탐색하므로 RID를 통해서 테이블 로우를 찾아갈 때보다 

 

페이지 I/O가 더 많이 발생할수 밖에 없다.

 

아래의 그림은 위 쿼리의 처리과정을 나타낸다.

 

jikwi, city 컬럼 때문에 KEY ROOKUP을 7168회나 수행하였으며,

 

이 과정에서 I/O가 많이 발생하는것을 볼 수 있다. 

이럴 때 jikwi, city 컬럼 자체를 IIDX__TBLINSATEST__BUSEO 인덱스에 포함시키게 되면

 

인덱스만 읽고  KEY ROOKUP 없이 데이터를 가져 올 수 있게된다.

DROP INDEX IDX__TBLINSATEST__BUSEO__INCLUDE ON dbo.TBLINSATEST

CREATE INDEX IDX__TBLINSATEST__BUSEO__INCLUDE ON dbo.TBLINSATEST (buseo) INCLUDE (jikwi,city)

SELECT
	jikwi
,	city
,	sudang
FROM dbo.TBLINSATEST WITH(NOLOCK,INDEX(IDX__TBLINSATEST__BUSEO__INCLUDE))
WHERE buseo = N'개발부'

 

jikwi, city 컬럼이 인덱스에 추가되었으므로 

 

인덱스를 읽은 후에 KEY ROOKUP 과정을 거치치 않아도 된다. 

 

논리적읽기가 기존에는 14813건이었는데 커버드 인덱스 생성 후 31건으로 기존부하의 0.2% 정도이다.

아래의 그림은 커버드 인덱스를 사용한 후 쿼리의 처리과정을 나타낸 것이다. 

 

커버드 인덱스를 통해서 jikwi, city 컬럼은 힙영역에 데이터 페이지에 함께 저장되므로 

 

따로 테이블을 랜덤 액세스 할 필요가 없어진다. 

 

 

커버된 인덱스를 잘 활용하면 조회 성능이 극적으로 향상되기도

 

하지만, 그렇다고 모든 컬럼을 인덱스에 포함시키면 DML 성능에 문제가 생길 수 있다. 

 

해당 쿼리에 수행 빈도와 중요성 등을 고려하여 어떤 컬럼을 추가할지 결정해야한다.