9. 그래픽 실행 계획의 기초 - Table Joins

Table Joins

 

지금까지 우리는 하나의 테이블만 이용해서 실행 계획들을 살펴보았다. 지금부터는 두 개 이상의 테이블을 연결하기 위해 조인이라는 것을 해 보도록 하겠다. 아래는 HumanResources 스키마의 테이블들을 이용해서 사원 정보를 조회하는 쿼리이다. 

 

SELECT e.[Title],        a.[City],        c.[LastName] + ', ' + c.[FirstName] AS EmployeeName FROM [HumanResources].[Employee] e JOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID] = ed.[EmployeeID] JOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID] JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID];

 

실행 계획은 다음과 같다.

 

[그림 1] Table Join

 

이 쿼리는 실행 계획에서도 파악할 수 있듯이 상당히 많은 비용을 사용하는 연산들을 하고 있다. 여기에서 가장 많은 비용을 사용하는 연산은 다음의 3가지로 압축할 수 있다.

• [Person].[Address] 테이블에 대한 Index Scan 연산 : 45%
• [HumanResource].[EmployeeAddress] 테이블과 [Person].[Address] 테이블 사이의 Hash Match 조인 연산 : 28%
• [Person].[Contact] 테이블에 대한 Clustered Index Seek 연산 : 18%

이미 설명한 바 있듯이 비용은 실행 트리의 오른쪽에서 왼쪽으로 이동해 가며 누적 표시된다. 상대 비용은 각각의 실행 계획 아이콘에서 확인할 수 있다. 

 

자! 그럼 하나 하나 살펴보기로 하자. 그래픽 실행 계획을 읽는 순서는 "오른쪽에서 왼쪽으로, 위에서 아래로"라는 것을 상기시키고...

가장 먼저 오른쪽 상단에 [HumanResources].[EmployeeAddress] 테이블에 대한 Index Scan이 보인다. 그리고 그 아래로 [Person].[Address] 테이블에 대한 또 하나의 Index Scan이 보인다. 이 연산은 가장 비용이 높은 연산이다. ToolTip을 열어보도록 하자. IX_Address_AddressLine_AddressLine2_City_StateProvinceId_PostalCode 인덱스를 스캔하여 우리가 필요로 하는 데이터를 얻기 위해 19,614 행을 엑세스하고 있음을 금방 확인할 수 있을 것이다.

 

[그림 2] Table Join - Index Scan 연산 ToolTip

 

그리고, Output List에서 보여지듯이 이 연산을 통해 [AddressID]와 [City] 컬럼을 얻었다. 쿼리 옵티마이저는 인덱스를 통해 해당 데이터를 취득하기 위한 최적의 방법을 해당 테이블의 인덱스와 컬럼의 선택도를 기반으로 계산한다. 19,614행을 얻기 위해 총 쿼리 비용의 45%가 소요되며, 예상 연산 비용(Estimated Operator Cost)은 0.180413이다. 예상 연산 비용은 해당 연산을 수행하기 위해 필요한 비용을 옵티마이저가 내부적으로 계산된 수치로써, 이 값은 작으면 작을수록 좋다.

 

 

 

Hash Match(Join)

  

계속해서 위의 예제를 살펴보자.  위의 두 Index Scan을 통해 얻어진 결과를 Hash Match Join을 통해 결합하고 있다. 이 연산은 이 쿼리에서 두 번째로 비싼 비용을 사용하는 연산으로 ToolTip에서 보여지는 내용은 다음과 같다.

 

[그림 3] Table Join - Hash Match 연산 ToolTip

 

Hash Match Join을 이해하기 위해서는 먼저, hashing과 hash table이라는 두 가지 컨셉을 알아야 한다.

 

Hashing(이하 해싱)은 데이터를 좀 더 빨리 검색하기 위해 고안된 프로그램적인 기술로써, 데이터가 저장되어 있는 주소를 함수로 계산한 후, 계산된 주소로 직접 접근 가능하게 하는 기법이다. 이 때 계산에 사용된 함수를 hashing function(이하 해시 함수)이라고 한다. 이 방법을 통해 테이블 내의 특정 행은 그 행을 나타내는 어떤 유일한 값으로 변환할 수 있다. 변환된 값은 암호화되며, 반대로 암호화된 해시 값은 원래의 데이터로 되돌릴 수도 있다.

 

