MySQL EXPLAIN ANALYZE #2

 

글 하단 부분에서 별도 pdf로 다운로드 받으실 수 있습니다.

 

MySQL의 EXPLAIN ANALYZE 읽어보기 #2

 

DBMS: MySQL 8.0.35

지난 글에서는 MySQL의 실제 실행계획을 살펴봤습니다.

https://sweetquant.tistory.com/553

MySQL 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;

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과 테이블 구조(현재 인덱스, 컬럼 구성, 데이터 양, 테이블 간의 관계 등)를 고민해 그에 맞는 적절한 진료를 해야 합니다.

오늘 준비한 글은 여기까지입니다. 감사합니다.

 

아래는 실제 강의에서 해당 과정을 설명하는 슬라이드입니다.

 

 

 

아래는 현재 글을 pdf로 만든 자료입니다.

20240429_StartUPTuning_실행계획2.pdf

0.33MB

 

 

---

[StartUP Tuning]

• SQL 입문을 넘어선 MySQL을 활용한 SQL 튜닝 입문 교육
• 2024년 5월 주말 오프라인 교육 과정을 모집중: https://cafe.naver.com/dbian/7717

StartUP Tuning - MySQL/MariaDB를 활용한 SQL 튜닝 입문

​

[StartUP SQL]

• 데이터 분석가, 개발자, 기획자를 위한 SQL 입문 교육
• 교육용 슬라이드 전체 무료 오픈: https://sweetquant.tistory.com/497

StartUP SQL 소개 및 목차