본 문서의 내용은 PostgreSQL 버젼에 따라 다를 수 있습니다. 본 문서에서 사용한 버젼은 아래와 같습니다.
AWS RDS PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
PostgreSQL의 EXPLAIN OPTION
PostgreSQL은 MySQL에 비해 다양하고 많은 정보를 실행계획에 표현해줍니다. PostgreSQL의 EXPLAIN 명령어 옵션들에 대해 간단히 살펴보겠습니다.
ANALYZE: 실제 쿼리를 수행하고 사용한 실행계획을 보여준다.
ANALYZE는 실제 SQL이 실행되므로, INESRT, UPDATE, DELETE에서는 사용해서는 안된다.
VERBOSE: 쿼리 계획에 추가적인 정보를 포함, 출력 컬럼과 각 노드의 OID
OID: Object Identifier, PG에서 객체를 구분하는 ID
SELECT * FROM pg_class WHERE oid = '테이블_OID';
BUFFERS: 쿼리 실행 중에 사용된 공유 버퍼, 로컬 버퍼, 그리고 쓰기 버퍼에 대한 정보
ANALYZE와 함께 사용해야 의미가 있다.
COSTS: 각 계획 단계의 추정 비용. 기본적으로 활성화되어 있음
COSTS OFF 로 예상 비용 표시 제외 가능
TIMING: 각 계획 단계의 시간 표시
ANALYZE 사용시 자동 활성화
FORMAT: 결과를 다른 형식으로 반환.
TEXT, XML, JSON, YAML입니다.
SQL 튜닝을 위해 가장 활발히 사용할 부분은 ANALYZE와 BUFFERS입니다. COSTS의 경우는 OFF도 가능합니다. 저는 교육 자료를 만들때 주로 아래와 같은 옵션으로 SQL의 실행계획을 추출합니다. (실제 튜닝 중이라면, 통계 구성을 예상할 수 있는 COSTS도 같이 보는게 좋겠죠.)
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ NestLoop(t1 t2) */
t1.* ,t2.ord_amt
,(SELECT x.rgn_nm FROM m_rgn x WHERE x.rgn_id = t1.rgn_id) rgn_nm
FROM m_cus t1 LEFT OUTER JOIN (
SELECT a.cus_id, sum(a.ord_amt) ord_amt
FROM t_ord a
GROUP BY a.cus_id) t2 on (t2.cus_id = t1.cus_id)
WHERE t1.rgn_id = 'A';
위와 같이 EXPLAIN에 ANALYZE와 BUFFERS 그리고 COSTS OFF를 하면 아래와 같은 실행계획을 확인할 수 있습니다.
Nested Loop Left Join (actual time=1.167..1.440 rows=30 loops=1)
Join Filter: ((t2.cus_id)::text = (t1.cus_id)::text)
Rows Removed by Join Filter: 1354
Buffers: shared hit=122
-> Seq Scan on m_cus t1 (actual time=0.012..0.024 rows=30 loops=1)
Filter: ((rgn_id)::text = 'A'::text)
Rows Removed by Filter: 60
Buffers: shared hit=2
-> Materialize (actual time=0.037..0.041 rows=46 loops=30)
Buffers: shared hit=60
-> Subquery Scan on t2 (actual time=1.106..1.136 rows=89 loops=1)
Buffers: shared hit=60
-> HashAggregate (actual time=1.106..1.127 rows=89 loops=1)
Group Key: a.cus_id
Batches: 1 Memory Usage: 80kB
Buffers: shared hit=60
-> Seq Scan on t_ord a (actual time=0.002..0.229 rows=3047 loops=1)
Buffers: shared hit=60
SubPlan 1
-> Index Scan using m_rgn_pk on m_rgn x (actual time=0.001..0.001 rows=1 loops=30)
Index Cond: ((rgn_id)::text = (t1.rgn_id)::text)
Buffers: shared hit=60
Planning Time: 0.193 ms
Execution Time: 1.483 ms
위 실행계획에서 Buffers 부분이 IO에 대한 항목입니다. shared hit는 모두 메모리에서 처리된 Logical IO입니다. 디스크에서 처리된 Physical IO가 발생하면, Buffers 항목에 read로 별도 표시를 해줍니다.
이와 같은 트리형태의 실행계획과 SQL이 실제 실행된 실행계획, 그리고 각 단계별 rows, loops의 수치와 IO 수치 제공은 SQL 튜닝을 더욱 효율적으로 할 수 있도록 해줍니다. PostgreSQL의 SQL 성능때문에 고민중이라면 이와 같은 실행계획에 익숙해주시기를 권장합니다.
위와 같은 ZB급 데이터의 대다수는 동영상, 게임, 오디오, 신문 기사와 같은 비정형적인 데이터입니다.
하지만, 이처럼 데이터가 늘어난다는 것은 관계형 데이터베이스로 관리하는 정형적인 형태의 데이터도 그만큼 늘어나고 있다고 볼 수 있습니다. 비정형 데이터를 효율적으로 분석하기 위해서는 정형 데이터로 가공이 필요하기 때문입니다.
뿐만아니라, 최근에 활발하게 추진한 마이데이터 관련 사업은 비정형 데이터뿐만 아니라 정형 데이터도 폭발적으로 증가시키고 있습니다. 2020년대 들어서는 이러한 데이터 증가 현상과 함께 클라우드 환경으로의 서버 이전 현상도 활발히 일어나고 있습니다.
‘(정형)데이터 증가’와 ‘클라우드 환경’ 이 두 가지 키워드의 공통 분모에는 여러 가지가 있지만, 그 중의 하나가 바로 비용(Money)입니다.
클라우드 서비스 업체는 다양한 방법으로 비용을 청구합니다. 기본적인 데이터베이스의 디스크 용량과 함께, 디스크에 발생한 IO에 따라서도 비용이 발생합니다. 그러므로 클라우드 환경에서 데이터베이스 비용을 줄이기 위해서는 디스크를 아껴 쓰고 디스크 사용량도 줄여야 합니다.
실제로 클라우드 환경으로 넘어갔던 기업들이 디스크 비용의 부담으로 데이터 조회를 “아껴서” 하라는 난애한 지침을 내리는 경우도 있습니다. 일반적인 개발자나 데이터 분석가 수준에서 비용까지 생각하면 데이터를 핸들링하기는 매우 어려운 일입니다.
상황과 지시가 어떻든 간에, 클라우드 환경의 데이터베이스 비용을 줄이기 위해 우리가 접근할 수 있는 전략 중 하나가 바로 파티셔닝입니다. 어떤 데이터를 조회하든 비용이 발생하지만, 대부분은 빅 테이블에 비용이 집중될 가능성이 높습니다. 그러므로 우리는 빅 테이블의 파티션을 통해 비용 절감의 기회를 만들어 볼 수 있습니다.
1,000 G(Giga)의 테이블이 있다고 가정해보죠. 분석을 위해 최근 데이터 한달만 읽고 싶지만, 해당 테이블에는 일자에 대한 인덱스가 없는 상황이라면 1,000 G 전체를 스캔해야 합니다. 다시 말해, 클라우드 환경에 1,000 G에 대한 IO 비용을 지불해야 합니다. 마찬가지로 일자에 대한 인덱스가 없는 상황에서 매월 과거 한달 데이터를 삭제한다고 가정해보죠. 일자에 대한 인덱스가 없으므로 한 달치데이터 삭제를 위해서도 1,000 G의 데이터에 접근이 발생합니다.
그렇다면 일자에 대한 인덱스를 만들어 보면 어떨까요? 이 경우는 비용(Money)에 도움이 될 수도 있고 안될 수도 있습니다. 읽어야 할 한달 치의 데이터가 매우 많다면 DBMS의 옵티마이져는 인덱스가 아닌 FULL SCAN을 선택할 가능성이 있습니다. DBMS의 옵티마이져는 SQL의 성능과 관련한 비용(Cost)에만 집중을 합니다. 클라우드에 지불할 비용(Money)은 전혀 신경 쓰지 않으니까요. 또한 데이터 분포에 따라서는 인덱스로 한달 데이터를 읽은 경우가 더 많은 비용이 발생할 수도 있습니다. 삭제 역시 마찬가지입니다. 인덱스를 사용할 수도 있지만 FULL SCAN을 택할 수도 있습니다. 추가로 인덱스가 있는 상태에서 테이블 삭제가 발생한다면, 인덱스 삭제에 대한 IO 비용도 같이 발생하지 않을까 생각이 듭니다. 정리하면, 많은 데이터를 처리하기 위한 인덱스는 클라우드 비용(Money) 절약에 직접적으로 도움이 될지는 상황에 따라서 달라질 수 있습니다.
1,000 G 테이블을 월별로 파티션하면 어떨까요? 최근 한달의 데이터를 조회한다면 조회 월에 해당하는 파티션 하나만 전체로 읽으면 됩니다. 파티션 하나가 10 G라면, 10 G에 대한 IO 비용(Money)만 지불하면 됩니다. 삭제는 어떨까요? 파티션은 파티션별로 TRUNCATE나 DROP이 가능합니다. 그러므로 삭제에 대한 IO 비용 없이 데이터 삭제가 가능 할 수도 있습니다. (DBMS나 클라우드 환경에 따라 다를 수는 있습니다.)
이처럼 빅 테이블에 파티션을 지정하는 것만으로 우리는 비용을 아낄 수 있는 기회를 창출할 수 있습니다. 필요하다면, 일반 테이블의 파티션 전환을 설득하기 위해 지난 글에서 설명한 것처럼 용량 산정 작업을 진행해 볼 수 있습니다.
파티션은 클라우드 환경에서의 비용뿐만 아니라 SQL의 성능에도 도움을 줄 수 있으며, 백업 및 복구 시간의 단축에도 이득을 줍니다. 파티션은 빅 테이블의 데이터 이관에도 도움이 됩니다.
하지만 파티션이 이와 같이 장점만 있는 것은 아닙니다. 무엇보다 일반 테이블을 파티션으로 전환하는 것은 매우 어려운 일입니다. “일필휘지(一筆揮之)”하듯, 한 번에 휙 만들어 적용할 수 있는 작업이 절대 아닙니다.
파티션을 적용하면, 이미 서비스되고 있는 파티션 테이블을 사용하는 모든 SQL들의 성능에 영향을 주게 됩니다. 성능이 좋아질 수도 있지만, 반대로 성능이 나빠질 수도 있습니다. 하나의 핵심 SQL이 성능이 나빠지면, 전체 시스템을 위험에 빠뜨릴 수도 있습니다. 파티션 적용은 이러한 위험 상황을 만들 가능성도 가지고 있습니다.
제가 경험한 사이트 역시 파티션을 적용한 후 SQL을 그에 맞게 변경하지 않아 30분만에 끝나던 배치 작업이 하루 종일 걸려도 끝나지 않은 경우가 실제 있었습니다. 또한 파티션 테이블이 추가되면 그만큼 관리할 포인트가 늘어납니다. DBMS를 관리하는 입장에서는 반가운 작업이 아닐 수 있습니다. 그러므로 이미 운영 중이며 데이터가 많이 쌓인 상황에서 파티션은 정말 어려운 작업이며, 섣불리 진행하면 안 되는 작업입니다.
파티션 키와 파티션 방법을 결정하기 위해서는 비즈니스를 이해하고 기존에 개발된 SQL들도 검토해야 합니다. 또한 파티션 구성 방법의 특징을 이해하고 있어야 합니다. 다시 말해, 파티션을 정하려면 비즈니스 담당자, 개발 담당자, 데이터베이스 담당자 모두가 모여 머리를 맞대고 고민해야 합니다.
또한, 앞서 말한 것처럼 위험성에 대해서 서로가 충분히 인지하고 파티션 후의 상황에 대응할 수 있도록 서로 노력해야 합니다.
클라우드 환경이면서 테이블의 데이터가 지속적으로 늘어날 가능성이 높다면 서둘러 파티션을 고민해보기 바랍니다.
하지만, 이미 데이터가 많이 쌓여 있고 대부분의 서비스가 성숙한 상황이라면 파티션에 대해서는 좀 더 보수적으로 접근하기 바랍니다. 비용을 줄이기 위한 노력이 서비스 장애라는 더 큰 어려움을 가져올 수도 있습니다.
본 문서에서 준비한 내용은 여기까지입니다. 이 문서에서는 파티션 키를 정하는 법이나 파티션의 종류에 대해서는 설명하지 않습니다. 또한 이에 따른 SQL의 성능도 설명하지 않습니다. 간단히 설명할 수 있는 부분이 아니기 때문입니다. 파티션과 성능 관련해서는 조시형님의 “친절한 SQL 튜닝”과 “오라클 성능 고도화”를 읽어 보시는 것을 추천합니다.
기업의 데이터는 계속 쌓여가고 늘어납니다. 그것도 쉴 새 없이요. 그리고 최근에는 더 폭발적으로 늘어나고 있습니다. 데이터가 문제없이 쌓이도록 하기 위해서는 필요한 디스크 공간을 미리 산정해야 합니다. 디스크 공간 확보를 위해서는 비용(Money)이 필요하기 때문입니다. 오늘은 빅 테이블의 용량을 산정하는 법에 대해 살펴보겠습니다. 어렵지 않으며, 모두가 알만한 이야기입니다. 리마인드 개념으로 한 번 읽어 보시기 바랍니다.
1,000G(Giga)의 판매 테이블이 있다고 가정하도록 하겠습니다. 해당테이블에는 2021년 1월부터 2023년 12월까지 2년 간의 데이터가 존재합니다. 테이블 용량이 너무 커서 해당 테이블이 내년에는 얼만큼 늘어날지 걱정이 된다고 합니다.
이 상황에서 가장 간단한 용량 산정 방법은, 2년 간의 데이터가 1,000G의 용량을 차지하므로 2024년에는 1,000G의 절반인 500G가 필요하다고 산정하는 것입니다.
여기서는 이처럼 간단한 방법이 아닌, 월별로 데이터를 살펴보고 그에 따른 증가양을 분석해 2024년의 테이블 용량을 산정하려 합니다. 현대의 데이터는 날이 갈수록 쌓이는 양이 늘어나고 있습니다. 데이터의 특징(업무의 특징)에 따라 다르겠지만, 많은 생활들이 디지털화되면서 그에 맞추어 디지털로 입력되는 데이터의 양 역시 늘어나고 있습니다.
그러므로 1000G의 절반을 2024년의 디스크 용량으로 산정하기 보다는 월별로 변경되는 데이터의 양을 조사해 디스크 크기를 산정하는 것이 보다 합리적입니다.
우선 판매 테이블의 년월별 데이터 건수를 조사합니다. 이를 통해 아래와 같은 표를 만들어 낼 수 있습니다.
테이블의 전체 용량은 1,000G이고 테이블의 총 건수는 60.38 억 건입니다. 1월달의 판매 데이터가 1.32 억 건이므로 아래와 같은 계산을 통해 1월달이 차지하는 디스크 용량을 추정해 볼 수 있습니다.
ROUND(1.32 / 60.38 * 1000,2) = 21.86 G
위 식을 각 월별로 모두 적용해 월별로 용량을 추정해볼 수 있습니다. 위의 표에서 노란색 부분이 월별로 용량을 추정한 부분입니다. 각 월별 용량을 모두 합해 보면 1,000G가 되는 것을 알 수 있습니다.
이제, 위 데이터를 근거로 2024년 12월까지의 디스크 용량을 산정하면 됩니다. 이때 다양한 방법을 고려할 수 있습니다.
여기서는 “최근 6개월 간의 용량 증감의 평균 값”을 사용해 용량을 산정할 계획입니다.
(이 기준은 각자가 생각하는 적정한 수준으로 정하면 되겠지요.)
“최근 6개월 간의 용량 증감의 평균 값”을 구하기 위해서는, 먼저 각 월별로 전월 대비 증감한 용량을 구합니다.
그리고 최근 6개월의 증가에 대해서만 평균을 구하면 됩니다. 아래 표의 녹색 부분에 해당합니다.
최근 6개월 간의 용량 증감 평균이 1.02G가 나옵니다.
이제 이 1.02G란 값을 적용해 2024년의 디스크 공간을 산정하면 됩니다. 2024년 1월 용량은 2023년 12월 용량인 56.64에 1.02를 더한, 57.66이 됩니다. 이 계산을 2024년 각 월에 적용해 2024년 디스크 공간을 산정할 수 있습니다.
판매 테이블의 디스크 용량 산정에 대한 최종 결과는 아래 차트와 함께 “최근 6개 월 간의 판매 테이블의 데이터 증가양을 고려했을 때, 2024년 판매 데이터를 위해서는 최소 759G의 디스크 확보가 필요합니다.”라고 마무리할 수 있습니다.
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을 운영하고 있다면, 이와 같은 기능이 있음을 잘 기억할 필요가 있습니다. 가끔 업무 요건에 따라, 튜닝을 위해 써먹을 수 있는 그런 기술이니까요.
위와 같이 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가 발생했습니다.
아래는 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 자원 사용량 증가에도 영향을 줄 수있습니다. 무엇보다 중요한 것은, 결론만 외우는 것이 아니라 실행계획을 통해 이와 같은 차이점을 확인하고 이해하는 것입니다.
위와 같은 인덱스 구성에서 다음과 같은 SQL을 실행합니다. SQL에서는 ORD_DT 조건만 사용하고 있습니다.
아래 SQL의 성능을 위해서는 ORD_DT가 선두 컬럼인 인덱스를 추가해야 하지만, 여기서는 인덱스 추가를 고려하지 않습니다. 현재 인덱스 구성에서 선두 컬럼이 ORD_DT인 인덱스는 존재하지 않습니다. 그러므로 일반적인 Index Scan이나 Index Lookup은 할 수 없습니다. 하지만 MySQL 8에서는 Index Skip Scan을 활용할 수 있으므로, ORD_DT가 두 번째 컬럼으로 위치한 T_ORD_JOIN_X02 인덱스를 사용할 수 있습니다. 아래 SQL의 TREE 형태의 실행계획을 보면 Index for skip scan이 있는 것을 알 수 있습니다. IO를 측정(Buffer pages)해보면 4,224 페이지의 IO 호출이 있었습니다. 실행시간 기준으로 0.94초 정도가 걸렸고요.
-- 1. INDEX 컬럼만 조회, SKIP SCAN 발생, Buffers = 4224
SELECT T1.CUS_ID ,T1.ORD_DT
FROM T_ORD_JOIN T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170301','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170305','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- ----- ---------- ----- -------------- -------------- ------- ---- ------ -------- --------------------------------------
1 SIMPLE T1 None range T_ORD_JOIN_X02 T_ORD_JOIN_X02 168 None 356310 100.0 Using where; Using index for skip scan
-> Filter: ((t1.ORD_DT >= <cache>(str_to_date('20170301','%Y%m%d'))) and (t1.ORD_DT < <cache>(str_to_date('20170305','%Y%m%d')))) (cost=96850.94 rows=356310)
-> Index range scan on T1 using index_for_skip_scan(T_ORD_JOIN_X02) (cost=96850.94 rows=356310)
* Rows read: 32180
* Buffer pages: 4224
2. SELECT 절에 인덱스 외의 컬럼을 조회 - TABLE FULL SCAN 발생
이제 SQL을 변경해봅니다. 앞에서 실행한 SQL은 T_ORD_JOIN_X02 인덱스에 있는 컬럼만 사용하고 있습니다.
다음과 같이 T1.*로 SQL을 변경해 실행해봅니다. 실행계획을 보면 Table scn(TABLE FULL SCAN)으로 변경이 되었습니다 IO 수치 역시 49,677로 이전 Index Skip Scan보다 열배 이상 늘어났습니다. 실행시간도 3.33초로 증가했습니다. 이처럼 SELECT 절에 인덱스 외의 컬럼이 추가되자 Index Skip Scan이 작동하지 않습니다. 인덱스를 통해 다시 클러스터드 인덱스에 접근하는 부하가 크다고 옵티마이져가 판단했기 때문에 FULL SCAN을 선택했을 것이라 생각이 듭니다.
(현재까지 테스트로는 인덱스에 없는 컬럼이 하나라도 추가되면 FULL SCAN으로 처리됩니다. 다른 테이블(두 번째 컬럼의 카디널리티가 큰 테이블)을 사용해도 마찬가지입니다. 이는 두 번째 컬럼의 통계를 적절히 계산 못하는 상황이 있는거 아닌가란 생각이 듭니다.)
-- 2. INDEX 이외의 컬럼을 사용, TABLE FULL SACN으로 변경됨, Buffers = 49677
SELECT T1.*
FROM T_ORD_JOIN T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170301','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170305','%Y%m%d')
Execution Plan:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- ----- ---------- ---- ------------- ---- ------- ---- ------- -------- -----------
1 SIMPLE T1 None ALL None None None None 3207440 11.11 Using where
-> Filter: ((t1.ORD_DT >= <cache>(str_to_date('20170301','%Y%m%d'))) and (t1.ORD_DT < <cache>(str_to_date('20170305','%Y%m%d')))) (cost=341086.14 rows=356311)
-> Table scan on T1 (cost=341086.14 rows=3207440)
* Rows read: 3224000
* Buffer pages: 49677
3. SELECT 절에 PK 컬럼만 조회 - INDEX SKIP SCAN 발생
이번에는 인덱스 컬럼인 CUS_ID, ORD_DT가 아닌 ORD_SEQ를 SELECT 절에 사용해 봅니다. 그러면 다시 Index Skip Scan이 작동합니다. MySQL의 InnoDB는 테이블마다 PK로 클러스터드 인덱스를 만들고, 넌클러스터드 인덱스의 리프 노드에는 클러스터드 인덱스의 키(PK) 값을 저장하기 때문에, 이처럼 PK 컬럼을 SELECT하는 경우는 기존의 X02 인덱스만 사용해서 해결이 가능합니다.
이러한 포인트를 잘 이해했다면, 이 부분을 응용해 T1.* 와 같은 결과를 만들어내면서 Index Skip Scan을 유지할 수 있습니다. (이 부분은 별도 글로 올리도록 하겠습니다.)
-- 3. PK 컬럼(ORD_SEQ)만 SELECT, INDEX SKIP SCAN, Buffers = 4224
SELECT T1.ORD_SEQ
FROM T_ORD_JOIN T1
WHERE T1.ORD_DT >= STR_TO_DATE('20170301','%Y%m%d')
AND T1.ORD_DT < STR_TO_DATE('20170305','%Y%m%d');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-- ----------- ----- ---------- ----- -------------- -------------- ------- ---- ------ -------- --------------------------------------
1 SIMPLE T1 None range T_ORD_JOIN_X02 T_ORD_JOIN_X02 168 None 356310 100.0 Using where; Using index for skip scan
-> Filter: ((t1.ORD_DT >= <cache>(str_to_date('20170301','%Y%m%d'))) and (t1.ORD_DT < <cache>(str_to_date('20170305','%Y%m%d')))) (cost=96849.94 rows=356310)
-> Index range scan on T1 using index_for_skip_scan(T_ORD_JOIN_X02) (cost=96849.94 rows=356310)
* Rows read: 32180
* Buffer pages: 4224
준비한 내용은 여기까지입니다. MySQL도 이제 Index Skip Scan이 있다 정도를 알고 가시면 될거 같습니다.^^
MySQL 또는 PostgreSQL의 SQL 튜닝 입문 강의가 필요하신 분은 아래 링크를 참고해주세요.!
Leading(table table[ table...]) - Forces join order as specified. - 지정된 대로 조인 순서를 강제한다 Leading(<join pair>) - Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure. - 지정된 대로 조인 순서와 방향을 강제한다. 조인 쌍은 괄호로 둘러싸인 테이블 또는 다른 조인 쌍의 쌍이며, 이를 통해 중첩 구조를 만들 수 있다
이처럼 두 가지 사용법이 적혀 있습니다. 하지만 이 설명만 봐서는 잘 이해할 수 없습니다.
Leading 힌트의 용법을 이해하기 위해서는 조인 순서와 조인 방향에 대해 정의할 필요가 있습니다.
조인 순서: 조인을 처리하는 순서
조인 순서가 A, B, C: A와 B를 조인해, (A, B)를 만든 후에 (A, B)와 C를 조인하는 순서로 처리
이처럼 조인 순서만 정의하면, 두 데이터 집합을 조인할 때는 어떤 방향으로 조인할지는 알 수 없다.
예를 들어, A와 B를 조인할 때 어느 테이블을 먼저 처리할지는 알 수 없다.
(A, B)와 C를 조인할 때도, 어느 쪽을 먼저 접근해 처리할지는 알 수 없다.
조인 방향: 두 데이터 집합이 결합(조인)할 때 정해지는 두 데이터 집합 간의 접근 순서
A, B, C의 조인 순서이면서, A에서 B 방향으로 조인, C에서 (A, B) 방향으로 조인
조인 순서와 함께 조인 방향이 정의되었다.
A에서 B 방향으로 조인 처리해서 (A, B) 생성, C에서 (A, B) 데이터 집합 방향으로 조인 처리
A, B, C의 조인 순서이면서, B에서 A 방향으로 조인, (B, A)에서 C 방향으로 조인
B에서 A 방향으로 조인 처리해서 (B, A) 생성, (B, A) 집합에서 C 방향으로 조인 처리
아마 이해가 될 듯하면서도 이해가 안될 수도 있습니다. 조인 순서나 조인 방향이나 얼핏 보기에 비슷하니까요.
아래와 같이 Leading(t1 t2) 로 힌트를 지정합니다. 이처럼 괄호를 하나만 사용하면 조인 순서만 지정됩니다. 이 경우 NL 조인을 한다고 가정하면 t1을 먼저 접근해 t2 방향으로 조인을 처리할 지, t2를 먼저 접근해 t1 방향으로 조인을 처리할지 알 수 없습니다. 이러한 조인 방향은 옵티마이져가 선택합니다. 사실 두 개 테이블만 있는 상황에서는 이 같은 조인은 불필요합니다.
/*+ Leading(t1 t2) */
SELECT t1.cus_id ,SUM(t2.ord_qty) ord_qty
FROM m_cus t1
INNER JOIN t_ord_join t2
ON (t2.cus_id = t1.cus_id)
WHERE t1.cus_gd = 'B'
AND t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
GROUP BY T1.cus_id;
이번에는 Leading 힌트에 괄호를 중첩해서 사용합니다. 이와 같이 정의하면 조인 방향이 정해집니다. 단순한 NL 조인이라면, 앞에 있는 t1이 선행 집합이 되어 t2 방향으로 접근해 조인이 처리됩니다. 그런데, SQL이 HASH 조인으로 풀린다면 주의가 필요합니다. Leading 힌트의 뒤쪽에 있는 t2가 Build가 되고, t1이 Probe가 됩니다.
다시 말해, HASH JOIN에서는 Build가 일반적으로 먼저 만들어진다는 개념과, Leading의 서술에 따른 순서가 반대되므로 이 부분은 외워두기 바랍니다.
/*+ Leading((t1 t2)) */
SELECT t1.cus_id ,SUM(t2.ord_qty) ord_qty
FROM m_cus t1
INNER JOIN t_ord_join t2
ON (t2.cus_id = t1.cus_id)
WHERE t1.cus_gd = 'B'
AND t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
GROUP BY T1.cus_id;
이번에는 세 개 테이블을 조인해봅니다. 아래와 같이 Leading에서 순서만 지정해봅니다. t1, t2, t3, 순서의 조인만 유지합니다. 두 데이터 집합이 조인되는 순간에는 어느쪽을 선행을 할지, HASH 조인이라면 어느쪽을 Build로 할지는 옵티마이져가 알아서 선택합니다.
/*+ Leading(t1 t2 t3) */
SELECT t1.itm_id ,t1.itm_nm ,t2.ord_st ,count(*) ord_qty
FROM m_itm t1
INNER JOIN t_ord_join t2 on (t2.itm_id = t1.itm_id)
INNER JOIN m_cus t3 ON (t3.cus_id = t2.cus_id)
WHERE t1.itm_tp = 'ELEC'
AND t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
AND t3.cus_gd = 'B'
GROUP BY t1.itm_id ,t1.itm_nm ,t2.ord_st;
조인순서와 함께 조인 방향을 모두 지정하고 싶다면 아래와 같이 괄호를 조인이 발생하는 두 데이터 집합간에 추가 중첩합니다. 아래 SQL의 조인되는 순서와 방향을 정리하면 다음과 같습니다.
Leading(((t1 t2) t3)) 괄호가 세 번 중첩, t1과 t2를 묶고, (t1 t2)와 t3를 한 번 더 묶었습니다.
이러한 정의가 틀리지는 않지만, 이 부분에만 포커스를 맞추어서 접근하다 보면 튜닝이 더 어려울 수 있습니다.
SQL 성능 개선의 초심자라면, “SQL을 빠르게 만들자” 보다는, “SQL의 비효율을 찾아 제거해보자”라는 개념으로 접근하는 것이 좀 더 쉽습니다.
그러므로 저는 SQL 튜닝에 대해 다음과 같이 정의합니다. “SQL 튜닝이란 SQL의 비효율을 제거하는 작업이다. 여기에 SQL의 중요도에 따라 추가 최적화를 이끌어 내야 하는 작업이다.” 여기서 SQL의 중요도는 사용 빈도나 업무의 중요성으로 판단할 수 있습니다.
SQL 튜닝 관련해 제가 경험한 몇 가지 사례를 살펴보겠습니다. 안타깝게도 MySQL이나 PostgreSQL 보다는 오라클에서 겪었던 경험이 주를 이룹니다.
제가 경험한 사이트 중에 12시간이 지나도 끝나지 않는 SQL이 있었습니다. 해당 SQL에는 3억 건 이상의 데이터 집합 두 개가 조인하고 있었고, 이 외에도 천 만 건 이상의 여러 테이블이 조인이 되는 한 문장의 배치 SQL이었습니다. 물론 오라클의 병렬 처리도 작동하고 있는 SQL이었습니다. 이러한 배치 SQL은 튜닝이 쉽지 않습니다. 튜닝의 실제 효과를 알기 위해서는 실행을 해야 하는데, 한 번 실행하면 최소 몇 시간은 걸리기 때문입니다. 어려운 상황 속에서도 SQL을 부분별로 나누어서 살펴보다 보니, 조인 전에 매우 많은 IN 조건이 처리되면서 오래 걸리는 것을 확인해습니다. IN 보다 조인이 먼저 처리되도록 SQL을 변경하자 4시간 이내로 SQL이 완료되었니다. 이는 조인을 먼저 하면 결과 건수가 줄어들어 IN 처리할 대상이 그만큼 줄어들어 개선 효과가 있었던 사례입니다. 어느 한 사이트는 사용자들이 비교적 자주 실행하는 SQL인데도 43초가 걸리고 있었습니다. SQL을 살펴보니, 특정 사용자가 자신의 데이터만 조회하는 SQL임에도 불구하고 사용자ID에 대한 조건을 LIKE로 처리하고 있었습니다. 이로 인해 사용자ID에 대한 인덱스를 사용하지 않아 오래 걸리는 케이스였습니다. 이 경우는 힌트로 해결할 수도 있지만, 가장 좋은 것은 프로그램을 변경해 사용자ID에 대한 조건을 같다(=) 조건으로 처리하는 것입니다. 이와 같이 처리하면 DBMS의 옵티마이져가 알아서 사용자ID에 대한 인덱스를 사용하게 됩니다. 이를 통해 43초 걸리던 SQL은 0.02초로 개선이 되었습니다.
또 다른 사이트는 차세대 사이트로서, 고객의 주문을 집계하는 배치에서 성능 저하가 있었습니다. AS-IS에서는 30분이 안 걸리던 집계 처리가 차세대에서는 6시간 이상이 걸리는 상황이 발생한 것입니다. 원인을 살펴보니 차세대로 넘어가면서 관련 테이블을 파티션으로 변경했고, SQL이 파티션의 키 값을 활용하지 못하도록 되어 있어 성능 저하가 발생한 것입니다. SQL을 변경해 파티션 키를 사용할 수 있도록 변경을 해주었고, 원래의 성능으로 돌아갈 수 있었습니다. 지금까지 소개한 사례들은 주로 오라클 환경에서의 경험입니다. MySQL 환경에서의 사례도 있습니다. MySQL 환경에서 업데이트 SQL이 한 시간 이상 걸려 문의가 들어온 적이 있었습니다. SQL은 업데이트 대상을 상관 서브쿼리 방식으로 선별하고 있었고, 실행계획을 확인해 보니, 상관 서브쿼리의 테이블을 FULL SCAN으로 처리하고 있었습니다. SQL을 중지하고 업데이트 대상 선별 방식을 직접적인 조인으로 변경하자 10초만에 업데이트가 완료되었습니다.
지금까지 살펴본 튜닝 사례에서 저는 “SQL을 빠르게 하자”가 아닌, “이 SQL의 비효율이 뭐지?”라는 방향으로 접근을 했습니다. 이와 같은 방향으로 접근해 비효율을 제거하다 보면, SQL은 저절로 빨라지게 됩니다.
사람마다 다를 수 있지만, 저에게 SQL 튜닝은 그 과정 자체가 미지의 세계를 탐험하는 것처럼 매우 흥미롭고 흥분되는 일입니다. 마치 셜록 홈즈가 사람의 겉모습만을 보고 그 사람의 직업과 과거를 맞추는 것처럼, SQL과 실행계획을 통해 문제점을 직관적으로 찾아내는 것은 행복한 흥분을 선사합니다.
StartUP Tuning 강의를 통해 SQL 튜닝의 기본을 습득하고, 그 신나는 세계에 한 발짝 들어와 보기 바란다.
MySQL에서 테이블을 생성할 때, 캐릭터셋(CHARSET)과 콜레이션(COLLATION)을 설정할 수 있습니다.
캐릭터셋과 콜레이션은 테이블뿐만 아니라 컬럼 단위로도 설정이 가능합니다.
이러한 설정을 잘못하면 조인 SQL 자체가 실행이 안될 수도 있으며, 이는 성능 저하로 연결될 수도 있습니다.
캐릭터셋과 콜레이션은 데이터베이스에서 문자 데이터를 저장하고 비교하는 방식을 뜻합니다.
캐릭터셋(Character Set)
데이터베이스에 저장할 수 있는 문자들의 집합
각 문자셋은 다양한 문자를 인코딩하는 코드를 정의합니다. UTF-8, ASCII, LATIN1등이 있습니다.
UTF-8은 국제적으로 널리 사용되는 캐릭터셋입니다. 거의 모든 언어와 이모지까지 지원합니다.
캐릭터셋에 따라 같은 텍스트도 다른 방식으로 저장될 수 있습니다.
콜레이션(Collation)
문자 데이터를 비교하고 정렬할때 사용하는 규칙의 집합입니다.
대소문자 구분, 악센트 구분, 문자의 정렬 순서 등을 정의합니다.
콜레이션에 따라 언어와 문자의 우선 순위가 달라질 수 있습니다.
이러한 캐릭터셋과 콜레이션은 MySQL이 설치되면서 서버 레벨(DBMS)에서 정의가 됩니다.
my.cnf나 my.ini에 기본으로 값이 설정되어 있으며, 변경이 가능합니다.
현재 운영중인 시스템에서 서버레벨에서 이 설정을 변경하는 순간 매우 큰 혼란이 만들어지게 됩니다.
서버 레벨에서 정의한 캐릭터셋과 콜레이션은 데이터베이스를 생성할 때 상속됩니다.
그리고 데이터베이스에 만들어진 캐릭터셋과 콜레이션은 다시 테이블에 상속됩니다.
캐릭터셋과 콜레이션이 이미 상속되어 테이블이 만들어진 상태에서 이와 같은 설정을 변경한다면 어떤 여파가 있을지 상상하기도 힘듭니다. 그러므로 캐릭터셋과 콜레이션은 서비스를 시작하기 전에 정의되어야 합니다. 아니, 개발을 시작하기 전 설계 단계에서부터 정의되어야 맞습니다.
MySQL 관련된 블로그나 자료를 찾다 보면, 테이블을 생성할 때, 캐릭터셋이나 콜레이션을 지정해서 생성하는 스크립트를 볼 수 있습니다. 이러한 스크립트를 카피해서 사용하다 보면, 예상하지 못한 문제에 마주칠 수 있습니다.
먼저, 자신이 접속한 데이터베이스의 캐릭터셋과 콜레이션을 살펴봅니다.
# 사용할 기본 데이터베이스 변경하기
USE MYTUNDB;
# 현재 계정이 사용하는 기본 데이터베이스 확인하기
SHOW VARIABLES LIKE 'character_set%';
-- character_set_database utf8mb4
-- character_set_connection utf8mb4
SHOW VARIABLES LIKE 'collation%';
-- collation_database utf8mb4_0900_ai_ci
-- collation_connection utf8mb4_0900_ai_ci
위와 같이 기본 캐릭터셋과콜레이션이 있음에도 불구하고, 아래와 같이 COLLATE를 기본과 다른 설정으로 생성을 해봅니다. 인터넷을 검색하다 알게된걸 그냥 카피해서 사용한 것이죠. 데이터베이스의 기본 콜레이트는 utf8mb4_0900_ai_ci이지만, 아래 테이블은 utf8mb_unicode_ci 를 사용했습니다.
CREATE TABLE MYTUNDB.T_ORD_TEST
(
ORD_SEQ BIGINT UNSIGNED NOT NULL COMMENT'주문번호',
CUS_ID VARCHAR(40) NOT NULL COMMENT '고객ID',
ORD_DT DATETIME NULL COMMENT '주문일시',
ORD_ST VARCHAR(40) NULL COMMENT '주문상태',
PAY_DT DATETIME NULL COMMENT '결제일시',
PAY_TP VARCHAR(40) NULL COMMENT '결제유형',
ORD_AMT DECIMAL(18,3) NULL COMMENT '주문금액',
PAY_AMT DECIMAL(18,3) NULL COMMENT '결제금액',
PRIMARY KEY(ORD_SEQ)
)
CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
COMMENT '주문테스트';
-- 테스트를 위한 데이터 입력 및 인덱스 생성
INSERT INTO MYTUNDB.T_ORD_TEST SELECT* FROM T_ORD;
CREATE INDEX T_ORD_TEST_X01 ON T_ORD_TEST(CUS_ID);
이제 기존에 원래 있던 테이블과 조인 SQL을 만들어 실행해 봅니다.
SELECT *
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_TEST T2 ON (T2.CUS_ID = T1.CUS_ID)
WHERE T1.CUS_ID = 'CUS_0001'
위 SQL을 실행해보면 다음과 같은 에러를 만납니다.
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
두 테이블(M_CUS와 T_ORD_TEST)간에 조인 조건으로 사용한 CUS_ID가 테이블별로 다른 콜레이트를 사용하기 때문에 발생한 에러입니다. 두 테이블의 CUS_ID에 대한 콜레이트는 아래 SQL로 확인할 수 있습니다.
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('M_CUS','T_ORD_TEST') AND COLUMN_NAME = ('CUS_ID')
AND TABLE_SCHEMA = 'MYTUNDB';
TABLE_NAME|COLUMN_NAME|CHARACTER_SET_NAME|COLLATION_NAME |
----------+-----------+------------------+------------------+
m_cus |CUS_ID |utf8mb4 |utf8mb4_0900_ai_ci|
t_ord_test|CUS_ID |utf8mb4 |utf8mb4_unicode_ci|
이와 같이 콜레이트가 다른 상황에서 SQL을 실행하기 위한 임시 방편은 아래와 같이 조인시 콜레이트를 재정의하는 것입니다. 아래는 T1(M_CUS)의 콜레이트를 T2(T_ORD_TEST)와 동일하게 변경한 경우입니다.
EXPLAIN ANALYZE
SELECT *
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_TEST T2 ON (T2.CUS_ID = T1.CUS_ID COLLATE utf8mb4_unicode_ci)
WHERE T1.CUS_ID = 'CUS_0001'
;
-> Index lookup on T2 using T_ORD_TEST_X01 (CUS_ID=('CUS_0001' collate utf8mb4_unicode_ci)) (cost=11.55 rows=33) (actual time=0.168..0.183 rows=33 loops=1)
실행계획을 보면, T_ORD_TEST_X01이라는 인덱스를 사용해 결과를 처리하고 있습니다.
인덱스를 사용한다고 성능이 무조건 좋은건 아니지만, 원하는 방향으로 처리가 되고 있습니다.
만약에 콜레이트를 T1쪽이 아닌 T2쪽의 콜레이트를 변경하면 어떻게 될까요? 아래와 같이 T2(T_ORD_TEST)를 FULL SCAN하는 것을 알 수 있습니다.
빅데이터와 No-SQL 기술의 등장은 우리에게 텍스트, 이미지, 비디오, 음성과 같은 다양한 형태의 데이터를 새로운 방식으로 다루게 했습니다. 이전에는 '데이터'라고 하면 대부분의 사람들이 컬럼과 로우로 구성된 테이블 형태의 '관계형 데이터'를 떠올렸습니다. 하지만 지금은 그 경계가 흐려졌습니다.
No-SQL 기술이 주목받을 때, 많은 이들이 관계형 데이터베이스의 중요성이 줄어들 것이라 예상했지만, 그러지 않았습니다. No-SQL은 그 자체로 중요한 기술이지만, 관계형 데이터베이스를 완전히 대체할 수는 없었습니다. 이는 기업의 업무에 필요한 데이터를 다루기에 관계형 구조의 데이터가 효율이 가장 좋기 때문입니다.
그렇다면, 생성형 AI의 선두 주자인 GPT와 같은 기술의 등장은 어떨까요? GPT는 관계형 데이터베이스의 위치에 어떤 영향을 줄까요? 실제로 GPT는 테이블 형태의 데이터를 저장하거나 관리하는 기능에는 한계가 있습니다. 그러나, GPT는 관계형 데이터의 분석 및 해석에 있어 매우 유용한 도구로 활용할 수 있습니다.
기업들이 오랜 시간 동안 쌓아온 관계형 데이터는 이제는 GPT와 같은 기술을 통해 새로운 가치와 인사이트를 제공할 수 있습니다. 따라서 GPT의 등장은 기업들에게 오랜 시간 동안 축적해온 관계형 데이터를 다시 한번 깊게 다뤄볼 기회를 제공하게 될 것입니다.
간단한 예를 살펴보도록 하죠. 아래는 제가 사용하는 샘플 DB에서 월별 제품카테고리별 판매 현황을 추출한 SQL입니다.
(기업의 관계형 데이터를 추출해냈다고 생각하면 되는 것이죠.)
SELECT DATE_FORMAT(T1.ORD_DT,'%Y%m') Order_YearMonth
,T3.ITM_TP ItemCategory
,COUNT(*) OrderQty
,SUM(T1.ORD_AMT) OrderAmount_KRW
FROM MYTUNDB.T_ORD T1
INNER JOIN MYTUNDB.T_ORD_DET T2 ON (T2.ORD_SEQ = T1.ORD_SEQ)
INNER JOIN MYTUNDB.M_ITM T3 ON (T3.ITM_ID = T2.ITM_ID)
GROUP BY DATE_FORMAT(T1.ORD_DT,'%Y%m')
,T3.ITM_TP
ORDER BY Order_YearMonth, ItemCategory;
위 SQL로 추출한 결과를 아래와 같이 첨부하니, GPT-4를 사용하시는 분들은 똑같이 분석을 해보시기 바랍니다.
위 데이터를 GPT-4를 사용해 분석을 요청해 아래와 같은 결론을 얻었습니다. GPT를 통해 더욱 다양한 분석 기법을 요청할 수도 있습니다. 또한 집요하게 물어볼 수록 우리가 쉽게 얻기 어려운 정보도 얻어낼 수 있습니다.
- 제품 유형별로는 '의류' 카테고리에 더 주력하는 것이 좋을 것 같습니다. - 월별 판매 추이를 보면 1월부터 4월까지, 그리고 8월부터 10월까지 판매가 상승하는 추세를 보이므로 이 기간에 판매 활동을 강화하는 것이 좋습니다. - 계절별로는 여름에 판매가 가장 활발하므로 여름 시즌에 특히 주의를 기울이면 좋을 것 같습니다.
예산 상의 한계로 고가의 분석 도구나 전문 분석가를 고용하지 못했던 기업들에게는 GPT와 같은 AI 도구가 저렴하면서도 효과적인 분석 도구로서 큰 기회가 될 수 있습니다.
더불어, 인간이 놓치기 쉬운 데이터의 패턴이나 정보를 GPT를 통해 발견할 수도 있을 것입니다.
그렇지만, 아쉽게도 한계는 존재합니다. 대량의 데이터 처리는 여전히 제한적이며, 특히 실시간 분석에도 한계가 있습니다. 분석 과정에서 예상치 못한 방향으로 진행될 수도 있어, AI의 분석 결과는 반드시 인간이 검증하고 해석해야 합니다.
마지막으로 강조하고 싶은 것은, 관계형 데이터의 중요성이 여전하다는 것입니다. GPT와 같은 혁신적인 기술의 등장에도 불구하고, 관계형 데이터를 다루는 기초적인 학문인 SQL과 관계형 데이터베이스 기술의 중요성은 여전하므로 지속적인 학습과 연구가 필요하다는 것을 잊지 말아야 합니다.
Chat-GPT4 기능을 활용해 Python으로 MySQL 스키마 비교 프로그램을 만들어봤습니다. (30분 안걸렸네요.) 저는, GPT-4를 사용하면서 아이언맨이 자비스와 대화하면서 뭔가를 만들어 가는 것이 불가능한 일이 아니구나라는 생각이 들었습니다. 정말... 놀라운 생산성이네요.. 제가 직접 코딩한게 하나도 없네요. 어떻게 만들것인지를 대화를 통해 정의해주고, 제가 아는 정보를 약간 알려주고, 만들어준 코드를 카피해서 실행만 했네요.
GPT-4를 잘 다루려면 자신이 프로그램 언어를 잘 이해하고 있어야 합니다. 또한, 자신이 해당 분야에 대해 잘 알고 있어야 GPT를 효율적으로 사용할 수 있습니다. 개인적인 생각은 GPT-4가 나왔으니, 프로그래밍을 할 필요가 없는게 아니라, 프로그래밍을 더욱 잘해야 할거 같습니다. GPT-4 역시 에러가 나는 코드를 만들어내므로 이를 확인하고 대처할 수 있어야 합니다. 어쨋든 이정도 생산성이라면, 간단한 코드 대부분은 GPT-4가 대체해줄 가능성이 크기 때문에, 우리는 그보다 좋은 실력과 창의력을 가지고 있을 필요가 있습니다.
실제 삼성전자의 주가를 이용하여 TR(True Range)를 구해보면 아래 엑셀표와 같습니다. 삼성전자의 2023년 8월 10일 TR은 1,100원입니다. 그리고 2023년 8월 10일 ATR(20)은 TR의 최근 20일 평균이므로 1,475원입니다.
ATR(20) 값인 1475원을 사용하여 손절선을 설정합니다. 터틀 트레이딩에서는 ATR(20)의 2배를 사용한다고 합니다. 손절금액을 더 크게 설정하려면 배수를 높이면 됩니다. ATR(20)을 이용해 아래와 같이 손절률을 계산할 수 있습니다.(2배 사용)
손절률 = (현재가 - (현재가 - (ATR20 * 2))) / 현재가 * 100
삼성전자의 2023년 8월 10일 종가(현재가)가 68,000이므로 위 공식에 대입해 보면 아래와 같이 손절률이 나옵니다.
(68000 - (68000 - (1475*2)))/68000 * 100 = 4.33%
다음 단계가 매우 중요합니다. 손절률인 4.33%를 사용하여 매수할 수량(금액)을 결정해야 합니다. 먼저 감내 가능한 손실 금액을 결정해야 합니다. 즉, 얼마까지 손실이 허용되는지, 얼마까지 손실이 발생하면 반드시 손절할 것인지를 결정해야 합니다. 감내 가능한 손절 금액은 개인마다 다릅니다. 예를 들어, 한 번의 거래에서 최대 50,000원까지 손실을 감수할 수 있다면, 아래 공식을 사용해 매수 가능한 수량을 계산할 수 있습니다.
매수수량 = 손실가능금액 / (ATR20 * 2)
위 공식을 사용해, 삼성전자의 23년 8월 10일 기준으로 손실이 50,000원까지 나도 된다고 가정하면, 아래와 같이 매수 수량을 구할 수 있습니다.
50000 / (1475 * 2) = 17
삼성전자의 2023년 8월 10일 종가가 68,000원입니다. 68,000원으로 17주를 사면 총 1,156,000 원이 필요합니다.
삼성전자 17주를 산 후에 -4.33%로 Stop-Loss를 설정합니다. 그러면, 약 50,000원 정도 손실이 되면 자동 손절이 이루어지게 됩니다.
이번에는 주식을 어떤 요일에 사는게 좋은지, 과거 데이터를 간단히 집게해보도록 하겠습니다.
설명에 앞서 다음과 같은 사항을 기억해 주세요.
- 본 글은 주식 투자를 권장하거나, 어떤 종목을 추천하기 위한 글이 아닙니다.
- 본 글을 참고해 발생한 손실에 대해서는 누구도 책임지지 않습니다.
- 데이터의 정확성은 검토했지만, 다양한 이유로 일부 데이터가 부정확할수도 있으니 양해바랍니다.
년도별로 요일별 평균 수익률을 차트로 그려보면 다음과 같습니다.
각 요일에 종가(Close-Price)에 사서 다음날 시가(Open-Price)에 매도했다고 가정합니다.
월요일이 높은 년도도 있지만, 2011년에는 화요일이 월등합니다. 월요일은 마이너스이고요.
그리고 2015년에는 수요일이 높은 편으로 보입니다.
데이터를 더 갖다가 살펴볼 필요가 있어 보입니다. 예를 들어, 주식 시장의 전체 분위기가 좋으면 주초에 주로 오르고, 시장 분위기가 나쁘면, 주초가 별로인가.. 라는 추측이 듭니다. 나중에 지수 데이터를 연결해서 살펴봐야 할거 같네요.
위 차트를 년도 상관없이, 요일별로 평균을 구해보면 아래와 같습니다. 전체 평균을 봤을때는 화요일이 가장 좋네요.!
여기서는 각 요일별 종가에 매수한 후에, 다음날 시가에 바로 판 것을 가정했습니다.
이러한 집계 기준을 어떻게 정하는지에 따라 결과가 많이 달라질거 같습니다.
예를 들어, 월요일 종가가 아닌 시가에 샀다고 가정할 수도 있고, 사고 나서 일주일을 보유했다고 가정할 수도 있고요.
그리고 한국 시장이 아니라, 미국이나 중국이라면? 또 다른 결과가 나올까요? 나중에 기회가 되면 해봐야겠습니다.
이상입니다.!
역시 SQL로 간단히 구할 수 있습니다.
- 데이터 추출 기준: 2010년부터 2023년 8월까지의 한국 주식 시장의 주가 데이터
- 데이터 추출 방법: SQL
WITH W01 AS(
SELECT T1.STK_CD ,T1.DT
,T1.C_PRC B_PRC # BUY when Close price
,DAYOFWEEK(T1.DT) WK_NO
,CASE DAYOFWEEK(T1.DT)
WHEN 2 THEN 'MON'
WHEN 3 THEN 'TUE'
WHEN 4 THEN 'WED'
WHEN 5 THEN 'THU'
WHEN 6 THEN 'FRI' END WK
,LEAD(T1.O_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) S_PRC # SELL when next open Price
FROM HIST_DT T1
WHERE T1.DT >= '20100101'
AND T1.DT <= '20141231'
)
SELECT DATE_FORMAT(T1.DT,'%Y') YY
,T1.WK
,AVG(ROUND((T1.S_PRC - T1.B_PRC) / T1.B_PRC * 100, 2)) AVG_PROF
,COUNT(*) SAMP_CNT
FROM W01 T1
GROUP BY DATE_FORMAT(T1.DT,'%Y'), T1.WK
ORDER BY DATE_FORMAT(T1.DT,'%Y'), MAX(T1.WK_NO)
When is the time of day that working people are most interested in the stock market?
I think it's probably not during the morning hours when the stock market opens.
The stock market that opens after waiting overnight! You can't resist checking it out in the morning, can you?
When I check the stocks that are going up in the morning, my heart flutters. Oh, I should buy before they leave me behind!
When I look at the stocks that fall in the morning, I have a somewhat greedy thought. Oh! It fell, it's the price I've been waiting for. I should buy it!
Whether it's because the price dropped or went up, it seems like the "I have to buy it!" circuit in my brain is highly activated by morning.Not everyone may agree, but it is true that stock trading is most active in the morning when the stock market opens.
So, is it a good choice to buy stocks at the morning price, precisely the opening price (Open-Price)?
Let's find out through simple data aggregation.
Before explaining, I want to clarify that this is not an article recommending stock investment or suggesting any specific stock.
Please remember that no one is responsible for any losses incurred in the stocks invested in as a result of this article.
We have verified the accuracy of the data. However, please understand that there may be some inaccuracies due to errors that may have occurred during the data collection process or at the time of data collection.
Let's use SQL to calculate the average profit rate for the following two trade cases:
Case 1: Buy at today's open price and sell at today's close price.
Case 2: Buy at today's close price and sell at tomorrow's open price.
Actually, we can't buy and sell stocks every day like in the example above, right? However, if we aggregate the data, we can find some useful information for our investment.
If we use the daily stock price data I have (Korea) and aggregate it by year, the following results are obtained:
Overall, it can be seen that Case2 [Buy at today's closing price and sell at tomorrow's opening price] has a much better average return rate than Case1. Buying at today's opening price and selling at today's closing price, like in Case1, results in mostly negative returns on average.
This test is completely meaningless for those who invest in value. If you approach investing from a perspective of investing in really good companies, it doesn't matter whether you buy in the morning or in the evening. However, I think this is useful information to refer to when buying stocks.
Conclusion! Buy at the closing price!
The above result can be easily implemented with the following SQL. Are you all studying SQL?
WITH W01 AS(
SELECT T1.STK_CD ,T1.DT ,T1.C_PRC ,T1.O_PRC
,LEAD(T1.O_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_O
,LEAD(T1.C_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_C
FROM HIST_DT T1
WHERE T1.DT >= '20210101'
AND T1.DT <= '20211231'
ORDER BY T1.DT DESC
)
,W02 AS(
SELECT T1.*
,ROUND((T1.C_PRC - T1.O_PRC) / T1.O_PRC * 100,1) `OPEN_CLOSE`
,ROUND((T1.AF_O - T1.C_PRC) / T1.C_PRC * 100,1) `CLOSE_OPEN`
FROM W01 T1
)
SELECT DATE_FORMAT(T1.DT,'%Y')
,AVG(`OPEN_CLOSE`) `CASE1_OPEN_CLOSE`
,AVG(`CLOSE_OPEN`) `CASE2_CLOSE_OPEN`
,COUNT(*) CNT
FROM W02 T1
GROUP BY DATE_FORMAT(T1.DT,'%Y');
실제, 위의 예처럼 매일매일 사고 팔수는 없겠죠? 그래도 데이터를 집계해보면, 우리가 투자에 참고할만한 정보가 나온답니다.
제가 보유한 일별 주가 데이터(한국)를 사용해 년별로 집계해보면 아래와 같은 결과가 나옵니다.
전체적으로 Case2[오늘 종가(Close Price) Buy - 내일 시가(Open Price) Sell]가 Case1보다 평균 수익률이 훨씬 좋은 것을 알 수 있습니다.Case1과 같이 오늘시가에 사서 오늘종가에 파는 경우는 평균적으로 마이너스 수익률인 경우가 대부분입니다.
가치투자를 하시는 분들께는 이런 테스트는 완전히 무의미합니다. 정말 좋은 기업에 투자 관점을 가지고 접근하시는 거라면 아침에 사든, 저녁에 사든 중요하지 않으니까요. 하지만, 자신이 주식을 살때 한 번쯤은 참고할 만한 정보라고 생각합니다.
결론! 종가에 사자!
위 결과는 아래의 SQL로 간단하게 구현할 수 있답니다. 다들, SQL 공부 하고 계시죠?
WITH W01 AS(
SELECT T1.STK_CD ,T1.DT ,T1.C_PRC ,T1.O_PRC
,LEAD(T1.O_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_O
,LEAD(T1.C_PRC) OVER(PARTITION BY T1.STK_CD ORDER BY T1.DT ASC) AF_C
FROM HIST_DT T1
WHERE T1.DT >= '20210101'
AND T1.DT <= '20211231'
ORDER BY T1.DT DESC
)
,W02 AS(
SELECT T1.*
,ROUND((T1.C_PRC - T1.O_PRC) / T1.O_PRC * 100,1) `오늘시가_오늘종가`
,ROUND((T1.AF_O - T1.C_PRC) / T1.C_PRC * 100,1) `오늘종가_내일시가`
FROM W01 T1
)
SELECT DATE_FORMAT(T1.DT,'%Y')
,AVG(`오늘시가_오늘종가`) `CASE1_오늘시가_오늘종가`
,AVG(`오늘종가_내일시가`) `CASE2_오늘종가_내일시가`
,COUNT(*) CNT
FROM W02 T1
GROUP BY DATE_FORMAT(T1.DT,'%Y');
An index is a data object (structure) that collects some data to quickly find data within a table. Indexes are essential for improving SQL performance. To maximize SQL performance, you need the optimal index.
To create the optimal index, the following skills are required:
Ability to understand the physical structure of the index
Ability to break down and understand complex SQL
Ability to predict how the created index will be used
Understanding of the internal processing methods (NESTED LOOPS, MERGE, HASH) of JOINs
Experience in creating and applying indexes in real-world situations, in addition to the above skills.
To fully understand indexes, it is recommended to search for more materials along with books related to DB performance.
The reason for studying indexes is not to create them, but to create SQL that uses indexes efficiently. Creating indexes is the next step.
Profiling shows performance-related information about the executed SQL. When profiling is enabled through SET PROFILING and SQL is executed, profiling information is collected. The collected profile information can be viewed through the show profile command.
Please refer to the following commands for profiling:
Turn on profiling: SET PROFILING = 1;
Turn off profiling: SET PROFILING = 0;
Profile retrieval:
Detailed profile of the last executed SQL: SHOW PROFILE;
Execution time of profiled SQL: SHOW PROFILES;
Detailed profile of a specific SQL: SHOW PROFILE FOR QUERY [Profile ID]
The profile ID of a SQL executed through SHOW PROFILES can be obtained.
Detailed profile retrieval by specific element:
show profile cpu for query [Profile ID];
show profile block IO for query [Profile ID];
show profile memory for query [Profile ID];
show profile source for query [Profile ID];
The process of setting up profiling, running SQL, and checking the profiling information collected for the SQL is as follows.
SET PROFILING = 1; # Turn on profile
# SQL execute
SELECT COUNT(*)
FROM MYTUNDB.T_ORD_BIG T1
WHERE T1.ORD_YMD LIKE '201701%';
SHOW PROFILE;
# profile result
# starting 0.000077
# ...
# statistics 0.008959
# preparing 0.000035
# executing 0.205336
# ...
# cleaning up 0.000031
SET PROFILING = 0; # Turn off profile
The Need for Continuous SQL Performance Improvement
Improving SQL performance yields significant benefits even in the short term. However, to maintain stable system performance, it is necessary to conduct long-term SQL performance improvement work.
In the figure below, it can be seen that the performance of the malicious SQL was dramatically improved by tuning it in the early stages of the project. However, as the system continues to add or change features, SQL is modified, leading to performance degradation again. Therefore, continuous SQL performance improvement is necessary.
현재(2023년 1월) 기준으로 위의 레퍼런스를 살펴보면, 해시 조인과 관련된 힌트로, HASH_JOIN과 NO_HASH_JOIN이 있는 것을 알 수 있습니다. 하지만, Description을 보면 해당 힌트는 MySQL 8.0.18 에서만 사용할 수 있는 힌트입니다.
저는 현재 8.0.22 를 사용하고 있습니다. 이처럼 8.0.18 이후 버젼에서는 해시 조인을 컨트를하려면 BNL이나 NO_BNL이란 힌트를 사용해야 합니다.
위 레퍼런스에는, BNL 힌트의 설명으로 아래와 같이 정리되어 있습니다.
- Prior to MySQL 8.0.20: affects Block Nested-Loop join processing;
- MySQL 8.0.18 and later: also affects hash join optimization;
- MySQL 8.0.20 and later: affects hash join optimization only
BNL은 원래 Block Nested-Loop 를 뜻합니다. NL(Nested-Loops) 조인을 처리할 때, 조인 버퍼를 사용해 NL 조인 성능을 향상시키는 기능입니다. MySQL 8 이전 버젼은 해시 조인을 지원하지 않았기 때문에, BNL 기능으로 해시 조인을 어느 정도 대체했던거 같습니다.
어찌되었든, 8.0.2 부터는 BNL 힌트가 해시 조인 최적화에 영향을 준다니 혼란스럽습니다. 아마도 8.0.2부터는 본연의 BNL(Block Nested-Loops) 기능 자체는 없어진거 같습니다. 그냥 BNL 힌트를 제거하고 HASH_JOIN 힌트를 살려줬으면 혼란이 없지 않았을까 생각이 듭니다만, 제가 모르는 다양한 이유가 있겠죠.
그러면, BNL 힌트를 사용해 해시 조인을 한번 테스트해볼까 합니다. 생각보다? 아니, 생각만큼 힌트가 잘 안먹습니다.
먼저, 저희가 테스트할 테이블인 M_CUS와 T_ORD_BIG에는 어떤 인덱스가 있는지 먼저 살펴봅니다.
인덱스는 조인의 내부적인 처리 방법(NL, HASH, Sort Merge)에 영향을 끼치는 중요 요소입니다.
MySQL에서 인덱스는 'show index from 테이블명' 명령어를 통해 쉽게 확인할 수 있습니다.
위와 같이 인덱스가 구성된 상태에서 아래와 같이 SQL을 실행해 봅니다. 실행 계획을 보면, M_CUS와 T_ORD_BIG을 NL 방식으로 조인하고 있습니다. 조인 조건으로 사용된 CUS_ID 컬럼의 인덱스는 M_CUS에만 있으므로, M_CUS를 후행 집합으로 NL 처리하고 있습니다.
EXPLAIN ANALYZE
SELECT T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID
-> Table scan on <temporary> (actual time=0.003..0.015 rows=72 loops=1)
-> Aggregate using temporary table (actual time=187.347..187.376 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=1.043..126.440 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.973..91.609 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=19800)
이제, NL이 아닌, 해시 조인으로 위 SQL이 처리되도록 하기 위해 아래와 같이 BNL 힌트를 적용합니다.
실행계획을 확인해보면, 힌트가 무시되고 NL 조인이 여전히 사용중인 것을 알 수 있습니다.
힌트를 잘 못 준건 아닐까란 생각에, /*+ BNL(T1) */ 이나, /*+ BNL(T1, T2) */, /*+ BNL(T2, T1) */ 등 다양하게 시도해봤으나 해시 조인이 발생하지는 않습니다.
아마도 MySQL의 옵티마이져가 생각했을때, 현재 SQL과 테이블, 인덱스 구성에서는 해시 조인이 비효율적이라고 판단한 것이겠죠.
EXPLAIN ANALYZE
SELECT /*+ BNL(T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.014 rows=72 loops=1)
-> Aggregate using temporary table (actual time=120.311..120.340 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=0.240..80.108 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.223..58.354 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.000..0.001 rows=1 loops=19800)
이 경우, T2(T_ORD_BIG)쪽에는 CUS_ID에 대한 인덱스가 없으므로 해시 조인이 발생할 가능성이 높습니다. (FULL SCAN 방식으로 NL을 처리할 수는 없으니까요.)
BNL 힌트 없이, JOIN_ORDER 만으로 조인 순서를 변경하자 해시 조인이 사용된 것을 알 수 있습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T1, T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.014 rows=72 loops=1)
-> Aggregate using temporary table (actual time=106.843..106.871 rows=72 loops=1)
-> Inner hash join (t2.CUS_ID = t1.CUS_ID) (cost=1598243.65 rows=355158) (actual time=0.386..67.943 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462) (actual time=0.250..55.218 rows=19800 loops=1)
-> Hash
-> Table scan on T1 (cost=9.25 rows=90) (actual time=0.058..0.081 rows=90 loops=1)
이번에는 조인 순서를 T2->T1으로 변경하고 BNL 힌트까지 추가해봅니다. 혹시나 JOIN_ORDER와 BNL을 같이 써야만 해시 조인이 사용되는지 의심되서 입니다.
하지만, 다시 NL 조인이 사용되는 것을 볼 수 있습니다. 아무래도 M_CUS의 PRIMARY 인덱스 사용의 우선 순위가 높은거 아닌가 싶습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T2, T1) BNL(T2, T1) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.011 rows=72 loops=1)
-> Aggregate using temporary table (actual time=93.163..93.184 rows=72 loops=1)
-> Nested loop inner join (cost=31569.86 rows=39462) (actual time=0.236..62.395 rows=19800 loops=1)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.219..44.639 rows=19800 loops=1)
-> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID) (cost=0.25 rows=1) (actual time=0.000..0.000 rows=1 loops=19800)
끝으로, 실제 성능과 상관없이, 죽었다 깨어나도 T2->T1 순서로 조인 순서를 유지하면서, 해시 조인을 만들어 보고 싶다면, 조인 조건의 강제 변화를 고려할 수 있습니다.
아래와 같이 T1.CUS_ID를 TRIM 처리해서 인덱스를 사용못하도록 합니다. 그리고 JOIN_ORDER 힌트만 사용해서 T2 먼저 접근하도록 합니다. BNL 힌트를 주지 않았지만, 해시 조인이 발생한 것을 알 수 있습니다.
(아래와 같이 조인 조건절을 일부러 변형해 해시 조인을 유도하는 것은 절대 좋은 방법이 아니죠.)
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T2, T1) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (TRIM(T1.CUS_ID) = T2.CUS_ID) -- > T1.CUS_ID를 일부러 TRIM 처리
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Table scan on <temporary> (actual time=0.002..0.024 rows=72 loops=1)
-> Aggregate using temporary table (actual time=280.777..280.815 rows=72 loops=1)
-> Inner hash join (trim(t1.CUS_ID) = t2.CUS_ID) (cost=372923.03 rows=3551580) (actual time=141.649..239.391 rows=19800 loops=1)
-> Table scan on T1 (cost=0.00 rows=90) (actual time=0.078..0.119 rows=90 loops=1)
-> Hash
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=17758.16 rows=39462) (actual time=0.243..48.248 rows=19800 loops=1)
지금까지 살펴본 바로는, 해시 조인을 사용하는 힌트인 BNL이 그렇게 잘 작동하는거 같지는 않습니다. 조인을 처리할 수 있는 막강한 PRIMARY 인덱스가 있다면, 아마도 해시 조인보다 NL 조인을 선호하는 것은 아닌가 생각이 듭니다.
BNL 힌트는 잘 작동하지 않지만, NO_BNL 힌트는 또 잘 먹는거 같드라고요.
아래와 같이 해시 조인이 자동으로 작동했던, JOIN_ORDER(T1, T2)를 적용한 후에 NO_BNL까지 힌트를 걸어주면 NL 방식으로 조인이 처리되어 매우 느린 것을 알 수 있습니다.
EXPLAIN ANALYZE
SELECT /*+ JOIN_ORDER(T1, T2) NO_BNL(T2) */
T1.CUS_ID
,MAX(T1.CUS_NM) CUS_NM
,SUM(T2.ORD_AMT) ORD_AMT
,COUNT(*) ORD_CNT
FROM MYTUNDB.M_CUS T1
INNER JOIN MYTUNDB.T_ORD_BIG T2
ON (T1.CUS_ID = T2.CUS_ID)
WHERE T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;
-> Group aggregate: max(t1.CUS_NM), sum(t2.ORD_AMT), count(0) (actual time=187.159..6557.740 rows=72 loops=1)
-> Nested loop inner join (cost=1598243.65 rows=355158) (actual time=16.554..6530.982 rows=19800 loops=1)
-> Index scan on T1 using PRIMARY (cost=9.25 rows=90) (actual time=0.060..0.328 rows=90 loops=1)
-> Filter: (t2.CUS_ID = t1.CUS_ID) (cost=13816.34 rows=3946) (actual time=24.775..72.503 rows=220 loops=90)
-> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d')))) (cost=13816.34 rows=39462) (actual time=0.290..61.939 rows=19800 loops=90)
본 글에서는 BNL을 이용해 해시 조인이 발생한 케이스는 나오지 않았으나, 인덱스의 구성, 쿼리 조건의 변경에 따라 BNL 힌트가 작동이 될 수도 있다고 생각합니다. 어쨋든, 8.0.2 부터는 BNL 힌트를 사용해 해시 조인을 유도할 수 있다는 점과, 힌트를 준다고 해도, 무조건 해시 조인이 발생하지 않는다는 점을 알고 있으면 될거 같습니다.
