RDB 최적화 활용을 위한 주요 핵심 요소에 대한 논고에 이어 이제부터 기본 테크닉에 대하여 각 사안별 실전 노하우을 요약하여 게재하기로 한다. 성능의 핵심은 바로 I/O 효율에 있다는 주제는 지난번 "I/O (엑세스)와 실행계획의 비효율 개선을 통한 최적화"에서 어느 정도 이해하였으리라 생각하며 이번 글은 I/O효율화를 위한 옵티마이징 전략에 대하여 살펴보기로 한다. 물론 옵티마이징 전략에는 여러가지 무기를 사용할 수 있으며 이에 대한 언급을 모두 하여야 하나 본 논고의 한계상 여기서는 대부분의 독자들이 사용하고 있고 거의 모든 RDBMS에서 채택하고 있는 Balanced Tree Index 인덱스 전략에 대한 부분으로 국한하여 언급하기로 한다.

대부분의 DB 관련 개발자나 설계자들은 일단 속도가 느리면 Full Scan하여 속도가 느리다고 쉽게 단언하고 가볍게 인덱스를 추가하여 응답속도 개선을 체감하고 인덱스 역할에 대한 인식에 있어서 매우 당연한 상식수준에서 넘어가 버리는 경향이 있다.

실제 인덱스는 원하는 조건의 정보를 위해 많은 불필요한 Block I/O를 하지 않고 꼭 필요한 Block 엑세스만을 하게 하는 I/O 효율을 개선하는데 매우 중요한 전략적 무기인 것은 두말할 나위가 없다. 하지만 인덱스가 만들어져 있다고 하여 무조건 인덱스를 사용한다는 것은 잘못된 생각이며 인덱스를 엑세스하였다고 해서 원하는 정도의 I/O 효율이 개선되어 성능개선이 바로 나타난다는 것도 잘못된 생각이다. 오히려 인덱스 엑세스를 하여 I/O 효율에 악영향을 미치는 경우도 다반사로 발생하기 때문이다. 즉, 인덱스를 사용할 수 있는 원칙이 지켜져야 인덱스를 사용하며 인덱스를 사용하는 것 보다 더욱 중요한 핵심은 "얼마나 효율적인 인덱스 엑세스를 하였느냐" 이다.

이러한 오류는 그동안 필자가 만나왔던 여러 개발/설계자들의 반응을 보면 기가 막힐 정도로 엉뚱한 항의섞인 답변을 보면 쉽게 이해할 수 있다. 그 답변은 바로 "실행계획을 보면 인덱스 스캔을 하였는데요 ?" 독자들의 이해를 돕기 위하여 다음과 같은 실례를 들어보자.

현재 매출실적 테이블이 약 백만건 정도 있으며 이 테이블의 PK(식별자)는 사업장구분(mandt) + 제품코드(prdcd) + 매출일자(saledt) 로 구성되어 있는 상황이고 여타 다른 인덱스는 없으며 PK만 Unique Index로 생성되어 있다. 해당 고객사 시스템의 사업장 구분은 총 3종류이며 매출실적에 존재하는 제품코드는 50여종의 제품이 존재하고 있는 상황이며 현재 실적에 쌓여있는 데이터의 매출일자는 약 2년(700여일) 정도의 데이터가 쌓여있는 상황이다.

이러한 상황에서 어느 조회용 응용 프로그램에서 아래의 SQL을 사용하고 있다.

Select a,b,c,d,… From sale_sum_d Where mandt = :v1 and saledt = :v2

본 SQL에 대한 실행계획은 아래와 같이 나타났다.

TABLE ACCESS (BY INDEX ROWID) OF 'SALE_SUM_D'

INDEX (RANGE SCAN) OF 'SALE_SUM_D_PK' (UNIQUE)

실행계획을 보더라도 인덱스 스캔을 하였으며 이러한 실행계획을 확인한 개발자는 인덱스를 사용한다고 자신있게 예기한다는 사실이다.

그러나 실제 엑세스한 Rows까지 자세히 확인하여 보면 엄청난 사실을 확인하게 된다. 아래 내용은 실제 실행계획의 단계별 엑세스 Rows 결과이다.

Rows Execution Plan

--------- --------------------------------------------------

