[DB] Query 작성 시 유의사항 - Noah Development Note

Intro

안녕하세요 Noah입니다

오늘은 팀원이 작성한 쿼리 중 내부적으로 이슈가 된 쿼리가 있어 해당 쿼리를 분석해보려 합니다.

어떤 쿼리인지 살펴보고 일반 개발자분들이 DBA처럼 완벽한 쿼리 튜닝은 할 수 없을지라도 최소한의 주의사항은 지키면서 쿼리를 작성하자는 마음으로 글을 쓰게 되었습니다.

쿼리 예시

기능은 앱 시청기록 메뉴 접근 시 내가 시청했던 영상에 대한 시청 기록과 연관된 데이터 리스트를 받는 것입니다.
어떤 페이지든 성능을 고려해야겠지만 해당 페이지는 유저들의 이용량이 많을 것으로 판단되어 특히 신경을 써야 하는 페이지입니다.
(아래 코드는 실제 작성되어 있는 것에서 컬럼명 및 테이블명만 수정하여 그대로 가져온 것입니다.)

SELECT A.*
     ,(SELECT IF(DEL_YN='N',true,false)
       FROM USER_CLASS
       WHERE USER_UID = '유저아이디'
         AND CLASS_ID=A.CLASS_ID
         AND RELATION =13) AS bookMark /* TRUE일경우 북마크설정, FLASE일경우 북마크해제 */
FROM (select TMP.*
           ,IFNULL(UC2.EVALUATION_SCORE,0)  AS EVALUATION_SCORE
           ,IFNULL(UC2.EVALUATION_USER_CNT, 0) AS EVALUATION_USER_CNT
           ,C.CLASS_NM AS classTitle
           ,(TIME_TO_SEC(TMP.PLAYED_TIME)/TIME_TO_SEC(C.LEN))*100 as playedPercent
           ,C.CLASS_CATEGORY AS classWorkoutCategoryCode
           ,C.LIVE_JOB_ID AS liveId
           ,C.CONTENTS_TYPE
           ,C.VOD_ENTRY_ID AS videoId
           ,C.CREATE_DATETIME
           ,C.CLASS_LEVEL AS classLevelCode
           ,C.PAID_FREE_TYPE AS PAID_FREE_GUBUN
           ,C.THUMBNAIL_S3_FILE_URL
           ,C.LEN
           ,(SELECT GROUP_CONCAT(L.LECTURE_NAME)
             FROM CLASS_LECTURE CL,LECTURE L
             WHERE CL.CLASS_ID=TMP.CLASS_ID
               AND CL.LECTURE_ID=L.LECTURE_ID
               AND L.DEL_YN='N'
               AND CL.DEL_YN='N') AS lectureName
           ,(SELECT COUNT(1)
             FROM VIEWING_HISTORY VH
             WHERE VH.CLASS_ID=TMP.CLASS_ID
    ) AS CLASSVIEWCNT
      from (SELECT VH.CLASS_ID
                 ,VH.WATCH_START_DATETIME
                 ,VH.WATCH_END_DATETIME
                 ,TIME_TO_SEC(VH.PLAYED_TIME) AS PLAYED_TIME
            from VIEWING_HISTORY VH
            WHERE VH.USER_UID= '유저아이디'
              AND VH.WATCH_END_DATETIME IS NOT NULL
            order by VIEWING_HISTORY_ID desc
            LIMIT 18446744073709551615)  TMP , CLASS C
                                                   LEFT JOIN  (SELECT   CLASS_ID
                                                                    ,IF(COUNT(1)=0,0 ,TRUNCATE(AVG(EVALUATION_CNT),1))  AS EVALUATION_SCORE
                                                                    ,COUNT(CLASS_ID) AS EVALUATION_USER_CNT
                                                               FROM USER_CLASS
                                                                 AND DEL_YN ='N'
                                                               GROUP BY CLASS_ID  ) UC2
                                                              ON C.CLASS_ID = UC2.CLASS_ID
         ,CLASS_LECTURE CL,LECTURE L
      WHERE TMP.CLASS_ID=C.CLASS_ID
        AND C.CONTENTS_TYPE = 'VV'
        AND C.CLASS_ID = CL.CLASS_ID
        AND CL.LECTURE_ID = L.LECTURE_ID
        AND L.DEL_YN='N'
      GROUP BY TMP.CLASS_ID
      LIMIT 10) A
ORDER BY A.WATCH_END_DATETIME DESC

