북마크 룩업

코다람쥐 2022. 4. 15. 14:08

1. Index Seek가 나쁜경우

Index Scan이 항상 나쁜 것은 아니고, Index Seek가 항상 좋은 것도 아니다.
인덱스를 활용하는데 어떻게 느릴 수가 있을까?

Non-Clustered의 경우 아래와 같은 방식으로 데이터를 찾아나간다.
1) RID -> Heap Table (Bookmark Lookup)
2) Key -> Clustered

USE Northwind;

SELECT *
INTO TestOrders
FROM Orders;

SELECT *
FROM TestOrders;

CREATE NONCLUSTERED INDEX Orders_Index01
ON TestOrders(CustomerID);

-- 인덱스 번호
SELECT index_id, name
FROM sys.indexes
WHERE object_id = object_id('TestOrders');

--조회
DBCC IND('Northwind', 'TestOrders', 2);

SET STATISTICS TIME ON; -- 경과 시간 보기
SET STATISTICS IO ON; -- 논리적 읽기 보기
SET STATISTICS PROFILE ON -- 실제로 실행된 순서 보기

위의 구문은 임시테이블을 만들고 Ordedrs_Index01라는 이름의 새로운 인덱스 생성해주었다.

SELECT *
FROM TestOrders
WHERE CustomerID = 'QUICK';

인덱스를 사용해서 데이터를 추출했음에도 불구하고 Table Scan이 떴다.

그 이유는 Index Seek보다 Scan이 빠르다고 프로그램이 판단했기 때문!
논리적 읽기 20

SELECT *
FROM TestOrders WITH(INDEX(Orders_Index01)) -- Index Seek 강제하기
WHERE CustomerID = 'QUICK';

정말로 Index Seek가 더 느린건지 확인하기 위해 WITH(INDEX(Orders_Index01)이라는 구문을 넣어서 Index Seek를 강제해보았다.

그 결과 정말 논리적 읽기의 값이 30이 떴다. Indes Scan의 값이 20에 뜬 것에 비해서 정말로 느리다.

그 이유는 RID -> Heap Table 부분에서 논리적 읽기가 28번이나 수행되었다.
결론적으로 대부분의 경우 Index Seek가 좋지만 경우에 따라서 Index Seek가 Index Scan보다 더 느릴 수도 있다.

2. 검색조건 개선

방법1) 검색조건을 1개 더 추가해보자.

SELECT *
FROM TestOrders WITH(INDEX(Orders_Index01)) 
WHERE CustomerID = 'QUICK' AND ShipVia = 3;

논리적 읽기 30

결론. Orders_Index01이 커버하는 인덱스는 CustomerID뿐이기 때문에 실제로 데이터를 확인해보기 전까지는 ShipVia를 알 수 없기 때문에 의미없는 조건이 된다.

방법2) Covered Index

그렇다면 ShipVia도 인덱스로 설정하면? -> 복합인덱스로 설정하기

DROP INDEX TestOrders.Orders_Index01; -- 기존의 인덱스 삭제
CREATE NONCLUSTERED INDEX Orders_Index01
ON TestOrders(CustomerID, ShipVia); -- 복합인덱스 생성

SELECT *
FROM TestOrders WITH(INDEX(Orders_Index01)) 
WHERE CustomerID = 'QUICK' AND ShipVia = 3;

논리적 읽기 10

이처럼 실제로 검색하려던 컬럼들을 모두 꽉꽉 채워서 인덱스로 설정하는 것을 Covered Index라고 한다.

결론. 논리적 읽기가 10으로 줄은 것으로 보아 효과가 있다.

Q) 그렇다면, 무조건 Convered Index로 추가해주면 장땡인가?
A) NOPE. 서칭은 빠르지만, DML(Insert, Update, Delete)연산을 할 때 작업에 부하가 걸린다.

따라서, 상황마다 다른데 DML연산이 많이 들어가면 Convered Index를 지양하고, DML연산이 적고 서칭이 많이 필요하게 되면 사용하는 것이 좋다.

방법3) INCLUDE

DROP INDEX TestOrders.Orders_Index01; -- 기존의 인덱스 삭제
CREATE NONCLUSTERED INDEX Orders_Index01
ON TestOrders(CustomerID) INCLUDE (ShipVia); 

SELECT *
FROM TestOrders WITH(INDEX(Orders_Index01)) 
WHERE CustomerID = 'QUICK' AND ShipVia = 3;

INCLUDE : 키 값으로 CustormerID만 정렬하고 리프페이지에 ShipVia의 정보를 추가로 들고있겠다는 뜻

논리적 읽기 20

결론. 논리적 읽기가 30->20으로 줄은 것으로 보아 효과가 있지만 방법2(논리적읽기 10)보다는 덜 효율적이다.

방법4) Clustered Index

방법2, 방법3에 대한 노력에도 불구하고 답이 안보인다면 최후의 보루로 쓸 수 있는 방법은 Clustered Index를 사용하는 것이다.

하지만 두 가지의 단점이 있다.

단점1 : Non-Clustered인덱스에 악영향을 줄 수 있음.

단점2 : 테이블당 1번만 사용가능

결론. 가장 중요하고 효율적인 컬럼에 클러스터드 인덱스를 신중하게 넣어야한다.

요약

- 논클러스터드 인덱스가 악영향을 주는 경우? -> 북마크 룩업이 심각한 부하를 야기할 때

- 대안?
옵션1) Convered Index. 단점 : DML연산의 성능 저하
옵션2) Index에 INCLUDE로 힌트를 남긴다.
옵션3) Clustered Index. 단점 : Non-Clustered인덱스에 악영향을 줄 수 있음. 1번만 사용가능