CH01. 인덱스 원리와 활용 - 08. 인덱스 설계

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

 

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

인덱스 스캔 방식에 여러 가지가 있지만 가장 정상적이고 일반적인 것은 Index Range Scan입니다. 그리고 이를 위해서는 인덱스 선두 컬럼이 조건절에 반드시 사용되어야 합니다. 따라서 결합 인덱스를 구성할 때

첫 번째 기준은 조건절에 항상 사용되거나, 적어도 자주 사용되는 컬럼들을 선정하는 것입니다.

두번 째 기준은 그렇게 선정된 컬럼 중 '='조건으로 자주 조회되는 컬럼을 앞쪽에 두어야 한다는 것입니다.

 

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

앞의 공식은 일반적으로 통용될 수 는 있지만 인덱스 설계가 그렇게 간단하지는 않습니다. 이 공식을 지키려다 보면 원칙과 기준이 없을 때처럼 인덱스 개수가 자꾸 늘어나게 됨을 느낄 수 있습니다.

 

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

 

실제 인덱스를 설계할 때는 시스템 전체적인 관점에서의 추가적인 요소들을 고려해야 하고 그런 요소들을 열거해보면 아래와 같습니다.

- 쿼리 수행 빈도

- 업무상 중요도

- 클러스터링 팩터

- 데이터량

- DML 부하(=기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)

- 저장 공간

- 인덱스 관리 비용

 

조건절 패턴이 10개 있을 때, 인덱스를 10개나 만들 수 없습니다, 10개 중 최적을 달성해야 할 가장 핵심적인 액세스 경로 한 두개를 전략적으로 선택하고, 그것을 중심으로 나머지 액세스 경로는 약간의 비효율이 있더라도 목표한 성능 수준에 도달하도록 구성해 주면 됩니다.

 

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

고객은 100만명이고, 거주지역으로는 15개의 값이 있으면 분포는 균일하다고 가정하겠습니다. 쿼리1관 쿼리4는 수행빈도가 매우 높고, 쿼리2와 3은 그다지 높지 않습니다.

중요한것은 액세스 효율을 높이는 것도 중요하지만 인덱스 개수를 최소화하는 것도 중요한 목표여야 합니다.

 

저의 답:

x01 : 고객번호 + 거주지역
x02 : 연령 + 성별 + 이름
x03 : 연령 + 거주지역 + 등록일

쿼리 1,4는 자주 사용하고, 고객번호가 선두로 조건절에 사용되고 있으므로 x01하나로 커버칠 수 있다고 생각합니다.

x02와 x03은 자주 사용되지는 않지만 연령 이외에 컬럼이 공동퇴는게 없어서 x02,x03으로 각각 생성해 주었습니다.

 

Right Growing 인덱스

인덱스에는 키 값 기준으로 정렬된 상태를 유지하며 값이 입력됩니다. 따라서 '일련번호'나 '입력일시'처럼 순차적으로 증가하는 컬럼에 생성한 인덱스는 항상 맨 우측 블록으로만 값이 입력됩니다.

이런 특징을 갖는 인덱스를 'right-growing 인덱스'라고 부르며, 여러 프로세스에 의한 동시 입력이 많을 때 트랜잭션 성능을 떨어뜨리는 주범입니다. 입력 값이 다르므로 로우 Lock 경합은 발생하지 않지만 같은 블록을 갱신하려는 프로세스 간 버퍼 Lock 경합이 발생하기 때문입니다. 이때 나타나는 대기이벤트가 buffer busy waits입니다.

이런 인덱스 경합은 흔히 발생하는 현상으로서 RAC환경에서 특히 심각한 성능 저하를 일으키며, gc buffer busy 이벤트로 관찰됩니다. 여러 노드가 동시에 Current 블록 하나를 서로 주고 받으며 값을 insert 해야 하기 때문입니다.

