개발자를 위한 튜닝 실전(2편)
성공과 실패의 경험을 나누자, 용기와 희망을 나누자
개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서DB 분야에 입문한지 1~2년 된 기입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.
전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.
인덱스 생성도를 이용한 튜닝 방법
온라인 쿼리에서의 튜닝은 대부분 인덱스와 연관되어 있다. 쿼리 성능과 가장 밀접한 관계가 있는 것이 바로 인덱스다. 인덱스의 올바른 생성 규칙과 관련해서는 지난 연재인 ‘인덱스 끝장리뷰’를 참고하기 바란다. 이번 연재는 인덱스 생성도를 통한 튜닝 방법에 대하여 설명 하고자 한다. 필자의 경험을 따르면 온라인 쿼리의 튜닝은 대부분 인덱스 생성도만으로 해결 가능하였다.
인덱스 생성도는 원과 선과 점으로 구성되는데 간략히 설명하면 다음과 같다.
인덱스 생성도를 활용하면 다음과 같은 여러 가지 이점이 있다.
1. 인덱스 생성도는 복잡한 쿼리를 단순하게 보여준다.
2. 인덱스 생성도는 테이블의 접근 방향과 순서를 쉽게 알 수 있다.
3. 오라클 플랜을 이해할 수 있고 더 나아가 올바른 플랜을 수립할 수 있다.
4. 인덱스를 생성해야 할 위치를 알 수 있으며 인덱스 구성 컬럼을 알 수 있다. 
성별 컬럼의 분포도는 50%이며, 나이 컬럼의 분포도는 1% 내외이다. 따라서 성별과 나이 컬럼은 인덱스 컬럼으로 사용하기에는 부적합하다. 그러므로 조건절에 사용되는 컬럼 중에서 분포도가 좋은 고객명 컬럼만 인덱스 생성도에 표시하였다. 현재 고객명 컬럼은 인덱스가 없으므로 인덱스 생성이 필요함을 알 수 있다. 
성별 컬럼의 분포도는 50%이며 나이 컬럼의 분포도는 1% 내외이다. 따라서 성별과 나이 컬럼은 인덱스 컬럼으로 사용하기에는 부적합하다. 그러므로 조건절에 사용되는 컬럼 중에서 분포도가 좋은 고객명 컬럼만 인덱스 생성도에 표시하였다. 조인절에서 사용하고 있는 고객번호 컬럼은 분포도의 좋고 나쁨을 떠나서 무조건 표시해야 한다. 위의 인덱스 생성도를 보면 쿼리에서 사용되는 인덱스 컬럼을 쉽게 알 수 있다. 바로 고객명(고객.고객명)과 고객번호(주문.고객번호)다. 그리고 고객테이블, 주문테이블 순으로 접근함을 알 수 있다. 
조건절에서 사용하는 컬럼 중에서 분포도가 좋은 고객명 컬럼과 주문일자 컬럼만 인덱스 생성도에 표시하였다. 조인절에서 사용하고 있는 고객번호 컬럼은 분포도의 좋고 나쁨을 떠나서 무조건 표시해야 한다. 위의 인덱스 생성도를 보면 고객명 조건절을 통하여 고객 테이블을 먼저 접근하고, 이후 고객번호 조인절에 의해서 주문 테이블을 접근함을 알 수 있다. 주문테이블의 주문일자 컬럼은 인덱스가 없기 때문에 컬럼값에 의한 필터만 발생한다. 
만약 위의 인덱스 생성도처럼 조건절에서 사용하는 컬럼 중에서 분포도가 좋은 주문일자 컬럼에 인덱스를 추가로 생성한다면 어떠한 문제가 발생할지 생각해 보자. 오라클은 일단 아래 2가지 접근 방향으로 나누어 판단할 것이다.
첫 번째는 고객명 조건절을 통하여 고객 테이블을 먼저 접근하고, 이후 고객번호 조인절에 의해서 주문 테이블을 접근하는 경우인데 이때 주문일자 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않고 컬럼값에 의한 필터만 발생한다.
두 번째는 주문일자 조건절을 통하여 주문 테이블을 먼저 접근하고 이후 고객번호 조인절에 의해서 고객 테이블을 접근하는 경우이다. 이때 고객명 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않고 컬럼값에 의한 필터만 발생한다.
이와 같이 양방향 접근이 가능하므로 만약 오라클이 CBO 방식을 따른다면, 최소 비용이 소요되는 방향(플랜)으로 결정할 것이다. 하지만 부정확한 통계정보로 인하여 오라클 옵티마이저가 잘못된 방향을 결정할 수도 있다. 이때 통계정보를 재생성 하거나 혹은 아래와 같이 힌트절을 통하여 직접 방향을 결정해 해결하기도 한다. 
고객명 조건절을 통하여 고객 테이블을 먼저 접근하고 이후 고객번호 조인절에 의해 주문 테이블을 접근하는 경우이다. 이때 인덱스를 타는 컬럼은 접근 방향의 목적지(화살표)인 1번과 3번 컬럼이며, 2번과 4번 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않는다. 특히 4번 컬럼은 컬럼값에 의한 필터만 발생한다. 
위의 쿼리는 Outer Join 쿼리이다. 따라서 오라클 옵티마이저는 주문일자 조건절을 통하여 주문 테이블을 먼저 접근하고, 이후 고객번호 조인절에 의하여 고객 테이블을 접근할 것이다. 이때 인덱스 타는 컬럼은 접근 방향의 목적지(화살표)인 4번과 2번 컬럼이며, 3번과 1번 컬럼은 비록 인덱스가 있더라도 인덱스를 타지 않는다. 특히 1번 컬럼은 컬럼값에 의한 필터만 발생한다.
이와 같은 방식으로 인덱스 생성도를 작성한다면 아무리 복잡한 쿼리라도 단순하게 도식화할 수 있고, 또한 테이블 접근 순서 및 인덱스 생성 위치에 대해서도 정확하게 이해할 수 있다. 이러한 방법으로 Heavy 쿼리를 분석한다면 대부분의 튜닝은 쉽게 해결된다. 
우리가 온라인에서 흔히 접할 수 있는 복잡한 쿼리라도 위의 인덱스 생성도 그림처럼 대부분은 5~6개 미만의 테이블로 구성된다. 이와 같이 간략히 구성된 인덱스 생성도를 통해 우리는 테이블 접근 순서를 알 수 있으며, 그에 따른 인덱스 생성 위치를 알 수 있다.
조건1로 진입시 테이블 접근 순서: A → B→ E→ D→ C 조건1로 진입시 인덱스 생성 포인트: 1번, 3번, 8번, 11번, 5번 조건3로 진입시 테이블 접근 순서: D→ E→ B→ A→ C 조건3로 진입시 인덱스 생성 포인트: 12번, 10번, 7번, 2번, 5번 조건4로 진입시 테이블 접근 순서: E→ B→ A→ D→ C 혹은 E→ D→ B→ A→ C 조건4로 진입시 인덱스 생성 포인트: 9번, 7번, 2번, 11번, 5번 조건2로 진입 시 테이블 접근 순서: Outer Join 테이블을 먼저 접근하지는 않는다. 조건2로 진입시 인덱스 생성 포인트: Outer Join 테이블을 먼저 접근하지는 않는다.
테이블 접근 순서는 오라클 옵티마이저가 결정한다. 그 결정이 올바르기 위해서는 현 시점의 DB 정보가 정확하게 통계 정보로 구축되어 있어야 하며, 필요한 인덱스도 미리 만들어져 있어야 한다는 전제가 필요하다. 그러나 현실은 그러하지 못하므로 개발자가 직접 테이블 접근 순서를 판단할 수 있어야 하며, 그에 따른 필요한 인덱스 생성 위치를 알고 있어야 한다. 그러기 위해서는 인덱스 생성도에 대한 이해는 꼭 필요하다. 필자는 현업에서 경험한 다양한 온라인 쿼리에 대한 튜닝을 이러한 인덱스 생성도를 통해 대부분 해결하였다. 인덱스 생성도는 크게 수평형, 수직형, 혼합형 3가지 형태로 구분된다.
인덱스 생성도가 어떠한 형태이든 우리는 테이블 접근 순서를 스스로 결정할 수 있어야 하며, 인덱스 생성 위치에 대해서도 정확하게 알고 있어야 한다. 만약 필요한 위치에 인덱스가 없다면 인덱스를 생성해야 한다. 또한 오라클 옵티마이저에 의한 테이블 접근 순서가 틀리다고 판단되면 힌트절을 사용하여 접근 순서를 바로 잡아야 한다.
지금까지 인덱스 생성도를 이용한 튜닝 방법에 대하여 그림을 통하여 자세히 설명을 하였다. 인덱스 생성도는 복잡한 쿼리를 단순하게 도식화할 수 있고, 인덱스 생성 포인트에 대하여 정확하게 이해할 수 있는 좋은 방법임을 다시 한 번 강조한다.
인덱스 생성도는 개발자가 흔히 접하는 OLTP 쿼리의 대부분에 적용 가능하다. 하지만 배치성 쿼리, OLTP High Concurrency Table, Critical SQLs들에 빈번히 사용되는 Table의 Index 생성 시에는 충분한 영향도 검증을 해야 함에 유의하자.
튜닝실전 1편에서는 대용량 데이터를 처리하는 배치 쿼리에 있어서 인덱스를 사용하지 말아야 하는 경우에 대하여 중요하게 설명하였다. 튜닝실전 2편에서는 소규모 데이터를 처리하는 온라인 쿼리에 있어서 인덱스를 정확히 사용해야 하는 경우에 대해서 주로 설명하였다. 다음 연재인 튜닝실전 3편에서는 튜닝이 잘 된 기본적인 쿼리 작성에 대하여 설명하고자 한다. (다음 회에 계속)