832 TABLE ACCESS (BY INDEX ROWID) OF 'SALE_SUM_D'

637664 INDEX (RANGE SCAN) OF 'SALE_SUM_D_PK' (UNIQUE)

엑세스한 rows를 보면 인덱스 스캔에서 637664건을 스캔하고서 인덱스 Object의 rowid를 가지고 테이블 엑세스한 rows는 832건 밖에 되지 않는다. 결국 832건의 결과를 추출하기 위하여 637664건의 인덱스를 읽었다는 예기이며 이는 불필요한 인덱스 세크먼트를 엄청나게 엑세스하였다는 사실이다. 당연히 응답속도가 느릴 수 밖에 없다.

이러한 문제점은 바로 인덱스 전략의 비효율로 인한 문제점이며 결정적으로 범위를 줄여주는 일자조건 컬럼이 결합인덱스의 맨 마지막 컬럼으로 존재하고 있으며 결합인덱스의 첫번째 컬럼 조건은 = 조건으로 사용되었으나 결합인덱스의 두번째 컬럼이 조건에 나타나지 않아 결정적으로 범위를 줄여주는 세번째 컬럼인 매출일자 조건이 여당 중의 주류조건이 아닌 비주류 조건으로 인덱스에서 체크기능밖에 하질 못해 실제 인덱스 세그먼트를 읽는 범위가 3종류의 사업장 구분 중 조건에 부여된 하나의 사업장의 모든 인덱스 세그먼트를 (전체의 33% 정도) 범위 스캔하게 되었다.

이를 최적화하기 위해서는 간단히 결합 인덱스의 순서를 바꾸면 쉽게 해결할 수 있다. 즉 비주류로 사용된 가장 똑똑한 조건인 매출일자 조건을 주류조건으로 끌여들이기 위하여 결합인덱스의 순서를 mandt+saledt+prdcd 로 바꾸거나 새로 인덱스를 생성하면 SQL 수정도 필요없이 100배 이상의 응답속도를 개선할 수 있을 것이다.

그러나 이런 식으로 성능개선을 위하여 인덱스를 계속 추가하거나 기존의 인덱스 컬럼 결합순서를 바꾸게 되면 기존에 문제없던 SQL들의 실행계획에 변화가 생기거나 없던 비효율이 부가적으로 더 많이 나타나게 되기 때문에 인덱스 전략 수립의 중요성은 매우 중요하다. 쉬운 예로 위에서 예로 들은 SQL말고 원래 아래와 같은 SQL도 존재하고 있었다면 위에서 예기한 것 처럼 결합인덱스 순서를 바꾸었을 때 어떠한 일이 발생되겠는가 ?

Select a,b,c,d,… From sale_sum_d

Where mandt = :v1 and prdcd = :v2 and saledt like :yyyymm||'%'

이러한 상황에서 mandt와 prdcd 조건이 모두 = 조건이며 saledt 조건이 범위 조건이므로 원래 결합인덱스 순서인 mandt+prdcd+saledt 일 경우였을 때는 거의 1초 이내의 우수한 성능을 보였을 것이나 mandt+saledt+prdcd 순서로 결합인덱스 순서를 바꾼 경우에는 30여종의 전제품에 대하여 한달간의 데이터를 모두 범위 스캔하게 되므로 특정 한제품에 대한 결과가 필요함에도 불구하고 불필요한 29종의 제품 범위를 불필요한 인덱스 세그먼트 엑세스하게 된다. 역시 SQL이 바뀌지 않은 2번째 경우 SQL이 이번에는 엄청난 속도 저하를 발생하게 된다는 사실이다. 이러한 문제 때문에 아예 별도의 순서를 뒤바꾼 결합인덱스를 추가하는 경우가 발생한다. 이런 식의 순서를 바꾸어 발생하는 조건의 유형을 만족하는 최적의 인덱스 전략을 수립한다면 배보다 배꼽이 큰 엄청난 경우의 수에 해당하는 결합인덱스들이 양산되게 되는 우스운 일이 벌어지게 된다.

