JOIN 순서에 따른 비용 절감

주제 : JOIN 순서에 따른 비용 절감

작성일 : 2009.03.16

작성자 : 유일환(ryu1hwan@empal.com / http://blog.naver.com/ryu1hwan ) 

 

안녕하세요. 유일환입니다.

SQLLeader에는 처음으로 글을 올려 봅니다.

부족한 글이지만, 시간 나는 대로 하나씩 올려보도록 하겠습니다.

2009년도 어느새 3월 중순에 들어서고,

황사가 불어오는 걸 보니, 어느새 봄이 성큼 다가오고 있는 거 같습니다.

꽃 내음이 아닌 모래 내음을 통해 봄이 오는걸 느껴야 하는 현실이 슬프네요.

(그래도 오늘 우리나라가 멕시코를 8:2로 통쾌하게 이겨줘서 너무 기분좋은 오후네요~)

 

오늘은 JOIN순서에 따라 비용이 어떻게 다른지에 대해 알아보도록 하겠습니다.

여기서는 실제 옵티마이져가 아닌 가상의 비용산출 방법을 통해 비용을 계산할 것입니다.

가상의 비용산출은 제가 마음대로 만들어낸 방법입니다.

SQL이 실행되는 각 연산마다 가상의 비용값을 주어서 SQL이 실행되는 비용을 산출 할 것입니다.

실제로는 옵티마이져가 비용값을 산출하지만,

이 옵티마이져는 SQL의 버젼,서버의 환경, 데이터의 환경에 따라 각각 다른 실행계획을 생성합니다.

여기서는 어떻게 JOIN의 순서를 정하는 것이 좋은가에 대해 이해하는 것이 목적이므로,

옵티마이져는 무시를 하고 저희가 직접 옵티마이져가 되어서 가상의 비용을 산출하도록 하겠습니다.

비용 산출 규칙은 다음과 같이 정하도록 하겠습니다.

1. 데이터를 Table에서 읽어올 때 : 1건당 비용 1

2. 데이터를 JOIN 할 때 : JOIN결과 1건당 비용 1

3. GROUP BY를 할 때 : GROUP BY 이전의 데이터 1건당 비용 1

4. JOIN, 또는 GROUP BY를 통해 가공된 결과에 대해서는 다시 읽게 될 때 추가 비용없음(JOIN 비용은 존재)

5. JOIN은 서술된 순서대로 발생됩니다.

간단하게 4개의 규칙만을 기억하시면 되겠습니다.

 

우리가 사용할 테이블은 다음과 같이 3개가 있습니다.

판매원
판매원ID	판매원등급
a	A
b	A
c	C

 

판매
판매일자	판매원ID	판매수량
2009-01-01	a	10
2009-01-02	a	5
2009-01-03	a	5
2009-01-04	a	5
2009-01-05	a	5
2009-01-01	b	5
2009-01-02	b	10
2009-01-03	b	10
2009-01-04	b	10
2009-01-01	c	5

 

월별목표
목표월	판매원ID	목표수량
200901	a	30
200901	b	20
200901	c	10

 

이렇게 판매원, 판매, 월별목표 3개의 테이블로 구성이 되어 있습니다.

위 3개 테이블을 이용해 만들어야 할 결과는 다음과 같습니다.

최종결과
판매원ID	판매원등급	1월목표	1월판매
a	A	30	30
b	A	20	35

판매원등급이 A인 판매원들에 대해 1월목표와 1월 판매를 출력하는 것입니다.

위와 같은 결과를 만들기 위한 SQL은 여러가지 방법이 있습니다.

여기서는 2가지의 SQL을 사용해 각각의 비용을 생각해 보도록 하겠습니다.

 

<SQL-1>모든 데이터를 한 번에 JOIN

SELECT  T1.판매원ID         ,T1.판매원등급         ,MAX(T3.목표수량) 1월목표         ,SUM(T2.판매수량) 1월판매 FROM    판매원 T1         INNER JOIN 판매 T2                 ON T1.판매원ID = T2.판매원ID                 AND T2.판매일자 LIKE '200901%'         INNER JOIN 월별목표 T3                 ON T1.판매원ID = T3.판매원ID                 AND T3.목표월 = '200901' WHERE   T1.판매원등급= 'A' GROUP BY T1.판매원ID, T1.판매원등급

 

<SQL-1>의 비용을 산출해 보도록 하겠습니다.

먼저 판매원등급='A'인 데이터만 가져오면 다음과 같은 <RESULT 1-1>이 나옵니다.

<RESULT 1-1>

판매원
판매원ID	판매원등급
a	A
b	A
DATA READ	2

여기서는 DATA를 총 읽는데 2의 비용이 들었습니다.(2건을 읽었으니까요,)

그 다음, 위의 <RESULT 1-1>을 판매테이블과 JOIN을 합니다.

<RESULT 1-2>

판매원		판매
판매원ID	판매원등급	판매일자	판매원ID	판매수량
a	A	2009-01-01	a	10
a	A	2009-01-02	a	5
a	A	2009-01-03	a	5
a	A	2009-01-04	a	5
a	A	2009-01-05	a	5
b	A	2009-01-01	b	5
b	A	2009-01-02	b	10
b	A	2009-01-03	b	10
b	A	2009-01-04	b	10

DATA READ	9
DATA JOIN	9

판매테이블에서 DATA는 총9건을 읽게 되고, JOIN결과는 총 9건이므로 총 18의 비용이 소모가 됩니다.

그 다음, <RESULT 1-2>를 목표 테이블과 JOIN을 수행하게 됩니다.

<RESULT 1-3>

판매원		판매			월별목표
판매원ID	판매원등급	판매일자	판매원ID	판매수량	목표월	판매원ID	목표수량
a	A	2009-01-01	a	10	200901	a	30
a	A	2009-01-02	a	5	200901	a	30
a	A	2009-01-03	a	5	200901	a	30
a	A	2009-01-04	a	5	200901	a	30
a	A	2009-01-05	a	5	200901	a	30
b	A	2009-01-01	b	5	200901	b	20
b	A	2009-01-02	b	10	200901	b	20
b	A	2009-01-03	b	10	200901	b	20
b	A	2009-01-04	b	10	200901	b	20

DATA READ	2
DATA JOIN	9

월별목표에서 데이터는 2건만을 가져오지만 9건의 결과를 가진 <RESULT 1-2>와 JOIN을 하므로 JOIN의 비용은 9가 소모됩니다.

총 11의 비용이 소모가 되었습니다.

마지막으로 <RESULT 1-3>의 결과애 대해 GROUP BY를 수행합니다.

<RESULT 1-4>

최종결과
판매원ID	판매원등급	1월목표	1월판매
a	A	30	30
b	A	20	35

GROUP BY

9

GROUP BY하기 전에 데이터가 9건이므로 총 9의 비용이 소모됩니다.

각 과정의 비용을 모두 합해 보면, 총 40의 비용이 소모되었습니다.

 

그 다음, 2번 째 SQL을 보도록 하겠습니다.

<SQL-2>판매원 목표, 판매원 판매를 각각 구한후 다시 JOIN

SELECT  T1.판매원ID         ,T1.판매원등급         ,T2.1월목표         ,T1.1월판매 FROM    (         SELECT  T1.판매원ID                 ,T1.판매원등급                 ,SUM(T2.판매수량) 1월판매         FROM    판매원 T1                 INNER JOIN 판매 T2                         ON T1.판매원ID = T2.판매원ID                         AND T2.판매일자 LIKE '200901%'         WHERE   T1.판매원등급 = 'A'         GROUP BY T1.판매원ID, T1.판매원등급         ) T1         INNER JOIN 월별목표 T2                 ON T1.판매원ID = T2.판매원ID                 AND T2.목표월 = '200901'

<SQL-2>에 대해 가상비용을 산출해 보도록 하겠습니다.

먼저 판매원에서 판매원등급이 A인 데이터만 추출합니다.

<RESULT 2-1>

판매원
판매원ID	판매원등급
a	A
b	A
DATA READ	2

<RESULT 2-1>과 판매 데이터를 JOIN합니다.

<RESULT 2-2>

판매원		판매
판매원ID	판매원등급	판매일자	판매원ID	판매수량
a	A	2009-01-01	a	10
a	A	2009-01-02	a	5
a	A	2009-01-03	a	5
a	A	2009-01-04	a	5
a	A	2009-01-05	a	5
b	A	2009-01-01	b	5
b	A	2009-01-02	b	10
b	A	2009-01-03	b	10
b	A	2009-01-04	b	10

DATA READ	9
DATA JOIN	9

총 18의 비용이 소모됩니다. 여기까지는 <SQL-1>과 동일힙니다.

여기서는 판매목표와 JOIN 을 하기 전에 RESULT<2-2>에 대해 먼저 GROUP BY를 수행합니다.

<RESULT 2-3>

RESULT-3
판매원ID	1월판매
a	30
b	35
GROUP BY	9

9건의 데이터를 읽어서 GROUP BY 하므로 9의 비용이 소모됩니다.

<RESULT 2-3>의 결과를 목표와 JOIN하면 최종 결과가 나옵니다.

<RESULT 2-4>

최종결과
판매원ID	판매원등급	1월목표	1월판매
a	A	30	20
b	A	20	15

DATA READ	2
DATA JOIN	2

목표에서 2건을 가져오고 2건만 JOIN하므로 총 4의 비용이 소모됩니다.

<SQL-2>의 총 소요 비용은 33이 됩니다.

 

정리해 보면, <SQL-1>은 총 40의 비용, <SQL-2>는 총 33의 비용이 소모됩니다.

여기서 먼저 말씀 드릴 것은, 우리가 사용하는 실제 옵티마이져는 매우 유능하고 똑똑한 녀석입니다.

그러므로 <SQL-1>처럼 SQL을 사용해도 가장 효율적인 결과를 찾아서 수행합니다.

실제로는 <SQL-1>과 <SQL-2>과 같이 단순한 SQL에서는 동일한 비용이 소모될 수도 있습니다.

하지만, SQL이 복잡해지고 데이터가 많아지면 많아질 수록, 옵티마이져가 최적의 경로로 JOIN을 해주지 못할 가능성은

점점 높아집니다.

그러므로 이와 같은 가상의 과정을 통해 어떤 순서가 올바른 순서인지를 알고 계셔야,

후에 튜닝을 할 때도 이와 같은 방법을 응용하실 수 있습니다.

자, 그럼 <SQL-1>이 왜 <SQL-2>보다 많은 비용이 소모되었을 까요?

<SQL-1>과 <SQL-2>가 어디가 다른가를 찾아낸다면 그 해답이 보입니다.

<SQL-1>은 판매원, 판매, 판매목표를 모두 JOIN한 후 GROUP BY를 하지만,

<SQL-2>는 판매원, 판매를 JOIN해서, GROUP BY한 후 판매목표와 JOIN을 합니다

즉, GROUP BY를 먼저 해서 JOIN의 회수를 감소시켜 성능을 향상시키게 됩니다.

그러면 무조건 GROUP BY를 먼저 하고 JONI을 하면 될까요? 이것은 테이블들의 관계의 구조에 따라 결정되어집니다.

판매원과 판매간의 관계는 1:N입니다. 판매와 판매목표간의 관계도 1:N입니다.

하지만, 2009년1월의 데이터에 대해서만 SQL을 할 때는 판매원과 판매는 여전히 1:N이지만,

판매원과 판매목표는 1:1의 관계입니다.

판매원과 판매를 JOIN한후 판매목표를 JOIN하게 되면 판매목표의 데이터가 판매만큼 데이터가 늘어나게 됩니다.

결과적으로 <SQL-1>에서 판매목표값에 대해 MAX를 쓰게 되는 이유가 여기에 있습니다.

그러므로 판매원과 판매를 JOIN한후 GROUP BY를 하게 되면, GROUP BY된 결과와 판매목표간에는 1:1 JOIN이 발생됩니다.

판매목표는 불필요하게 데이터가 늘어날 일이 없게 되고 MAX라는 연산이 필요없게 됩니다.

개발을 처음 하시는 많은 분들이 <SQL-1>과 같은 유형으로 많이 SQL을 만듭니다.

이와 같은 <SQL-1>이 가장 위험한 경우는 N:N JOIN이 발생될 수도 있다는 것입니다.

그러므로 테이블 관계를 정확히 파악하시고 JOIN을 하셔야 합니다. 테이블 관계만 정확히 파악하고 JOIN을 하신다면

저절로 <SQL-2>처럼 SQL을 개발하게 됩니다.

 

정리하자면, GROUP BY를 통해 데이터를 줄여 JOIN횟수를 줄이는 것이 JOIN순서에 따른 성능향상의 핵심이 됩니다.

하지만, 모든 경우가 가능한 것이 아닌 테이블간의 관계에 따라 응용이 가능하다는 것입니다.

즉, 테이블간의 1:N, 1:1:, N:N관계를 잘 알고 있어야 하며,

위에서 제가 보여드린 방법처럼 SQL에 대해 가상 비용 산출을 가늠해 보신다면, 가장 적절한 순서로

SQL이 JOIN되도록 SQL을 만들어 내실 수 있을 겁니다.

 

좋은 하루들 되시길~~