쿼리 성능 최적화(EXISTS, JOIN)

 

튜닝해야할 쿼리

 

최근 아래와 비슷한 쿼리의 성능이 너무 좋지 않아 Time out이 발생하여, 튜닝을 하게 되었다.

-- 변경 전
SELECT SUM(DS.CNT)
FROM (
    SELECT COUNT(1) AS CNT
    FROM USER_MAILBOX UM
    INNER JOIN USER_MAIL M ON UM.MAILBOX_ID = M.MAILBOX_ID
    INNER JOIN USER_MAIL_MESSAGE MSG ON M.MSG_ID = MSG.MSG_ID
    WHERE 1 = 1
    AND EXISTS (
        SELECT 'Y'
        FROM USER_MAIL_RECIPIENT R
        WHERE R.MSG_ID = M.MSG_ID
        AND M.MAILBOX_ID = '1234'
        AND R.RECIPIENT LIKE CONCAT('%', '철수', '%')
    )
    AND M.MAILBOX_ID = '1234'
    AND M.MAIL_DEL_YN = 'N'
) DS;

 

 

 

느린 이유

1.  EXISTS절이 효율적인 경우는 보통 조건을 만족하는 첫번째 레코드를 찾는 경우이다. 대량의 데이터가 있을 때 특정 조건을 만족하는지만 확인할 경우에는 효율적이지만, 조건을 만족하는 모든 레코드를 가져오는 경우에는 비효율적이다.
   
   
2.  EXISTS를 사용하여 서브쿼리를 실행하는데, 이는 메일과 관련된 수신자가 존재하는지를 매번 확인하는 방식이다. 이 방식은 특정 조건을 만족하는지를 체크하기 위해 서브쿼리를 반복적으로 실행해야 하므로 비효율적이다.
   
   
3.  보통 데이터베이스 엔진은 서브쿼리 내의 EXISTS는 인덱스를 활용하지 못하는 경우가 있다.

 

 

수정한 쿼리

 

INNER JOIN을 이용하여 아래와 같이 수정하였다.

SELECT SUM(DS.CNT)
FROM (
    SELECT COUNT(1) AS CNT
    FROM USER_MAILBOX UM
    INNER JOIN USER_MAIL M ON UM.MAILBOX_ID = M.MAILBOX_ID
    INNER JOIN USER_MAIL_MESSAGE MSG ON M.MSG_ID = MSG.MSG_ID
    INNER JOIN USER_MAIL_RECIPIENT R ON M.MSG_ID = R.MSG_ID
    WHERE 1 = 1
      AND M.MAILBOX_ID = '1234'
      AND R.RECIPIENT LIKE '%철수%'
      AND M.MAIL_DEL_YN = 'N'
) DS;

INNER JOIN을 통해 필요한 모든 데이터를 한 번에 가져오게 수정하였다. 조인 조건을 바탕으로 인덱스를 효율적으로 사용할 수 있으므로 조인된 결과에서 불필요한 레코드를 제거하거나 필요한 데이터만 가져올 수 있을 것으로 생각되었다.

결과적으로 기존에 Time out이 나던 쿼리가 비교적  빠른 시간 내에 실행되었다.

 

 

 

EXISTS를 사용하는 경우

 

다음과 같은 경우에 EXISTS은 효율적일 수 있다.

1. 대량의 데이터를 확인할 때 특정 조건만 확인하는 경우
   EXISTS는 조건을 만족하는 첫 번째 레코드를 찾으면 즉시 처리를 종료한다. 따라서, 데이터 집합이 매우 크고 조건을 만족하는 레코드가 존재하는지만 확인하면 될 때  효율적이다.
   
   
2. 조건에 만족하는 첫번째 레코드만 찾는 경우
   위에서 언급되었듯이 EXISTS는 조건을 만족하는 첫 번째 레코드를 찾으면 즉시 처리를 종료하기 때문에 이런 경우 효율적이다.

 

반면에 조인된 테이블에서 모든 데이터를 가져와야 하는 경우나, 조건을 만족하는 모든 레코드를 가져와야 할 때는 EXISTS보다는 조인을 사용하는 것이 더 효율적일 수 있다.