<오라클 성능 고도화 원리와 해법2> Ch01-08 인덱스 설계

오라클 성능 고도화 원리와 해법2 - Ch01-08 인덱스 설계

지금까지 다양한 인덱스 스캔 방식과 Random 액세스 최소화 원리, 스캔 효율화 원리에 대해 설명했다. 이제 이들 원리를 기초로 효과적인 인덱스 설계 방안은 무엇인지 설명하려고 한다.

SQL 각각을 위해 최적화된 인덱스를 모두 생성할 수 있다면 SQL 튜닝과 인덱스 설계만큼 쉬운 것도 없다. 하지만 그런 식으로 인덱스를 생성하다 보면 테이블마다 수십 개씩 달릴 것이 불보듯 뻔하고 관리 비용뿐만 아니라 시스템 부하를 가중시키는 원인이 된다. 인덱스 설계가 어려운 이유가 여기에 있으며, 시스템 전체를 바라보는 시각에서 전략적으로 접근하려는 노력이 무엇보다 중요하다고 하겠다.

인덱스를 구성할 때의 컬럼 순서 결정 원리부터 살펴보자.

(1) 가장 중요한 두 가지 선택 기준

인덱스 스캔 방식에 여러 가지가 있지만 가장 정상적이고 일반적인 것은 Index Range Scan이라고 했다. 그리고 이를 위해서는 인덱스 선두 컬럼이 조건절에 반드시 사용되어야만 한다. 따라서 결합 인덱스를 구성할 때 첫 번째 기준은, 조건절에 항상 사용되거나, 적어도 자주 사용되는 컬럼들을 선정하는 것이다.

두 번째 기준은, 그렇게 선정된 컬럼 중 낮은 조건으로 자주 조회되는 컬럼을 앞쪽에 두어야 한다는 것이다. 그 이유에 대해서는 앞절에서 충분히 설명하였다.

아래 두 가지 선택 기준을 공식처럼 외우기 바란다.

1. 조건에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.
2. 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다.

(2) 인덱스 설계는 공식이 아닌 전략과 선택의 문제

앞서 제시한 두 가지 선택 기준이 일반적으로 통용될 수 있는 기본 공식임은 틀림없지만 인덱스 설계가 그렇게 간단하지는 않다. 오히려 이 공식을 지키려다보면 원칙과 기준이 없을 때처럼 인덱스 개수가 자꾸 늘어나게 됨을 느낄 것이다. 간단한 예제와 함께 설명해보자.

간단한 설계 예제

모델링에 모범 답안은 있어도 정답은 없다고 흔히 얘기하듯이, 인덱스 설계에도 정답은 없다. 개별 쿼리 기준으로는 어떤 인덱스가 더 좋은지 명확히 구분할 수 있지만 시스템 전체적인 관점에서의 효율은 또 다른 기준, 즉 쿼리 수행 빈도, 업무 상 중요도, DML, 부하, 저장 공간, 관리 비용 같은 상황적 요소까지 고려해평가되어야 하기 때문이다.

그리고 그런 상황적 요소들을 해석하는 기준이 사람마다 다르기 때문에 설계자의 성향이나 스타일에 따라 결과물도 달라지게 마련이다. 그런 모든 요소들을 일일이 나열할 수 없으므로 독자 스스로가 가정을 세워가며 설계하기 바라고, 왜 그런 선택을 했는지 판단 근거를 제시할 수 있으면 된다.

효율성 비교 분석

설계 방식에 따라 실제 효율성에 얼마나 차이가 생기는지 비교 분석해보자. 모든 스타일을 일일이 분석하기에는 지면이 부족하므로 양극에 놓인 두 개 스타일만 비교해보이려고 한다.

스캔 효율성 이외의 판단 기준

쿼리 수행 빈도도 중요한 판단 기준이고, NL 조인의 Inner 쪽 테이블로서 자주 액세스 되는 지도 중요한 판단 기준이다.

