오라클 like 슬로우 쿼리 개선

개요

---

운영하는 서비스에서 간혹 슬로우 쿼리가 발생하여 DBA로부터 연락이 오는 경우가 있었는데, 운영팀과 협의하여 아파치 유입값 중 10초 이상의 지연이 발생하는 건에 대해 메신저를 통해 알람을 받도록 설정을 해 둔게 있었다.

서비스 운영 중 위와 같은 알림을 받게 되었고 해당 로그를 추적해본 결과, 발송 결과를 조회하는 특정 케이스에 슬로우 쿼리가 발생함을 인지할 수 있었다.

 

원인 파악

---

현재 운영중인 서비스에는 핀포인트라는 모니터링 툴이 연동되어 있는데 해당 툴을 통해 체크해본 결과, 슬로우 쿼리가 발생한 쿼리를 특정할 수 있었다.

슬로우 쿼리가 발생했던 쿼리는 대략 아래와 같다 (실제 쿼리를 바탕으로 임의로 작성한 쿼리임).

# 수행시간 대략 20초
SELECT *
FROM message_batch mb
LEFT JOIN message_content mc ON mb.batch_id = mc.batch_id
WHERE mb.user_id = 1234
  AND mb.status IN ('SENT', 'COMPLETED')
  AND mb.batch_id IN (
        SELECT batch_id
        FROM recipient_list
        WHERE user_id = 1234
          AND phone_number like '%' || '01011112222' || '%'
      );

해당 유저가 발송한 내역 중 수신자의 번호로 필터링하여 결과를 보여주는 쿼리였는데, 수신번호 전부가 아닌 일부를 입력해도 결과를 도출할 수 있도록 like문을 사용한 부분이 슬로우 쿼리를 유발했다. 참고로 recipient_list 테이블은 해당 유저만 550만건의 데이터가 있었으며 전체 유저로 보았을 때 약 1억건까지의 데이터를 보유한 테이블이었다.

 

Like

Oracle에서 와일드카드 문자(%, _)를 사용하는 LIKE 조건은, 특히 앞에 %가 붙는 경우(예: LIKE '%abc')에는 인덱스를 사용할 수 없어 Full Table Scan이 발생한다. 또한 LIKE는 문자열 비교 시 대소문자 민감도, NLS 설정, 와일드카드 해석 로직 등 복잡한 연산이 포함되어 있어, CPU 사용량이 많아지고 성능 저하로 이어질 수 있다. 대량의 데이터가 존재하는 테이블에서 사용할 경우 특히 주의가 필요하다.

 

Instr

INSTR 함수는 문자열 내에서 특정 문자열의 위치(index)를 반환하는 Oracle의 내장 함수로, 단순한 C 수준의 문자열 비교 연산만 수행한다. 이 함수 역시 일반적인 B-Tree 인덱스를 사용할 수는 없지만, LIKE와 비교했을 때 불필요한 문자열 처리 로직이 없어 CPU 연산량이 적고 처리 속도가 더 빠르다. 결과적으로 LIKE '%xxx%'와 유사한 기능을 하면서도, 성능상 이점이 있는 대체 수단이 될 수 있다.

 

문제 해결

---

위 문제가 되었던 쿼리를 보았을 때, 요구사항은 특정 문자열의 포함 여부만 조건으로 걸면 되는것이었으므로 like 문을 instr문으로 변경하여 수행시간을 비교해 보았다.

SELECT *
FROM message_batch mb
LEFT JOIN message_content mc ON mb.batch_id = mc.batch_id
WHERE mb.user_id = 1234
  AND mb.status IN ('SENT', 'COMPLETED')
  AND mb.batch_id IN (
        SELECT batch_id
        FROM recipient_list
        WHERE user_id = 1234
          AND INSTR(phone_number, '01022221111') > 0
      );

위와 같이 수정한 결과, 쿼리 수행시간이 기존 약 20초에서 7초로 개선이 되었음을 확인할 수 있었다.