습관성 GROUP BY를 주의하자!

 

습관성 GROUP BY를 주의하자!

 

GROUP BY 하나로 인해 성능이 달라지는 부분을 MySQL, PostgreSQL, ORACLE 세 개의 DB 모두 살펴보겠습니다.

혹시, 현재 운영중에 이와 같은 패턴이 있다면, 그리고 그 SQL이 매우 자주 실행되는 초핵심 SQL이라면 반드시 점검하실 필요가 있습니다.

 

MySQL 8.0.22

먼저 MySQL을 살펴보겠습니다. 우선 T_ORD_BIG 테이블의 인덱스는 아래와 같이 구성되어 있으며, PostgreSQL, ORACLE 모두 같은 인덱스 구조를 가지고 있습니다.

TABLE_SCHEMA  TABLE_NAME  INDEX_NAME     IX_COLS        MB      
------------  ----------  -------------  -------------  ------  
mytundb       t_ord_big   PRIMARY        ORD_SEQ        554.00  
mytundb       t_ord_big   T_ORD_BIG_X01  ORD_DT         131.69  
mytundb       t_ord_big   T_ORD_BIG_X02  RNO            151.73  
mytundb       t_ord_big   T_ORD_BIG_X03  ORD_DT,CUS_ID  191.86  
mytundb       t_ord_big   T_ORD_BIG_X04  CUS_ID,ORD_DT  191.86

 

 

위와 같은 인덱스 구성에서 아래와 같이 특정 고객의 마지막 주문일자를 구하는 SQL을 실행합니다. IO 수치(Buffer Pages)를 추적해 보면, 3입니다. 거의 최적화된 IO라고 볼 수 있습니다.

SELECT  MAX(T1.ORD_DT) LAST_ORD_DT
FROM    T_ORD_BIG T1
WHERE   T1.CUS_ID = 'CUS_0001';

Execution Plan:
id  select_type  table  partitions  type  possible_keys  key   key_len  ref   rows  filtered  Extra                         
--  -----------  -----  ----------  ----  -------------  ----  -------  ----  ----  --------  ----------------------------  
1   SIMPLE       None   None        None  None           None  None     None  None  None      Select tables optimized away  

-> Rows fetched before execution  (cost=0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)

 - Time sec: 0.002003
   * Rows read: 1
   * Buffer pages: 3

 

위와 같이 SQL을 사용한다면 전혀 문제 없습니다. 그런데 실수로(?) 또는 습관적으로 GROUP BY 를 추가하는 경우가 있습니다. 아래와 같이 말이죠. 아래 SQL은 위의 SQL과 완전히 같은 결과를 보여줍니다. 하지만, 성능적으로는 차이가 있습니다.

IO를 보면 45로 기존 대비 10배 이상 증가했습니다. 물론, 따져보면 실제 실행시간은 이전이나 지금이나 거의 차이는 없습니다. 인덱스만 이용해 SQL이 처리되기 때문입니다. 실행계획을 살펴보면, GROUP BY 를 위해 Index range scan이 나온것을 알 수 있습니다. 

SELECT  MAX(T1.ORD_DT) LAST_ORD_DT
FROM    T_ORD_BIG T1
WHERE   T1.CUS_ID = 'CUS_0001'
GROUP BY T1.CUS_ID; -- > GROUP BY 를 추가!!!!

Execution Plan:
id  select_type  table  partitions  type   possible_keys                key            key_len  ref   rows  filtered  Extra                                  
--  -----------  -----  ----------  -----  ---------------------------  -------------  -------  ----  ----  --------  -------------------------------------  
1   SIMPLE       T1     None        range  T_ORD_BIG_X03,T_ORD_BIG_X04  T_ORD_BIG_X04  163      None  228   100.0     Using where; Using index for group-by  

-> Group aggregate (computed in earlier step): max(t1.ORD_DT)  (actual time=0.105..0.126 rows=1 loops=1)
    -> Filter: (t1.CUS_ID = 'CUS_0001')  (cost=159.60 rows=228) (actual time=0.102..0.122 rows=1 loops=1)
        -> Index range scan on T1 using index_for_group_by(T_ORD_BIG_X04)  (cost=159.60 rows=228) (actual time=0.099..0.119 rows=1 loops=1)

 - Time sec: 0.000999
   * Rows read: 4
   * Buffer pages: 45

 

 

PostgreSQL 16.1

PostgreSQL도 다르지 않습니다. 먼저 GROUP BY 없이 마지막 일자를 가져오는 SQL입니다. IO(Buffers) 수치를 보면 4입니다.