이러한 상황에서 우리는 자연스럽게 결합인덱스를 선정하는 기준에서 선행인덱스 컬럼의 결정이 매우 중요하며 이러한 결정을 하는데 있어 가장 중요한 부분이 바로 얼마나 조건에 항상 사용되어 지느냐와 항상 조건이 =로 사용되어 질 수 있느냐가 중요하다는 결론을 쉽게 얻을 수 있다. 흔히 잘못 이해하고 있는 독자는 분포도가 좋은(값의 종류가 많은) 컬럼이 선행컬럼으로 결정해야 한다고 믿는 경우가 있으나 그렇지 않다는 것을 유념하기 바란다.

진짜 기술은 바로 무었인가 ? 인덱스를 추가하지 않고 그리고 기존의 결합인덱스의 컬럼순서도 바꾸지 않고 원하는 성능을 보장받을 수 있는 기법이 바로 기술이 아닐까 ? 이러한 기술은 우리가 옵티마이저의 원리에서 인덱스에 대한 이해만 제대로 가지고 있다면 간단하다. 처음 원래의 결합인덱스 순서인 mandt+prdcd+saledt 인 상황에서 제시된 SQL을 다음과 같이 바꾸어 보도록 하자.

Select a,b,c,d,… From sale_sum_d

Where mandt = :v1 and saledt = :v2

and prdcd

in (select prdcd from prd_mast

where prdcd > ' ' group by prdcd)

또는

Select y.a, y.b, y.c, y.d,…

From (select prdcd from prd_mast

where prdcd > ' '

group by prdcd) x, sale_sum_d y

Where y.mandt = :v1 and y.saledt = :v2

and y.prdcd = x.prdcd

윗부분의 SQL은 공급자 역할을 하는 SubQuery이며 아래부분의 SQL은 인라인뷰를 이용한 조인 SQL이다. 이렇게 하여 없던 prdcd 조건을 강제로 = 조건으로 만들어 결정적인 범위 축소 조건인 saledt = 조건을 주류조건으로 개선한 경우이다. 이렇게 만들어진 SQL에서는 반드시 prdcd를 먼저 =로 공급해 줄 수 있도록 공급자용 서브쿼리가 되거나 이렇게 공급자용 서브쿼리가 되지 않을 경우 조인으로 해결할 수도 있는데 이럴 경우에는 두번째 해결 방법처럼 인라인 뷰로 묶어서 먼저 드라이빙 되도록 해야 한다.

인덱스 전략은 특정 하나의 SQL만을 위하여 인덱스를 새로 생성하거나 함부로 인덱스 컬럼의 순서를 바꿀 수 없다. 해당 테이블을 엑세스하는 SQL은 무수히 많이 있을 것이며 이러한 여러가지 SQL의 엑세스 유형이 골고루 나타날 것이기 때문에 가장 최적의 방안이라면 기존의 인덱스 상황을 변화시키지 않고서 SQL의 수정과 튜닝기법을 이용하여 기존의 인덱스를 가장 최적으로 활용할 수 있는 기법이 무었보다 중요하다는 사실을 명심해야 한다.

그럼 어느 특정 테이블(천만건)을 활용하는 수많은 SQL을 분석해 본 결과 엑세스 유형이 아래 [그림1]과 같은 상황을 가정해 보자.

해당 태이블을 사용하는 수 십가지의 SQL에서의 조건문들을 분석한 결과 아래 [그림1]과 같이 대표적으로 6가지의 엑세스 형태가 도출되었으며 현재 그러한 조건 형태에 대하여 활용할 수 있는 인덱스로서 현행 인덱스 전략이 [그림1] 아래 부분에 나타나 있다.

[그림1]

그럼 [그림1]의 상황을 접수일자 조건과 취소일자 조건 그리고 실판매일자 조건에 대한 case별로 자세히 파악하여 보자.

나타난 1)번 엑세스 형태는 특정접수일자 = 조건이 들어오는 경우이거나 다른 형태로는 접수일자가 like 로 월기간 조건으로 들어오는 경우이다 고로 idx1 인덱스가 최적의 인덱스 전략으로서 문제가 없다. 다음으로 2)번 엑세스 유형은 실판매일자가 = 조건이거나 like 월 조건으로 나타나는 경우로서 이 역시 idx2 인덱스로서 최적의 효율을 보장하고 있다. 3)번 엑세스 유형은 취소일자가 = 조건 또는 like 월 기간 조건으로 사용되는 경우로 idx3 인덱스가 이를 위한 최적의 인덱스 전략이라 할 수 있다. 문제는 4) 번 엑세스 유형부터 이다.