Hash table(이하 해시 테이블)은 해시 함수에 의해 계산된 함수 값(주소)를 저장하고 있는 표 형태의 데이터 구조이다. 일반적으로 bucket과 slot의 배열로 선언되어 있는데, 계산된 함수 값들은 각각의 bucket에 균등하게 나뉘어져 bucket내의 slot에 저장된다.  

 

자 이제 Hash Match Join의 메카니즘에 대해 알아보자.

먼저, 조인에 참여하는 두 개의 테이블 중 상대적으로 크기가 작은 테이블을 해시 함수에 통과시켜 그 결과 값을 해시 테이블에 저장한다. 그런 다음, 큰 테이블을 한 행, 한 행, 해시 테이블에 매칭을 시켜가며 값을 찾아내 간다. 여기서 기억해야 할 중요한 사실 하나! 해시 테이블에는 실제 데이터가 아닌 해시 값이 저장되기 때문에, 작은 테이블이 해싱되어야 해시 테이블의 크기를 작게 유지하여 비교 속도가 빨라지게 된다. 따라서, 해시되는 테이블이 작으면 작을수록 속도가 빨라지게 된다.

 

Hash Match Join은 아주 작은 테이블과 아주 큰 테이블이 조인할 때 다른 조인들(Nested Loop Join, Merge Join)보다 월등한 성능을 발휘한다.  조인 컬럼에 대한 정렬이 필요없거나, 쓸만한 인덱스가 없을 경우에도 유용하다. Hash Match Join이 발생했다는 것은 다른 조인 방법이 마땅히 없다는 것을 의미한다. 따라서 실행 계획 내에 Hash Match Join 연산이 있다면 다음 사항을 유심히 살펴볼 필요가 있다.

• 인덱스가 없거나 인덱스가 잘못 작성되어 있는가?
• 쿼리에서 조건(WHERE)절을 빼먹지는 않았는가?
• 조건절에 변형이 일어나 인덱스를 사용하지 못하고 있지는 않은가?

만일, 조인이 되는 테이블에 적절한 인덱스를 추가하거나, 조건절을 제한하여 반환되는 데이터 양을 줄인다던지 혹은, 조건절의 변형을 제거하는 방법 등을 통해 다른 조인 방식을 유도해도 먹히지 않는다면, 쿼리 옵티마이저가 판단하기에 Hash Match Join이 가장 이상적인 조인 방법일지도 모른다.

 

이 예제에서 예상 행 수(282.216)와 실제 행 수(290) 는 약간의 차이를 보이고 있다. 이 차이가 크지 않다면 별 문제 없으나, 큰 차이를 보인다면 통계 정보가 오래되었다는 것을 의미하므로 UPDATE STATISTICS문이나 sp_updatestats 시스템 저장 프로시저를 이용해서 통계 정보를 갱신해 주어야 한다. 그렇지 않으면 예상 실행 계획과 실질 실행 계획가 달라지는 결과를 초래할 수도 있다.

 

이렇게 첫번째 Hash Match Join로 얻어진 결과는 [HumanResources].[Employee] 테이블에 대해 Clustered Index Scan한 결과와 다시 Hash Match Join을 하고 있다.

 

 

 

Clustered Index Seek

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

Hash Match Join 다음으로 [Person].[Contact] 테이블에 대해 Clustered Index Seek 연산을 수행하는 것이 보인다.

PK_Contact_ContactId 인덱스를 사용하고 있는데, 이 인덱스는 primary key이자 clustered index이다. 이 연산은 세 번째로 많은 비용을 사용하고 있다. 이 연산의 ToolTip은 다음과 같다.

 

[그림 4] Table Join - Clustered Index Seek 연산 ToolTip

 

여기서 Seek Predicates 섹션을 주목하길 바란다. [HumanResources].[Employee] 테이블의 [ContactID] 컬럼과 [Person].[Contact] 테이블을 직접 조인하고 있다.

 

 

 

Nested Loops Join

 

