SQL (22) - 북마크 룩업

이번 포스트에서는 SQL에서 Non-Clustered Index를 사용하는 경우 수행하는 북마크 룩업에 대해 알아보겠습니다.

 

1) Non-Clustered 인덱스

Non-Clustered 인덱스에서는 Clustered 인덱스와는 다르게 2가지의 방법으로 데이터를 탐색합니다.

 

첫번째는, Key를 사용해 Clustered 인덱스로부터 데이터를 가져오는 방법입니다.

이 경우 Clustered 인덱스와 같이 적용되어야 사용이 가능합니다.

 

두번째는, Heap RID를 통해 Heap Table에 접근하여 데이터를 가져오는 방법입니다.

Clustered 인덱스가 적용되지 않은 경우에만 사용이 가능하여

이 방법을 북마크 룩업이라고 표현합니다.

 

두번째 방법인 북마크 룩업에 대해 알아보기 위해 

예제 테이블과 인덱스를 만들어보았습니다.

 

2) 예제

SELECT *
INTO TestOrders
FROM Orders;

SELECT *
FROM TestOrders;

-- Non-Clustered 인덱스 생성
CREATE NONCLUSTERED INDEX Orders_Index01
ON TestOrders(CustomerID);

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

-- INDEX 조회
DBCC IND('Northwind', 'TestOrders', 2);
--	9120
-- 9080 9088 9089

-- 쿼리의 정보를 보여주는 명령어(세번째 명령어는 실제 쿼리의 동작 순서를 보여줌)
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;

 

위 쿼리의 실행 결과는 다음과 같습니다.

인덱스 번호

 

인덱스 조회

 

이제 생성된 Non-Clustered 인덱스를 사용하여 북마크 룩업을 알아보겠습니다.

 

3) 특정 데이터 탐색

SELECT *
FROM TestOrders
WHERE CustomerID = 'QUICK';

 

위와 같이 쿼리를 구성하였습니다.

실행 후의 결과는 다음과 같습니다.

 

 

 

Index와 관련된 연산이 아닌 Table Scan이 발생한 이유는

DBMS가 판단하기에 Table Scan이 더 효율적이기 때문입니다.

 

4) 인덱스 사용을 강제하여 검색

이번에는 이전의 방식이 아닌 인덱스 사용을 강제하여 검색해보겠습니다.

SELECT *
FROM TestOrders WITH(INDEX(Orders_Index01))
WHERE CustomerID = 'Quick';

 

실행 결과는 다음과 같습니다.

 

 

 

총 30개의 논리적 읽기가 수행되었습니다.

메세지를 확인해보면 RID LookUp에서 28개의 행에 대해 28개의 연산을 수행하였습니다.
따라서 유추할 수 있는 결과는 28개의 룩업을 시도하여 28개의 행을 찾아냈다는 것입니다.

 

동작 방식은 Index Seek + RID Look up으로 이루어졌습니다.
논리적 읽기로 보아 상황에 따라 Index Seek보다 Index Scan이 효율적일 수 있다는 것을 알 수 있습니다.

 

5) 인덱스 검색을 하되, 조건을 추가

이번에는 인덱스 검색을 하되, 조건을 하나 추가하여 검색해보겠습니다.

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

 

위 쿼리의 실행 결과는 다음과 같습니다.

 

 

 

 

이전과 별반 차이없이 30개의 논리적 연산이 수행되었지만

RID LookUp에서 8개의 행에 대해 28개의 연산을 수행했다는 차이점이 존재합니다.
이는조건을 만족하는 행이 8개 밖에 없다는 것을 의미합니다.

 

위의 결과로 보아 Index Scan에서 많은 연산 비용을 소모하는 것은 RID Lookup인 것을 알 수 있습니다.

이제부터 어떻게 하면 RID Lookup의 연산을 줄일 수 있을 지 확인해보겠습니다.

 

 

6) 첫 번째 방법 - 복합 인덱스(Covered Index) 사용

-- 우선 인덱스를 삭제 
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개의 논리적 읽기가 수행되었고,

특히 RID Lookup 연산에서 수행횟수가 많이 줄어들은 것을 볼 수 있습니다.

 

위와 같이 감소된 이유는 복합 인덱스를 통해 CustomerID와 ShipVia를 동시에 사용하여

단일 인덱스와는 달리 Heap Table에 접근하지 않고도 Leaf Page에서 데이터를 검색할 수 있기 때문입니다.

 

그러나 복합 인덱스는 연산을 효율적으로 줄일 수 있지만
데이터 조작어인 DML연산(INSERT, DELETE, UPDATE)의 부하를 증가시키는 문제점이 있습니다.

 

따라서 이번에는 다른 방법을 사용해보도록 하겠습니다.

 

7) 두 번째 방법 - Include

Include를 사용하게 되면 해당하는 열을 데이터를 찾을 때 참고하는 용도로 사용합니다.

쿼리는 다음과 같습니다.

-- CustomerID는 Non-Clustered 인덱스로, ShipVia는 참고하는 용도로 사용
CREATE NONCLUSTERED INDEX Orders_Index01
ON TestOrders(CustomerID) INCLUDE (ShipVia);

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

 

위 쿼리의 실행 결과는 다음과 같습니다.

 

 

이전의 쿼리와 마찬가지로 Index Seek + RID Look up 동작으로 데이터를 탐색했습니다.
탐색 과정에서 논리적 읽기는 10번 수행되었습니다.
이 과정에서 알 수 있는 것은 RID Lookup에서 8번 룩업을 시도하여 8개의 행을 찾아냈다는 것입니다.

 

이전의 복합 인덱스와 효율은 차이가 나지 않지만, 

DML 연산에 부하를 주지 않는다는 장점이 존재합니다.

 

만약, 위와 같은 경우에도 부하를 줄이지 못했다면

결국 Clustered 인덱스를 사용하는 방법만이 남게 됩니다.

그러나 Clustered 인덱스는 데이터 테이블에 하나만 존재하여야 하므로 신중하게 사용해야 합니다.

 

8) 결론 - Key Lookup의 부하를 줄이는 방법

• 복합 인덱스 사용 : 단, DML 연산의 부하에 주의
• include 사용 : 복합 인덱스와 다르게 DML 연산에 부하를 주지 않음
• Clustered Index 사용 : 가장 좋지만 신중하게 사용해야 함