분석

큰 틀에서부터 위 쿼리를 하나씩 분석해 보도록 하겠습니다.

1차 튜닝

쿼리가 라인 정리 및 대소문자 정리가 되어있지 않아 보는 것 자체가 어려움
코드 분석 전 우선 코드를 분석하는 것 자체가 어렵다는 것이 가장 큰 단점으로 보입니다.
저라면 위 쿼리를 아래 쿼리와 같이 정리할 것 같습니다.
(더 좋은 정리 방법이 있다면 공유해 주세요 ^^)

     SELECT A.*
     FROM (
           SELECT TMP.*
                , IFNULL(UC2.EVALUATION_SCORE,0)  AS EVALUATION_SCORE
                , IFNULL(UC2.EVALUATION_USER_CNT, 0) AS EVALUATION_USER_CNT
                , C.CLASS_NM AS CLASSTITLE
                , (TIME_TO_SEC(TMP.PLAYED_TIME)/TIME_TO_SEC(C.LEN))*100 AS PLAYEDPERCENT
                , C.CLASS_CATEGORY AS CLASSWORKOUTCATEGORYCODE
                , C.LIVE_JOB_ID AS LIVEID
                , C.CONTENTS_TYPE
                , C.VOD_ENTRY_ID AS VIDEOID
                , C.CREATE_DATETIME
                , C.CLASS_LEVEL AS CLASSLEVELCODE
                , C.PAID_FREE_TYPE AS PAID_FREE_GUBUN
                , C.THUMBNAIL_S3_FILE_URL
                , C.LEN
           , (
              SELECT GROUP_CONCAT(L.LECTURE_NAME)
              FROM   CLASS_LECTURE CL, LECTURE L
              WHERE  CL.CLASS_ID = TMP.CLASS_ID
                AND  CL.LECTURE_ID = L.LECTURE_ID
                AND  L.DEL_YN = 'N'
                AND  CL.DEL_YN = 'N'
             ) AS LECTURENAME
           , (
              SELECT COUNT(1)
              FROM   VIEWING_HISTORY VH
              WHERE  VH.CLASS_ID=TMP.CLASS_ID
             ) AS CLASSVIEWCNT
           FROM (
                 SELECT VH.CLASS_ID
                      , VH.WATCH_START_DATETIME
                      , VH.WATCH_END_DATETIME
                      , TIME_TO_SEC(VH.PLAYED_TIME) AS PLAYED_TIME
                 FROM   VIEWING_HISTORY VH
                 WHERE  VH.USER_UID = '유저아이디'
                   AND  VH.WATCH_END_DATETIME IS NOT NULL
                 ORDER BY VIEWING_HISTORY_ID DESC
                 LIMIT  18446744073709551615
           ) TMP
           , CLASS C
           LEFT JOIN (
                 SELECT CLASS_ID
                      , IF(COUNT(1)=0,0 ,TRUNCATE(AVG(EVALUATION_CNT),1)) AS EVALUATION_SCORE
                      , COUNT(CLASS_ID) AS EVALUATION_USER_CNT
                 FROM   USER_CLASS
                   AND  DEL_YN = 'N'
                 GROUP BY CLASS_ID
           ) UC2
           ON C.CLASS_ID = UC2.CLASS_ID
           , CLASS_LECTURE CL
           , LECTURE L
           WHERE TMP.CLASS_ID = C.CLASS_ID
             AND C.CONTENTS_TYPE = 'VV'
             AND C.CLASS_ID = CL.CLASS_ID
             AND CL.LECTURE_ID = L.LECTURE_ID
             AND L.DEL_YN = 'N'
           GROUP BY TMP.CLASS_ID
           LIMIT 10
     ) A
     LEFT OUTER JOIN (
           SELECT IF(DEL_YN='N',TRUE,FALSE) AS BOOKMARK
                , CLASS_ID
           FROM   USER_CLASS
           WHERE  USER_UID = '유저아이디'
             AND  DEL_YN = 'N'
     ) B
     ON A.CLASS_ID = B.CLASS_ID
     ORDER BY A.WATCH_END_DATETIME DESC;

