쿼리 튜닝(프로시저, 임시테이블, Index Range Scan)

쿼리 튜닝(프로시저, 임시테이블, Index Range Scan)

 

문제점

해외에서 서비스 하고 있는 홈페이지가 있었습니다.

이번에 속도가 너무 느리다고 해외 현지에서 컴플레인이 걸렸는데요. 처음에는 물리적으로 네트워크 시간이 긴줄 알았지만 해외 DB를 Dump해봐 개발서버에 적용하고 SQL을 실행했는데 생각보다 많은 시간이 걸렸습니다.

내부 개발서버에서도 많이 느렸기 때문에 최대한 SQL 튜닝을 팀원과 함께 진행을 해 보았습니다.

 

HTTP/2(HTTP 2.0) 프로토콜이 적용된 홈페이지 입니다.

MariaDB 10.3DB를 사용하고 있습니다.

 

---

문제 파악 후 해결 방법

20s 이상이 걸리는 요청에는 3개의 프로시저와 3개의 SQL을 중점적으로 SQL을 파악하고 팀원들과 함께 어떻게 개선해 나갈지 토론을 하였습니다.

 

1. 프로시저 - TEMPORARY TABLE 삭제 및 원쿼리 튜닝

프로시저에는 CREATE TEMPORARY TABLE을 통해 임시테이블을 생성 후 필요한 데이터를 임시테이블에 담고 Update를 하면서 데이터를 가공 후에 Return하는 방식이였으며 매번 CREATE 하기 전 DROP을 하였습니다.

또한 프로시저 임시테이블 생성할 때 Index를 추가적으로 설정했는데 제대로 Index를 사용하지 않는것을 확인했습니다.

set profiling=1;
CALL SP_에시List(파라미터);
show profiles

profiling을 통해 어느 쿼리에서 시간이 많이 걸리는지 측정을 했고 임시테이블 생성 후 임시테이블에 데이터를 Insert할 때 시간이 굉장히 많이 걸린다는 것을 파악하고 임시 테이블을 삭제하고 원쿼리로 SQL 튜닝을 진행했습니다.

 

 

TEMPORARY TABLE 삭제 후 원쿼리로 SQL 튜닝을 진행했습니다.
개선률 : 약 60~70%정도 빨라졌습니다.
1. 약 2.7s 걸리던 프로시저가 약 850ms로 줄었습니다.
2. 좀 더 많은 데이터를 파라미터로 넣었고 약 5.8s걸리던 부분이 1.5s로 줄었습니다.

기존의 프로시저는 냅두고 새로운 프로시저를 생성 후 _step1으로 변경해서 원쿼리로 진행하였습니다.

 

 

 

 

 

2. 프로시저 및 SQL에 Index Range Scan 적용

프로시저 임시테이블 삭제 및 원쿼리 진행 후 EXPLAIN을 통해 쿼리 실행 계획을 확인해 보았습니다.

A테이블에 Index가 있었지만 Index Range Scan을 타고 있지 않았습니다.

선두 컬럼이 YEAR(@IN_ENDDTM) = DATE_FORMAT(A.REG_DTM, '%Y')로 가공이 되어 있었는데요. REG_DTM은 datetime 데이터타입이였습니다.

 

Index Range Scan을 탈 수 있도록 선두 컬럼을 변경했습니다.

-- 변경 전
YEAR(@DATE) = DATE_FORMAT(A.REG_DTM, '%Y')

-- 변경 후 
A.REG_DTM >= @START_DATE
AND A.REG_DTM <= @END_DATE

변경 후 Index Range Scan을 타는것을 실행계획을 보고 확인할 수 있었습니다.

위에서 프로시저 임시테이블을 삭제 후 850ms가 나왔던 쿼리를 다시 실행해 보니 510ms로 더욱더 빨라진 것을 확인할 수 있었습니다. 추가적으로 조건절 좌변에는 가공을 하지 않았으며 Index 순서를 맞추어 주었습니다.

 

※ Index 생성을 제대로 했다는 가정하에 입니다.

 

---

최종 개선률

조건에 따라서 데이터가 다르기 때문에 평균적으로 80%~90%정도 속도가 개선된것을 확인할 수 있었습니다.

이렇게 간단하게도 개선률이 엄청 올라간것을 확인할 수 있었으며 SQL 튜닝을 진행하면서 '친절한 SQL'도서에서 읽은 내용도 많은 도움이 되었습니다.

첫 SQL튜닝이였지만 이렇게 개선이 된다는 것을 실제로 확인해보니 앞으로 Index와 Where문 조건을 맞추어 사용해야 겠다는 생각이 들었습니다.

 

개선전 한국->해외 홈페이지 접속할 때 약 ~40s정도 걸렸지만 지금은 ~6s로 엄청 줄었습니다.

 

Reqeust	개선 전	개선 후
get-s**.ajax	24.59s	3.72s
get-k**.ajax	21.24s	3.66s
get-k**.ajax	26.14s	3.46s
get-n**.ajax	16.56s	2.41s
get-h**.ajax	23.77s	2.37s
get-h**.ajax	15.39s	2.08s
get-n**.ajax	22.81s	1.83s
get-h**.ajax	22.41s	1.32s
get-n**.ajax	15.72s	970ms
get-h*.ajax	14.32s	704ms

와... 대박입니다. 같은 Result값이면서도 속도는 엄청 줄었네요.

첫 SQL튜닝을 하면서 많이 알게 된것 같습니다. 프로시저에 대해서도 요번 회사와서 처음 사용해 보았는데 프로시저에 대한 내용도 많은 공부가 되었던것 같습니다.

 

---

 

개선 요약 및 테스트 하면서 발견한 내용

1. 프로시저 임시테이블을 가급적 사용하지 말자

2. 프로시저 임시테이블을 사용해야 한다면 데이터를 SELET하는 테이블의 Index를 실행계획으로 살펴보자.

3. 조건절에 좌변은 Index를 사용할 수 있도록 가공하지 않는다.

4. Index 컬럼에 함수사용하면 Index를 타지 않는다.

5. BETWEEN, LIKE, >, < 등의 범위 조건에 사용된 컬럼까지는 Index를 타지만 이후에는 인덱스를 사용하지 않는다.

6. Index 컬럼 순서를 Index와 맞추어 주어야 한다. -> '친절한 SQL' 도서에서는 순서도 맞아야 한다고 하지만 MariaDB에서 실험해 보았을 때는 Index 순서를 안지켜도 되었습니다.

7. Where문 조건에 INDEX를 제대로 사용했는데 Index Range Scan이 안되는 경우, Select 문제 Index에 사용하지 않은 Column이 있는지 확인하기 -> Index에 없는 Column을 Select에 사용할 때 Index Range Scan이 안되었습니다.

 

etc) DB마다 조금씩 Index에 대해서 다를 수 있다는 것을 튜닝을 통해 알게 되었습니다.'친절한 SQL'도서에서는 오라클 기준이였으며, 이번 튜닝을 진행한 DB는 MariaDB였습니다.

SQL에서도 ANSI 표준이 있듯이 튜닝도 오라클 기준으로 하되 안맞는 부분은 추가적으로 수정해야 할것 같습니다.