데이터량도 중요한 판단 기준이다. 데이터량이 적다면 인덱스를 많이 만들더라도 저장 공간이나 트랜잭션 부하 측면에서 그다지 문제될 것이 없어 인덱스 구성 전략 때문에 크게 고민할 필요가 없다. 초대용량 테이블일 때는? 전문가의 손길이 필요하며, 진정한 튜닝 전문가는 이때 빛을 발한다.

「자주 사용되는 날 조건」이라는 애초의 기준은 인덱스 스캔의 효율성만을 고려한 것으로서, 실제 인덱스를 설계할 때는 시스템 전체적인 관점에서의 추가적인 요소들을 고려해야 한다. 그런 요소들을 열거해보면 다음과 같다.

• 쿼리 수행 빈도
• 업무 상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하(=기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)
• 저장 공간
• 인덱스 관리 비용 등

이런 상황적 요소에 대한 해석과 판단 기준이 서로 다르기 때문에 설계자의 성향이나 스타일에 따라 결과물도 크게 달라진다. 전장에서 똑같은 상황에 맞닥뜨리더라도 지휘관 스타일에 따라 전략과 전술이 달라지는 것처럼 말이다.

인덱스 설계는 공식이 아닌 전략과 선택의 문제

인덱스 설계가 어려운 이유는, 개별 쿼리 성능뿐만 아니라 그 개수를 최소화함으로써 DML 부하를 줄이는 것까지 목표로 삼아야하기 때문이다. 따라서 시스템 전체적인 시각에서 대안 전략들을 수립하고, 그 중 최적을 선택할 수 있는 능력이 무엇보다 중요하다.

조건절 패턴이 10개 있을 때, 10개 패턴을 모두 만족하려고 인덱스를 10개나 만들 수는 없다. 그런 설계라면 몇 가지 공식만 가지고도 누구나 할 수 있다. 10개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로(Critical Access Path) 한 두 개를 전략적으로 선택하고, 그것을 중심으로 나머지 액세스 경로는 약간의 비효율이 있더라도 목표한 성능 수준에 도달하도록 구성해주면 된다.

그리고 왜 그런 선택을 했는지, 전략적 판단 근거가 무엇인지 답할 수 있어야 한다. 단순한 공식에 의한 결정이 아니라 상황을 이해하고 나름의 판단 기준을 가지고 결정을 내리라는 것이다.

이처럼 전략적 선택을 통해 인덱스 개수를 최소한으로 유지한다면, 사용 빈도가 높거나, 중요한 액세스 경로가 새로 추가됐을 때 최적의 인덱스를 제공해줄 여유가 생긴다.

참고로, 지금까지 파티셔닝 기능은 배제한 채 설명했지만 실무적으로는 파티션 설계를 먼저 진행하거나 최소한 인덱스 설계와 병행하는 것이 바람직하다.

인덱스 전략 수립을 위한 훈련

본 장에서 설명한 인덱스 원리를 차근차근 공부한 독자라면 지금쯤 전략적인 인덱스 설계에 필요한 기본기를 어느 정도 갖추었을 것으로 믿는다. 이제 수많은 훈련을 통해 자기만의 판단 기준과 선택 능력을 기르는 일만 남았다.

인덱스 설계 조정을 통한 튜닝 사례 - Right Growing 인덱스

인덱스에는 키값 기준으로 정렬된 상태를 유지하며 값이 입력되는다. 따라서 ‘일련번호’ 나 입려일시’ 처럼 순차적으로 증가하는 컬럼에 생성한 인덱스는 항상 맨 우측 블록으로만 값이 입력된다. 이런 특징을 갖는 인덱스를 ‘right-growing 인덱스’ 라고 부르며, 여러 프로세스에 의한 동시 입력이 많을 때 트랜잭션 성능을 떨어뜨리는 주범이다. 입력값이 다르므로 로우락 경합은 발생하지 않지만 같은 블록을 갱신하려는 프로세스간 버퍼락 경합이 발생하기 때문이다. 이때 나타나는 대기 이벤트가 buffer busy waits라고 설명한 것을 1권을 본 독자라면 기억할 것이다. 이런 인덱스 경합은 의외로 흔히 발생하는 현상으로서 RAC 환경에서 특히 심각한 성능 저하를 일으키며, gc buffer busy 이벤트로 관찰된다. 여러 노드가 동시에 Current 블록 하나를 서로 주고받으며 값을 입력해야 하기 때문이다. 해결 방법으로는, Reverse Key 인덱스로 바꿔 블록이 분산되도록 하거나, 그보다 오라클이 권하는 솔루션으로서 인덱스를 해시 파티셔닝하는 방법이 있다. 해시 파티셔닝을 하면 값이 순차적으로 증가하더라도 해시 함수에서 리턴된 값에 따라 서로 다른 파티션에 입력되므로 경합을 그만큼 줄일 수 있다.