해결방법으로는 Reverse Key 인덱스로 바꿔 블록이 분산되도록 하거나 그보다 오라클이 권하는 솔루션으로서 인덱스를 해시 파티셔닝하는 방법이 있습니다. 해시 파티셔닝을 하면 값이 순차적으로 증가하더라도 해시 함수에서 리턴된 값에 따라 서로 다른 파티션에 입력되므로 그만큼 경합을 줄일 수 있습니다.

 

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

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

결합 인덱스 컬럼 간 순서를 정할 때도 선택도가 낮은, 즉 변별력이 높은 쪽을 앞에 두는 것이 유리하다고 알려져 있는데, 개별 컬럼의 선택도가 고려사항은 될 수 있지만 어느 쪽이 유리한지는 상황에 따라 다릅니다.

 

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

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

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

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

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

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

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

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

 

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

오히려 고객등급을 선두에 두면 나중에 이 컬럼이 조건절에서 누락되거나 범위검색 조건으로 조회되는 일이 생기더라도 Index Skip Scan을 효과적으로 활용할 수 있어서 유리합니다. Index Skip Scan은 선행 컬럼의 Distinct Value 개수가 적고, 후행컬럼의 Distinct Value개수가 많아야 효율적입니다.

 

굳이 Index Skip Scan이 아니더라도 In-List로 값을 제공함으로써 쉽게 튜닝할 여지가 생깁니다. 고객번호를 선두에 두었는데 조건에서 누락되거나 범위검색 조건으로 조회된다면 그런 튜닝 기법을 적용하기가 곤란합니다.

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

(톰 카이트의 설명에 의하면 변별력이 좋은 컬럼을 선두에 두는 것이 좋다는 상식은 오라클 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만명이고 상품개수는 10000개입니다. 이때 아래 두 인덱스 구성전략 중 하나를 선택한다면?

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

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

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

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

 

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

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

<1> where 거래일자 = :a and 상품번호 = :b

<2> where 거래일자 = :a and 고객번호 = :b

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

 

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

 

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

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

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

 

create table t as select rownum a, rownum b, rownum c, rownum d, rownum e from dual

connect by level <= 100000;

create index t_index on t(a,b,c,d);

 

위와 같이 테이블,인덱스를 구성했다면 아래 쿼리는 모두 order by 가 생략됩니다.

select * from t where a=1 order by a, b, c;

select * from t where a=1 and b=1 order by c,d;

select * from t where a=1 and c=1 order by b,d;

select * from where a=1 and b=1 order by a,b,c,d;

 

아래처럼 order by 절에 기술한 순서가 인덱스 순서와 일치하기만 한다면 조건절에서 어떤 연산자로 비교되더라도 정렬 작업이 생략될 수 있습니다.

 

select * from t where a between 1 and 2

and b not in (1,2)

and c between 2 and 3

order by a,b,c,d;

 

select * from t

where a between 1 and 2

and c between 2 and 3

order by a,b,c;

 

select * from t

where a between 1 and 2

and b<> 3

order by a,b,c;

 

아래 경우에도 정렬작업이 생략되긴 하지만 인덱스 선두 컬럼이 조건절에 누락됐으므로 Index Full Scan방식으로 처리됩니다.

select /*+ index(t) */ * from t

where b between 2 and 3

order by a,b,c,d;

 

위쿼리등에 대해 정렬작업을 생략할 수 있다는 뜻이지 옵티아미저가 항상 그렇다는 뜻은 아닙니다. 통계정보를 기반으로 비용을 계산한 결과 옵티마이저가 Table Full Scan을 선택하거나 다른 인덱스를 선택한다면 정렬 작업은 별도로 수행됩니다.

 

아래는 인덱스를 이용한 소트 오퍼레이션 대체가 불가능한 경우입니다.

select * from t where a =1 order by c;

 

select * from t where a =1 and b between 1 and 2 order by c,d ;

 

select * from t where a =1 and b between 1 and 2 order by a,c,d;

 

(5)인덱스 설계도 작성

위와 같은 설계도를 작성해서 효율적인 인덱스를 구성할 수 있습니다.