스칼라 서브쿼리를 활용하기 때문에 영상 종류가 많아질수록 성능이 떨어질 수밖에 없음
스칼라 서브쿼리 사용 지양
```
   SELECT A.*
      ,(SELECT IF(DELETE_YN='N',true,false)
        FROM USER_MY_CLASS
        WHERE USER_ID = '유저아이디'
          AND CLASS_ID=A.CLASS_ID) AS bookMark
```
이런 식으로 SELECT절 안에 ‘스칼라 서브쿼리’를 사용하게 되면 FROM절에서 ‘인라인뷰형태’로 사용하는 것과는 다른 작동방식을 보입니다.
쿼리는 먼저 FROM절에 있는 Query문을 기반으로 데이터를 모두 조회한 뒤 SELECT절에 작성되어진 양식으로 값을 뿌려주게 됩니다.
이 때 SELECT절 안에 또다시 서브쿼리가 있다면 해당 서브쿼리를 별도로 실행하는 작업이 2차적으로 필요해지는 것입니다.
문제는 이런 현상이 한 ROW를 조회할 때마다 반복되기 때문에 자주 조회하는 페이지일수록, 데이터량이 많아질 수록 성능이 당연히 떨어질 수 밖에 없습니다.
(RDBMS에서 지원하는 캐싱에 대한 내용을 나중에 알아보겠습니다.)
인라인뷰로 변경 시
```
   SELECT A.*
        , IFNULL(B.BOOKMARK, 0) AS BOOKMARK
        -- (중략)
        GROUP BY TMP.CLASS_ID
        LIMIT 10
   ) A
   LEFT OUTER JOIN (
         SELECT IF(DEL_YN='N',true,false) AS BOOKMARK
               ,CLASS_ID
         FROM USER_CLASS
         WHERE USER_UID = '유저아이디'
   ) B
   ON A.CLASS_ID = B.CLASS_ID
   ORDER BY A.WATCH_END_DATETIME DESC
```
위와 같이 작성을 하게 되면 최초 FROM을 통해 데이터를 가져올 당시에 한번에 데이터를 가져와 BOOKMARK 값을 뿌려주기 때문에 2차적인 조회를 하지 않게 됩니다.
불필요한 값을 서브쿼리로 호출하는 행위

기존 쿼리

기존 쿼리 내용을 살펴보면 유저의 최근 시청 기록에 대한 데이터를 각 영상별로 정제해서 가져오도록 조건이 설정된 것이 아니라 시청했던 모든 내역을 불필요하게 조회하고 있었습니다.
하지만 이런 식으로 쿼리 조회 시 불필요한 데이터 ROW들을 상위 쿼리에서도 관리해야 하기 때문에 자원 낭비가 발생하게 됩니다. 때문에 자원을 효율적으로 사용할 수 있도록 조건절을 설정할 필요가 있습니다.

변경한 쿼리

변경한 쿼리에서는 기존 쿼리와 달리 각 영상별 유저의 가장 최근 시청 기록만을 조회하도록 변경하였습니다.

2차 튜닝

