[튜닝가이드] 인덱스 사용 여부 관련 생각 + [펌]

오랜만에 DB Guide net 에 접속했다.

그 중 Index 사용 여부에 대한 가이드 글을 읽고 느낀점에 대해서 정리해 보았다.

먼저 읽은 글의 본문은 아래와 같다.

이병국의 개발자를 위한 DB 이야기: 튜닝(31회)

개발자를 위한 DB 튜닝 실전(1편)

http://www.dbguide.net/knowledge.db?cmd=view&boardUid=192095&boardConfigUid=19&boardStep=&categoryUid=196

고작 1편보고 왜 작성하느냐...가 아니라 그동안 잠시 잊고 있었던 부분을 다시 짚는 부분과 함께,

예시 쿼리들을 직접 손으로 작성하면서 이렇게 변경해도 되는 구나 ....하기 위해서 작성하는 부분이다.

---

* 인덱스를 사용하는 것은 언제나 옳다???

- 답은 No 이다. 누구나 알겠지만 Full scan이 성능상 더 효과적인 경우가 있기 때문이다.

아래의 쿼리에 대해서 보자

SELECT SUM(CASE WHEN 활동구분 = ‘방문’ THEN 1 ELSE 0 END) AS 방문횟수 

       SUM(CASE WHEN 활동구분 = ‘우편’ THEN 1 ELSE 0 END) AS 우편횟수 

       SUM(CASE WHEN 활동구분 = ‘전화’ THEN 1 ELSE 0 END) AS 전화횟수 

       SUM(CASE WHEN 활동구분 = ‘SMS’ THEN 1 ELSE 0 END) AS SMS건수 

FROM 영업활동                             -- 3000만 건 이상의 대용량 테이블(10년 활동 보관)

WHERE 활동일자 BETWEEN ? AND ?          -- 조회 구간 최대 1년(인덱스 있음)

AND 활동구분 IN (‘방문’, ’우편’, ’전화’, ‘SMS’)

-  테이블 전체 건수의 1/10에 해당하는 300만 건을 추출

위의 쿼리를 인덱스를 사용하면 엄청난 시간이 소요될 수 있다. 하지만 /*+ FULL(영업활동) */  힌트를 사용하여 Full Scan을 하면 더 적게 소요될 수 있다.

이 말은 즉 데이터를 scan 시 Block 단위로 읽게(I/O) 된다. Index 를 이용하게 되면 Random access 를 하게 된다. 하지만 full scan을 하게 되면 Seqential read 하게 된다. 물론 다 그런거는 아니겠지만 대부분 Random access 하게 되면 Single block I/O 가 되며, Seqential read 하게 되면 Multi block I/O를 하게 되어 훨씬 I/O양을 줄일 수 있는 효과를 가져올 수 있다.

물론 이 내용들은 머릿속에서 엇박자로 알고 있는 부분들이라 다시 정리하게 되는 것 같다. (이해는 가지만 또 설명하게 되면 어버버 되는 상황??ㅠ)

자세한 내용은 아래에서 읽으면서 정리하는 것을 추천한다.

- I／O 효율화 원리

http://wiki.gurubee.net/pages/viewpage.action?pageId=29065463

해당 내용을 이해한다면 왜 Index를 사용안하고 Full table scan 이 더 효율적인지 이해할 수 있을 것이다.

---

두번째 내용은 Group by 사용과 성능 이슈 (이 부분은 기존 dbguide.net 내용을 펌~~~)

업무를 진행하다 보면 group by를 많이 사용하게 된다...내 의지와 상관없이...통계 때문일 것이다.

SELECT A.부서코드 

      , B.부서명 

      , SUM(A.판매수량) AS 판매수량 

      , SUM(A.판매금액) AS 판매금액 

FROM 판매실적 A                      -- 1천만 건 이상의 대용량 테이블(10년치 판매 실적)

     , 부서 B                          -- 수백 건 미만의 부서코드 

WHERE A.부서코드 = B.부서코드 

AND A.판매일자 BETWEEN ? AND ?      -- 조회 구간 최대 1주일(인덱스 있음)

GROUP BY A.부서코드, B.부서명 

ORDER BY A.부서코드, B.부서명 

 

여기서는 데이터 구간이 총 데이터의 1/100 , 10만건 이하이기에 인덱스를 활용해야 한다고 한다.

또한, 부서명은 order by 절에 영향을 주지 않으며, 부서 테이블은 오로지 부서명을 조회하는 용도로만 사용한다.

부서 테이블은 부서명 조회 용도이며 부서테이블과 관련한 조건절이 없다.

SELECT A.부서코드 

      , (SELECT 부서명 FROM 부서 WHERE 부서코드 = A.부서코드) AS 부서명 

      , SUM(A.판매수량) AS 판매수량 

      , SUM(A.판매금액) AS 판매금액 

FROM 판매실적 A                       -- 1천만 건 이상의 대용량 테이블(10년치 판매 실적)

WHERE A.판매일자 BETWEEN ? AND ?     -- 조회 구간 최대 1주일(인덱스 있음)

