Phoenix - Joins (조인)

Phoenix 는 표준 SQL 조인 문법을 조인합니다.

아래와 같이 3개의 거래 관련 테이블과 row들이 있다고 가정해보겠습니다.(Orders, Customers, Items)

Orders 테이블 :

OrderID	CustomerID	ItemID	Quantity	Date
1630781	C004	I001	650	09-01-2013
1630782	C003	I006	2500	09-02-2013
1630783	C002	I002	340	09-03-2013
1630784	C004	I006	1260	09-04-2013
1630785	C005	I003	1500	09-05-2013

Customers 테이블 :

CustomerID	CustomerName	Country
C001	Telefunken	Germany
C002	Logica	Belgium
C003	Salora Oy	Finland
C004	Alps Nordic AB	Sweden
C005	Deister Electronics	Germany
C006	Thales Nederland	Netherlands

Items 테이블 :

ItemID	ItemName	Price
I001	BX016	15.96
I002	MU947	20.35
I003	MU3508	9.60
I004	XC7732	55.24
I005	XT0019	12.65
I006	XT2217	12.35

위 테이블에 대해 아래와 같이 query 을 해보겠습니다.

SELECT O.OrderID, C.CustomerName, C.Country, O.Date
FROM Orders AS O
INNER JOIN Customers AS C

ON O.CustomerID = C.CustomerID;

query 수행 결과는 아래와 같습니다.

O.OrderID	C.CustomerName	C.Country	O.Date
1630781	Alps Nordic AB	Sweden	09-01-2013
1630782	Salora Oy	Finland	09-02-2013
1630783	Logica	Belgium	09-03-2013
1630784	Alps Nordic AB	Sweden	09-04-2013
1630785	Deister Electronics	Germany	09-05-2013

인덱스를 이용한 조인

Join 쿼리를 실행할 때 보조 인덱스는 자동으로 사용됩니다. 위 예제 테이블에서 “Orders” 와 “Items” 테이블에 index 를 부여하겠습니다.

CREATE INDEX iOrders ON Orders (ItemID) INCLUDE (CustomerID, Quantity);
CREATE INDEX i2Orders ON Orders (CustomerID) INCLUDE (ItemID, Quantity);

CREATE INDEX iItems ON Items (ItemName) INCLUDE (Price);

“Items” 테이블과 “Orders” 테이블을 조인하여 아이템별 총 판매액을 구할 수 있습니다.

SELECT ItemName, sum(Price * Quantity) AS OrderValue
FROM Items
JOIN Orders
ON Items.ItemID = Orders.ItemID
WHERE Orders.CustomerID > 'C002'

GROUP BY ItemName;

위 쿼리의 결과는 아래와 같습니다.

ItemName	OrderValue
BX016	10374
MU3508	14400
XT2217	46436

위 쿼리의 실행 꼐획은 아래와 같습니다. (Explain <query> 로 실행 가능)

CLIENT PARALLEL 32-WAY FULL SCAN OVER iItems
    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [iItems.0:ItemName]
CLIENT MERGE SORT
    PARALLEL INNER-JOIN TABLE 0

        CLIENT PARALLEL 32-WAY RANGE SCAN OVER i2Orders ['C002'] - [*]

이 경우, 컬럼 “ItemName” 의 값이 indexing 되어 있는 index table “iItems” 는 “Items”  테이블을 대체하기 위해 사용됩니다. 이는 GROUP BY 에서도 이득입니다.

반면, “Orders” 테이블 에서는 index table “i2Orders” 이 선호됩니다. WHERE 절 이하로 인해 Ragne Scan 이 적용되기 때문입니다.

그룹 조인과 파생 테이블

Phoenix 는 복합 조인 문법(그룹 조인 또는 서브 조인) 또한 지원하고, 파생 테이블(서브 쿼리 테이블 - inline view)과도 조인합니다.

다른 조인들이 실행 되기 이전에 특정 조인을 우선 실행할 수 있도록 괄호를 이용하여 조인을 그룹핑 할 수 있습니다.

SELECT O.OrderID, I.ItemName, S.SupplierName
FROM Orders AS O
LEFT JOIN
    (Items AS I
    INNER JOIN Suppliers AS S
    ON I.SupplierID = S.SupplierID)

ON O.ItemID = I.ItemID;

위 query 를 subquery(파생테이블)을 이용하여 변경해보도록 하겠습니다.

SELECT O.OrderID, J.ItemName, J.SupplierName
FROM Orders AS O
LEFT JOIN
    (SELECT ItemID, ItemName, SupplierName
    FROM Items AS I
    INNER JOIN Suppliers AS S
    ON I.SupplierID = S.SupplierID) AS J