(3) 결합 인덱스 컬럼 순서 결정시, 선택도 이슈

인덱스 생성 여부를 결정할 때는 선택도(selecivity)가 충분히 낮은지가 중요한 판단 기준임이 틀림없다. 4절에서 보았듯이 인덱스를 스캔하면서 테이블을 액세스하는 양이 일정 수준을 넘는 순간 Full Table Scan 보다 오래 걸리기 때문이다. 따라서 선택도 (결합 인덱스 일 때는 결합 선택도)가 높은 인덱스는 생성해봐야 효용가치가 별로 없다.

결합 인덱스 컬럼간 순서를 정할 때도 선택도가 낮은, 즉 변별력이 높은 쪽을 앞에 두는 것이 유리하다고 흔히 알려져있는데, 과연 그럴까? 결론부터 말하면, 개별 컬럼의 선택도가 고려사항은 될 수 있지만 어느 쪽이 유리한지는 상황에 따라 다르다.

선택도가 액세스 효율에 영향을 주지 않는 경우

’=’ 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미없는 것이거나 오히려 손해일 수 있다.

예를 들어, 항상 사용되는 “고객번호, 고객등급, 거래일자 중 고객번호와 고객등급은 ‘=’ 조건으로, 거래일자는 between으로 사용된다. 그리고 거래유형과 상품번호는 항상 사용되는 조건이 아니어서 인덱스를 아래와 같이 구성했다고 가정하자.

IDX01 : 고객등급 + 고객번호 + 거래일자 + 거래유형 + 상품번호

between 조건으로 사용되는 거래일자 뒤쪽에 놓인 거래유형과 상품번호는 어차피 인덱스 필터 조건으로 사용되므로 변별력이 좋고 나쁨을 따질 이유가 없다.

그렇다면 거래일자 앞쪽에 위치한 두 컬럼은 변별력에 따라 위치를 바꿔줄 필요가 있는 것일까? 즉, 변별력이 좋은 고객번호를 앞으로 이동시키는 것이 효과적인가?

수직적 탐색 과정에서(몇몇 경우를 제외하고) 모든 인덱스 컬럼을 비교조건으로 사용하므로 스캔 시작지점은 어느 것을 앞에 두더라도 동일하게 결정된다.

그리고 앞에서 여러 차례 강조했듯이 선행 컬럼이 모두 ‘=’ 조건이면 첫번째 범위 검색조건(여기서는 거래일자)까지의 인덱스 레코드들은 모두 한 곳에 모여 있다. 따라서 변별력이 좋지 않은 고객등급을 앞에 두더라도 수평적 탐색을 위한 스캔 범위는 최소화될 것이고, 인덱스 액세스 효율에 전혀 영향을 미치지 않는다.

선택도가 ‘높은 컬럼’을 앞쪽에 두는 것이 유리한 경우

오라클9부터 제공되는 Index Skip Scan을 효과적으로 활용할 수 있어 유리하다. 이미 설명한대로 Index Skip Scan은 선행 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value 개수가 많아야 효율적이다.

굳이 Index Skip Scan이 아니더라도 IN-List로 값은 제공함으로써 쉽게 튜닝할 여지가 생긴다. 고객번호를 선두에 두었는데 조건에서 누락되거나 범위 검색조건으로 조회된다면 그런 튜닝 기법을 적용하기가 곤란하다. (물론 좁은 범위의 고객번호를 입력하고 조회한다면 고객번호를 선두에 두는 것이 더 나을 수도 있는데, 그런 경우에 대한 유•불리는 곧이어 표를 통해 비교해볼 것이다.)