위 [그림1]의 4)번 엑세스 유형은 접수일자가 오늘 이전인 것 중에서 실판매가 이루어지지 않은 건(실판매일자가 null인 건)을 추출하기 위한 조건이다. 실지 실판매로 전환되지 않은 건은 그리 많지 않으므로 소량의 데이터를 추출해야 하지만 접수일자 조건이 <= 조건으로 비교되어 idx1 range scan 실행계획이 나타날 가능성이 매우 높다. 하지만 rcpt_dt <= sysdate(오늘날자:상수) 조건은 거의 전 데이터일 수 밖에 없으니 천만건 가량의 인덱스 row 엑세스를 하여 테이블 엑세스를 천만번 한 후 테이블에 존재하는 실판매일자 조건을 체크하여 null인 경우(극히 적음)에만 결과로 추출하게 된다는 예기이다. 참으로 억울한 상황이다. 그렇다고 실판매일자+접수일자로 인덱스를 생성할 수도 없다. MS-SQL의 경우는 null value도 인덱스 세크먼트에 관리되고 있지만 오라클의 경우는 null 비교를 할 경우 인덱스 스캔을 할 수 없으므로 무용지물이다.

5)번 엑세스 유형은 특정 접수일자 건 조건 (접수일자=조건)의 데이터 중에서 취소되지 않은 건을 추출하고자 하는 경우이다. 상식적인 수준의 업무기준에 비추어 볼 때 취소건이 극히 적을 것이라는 가정을 한다면 접수일자 조건만으로 인덱스 스캔을 하더라도 걱정할만한 비효율은 없을 것이다. 테이블에 가서 취소일자 조건을 체크하더라도 대부분이 취소되지 않은 건이므로 테이블 엑세스한 건은 거의 대부분이 비효율이 아니라는 얘기이다. 즉, 5)번 엑세스 유형은 idx1으로도 어느정도 효율을 보장할 수 있다.

마지막으로 6)번 엑세스 유형을 보도록 하자. 이 경우는 접수일자는 between 조건이며 취소일자도 like로서 유사하게 범위 조건이 사용되었다. 옵티마이져가 판단하는 기준으로 like나 between은 cost가 동일한 범위 스캔이라는 사실이다. 하므로 idx1 또는 idx3 중 어느 인덱스 하나를 사용하게 될 것이다. 그러나 어느 인덱스 하나가 선택되더라도 나머지 조건 체크를 위해 테이블에 엑세스한 후 나머지 야당으로 결정된 조건에서 대부분 버려지는 비효율은 마찬가지로 존재한다는 것을 쉽게 알 수 있다. 만일 두개의 독립적인 인덱스 컬럼이 모두다 = 조건으로 사용되었다면 각각 조건에 해당되는 건만을 인덱스에서 골라내어 동일한 physical address 인 건만을 추출하는 인덱스 머지 방식 (and equal)의 실행계획을 유도할 수 있지만 이 경우는 두 컬럼의 조건이 = 조건이 아니므로 어느 하나의 인덱스만을 사용하게 되어 나타나는 문제이다.

이렇게 위 [그림1]의 사례에서 확인할 수 있듯이 인덱스는 독립적으로 홀로서기 인덱스를 컬럼별로 만들어서는 최적의 효과를 나타낼 수 없다는 사실이다. 대부분의 조건들이 단하나의 컬럼만이 사용되는 것이 아니고 여러 개의 컬럼 조건이 다양한 비교 조건으로 나타나기 때문에 부여된 조건에 필요한 정확한 데이터 만을 추출하기 위한 I/O 효율을 위해서는 여러 개의 컬럼이 결합되어 인덱스 전략에 반영되어야 만이 효율을 보장할 수 있기 때문에 진정한 인덱스 전략은 결합인덱스 전략에서 꽃을 피우게 된다.

그렇다고 접수일자+취소일자+실판매일자 와 같이 무조건 조건에 나타나는 컬럼들을 나열식으로 붙여 놓아봐야 해법이 아니라는 사실은 굳이 설명이 필요없을 것이다.

