인덱스를 활용한 SQL 튜닝

2023-12-11

 

배너.png





서비스 운영관리에 있어서 성능은 필수적인 요소입니다

최근에는 서버의 사양이 대체로 높은 편이지만 여전히 자원의 증설에는 시간과 비용의 한계가 있기 때문에 투자한 비용 대비 효과가 뛰어난 SQL 튜닝은 여전히 그 효용성을 인정받고 있습니다.

 

다양한 SQL 튜닝방법이 있지만 그 중 적절한 인덱스를 생성하여 데이터 검색 속도를 항상시키는 것은 대표적인 SQL 튜닝 방법 중 하나입니다.

세 가지 실제 사례를 통해 인덱스를 활용한 튜닝 CASE를 자세히 알아보겠습니다.


CASE 1) 인덱스가 없어 항상 Full Table scan 하고 있음

이미지편집_02.png

이미지편집_03.png


첫번째 사례의 경우 WHERE 조건절에 주요 컬럼들이 상수 조건으로 입력되지만

인덱스가 없어서 항상 Full Table Scan을 하여 SQL의 평균 Elapse Time22.7초 였습니다.

이러한 경우 아래 예시와 같이 WHERE 조건절의 주요 컬럼 상수조건들에 대해 복합인덱스를 생성하여 Index Scan 하도록 유도하면 해결됩니다.

이미지편집_04.png


저희 셀파오라클 기능 중 Search SQL - Full Table Scan을 활용하면 인덱스를 활용하지 못하고 Full Table Scan 하는 SQL들을 실시간으로 아주 쉽게 찾을 수 있습니다.


이미지편집_05.png

CASE 2) NL 조인으로 유도해야 하나 상수조건에 인덱스가 없어 항상 HASH 조인 및 Full Table Scan하는 경우

이미지편집_06.png


이미지편집_07.png

두번째 사례에서는 WHERE 조건절에 SQL 처리 범위를 줄여주는 상수조건이 있으므로,Nested Loop 조인으로 유도해야 하나

GUSR_APP.T_EXE_CNTC_ETXBL_RQIQ 테이블의 범위를 줄일 수 있는 상수조건들에 대한 인덱스가 없어 항상 HASH JOIN FULL Table Scan하고 있었습니다.

해당 테이블의 데이터 특성상 시간이 지날수록 데이터가 많아지는 구조였고, 점차 응답시간이 느려질 가능성이 높아, 인덱스 생성이 필요한 상황이었습니다.


따라서 HASH JOIN FULL Table Scan하지 않도록 다음과 같이 인덱스를 생성하였습니다.


이미지편집_08.png



CASE 3) 컬럼에 인덱스가 없음 + 조건절의 형태도 인덱스 못쓰는 형태

이미지편집_09.png

이미지편집_10.png



마지막 세번째 사례의 경우, T_MOB_PUSH_SNDING 테이블의 데이터에 대해 예외처리 되는 조건 결과를 INSERT하는 구조에서RESULT_CODE 컬럼에 인덱스가 없고

조건절의 형태도 [ != ], [ OR ],  [ IS NULL ] 형태로 되어 있어 인덱스가 있더라도 인덱스를 활용하지 못하는 SQL 형태였습니다.


해당 SQL은 하루 3만번 이상 수행될 정도로 수행 횟수가 많았기 때문에 튜닝의 필요성이 컸던 경우입니다.


이미지편집_11.png

위의 T_MOB_PUSH_SNDING 테이블의 데이터 분포를 보면 RESULT_CODE = Y 인 데이터가 99% 이상 차지하고 있으며

Y가 아니거나 NULL 인 데이터를 INSERT하는 형태이기 때문에 RESULT_CODE에 대한 인덱스가 필요합니다.


이미지편집_12.png



RESULT_CODE로 인덱스를 생성하되, RESULT_CODE 컬럼이 NULL 허용 타입이므로 NOT NULL 조건이 강제된 컬럼(SN)을 포함하여 위의 예시 스크립트와 같이 인덱스를 생성해야 합니다.

또한 기존의 SQL의 조건절 형태를 아래의 튜닝 후 SQL 스트립트와 같이 수정하여 인덱스를 탈 수 있도록 해야 합니다.


이미지편집_13.png

개발 중에 특정 테이블이 평소 인덱스를 잘 타고 있는지 또는 튜닝 후 내가 생성한 인덱스가 잘 활용되고 있는지 궁금한 경우가 많습니다

셀파오라클은 Performance Impact Analysis 화면에서 보다 쉽게 TABLE FULL SCAN INDEX SCAN을 확인 할 수 있도록 기능을 제공하고 있습니다.


이미지편집_15.png



 

작성 : 기술본부 임예빈 책임
편집 : 최지은 책임,조연지 대리 

인덱스를 활용한 SQL 튜닝
셀파소프트
-

다음 글

DB 속담 시리즈

목록보기