8. 그래픽 실행 계획의 기초 - Single Table Query

Single Table Query

하나의 테이블(Single table)를 질의했을 때 나타나는 아주 단순한 실행 계획들이다.

 

Clustered Index Scan

   

 

☞ 주인장의 다른 포스트도 있어요!!!

AdventureWorks 데이터베이스의 Person.Contact 테이블에 대해 아래의 쿼리를 실행해 보자

 

SELECT * FROM Person.Contact

 

이 쿼리의 실행 계획은 아마 아래와 같이 나타날 것이다.

 

[그림 1] Clustered Index Scan의 예

 

필요한 데이터를 조회하기 위해 Clustered Index Scan 연산을 수행하였음을 확인할 수 있다. Clustered Index Scan 아이콘 위에 마우스를 갖다 대어 ToolTip 창을 확인해 보자. PK_Contact_ContactID라는 Clustered Index를 사용했으며 이 연산을 통해 얻어지는 예상 행 수는 19972행이다.

 

SQL Server의 인덱스는 B-tree 구조 인덱스이다. 혹자는 B-tree의 B가 Binary라고도 하지만 B-tree의 B는 Balanced 라는 것이 정설이다. Clustered Index도 다른 인덱스(Nonclustered Index)와 마찬가지로 인덱싱을 위해 인덱스의 키 구조를 저장한다. 하지만 Clustered Index는 Nonclustered Index와 달리 인덱스의 최하위 리프 레벨(leaf level)에 인덱스의 키 값 순으로 실제 데이터를 함께 저장한다. 따라서 Clustered Index는 테이블 당 하나만 만들 수 있다.

 

Clustered Index Scan은 쿼리에서 요청한 데이터를 조회하기 위해 인덱스 전 영역(혹은 대부분)을 스캔해 나가는 방식으로 Table Scan과 같은 컨셉 - Clustered Index의 최하위 리프 레벨(leaf level)에는 실제 데이터가 저장되어 있다는 것을 잊지 말자! - 이라고 생각해도 무방하다.

 

Index Scan은 주로 넓은 범위의 인덱스 영역을 조회할 때 발생한다. 이 경우 아래의 그림에서 처럼 인덱스의 키 값을 이용하여 하나 하나 랜덤 엑세스(Index Seek)를 하는 것보다 인덱스를 그냥 쭉 스캔(Index Scan)하는 것이 더 유리하다고 쿼리 옵티마이저는 판단한 것이다. 

 

 

[그림 2] Index Seek과 Index Scan의 비교

 

 

 

Clustered Index Seek

 

☞ 주인장의 다른 포스트도 있어요!!!

앞서 실행한 쿼리에 다음과 같이 WHERE 절을 추가해 보자.

 

SELECT * FROM Person.Contact WHERE ContactID = 1

 

그리고 이 쿼리의 실행 계획을 보도록 하자

 

[그림 3] Clustered Index Seek의 예

 

Index Seek는 데이터를 조회하는 방식에 있어 Index Scan과는 명백하게 다르다. 앞서 설명하였다시피 Index Scan은 인덱스의 전 영역 혹은 대부분의 영역을 순차적으로 스캔해 나가며 필요한 데이터를 찾아내 가는 반면, Index Seek는 주어진 인덱스의 키 값을 근거로 하여 필요한 데이터만 콕!콕! 집어낸다. 이는 우리가 책에서 특정 단어를 찾을 때 색인을 이용하는 것과 같은 맥락이다. 따라서 Clustered Index Seek는 Clustered Index Scan보다 훨씬 더 적은 비용이 든다. 또한 인덱스의 최하위 리프레벨에 실제 데이터가 저장되어 있으므로, 인덱스을 검색한 후 별도로 데이터 페이지에 엑세스할 필요도 없다.

 

위의 예제에서는 Person.Contact 테이블에 대해 PK_Contact_ContactId라는 Clustered Index를 이용하여 Clustered Index Seek 연산을 통해 ContactId = 1인 것만 콕! 집어서 가져오고 있다. 그리고 중요한 사실 하나! Clustered Index Seek의 ToolTip 창을 확인해 보면 Ordered 속성이 True라는 것을 확인할 수 있을 것이다. 이는 쿼리 옵티마이저에 의해 데이터가 정렬되었음을 의미한다.

 

 

 

Nonclustered Index Seek

 

이번에는 Nonclustered Index를 사용하기 위해 조금 다른 쿼리를 작성해 보도록 하자.

 

SELECT ContactID FROM Person.Contact WHERE EmailAddress LIKE 'sab%'

 