1차 튜닝은 기존 쿼리에 대해 내용적으로 문제가 있던 것을 지적했다면 2차 튜닝은 아예 서브쿼리 자체를 줄여야 한다는 점을 짚고 넘어가려 합니다. MySQL은 기본적으로 Join으로 연결하는 것이 서브쿼리를 사용하는 것보다 더 좋은 성능을 보입니다. 5.6버전부터는 서브쿼리 성능 최적화가 이루어졌지만 적용 안되는 조건들이 다수 존재합니다.
이와 같은 점을 고려하여 다음과 같은 쿼리로 위 쿼리를 변경할 수 있습니다.

    SELECT VH.USER_UID
         , VH.CLASS_ID
         , VH.WATCH_START_DATETIME
         , VH.WATCH_END_DATETIME
         , TIME_TO_SEC(VH.PLAYED_TIME) AS PLAYED_TIME
         , IFNULL(UC2.EVALUATION_SCORE,0)  AS EVALUATION_SCORE
         , IFNULL(UC2.EVALUATION_USER_CNT, 0) AS EVALUATION_USER_CNT
         , C.CLASS_NM AS classTitle
         , (TIME_TO_SEC(VH.PLAYED_TIME)/TIME_TO_SEC(C.LEN))*100 as playedPercent
         , C.CLASS_CATEGORY AS classWorkoutCategoryCode
         , C.LIVE_JOB_ID AS liveId
         , C.CONTENTS_TYPE
         , C.VOD_ENTRY_ID AS videoId
         , C.CREATE_DATETIME
         , C.CLASS_LEVEL AS classLevelCode
         , C.PAID_FREE_TYPE AS PAID_FREE_GUBUN
         , C.THUMBNAIL_S3_FILE_URL
         , C.LEN
         , GROUP_CONCAT(L.LECTURE_NAME ORDER BY LECTURE_NAME) AS lectureName
         , VH2.CNT AS CLASSVIEWCNT
         , CASE WHEN UC3.DEL_YN ='N' THEN TRUE ELSE FALSE END bookMark
    FROM VIEWING_HISTORY VH
    INNER JOIN CLASS C
         ON VH.CLASS_ID = C.CLASS_ID 
        AND C.CONTENTS_TYPE = 'VV'
    INNER JOIN (
        SELECT USER_UID, CLASS_ID, MAX(VIEWING_HISTORY_ID) MAX_VIEWING_HISTORY_ID
        FROM VIEWING_HISTORY A
        WHERE A.WATCH_END_DATETIME IS NOT NULL
        GROUP BY A.USER_UID, CLASS_ID
    ) A
         ON VH.USER_UID = A.USER_UID 
        AND VH.VIEWING_HISTORY_ID = A.MAX_VIEWING_HISTORY_ID
    INNER JOIN (
        SELECT CLASS_ID
             , COUNT(1) AS CNT
        FROM VIEWING_HISTORY vh
        GROUP BY CLASS_ID
    ) VH2
        ON VH.CLASS_ID  = VH2.CLASS_ID
    LEFT OUTER JOIN  (
        SELECT USER_UID
             , CLASS_ID
             , IF(COUNT(1)=0,0 
             , TRUNCATE(AVG(EVALUATION_CNT),1)) AS EVALUATION_SCORE
             , COUNT(CLASS_ID) AS EVALUATION_USER_CNT
        FROM USER_CLASS
        WHERE RELATION='12'
          AND DEL_YN ='N'
        GROUP BY USER_UID, CLASS_ID
    ) UC2
         ON VH.CLASS_ID = UC2.CLASS_ID 
        AND VH.USER_UID = UC2.USER_UID
    LEFT OUTER JOIN USER_CLASS UC3
         ON VH.USER_UID = UC3.USER_UID 
        AND VH.CLASS_ID = UC3.CLASS_ID 
        AND UC3.RELATION ='13'
    INNER JOIN CLASS_LECTURE CL
         ON VH.CLASS_ID = CL.CLASS_ID 
        AND CL.DEL_YN ='N'
    INNER JOIN LECTURE L
         ON CL.LECTURE_ID = L.LECTURE_ID 
        AND L.DEL_YN='N'
    WHERE VH.USER_UID ='유저 아이디'
    GROUP BY VH.CLASS_ID

위와 같이 쿼리를 변경함으로써 서브 쿼리를 많이 제외한 것을 보실 수 있습니다.

부가 효과 01. 자원 낭비 감소

12번의 테이블 조인이 발생하던 기존 쿼리

조인회수 감소

위 내용을 보시면 기존과 비교하여 테이블 조인 회수 감소 및 테이블 조인 시 Full Scan이 발생하지 않도록 조치된 것을 보실 수 있습니다.
부가 효과 02. 협업 가능 이 외에도 테이블 조인 시 쿼리 작성 방법도 기존 쿼리는 MSSQL, Oracle, MySQL이 혼재되었던 것에 비해 현재는 MySQL 방식으로 통일하여 다른 개발자들과 업무적으로 협업하기에도 더 효과적인 것을 알 수 있습니다.

글을 마치며

이번에 글을 작성하며 동일한 결과값을 가져오더라도 서버단에서 처리할 것인지 DB단에서 처리할 것인지를 결정해야 하고 DB단에서 조건을 처리하기로 결정하였으면 어떤 방식으로 처리하는 것이 가장 효과적으로 처리하는 것인지에 대해 고찰할 수 있었다는 것이 좋았습니다.

개발 실력이 늘어가는 것은 경력이 쌓이며 자연스럽게 늘어가는 것도 있겠지만 이렇게 주변 사람들과 소통하고 더 나은 방법을 찾기 위해 토론하는 경험을 통해 혼자 공부하는 것보다 더 재밌고 빠르게 늘 수 있다는 것을 알 수 있었습니다.

여러분도 쿼리 튜닝에 대해 알고 있으신 지식이 있다면 여기서 공유하시면서 댓글로 같이 공유해 주시면 감사하겠습니다.

이상으로 마치겠습니다. 긴 글 읽어주셔서 감사드립니다. ^^