SQL BOOSTER 이어지는 이야기 .01

SQL BOOSTER 에 이어지는 이야기들입니다.~! 
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.

www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877

SQL BOOSTER

설명의 편의상 반말체로 작성한 점 양해바랍니다.  pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.

 

SQL_Booster_이어지는이야기01.pdf

0.23MB

<SQL을 변경한 성능 개선 .01>

SQL BOOSTER를 읽어주신 독자 여러분께 깊은 감사의 마음을 전합니다. 꼼꼼히 읽어주시는 분들의 고마움에 보답하고자 책에는 담지 않았지만, 책에서 구축된 데이터베이스를 사용해 추가로 익힐 수 있는 내용을 짤막하게 시리즈로 적어보려고 합니다. (SQL BOOSTER를 끝까지 읽으면 만들어지는 테이블들을 사용합니다.) 올리는 주기와 시기, 내용을 약속할 수 없는 점 먼저 이해 바랍니다. 본문의 내용은 전달 효율성을 위해 반말체를 사용하는 점 이해 부탁드립니다. 그럼, 시작하도록 하겠습니다.

 

 

하나의 테이블에서 데이터를 조회할 때, 상황에 따라 다양한 구간을 조회해야 할 때가 있다. 예를 들면 아래 SQL과 같다.

-- [SQL-1] ITM_ID에 따라 다양한 구간을 조회 – UNION ALL

SELECT  T1.ITM_ID
        ,COUNT(*) ORD_CNT
FROM    T_ORD_JOIN T1
WHERE   T1.CUS_ID = 'CUS_0002'
AND     T1.ITM_ID = 'ITM002' AND T1.ORD_YMD BETWEEN '20170102' AND '20170103'
GROUP BY T1.ITM_ID
UNION ALL
SELECT  T1.ITM_ID
        ,COUNT(*) ORD_CNT
FROM    T_ORD_JOIN T1
WHERE   T1.CUS_ID = 'CUS_0002'
AND     T1.ITM_ID = 'ITM079' AND T1.ORD_YMD BETWEEN '20170102' AND '20170115'
GROUP BY T1.ITM_ID
UNION ALL
SELECT  T1.ITM_ID
        ,COUNT(*) ORD_CNT
FROM    T_ORD_JOIN T1
WHERE   T1.CUS_ID = 'CUS_0002'
AND     T1.ITM_ID = 'ITM015' AND T1.ORD_YMD BETWEEN '20170102' AND '20170125'
GROUP BY T1.ITM_ID

 

아이템별로 팔리는 주기가 다르다고 가정했을 때 이와 같은 SQL이 나올 수 있다. 위 SQL을 실행계획을 확인해보면 아래와 같다.

[PLAN-1] ITM_ID에 따라 다양한 구간을 조회 – UNION ALL
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |      3 |00:00:00.01 |     106 |
|   1 |  UNION-ALL                    |                |      1 |      3 |00:00:00.01 |     106 |
|   2 |   SORT GROUP BY NOSORT        |                |      1 |      1 |00:00:00.01 |      27 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   1000 |00:00:00.01 |      27 |
|*  4 |     INDEX RANGE SCAN          | X_T_ORD_JOIN_2 |      1 |   2000 |00:00:00.01 |      11 |
|   5 |   SORT GROUP BY NOSORT        |                |      1 |      1 |00:00:00.01 |      27 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   1000 |00:00:00.01 |      27 |
|*  7 |     INDEX RANGE SCAN          | X_T_ORD_JOIN_2 |      1 |   2000 |00:00:00.01 |      11 |
|   8 |   SORT GROUP BY NOSORT        |                |      1 |      1 |00:00:00.01 |      52 |
|*  9 |    TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   1000 |00:00:00.01 |      52 |
|* 10 |     INDEX RANGE SCAN          | X_T_ORD_JOIN_2 |      1 |   4000 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."ITM_ID"='ITM002')
   4 - access("T1"."CUS_ID"='CUS_0002' AND "T1"."ORD_YMD">='20170102' AND 
              "T1"."ORD_YMD"<='20170103')
   6 - filter("T1"."ITM_ID"='ITM079')
   7 - access("T1"."CUS_ID"='CUS_0002' AND "T1"."ORD_YMD">='20170102' AND 
              "T1"."ORD_YMD"<='20170115')
   9 - filter("T1"."ITM_ID"='ITM015')
  10 - access("T1"."CUS_ID"='CUS_0002' AND "T1"."ORD_YMD">='20170102' AND 
              "T1"."ORD_YMD"<='20170125')

실행계획을 확인해 보면 Buffers(논리적IO)가 106이다. 그리고 같은 테이블인 T_ORD_JOIN에 세 번이나 접근하고 있다. SQL에서 T_ORD_JOIN을 3개를 UNION ALL로 묶었기 때문에 당연한 결과다.

같은 테이블의 반복 사용은 성능 저하로 이어진다. 반복 사용을 제거하기 위해 아래와 같은 SQL을 고민해 볼 수 있다.

-- [SQL-2] ITM_ID에 따라 다양한 구간을 조회 – CASE 사용
SELECT  T1.ITM_ID
        ,COUNT(*) ORD_CNT
FROM    T_ORD_JOIN T1
WHERE   T1.CUS_ID = 'CUS_0002'
AND   CASE WHEN T1.ITM_ID = 'ITM002' AND T1.ORD_YMD BETWEEN '20170102' AND '20170103' THEN 1
           WHEN T1.ITM_ID = 'ITM079' AND T1.ORD_YMD BETWEEN '20170102' AND '20170115' THEN 1
           WHEN T1.ITM_ID = 'ITM015' AND T1.ORD_YMD BETWEEN '20170102' AND '20170125' THEN 1
        END = 1
