MySQL의 강력한 한방! Index For GroupBy

 

MySQL의 강력한 한방! Index For GroupBy

 

MySQL에는 Index For GroupBy라는 강력한 기능이 있습니다. 다른 DBMS에는 없는 훌륭한 기능입니다.
테스트를 통해 다른 DBMS에 비해 얼마나 좋은 성능을 내는지 살펴보겠습니다.

 

 

1. GROUP BY COUNT - ORALE 19C SE

먼저 DBMS의 가장 큰 형님 오라클부터 살펴보겠습니다. 아래와 같이 단순히 GROUP BY와 COUNT를 하는 SQL이 있습니다. 이에 맞게 인덱스도 생성합니다. SQL을 실행해 성능을 측정해보면 17,044의 IO가 발생했습니다.

-- CREATE INDEX T_ORD_BIG_TEST_X ON T_ORD_BIG(ORD_YMD);

SELECT  T1.ORD_YMD, COUNT(*)
FROM    T_ORD_BIG T1
WHERE   T1.ORD_YMD > ' ' -- 인덱스를 강제로 사용하게 하기 위해 조건 사용
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;

 
-------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |    100 |00:00:03.81 |   17044 |
|   1 |  SORT GROUP BY        |                  |      1 |    100 |00:00:03.81 |   17044 |
|*  2 |   INDEX FAST FULL SCAN| T_ORD_BIG_TEST_X |      1 |   6094K|00:00:01.54 |   17044 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   2 - filter("T1"."ORD_YMD">' ')

 

 

2. GROUP BY COUNT - PostgreSQL 16.1

이번에는 PostgreSQL에서 작업을 해봅니다. 아래와 같습니다. 오라클과 마찬가지로 인덱스 리프를 모두 읽어 GROUP BY를 처리합니다. 그 결과 IO(Buffers) 가 52,401이나 나옵니다.

-- CREATE INDEX t_ord_big_test_x on t_ord_big(ord_ymd);

SELECT  t1.ord_ymd, COUNT(*)
FROM    t_ord_Big t1
GROUP BY t1.ord_ymd
ORDER BY 1 DESC;

GroupAggregate (actual time=30.175..10685.530 rows=365 loops=1)
  Group Key: ord_ymd
  Buffers: shared hit=52401
->  Index Only Scan Backward using t_ord_big_test_x on t_ord_big t1
         (actual time=0.017..4792.832 rows=61194000 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=52401
Planning Time: 0.064 ms
Execution Time: 10685.672 ms

 

 

3. GROUP BY COUNT - MySQL 8.0.22

이번에는 MySQL입니다. MySQL 역시 GROUP BY와 카운트가 사용된 SQL은 리프 페이지를 모두 스캔해야 하므로 제법 시간이 걸립니다. 77,979 페이지의 IO가 발생했습니다.

SELECT  T1.ORD_YMD, COUNT(*)
FROM    T_ORD_BIG T1
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;

-> Group aggregate: count(0)  (actual time=28.596..10960.686 rows=349 loops=1)
  -> Index scan on T1 using T_ORD_BIG_X05(reverse)
     (actual time=1.328..7564.492 rows=6094000 loops=1)

id table  ptype   key            key_len  rows     Extra                             
-- -----  ------  -------------  -------  -------  --------------------------------  
1  T1     Nindex  T_ORD_BIG_X05  35       6145479  Backward index scan; Using index  

 - Time sec: 7.538511
   * Rows read: 6094000
   * Buffer pages: 77979

 

 

 

4. ONLY GROUP BY - ORALE 19C SE

다시, 오라클로 돌와옵니다. 이번에는 GROUP BY만 SQL에 존재합니다. COUNT 처리는 하지 않습니다. 이전과 같은 17,044의 IO가 발생했습니다.

SELECT  T1.ORD_YMD
FROM    T_ORD_BIG T1
WHERE   T1.ORD_YMD > ' ' -- 인덱스를 강제로 사용하게 하기 위해 조건 사용
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;

------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Starts |A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |      1 |   100 |00:00:03.67 |   17044 |
|   1 |  SORT GROUP BY        |                  |      1 |   100 |00:00:03.67 |   17044 |
|*  2 |   INDEX FAST FULL SCAN| T_ORD_BIG_TEST_X |      1 |  6094K|00:00:01.54 |   17044 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."ORD_YMD">' ')

 

 

