[SQL 튜닝] Index Range Scan을 통한 SQL 성능 개선 사례

서론

 

프로젝트 개발 진행 중 SQL 속도 개선 문의를 받았습니다. 원인을 파악해보니 기존에 개발된 SQL문이 품목 이동 이력 테이블(ITEM_MOVEMENT)을 조회하는데 데이터가 천만 건 이상 존재하여 속도가 느리다는 것이었습니다. 오늘은 이 개선 사례에 대해 좀 더 간소화된 예시로 다듬어 정리해보려고 합니다.

 

개발 환경

- MSSQL

---

 

본론

 

문제의 원인을 파악하기 위해 실행계획을 살펴보니... 품목 이동 이력 테이블(ITEM_MOVEMENT)에서 매번 테이블 FULL스캔으로 천만 건 이상을 조회하여 이동 수량(MV_QTY)을 집계하는 게 원인이었습니다. 프로그램 화면에서 조건을 품목코드(ITEM_CODE), FROM, TO 이동일(MOVEMENT_DT)을 필수 입력으로 받았지만 해당 조건들이 데이터를 필터링하는데 역할을 수행하지 못했습니다.

 

[프로그램 화면]

[관련 테이블 인덱스]

 

테이블명

 - ITEM_MOVEMENT

인덱스

- ITEM_MOVEMENT_PK : (MOVEMENT_NO, MOVEMENT_YEAR)

- ITEM_MOVEMENT_IDX1 : (PLANT_CODE)

 

[문제의 SQL]

 

SELECT MOVEMENT_DT, ITEM_CODE, SUM(MV_QTY) AS MV_QTY
FROM ITEM_MOVEMENT
WHERE
ITEM_CODE = @ITEM_CODE
AND MOVEMENT_DT >= @FROM_DATE
AND MOVEMENT_DT < @TO_DATE
GROUP BY MOVEMENT_DT, ITEM_CODE

 

[원인 파악]

 

조건절에 ITEM_CODE, MOVEMENT_DT 조건이 있지만 이는 인덱스에 없는 컬럼들이므로  Index Range Scan이 불가능합니다. 따라서 Table Acccess Full Scan 하여 천만 건이 넘는 전체 데이터를 스캔 후 조건절로 필터링하므로 상당히 비효율적입니다. 인덱스 스캔과 관련해서는 아래 글에 정리해놓은 게 있으니 이해를 돕는데 도움이 되면 좋겠습니다.

 

https://goldswan.tistory.com/33

[Oracle]실행계획 해석_인덱스 관련

 

[해결책 제시]

 

1. 테이블에 (ITEM_CODE, MOVEMENT_DT) 인덱스를 추가

2. 특정 형식으로 채번되는 PK의 특성을 이용하여 Index Range Scan되도록 조건절을 추가 -> 채택

 

제가 생각해냈던 위 해결책에 대해 하나씩 살펴보겠습니다.

 

1. 테이블에 (ITEM_CODE, MOVEMENT_DT) 인덱스를 추가

 

-> 테이블에 (ITEM_CODE, MOVEMENT_DT) 인덱스를 추가하면 품목코드(ITEM_CODE)가 범위 조건(>=, <)이 아닌 = 조건이므로 상당히 많은 데이터를 Index Range Scan으로 필터링할 수 있을 걸로 기대되었습니다. 그러나 현재 운영 중인 업체였고 천만 건 이상 데이터가 있는 테이블의 인덱스를 생성하기 위해선 최소 3분 이상의 생성 시간이 필요했습니다.(테스트 서버에서 테스트) 인덱스를 생성하는 동안에는 해당 테이블이 Lock이 되기 때문에 운영에 리스크가 있었습니다. 또한 인덱스를 추가하게 되면 CUD 수행 때 추가된 인덱스도 관리해줘야 하므로 그만큼 성능에 영향이 가는 부분도 있었습니다. 데이터가 빈번히 생성, 삭제되는 테이블이었으므로 1번의 방법으로는 리스크가 많아 다른 방법을 찾기로 했습니다.

 

2. 특정 형식으로 채번되는 PK의 특성을 이용하여 Index Range Scan되도록 조건절을 추가

 

-> PK인 (MOVEMENT_NO, MOVEMENT_YEAR) 중 MOVEMENT_NO는 아래와 같이 채번되는 특성이 있었습니다.

 

MOVEMENT_NO : 대문자 알파벳 2개 + 'YYMMDD' + 6자리 숫자

(예 : MV220422000001, AA220423000002, ZZ220424000003)

 

위 비즈니스 로직에서 필요한 데이터는 MOVEMENT_NO가 대문자 알파벳 2개가 'MV'로 이루어진 데이터만 집계하면 되는 특징이 있었습니다. 또한 채번되는 'YYMMDD'가 조건절에 있는 MOVEMENT_DT의 년월일과 동일한 특징이 있었습니다.

운영 서버에서 앞자리가 'MV'인 데이터의 전체 개수를 파악해보니 약 50만 건이 조회되었고 이는 1000만 건에서 5%를 차지했습니다. 이동일 조건도 추가가 되면 충분히 필터링되어 성능개선 효과를 가져올 수 있다 생각하여 아래의 조건을 추가했습니다.

 

[개선 SQL]

SELECT MOVEMENT_DT, ITEM_CODE, SUM(MV_QTY) AS MV_QTY
FROM ITEM_MOVEMENT
WHERE
--속도 개선을 위해 MOVEMENT_NO 범위조건 추가
MOVEMENT_NO >= 'MV'+RIGHT(@FROM_DATE,6)
AND MOVEMENT_NO < 'MV'+RIGHT(@TO_DATE,6)
AND ITEM_CODE = @ITEM_CODE
AND MOVEMENT_DT >= @FROM_DATE
AND MOVEMENT_DT < @TO_DATE
GROUP BY MOVEMENT_DT, ITEM_CODE

 

[성과]

 

위 조건을 추가하고 업무적으로 주로 쓰는 이동일 한 달 간격으로 조회를 하니 천만 건에서 평균 만 건으로 스캔하는 데이터가 줄어들었습니다. 이는 테이블 전체를 100% 스캔할 때 보다 10000/10000000 * 100 = 0.1% 만 스캔되어 처음보다 매우 효율적이었습니다. 결과적으로 2번의 방법을 사용하여 평균 실행시간이 3분 이상 걸리는 쿼리를 3초 안에 수행되도록 개선했습니다! 참으로 감격스러운 순간이었습니다.

 

---

 

결론

 

업무적인 특성을 분석하고 효율적으로 Index Range Scan을 하기 위해 조건절을 추가함으로써 SQL 성능을 개선하는 사례를 소개해보았습니다. 제가 소개한 사례가 독자분들에게 새로운 영감이 되거나 도움이 되었으면 좋겠습니다!