인덱스 압축 기능을 고려하더라도 고객등급을 앞쪽에 두는 것이 유리하다. 선택도가 높은 컬럼을 앞쪽에 두어야 인덱스 압축률이 더 좋아지기 때문이다.

참고로 유명한 톰 카이트(Tom Kyte)의 설명에 의하면 변별력이 좋은 컬럼을 선두에 두는 것이 좋다는 상식은, 오라클5 버전에서 사용하던 인덱스 압축 방식 때문에 생긴 것이고 그런 특징은 오라클6에서 로우 단위 Lock이 구현되면서 사라졌다고 한다.

상황에 따라 유•불리가 바뀌는 경우

범위 검색조건 앞쪽에 놓인 두 컬럼, 예를 들어 고객등급과 고객번호가 모두 ‘=’ 조건인 경우는 앞에서 살펴보았다. 이제 가정을 바꿔, 대부분 ‘=’ 조건으로 사용되지만 가끔씩 (또는 혹시 모를 미래시점에) 둘 중 하나가 조건절에서 빠지거나 범위 검색조건으로 사용될 수도 있다고 하자. 그럼에도 인덱스 개수를 최소화할 목적으로 이 두 컬럼을 인덱스 선두에 나란히 두려한다면 어느 쪽을 앞에 두는 것이 유리할까?

상황에 따라 유•불리가 바뀌는데, 이해하기 쉽도록 표로써 정리해보았다.

종합해보면, 선택도가 높은 컬럼(예, 고객등급)을 선두에 두면 나중에 범위 검색조건이 사용되거나 아예 조건절에서 누락되더라도 Index Skip Scan 또는 IN-List를 활용할 수 있어 유리하다. 다만, 이들 기법이 효과를 발휘할 정도로 Distinct Value 개수가 충분히 적은지가 관건이다.

선택도가 낮은 컬럼(예, 고객번호)을 선두에 두면 이를 범위 검색조건으로 조회하는 일이 생겼을 때 불리하지만, 입력값의 범위가 좁다면 비효율이 크지 않아 Index Skip Scan이나 IN-List를 활용하지 못하더라도 오히려 유리할 수 있다.

선택도가 ‘낮은 컬럼’을 앞쪽에 두는 것이 유리한 경우

범위 검색조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다. 예를 들어, 다음과 같은 조건절이 있다고 하자.

where 고객번호 = :a and 상품번호 = :b and 거래일자 between :c and :d

고객은 100만명이고, 상품 개수는 10,000개다. 이때 아래 두 인덱스 구성 전략 중 하나를 선택 하라고 하면 X01을 택하는 것이 현명하다.

X01: 고객번호 + 거래일자 + 상품번호
X02: 상품번호 + 거래일자 + 고객번호

X01 인덱스는 중간에 놓인 거래일자가 between 조건이어서 상품번호는 인덱스 스캔 단계에서 주로 필터 역할만 한다. 그렇더라도 변별력이 좋은 고객번호에 의해 스캔 범위가 최소화되기 때문에 비효율이 크지 않다.

X02 인덱스는 변별력이 좋은 고객번호가 필터 역할을 하므로 비효율적이다. 예를 들어, X01 인덱스가 고객번호와 거래일자 조건에 의해 100개 레코드를 스캔하고서 1개 레코드를 최종 선택한다면, X02 인덱스는 상품번호와 거래일자 조건으로 10,000개 레코드를 스캔하고서 1개 레코드를 선택하게 될 것이다. 이처럼 범위 검색조건을 사이에 두고 고민할 때는 변별력이 좋은 컬럼을 앞 쪽에 두는 것이 유리하다.

여기서 한가지 생각해볼 것이 있다. 위와 같이 거래일자를 사이에 두고 단순히 상품번호와 고객번호 간 선택도를 따져서 순서를 결정할 일이 있겠는가?