5. GROUP BY COUNT - PostgreSQL 16.1

PostgreSQL에서 GROUP BY만 있는 SQL을 실행해봅니다. 2번과 마찬가지로 52,401의 IO가 발생합니다.

SELECT  t1.ord_ymd
FROM    t_ord_Big t1
GROUP BY t1.ord_ymd
ORDER BY 1 DESC;

Group (actual time=0.019..9361.799 rows=365 loops=1)
  Group Key: ord_ymd
  Buffers: shared hit=52401
  ->  Index Only Scan Backward using t_ord_big_test_x on t_ord_big t1 
      (actual time=0.018..4604.412 rows=61194000 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=52401
Planning Time: 0.060 ms
Execution Time: 9361.961 ms

 

6. GROUP BY COUNT - MySQL 8.0.22

마지막으로 MySQL에서 COUNT 없이 GROUP BY만 사용해봅니다. 두둥!!! IO가 1,056으로 획기적으로 줄었습니다.

이는 MySQL에만 존재하는 내부적인 알고리즘때문인듯 합니다. 클래식 실행계획의 Extra를 보면 Using index for group-by가 표시되어 있습니다. 또한 Tree 실행계획에는 using index_for_group_by라고 표시되어 있습니다.

SELECT  T1.ORD_YMD
FROM    T_ORD_BIG T1
GROUP BY T1.ORD_YMD
ORDER BY 1 DESC;

id  table  type   key            key_len  rows  Extra                                                      
--  -----  -----  -------------  -------  ----  -----------------------------
1   T1     range  T_ORD_BIG_X05  35       351   Using index for group-by; ...

-> Group (computed in earlier step, no aggregates)  (actual time=7.000..7.138 rows=349 loops=1)
    -> Sort: t1.ORD_YMD DESC  (actual time=6.998..7.066 rows=349 loops=1)
        -> Table scan on <temporary>  (actual time=0.002..0.051 rows=349 loops=1)
            -> Temporary table with deduplication (actual time=5.657..5.775 rows=349 loops=1)
                -> Index range scan on T1 using index_for_group_by(T_ORD_BIG_X05)
                   (actual time=0.149..5.239 rows=349 loops=1)

 - Time sec: 0.011888
   * Rows read: 350
   * Buffer pages: 1056

 

 

결론

SQL에서 집계함수 없이 GROUP BY만 사용되고 있고, GROUP BY 컬럼이 인덱스로 커버가 되는 상황이라면, MySQL의 성능이 압도적으로 좋습니다. 정확히는 MySQL의 실행계획에 index_for_group_by가 출현해야 합니다. 그러나, 이와 같은 패턴, 이와 같은 상황은 실제 SQL에서 잘 나오지 않습니다. 특히나 SQL이 복잡해질수록 거의 나오기 힘든 패턴입니다.

그러므로 이런 기능 하나로 MySQL이 다른 DBMS보다 좋다라고 할 수는 없습니다. 각 DBMS마다 가지고 있는 장점과 단점이 있습니다. 우리는 이러한 부분을 잘 파악하고 그에 맞게 사용해야 합니다.

 

어쨋든, MySQL을 운영하고 있다면, 이와 같은 기능이 있음을 잘 기억할 필요가 있습니다. 가끔 업무 요건에 따라, 튜닝을 위해 써먹을 수 있는 그런 기술이니까요.

 

 

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

- 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