SELECT  MAX(T1.ORD_DT) LAST_ORD_DT
FROM    T_ORD_BIG T1
WHERE   T1.CUS_ID = 'CUS_0001';

Result (actual time=0.057..0.059 rows=1 loops=1)
  Buffers: shared hit=4
  InitPlan 1 (returns $0)
    ->  Limit (actual time=0.052..0.053 rows=1 loops=1)
          Buffers: shared hit=4
          ->  Index Only Scan Backward using t_ord_big_x04 on t_ord_big t1 (actual time=0.049..0.050 rows=1 loops=1)
                Index Cond: ((cus_id = 'CUS_0001'::text) AND (ord_dt IS NOT NULL))
                Heap Fetches: 0
                Buffers: shared hit=4
Planning Time: 0.298 ms
Execution Time: 0.100 ms

 

이번에는 같은 결과이지만, GROUP BY를 추가한 SQL입니다. IO(Buffers) 수치가 64로 급등한 것을 알 수 있습니다. 같은 결과이지만, GROUP BY 가 추가되었으므로 내부적으로 GROUP BY 를 처리하기 때문입니다.

SELECT  MAX(T1.ORD_DT) LAST_ORD_DT
FROM    T_ORD_BIG T1
WHERE   T1.CUS_ID = 'CUS_0001'
GROUP BY T1.CUS_ID;

GroupAggregate (actual time=20.337..20.339 rows=1 loops=1)
  Buffers: shared hit=64
  ->  Index Only Scan using t_ord_big_x04 on t_ord_big t1 (actual time=0.037..11.810 rows=66000 loops=1)
        Index Cond: (cus_id = 'CUS_0001'::text)
        Heap Fetches: 0
        Buffers: shared hit=64
Planning Time: 0.132 ms
Execution Time: 20.385 ms

 

 

ORACLE 19C SE

내친김에 오라클도 살펴보도록 하겠습니다. 이 부분은 오라클도 별반 다르지 않습니다. 아래는 GROUP BY가 없는 경우로 3블록의 IO가 발생했습니다.

SELECT  MAX(T1.ORD_DT) LAST_ORD_DT
FROM    T_ORD_BIG T1
WHERE   T1.CUS_ID = 'CUS_0001';

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |               |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   FIRST ROW                  |               |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T_ORD_BIG_X04 |      1 |      1 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."CUS_ID"='CUS_0001')

 

아래는 GROUP BY가 추가된 경우입니다. IO가 260으로 매우 높습니다. 다른 DBMS보다 나쁘지만 이는 의미가 없습니다. 데이터가 실제 저장되어 군집된 상태가 다를 수 있기 때문입니다.

SELECT  MAX(T1.ORD_DT) LAST_ORD_DT
FROM    T_ORD_BIG T1
WHERE   T1.CUS_ID = 'CUS_0001'
GROUP BY T1.CUS_ID;

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      1 |        |      1 |00:00:00.03 |     260 |
|   1 |  SORT GROUP BY NOSORT|               |      1 |      1 |      1 |00:00:00.03 |     260 |
|*  2 |   INDEX RANGE SCAN   | T_ORD_BIG_X04 |      1 |      1 |  66000 |00:00:00.12 |     260 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."CUS_ID"='CUS_0001')

 

 

결론

사소한 습관성 GROUP BY를 사용하지 않도록 주의합시다. 실제 시간상으로 성능 차이가 크지는 않지만 DB 내부적인 IO에서는 제법 차이가 있으며, 해당 SQL이 정말 자주 실행된다면 불필요한 연산으로 DB 자원 사용량 증가에도 영향을 줄 수있습니다. 무엇보다 중요한 것은, 결론만 외우는 것이 아니라 실행계획을 통해 이와 같은 차이점을 확인하고 이해하는 것입니다.

 

 

 

 

끝으로, 저는 아래 강의들을 진행하고 있습니다. 많은 관심 부탁드립니다.

- StartUP Tuning For PostgreSQL: PostgreSQL을 활용한 SQL 튜닝 입문 교육
  https://cafe.naver.com/dbian/7181
- StartUP Tuning For MySQL: MySQL을 활용한 SQL 튜닝 입문 교육
  https://cafe.naver.com/dbian/6958

- 평생필요한 데이터 분석: 저자 직강!, 주식 데이터를 활용한 SQL 입문자 교육

  https://cafe.naver.com/dbian/7131

 

 

450