만약 위와 같은 조건절 하나뿐이라면 인덱스는 [고객번호 + 상품번호 + 거래일자] 순으로 구성하는 것이 최선이다. 그럼에도 X01처럼 설계했다면 그것은 아마 “고객번호=, 거래일자 between” 조건을 가진 다른 SQL이 있었기 때문일 것이다. 반대로 X02처럼 설계했다면 “상품번호=, 거래일자 between” 조건을 가진 다른 SQL이 있기 때문일 것이다.

즉, 처음 설명한 두 가지 원칙(자주 사용되는 = 조건)에 의한 결정이지 선택도에 의한 결정이 아니다.

선택도가 낮은 컬럼을 ‘선택’ 하는 것이 유리한 경우

아래와 같은 조건절 하에서만약 거래일자를 선두에 둔 단 하나의 인덱스를 생성하기로 하였고, 후행 컬럼으로서 상품번호와 고객번호 둘 중 하나만 ‘선택’ 하라면 어느 쪽을 택하겠는가?

<검색조건 1> where 거래일자 = :a and 상품번호 = :b
<검색조건 2> where 거래일자 = :a and 고객번호 = :b

그야 물론 위 두 검색조건 중 어느 쪽이더 많이 사용되는지를 보고 결정하는 것이 정답이다. 만약 사용 빈도까지 똑같다면? 그때는 선택도가 낮은 고객번호를 선택하는 것이 현명하다. 선택도가 낮은 고객번호를 위해 인덱스를 사용해야 더 적은 양의 테이블 Random 액세스가 발생하기 때문이다.

하지만 이런 식으로 후행 컬럼의 선택도를 비교해

둘 중 하나를 선택하는 일은 없다. [거래일자 + 상품번호]로 구성된 인덱스를 생성하지 않았다면 그런 액세스 경로가 없거나 결합 선택도가 높아서 그런 것이지 상품번호와 고객번호 간의 개별 선택도를 따져서 결정할 문제는 아니라는 것이다.

단일 컬럼 인덱스 생성 여부를 결정할 때 해당 컬럼의 선택도만 고려할 뿐 다른 컬럼의 선택도와 비교해가며 결정하지는 않는다. 예를 들어, 고객 테이블에서 주민등록번호 또는 고객명으로 조회하는 기능이 있는데, 이 둘 간의 선택도를 비교해서 인덱스 생성 여부를 결정하지는 않는다. 선택도가 낮으면 둘 다 생성할 수도 있고, 선택도가 높으면 둘 다 생성하지 않을 수도 있는 일이다.

결론적으로 결합 인덱스 컬럼간 순서를 정할 때는, 개별 컬럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지, 사용 빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는데에 어느 쪽 효용성이 높은지 등이 더 중요한 판단 기준이다.

(4) 소트 오퍼레이션을 생략하기 위한 컬럼 추가

인덱스는 항상 정렬 상태를 유지하므로 orderby, groupby를 위한 소트 연산을 생략할 수 있도록 해준다. 따라서 조건에 사용되지 않은 컬럼이 더라도 소트 연산을 대체할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수가 있다.

인덱스를 이용해 소트 연산을 대체하려면, 인덱스 컬럼 구성과 같은 순서로 누락 없이 (뒤쪽 컬럼이 누락되는 것은 상관 없음) order by 절에 기술해 주어야 한다. 단, 인덱스 구성 컬럼이 조건 절에서 ‘연산자로 비교된다면, 그 컬럼은 order by 절에서 누락되거나 인덱스와 다른 순서로 기술하더라도 상관 없다. 그리고 이런 규칙은 groupby 절에도 똑같이 적용된다.

(5) 인덱스 설계도 작성

앞에서도 언급했듯이 인덱스 설계 시 시스템 전체 효율을 고려해야 한다. 조화를 이룬 건축물을 짓기 위해 설계도가 필수인 것처럼 인덱스 설계 시에도 전체를 조망할 수 있는 설계 도면이 필요한 이유다. 그림1-57은 인덱스 설계도를 예시한 것이다.

