또한 데이터베이스에 저장된 데이터를 활용하기 위해, 수 없이 수 많은 데이터를 읽어올려(Output) 조회하게 됩니다.
이처럼 데이터베이스는 데이터를 읽고, 쓰는 작업을 주로 합니다. 이 과정에서 IO 횟수(데이터를 읽거나 저장한 횟수)에 따라 SQL의 성능이 달라집니다.
오라클이나 PostgreSQL은 SQL 실행시 발생한 IO 수치를 수집하거나 측정하는 것이 비교적 수월합니다.
반면에 MySQL은 이와 같은 IO 수치 측정이 번거롭게 되어 있습니다.
또한, 글로벌하게 측정이 되므로 여러 명이 동시에 사용 중인 DB에서는 측정이 불가능합니다.
아마도 스토리지 엔진과 DB 엔진이 나누어져 있는 아키텍쳐 때문인건 아닌지 생각해 봅니다.
어쨋든, MySQL도 IO를 측정할 수 있는 방법이 있으니 참고하시기 바랍니다.
MySQL에는 SHOW STATUS로 innodb_buffer_pool_read_requests를 확인해 IO 수치를 확인할 수 있습니다.
SQL 실행 전 후로 해당 수치를 측정해 두 수치의 차이를 구하면, 해당 SQL의 IO 수치가 됩니다.
(이미 말했듯이, 현재 DB를 혼자 사용하고 있어야 정확한 수치가 됩니다.)
아래는 특정 일자의 T_ORD_BIG 데이터를 조회하는 SQL의 IO를 측정하는 과정입니다.
현재 T_ORD_BIG에는 PK외에 인덱스가 없습니다. 그러므로 131,561이라는 높은 수치의 IO가 발생되고 있습니다.
실제 실행 시간도 5.9초 정도 걸립니다.
SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 122952720
SELECT T1.CUS_ID ,COUNT(*) CNT
FROM mytundb.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;
SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 123084281
SELECT 123084281 - 122952720 FROM DUAL; -- 131561
이제 아래와 같이 인덱스를 만들어 봅니다.
CREATE INDEX T_ORD_BIG_X01 ON mytundb.T_ORD_BIG(ORD_DT);
인덱스를 만들었으므로 앞에서 테스트한 SQL은 빨라질 것입니다. 빨라진다는 것은 IO 수치가 줄어든다는 것과 같습니다.
다시 IO수치를 측정해보면 다음과 같습니다.
IO 수치가 55889로 이전의 절반으로 줄어든 것을 알 수 있습니다. 실행시간 또한 감소되었습니다.
SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 123220482
SELECT T1.CUS_ID ,COUNT(*) CNT
FROM mytundb.T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;
SHOW STATUS LIKE 'innodb_buffer_pool_read_requests'; -- 123276371
SELECT 123276371 - 123220482 FROM DUAL; -- 55889
스타벅스 앱을 키면 Quick Order에는 최근 주문 내역을 보여줍니다. 사용자에게 앱을 키자마자 최근주문 내역을 보여줘 조금 더 편리하게 앱을 사용할 수 있도록 한 것입니다. 다른 많은 시스템이나 플랫폼도 비슷합니다. 접속을 하면, 마지막 접속기록이나 최근 구매 내역을 보여줍니다. 이와 같은 기능을 처리하는 SQL의 성능 개선 과정을 간단히 살펴보겠습니다.
실습을 위해 Ord(주문), OrdDet(주문상세), 상품(Item) 테이블을 사용합니다. 해당 테이블은 StartUP SQL의 무료 스크립트를 통해 자신의 환경에도 생성할 수 있습니다. 참고: https://sweetquant.tistory.com/503
위 테이블들을 이용해 특정 회원의 최근 주문 상품 세 개를 가져오려고 합니다. 아래와 같이 SQL을 구현할 수 있습니다.
SELECT T2.ItemId
,T3.ItemNm
,T1.OrdDtm
,T1.OrdNo
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2 ON (T2.OrdNo = T1.OrdNo)
INNER JOIN startdb.Item T3 ON (T3.ItemId = T2.ItemId)
WHERE T1.MemberId = 'M0001'
ORDER BY T1.OrdDtm DESC
LIMIT 3;
- Time sec: 0.018636
* Rows read: 1263
* Buffer pages: 3797
SQL의 마지막에 수행 시간과 IO(Buffer pages) 수치를 첨부했습니다. 0.018초로 그렇게 느리다고 할 수는 없는 SQL입니다. 하지만 데이터 3건만 보여주는 상황에서 3,797이란 IO 수치는 약간 높은 편입니다. 수십만 명의 회원이 동시에 사용한다고 생각해보면, 약간씩 더 느려질 소지가 있습니다. 위 SQL이 현재 시스템에서 매우 많이 사용되는 핵심 SQL이라면 당연히 추가 성능 개선이 필요합니다. SQL의 성능 개선을 위해 가장 먼저 할일은 실행계획을 확인하는 것입니다. 위 SQL의 실제 실행계획은 아래와 같습니다.
1. -> Limit: 3 row(s) (actual time=162..162 rows=3 loops=1)
2. -> Nested loop inner join (actual time=162..162 rows=3 loops=1)
3. -> Nested loop inner join (actual time=162..162 rows=3 loops=1)
4. -> Sort: startdb.t1.OrdDtm DESC (actual time=162..162 rows=3 loops=1)
5. -> Index lookup on T1 using Ord_FK01 (MemberId='M0001') (actual time=18.5..161 rows=1257 loops=1)
6. -> Filter: (startdb.t2.ItemId is not null) (actual time=0.027..0.0288 rows=1 loops=3)
7. -> Index lookup on T2 using PRIMARY (OrdNo=startdb.t1.OrdNo) (actual time=0.0261..0.0277 rows=1 loops=3)
7. -> Single-row index lookup on T3 using PRIMARY (ItemId=startdb.t2.ItemId) (actual time=0.0104..0.0105 rows=1 loops=3)
실행계획을 확인해 보면 세 테이블이 모두 NL(Nested Loop) 방식으로 조인되고 있습니다. 성능 개선을 위해서는 이러한 조인 방식을 이해하는 것이 매우 중요합니다. 이 부분을 여기서 다루자면 길어지므로 나중에 기회가 되면 별도로 이야기하도록 하겠습니다. 실행계획이 익숙하지 않은 분들은, 실행계획에서 문제가 되는 부분을 찾기가 쉽지 않을 것입니다. 실행계획의 마지막 단계(1번 라인)의 actual time을 보면 162..162(시작시간ms..종료시간ms)입니다. 162ms에 이 SQL이 완료되었다는 것을 알 수 있습니다. 이제, 마지막 단계를 제외하고 각 단계별로 actual time을 살펴봅니다. 가장 많은 시간을 차지하는 단계를 한 번 찾아보는거죠. 5번 단계가 다른 단계들에 비해 많은 시간을 사용하고 있는 것을 알 수 있습니다. 5번 단계의 작업 내용은 아래와 같습니다. -> Index lookup on T1 using Ord_FK01 (MemberId='M0001') (actual time=18.5..161 rows=1257 loops=1)
T1(Ord) 테이블을 Ord_FK01 인덱스를 사용해 Lookup 처리하고 있습니다. Ord_FK01은 MemberId로 구성된 인덱스입니다. 실행계획에서 actual 항목의 rows를 보면 1257입니다. Ord_FK01을 이용해 추출한 데이터가 1257건이라는 뜻입니다. 우리가 필요한건 최근 조회 세 건인데, 1257건의 주문 데이터에 접근한 것입니다. 바로 이 부분에 비효율이 있다라고 할 수 있습니다.
우리는 성능을 개선하기 위해 아래와 같은 인덱스를 만들어 볼 수 있습니다. CREATE INDEX Ord_X01 ON startdb.Ord(MemberId, OrdDtm);
인덱스를 만든 후에 다시 같은 SQL을 실행해보고 실제 실행계획을 추출해보면 아래와 같습니다.
1. -> Limit: 3 row(s) (actual time=0.227..0.271 rows=3 loops=1)
2. -> Nested loop inner join (actual time=0.226..0.269 rows=3 loops=1)
3. -> Nested loop inner join (actual time=0.208..0.242 rows=3 loops=1)
4. -> Covering index lookup on T1 using Ord_X01 (MemberId='M0001') (reverse) (actual time=0.178..0.182 rows=3 loops=1)
5. -> Filter: (startdb.t2.ItemId is not null) (actual time=0.0155..0.0186 rows=1 loops=3)
6. -> Index lookup on T2 using PRIMARY (OrdNo=startdb.t1.OrdNo) (actual time=0.0149..0.0178 rows=1 loops=3)
7. -> Single-row index lookup on T3 using PRIMARY (ItemId=startdb.t2.ItemId) (actual time=0.00827..0.00837 rows=1 loops=3)
- Time sec: 0.005036
* Rows read: 9
* Buffer pages: 17
* Disk reads: 0
총 실행시간이 0.005초로 인덱스를 만들기 전인 0.018초에 비해 1/3 이상으로 감소했습니다. IO 수치인 Buffer pages도 3,797에서 17로 획기적으로 감소한 것을 알 수 있습니다. 실행계획의 4번 단계를 살펴보면 다음과 같습니다. -> Covering index lookup on T1 using Ord_X01 (MemberId='M0001') (reverse) (actual time=0.178..0.182 rows=3 loops=1)
새로 만든 인덱스를 Covering Index lookp으로 처리하고 있습니다. 또한 (reverse)라고 표시되어 있는데, 이는 인덱스 리프 데이터를 역순으로 접근하는 방식을 뜻합니다. 인덱스의 리프 데이터는 인덱스 키 값으로 정렬되어 있으므로 이처럼 역순으로 접근해 세 건만 읽어들이면, 최근 주문 세건을 추출해낼 수 있습니다. 4번 단계의 rows를 보면 3으로 줄어든 것을 알 수 있습니다.
최근 데이터 조회에 적절한 인덱스를 만들때는 WHERE 절의 조건을 인덱스의 앞쪽 컬럼으로 배치하고 ORDER BY에 있는 컬럼을 인덱스의 뒤쪽 컬럼에 배치합니다. 그러므로 위 SQL에서 WHERE 조건인 MemberId, ORDER BY 컬럼인 OrdDtm을 조합해 Ord_X01(MemberId, OrdDtm)으로 인덱스를 구성한 것입니다. (SQL에 따라 이와 같은 인덱스 구성이 불가능하고나 복잡한 경우도 많습니다.)
이처럼 인덱스를 만들어주는 것만으로 최근 주문을 조회하는 SQL 성능이 획기적으로 개선되었습니다. 그런데 중요한건, "인덱스를 만들자"가 아닙니다. 실행계획을 먼저 살펴보고, 성능에 문제가 되는 지점을 찾아내는 것입니다. 그리고 그에 맞게 개선안을 적용해야 합니다. 또한 지금 살펴본 SQL보다 훨씬 복잡해서 단순 인덱스 전략으로 성능 개선을 만들어 내지 못하는 경우도 많습니다. 그러므로 우리는 성능 개선을 위해 단순히 인덱스를 만들자가 아닌, 조인의 작동원리, 인덱스의 구조와 작동원리를 잘 알고 있어야 합니다.
인덱스를 마스터하기 위해서는 많은 시간과 노력이 필요합니다. 인덱스를 본격적으로 공부하기에 앞서 인덱스의 개념에 대해 간단히 알아볼 필요가 있습니다.
"데이터를 정렬해 라벨링하고, 라벨을 참고해 원하는 데이터를 빠르게 찾는다" 이것이 바로 인덱스의 개념입니다.
INDEX 개념잡기
DBMS: MySQL 8.0.35
SQL에 성능 문제를 해결하기 위해서 가장 먼저 할 일은 실행계획을 확인하는 것입니다.
실행계획을 통해 Table scan과 같은 테이블 전체를 검색하는 작업을 발견하면, 인덱스(INDEX)를 생성해 테이블 전체 검색이 아닌 인덱스를 이용한 데이터 검색으로 SQL이 처리되도록 유도할 수 있습니다.
그러나, 이러한 단순한 사실만 알고 인덱스를 적용해서는 SQL 성능 개선을 제대로 할 수 없습니다.
실제로 인덱스를 만들어도 성능 개선 효과가 없거나 오히려 역효과가 나는 경우도 있습니다.
인덱스를 통한 성능 개선 효과가 있는지 판단하기 위해서는 인덱스의 개념과 물리적인 구조도 이해하고 있어야 합니다.
[Tip] 느린 SQL의 성능 개선 방법에는 인덱스만 있는 것은 아닙니다. 인덱스는 다양한 성능 개선 방법을 익히기 위한 시작점일 뿐입니다.
우리는 가장 먼저 인덱스의 개념을 이해할 필요가 있습니다. 회사에 아래 그림 좌측과 같이 12개의 거래처가 있다고 가정해봅시다. 12개의 거래처 관련된 문서를 거래처 서류함에 보관하려고 합니다.
회사의 팀장이 ‘나엉망’ 직원에게 거래처 서류를 정리하라고 지시했습니다.
‘나엉망’ 직원은 빨리 정리하고 쉬기 위해서 무작위로 서류를 세 개씩 나누어서 서류함의 네 개 서랍에 나누어 넣었습니다. 무작위로 서류를 서랍에 넣었으므로 결과적으로 필요한 서류가 어느 서랍에 있는지는 알 수가 없게 되었습니다. 다시 말해, Airbnb 서류가 어느 서랍에 있을지는 알 수 없습니다. Airbnb 서류를 찾기 위해서는 서류함의 서랍을 하나씩 열어봐야 합니다. 운이 좋으면 첫 번째 서랍에서 찾을 수도 있고, 운이 나쁘면 네 번째 서랍에서 서류를 발견하게 될 것입니다. 다른 거래처의 서류도 마찬가지입니다. 그날의 운에 따라 서류를 빨리 찾거나 늦게 찾거나 하는 것이죠.
이러한 상황을 알게 팀장은 이번에는 ‘나깔끔’ 직원을 불러 서류를 다시 정리하라고 지시했습니다.
‘나깔끔’직원은 모든 서류를 꺼낸 후에 알파벳 순으로 정렬을 한 후에, 알파벳 순서대로 거래처를 세 개씩 나누고 서랍에 차례대로 넣었습니다. 그리고 서랍 앞에는 어떤 알파벳으로 시작하는 거래처 서류가 있는지 알아 볼 수 있도록 라벨링을 했습니다. 다음 그림과 같이 말이죠.
이와 같이 정리를 해 놓으니, 서랍을 한 번만 열면 원하는 서류를 찾을 수 있습니다. Airbnb는 (A)로 시작하므로 1번 서랍을 열면 바로 찾을 수 있습니다. Samsung 서류는 (N)과 (T) 사이에 있으므로 3번 서랍을 열만 바로 찾을 수 있습니다. 이처럼 데이터를 정렬하고 라벨링을 해서, 원하는 데이터를 빠르게 찾을 수 있도록 도움 주는 객체가 바로 인덱스입니다.
인덱스는 일반적으로 삼각형 모양으로 표현합니다. 서류함을 삼각형의 위쪽 꼭지점으로, 서랍 안에 들어가 있는 서류들을 삼각형의 밑면으로 표현할 수 있습니다. 아래 그림과 같이 말이죠.
인덱스는 실제 B Tree 자료 구조로 구현되어 있으며 B Tree에서의 데이터 검색을 효과적으로 설명하기 위해 이와 같은 이와 같은 삼각형 모양을 자주 사용하게 됩니다.
인덱스의 개념을 간단히 살펴봤습니다. 인덱스를 제대로 사용하기 위한 첫 단추를 잠근 것입니다. 인덱스를 마스터하기 위해서는 앞으로 인덱스의 구조도 알아야 하며, SQL에 따라 인덱스 탐색이 어떻게 되는지, 그리고 인덱스의 종류도 공부해야 합니다. 인덱스 개념만 공부한 채로 섣부르게 인덱스를 만들게 되면 큰 문제를 겪을 수 있습니다.
SQL 경력이 20년이 넘는 저 역시도 여전히 인덱스를 만드는 일은 항상 걱정이 앞서는 일이기도 합니다.
인덱스는 어떤 SQL의 성능에는 도움이 되기도 하지만, 어떤 SQL의 성능에는 악영향을 줄 수도 있기 때문입니다.
항상 인덱스 추가, 변경은 긴장되는 일이랍니다.
시간을 가지고 인덱스와 SQL의 성능에 대해 더 공부해보신후 인덱스 적용을 고민해보시기 바랍니다.
그리고 지난 글 마지막에 SQL의 가장 큰 문제가 어디인지 스스로 진단해보기를 부탁드렸습니다. 지난 글에서 설명한 SQL과 실행계획은 다음과 같습니다.
EXPLAIN ANALYZE
SELECT T1.CUS_ID ,COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;
1 -> Table scan on <temporary> (actual time=5992..5992 rows=9 loops=1)
2 -> Aggregate using temporary table (actual time=5992..5992 rows=9 loops=1)
3 -> Filter: (t1.ORD_DT = ('20170103')) (actual time=35.4..5985 rows=18000 loops=1)
4 -> Table scan on T1 (actual time=0.0889..5303 rows=6.09e+6 loops=1)
라인 번호 기준으로 4->3->2->1의 흐름으로 SQL이 처리되는데, 전체 실행 시간이 5.9초 정도입니다.
이 중에 4번과 3번 라인의 Table scan과 Filter가 대부분의 실행 시간을 차지하고 있습니다.
또한, 4번 단계에서 처리한 데이터 건수가 6.09e+6(6,090,000)이었으나 3번 단계에서 18,000건으로 매우 많이 줄어 든 것을 알 수 있습니다.
이러한 정보를 종합해 보면, 4번과 3번 단계를 처리하는 과정이 성능 문제의 중심이란 것을 알 수 있습니다.
어디가 아픈지는 알았으니 이제 치료를 해야 하는데, 치료를 하기 위해서는 기본적인 치료법을 알고 있어야 합니다.
아픈 SQL을 치료하는 방법은 매우 다양합니다.
그 중에 Table scan(TABLE FULL SCAN)이란 질병이 있는 SQL은 인덱스(INDEX)라는 처방이 필요합니다.
이 인덱스란 치료를 처방하기 위해서는 인덱스의 구조와 인덱스를 이용해 데이터를 찾는 과정에 대한 지식이 필요합니다.
이는 별도로 길게 다루어야 할 내용입니다. 그러므로 지금은 인덱스를 만들어 효과가 있는지 정도만 살펴보도록 하겠습니다.
SQL을 보면 WHERE 절에 ORD_DT에 대한 조건이 있습니다. 그리고 실행계획의 3번 라인을 살펴보면 Filter 조건으로 ORD_DT가 사용되고 있습니다. 이는 실행계획에서 Filter 부분이 SQL의 WHERE 절을 처리한 것이라는 뜻입니다. 인덱스는 기본적으로 Filter된 컬럼에 대해 구성합니다.
아래 SQL로 인덱스를 추가합니다.
CREATE INDEX T_ORD_BIG_X01 ON T_ORD_BIG(ORD_DT);
[Tip] 인덱스를 만드는 작업은 DB내 자원을 제법 사용합니다. 해당 테이블에 부하가 걸리기도 합니다. 실제 인덱스를 만들어본 경험이 없으신 분은 개발 DB에서 충분한 테스트를 해보기 바랍니다. 운영 DB에서 인덱스를 만들 때는 사용자가 가장 적은 시간을 이용하는 것을 권장합니다.
인덱스를 만들고, 이전 SQL을 다시 실행해 실제 실행계획을 추출해봅니다. 아래와 같습니다.
EXPLAIN ANALYZE
SELECT T1.CUS_ID ,COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;
1 -> Table scan on <temporary> (actual time=38.2..38.2 rows=9 loops=1)
2 -> Aggregate using temporary table (actual time=38.2..38.2 rows=9 loops=1)
3 -> Index lookup on T1 using T_ORD_BIG_X01 (ORD_DT= '20170103') (actual time=0.521..26.1 rows=18000 loops=1)
인덱스를 만들어준 것만으로 기존 5.9초였던 실행 시간이 0.038초로 개선되었습니다.
실행계획을 살펴보면 네 개의 단계였던 실행계획이 세 개의 단계로 줄어든 것을 알 수 있습니다.
주의 깊게 볼 것은 3번 라인의 Index Lookup입니다. 이전 실행계획에 있던 Table scan과 Filter가 사라지고 Index Lookup이 나타났습니다. Index Lookup은 WHERE 절의 동등(=) 조건에 해당하는 데이터를 인덱스를 사용해 찾아낸 경우에 나타나는 작업입니다.
Index Lookup의 actual 항목을 살펴보면 처리한 시간이 0.521..26.1ms로 매우 작습니다. 또한 인덱스로 처리한 건수(rows)가 18,000 건으로 SQL 결과를 얻어내는데 필요한 만큼의 데이터만 읽은 것입니다.
다시 말해, 불필요한 데이터에 대한 접근 없이 SQL이 처리된 것입니다.
이처럼 인덱스가 없는 SQL은 인덱스를 만드는 것만으로 성능 개선 효과가 나타날 수 있습니다.
물론 무조건 인덱스를 만든다고 성능 개선이 되는 것은 아닙니다. 경우에 따라서는 생성한 인덱스가 아무 효과가 없을 수도 있으며, 오히려 역효과가 날 수도 있습니다.
인덱스는 SQL 성능 개선을 위한 많은 방법 중에 하나입니다.
앞에서도 설명 드렸듯이 SQL의 성능 문제를 해결하기 위해 가장 먼저 할 일은 실행계획을 살펴보는 것입니다.
실행계획으로 현재 아픈 곳을 찾아내 진단하고, 현재 SQL과 테이블 구조(현재 인덱스, 컬럼 구성, 데이터 양, 테이블 간의 관계 등)를 고민해 그에 맞는 적절한 진료를 해야 합니다.
1. 강의 소개 2. 실습환경 구성(MySQL) 3. 실습환경 구성(Maria) 5. 성능 기본 지식 6. 실행계획 7. INDEX 1 8. INDEX 2 9. INDEX와 성능 1 10. INDEX와 성능 2 11. INDEX와 성능 3 12. ORDER BY 13. JOIN의 내부 처리 방식 14. HINT 15. JOIN과 성능 16. 맛보기 튜닝 17. Paging SQL 18. INDEX 설계 19. 성능 수집 20. 파티션 21. Transaction 22. SQL GUIDE
상세 목차
1. 강의 소개 1. ERD: MYTUNDB 논리(Logical) 1. ERD: MYTUNDB 물리(Physical) 1. 강사 소개 1. DBian 도서 소개 1. StartUP Tuning 1. 차례 2. 실습환경 구성(MySQL) 2. 완성된 실습 환경 2. MySQL 다운로드 2. MySQL 다운로드: Windows MySQL 8.0.35 2. MySQL 다운로드: MAC MySQL 8.0.35 다운로드 2. 기존 MySQL Upgrade / Remove 선택 2. Choosing a setup Type 2. Check Requirements 2. Installation 2. Product Configuration / Type and Networking 2. Authentication Method / Accounts and Roles 2. Windows Service / Server File Permissions 2. Apply Configuration 2. MySQL Router Configuration / Connect To Server 2. Apply Configuration 2. Installation Complete 2. Workbench 접속 #1 2. Workbench 접속 #2 2. Workbench 접속 #3 2. Hello SQL 2. Workbench 필요한 환경 설정 #1 2. Workbench 필요한 환경 설정 #2 2. Dbeaver 2. SQL Tool 3. 실습환경 구성(Maria) 3. 다운로드 3. 완성된 실습 환경 3. MariaDB 설치 3. HeidiSQL 3. Dbeaver 3. Hello SQL 4. 실습 데이터베이스 생성 4. CREATE DATABASE 4. 기본 데이터베이스 설정 4. 캐릭터셋 확인 4. 테이블 및 데이터 생성 5. 성능 기본 지식 5. SQL 튜닝이란? 5. SQL 성능 개선의 주요 요소 5. 비효율 SQL이란? 5. SQL 처리 과정 5. Execution Plan과 Optimizer #1 5. Execution Plan과 Optimizer #2 5. Execution Plan과 Optimizer #3 5. Statistics #1 5. Statistics #2 5. Statistics #3 5. Storage Engine 5. Oracle VS. MySQL 5. IO 5. 물리적 IO와 논리적 IO #1 5. 물리적 IO와 논리적 IO #2 5. Buffer pool size 변경하기 5. SQL 성능 개선 프로세스 5. 지속적인 SQL 성능 개선 필요성 6. 실행계획 6. 인덱스 초기화 6. EXPLAIN 6. EXPLAIN 이해하기 6. EXPLAIN 이해하기 – select_type 6. EXPLAIN 이해하기 – 실행계획의 흐름 해석하기 6. EXPLAIN 이해하기 – type(데이터 접근방식) 6. EXPLAIN – Extra(옵티마이져 동작방식) 이해하기 6. EXPLAIN FORMAT 6. EXPLAIN FORMAT = TREE (MySQL) 6. 실행한 SQL의 실제 접근 데이터 건수 측정 6. 실행한 SQL의 요청 논리적IO 측정 6. ANALYZE SQL(MariaDB) 6. EXPLAIN ANALYZE #1 (MySQL) 6. EXPLAIN ANALYZE #2 (MySQL) 6. PROFILING 6. 다른 세션의 실행계획 확인 (MySQL) BOOSTER QUIZ 6-1 7. INDEX 1 7. INDEX란? 우선 읽어보자! 7. 실행계획의 비효율을 찾아 INDEX로 해결하기 #1 7. 실행계획의 비효율을 찾아 INDEX로 해결하기 #2 7. 실행계획의 비효율을 찾아 INDEX로 해결하기 #3 7. INDEX의 생성과 삭제 7. INDEX 개념 잡기 #1 7. INDEX 개념 잡기 #2 7. B-Tree INDEX #1 7. B-Tree INDEX #2 7. 테이블 구조 7. Heap 구조와 Table Scan(TABLE FULL SCAN) 7. Heap과 INDEX #1 7. Heap과 INDEX #2 7. Heap과 INDEX #3 7. Heap과 INDEX #4 7. Heap과 INDEX #5 7. 인덱스는 삼각형으로 7. 인덱스 탐색 과정의 시각화 #1 7. 인덱스 탐색 과정의 시각화 #2: CUS_ID 인덱스를 사용한 데이터 검색 7. 인덱스 직접 그려보기 7. CLUSTERED (INDEX) #1 7. CLUSTERED (INDEX) #2 7. CLUSTERED (INDEX) #3 7. HEAP VS. CLUSTERED #1 7. HEAP VS. CLUSTERED #2 8. INDEX 2 8. 데이터 접근 방법 8. 인덱스를 이용한 데이터 접근 #1 8. 인덱스를 이용한 데이터 접근 #2: 인덱스 접근 시각화 8. 인덱스를 이용한 데이터 접근 #3: INDEX LOOKUP 8. 인덱스를 이용한 데이터 접근 #4: INDEX RANGE SCAN 8. 인덱스를 이용한 데이터 접근 #5: COVERING INDEX (LOOKUP/RANGE SCAN) 8. 인덱스를 이용한 데이터 접근 #6: INDEX SCAN(INDEX FULL SCAN) 8. 인덱스를 이용한 데이터 접근 #7: 정리 8. 테이블(클러스터드) 접근 #1 8. 테이블(클러스터드) 접근 #2: 검색양에 따른 성능 변화 8. 테이블(클러스터드) 접근 #3: 테이블 접근 횟수에 따른 성능 변화 8. 테이블(클러스터드) 접근 #4: Covering Index 8. 테이블(클러스터드) 접근 #5: 클러스터드 인덱스를 이용한 검색 8. INDEX 확인하기 #1 8. INDEX 확인하기 #2 8. CLUSTERD INDEX에 대한 고민 8. InnoDB가 제공하는 INDEX 구조 8. INDEX 분류 8. 조건에 따른 인덱스 선택 BOOSTER QUIZ 8-1 BOOSTER QUIZ 8-2 BOOSTER QUIZ 8-3 BOOSTER QUIZ 8-4 BOOSTER QUIZ 8-5 9. INDEX와 성능 1 9. 어느 컬럼에 인덱스를 만들 것인가? #1 9. 어느 컬럼에 인덱스를 만들 것인가? #2 9. 어느 컬럼에 인덱스를 만들 것인가? #3 9. 어느 컬럼에 인덱스를 만들 것인가? #4: RNO를 제외한 조건은 어디서 처리되는가? BOOSTER QUIZ 9-1 BOOSTER QUIZ 9-2 BOOSTER QUIZ 9-3 9. 공식만 외우지 말자 #1 9. 공식만 외우지 말자 #2 10. INDEX와 성능 2 10. 단일 인덱스 VS. 복합 인덱스 #1 10. 단일 인덱스 VS. 복합 인덱스 #2 10. 단일 인덱스 VS. 복합 인덱스 #3 10. 단일 인덱스 VS. 복합 인덱스 #4 10. 단일 인덱스 VS. 복합 인덱스 #5 10. 단일 인덱스 VS. 복합 인덱스 #6: 오라클의 실행계획 BOOSTER QUIZ 10-1 10. 복합 인덱스의 컬럼 순서 10. 복합 인덱스 컬럼 순서에 따른 성능 차이 #1 10. 복합 인덱스 컬럼 순서에 따른 성능 차이 #2 10. 복합 인덱스 컬럼 순서에 따른 성능 차이 #3 10. 복합 인덱스 컬럼 순서에 따른 성능 차이 #4 BOOSTER QUIZ 10-2 BOOSTER QUIZ 10-3 BOOSTER QUIZ 10-4 10. 복합 인덱스의 선두 조건 누락 10. 복합 인덱스 컬럼 선정과 순서 #1 10. 복합 인덱스 컬럼 선정과 순서 #2 10. 복합 인덱스 컬럼 선정과 순서 #4 BOOSTER QUIZ 10-5 BOOSTER QUIZ 10-6 BOOSTER QUIZ 10-7 BOOSTER QUIZ 10-8 10. 복합 인덱스 정리 11. INDEX와 성능 3 11. 인덱스를 제대로 활용할 수 없는 SQL #1 11. 인덱스를 제대로 활용할 수 없는 SQL #2 BOOSTER QUIZ 11-1 BOOSTER QUIZ 11-2 11. Index Covering(Covered Index) #1 11. Index Covering(Covered Index) #2 BOOSTER QUIZ 11-3 11. 복합 인덱스의 선두 조건 누락 #1 11. 복합 인덱스의 선두 조건 누락 #2 11. 복합 인덱스의 선두 조건 누락 #3 11. 복합 인덱스의 선두 조건 누락 #4 – Index Skip Scan BOOSTER QUIZ 11-4 11. 언제까지 인덱스를 탈 것만 같아? #1 11. 언제까지 인덱스를 탈 것만 같아? #2 11. 언제까지 인덱스를 탈 것만 같아? #3 11. 너무 많은 인덱스의 위험성 11. 클러스터드 인덱스의 크기 11. INDEX란? 한 번 더 읽어보자! BOOSTER QUIZ 11-5 12. ORDER BY 12. 비효율 찾기 12. Using filesort 12. Using filesort 제거하기 12. sort buffer size 12. INDEX RANGE SCAN - ASC 12. INDEX RANGE SCAN - DESC 12. INDEX RANGE SCAN – 정렬과 무관하게 인덱스를 사용하면? 12. ORDER BY LIMIT n 12. ORDER BY SQL을 위한 인덱스 전략 #1 12. ORDER BY SQL을 위한 인덱스 전략 #2 12. ORDER BY SQL을 위한 인덱스 전략 #3 BOOSTER QUIZ 12-1 BOOSTER QUIZ 12-2 13. JOIN의 내부 처리 방식 13. JOIN의 내부 처리 방식 13. NL JOIN 이해하기 #1 13. NL JOIN 이해하기 #2 13. HASH JOIN 이해하기 #1 13. HASH JOIN 이해하기 #2 13. HASH JOIN 이해하기 #1 (MariaDB) 13. HASH JOIN 이해하기 #2 (MariaDB) 13. HASH JOIN 이해하기 #3 (MariaDB) 13. HASH JOIN 이해하기 #4 (MariaDB) 13. HASH JOIN 이해하기 #1 (MySQL) 13. HASH JOIN 이해하기 #2 (MySQL) BOOSTER QUIZ 13-1 BOOSTER QUIZ 13-2 14. HINT 14. Optimizer Hints 14. 인덱스 힌트 14. JOIN_ORDER #1 14. JOIN_ORDER #2 14. JOIN_ORDER 와 JOIN 방법 제어 15. JOIN과 성능 15. NL JOIN 튜닝 – 선행접근과 후행접근 15. NL JOIN 튜닝 – 후행접근 조인 컬럼의 인덱스 유무 #1 15. NL JOIN 튜닝 – 후행접근 조인 컬럼의 인덱스 유무 #2 15. NL JOIN 튜닝 – 후행접근 조인 컬럼의 인덱스 유무 #4 15. NL JOIN 튜닝 – 후행접근 조인 컬럼의 인덱스 유무 #4 15. NL JOIN 튜닝 – 후행접근 조인 컬럼의 인덱스 유무 #5 15. NL JOIN 튜닝 – 후행접근 조인 컬럼의 인덱스 유무 #6 15. NL JOIN 튜닝 – 후행접근 조인 컬럼의 인덱스 유무 #7 15. NL JOIN 튜닝 – join_cache_level (MariaDB) BOOSTER QUIZ 15-1 BOOSTER QUIZ 15-2 15. NL JOIN 튜닝 – 조인 순서 #1 15. NL JOIN 튜닝 – 조인 순서 #2 15. NL JOIN 튜닝 – 조인 순서 #3 15. NL JOIN 튜닝 – 조인 순서 #4 15. NL JOIN 튜닝 – 조인 순서 #5 BOOSTER QUIZ 15-3 15. NL JOIN 튜닝 – 여러 테이블의 조인 #1 15. NL JOIN 튜닝 – 여러 테이블의 조인 #2 15. NL JOIN 튜닝 – 여러 테이블의 조인 #3 15. NL JOIN 튜닝 – 여러 테이블의 조인 #4 15. HASH JOIN 튜닝 – Build Input 선택 #1 (MariaDB) 15. HASH JOIN 튜닝 – Build Input 선택 #2 (MariaDB) 15. HASH JOIN 튜닝 – Build Input 선택 #1 (MySQL) 15. HASH JOIN 튜닝 – Build Input 선택 #2 (MySQL) 15. HASH JOIN 튜닝 – 인덱스 BOOSTER QUIZ 15-4 BOOSTER QUIZ 15-5 15. JOIN과 성능 Summary
16. 맛보기 튜닝 16. 특정 고객의 마지막 주문 일자 구하기 #1 16. 특정 고객의 마지막 주문 일자 구하기 #2 16. 특정 고객의 마지막 주문 일자 구하기 #3 BOOSTER QUIZ 16-1 16. 여러 고객의 마지막 주문 일자: 서브쿼리 #1 16. 여러 고객의 마지막 주문 일자: 서브쿼리 #2 16. 불필요한 카운트 16. Materialize #1 16. Materialize #2: SQL 변경과 NO_MERGE 16. Materialize #3: SQL 변경 BOOSTER QUIZ 16-2 16. 무리한 스칼라 서브쿼리 BOOSTER QUIZ 16-3 16. WHERE 절 서브쿼리의 순환조건 #1 16. WHERE 절 서브쿼리의 순환조건 #2 16. WHERE 절 서브쿼리의 순환조건 #3 BOOSTER QUIZ 16-4 16. WHERE 절 서브쿼리의 순환조건: 마지막 데이터 & 빅 테이블 #1 16. WHERE 절 서브쿼리의 순환조건: 마지막 데이터 & 빅 테이블 #2 BOOSTER QUIZ 16-5 BOOSTER QUIZ 16-6 16. QUERY TRANSFORMATION 16. 조건 공급 #1 16. 조건 공급 #2 16. 조건 공급 #3 16. 조건 공급 #4 16. 적절하지 않은 조건 공급 BOOSTER QUIZ 16-7 BOOSTER QUIZ 16-8 16. MySQL의 강력한 무기 Using index for group by #1 16. MySQL의 강력한 무기 Using index for group by #2 16. MySQL의 강력한 무기 Using index for group by #3 16. MySQL의 강력한 무기 Using index for group by #4 16. MySQL의 강력한 무기 Using index for group by #5 BOOSTER QUIZ 16-9 BOOSTER QUIZ 16-10 BOOSTER QUIZ 16-11 16. 날짜 테이블 #1 16. 날짜 테이블 #2 16. 날짜 테이블 #3 BOOSTER QUIZ 16-12 16. 값의 분포도를 생각하자 #1 16. 값의 분포도를 고려 #2 16. 조인을 추가한 복합 인덱스 활용 #1: 날짜 조건 누락(개선 전) 16. 조인을 추가한 복합 인덱스 활용 #2 : 날짜 조건 누락(개선 후) 16. 조인을 추가한 복합 인덱스 활용 #1: 회원ID 누락(개선 전) 16. 조인을 추가한 복합 인덱스 활용 #2: 회원ID 누락(개선 후) 17. Paging SQL 17. 페이징의 종류 17. WAS 페이징 17. DB 페이징 #1 17. DB 페이징 #2 17. DB-INDEX 페이징 #1 17. DB-INDEX 페이징 #2 17. DB-INDEX 페이징 #3 17. DB-INDEX 페이징 #4 17. 조인 SQL의 DB-INDEX 페이징 #1 17. 조인 SQL의 DB-INDEX 페이징 #2 17. DB-INDEX 페이징 유지의 어려움 BOOSTER QUIZ 17-1 BOOSTER QUIZ 17-1 18. INDEX 설계 18. INDEX 설계 18. INDEX 설계 과정 BOOSTER QUIZ 18-1 19. 성능 수집 19. SLOW Query 설정 19. Performance Schema 19. 성능 관련 주요 VIEW 20. 파티션 20. 파티션 개념 #1 20. 파티션 개념 #2 20. 파티션 개념 #3 20. 파티션 개념 #3 20. 파티션 만들기 20. 파티션 성능 #1 20. 파티션 성능 #2 20. 파티션 성능 #3 21. Transaction 21. Transaction 21. ACID 21. Isolation level 21. MySQL과 MariaDB의 기본 Isolation level 21. Isolation level 변경하기 21. AUTO COMMIT 21. 테스트 테이블 생성 21. WAIT 테스트 #1 21. WAIT 테스트 #2 21. WAIT 테스트 #3 21. 출금 테스트 #1 21. 출금 테스트 #2 21. 출금 테스트 #3 21. 출금 테스트 #4 21. Manual Commit 과 MetadataLock 21. Isolation Level에 따른 테스트 #1 21. Isolation Level에 따른 테스트 #2 22. SQL GUIDE 22. SQL GUIDE의 목적 22. 필수 SQL GUIDE - 01 22. 필수 SQL GUIDE - 02 22. 필수 SQL GUIDE - 03 22. 필수 SQL GUIDE - 04 22. 필수 SQL GUIDE - 05 22. 필수 SQL GUIDE - 06 22. 필수 SQL GUIDE - 07 22. 필수 SQL GUIDE - 08 22. 필수 SQL GUIDE - 09 22. 필수 SQL GUIDE - 10 22. 필수 SQL GUIDE - 11 22. 필수 SQL GUIDE - 12 22. 필수 SQL GUIDE - 13 22. 권장 SQL GUIDE - 01 22. 권장 SQL GUIDE - 02 22. 권장 SQL GUIDE - 03 22. 권장 SQL GUIDE - 04 22. 권장 SQL GUIDE - 05 22. 권장 SQL GUIDE - 06 22. 권장 SQL GUIDE - 07 22. 권장 SQL GUIDE - 08 22. 권장 SQL GUIDE - 09 22. 권장 SQL GUIDE - 10 22. 권장 SQL GUIDE - 11
본 문서는 StartUP Tuning(for MySQL) 강의의 일부를 책의 서술 형태로 정리한 문서입니다. 가장 아래에 PDF 버젼으로도 첨부드리니 필요하신 분은 다운 받아가시기 바랍니다.
SQL의 성능에 문제가 있을 때 우리가 가장 먼저 할 일은 실행계획을 살펴보는 것입니다. MySQL은 8버전부터 EXPLAIN ANALYZE 기능을 지원합니다. EXPLAIN ANALYZE는 “실제 실행된” SQL의 실행계획을 Tree 형태로 보여줍니다. 실제 실행계획은 SQL 성능 개선할 때 매우 유용한 기능입니다. 아래 SQL은 T_ORD_BIG 테이블에서 특정 일자의 데이터를 조회해 고객ID(CUS_ID)별 주문 건수를 구하는 SQL입니다. EXPLAIN ANALYZE를 사용했으므로 SQL의 실행 결과가 아닌 SQL을 처리한 과정에 해당하는 실행계획을 결과로 얻을 수 있습니다.
EXPLAIN ANALYZE
SELECT T1.CUS_ID ,COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;
위 SQL을 실행하면 약 5.9초 정도의 시간이 걸리며, 아래와 같은 실제 실행계획이 출력됩니다. (지면상 실행계획의 일부 내용은 생략했습니다.)
Tree 형태의 실행계획은 일반적으로 가장 최하위 자식(Child) 단계부터 부모(Parent) 단계로 가면서 처리가 됩니다. 그러므로 위 실행계획의 흐름은 4->3->2->1 순서가 됩니다.
[Tip] 실행계획은 SQL의 내부적인 처리 흐름을 보여줄 뿐, 실제 DB 내부적인 처리 순서를 완벽하게 표현해주지는 않습니다. 4->3->2->1이란 순서가 4가 완료된 후 3이 실행되고, 3이 완료되면 2가 실행된다고 말하기는 어렵습니다. 4가 완료된 후 3이 처리되었을 수도 있으며, 4가 처리되면서 3이 동시에 처리될 수도 있습니다. 실행계획을 통해 내부적인 처리 순서를 완벽히 파악하기 보다는 전반적인 흐름을 이해하는 것에 초점을 맞추는 것이 좋습니다.
실행 계획의 처리 흐름대로 따라가면서 각 단계를 하나씩 살펴보도록 하겠습니다
4번 라인: Table scan on T1
가장 첫 번째 단계인 4번 라인을 보면, Table scan이란 작업이 실행되고 있습니다.
Table scan이란 테이블 전체를 검색하는 것을 뜻합니다. 테이블에 1억 건이 있다면 1억 건 전부를, 10억 건이 있다면 10억 건 전부를 검색했음을 뜻합니다.
Table scan 뒤에는 대상 테이블의 별칭이 적혀 있습니다.
현재 실행계획에는 Table scan on T1 이라고 표시되어 있습니다. 실행한 SQL의 FROM 절에서 T_ORD_BIG에 T1이란 별칭을 사용했습니다. 그러므로 Table scan의 대상이 T_ORD_BIG임을 알 수 있습니다.
감사하게도 실제 실행계획에는 해당 단계별로 수행된 시간과 처리한 건수의 정보를 actual 항목에 표시해 줍니다. 이와 같은 실제 처리된 정보는 SQL 튜닝에 매우 유용합니다. 4번 라인의 Table scan의 actual 정보는 다음과 같습니다.
Table scan on T1 (actual time=0.0889..5303 rows=6.09e+6 loops=1)
actual의 time 부분을 살펴보면 0.0859..5303로 표시되어 있습니다.
이는 해당 단계가 0.0859ms에 시작해 5303ms에 종료되었음을 뜻합니다. 5303ms는 약 5.3초입니다. 위 SQL은 총 실행 시간이 5.9초 정도가 걸립니다. 그 중에 4번 라인의 Table scan이 5.3초로 대부분을 차지하고 있는 상황입니다.
actual 항목 중에 rows 부분도 주의 깊게 살펴봐야 합니다. rows는 해당 단계가 끝났을 때 추출된 실제 데이터 건수입니다.
여기에는 6.09e+6이라고 표시되어 있습니다. 이는 과학적 표기법(또는 지수 표기법) 형태의 수치로서 구글이나 GPT를 통해 일반적인 숫자로 환산해보면 6,090,000 입니다. 다시 말해 Table scan 단계에서 추출된 데이터는 6,090,000 건입니다.
이 수치는 정확히 T_ORD_BIG의 현재 데이터 건 수와 일치합니다. Table Scan에서 테이블 전체 데이터를 읽었다는 것을 알 수 있으며, 이 과정이 5.3초 정도가 걸렸음을 알 수 있습니다.
3번 라인: Filter
Table scan을 거쳐 다음 실행되는 단계는 3번 라인의 Filter입니다. Filter는 특정 조건을 사용해 데이터를 걸러내는 처리를 하는 작업입니다.
MySQL의 Filter 단계에는 Filter로 사용된 조건이 같이 표시됩니다. 3번 라인의 실행계획을 통해 ORD_DT 조건에 대해 Filter가 처리된 것을 알 수 있습니다.
actual 항목을 보면 time 부분이 35.4..5985입니다. 35.4ms에 시작해 5985ms에 해당 단계가 완료되었습니다. 처리되는 시간대가 4번 단계의 Table scan과 겹치는 것으로 보아 3번과 4번이 동시에 처리되는 작업임을 유추할 수 있습니다. 실제로도 테이블의 데이터를 검색하면서 Filter 작업이 동시에 이루어집니다. 다만, MySQL의 Tree 실행계획에서는 별도 단계로 표시해준 것입니다. 다른 DBMS의 경우는 3번과 4번 단계를 하나의 단계로 표현해주기도 합니다.
3번 단계의 rows를 보면 18,000입니다. 4번 단계의 rows는 6,090,000이었습니다. 4번->3번 흐름으로 SQL이 처리되므로 6,090,000 건의 데이터에 접근해 결과적으로 18,000 건만 남았다고 해석할 수 있습니다. 이를 역산해보면 6,072,000 건((6,090,000-18,000)의 데이터가 4번에서 3번 단계를 거치면서 버려진 것입니다. 버려졌다는 것은 SQL 결과를 만드는데 불필요하다는 뜻입니다.
그러므로 이러한 불필요한 접근을 줄일 수 있다면 SQL 성능이 개선될 수 있습니다. 이는 다음 번에 이야기하기로 하고 계속해서 실행계획을 읽어 나가도록 하겠습니다.
2번 라인: Aggregate
2번 라인의 Aggregate는 데이터를 집계 처리하는 작업입니다.
Aggregate using temporary table (actual time=5992..5992 rows=9 loops=1)
현재 우리가 실행한 SQL에는 “GROUP BY T1.CUS_ID”가 포함되어 있습니다. 그러므로 DB 내부적으로 Aggregate 작업을 거쳐 집계된(GROUP BY) 결과를 만들어냅니다. actual 항목의 time을 보면 5992..5992로 5.9초에 시작해 5.9초에 끝난 것을 알 수 있습니다. 시작과 종료 시간이 0.000초로 매우 빨리 처리된 것입니다.
Aggregate를 거쳐 만들어진 결과 건수는 9입니다. actual의 rows를 보면 알 수 있습니다. Aggregate의 결과는 9건이지만, Aggregate를 수행한 대상 건수는 몇 건일까요? 바로 이전 실행 단계인 3번 라인의 Filter의 rows에 그 정답이 있습니다. 3번 라인의 Filter의 rows는 18,000 이었습니다. 그러므로 18,000 건의 데이터가 Aggregate 작업으로 공급되었으며, 18,000 건의 데이터를 집계해 9 건의 결과를 만든 것입니다.
이처럼 rows 항목은 이전 단계와 비교해 보는 것이 좋습니다. 각 실행 단계를 거치면서 데이터 양이 어떻게 변하는지 추적할 수 있으며, 이를 통해 과도한 데이터 흐름이 있는 단계를 찾아낼 수 있습니다.
1번 라인: Table scan on <temporary>
1번 라인의 처리 내용을 살펴보면 4번 라인과 마찬가지로 Table scan을 하고 있습니다.
Table scan on <temporary> (actual time=5992..5992 rows=9 loops=1)
다만 1번 라인의 Table scan 대상은 <temporary>라는 임시 테이블입니다. 이 임시 테이블은 2번 라인의 Aggregate를 거쳐 내부적으로 만들어진 테이블입니다. Aggregate 단계에서 9건의 데이터가 만들어졌으므로 <temporary>에는 당연히 9 건의 데이터만 담겨 있습니다. 9건의 데이터를 Table scan하는 작업은 매우 빠릅니다.
사실 실행 계획의 가장 마지막 단계는 SQL의 최종 결과를 단순 출력하는 단계로 대부분의 경우 주의 깊게 볼 필요는 없습니다.
지금까지의 내용을 기억하면서 아래 SQL과 실행계획을 다시 한번 꼼꼼하게 살펴보시기 바랍니다. 어느 부분이 현재 SQL의 가장 큰 문제인지 진단해볼 수 있을까요? 해결법은 찾지 못하더라도, 어느 부분이 가장 문제가 있다고 말할 수는 있어야 합니다.
EXPLAIN ANALYZE
SELECT T1.CUS_ID ,COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_DT = STR_TO_DATE('20170103','%Y%m%d')
GROUP BY T1.CUS_ID;
이번 StartUP Tuning(MySQL을 활용한 SQL 튜닝 입문) 교재에 실릴 Booster Quiz입니다.
교육 앞 부분의 문제인만큼, 인덱스 구조만 이해하면 누구라도 풀수 있는 수준의 문제입니다. 교재에 직접 손으로 그려보면서, "아~ 인덱스가 이렇게 작동하는구나"를 느낄 수 있는 간단한 문제입니다. ("입문" 교육이라고 말씀을 드리긴 하지만, 교육 중반 정도 넘어가면 어려운 문제들이 나옵니다.)
오늘은 StartUP Tuning 강의 중 일부인 "언제까지 인덱스를 탈 것만 같아?" 부분을 소개해봅니다.
MySQL로 데이터를 조회할 때 언제까지 인덱스를 사용하는지, 언제나 인덱스가 좋은 것인지에 대한 내용입니다.
SQL의 성능을 위해서는 기본적으로 세 개의 요소가 필요합니다. StartUP Tuning에서는 아래와 같이 세 개의 요소를 정의합니다.
INDEX
비효율 SQL 변경(제거)
힌트 사용
이 외에도 SQL 성능을 위해 다양한 요소가 필요하겠지만 기본적으로 가장 신경써야 할 부분은 위의 세 가지입니다.
SQL 성능을 위해 인덱스를 잘 구성해야 합니다. 기본적인 인덱스 구성 없이 SQL 성능을 끌어 올리는 것은 불가능합니다.
그러나 인덱스가 잘 구성되어 있어도 SQL을 잘 작성하지 않는다면 아무 필요가 없습니다.
(StartUP Tuning 강의에서는 인덱스를 하루에 걸쳐 설명하지만, 항상 SQL의 중요성을 강조합니다.)
SQL이 실행되면 MySQL의 옵티마이져는 상황에 따라서 인덱스로 처리할지 테이블 FULL SCAN으로 처리할지 선택하게 됩니다. 이는 인덱스를 사용하는 것이 항상 성능에 유리하지는 않다는 뜻이기도 합니다.
아래와 같은 SQL의 조건을 변경해가면서 실행하면, 어느 지점부터 인덱스가 아닌 FULL SCAN을 선택하는지 간단히 살펴볼 수 있습니다. T_ORD_BIG에는 약 6백만건의 데이터가 존재하며 ORD_YMD 컬럼에 싱글 컬럼 인덱스가 존재합니다. SELECT 절에 일부러 인덱스에 없는 PAY_DT 컬럼을 사용해 인덱스를 사용한 후 테이블 접근이 발생하도록 유도했습니다.
SELECT MAX(T1.PAY_DT) MAX_PAY_DT, COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD >= '20170104'
AND T1.ORD_YMD < '20170110';
조회 조건을 변경해가면서 테스트를 해보면 아래와 같은 결과를 얻을 수 있습니다. 조회 건수가 450,000 건 정도가 되자 인덱스가 아닌 Table Scan(TABLE FULL SCAN)을 선택했습니다. (이는 인덱스로 데이터를 탐색 후 테이블 접근이 발생하는 것이 전제된 경우입니다. 만약에 테이블 접근 없이 인덱스만 사용한다면 다른 결과가 나올 것입니다.) 실행시간 역시 342,000건 까지는 1.75초 정도였으나 450,000건인 Table Scan부터는 7.05 초로 매우 느려진 것을 알 수 있습니다.
항상 옵티마이져가 최선의 선택을 하는 것은 아닙니다. 최선의 선택을 위해 주어진 환경에서 최대한 노력하지만, 최선의 선택이 아닐 수도 있습니다. 실제로 현재 환경에서 450,000건 정도는 Table Scan보다 인덱스를 활용한 처리가 더 빠릅니다. 위 결과를 시각화 해보면 다음과 같습니다. Table Scan(FULL SCAN)에서 Buffers(IO) 수치는 꺽이지만 실행 시간은 오히려 7초대까지 올라간 것을 알 수 있습니다.
여기서 주의할 점이 있습니다. 450,000이 Table Scan을 선택하는 일반화된 숫자가 절대 아니라는 점입니다. 이러한 숫자는 서버와 디스크 성능, 서버 설정, 테이블의 크기(로우x컬럼), 인덱스 구성, 통계, SQL문 등에 의해 다른 결과가 나올 수 있습니다. 제 개인 PC에 설치된 MySQL에서 이와 같은 수치가 나온 것이므로 각자 환경에서 큰 테이블을 기준으로 테스트 해보시기 바랍니다.
추가로 하나의 테스트를 더 해 봅니다. 조회 건수를 변경해가면서 INDEX 또는 NO_INDEX 힌트를 적용해 성능 비교를 하는 것입니다. 아래와 같은 결과를 얻을 수 있습니다.
450,000건을 처리할 때 인덱스를 사용한 경우에는 3.28초 정도의 시간이 걸린 것을 알 수 있습니다. 해당 건수의 FULL SCAN 시간은 6.64초 정도가 나왔습니다. (이전 테스트에서 7.05초 정도였습니다. 약간 시간이 줄어들었으나 크게 의미는 없습니다.)
위 내용을 시각화 해보면 다음과 같습니다.
Table Scan(FULL SCAN)은 몇 건의 데이터를 조회하든 거의 일정한 시간과 거의 일정한 IO를 유지합니다. 반면에 인덱스를 이용한 데이터 접근은 어느 순간(450,000 건을 넘어서는 순간)부터 IO와 실행시간이 급등하기 시작합니다.
이처럼 조회할 데이터가 많아질 수록 인덱스보다 Table Scan(FULL SCAN)이 SQL 성능을 위해 더 좋은 선택일 수 있습니다. (한번 더 강조하면, 이와 같은 수치는 테스트 환경에 따라 상이합니다.)
조회할 데이터가 많으면 Table Scan이 더 좋다! 그러나, SQL 튜닝에 많은 도움을 주시는 변동구 이사님께서 이런 말씀을 하셨습니다. "지금도 빠르고 앞으로도 빠를 것인가?"
현재 테스트한 T_ORD_BIG에는 6백만건 정도의 데이터가 있습니다.
데이터가 지금의 열 배가 된다면, 여전히 Table Scan(FULL SCAN)이 인덱스를 활용한 검색보다 빠를 수 있을지는 생각을 해볼 필요가 있습니다.
StartUP Tuning(MySQL을 활용한 SQL 튜닝 입문 교육)에서는 지금까지의 내용을 다음과 같은 슬라이드로 소개합니다.
오늘 준비한 내용은 여기까지입니다. 정리해보면 다음과 같습니다.
옵티마이져는 데이터 처리량에 따라 인데스와 Table Scan 중에 선택을 한다.
옵티마이져의 선택이 완벽하지 않을 수도 있다.
실제로 처리할 건수가 많아질 수록 인덱스보다는 Table Scan이 더 효과적이다.
그러나 "지금도 빠르고 앞으로도 빠를것인가?" 에 대해 고민해보자.
감사합니다.
[StartUP Tuning]
- SQL 입문을 넘어선 MySQL을 활용한 SQL 튜닝 입문 교육 - 2024년 5월 오프라인 교육 과정을 모집중입니다.
1만 시간의 법칙이라고 들어보셨나요? 1만 시간을 투자한다면, 전문가가 될 수 있다는 개념입니다.
2024년을 맞이해, 새로운 기술로 1만 시간을 채워볼수 있을까란 생각이 들어 계산을 좀 해봤습니다.
SQL로요! (여기서는 MySQL을 사용했습니다.) 먼저 아래와 같이 날짜 테이블을 생성합니다.
(C_BAS_DT는 StartUP Tuning 강의에서 튜닝을 위해 소개되는 테이블입니다.)
CREATE TABLE C_BAS_DT
(
BAS_DT DATE NOT NULL
,BAS_YMD VARCHAR(8) NOT NULL
,BAS_YM VARCHAR(6) NOT NULL
,BAS_YW VARCHAR(6) NOT NULL
,BAS_DT_SEQ INT NOT NULL
,PRIMARY KEY(BAS_DT)
);
그다음에, MySQL의 RECURSIVE문을 이용해 기본 데이터를 저장합니다. 아래 SQL을 실행하면 2050년 1월 1일까지의 날짜 데이터가 C_BAS_DT에 저장됩니다.
SET @@cte_max_recursion_depth = 30000;
INSERT INTO C_BAS_DT (BAS_DT, BAS_YMD, BAS_YM, BAS_YW, BAS_DT_SEQ)
WITH RECURSIVE WR01 AS (
SELECT STR_TO_DATE('20000101','%Y%m%d') AS BAS_DT
UNION ALL
SELECT DATE_ADD(BAS_DT, INTERVAL 1 day) BAS_DT
FROM WR01
WHERE BAS_DT < STR_TO_DATE('20500101','%Y%m%d')
)
SELECT BAS_DT BAS_DT
,DATE_FORMAT(BAS_DT, '%Y%m%d') AS BAS_YMD
,DATE_FORMAT(BAS_DT, '%Y%m') AS BAS_YM
,CONCAT(YEAR(BAS_DT), LPAD(WEEK(BAS_DT), 2, '0')) AS BAS_YW
,ROW_NUMBER() OVER (ORDER BY BAS_DT) AS BAS_DT_SEQ
FROM WR01
ORDER BY BAS_DT ASC;
1만 시간의 법칙을 계산하기 위해서는 더 많은 날짜 데이터가 필요합니다. 리커시브를 계속 사용하기 보다는 셀프조인을 사용해 추가 날짜 데이터를 생성합니다.
아래의 SQL을 여러번 실행해 2300년 1월 1일까지의 데이터를 생성합니다. 세 번 정도 실행하면 됩니다.(앞에서 생성된 C_BAS_DT를 다시 사용(셀프조인)해 더 많은 연속된 날짜 데이터를 추가로 생성하는 방법입니다.)
-- INSERT 건수가 0건 될때까지 계속 실행(세번 실행하면 됨)
INSERT INTO C_BAS_DT (BAS_DT, BAS_YMD, BAS_YM, BAS_YW, BAS_DT_SEQ)
SELECT T3.BAS_DT BAS_DT
,DATE_FORMAT(T3.BAS_DT, '%Y%m%d') AS BAS_YMD
,DATE_FORMAT(T3.BAS_DT, '%Y%m') AS BAS_YM
,CONCAT(YEAR(T3.BAS_DT), LPAD(WEEK(BAS_DT), 2, '0')) AS BAS_YW
,ROW_NUMBER() OVER (ORDER BY T3.BAS_DT) + T3.MAX_DT_SEQ AS BAS_DT_SEQ
FROM (
SELECT DATE_ADD(T2.MAX_DT, INTERVAL T1.BAS_DT_SEQ day) BAS_DT
,T2.MAX_DT_SEQ
FROM C_BAS_DT T1
CROSS JOIN (SELECT MAX(X.BAS_DT) MAX_DT , MAX(X.BAS_DT_SEQ) MAX_DT_SEQ FROM C_BAS_DT X) T2
WHERE DATE_ADD(T2.MAX_DT, INTERVAL T1.BAS_DT_SEQ day) <= '23000101'
) T3
;
이제 위에서 만들어진 C_BAS_DT 테이블을 사용해, 평일에만 하루에 30분씩 투자해서 1만 시간을 채우려면 얼마나 걸릴지를, SQL로 구해봅니다. 아래와 같이 SQL을 구현해 볼 수 있습니다.
-- 오늘부터 하루에 30분씩 투자해서 평일(월,화,수,목,금)만 했을때 만시간을 채우려면 몇일이 끝일까?
-- SQL로 구해보자.
SELECT MAX(T2.BAS_DT) 만시간완료일자
,SUM(CASE WHEN T2.HOUR_SUM <= 10000 THEN 1 END) 일수
FROM (
SELECT T1.*
,DAYNAME(T1.BAS_DT) DNM
,0.5 HOUR
,SUM(0.5) OVER(ORDER BY T1.BAS_DT ASC) HOUR_SUM
FROM C_BAS_DT T1
WHERE T1.BAS_DT >= DATE_FORMAT(NOW(),'%Y%m%d')
AND DAYNAME(T1.BAS_DT) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
ORDER BY T1.BAS_DT ASC
) T2
WHERE T2.HOUR_SUM <= 10000;
만시간완료일자 |일수
---------------+-----+
2100-08-27 |20000|
제가 죽기전에는 하루에 30분씩으로는 1만 시간을 채울수가 없네요!
그렇다면, 지금 자신이 하고 있는 직업으로 1만 시간을 채우려면 얼마나걸리까요.
하루에 보통 8시간 일을 하니, 위 SQL에서 0.5 HOUR만 8 로 변경해주면 됩니다.
SELECT MAX(T2.BAS_DT) 만시간완료일자
,SUM(CASE WHEN T2.HOUR_SUM <= 10000 THEN 1 END) 일수
FROM (
SELECT T1.*
,DAYNAME(T1.BAS_DT) DNM
,8 HOUR
,SUM(8) OVER(ORDER BY T1.BAS_DT ASC) HOUR_SUM
FROM C_BAS_DT T1
WHERE T1.BAS_DT >= DATE_FORMAT(NOW(),'%Y%m%d')
AND DAYNAME(T1.BAS_DT) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
ORDER BY T1.BAS_DT ASC
) T2
WHERE T2.HOUR_SUM <= 10000;
만시간완료일자 |일수 |
----------+----+
2028-10-13|1250|
자신의 직업을 꾸준히 1250일 정도 한다면 만시간을 채울 수 있네요. 우리가 하고있는 일을 오늘부터 열심히 2028년 10월까지, 하루에 8시간씩만 쏟는다면 1만 시간을 채울 수 있습니다.
이처럼, 1만 시간을 채우기란 매우 어려운 일입니다. 실제 1만 시간을 채우지 않고도 1만 시간을 채운 효과를 보기 위해서는 다른 사람들의 도움을 받고, 다른 사람과 유연하게 협동을 해야 하지 않나란 생각이 듭니다.
2024년에서는 다양한 교육 과정과 협동을 통해 1만 시간을 더욱 단축해보실 수 있기 바랍니다.!
SQL(Structured Query Language)은 선언적 언어입니다. 그러나 SQL 튜닝을 잘하고 싶다면, SQL이 DBMS 내부적으로는 절차형 언어로 처리된다는 생각의 전환이 필요합니다. (SQL을 절차형 언어처럼 작성하라는 뜻이 절대 아닙니다.!!)
선언적 언어는 "어떻게(How)" 가 아닌 "무엇을(What)" 할 것인지, 또는 "무엇이" 필요한지를 정의하는 언어입니다. 다시말해, SQL을 사용할때는 필요한 데이터 집합이 무엇인지 정의만 해주면 됩니다. SQL과 같은 선언적 언어는 데이터를 어떠한 순서, 또는 어떠한 절차로 가져올 것인지를 정의할 필요가 없습니다.
자바, 파이썬, C와 같은 프로그래밍 언어는 절차형 언어입니다. 절차형 언어는 "무엇(What)"과 함께 "어떻게(How)"에 모두 집중해야 합니다. 절차형 언어는 필요한 결과(무엇)를 얻기 위한 절차(어떻게)까지 모두 구현해야 하기 때문입니다. 이와 같은 차이로, 절차형 언어만 다루던 분들이 초반에 SQL을 다루기 어려워하기도 합니다. 하지만, 절차형 언어를 제대로 잘하는 개발자라면, 분명히 "SQL 튜너"의 소질이 있을거라 생각합니다.
SQL은 표면적으로는 선언적 언어입니다. 하지만, DBMS 내부적으로 데이터가 처리되는 과정은 절차적입니다. 예를 들어, 뉴욕에 사는 고객의 최근 한달 주문 건수가 필요하다면, 아래와 같이 SQL로 필요한 데이터 집합을 정의해주면 됩니다.
SELECT COUNT(*) 주문건수
FROM 고객 A INNER JOIN 주문 B ON (A.고객ID = B.고객ID)
WHERE A.도시 = 'NY'
AND B.주문 >= ADD_MONTHS(NOW(),-1M);
위와 같은 SQL을 실행하면, DBMS는 내부적인 절차를 만들어 SQL에 맞는 데이터 집합을 만들어 줍니다. 이때, DBMS는 사용자가 요청한 SQL의 결과를 만들기 위해, 다양한 내부적인 처리 절차를 고려할 수 있습니다. 주문 테이블에서 최근 한달치를 읽은 후에, 고객에 접근해 결합(JOIN)할 수도 있으며, 반대로 도시가 NY인 고객을 먼저 찾은 후에 주문 테이블과 결합 할 수도 있습니다. SQL은 선언적인 언어이기 때문에, 내부적인 절차는 DBMS가 마음대로 정하고 처리하게 됩니다. 어떠한 절차로 처리하든 사용자가 요구한 데이터만 정확히 만들어주면 됩니다. 그러나, DBMS가 마음대로 처리 절차를 정한다기 보다는, SQL의 최종 결과를 얻는데 가장 비용이 적은(성능이 좋은) 방법을 고려해 절차를 정합니다. 그리고 이러한 처리 절차를 만드는 모듈이 그 유명한 옵티마이져(Optimizer)입니다.
이 글에서 SQL 성능 관련해 우리가 주목해야 할 것은, 선언적 언어인 SQL이 DBMS 내부에서는 절차적으로 처리된다는 것입니다. 그리고 이 절차에 따라 SQL의 성능 차이가 천차만별이 될 수 있습니다. SQL의 처리 절차는 실행계획(EXPLAIN)을 통해 확인할 수 있습니다. 그러므로, SQL의 성능에 이상이 있다면 가장 먼저 실행계획을 확인해야 합니다. 좀더 덧붙여 말하자면, 실행계획을 통해 SQL의 내부적인 처리 절차를 확인해야 하는 것입니다. 실행계획을 통해, 비효율적인 절차(처리 순서나 처리 방법)를 발견해 제거할 수 있다면, SQL의 성능은 저절로 좋아지게 될 것입니다.
SQL 튜닝은, 이러한 내부적인 절차를 관찰하고 비효율적인 절차를 찾아 제거하거나 변경하는 것입니다. 다시말해, SQL은 표면적으로는 선언적 언어이지만, SQL 튜닝 관점에서는 절차형 사고 방식으로 내부적인 처리 절차를 확인해야 합니다. 이러한 이해를 바탕으로 SQL 튜닝에 접근하면, SQL 튜닝을 좀 더 잘 할 수 있습니다.
끝으로, SQL을 절차적으로 작성하라는 뜻이 절대 아님을 다시 한 번 강조드립니다. 우리가 집합 개념으로 작성한 SQL이, DBMS 내부에서 어떤 절차로 처리되는지를 알 필요가 있다는 뜻입니다.
StartUP Tuning(PostgreSQL, MySQL을 활용한 SQL 튜닝 입문) 강의에서는,
Booster QUIZ를 통해, SQL 튜닝을 각자 직접 실습해볼 수 있도록 하고 있습니다.
이와 같은 실습과 고민의 시간은 SQL 튜닝 능력 향상에 많은 도움이 됩니다.
오늘은 Booster Quiz 중에 NL 조인 관련 연습문제를 하나 풀어보도록 하겠습니다.
Booster Quiz: 아래 SQL을 NL 조인으로 처리한다고 했을때, 어느 방향으로 NL 처리하는 것이 좋은지 파악하기 위해 아래 질문에 차례대로 답해보세요.
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
SQL을 보면 m_itm(아이템/품목)과 t_itm_evl_big(아이템평가), 두 개의 테이블이 사용되고 있습니다.
위 SQL에 대한 실행계획과 인덱스를 확인하지 않고 아래 질문에 차례대로 답해봅니다.
1. t1 > t2 방향으로 NL 조인한다면, t2에 방문(loop) 횟수는?
아래와 같이 원래 SQL에서 t1 부분만 추출해 카운트하면 알 수 있습니다.(답: 10번)
SELECT COUNT(*)
FROM m_itm t1
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000;
count|
-----+
10|
2. t2 > t1 방향으로 NL 조인한다면, t1에 방문(loop) 회수는?
아래와 같이 원래 SQL에서 t2 부분만 추출해 카운트하면 알 수 있습니다.(답:25,000번)
SELECT COUNT(*)
FROM t_itm_evl_big t2
WHERE t2.evl_pt = 5;
count|
-----+
25000|
1번과 2번 질문을 통해, 2번보다는 1번 방법이 더 유리할 수 있겠구나 판단할 수 있습니다.
t1>t2 방향으로 NL 조인하면 t2에는 10번만 접근하면 되고, t2>t1 방향으로 NL 조인하면 t1에는 무려 25,000번 반복 접근해야 합니다. 반복적인 접근 횟수를 줄이는 것은 SQL 성능에 도움이 됩니다. (물론 적절한 인덱스 구성이 선행되어야 합니다.)
이어서 다음 질문에도 답해봅니다.
3. 힌트를 사용해 t1 > t2 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인
아래와 같이 힌트를 적용해 실행계획을 확인해보면, t2의 loops가 10인 것을 알 수 있습니다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t1 t2)) NestLoop(t1 t2) */
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
GroupAggregate (actual time=9.148..10.760 rows=2 loops=1)
Group Key: t1.itm_id
Buffers: shared hit=296
-> Nested Loop (actual time=0.035..10.411 rows=3500 loops=1)
Buffers: shared hit=296
-> Index Scan using m_itm_pk on m_itm t1 (actual time=0.012..0.035 rows=10 loops=1)
Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
Rows Removed by Filter: 90
Buffers: shared hit=2
-> Index Scan using t_itm_evl_big_pk on t_itm_evl_big t2 (actual time=0.807..1.004 rows=350 loops=10)
Index Cond: ((itm_id)::text = (t1.itm_id)::text)
Filter: (evl_pt = '5'::numeric)
Rows Removed by Filter: 1550
Buffers: shared hit=294
Planning Time: 0.179 ms
Execution Time: 10.792 ms
4. 힌트를 사용해 t2 > t1 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인
아래와 같이 힌트를 적용해 실행계획을 확인해보면, t1의 실제 loops는 16번만 발생했습니다. 하지만, NL 조인의 후행 집합쪽을 보면, t1의 데이터를 Memoize 처리했고, Memoiz에 25,000 번의 반복 접근이 발생했습니다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t2 t1)) NestLoop(t1 t2) */
SELECT t1.itm_id
,COUNT(*) CNT
FROM m_itm t1
INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE t1.itm_tp = 'CLOTHES'
AND t1.unt_prc <= 1000
AND t2.evl_pt = 5
GROUP BY t1.itm_id;
HashAggregate (actual time=30.695..30.697 rows=2 loops=1)
Group Key: t1.itm_id
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=1694
-> Nested Loop (actual time=0.028..30.145 rows=3500 loops=1)
Buffers: shared hit=1694
-> Seq Scan on t_itm_evl_big t2 (actual time=0.010..22.777 rows=25000 loops=1)
Filter: (evl_pt = '5'::numeric)
Rows Removed by Filter: 147500
Buffers: shared hit=1662
-> Memoize (actual time=0.000..0.000 rows=0 loops=25000)
Cache Key: t2.itm_id
Cache Mode: logical
Hits: 24984 Misses: 16 Evictions: 0 Overflows: 0 Memory Usage: 2kB
Buffers: shared hit=32
-> Index Scan using m_itm_pk on m_itm t1 (actual time=0.004..0.004 rows=0 loops=16)
Index Cond: ((itm_id)::text = (t2.itm_id)::text)
Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
Rows Removed by Filter: 1
Buffers: shared hit=32
Planning Time: 0.186 ms
Execution Time: 30.736 ms
지금까지의 내용을 종합해, NL 조인 처리 방향에 따른 성능 차이를 아래와 같이 정리할 수 있습니다. 정리를 통해 현재 SQL에서는 어떤 방향으로 NL 조인이 적합한가를 판단할 수 있습니다.
t1 > t2
후행(t2)에 접근횟수: 10번
t1 > t2 IO(Buffers) : 296
t1 > t2 Execution time : 10.792 ms
t2 > t1
후행(t1)에 접근횟수: 실제 테이블에는 16번 접근, 하지만 memoize(cache) 처리된 데이터를 25,000번 접근.
IO(Buffers): 1694
Execution time : 30.736 ms
물론, 지금까지 살펴본 내용에 인덱스 구성을 추가하거나 변경한다면 완전히 다른 결과가 나올 수 있습니다.
여기서 중요한건, 이와 같은 순차적인 질문과 답으로 NL 조인의 방향에 따라 어떤 부분에서 성능 변화가 발생할 수 있는지 확인해보는 과정입니다.
안녕하세요. 오늘은 어떻게 보면 당연한 이야기이지만, SQL 튜닝 입문자라면 한번쯤은 생각해볼 이야기입니다.
SQL 튜닝(성능 개선)을 위해서는 SQL이 내부적으로 처리하는 데이터의 양을 정확히 파악할 필요가 있습니다. SQL의 성능은 SQL을 실행하면 나오는 최종 결과 건수 보다는 내부적으로 처리하는 데이터 양에 따라 좌우될 가능성이 높기 때문입니다.
아래의 SQL 을 살펴보도록 하겠습니다.
SELECT t1.ord_st ,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.ord_dt >= TO_DATE('20170301','YYYYMMDD')
AND t1.ord_dt < TO_DATE('20170401','YYYYMMDD')
GROUP BY t1.ord_st;
ord_st|cnt |
------+------+
WAIT | 36000|
COMP |334000|
위 SQL은 t_ord_big의 2017년 3월 데이터를 ord_st 별로 건수를 집계하고 있습니다. 실행해보면 표면적으로는 단 두 건의 데이터가 조회됩니다. 하지만, 내부적으로는 2017년 3월 전체 데이터를 접근하게 됩니다. 2017년 3월에 해당하는 데이터는 370,000 건이 있습니다. 이 수치는 위 SQL에서 GROUP BY를 제거하고 실행해보면 알 수 있습니다. (또는 위 SQL의 ord_st별 건수를 더해서도 알 수 있습니다.)
위 SQL의 튜닝을 위해서는 이 370,000건의 데이터를 어떻게 효율적으로 처리할 수 있을지를 고민해야 합니다. 370,000 건 모두를 인덱스 리프 블록에서 처리하게 할 수도 있으며, 테이블의 데이터 블록에서 처리하게 할 수도 있습니다. 이처럼 절대적으로 읽어야 할 데이터를 어떻게 효율적으로 처리할지 모색하는 것이 바로 SQL 튜닝이라 할 수 있습니다.
내부적으로 접근해야 하는 데이터 건수를 파악하는 것! 이것이 바로 SQL 튜닝의 시작점입니다. 어떤 방법으로 튜닝할까를 고민하기 전에, 절대적으로 읽어야 하는 건수가 몇 건인지 파악하는 노력을 해보기 바랍니다. 이러한 접근은 SQL 튜닝을 더 빠르고 효과적으로 수행하는 데 크게 기여할 것입니다
P.S. 위에서 실제 접근한 데이터 건수는 SQL의 실제 실행계획을 통해서도 알 수 있습니다. 아래는 PostgreSQL의 실행계획입니다. t_ord_big에 대한 인덱스를 이용해 370,000(actual 부분의 rows)건에 접근한 것을 알 수 있습니다.
HashAggregate (actual time=121.000..121.002 rows=2 loops=1)
Group Key: ord_st
Batches: 1 Memory Usage: 24kB
-> Index Scan using t_ord_big_x01 on t_ord_big t1 (actual time=0.017..57.820 rows=370000 loops=1)
Index Cond: ((ord_dt >= to_date('20170301'::text, 'YYYYMMDD'::text)) AND (ord_dt < to_date('20170401'::text, 'YYYYMMDD'::text)))
Planning Time: 0.121 ms
Execution Time: 121.035 ms
본 문서의 내용은 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의 Bitmap Index Scan
PostgreSQL의 실행계획에는 Index (Range) Scan이 아닌 Bitmap Index Scan이 종종 나타납니다.
설명에 앞서, 오라클의 Bitmap Index와 PostgreSQL의 Bitmap Index Scan를 구분할 필요가 있습니다. 이 두개는 완전히 다른 것입니다.
오라클의 Bitmap Index는 인덱스 자체를 Bitmap 형태로 구성한 것입니다.
반면에 PostgreSQL의 Bitmap Index Scan은 일반적인 B Tree 인덱스를 사용하는 처리 방식 중에 하나입니다. 다시 말해, B Tree 인덱스를 사용해 일반적인 Index Scan을 할 수도 있으며 Bitmap Index Scan을 할 수도 있습니다. Bitmap Index Scan은 인덱스를 통한 테이블 블록의 접근 횟수를 줄이기 위해 Bitmap 처리를 추가 적용한 것을 뜻합니다.
아래 SQL을 실행해 봅니다. 실행계획을 보면 x02(rno) 인덱스에 대해 Bitmap Index Scan이 작동했습니다.
총 실행 시간은 3.482 ms가 걸렸고, 3053의 IO(Buffers: shared ht)가 발생하고 있습니다.
EXPLAIN (ANALYZE,BUFFERS)
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.cus_id = 'CUS_0064'
AND t1.pay_tp = 'BANK'
AND t1.rno = 2
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
GroupAggregate (cost=10374.03..10374.25 rows=11 width=40) (actual time=3.446..3.448 rows=2 loops=1)
Group Key: (to_char(ord_dt, 'YYYYMM'::text))
Buffers: shared hit=3053
-> Sort (cost=10374.03..10374.06 rows=11 width=32) (actual time=3.440..3.442 rows=2 loops=1)
Sort Key: (to_char(ord_dt, 'YYYYMM'::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3053
-> Bitmap Heap Scan on t_ord_big t1 (cost=35.24..10373.84 rows=11 width=32) (actual time=0.712..3.432 rows=2 loops=1)
Recheck Cond: (rno = 2)
Filter: (((cus_id)::text = 'CUS_0064'::text) AND ((pay_tp)::text = 'BANK'::text))
Rows Removed by Filter: 3045
Heap Blocks: exact=3047
Buffers: shared hit=3053
-> Bitmap Index Scan on t_ord_big_x02 (cost=0.00..35.23 rows=3040 width=0) (actual time=0.282..0.283 rows=3047 loops=1)
Index Cond: (rno = 2)
Buffers: shared hit=6
Planning Time: 0.092 ms
Execution Time: 3.482 ms
Bitmap Index Scan 역시 Index Scan과 마찬가지로 수직적탐색과 수평적탐색, 테이블접근으로 작업이 진행됩니다.
단, 테이블 블록 접근 단계에서, 접근 횟수를 줄일 수 있도록 아래와 같은 알고리즘이 추가되어 있습니다.
인덱스 수평탐색을 하면서 얻은 ctid(실제 레코드 포인터) 정보를 모아서 비트맵으로 변환
ctid 별로 건건이 테이블 블록에 한 번씩 접근하는 것이 아니라,
ctid 를 모아서 비트맵으로 변환한 후에 같은 블록의 데이터는 한 번에 가져오는 방법
이를 통해, 불필요한 중복 접근을 줄일 수 있습니다.
마치 오라클의 TABLE ACCESS BY INDEX ROWID BATCHED 와 유사하다 할 수 있습니다.
Bitmap Index Scan에는 Bitmap Heap Scan 단계가 따라옵니다. 이는 ctid 비트맵을 사용해 Heap 테이블에 접근하는 단계입니다.
이와 같은 Bitmap Index Scan은 인덱스를 사용해 많은 데이터 블록에 접근할 가능성이 높거나, 실제 테이블의 데이터 정렬 순서와 인덱스 리프의 정렬 순서 차이가 클때 유용한 것으로 알려져 있습니다.
PostgreSQL의 pg_stats를 통해 컬럼과 테이블간의 정렬 순서의 상관 관계 통계값을 알 수 있습니다. rno의 상관 계수는 0.012로 낮은 편입니다. 결과적으로 x02 인덱스(rno)의 정렬 순서와 실제 데이터의 정렬 순서 차이가 커서 Bitmap Index Scan을 사용한 것으로 추정할 수 있습니다.
그러나! 100% 완벽한 통계는 없습니다. 아니요. 통계는 100% 완벽할 수 있을거 같습니다. 하지만, 그에 따라 선택한 옵티마이져의 결정이 항상 최선은 아닐 수 있다고 말하는게 맞을거 같습니다.
아래와 같이 힌트를 사용해 일반적인 IndexScan으로 SQL을 실행해봅니다. IO가 3,053으로 기존의 Bitmap Index Scan을 했을때와 동일합니다. 이처럼 IO가 같다면 Bitmap 연산을 추가로 수행하는 Bitmap Inedx Scan이 좀 더 느릴 수 있습니다. 현재 결과에서도 IndexScan의 실행시간이 2.416ms로 Bitmap Index Scan보다 아주 조금 더 빠릅니다.
EXPLAIN (ANALYZE,BUFFERS)
/*+ IndexScan(t1) */
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.cus_id = 'CUS_0064'
AND t1.pay_tp = 'BANK'
AND t1.rno = 2
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
GroupAggregate (cost=12031.20..12031.42 rows=11 width=40) (actual time=2.387..2.389 rows=2 loops=1)
Group Key: (to_char(ord_dt, 'YYYYMM'::text))
Buffers: shared hit=3053
-> Sort (cost=12031.20..12031.23 rows=11 width=32) (actual time=2.381..2.382 rows=2 loops=1)
Sort Key: (to_char(ord_dt, 'YYYYMM'::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3053
-> Index Scan using t_ord_big_x02 on t_ord_big t1 (cost=0.43..12031.01 rows=11 width=32) (actual time=0.043..2.371 rows=2 loops=1)
Index Cond: (rno = 2)
Filter: (((cus_id)::text = 'CUS_0064'::text) AND ((pay_tp)::text = 'BANK'::text))
Rows Removed by Filter: 3045
Buffers: shared hit=3053
Planning Time: 0.123 ms
Execution Time: 2.416 ms
3.482 ms나 2.416 ms나 인간이 거의 느낄 수 없는 시간 차이이기 때문에 의미가 있다고 할 수는 없습니다.
어쨋든, 옵티마이져가 선택한 방법이 최선이 아닐 수도 있다라고 한 번쯤 의심해볼 필요는 있습니다.
(절대, Bitmap Index Scan보다 Index Scan이 좋다는 일반론적 이야기가 아닙니다. 상황에 따라서는 Bitmap Index Scan이 더 유리할 수 있습니다. 또한 옵티마이져가 제대로 제 할일을 못한다라는 뜻의 이야기도 아닙니다.! 기본적으로 통계가 잘 구성되어 있다면 옵티마이져를 믿는 것이 우선입니다.)
이번에는 SQL을 살짝 바뿨봅니다. rno를 범위 조건으로 조금 더 많은 데이터를 조회해봅니다. 아래 결과를 살펴보면 Bitma Index Scan이 Index Scan보다 훨씬 적은 IO(Bitmap:3,192, IndexScan: 12,202)가 발생하고 실행시간도 근소하지만 더 빠릅니다.
EXPLAIN (ANALYZE,BUFFERS)
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.rno BETWEEN 2 AND 5
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
HashAggregate (cost=33470.11..33474.47 rows=349 width=40) (actual time=9.486..9.489 rows=12 loops=1)
Group Key: to_char(ord_dt, 'YYYYMM'::text)
Batches: 1 Memory Usage: 37kB
Buffers: shared hit=3192
-> Bitmap Heap Scan on t_ord_big t1 (cost=168.78..33409.45 rows=12131 width=32) (actual time=1.140..7.639 rows=12188 loops=1)
Recheck Cond: ((rno >= 2) AND (rno <= 5))
Heap Blocks: exact=3178
Buffers: shared hit=3192
-> Bitmap Index Scan on t_ord_big_x02 (cost=0.00..165.74 rows=12131 width=0) (actual time=0.741..0.741 rows=12188 loops=1)
Index Cond: ((rno >= 2) AND (rno <= 5))
Buffers: shared hit=14
Planning:
Buffers: shared hit=8
Planning Time: 0.104 ms
Execution Time: 9.546 ms
EXPLAIN (ANALYZE,BUFFERS)
/*+ IndexScan(t1) */
SELECT TO_CHAR(t1.ord_dt,'YYYYMM') ym,COUNT(*) cnt
FROM t_ord_big t1
WHERE t1.rno BETWEEN 2 AND 5
GROUP BY TO_CHAR(t1.ord_dt,'YYYYMM');
HashAggregate (cost=45715.02..45719.38 rows=349 width=40) (actual time=10.388..10.392 rows=12 loops=1)
Group Key: to_char(ord_dt, 'YYYYMM'::text)
Batches: 1 Memory Usage: 37kB
Buffers: shared hit=12202
-> Index Scan using t_ord_big_x02 on t_ord_big t1 (cost=0.43..45654.36 rows=12131 width=32) (actual time=0.015..8.493 rows=12188 loops=1)
Index Cond: ((rno >= 2) AND (rno <= 5))
Buffers: shared hit=12202
Planning:
Buffers: shared hit=8
Planning Time: 0.142 ms
Execution Time: 10.420 ms
이처럼 대량의 데이터에 접근해야 한다면, 바꿔말해 인덱스를 사용해 테이블의 데이터 블록에 많은 접근이 발생할 수 있다면, Bitmap 처리를 통해 불필요한 IO를 줄일 수 있습니다. 이것이 바로 Bitmap Index Scan입니다.
본 문서의 내용은 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 성능때문에 고민중이라면 이와 같은 실행계획에 익숙해주시기를 권장합니다.
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하는 것을 알 수 있습니다.