이번에는 IX_Contact_EmailAddress 라는 Nonclustered Index를 사용하였다. 여담이지만 Nonclustered Index Seek의 ToolTip 창에는 Index Seek라고 표시되어 있다. 마이크로소프트의 실수인듯 하다.

 

[그림 4] Nonclustered Index Seek의 예

 

Nonclustered Index Seek는 Clustered Index Seek와 마찬가지로 인덱스의 키 값을 이용하여 찾고자 하는 행에 직접 엑세스한다. 용어 자체의 의미에서도 알 수 있듯이 Clustered Index를 사용하는 대신 Nonclustered Index를 사용한다는 점을 제외하고는 Clustered Index Seek와 메카니즘은 같다. Clustered Index를 사용할지, 혹은 Nonclustered Index를 사용할지는 요청한 쿼리와 인덱스의 구조에 따라 쿼리 옵티마이저가 알아서 결정한다. 뒤에서 다시 설명하겠지만, Non-lustered Index를 사용하면 추가적인 I/O가 발생하는 경우도 있으므로 일반적으로 Clustered Index가 훨씬 효율적이다.

 

 

 

Key LookUp

  

앞에서 작성한 쿼리에 몇 개의 컬럼을 더 조회하도록 쿼리를 수정해 보겠다.

 

SELECT ContactID,        LastName,        Phone FROM Person.Contact WHERE EmailAddress LIKE 'sab%'

 

실행 계획은 다음과 같다.

 

[그림 5] Key LookUp의 예

 

드디어 우리는 단일 연산이 아닌 복합 연산 실행 계획을 만나게 되었다. 실행 계획을 읽는 순서는 이미 설명하였다. "오른쪽에서 왼쪽으로" 그리고 "위에서 아래로". 첫 번째 연산은 IX_Contact_EmailAddress 인덱스를 이용한 Index Seek 연산이다. 이 인덱스는 Non-unique, Nonclustered 인덱스로써 EmailAddress 컬럼을 포함하고 있다. 이 예제의 경우 IX_Contact_EmailAddress 인덱스는 Covering Index가 아니다.

 

그럼 Covering Index란 무엇인가? Covering Index란 조회하고자 하는 컬럼이 모두 인덱스 내에 포함되어 있는 Nonclustered Index를 의미한다. 따라서 이 쿼리의 경우 IX_Contact_EmailAddress 인덱스를 읽어야 할 뿐만 아니라, IX_Contact_EmailAddress 인덱스에 없는 다른 데이터를 조회하기 위해 Clustered Index인 PK_Contact_ContactID도 함께 읽어야만 한다.

 

Index Seek의 ToolTip를 보도록 하자. Output List에 EmailAddress와 ContactID 컬럼이 있음을 확인할 수 있다. 

 

[그림 6] Key LookUp - Index Seek의 ToolTip

 

sp_helpindex로 Person.Contact 테이블의 인덱스를 확인해 보면, IX_Contact_EmailAddress 인덱스의 인덱스 키에는 EmailAddress 컬럼 밖에 없다. 하지만 이 연산의 Output List에는 ContactID 컬럼도 포함되어 있다. 왜일까?

 

간단히 설명하자면, Clustered Index가 있는 테이블에 Nonclustered Index를 작성하게 되면 Nonclustered Index의 리프레벨은 Clustered Index의 키 값(ContactID)을 가리키기 때문 - 혹은, Nonclustered Index의 리프레벨에 Clustered Index의 키 값(ContactID)을 포함시키기 때문 - 이다.

 

이 키 값은 PK_Contact_ContactID Clustered Index를 Key Lookup하는데 사용된다. 아래의 그림에서 확인할 수 있듯이 Seek Predicates에서 Index Seek를 통해 공급받은 키인 Scalar Operator([AdventureWorks].[Person].[Contact].[ContactID])로 해당되는 행을 찾는다. Output List는 LastName과 Phone 컬럼이다.

 

[그림 7] Key LookUp - Key Lookup의 ToolTip

 

Key LookUp은 Clustered Index가 있는 테이블에서의 Bookmark Lookup(책갈피 조회)이다.

 

Key LookUp은 쿼리 옵티마이저가 단일 연산으로 행을 조회할 수 없어서, Clustered Key를 사용하여 Clustered Index에서 해당하는 데이터를 가져오는 과정을 의미한다. 실행 계획 내에 Key LookUp 연산이 있다는 것은 Covering Index 또는 Included Index로 성능을 개선할 여지가 있음을 나타낸다. Covering Index 및 Included Index는 쿼리에서 필요한 모든 컬럼을 포함하고 있기 때문에 Key LookUp 연산이 별도로 필요하지 않는다.

 

Key LookUp은 두 개의 연산으로 부터 얻어진 결과를 병합하기 위해 항상 Nested Loop Join 연산을 동반한다. 

 

