GROUP BY에 서브쿼리를: 된다고 막 쓰지 말자!

오늘은 오픈소스DBMS에서 GROUP BY에 서브쿼리를 사용하는 패턴을 살펴보겠습니다. 성능 관련해서 주의가 필요한 내용입니다.

MySQL

먼저 MySQL입니다. 아래 SQL은 tr_ord에서 2025년 1월 데이터를 조회하고 있습니다. 해당 데이터는 14,579건이 존재합니다. 14,579건에 대해 shop_id로 GROUP BY 처리하고 있습니다. 또한, SELECT 절에서는 shop_nm을 처리하기 위해 서브쿼리를 사용하고 있습니다. 그런데, GROUP BY 절에도 동일하게 서브쿼리를 사용했습니다. 아시는 분은 아시겠지만, GROUP BY 부분에 서브쿼리는 생략해도 됩니다. 하지만 개발 일정에 쫓겨 개발하다 보면 이렇게 개발을 하게 되는 경우들이 종종 있습니다. (저는, 절대 아닙니다만…) 또는 GROUP BY의 사용법을 잘 모르는 개발자일수도 있죠.

[SQL-1:MySQL]

EXPLAIN ANALYZE
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbmy.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbmy.tr_ord t1
WHERE   t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d')
AND     t1.ord_dtm <  STR_TO_DATE('20250201','%Y%m%d')
GROUP BY t1.shop_id
        ,(SELECT x.shop_nm FROM startdbmy.ms_shop x WHERE x.shop_id = t1.shop_id);
        
-> Table scan on <temporary>  (actual time=113..113 rows=289 loops=1)
    -> Aggregate using temporary table  (actual time=113..113 rows=289 loops=1)
        -> Index range scan on t1 using tr_ord_x01 over ('2025-01-01 00:00:00' <= ord_dtm < '2025-02-01 00:00:00'), with index condition: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20250101','%Y%m%d'))) and (startdbmy.t1.ord_dtm < <cache>(str_to_date('20250201','%Y%m%d'))))  (cost=32073 rows=27062) (actual time=0.0499..54.1 rows=14290 loops=1)
    -> Select #3 (subquery in projection; dependent)
        -> Single-row index lookup on x using PRIMARY (shop_id=startdbmy.t1.shop_id)  (cost=0.35 rows=1) (actual time=0.00182..0.00184 rows=1 loops=14579)
    -> Select #2 (subquery in projection; dependent)
        -> Single-row index lookup on x using PRIMARY (shop_id=startdbmy.t1.shop_id)  (cost=0.35 rows=1) (actual time=0.00327..0.0033 rows=1 loops=289)
SQL
복사

위 SQL의 실행계획을 살펴보면, 서브쿼리가 2번 사용되고 있습니다. 이미 성능에서 큰 손해를 보고 있는 것이죠. 또한, Select #3에 대한 서브쿼리의 actual loops 수치를 보면 14,579입니다. 서브쿼리가 GROUP BY 이전의 데이터 건수인 14,579건만큼 반복 실행된 것입니다. 다행히도, Select #2에 대한 서브쿼리는 GROUP BY 이후의 건수만큼인 289번(loops=289) 반복 실행되었네요. MySQL에서는 위 SQL을 다음과 같이 변경할 수 있습니다. GROUP BY 절에, SELECT 절의 별칭인 shop_nm을 그대로 사용하는 것이죠. ORACLE에서 MySQL로 전환하다 보면, 아래와 같은 문법이 지원된다는 이유로, 아래와 같이 SQL을 개발하는 경우가 종종 발견됩니다. 다행히도 실행계획에는 서브쿼리가 하나만 출력되었습니다. 하지만, 여전히 서브쿼리가 14,579번 반복 실행된 것을 알 수 있습니다.

[SQL-2:MySQL]

EXPLAIN ANALYZE
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbmy.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbmy.tr_ord t1
WHERE   t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d')
AND     t1.ord_dtm <  STR_TO_DATE('20250201','%Y%m%d')
GROUP BY t1.shop_id
        ,shop_nm;

-> Table scan on <temporary>  (actual time=75.6..75.7 rows=289 loops=1)
    -> Aggregate using temporary table  (actual time=75.6..75.6 rows=289 loops=1)
        -> Index range scan on t1 using tr_ord_x01 over ('2025-01-01 00:00:00' <= ord_dtm < '2025-02-01 00:00:00'), with index condition: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20250101','%Y%m%d'))) and (startdbmy.t1.ord_dtm < <cache>(str_to_date('20250201','%Y%m%d'))))  (cost=32073 rows=27062) (actual time=0.0422..38.6 rows=14290 loops=1)
    -> Select #2 (subquery in projection; dependent)
        -> Single-row index lookup on x using PRIMARY (shop_id=startdbmy.t1.shop_id)  (cost=0.35 rows=1) (actual time=0.00128..0.0013 rows=1 loops=14579)