ON O.ItemID = J.ItemID;

해쉬조인 VS 소트머지 조인

기본적으로 해쉬조인은 다른 조인 알리고즘에 비해 성능이 좋습니다. 그러나 한계 또한 지니고 있는데, 드라이빙 테이블이 메모리보다 작아야 합니다.

Phoenix 는 조인 수행을 빠르게 하기 위해 해쉬, 소트머지 둘 다 지원하고 있습니다.

그러나, Phoenix 는 현재 일반적으로 해쉬조인이 빠르기 때문에 항상 해쉬조인 알고리즘을 사용합니다. 소트머지 조인을 강제로 사용하기 위해서는 “USE_SORT_MERGE_JOIN” 이라는 힌트를 적용해야만 합니다.

두 알고리즘들 중 더 나은 알고리즘을 선택하는 문제는(해쉬조인을을 위해 더 적은 관계를 감지하는 부분과 함께) 앞으로 테이블 통계에 의해 제공되는 권고에 따라 자동으로 수행될 예정입니다.

Foreign Key 에서 Primary Key 로의 조인 최적화

종종 자식 테이블의 foreign key를 부모 테이블의 primary key 와 맵핑하는 자식-부모 테이블간의 조인이 발생합니다.

이런 경우, Phoenix 는 부모 테이블을 Full Scan 하는 대신 자식 테이블의 결과에서 얻은 foreign key 값에 기반하는 Skip Scan 이나 Range Scan 으로 드라이브 합니다.

Phoenix 는 조인키에서 일부 키들을 추출하고 정렬할 것입니다. 그러기 위해 부모 테이블 스캔이 가능한 가장 적절한 키 힌트나 범위를 얻을 수 있습니다.(???)

“Employee” 라는 부모 테이블과 “Patent” 라는 자식 테이블의 예를 보겠습니다.

CREATE TABLE Employee (

    Region VARCHAR NOT NULL,
    LocalID VARCHAR NOT NULL,
    Name VARCHAR NOT NULL,
    StartDate DATE NOT NULL,

    CONSTRAINT pk PRIMARY KEY (Region, LocalID)

);

CREATE TABLE Patent (
    PatentID VARCHAR NOT NULL,
    Region VARCHAR NOT NULL,
    LocalID VARCHAR NOT NULL,
    Title VARCHAR NOT NULL,
    Category VARCHAR NOT NULL,
    FileDate DATE NOT NULL,

    CONSTRAINT pk PRIMARY KEY (PatentID)

);

이제 filedate 가 2000년 1월 이후 인 구성원을 찾아 그들의 이름과 특허 갯수를 조회해 보겠습니다.

SELECT E.Name, E.Region, P.PCount
FROM Employee AS E
JOIN
    (SELECT Region, LocalID, count(*) AS PCount
    FROM Patent
    WHERE P.FileDate >= to_date('2000/01/01')
    GROUP BY Region, LocalID) AS P

ON E.Region = P.Region AND E.LocalID = P.LocalID

위 쿼리는 “Employee” 테이블에서 Skip Scan 으로 동작할 것입니다. 그리고 런타임 키 힌트를 계산하기 위해 “Region” 컬럼과 “LocalID” 컬럼을 조인키로 사용할 것입니다.

아래는 “Employee” 테이블에 대해 최적화 한것과 하지 않은 쿼리에 대한 수행 시간입니다. (“Empolyee”  는 약 5000000건, “Patent” 는 약 1000건 기준)

최적화 비적용	최적화 적용
8.1s	0.4s

그러나 자식 테이블의 foreign key 값들이 부모 테이블의 primary key 공간을 차지하는데 시간이 소요되기 때문에, Skip Scan 을 사용하는 것이 더 느려질 수도 있습니다.

NO_CHILD_PARAENT_OPTIMIZATION 힌트를 이용하여 optimizatino 을 항상 비활성화 할 수 있습니다.

(테이블 통계는 이 두 스키마 사이에서 더 나은 선택을 할 수 있도록 지원될 예정입니다.)

설정

조인 쿼리에 해쉬 조인을 사용하기로 했다면, 조인 테이블들 중 하나는 다른 테이블들의 데이터를 가지고 있는것으로 간주되는 모든 서버들에 브로드캐스트 되기 때문에 메모리 크기보다 작아야 합니다.

그리고 별개로 region server는 작은 테이블을 수용할 수 있도록 힙 사이즈가 충분해야 합니다.

