서비스 운영관리에 있어서 성능은 필수적인 요소입니다.
최근에는 서버의
사양이 대체로 높은 편이지만 여전히 자원의 증설에는 시간과 비용의 한계가 있기 때문에 투자한 비용 대비 효과가 뛰어난 SQL 튜닝은 여전히 그 효용성을 인정받고 있습니다.
다양한 SQL 튜닝방법이 있지만 그 중 적절한 인덱스를 생성하여 데이터
검색 속도를 항상시키는 것은 대표적인 SQL 튜닝 방법 중 하나입니다.
세 가지 실제 사례를 통해 인덱스를 활용한 튜닝 CASE를 자세히
알아보겠습니다.
CASE 1) 인덱스가 없어 항상 Full
Table scan 하고 있음
첫번째 사례의 경우 WHERE 조건절에 주요 컬럼들이 상수 조건으로
입력되지만,
인덱스가 없어서 항상 Full Table Scan을
하여 SQL의 평균 Elapse Time이 22.7초 였습니다.
이러한 경우 아래 예시와 같이 WHERE 조건절의 주요 컬럼 상수조건들에
대해 복합인덱스를 생성하여 Index Scan 하도록 유도하면 해결됩니다.
저희 셀파오라클 기능 중 Search SQL - Full Table Scan을
활용하면 인덱스를 활용하지 못하고 Full Table Scan 하는 SQL들을
실시간으로 아주 쉽게 찾을 수 있습니다.
CASE 2) NL 조인으로 유도해야 하나 상수조건에 인덱스가 없어 항상 HASH 조인 및 Full Table Scan하는 경우
두번째 사례에서는 WHERE 조건절에 SQL 처리 범위를 줄여주는 상수조건이 있으므로,Nested Loop 조인으로
유도해야 하나,
GUSR_APP.T_EXE_CNTC_ETXBL_RQIQ 테이블의 범위를 줄일 수 있는
상수조건들에 대한 인덱스가 없어 항상 HASH JOIN 및 FULL
Table Scan하고 있었습니다.
해당 테이블의 데이터 특성상 시간이 지날수록 데이터가 많아지는 구조였고, 점차
응답시간이 느려질 가능성이 높아, 인덱스 생성이 필요한 상황이었습니다.
따라서 HASH JOIN 및FULL Table Scan하지 않도록 다음과 같이 인덱스를 생성하였습니다.
CASE 3) 컬럼에 인덱스가 없음 + 조건절의
형태도 인덱스 못쓰는 형태
마지막 세번째 사례의 경우, T_MOB_PUSH_SNDING 테이블의
데이터에 대해 예외처리 되는 조건 결과를 INSERT하는 구조에서RESULT_CODE 컬럼에 인덱스가 없고,
조건절의 형태도 [ != ], [ OR ], [ IS NULL ] 형태로
되어 있어 인덱스가 있더라도 인덱스를 활용하지 못하는 SQL 형태였습니다.
해당 SQL은 하루 3만번
이상 수행될 정도로 수행 횟수가 많았기 때문에 튜닝의 필요성이 컸던 경우입니다.
위의 T_MOB_PUSH_SNDING 테이블의 데이터 분포를 보면 RESULT_CODE = Y 인 데이터가 99% 이상 차지하고 있으며,
Y가 아니거나 NULL 인 데이터를 INSERT하는 형태이기 때문에 RESULT_CODE에 대한 인덱스가
필요합니다.
RESULT_CODE로 인덱스를 생성하되, RESULT_CODE 컬럼이 NULL 허용 타입이므로 NOT NULL 조건이 강제된 컬럼(SN)을 포함하여 위의 예시 스크립트와
같이 인덱스를 생성해야 합니다.
또한 기존의 SQL의 조건절 형태를 아래의 튜닝 후 SQL 스트립트와 같이 수정하여 인덱스를 탈 수 있도록 해야 합니다.
개발 중에 특정 테이블이 평소 인덱스를 잘 타고 있는지 또는
튜닝 후 내가 생성한 인덱스가 잘 활용되고 있는지 궁금한 경우가 많습니다.
셀파오라클은 Performance Impact Analysis 화면에서 보다 쉽게 TABLE
FULL SCAN과 INDEX SCAN을 확인 할 수 있도록 기능을 제공하고 있습니다.
작성 : 기술본부 임예빈 책임
편집 : 최지은 책임,조연지 대리