SQL
복사

[SQL-1:MySQL]과 [SQL-2:MySQL]의 올바른 해법은 아래와 같습니다. GROUP BY에 불필요한 shop_nm 부분을 제거하는 것입니다. shop_nm은 어차피 shop_id에 종속되니까요. 서브쿼리가 하나만 출력되면서, 실행횟수도 GROUP BY 이후 건수인 289번만 반복 실행됩니다.

[SQL-3:MySQL]

EXPLAIN ANALYZE
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbmy.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbmy.tr_ord t1
WHERE   t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d')
AND     t1.ord_dtm <  STR_TO_DATE('20250201','%Y%m%d')
GROUP BY t1.shop_id;

-> Table scan on <temporary>  (actual time=70.3..70.4 rows=289 loops=1)
    -> Aggregate using temporary table  (actual time=70.3..70.3 rows=289 loops=1)
        -> Index range scan on t1 using tr_ord_x01 over ('2025-01-01 00:00:00' <= ord_dtm < '2025-02-01 00:00:00'), with index condition: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20250101','%Y%m%d'))) and (startdbmy.t1.ord_dtm < <cache>(str_to_date('20250201','%Y%m%d'))))  (cost=32073 rows=27062) (actual time=0.0545..54.8 rows=14290 loops=1)
    -> Select #2 (subquery in projection; dependent)
        -> Single-row index lookup on x using PRIMARY (shop_id=startdbmy.t1.shop_id)  (cost=0.35 rows=1) (actual time=0.00526..0.0053 rows=1 loops=289)
SQL
복사

PostgreSQL

PostgreSQL도 퀵하게 살펴봅니다. MySQL과 동일한 방식으로 GROUP BY 절에 서브쿼리를 사용하거나, SELECT 절의 서브쿼리 별칭을 GROUP BY 절에 그대로 사용할 수 있습니다. 아래와 같습니다.

[SQL-1: PostgreSQL]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbpg.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_dtm >= '20250101'::date
AND     t1.ord_dtm <  '20250201'::date
GROUP BY t1.shop_id
        ,(SELECT x.shop_nm FROM startdbpg.ms_shop x WHERE x.shop_id = t1.shop_id);
GroupAggregate (actual time=212.145..213.626 rows=289 loops=1)
  Group Key: t1.shop_id, ((SubPlan 1))
  Buffers: shared hit=57362
  ->  Sort (actual time=212.080..212.522 rows=14290 loops=1)
        Sort Key: t1.shop_id, ((SubPlan 1))
        Sort Method: quicksort  Memory: 1045kB
        Buffers: shared hit=57362
        ->  Index Scan using tr_ord_x01 on tr_ord t1 (actual time=19.697..202.655 rows=14290 loops=1)
              Index Cond: ((ord_dtm >= '2025-01-01'::date) AND (ord_dtm < '2025-02-01'::date))
              Buffers: shared hit=57362
              SubPlan 1
                ->  Seq Scan on ms_shop x (actual time=0.005..0.012 rows=1 loops=14290)
                      Filter: ((shop_id)::text = (t1.shop_id)::text)
                      Rows Removed by Filter: 299
                      Buffers: shared hit=57160
Planning:
  Buffers: shared hit=8
Planning Time: 0.294 ms
Execution Time: 221.128 ms


[SQL-2: PostgreSQL]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbpg.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_dtm >= '20250101'::date
AND     t1.ord_dtm <  '20250201'::date
GROUP BY t1.shop_id
        ,shop_nm;
GroupAggregate (actual time=199.159..200.591 rows=289 loops=1)
  Group Key: t1.shop_id, ((SubPlan 1))
  Buffers: shared hit=57362
  ->  Sort (actual time=199.103..199.571 rows=14290 loops=1)
        Sort Key: t1.shop_id, ((SubPlan 1))
        Sort Method: quicksort  Memory: 1045kB
        Buffers: shared hit=57362
        ->  Index Scan using tr_ord_x01 on tr_ord t1 (actual time=6.021..191.003 rows=14290 loops=1)
              Index Cond: ((ord_dtm >= '2025-01-01'::date) AND (ord_dtm < '2025-02-01'::date))
              Buffers: shared hit=57362
              SubPlan 1
                ->  Seq Scan on ms_shop x (actual time=0.005..0.012 rows=1 loops=14290)
                      Filter: ((shop_id)::text = (t1.shop_id)::text)
                      Rows Removed by Filter: 299
                      Buffers: shared hit=57160
Planning:
  Buffers: shared hit=8
Planning Time: 0.133 ms
Execution Time: 201.384 ms


