개발자를 위한 DB 튜닝 실전(1편)
성공과 실패의 경험을 나누자, 용기와 희망을 나누자
개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB엔지니어로서 활동하면서 얻은 경험과 지식을나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서DB 분야에 입문한지 1~2년 된 기입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.
전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면준전문가 수준의 DB 원리를 아는 것을 목표로 한다.
인덱스 사용하지 않고 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’)
위의 쿼리에서 우리가 알 수 있는 내용은 영업활동 테이블은 3000만 건 이상의 대용량 테이블이며, 조건절로 사용되는 활동일자 컬럼에 인덱스가 있다는 사실이다. 여기에서 우리는 조회 기간이 최대 1년이라는 사실에 주목해야 한다. 테이블 전체 건수의 1/10에 해당하는 300만 건을 추출해야 하는 것이다. 만약 이 쿼리에서 인덱스가 사용된다면 그 결과는 재앙에 가깝다. 쿼리 수행 시간은 아마도 수시간 이상 소요될 수도 있을 것이다.
인덱스는 소량의 데이터를 빠르게 조회 가능하게 할 수 있지만, 대량의 데이터를 빠르게 조회할 수 있게 하지는 못한다. 인덱스의 생성 목적은 ‘모든 데이터를 빨리 찾게 하는 것이 아니라 필요한 데이터를 빨리 찾게 하는 것’임을 잊지 말아야 한다. 요약한다면 인덱스는 성능 향상의 만능키가 아니며 또한 그렇게 인식되어서도 안 된다.
위의 퀴리에서 인덱스를 사용하지 못하게 아래와 같이 힌트절을 추가한다면 우리는 조회 결과를 수 분내에 얻을 수 있을 것이다.
SELECT /*+ FULL(영업활동) */ 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 영업활동 -- 3천만 건 이상의 대용량 테이블(10년 활동 보관) WHERE 활동일자 BETWEEN ? AND ? -- 조회 구간 최대 1년(인덱스 있음) AND 활동구분 IN (‘방문’, ’우편’, ’전화’, ‘SMS’)
그렇다면 우리는 어떠한 경우에 인덱스를 타게 할 건지 혹은 타지 않게 할 건지 결정하는 기준이 무엇인지 궁금할 것이다. 필자의 경우에는 전체 건수에 대해 조회할 건수가 1/100보다 작으면 인덱스를 타게 하고 크면 인덱스를 타지 않게 힌트절 FULL SCAN을 사용한다. 이 수치는 여러 주변 환경과 변수들이 많기 때문에 고정적이지는 않지만, 필자가 권고하는 수치이다. 또한 필자가 경험으로부터 얻은 수치임을 이해해 주기 바란다.
어떠한 경우에 인덱스를 타야하는지 또한 어떠한 경우에 인덱스를 타지 말아야 하는지에 대한 보다 구체적인 이해는 선행 연재인 ‘인덱스와 블록’에 관한 글을 먼저 읽어 보기를 권한다. 이번 연재에서는 아래의 그림처럼 버스와 택시의 예로서 설명을 마무리 한다.
[그림 1] 버스 여행과 택시 여행
만약 서울에서 부산까지 여행을 해야 한다면 우리는 버스보다는 택시가 더 빠르고 편하다는 것을 안다. 2명 혹은 3명까지도 버스보다는 택시가 더 빠를 것이다. 하지만 4명 이상부터는 이야기가 달라진다. 많은 인원이 여행을 해야 한다면 버스 한 대로 한꺼번에 이동하는 것이 시간과 비용 측면에서 휠씬 유리하다. 버스 한 대로 한 번에 운행 가능한 것을 택시 한 대로 여러 번 반복 운행 하는 것은 이치에 맞지 않다. 택시(인덱스)는 소규모 인원(데이터)의 운행(검색)에 적합한 운송 수단이다.
GROUOP BY 절의 사용과 성능 이슈
GROUP BY 절의 사용 방법에 따라 튜닝 성능에 영향을 미치는 경우가 많다. 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주일이므로 최대 조회 건수는 수만 건 가량 됨을 알 수 있다. 판매실적 테이블 전체 건수의 1/100, 즉 10만 건 미만에 해당하므로 인덱스는 반드시 타야 한다. 또한 부서명은 ORDER BY 절에 영향을 미치지 않으므로, 부서 테이블은 오로지 부서명을 조회하는 용도로만 사용됨을 알 수 있다. 그리고 부서 테이블과 관련된 조건절이 없으므로 OUTER JOIN으로?? 아래와 같은 쿼리로 개선할 수 있다.
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 절의 수행 이전에 부서 테이블을 접근하였으나 개선된 쿼리는 GROUP BY 절의 수행 이후에 부서 테이블을 접근 하므로 접근 빈도수가 대폭 줄어드는 만큼 효과를 얻었다.
그러나 이것이 성능 개선의 끝이 아니다. 만약 최종 집계된 부서의 개수가 부서코드 테이블의 전체 개수의 1/100이상이라면 우리는 아래의 쿼리처럼 힌트절 조정을 통하여 성능 개선을 추가적으로 이뤄 낼 수 있다(이번 연재의 첫 번째 튜닝 실전과 같은 맥락).
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 절의 어떠한 형식이나 어떠한 조건에 따라서 성능 개선의 방법은 다양하게 다를 수 있다. 튜닝은 살아있는 생명체를 대하듯 그때 그때 상황에 맞게 올바르게 대처해야 한다.
이번 연재에 이어서 앞으로 수회에 걸쳐서 필자가 경험한 튜닝 실전 시리즈를 연재할 것이다(튜닝 실전 시리즈 연재 기간 동안은 문제 풀이 및 정답 부분은 잠시 중단합니다). (다음 회에 계속)