Clustered Index Seek 다음으로 Hash Match로 얻어진 결과와 Nested Loops Join을 통해 조인을 하고 있다.

 

[그림 5] Table Join - Nested Loops Join 연산 ToolTip

 

Nested Loops Join은 nested iteration이라 불리기도 한다. 이 연산은 두 개의 데이터 집합을 공급받아,

내측 집합(inner set)의 각각의 행에 대해 외측 집합(outer set)을 스캔해나가며 비교하는 조인 방식이다. 일반적으로 내외측 데이터 집합 모두 크기가 작을 경우 최적의 성능을 낸다. 데이터가 아주 크지 않은 이상 실행 계획에서 가장 많이 보이는 조인 방식이다.

 

 

 

Compute Scalar

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

이 쿼리의 실행 계획에서 마지막으로 설명할 내용은 Select 연산 바로 오른쪽에 있는 Compute Scalar 연산이다. 역시 ToolTip을 함께 보며 설명하도록 하겠다.

 

[그림 6] Table Join - Compute Scalar 연산 ToolTip

 

이는 단순히 계산으로 부터 얻어진 하나의 값, 즉 스칼라 값을 보여주고 있다. 

예제에서는 [Contact.LastName] 컬럼과 [Contact.FirstName] 컬럼을 콤마(,)로 연결하여  [EmployeeName]이라는 별칭으로 표시하고 있다.

 

 

 

Merge Join

 

이번에 소개할 내용은 Hash Match Join, Nested Loops Join에 이어 마지막 조인 방법인 Merge Join 연산이다. Merge Join을 확인하기 위해 다른 쿼리를 작성해 보도록 하자.

 

SELECT c.CustomerID FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID = oh.SalesOrderID JOIN Sales.Customer c ON oh.CustomerID = c.CustomerID

 

이 쿼리의 실행 계획은 다음과 같다.

 

[그림 7] Table Join - Merge Join 연산

 

실행 계획에 따르면 쿼리 옵티마이저는 먼저, [Customer] 테이블에 대해 Clustered Index Scan 연산을, [SalesOrderHeader] 테이블에 대해서는 Non-clustered Index Scan 연산을 수행한다. WHERE 조건절에 아무 것도 정의하지 않았기 때문에 각각의 테이블에서 모든 행을 반환한다.

 

그런 다음, Merge Join 연산을 통해 반환된 모든 행을 조인한다. Merge 즉, 병합을 위해서는 조인된 양쪽 컬럼이 반드시 정렬되어 있어야 한다. 그렇기에 Merge Join을 Sort Merge Join 이라고 부르기도 한다. 하지만 이 예제에서는 선행 연산들에 의해 조인 컬럼들이 이미 정렬되어 있기 때문에 별도의 정렬 작업이 필요없다. Merge Join은 예제처럼 조인 컬럼들이 정렬이 되어 있을 경우 매우 빠르지만 정렬 작업이 필요한 경우에는 비용이 많이 드는 방법이 될 수 있다. 

 

정렬 작업이 필요한 경우 쿼리 옵티마이저는 다음의 두 가지 옵션을 생각한다.

• 조인 컬럼을 정렬한 후 Merge Join을 한다.
• 별로 내키지는 않지만 Hash Match Join을 한다. Hash Match 연산을 하는 것이 최적이었다면 벌써 선택했겠지만...

최종적으로 쿼리 옵티마이저는 그 밖의 모든 경우의 수를 고려해서 둘 중 보다 적은 리소스를 사용하는 조인 방법을 선택한다.

 

[그림 7] Table Join - Merge Join 연산 ToolTip

 

요컨대, Merge Join의 성능의 핵심은 조인될 컬럼이 정렬이 되어 있느냐이다. 만일 그렇지 않다면 쿼리 옵티마이저는 Merge Join을 수행하기 전에 반드시 정렬을 해야만 하며, 정렬이 발생했다는 것은 Merge Join은 이미 이상적인 조인 방법이 아니라는 것을 의미한다. 이 경우 인덱스 설계를 재고할 필요가 있다.

 

 

다음 이야기 : 10. 그래픽 실행 계획의 기초 - WHERE절의 추가