GROUP BY A.부서코드 

ORDER BY A.부서코드 

위와 같이 변경하면 부서 테이블에 대한 접근 빈도수를 최대한 줄일 수 있음.

Group by 절의 수행 이전에 부서 테이블을 접근하였으나 

개선된 쿼리는 group by 절의 수행 이후에 부서 테이블을 접근하므로 접근 빈도수가 대폭 줄어든 효과

---

Order by 절의 선행컬럼에 부서명이 있거나, 조건절에 부서 테이블의 컬럼이 있다면 Outer Join 방식으로 변경 불가능

SELECT A.부서코드 

      , B.부서명 

      , SUM(A.판매수량) AS 판매수량 

      , SUM(A.판매금액) AS 판매금액 

FROM 판매실적 A                        -- 1천만 건 이상의 대용량 테이블(10년치 판매 실적)

     , 부서 B                            -- 수백 건 미만의 부서코드 

WHERE A.부서코드 = B.부서코드 

AND A.판매일자 BETWEEN ? AND ?        -- 조회 구간 최대 1주일(인덱스 있음)

AND B.사용여부 = ‘Y’                     -- 현재 시점에 사용하는 부서코드 

GROUP BY B.부서명, A.부서코드 

ORDER BY B.부서명, A.부서코드

아래와 같이 인라인 뷰 방식의 쿼리로 개선 가능

SELECT A.부서코드 

      , B.부서명 

      , A.판매수량 

      , A.판매금액 

FROM 

(

    SELECT 부서코드 

          , SUM(판매수량) AS 판매수량 

          , SUM(판매금액) AS 판매금액 

    FROM 판매실적                       -- 1000만 건 이상의 대용량 테이블(10년치 판매 실적)

    WHERE 판매일자 BETWEEN ? AND ?    -- 조회 구간 최대 1주일(인덱스 있음)

    GROUP BY 부서코드 

) A, 부서 B                                -- 수백 건 미만의 부서코드 

WHERE A.부서코드 = B.부서코드 

AND B.사용여부 = ‘Y’                      -- 현재 시점에 사용하는 부서코드 

ORDER BY B.부서명, A.부서코드 

부서 테이블에 대한 접근 빈도수를 최대한 줄일 수 있었으며, Group by 절의 수행 이후에 부서 테이블을 접근하는 부분이라 접근 빈도수도 줄어드는 효과

하지만, 최종 집계된 부서의 개수가 부서코드 테이블의 전체 개수의 1/100 이상이라면 아래의 쿼리처럼 힌트절 조정을 통하여 성능 개선을 추가적으로 가능

첫번째 쿼리와 같이 Full 로 변경함으로써 성능 효과 개선 가능

SELECT /*+ FULL(B) */

       A.부서코드 

      , B.부서명 

      , A.판매수량 

      , A.판매금액 

FROM 

(

    SELECT 부서코드 

          , SUM(판매수량) AS 판매수량 

          , SUM(판매금액) AS 판매금액 

    FROM 판매실적                      -- 1000만 건 이상의 대용량 테이블(10년치 판매 실적)

    WHERE 판매일자 BETWEEN ? AND ?   -- 조회 구간 최대 1주일(인덱스 있음)

    GROUP BY 부서코드 

) A, 부서 B                              -- 수백 건 미만의 부서코드 

WHERE A.부서코드 = B.부서코드 

AND B.사용여부 = ‘Y’                    -- 현재 시점에 사용하는 부서코드 

ORDER BY B.부서명, A.부서코드

만약 조건절의 판매일자 조회 구간이 한 달 이상이라면 이번에도 인덱스를 사용치 않는 방법으로 아래와 같이 힌트를 이용해 변경 가능

SELECT /*+ FULL(A) FULL(B) */

       A.부서코드 

      , B.부서명 

      , A.판매수량 

      , A.판매금액 

FROM 

(

    SELECT 부서코드 

          , SUM(판매수량) AS 판매수량 

          , SUM(판매금액) AS 판매금액 

    FROM 판매실적                      -- 1000만 건 이상의 대용량 테이블(10년치 판매 실적)

    WHERE 판매일자 BETWEEN ? AND ?   -- 조회 구간 최대 1주일(인덱스 있음)

    GROUP BY 부서코드 

) A, 부서 B                              -- 수백 건 미만의 부서코드 

WHERE A.부서코드 = B.부서코드 

AND B.사용여부 = ‘Y’                    -- 현재 시점에 사용하는 부서코드 

ORDER BY B.부서명, A.부서코드 

조회 구간이 한달 이상이라면 대규모의 집계 처리를 의미하므로 두 테이블 모두 인덱스를 타는 것보다는 타지 않는 것이 더 선능에 좋을 것.

Group by 절의 어떠한 형식이나 어떠한 조건에 따라서 성능 개선의 방법은 다양하게 다를 수 있다. 

---

2번째 내용들은 이해를 하기보다는 정리 수준이라 쿼리 개발 때 참고해서 설계하는 것이 좋을 듯 싶다.