MySQL에서는 GROUP BY 처리를 위해 temp 공간을 활용하기도 한다. 이 경우 심각한 성능 저하가 발생할 수 있다.
아래 SQL은 2019년 부터 2023년 12월말까지의 주문 데이터에 대해 회원ID(member_id) 별 주문 건수를 구하고 있다.
SELECT /*+ NO_INDEX(t1) */
t1.member_id ,COUNT(*) cnt
FROM startdbmy.tr_ord_big t1
WHERE t1.ord_dtm >= STR_TO_DATE('20190101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY t1.member_id
ORDER BY 2 DESC;
[1] -> Sort: cnt DESC (actual time=496620..496621 rows=9999 loops=1)
[2] -> Table scan on <temporary> (actual time=496604..496609 rows=9999 loops=1)
[3] -> Aggregate using temporary table (actual time=496604..496604 rows=9999 loops=1)
[4] -> Filter: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20190101','%Y%m%d'))) (actual time=0.159..80258 rows=28.5e+6 loops=1)
[5] -> Table scan on t1 (cost=3.13e+6 rows=30.4e+6) (actual time=0.144..57554 rows=31.3e+6 loops=1)
현재 tr_ord_big 테이블에 2019년부터 2023년 12월말까지의 주문 데이터는 28,505,178 건이 입력되어 있다.
실행계획을 보면, Table Scan(FULL SCAN)을 사용해 조건에 맞는 데이터를 검색하고 있다. 데이터를 검색하는데 걸린 시간이 실행 계획 기준으로 80초(80258 ms)이다. 실행계획의 [5]번, [4]번이 이에 속한다. 필요한 데이터를 찾은 후에는 데이터를 GROUP BY 처리해야 한다. 실행계획의 [3]번이 이에 속한다. [3]번의 Actual time을 보면 종료 시간이 496.60초(496604 ms)다. 데이터를 찾는데 80초, 데이터를 GROUP BY 집계 처리하는데 410초가 걸린 것이다.
데이터 집계 과정의 실행계획([3]번 라인)을 살펴보면, Aggregate using temporary table을 하고 있다.
데이터 집계(GROUP BY) 처리를 위해 temp 공간을 사용하고 있는 것이다. 이로 인해 410초라는 어마어마한 시간이 걸리고 있는 것이다.
PostgreSQL이나 ORACLE의 경우는 대량의 데이터를 GROUP BY 하는 과정에서 HASH Aggregation이라는 매우 성능이 좋은 알고리즘을 사용하고 있다. PostgreSQL과 ORACLE의 실행계획을 살펴보며 다음과 같다.(각 DBMS별로 서버 자원과 설정은 같지 않지만 데이터 양은 동일하게 테스트했다.)
-- PostgreSQL
Sort (actual time=22098.002..22098.764 rows=9999 loops=1)
Sort Key: (count(*)) DESC
Sort Method: quicksort Memory: 775kB
Buffers: shared hit=15404 read=339800
-> HashAggregate (actual time=22094.009..22095.827 rows=9999 loops=1)
Group Key: member_id
Batches: 1 Memory Usage: 1169kB
Buffers: shared hit=15404 read=339800
-> Seq Scan on tr_ord_big t1 (actual time=219.114..18942.242 rows=12163034 loops=1)
Filter: ((ord_dtm >= to_date('20190101'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20230701'::text, 'YYYYMMDD'::text)))
Rows Removed by Filter: 19095440
Buffers: shared hit=15404 read=339800
Execution Time: 22100.628 ms
-- ORACLE
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:03.58 | 255K| 255K| | | |
| 1 | SORT ORDER BY | | 1 | 9999 | 100 |00:00:03.58 | 255K| 255K| 407K| 407K| 361K (0)|
| 2 | HASH GROUP BY | | 1 | 9999 | 9999 |00:00:03.58 | 255K| 255K| 1694K| 1694K| 1718K (0)|
|* 3 | TABLE ACCESS FULL| TR_ORD_BIG | 1 | 27M| 12M|00:00:03.64 | 255K| 255K| | | |
--------------------------------------------------------------------------------------------------------------------------------
두 DBMS 모두 HASH 알고리즘을 사용해 GROUP BY를 빠르게 처리하고 있다. 특히, ORACLE의 성능은 인상적이다.
어쨋든, MySQL은 temp 영역을 사용한 GROUP BY를 하고 있기 때문에, 대량의 데이터를 빠르게 GROUP BY 처리하고 싶다면, 다음과 같이 temp size를 늘리는 방법을 고려해야 한다.
SHOW SESSION variables like 'tmp_table_size'; -- 75497472(72MB)
SET SESSION tmp_table_size = 1024 * 1024 * 2048; -- temp table size를 2GB로 늘림
EXPLAIN ANALYZE
SELECT /*+ NO_INDEX(t1) */
t1.member_id ,COUNT(*) cnt
FROM startdbmy.tr_ord_big t1
WHERE t1.ord_dtm >= STR_TO_DATE('20190101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY t1.member_id
ORDER BY 2 DESC;
-> Sort: cnt DESC (actual time=44706..44707 rows=9999 loops=1)
-> Table scan on <temporary> (actual time=44693..44696 rows=9999 loops=1)
-> Aggregate using temporary table (actual time=44693..44693 rows=9999 loops=1)
-> Filter: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20190101','%Y%m%d'))) (actual time=14.5..39607 rows=2.5e+6 loops=1)
-> Table scan on t1 (cost=3.17e+6 rows=30.4e+6) (actual time=14.5..31350 rows=31.3e+6 loops=1)
기존의 496초 짜리 SQL이, temp table size를 2GB로 크게 늘리자 44.7초로 개선되었다. 하지만 메모리는 한정된 자원이기 때문에 모든 세션에 대해 이처럼 temp table size를 늘리는 것은 엄청난 리스크가 있다. 상황에 따라 배치성 SQL에서만 가끔 사용해 볼 만한 방법이다.
MySQL에서 위와 같은 SQL 패턴의 성능 개선을 위해서는 방금 필자가 보여준 temp table size가 아닌, INDEX를 활용하는 것이 더욱 추천할만하다. 또한, 이처럼 천만 건 이상의 데이터를 일괄적으로 읽는 경우, 단순히 성능 최적화를 고려하는 것 이외에 해당 업무의 필요성과 빈도를 고려하는 것이 중요하다. 이러한 업무가 자주 실행되어야 하는지 개발팀과 함께 논의할 필요가 있다. 단순히 SQL 개선이 아닌 업무 프로세스의 개선을 고려할 필요가 있는 것이다.