[쿼리 튜닝] Where 조건 좌측(?)에 함수를 사용하면 인덱스가 깨져요!!

최근 회사에서 쿼리 튜닝이 이슈로 떠오르고 있다. 

 

사실 이전에는 튜닝 대상 쿼리를 추출하는 방식이 굉장히 주먹구구식이고, 실행 당시 파라미터도 쉽게 파악할 수 없어서 쿼리 튜닝 작업에 큰 장벽이 있었다.

 

쿼리 튜닝이 이슈로 떠오르면서 이러한 문제점을 해결하기 위해 Whatap이라는 어플리케이션, 디비 모니터링 툴을 체험판으로 도입해보았고, 결과는 상당히 고무적인 것 같다. 

적용 2주만에 DB CPU 부하가 6~7% 정도 감소했다고 한다. 

 

물론 지금까지 거의 튜닝 작업을 하지 않다가 최근 2주 간 굉장히 열심히 튜닝 작업을 해서 발생한 성과라고 할 수 있지만, Whatap의 도입으로 어떤 쿼리가 문제인지 통계자료를 통해 파악하고, 파라미터 값도 쉽게 바인딩 할 수 있어 결정적인 영향을 줬다고 할 수 있다.

 

오늘 내가 튜닝한 쿼리는 Where 조건 좌측(뭐라고 표현해야할지 잘 모르겠다.)에 함수를 사용하여 인덱스가 깨지면서 수행시간이 길어진 쿼리였다.

이 쿼리는 고객 상담센터에서 상담직원이 고객의 핸드폰번호 또는 집 전화번호로 고객을 조회할 때 사용하는 쿼리이다. 

하루 평균 호출횟수는 4000회 정도, 평균 수행시간은 4639 (ms) 였다.

 

쿼리를 그대로 올리는 건 뭔가 찜찜해서 대충 느낌만 살려보면 아래와 같다.

AS-IS 쿼리

SELECT 
     CUSTOMER_NAME
    ,CUSTOMER_CELNO
    ,CUSTOMER_TELNO
    ,CUSTOMER_EMAIL
FROM CUSTOMER
WHERE 1=1
 AND (REPLACE(CUSTOMER_CELNO, '-', '') = :customer_celno
 OR REPLACE(CUSTOMER_TELNO, '-', '') = :customer_telno)

customer_celno와 customer_telno는 각각 어플리케이션에서 입력 받는 핸드폰 번호, 전화 번호이다.

일단 개발자가 이렇게 쿼리를 작성한 이유는 DB에 핸드폰번호와 전화번호가 '010-1234-5678' , '01098765432' 등 일정한 형식 없이 저장되어 있기 때문이다.

아예 DB 상에 모든 컬럼에서 '-' 를 빼서 변수와 비교하는 것이다.

 

이렇게 하면 조회는 되지만 기껏 CUSTOMER_CELNO, CUSTOMER_TELNO로 구성한 인덱스를 타지 못하는 문제가 있다. 

Where 조건 좌측에 함수를 사용하면 멍청한 SSMS 옵티마이저가 적절한 인덱스를 찾지 못하기 때문이다.

 

사실 근본적인 해결방법은 DB 상에 저장된 핸드폰번호와 전화번호를 일정한 형식으로 클렌징하는 것이겠지만,

그 작업을 하기 위해서는 고객을 저장하는 기능이 있는 모든 시스템에서 동일한 형식으로 핸드폰번호(전화번호)를 저장하는 것이 보장되어야하기 때문에 시스템 전수 조사 및 수정을 해야한다.

장기적보면 정말 좋은 해법이지만.. 나는 오늘의 내가 행복해야한다고 굳게 믿는 미천한 개발자이기 때문에 쿼리를 튜닝하기로 한다. 

 

함수를 사용해서 인덱스를 타지 못하는 것이므로 함수를 제거하고, 발생할 수 있는 핸드폰번호 및 전화번호 케이스를 나열하여 IN 조건 안에 넣었다.

TO-BE 쿼리

SELECT 
     CUSTOMER_NAME
    ,CUSTOMER_CELNO
    ,CUSTOMER_TELNO
    ,CUSTOMER_EMAIL
FROM CUSTOMER
WHERE 1=1
 AND CUSTOMER_CELNO IN (
   -- 010-305-0755
   LEFT(3,:customer_celno)+'-'+SUBSTRING(:customer_celno,4,3)+'-'+SUBSTRING(:customer_celno,7,LEN(:customer_celno))
   -- 010-3057-0755
 , LEFT(3,:customer_celno)+'-'+SUBSTRING(:customer_celno,4,4)+'-'+SUBSTRING(:customer_celno,8,LEN(:customer_celno))
   -- 0103050755
 , :customer_celno)
 OR CUSTOMER_TELNO IN (
   -- 02-345-6789
   LEFT(2,:customer_telno)+'-'+SUBSTRING(:customer_telno,3,3)+'-'+SUBSTRING(:customer_telno,6,LEN(:customer_telno))
   -- 02-3456-6789
 , LEFT(2,:customer_telno)+'-'+SUBSTRING(:customer_telno,3,4)+'-'+SUBSTRING(:customer_telno,7,LEN(:customer_telno))
   -- 031-345-6789
 , LEFT(3,:customer_telno)+'-'+SUBSTRING(:customer_telno,4,3)+'-'+SUBSTRING(:customer_telno,7,LEN(:customer_telno))
  -- 02-3456-6789
 , LEFT(3,:customer_telno)+'-'+SUBSTRING(:customer_telno,4,4)+'-'+SUBSTRING(:customer_telno,8,LEN(:customer_telno)) 
  -- 0234566789
, :customer_telno)

 

이렇게 다소 조잡해보이지만 발생할 수 있는 케이스를 모두 나열해서 인덱스를 탈 수 있도록 수정해주었다.

 

SSMS를 사용해서 AS-IS, TO-BE 쿼리의 예상실행계획을 돌려보면 쿼리비용이 각각 93%, 7%로 현저한 차이를 보였고, 배포 후 튜닝된 쿼리의 평균 수행시간을 수집해본 결과 1000 (ms) 정도가 나왔다.

 

이 정도면 만족!

 

그럼 끝.