다음으로는 해쉬 조인을 수행하는데 중요한 파리미터들에 대해 알아보겠습니다.

1. phoenix.query.maxServerCacheBytes

• 압축되거나 region server 로 보내기 전의 테이블 결과의 최대 크기
• 설정값보다 크게 직렬화를 시도하면 MaxServerCacheSizeExceededExeption 예외 발생
• Default : 104,857,600

2. phoenix.query.maxGlobalMemoryPercentage

• 모든 쓰레드들으 사용할 총 힙 메모리의 백분율
• 살아있는 캐시의 총 합은 글로벌 메모리 풀 사이즈보다 작아야 함. 그렇지 않으면 InsufficientMemoryException 발생
• Default : 15

3. phoenix.coprocessor.maxServerCacheTimeToLiveMs

• 서버 캐시의 최대 지속 시간(millisecond). 캐시 입력들은 마지막 접근으로부터 설정 시간만큼 지난후 만료
• 서버사이드에 IOException(“Could not find hash cache for joinId”) 발생할 때 조정해야 함. “Earlier hash caches(s) might have expired on servers”) 같은 warning 이 발생하면 이 설정값을 증가 시켜주는게 좋음
• Default : 30000

Phoenix 에서는 위 설정을 통해 언급된 exception 들을 제거할 수도 있겠지만, 이보다는 쿼리를 먼저 최적할 것을 권고하고 있습니다.

쿼리 최적화

이제 우리는 해쉬 조인을 수행한다면 쿼리 실행을 위한 충분한 메모리가 있어야 한다는 걸 알고 있습니다. 하지만 급하게 설정을 바로 변경하는 것보다, phoenix 의 아키텍처에 대해 이해하고, 조인 쿼리에 포함된 테이블의 순서를 변경해볼 필요가 있습니다.

기본 조인 순서, 그리고 어느쪽이 작은 테이블로 서버에 캐시 될지는 아래와 같습니다.

1. lhs INNER JOIN rhs 

• rhs 테이블이 서버에 캐시

2. lhs LEFT OUTER JOIN rhs

• rhs 테이블이 서버에 캐시

3. lhs RIGHT OUTER JOIN rhs

• lhs 테이블이 서버에 캐시

2개 테이블 이상의 조인 쿼리에서는 조인 순서가 더욱 복잡합니다. 이럴 경우 “EXPLAIN query” 를 통해 실제 실행계획을 확인할 수 있습니다. 멀티플 이너 조인 쿼리에서는 기본적으로 스타 조인을 적용합니다.

이 말은 리딩 테이블(왼쪽 테이블)은 오른쪽 테이블과 조인 하는데 오직 한 번만 스캔 되어진다는 것을 의미합니다.

만약 오른쪽 테이블의 전체 크기가 메모리 크기를 초과한다면 NO_START_JOIN 힌트를 이용하여 스타 조인을 비활성화 할 수 있습니다.

예제를 살펴보겠습니다.

SELECT O.OrderID, C.CustomerName, I.ItemName, I.Price, O.Quantity
FROM Orders AS O
INNER JOIN Customers AS C
ON O.CustomerID = C.CustomerID
INNER JOIN Items AS I

ON O.ItemID = I.ItemID;

위 쿼리의 기본 조인 순서는 다음과 같습니다.

1. SCAN Customers --> BUILD HASH[0]

   SCAN Items --> BUILD HASH[1]

2. SCAN Orders JOIN HASH[0], HASH[1] --> Final Resultset

NO_START_JOIN 힌트를 사용해보겠습니다.

SELECT /*+ NO_STAR_JOIN*/ O.OrderID, C.CustomerName, I.ItemName, I.Price, O.Quantity
FROM Orders AS O
INNER JOIN Customers AS C
ON O.CustomerID = C.CustomerID
INNER JOIN Items AS I

ON O.ItemID = I.ItemID;

힌트 사용 후, 조인 순서입니다.

1. SCAN Customers --> BUILD HASH[0]
2. SCAN Orders JOIN HASH[0]; CLOSE HASH[0] --> BUILD HASH[1]

3. SCAN Items JOIN HASH[1] --> Final Resultset

 

테이블의 전체 데이터 셋이 메모리를 소비하지 않아도 된다는 것은 가치가 있습니다. 대신 오직 쿼리에 의해 참조된 컬럼들과 조건을 만족하는 레코드들만이 서버 해쉬 테이블에 구축될 것입니다.

제한

너무 많은 데이터들간의 조인에서는 메모리 불충분으로 인한 실패 가능성이 있습니다.

참죄 : https://phoenix.apache.org/joins.html