[그림 8] Key LookUp -  Nested Loops 의 ToolTip

 

Nested Loops Join은 조인의 가~~~장 기본적인 형태이다. 예제의 경우 Key LookUp 연산이 필요하기 때문에, Nested Loops 연산을 통해 Index Seek와 Key LookUp 연산의 결과를 결합하고 있다. 만일 여기서 Key LookUp 연산이 필요없다면, 당연히 Nested Loops 연산 또한 더 이상 필요없게 된다.

 

 

 

Table Scan

  

이 연산자는 MS SQL Server 실행 계획 깨부수기를 진행하는 동안 수도 없이 보아 왔다. 따라서 여기서는 복습의 의미로 간단히 살펴보기로만 하겠다.

 

Table Scan 연산자는 테이블 - Heap, 혹은 Clustered Index가 없는 테이블이라는 표현이 더 명확하겠다. - 을 쭉 읽어나가며 필요한 행을 조회하는 연산이다.

 

SELECT * FROM [dbo].[DatabaseLog]

 

[그림 9] Table Scan

 

Table Scan은 다음과 같은 몇 가지 요인으로 인해 발생한다.

1. 테이블에 인덱스가 없거나 쓸만한 인덱스가 없는 경우
2. 위 예제처럼 테이블의 모든 행을 조회하는 경우
   상식적으로 생각해 봐도 전체 행을 조회하기 위해 인덱스를 사용한다는 것은 비효율적임이 틀림없다. 인덱스를 사용한다는 것은 결국 Bookmark LookUp이라는 추가적인 연산을 동반하기 때문이다. 
3. 쿼리 옵티마이저가 인덱스를 사용하는 것보다 Table Scan을 하는 것이 효율적이라고 판단하는 경우
   예를들어, 테이블에 유용한 인덱스가 있긴 하지만, 1페이지 내에 모든 데이터가 들어갈 정도로 테이블이 작을 경우, 쿼리 옵티마이저는 Index Scan보다는 Table Scan을 선택할 수도 있다.

 

 

 

RID LookUp

  

☞ 주인장의 다른 포스트도 있어요!!!

앞서 작성한 쿼리에서, Primary Key를 이용하여 [dbo].[DatabaseLog] 테이블을 조회하도록 아래와 같이 쿼리를 조금 수정해 보았다.

Index Seek와 RID LookUp 연산에서 얻어진 결과를 Nested Loops 연산으로 결합하는 실행 계획 결과를 얻을 수 있을 것이다.

 

SELECT * FROM [dbo].[DatabaseLog] WHERE [DatabaseLogID] = 1

 

[그림 10] RID LookUp

 

결과를 얻기 위해, 쿼리 옵티마이저는 먼저 Primary Key로 설정된 PK_DatabaseLog_DatabaseLogID 인덱스에 대해 Index Seek 연산을 수행한다. 이 인덱스에서 WHERE 절에서 요구한 행을 콕! 집어서 찾을 수 있기 때문에 PK_DatabaseLog_DatabaseLogID는 아주 똑똑한 인덱스이다. 하지만 이 인덱스에서 우리가 필요한 컬럼을 모두 얻을 수는 없다. 이 사실은 다음과 같이 확인할 수도 있다.

 

[그림 11] RID LookUp - Index Seek

 

Index Seek의 ToolTip을 열어보면, Output List에서 Bmk1000 라는 부분을 찾을 수 있을 것이다. 쿼리 옵티마이저는 이 Bmk1000이라는 표식을 통해 "이 Index Seek 연산은 실제로는 Bookmark LookUp 연산을 가진 실행 계획의 일부분"이라는 것을 우리에게 알려준다.

 

이어서 쿼리 옵티마이저는 RID LookUp 연산을 수행한다. RID LookUp 연산은 Clustered Index가 없는 Heap table에서 발생하는 책갈피 조회(Bookmark LookUp)의 일종으로써 Row Identifier(RID)를 이용하여 행을 조회한다. 바꾸어 말하면, Clustered Index가 없는 테이블에서는 RID로 인덱스와 힙을 연결한다는 것을 의미한다. 따라서, RID LookUp 연산에서도 Key LookUp 연산과 마찬가지로 추가적인 디스크 I/O가 발생한다. 

 

[그림 12] RID LookUp - RID LookUp

 

요컨데 Key LookUp 연산은 Clustered Index가 있는 테이블에서의 Bookmark LookUp이고, RID LookUp 연산은 Clustered Index가 없는 Heap에서의 Bookmark LookUp이라고 생각하면 간단할 것이다.

 

 

 

다음 이야기 : 9. 그래픽 실행 계획의 기초 - Table Joins