이제 위에서 제시된 [그림1]의 상황을 다음 [그림2]로 개선하여 보자.

아래 [그림2]는 사용된 SQL의 조건의 형태를 약간의 변화를 주어 결과적으로는 동일한 결과를 내게 하면서 인덱스 전략의 최소화 최적화를 노리는 기법을 예시로 표현한 사례이다.

[그림2]

실판매일자와 취소일자의 상관관계를 보면 상식적으로도 서로 배타적 관계라는 것을 알 수 있다. 즉, 취소일자가 있는 경우는 실판매일자가 있을 수 없고 실판매일자가 있는 경우는 취소일자가 있을 수 없다는 것이다. 이러한 상황을 이용하여 전략적으로 실판매일자와 취소일자를 not null 제약으로 선언하여 실판매가 이루어지지 않은 건의 실판매일자 초기값을 '99991231'로 지정하고 취소되지 않은 건 (취소일자의 초기값)에 대한 취소일자 역시 '99991231'로 지정하도록 정의하면 위의 [그림2]와 같이 2개의 결합인덱스로서 모든 경우를 만족시키는 가장 최적의 인덱스 전략으로 개선할 수 있다.

그럼 1)번 엑세스 유형과 2)번 엑세스 유형은 동일하게 idx1과 idx2 인덱스를 사용하게 될 것이므로 이전 상황과 동일하게 전혀 문제가 없으며 3)번 엑세스 유형은 취소건을 찾고자 하는 경우이니 실판매가 이루어지지 않은 조건을 같이 부여하더라도 답을 동일할 것이므로 실판매일자 조건을 sale_dt = '99991231'로 추가 조건을 주더라도 문제가 없으며 그렇게 되면 idx2의 sale_dt+cancel_dt 인덱스를 사용하여 가장 최적의 효율을 보장할 것이다.

4)번 엑세스 유형은 실판매가 이루어지지 않은 건 이라는 조건이 가장 효율이 있을 것이므로 sale_dt = '99991231' 조건으로 부여하면 idx2의 인덱스 효율을 보장받으며 접수일자조건은 수렴하지 않는 조건이니 아예 옵티마이져가 idx1 인덱스를 사용할 생각도 못하게 아예 가공시켜버린 경우이다.

5)번 엑세스 유형은 취소일자 is null 조건을 = 조건으로 바꾸고 idx1 의 결합인덱스 컬럼 모두를 여당 주류조건으로 사용되게 하여 이전의 경우보다도 더 확실한 인덱스 효율을 보장하게 되었다. 6)번 엑세스 유형은 접수일자 범위 조건보다 더 변별력이 있을법한 취소일자 조건을 무기로 사용하기로 하여 실판매가 이루어지지 않은 조건까지를 부가적으로 추가(sale_dt='99991231') 시켜 idx2 인덱스를 가장 최적으로 활용하도록 변화를 주었으며 접수일자 조건은 아예 가공 시켜 옵티마이져가 엉뚱한 인덱스를 사용하지 못하도록 예방책까지 구사한 사례이다.

이처럼 3개의 인덱스로도 최적화를 하지 못했던 상황을 2개의 인덱스로도 훨씬 최적화된 성능을 보장할 수 있도록 할 수 있다. 이것이 바로 옵티마이져를 이해하고 파악하고 있는 전략가로서의 인덱스 전략이며 옵티마이져의 원리를 응용하여 주무를 수 있는 SQL 구사 능력이라고 할 수 있다.

본 논고에서 이 오묘하고 심오한 옵티마이징 전략을 모두 다 언급하기에는 너무도 지면이 한정적이다. 이러한 옵티마이징 전략 수립을 가능하게 하는 힘의 근원은 바로 옵티마이져를 완벽에 가깝게 이해할 수 있는 즉, 옵티마이져와 같이 놀아줄 수 있는 옵티마이져 원리와 기법을 이해하는 능력이다.

앞으로 당사 컨설턴트들의 지속적인 DB튜닝을 위한 실전 테크닉을 위한 각 요소별 구체적인 사례와 함께 실전지침을 기대하시고 많은 노력과 훈련을 당부드린다.