GROUP BY T1.ITM_ID;

T_ORD_JOIN을 한 번만 사용해 SQL이 간단해졌다. 사실 이와 같은 SQL은 개발 편의성을 위해 작성되는 패턴이다. SQL의 성능 향상과는 아무 연관이 없다. ORD_YMD 조건이나, ITMD_ID 조건에 대해 적절한 인덱스가 있어도 효율적으로 사용할 수 없기 때문이다. 이와 같은 방법보다는 [SQL-1]의 UNION ALL 방법이 성능에 있어서 훨씬 유리하다. [SQL-2]의 실행계획을 살펴보자. 아래와 같다.

-- [PLAN-2] ITM_ID에 따라 다양한 구간을 조회 – CASE 사용
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |      3 |00:00:00.02 |     359 |
|   1 |  HASH GROUP BY               |                |      1 |      3 |00:00:00.02 |     359 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   3000 |00:00:00.01 |     359 |
|*  3 |    INDEX RANGE SCAN          | X_T_ORD_JOIN_1 |      1 |  30000 |00:00:00.01 |      87 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(CASE  WHEN ("T1"."ITM_ID"='ITM002' AND "T1"."ORD_YMD">='20170102' AND
   3 - access("T1"."CUS_ID"='CUS_0002')

[SQL-2]의 실행계획에서 총 Buffers는 359다. [SQL-1]보다 세 배 정도 나빠졌다. 테이블 반복을 아무리 제거해도 효율적으로 인덱스를 사용하지 못한다면 성능이 좋아질 수 없다. [PLAN-2]를 보면 X_T_ORD_JOIN_1 인덱스를 이용해 CUS_ID 조건만 access 처리했다. ([PLAN-2]의 7번, 13번 라인을 보면 알 수 있다.)

가능하면 [SQL-1]과 같이 개발하는 것이 성능에도 유리하고 명확해 보인다. 만약에 추가로 성능 개선이 필요하다면 아래와 같은 패턴을 고민해 볼 수 있다. 오라클의 LEAST와 GREATEST 함수를 사용한 방법이다.

[SQL-3] ITM_ID에 따라 다양한 구간을 조회 – LEAST, GREATEST 사용
SELECT  
T1.ITM_ID
        ,COUNT(*) ORD_CNT
FROM    T_ORD_JOIN T1
WHERE   T1.CUS_ID = 'CUS_0002'
AND     CASE WHEN T1.ITM_ID = 'ITM002' AND T1.ORD_YMD BETWEEN '20170102' AND '20170103' THEN 1
             WHEN T1.ITM_ID = 'ITM079' AND T1.ORD_YMD BETWEEN '20170102' AND '20170115' THEN 1
             WHEN T1.ITM_ID = 'ITM015' AND T1.ORD_YMD BETWEEN '20170102' AND '20170125' THEN 1
        END = 1
AND T1.ORD_YMD BETWEEN LEAST('20170102','20170102','20170102') 
AND GREATEST('20170103','20170115','20170125')
GROUP BY T1.ITM_ID;

[SQL-2]를 그대로 사용하고, 대신에 CUS_ID, ORD_YMD로 구성된 복합인덱스를 효율적으로 사용할 수 있도록 10번과 11번 라인에 조건을 추가했다. LEAST는 매개 변수 값 중에서 가장 작은 값을 돌려준다. GREATEST는 LEAST의 반대다. LEAST와 GREATEST의 매개 변수 값은 어느 값을 사용했는지 위 SQL을 보면 쉽게 알 수 있다. 색으로 표시해 놓았다.

실행계획을 통해 성능 개선이 되었는지 확인해보자. 아래와 같다.

[PLAN-3] ITM_ID에 따라 다양한 구간을 조회 – 최적화
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |      3 |00:00:00.01 |      52 |
|   1 |  HASH GROUP BY               |                |      1 |      3 |00:00:00.01 |      52 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   3000 |00:00:00.01 |      52 |
|*  3 |    INDEX RANGE SCAN          | X_T_ORD_JOIN_2 |      1 |   4000 |00:00:00.01 |      19 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(CASE  WHEN ("T1"."ITM_ID"='ITM002' AND "T1"."ORD_YMD">='20170102' AND … 생략
   3 - access("T1"."CUS_ID"='CUS_0002' AND "T1"."ORD_YMD">='20170102' AND "T1"."ORD_YMD"<='20170125')

총 Buffers가 52로 개선되었다. 인덱스를 추가하지 않아도, 조금만 생각해 보면 성능을 개선 할 수 있는 다양한 방법들이 있다. 항상 고민하고 좋은 방법을 찾는 습관을 갖기 바란다.

마지막으로 연습 문제를 하나 남긴다. 아래 SQL을 같은 방법으로 성능 개선을 시도해 보고 실행계획을 확인해 보기 바란다.

[SQL-4] PAY_TP에 따라 다양한 구간을 조회 – LEAST, GREATEST 사용
SELECT  T1.PAY_TP
        ,COUNT(*) ORD_CNT
FROM    T_ORD_BIG T1
WHERE   T1.CUS_ID = 'CUS_0002'
AND     CASE WHEN T1.PAY_TP IS NULL AND T1.ORD_YMD BETWEEN  '20170102' AND '20170103' THEN 1
             WHEN T1.PAY_TP = 'CARD' AND T1.ORD_YMD BETWEEN '20170102' AND '20170115' THEN 1
             WHEN T1.PAY_TP = 'BANK' AND T1.ORD_YMD BETWEEN '20170102' AND '20170120' THEN 1
        END = 1
GROUP BY T1.PAY_TP;