그림1-57에서 상단 좌측(1)을 보면 변경 전 인덱스 구성 ‘필드가 있고, 우측(2)에는 ‘변경 후 인덱스 구성’ 필드가 있다. 전자는 말 그대로 현재의 인덱스 구성을 데이터베이스 딕셔너리에서 읽어 기록한 것이고, 후자는 새로운 구성 전략을 기록한 것이다.

개별 SQL이 아니라 전체를 보면서 전략을 수립하려면 일단 테이블별로 실제 일어나는 액세스 유형을 모두 조사해야 하는 과정이 필요한데, 그림1-57 중 간부터 아래쪽까지 작성된 ‘액세스 경로(3)’가 그것이다. 17번 액세스 경로를 예로 들면, 조건에 사용된 비교 연산자가 ‘물건 종류 =, 도시 =, 구시군 =, 읍면동 =, C09 <>, 입력일 >=’인 것을 표시한 것이다. 그리고 그 아래쪽을 보면 현재 인덱스 구성(1) 하에서 6번 인덱스(IDX06)가 사용되지만 구성을 변경(2)하고 나면 3번(IDX3) 인덱스를 사용하게 될 것임을 표시하고 있다.

설계도 상단에 파티션 구성을 기록하는 필드를 둔 것도 주목하기 바란다. 앞서 말한 것처럼 인덱스 설계 전에 파티션 설계를 먼저 진행하거나 최소한 병행해야 제대로 된 인덱스 전략을 수립할 수 있다.

인덱스 설계도 작성을 통한 튜닝 사례

그림1-57을 보면, 매물 테이블에 사용된 액세스 경로가 총 25개인데, 인덱스가 무려 15개나 달린 것을 볼 수 있다. 필자가 컨설팅했던 이 회사에는 이런 매물 테이블이 20여개(아파트매매, 아파트전세, 아파트월세, 점포매매, 점포임대, 토지매매, 토지임대 등)에 이르고, 인덱스가 모두 같은 패턴으로 설계돼 있었다.

조회를 빠르게 하려고 이렇게 많은 인덱스를 만들어 놓았지만 조회해보면 어떤 매물 조회 화면이든지 수십초에서 수분이 경과해야 결과를 볼 수 있었다. 원인이 무엇이었을까? 그림1-57에서 현재 인덱스 구성과 액세스 경로를 잘 분석해보기 바란다. 지금까지 본 장에서 설명한 원리를 제대로 이해했다면 쉽게 원인을 찾아낼 수 있다.

원인을 못 찾은 독자를 위해 힌트를 주면, 대부분 액세스 경로에 ‘입력일≥=’ 조건이 포함된 것을 확인하기 바란다. 그리고 변경 전 인덱스 구성에서 대부분 인덱스 선두 컬럼이 ‘입력일’ 인 것도 확인하기 바란다.

상황을 더 심각하게 만든 것은, 선두 컬럼 입력일 조건에 해당하는 범위가 인덱스 전체 레코드에서 95% 이상이라는 사실이다. 매물

테이블에는 최근 한 달치 데이터만 보관하는데, 과거 데이터가 정확히 커팅되지 않아 한 달 이전 매물이 함께 조회되는 것을 막으려고 입력일 조건을 대부분 쿼리에 포함시켰던 것이다. 조건절에 항상 사용되는 컬럼이 인덱스 선두로서 1순위 후보지만는 조건이어야 한다는 사실을 몰랐다면 누구든 이런 식으로 설계할 수 있는 일이다.

15개나 되던 인덱스를 그림1-57 우측 상단(2)처럼 5개로 줄이면서 입력일 컬럼을 모두 제거하는 새로운 인덱스 구성 전략을 수립하였고, 인덱스 생성 스크립트까지 작성해서 DBA 팀에 넘겨주었다. 그 다음 날 아침 눈을 뜨자마자 사이트에 접속해보니 하루 전만 하더라도 수분씩 걸리던 대부분 화면에서 검색 결과가 바로바로 출력되는 것을 확인할 수 있었다. 필자가 수년간 데이터베이스 튜닝 컨설팅을 수행하면서 가장 짜릿한 희열을 느꼈던 순간이다.