[SQL-3: PostgreSQL]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbpg.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_dtm >= '20250101'::date
AND     t1.ord_dtm <  '20250201'::date
GROUP BY t1.shop_id;
HashAggregate (actual time=3.831..7.565 rows=289 loops=1)
  Group Key: t1.shop_id
  Batches: 1  Memory Usage: 61kB
  Buffers: shared hit=1358
  ->  Index Scan using tr_ord_x01 on tr_ord t1 (actual time=0.013..1.581 rows=14290 loops=1)
        Index Cond: ((ord_dtm >= '2025-01-01'::date) AND (ord_dtm < '2025-02-01'::date))
        Buffers: shared hit=202
  SubPlan 1
    ->  Seq Scan on ms_shop x (actual time=0.007..0.013 rows=1 loops=289)
          Filter: ((shop_id)::text = (t1.shop_id)::text)
          Rows Removed by Filter: 299
          Buffers: shared hit=1156
Planning:
  Buffers: shared hit=8
Planning Time: 0.314 ms
Execution Time: 7.619 ms
SQL
복사

MySQL과 마찬가지로, GROUP BY 절에 스칼라 서브쿼리가 사용되면, GROUP BY 이전의 레코드 건수만큼 반복 실행됩니다.

ORACLE

끝으로 ORACLE도 살펴봅니다. ORACLE은 GROUP BY 절에 스칼라 서브쿼리를 사용할 수 없습니다. SELECT 절에 스칼라 서브쿼리를 사용한 항목의 별칭도 GROUP BY 절에 사용할 수 없습니다. [SQL-1]과 [SQL-2]의 패턴 자체가 실행 불가능합니다.

[SQL-1:ORACLE]
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbora.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbora.tr_ord t1
WHERE   t1.ord_dtm >= TO_DATE('20250101','YYYYMMDD')
AND     t1.ord_dtm <  TO_DATE('20250201','YYYYMMDD')
GROUP BY t1.shop_id
        ,(SELECT x.shop_nm FROM startdbora.ms_shop x WHERE x.shop_id = t1.shop_id);
SQL Error [22818] [99999]: ORA-22818: 하위 질의식이 여기에 허용되지 않습니다.


[SQL-2:ORACLE]
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbora.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbora.tr_ord t1
WHERE   t1.ord_dtm >= TO_DATE('20250101','YYYYMMDD')
AND     t1.ord_dtm <  TO_DATE('20250201','YYYYMMDD')
GROUP BY t1.shop_id
        ,shop_nm;
SQL Error [904] [42000]: ORA-00904: "SHOP_NM": 부적합한 식별자


[SQL-3:ORACLE]
SELECT  t1.shop_id
        ,(SELECT x.shop_nm FROM startdbora.ms_shop x WHERE x.shop_id = t1.shop_id) shop_nm
        ,COUNT(*) cnt
FROM    startdbora.tr_ord t1
WHERE   t1.ord_dtm >= TO_DATE('20250101','YYYYMMDD')
AND     t1.ord_dtm <  TO_DATE('20250201','YYYYMMDD')
GROUP BY t1.shop_id;
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |    100 |00:00:01.15 |    5923 |   5915 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MS_SHOP    |    100 |      1 |    100 |00:00:00.01 |     105 |      0 |
|*  2 |   INDEX UNIQUE SCAN         | MS_SHOP_PK |    100 |      1 |    100 |00:00:00.01 |       5 |      0 |
|   3 |  HASH GROUP BY              |            |      1 |    298 |    100 |00:00:01.15 |    5923 |   5915 |
|*  4 |   TABLE ACCESS FULL         | TR_ORD     |      1 |   8343 |  14290 |00:00:01.14 |    5923 |   5915 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"."SHOP_ID"=:B1)
   4 - filter(("T1"."ORD_DTM">=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "T1"."ORD_DTM"<TO_DATE(' 2025-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
SQL
복사

마무리

[SQL-1]이나 [SQL-2]와 같은 패턴의 SQL을 개발하지 않도록 주의해주세요. 조회되는 데이터가 많을수록 성능에 큰 영향을 줍니다. 제가 요즘에 튜닝하는 사이트에서도 동일한 패턴으로 20분 넘어서도 안끝나는 SQL이 있습니다. GROUP BY 절에 서브쿼리들만 제거하니, 10초만에 끝날 수 있었죠.

이 글에서 서브쿼리를 쓰지 말자란 이야기는 한마디도 안했습니다. 필요한 곳엔 써야죠. 자주 이야기하지만, 서브쿼리가 성능에 도움이 되는 경우도 많습니다. 다만, 성능적으로 부하를 줄까를 실행계획을 통해 살펴보기 바랍니다.

오픈소스DBMS의 사용은 이제 피할 수 없습니다. 작년부터 해서 ORACLE 보다 MySQL, PostgreSQL 일들이 많은거 같습니다. 조금씩 관심을 갖고 준비해보시기 바랍니다.

준비한 내용은 여기까지입니다.

데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.

•

SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터

•

SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!

•

그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서

책 정보 링크>>>

SQL STARTER For MySQLSQL STARTER For MySQL