문제점

개발 TMS 서버에서는 1~2초 걸리는 쿼리가 운영 DB에서는 6초이상 소요됨

원인

단편적으로는 개발서버 데이터가 운영서버 데이터보다 적기 때문,
근본적인 해결 방법으로는 현재 JOIN 대상 테이블들을 FULL SCAN하고 있기 때문에
JOIN 대상 컬럼에 대한 INDEX 생성 필요

대상 쿼리

SELECT A.*
        FROM(
            SELECT D.THEATRE_ID, D.AUDITORIUM_ID
            ,CASE  WHEN (D.SPL_TITLE NOT LIKE '[%]%')  THEN (SELECT E.SPL_TITLE FROM SMS_SPL_DTL E WHERE E.CONTENT_TITLE = D.SPL_TITLE AND E.SPL_TITLE LIKE '[%]%' AND E.AUDITORIUM_ID = 'AU003' ORDER BY E.SPL_TITLE LIMIT 1 )ELSE D.SPL_TITLE  END AS SPL_TITLE
            ,CASE WHEN (B.SMS_CPL_ENCRYPTED_VIDEO = 'true') AND (B.SMS_CPL_IS_PLAYABLE = 'true') THEN 'ACTIVE' 
                WHEN (B.SMS_CPL_ENCRYPTED_VIDEO = 'true') AND (B.SMS_CPL_IS_PLAYABLE = 'false') THEN 'INACTIVE'
                WHEN (B.SMS_CPL_ENCRYPTED_VIDEO = 'false') AND (B.SMS_CPL_IS_PLAYABLE = 'true') THEN 'NOT_ENCRYPTED' ELSE '' END AS KEY_STATUS
            ,B.SMS_CPL_ID, B.SMS_CPL_ENCRYPTED_VIDEO, B.NOT_VALID_AFTER
            ,B.SMS_CPL_DIMENSION
            , SMS_CPL_RUNNUNG_TIME
            FROM SMS_CPL B
            LEFT JOIN (SELECT * FROM SMS_SPL_DTL) D
            ON D.CONTENT_ID = B.SMS_CPL_ID AND D.AUDITORIUM_ID = B.AUDITORIUM_ID
            WHERE B.AUDITORIUM_ID = 'AU003'
            AND (B.SMS_CPL_RUNNUNG_TIME/1000) >= 2400
            ) A
            WHERE A.SPL_TITLE IS NOT NULL;

인덱스 생성 전 실행 계획

JOIN되고 있는 SMS_CPL / SMS_SPL_DTL 모두 FULL SCAN을 하고 있다.

인덱스 생성

• 1. SMS_CPL 테이블은 AUDITORIUM_ID를 where 조건으로 AUDITORIUM_ID 를 사용하고 있고, 또 ON 조건으로 SMS_CPL_ID 를 사용하고 있다.

따라서 AUDITORIUM_ID 를 leading 컬럼으로 하여 인덱스 생성 필요

CREATE INDEX SMS_CPL_X1 ON SMS_CPL ( AUDITORIUM_ID , SMS_CPL_ID );

• SMS_SPL_DTL 테이블은 JOIN 이되는 ON 조건에서 AUDITORIUM_ID와 CONTENT_ID 컬럼을 사용하고 있다.

  CREATE INDEX SMS_SPL_DTL_X1 ON SMS_SPL_DTL ( AUDITORIUM_ID , CONTENT_ID );

• 또한 SMS_SPL_DTL CASE문 sub query에서 AUDITORIUM 와 CONTENT_TITLE 컬럼을 사용하고 있다.

  CREATE INDEX SMS_SPL_DTL_X2 ON SMS_SPL_DTL ( AUDITORIUM_ID , CONTENT_TITLE );

인덱스 생성 후 실행계획

• 두 테이블 모두 WHERE 조건과 ON 조건에서 INDEX를 사용해서 JOIN 됨을 볼 수 있다.

추가 내용

• SMS_SPL_DTL 테이블의 경우 AUDITORIUM, CONTENT_ID, CONTENT_TITLE 컬럼 모두 ' = ' 조건으로 JOIN 되고 있다. 이 때 컬럼의 Cardinality가 클수록 검색 성능이 향상 될 수 있기 때문에, AUDITORIUM 보다는 CONTENT_ID, CONTENT_TITLE 을 leading 컬럼으로 생성하는 것도 좋아 보인다.