08 인덱스 설계

인덱스를 구성할 때의 컬럼 순서 결정 원리

(1) 가장 주용한 두 가지 선택 기준

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

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

간단한 설계 예제 ( Page.182 )

• 총 고객 수 : 100만
• 총 상품 : 10만
• 시스템 전체적인 관점 : 쿼리 수행 빈도, 업무상 중요도, DML 부하, 저장 공간, 관리 비용 ( 상황 적인 요소 고려 )

-- 검색 조건 1
where 고객번호 = 1
  and 거래일자 between '20090101' and '20090331'
  
-- 검색 조건 2
where 상품번호 = 'A'
  and 거래일자 between '20090101' and '20090331'
  
-- 검색 조건 3
where 고객번호 = 1
  AND 상품번호 = 'A'
  and 거래일자 between '20090101' and '20090331'
  
-- 검색 조건 4
where 1 = 1
  and 거래일자 between '20090101' and '20090331'

• 스타일 A : '=' 조건 컬럼을 모두 선두에 둠으로써 모든 조건에 대해 인덱스 스캔 효율을 100%
  달성하려는 스타일이다. DML 부하와 관리비용이 조금 증가할 것이고, 나중에
  새로운 액세스 유형이 도출됐을 때 이미 많아져 버린 인덱스 개수 때문에 고민스로울 수 있다.
• 스타일 B : 고객번호별 조회와 상품번호별 조회를 둘 다 중요한 액세스 경로로 판단하였다.
  검색조건 3일 때 테이블 필터링이 발생하긴 하겠지만 고객번호의 변별력이 워낙 좋아
  인덱스를 사용함으로써 테이블 액세스를 최소화할 수 있다고 판단한 것 같다.
  아주 넓은 거래 일자 조건을 입력할 때는 상품번호 필터링 대문에 다소 비효율이 생길 수 있다.
• 스타일 C : 스타일 B와 기본적으로 같은 전략으로 볼 수 있다. 다만 검색조건 3일 때도 불필요한
  테이블 액세스가 발생하지 않도록 하려고 X1 인덱스에 상품번호를 추가한 점이 다르다.
  상품번호가 인덱스 필터 역할만 하지만 선두 컬럼이 고객번호가 워낙 변별력이 좋아
  인덱스 스캔 비효율은 아주 미미할 것으로 판단한 것 같다. 매우 좋은 전략이라고 평가 할 수 있다.
• 스타일 D: 마찬가지로 스타일 B와 기본적으로 같은 전략이라고 할 수 있다.
  다만 검색 조건 3일 때 불필요한 테이블 액세스가 발생하지 않도록 하려고 X2 인덱스에
  고객번호를 추가한 점이 다르다. 테이블 액세스는 최소화 할 수 있지만, 상품번호의 선택도가
  고객번호보다 높아서 스타일 C와 비교해 인덱스 스캔량이 더 많을 것이다.
  검색조건 4에 대한 고려가 없는 것도 흠이지만 거래일자 검색 범위가 넓어 일부러 Full Table Scan
  으로 유도하려는 것일 수 있다. 또는 거래일자 기분 Range 파티셔닝을 전제로 한 것인지도 모른다.
• 스타일 E : BETWEEN 조건을 최선두에 둔다면 나머지 조건은 거의 인덱스 필터 역할만 하므로 적어도
  검색조건 1~4를 위해서라면 둘 중 하나를 제거해도 무방하다.
  그러면 스타일 J와 같은 전략이다.
• 스타일 F : 검색조건 1~4를 위한 것이라며 그다지 효과적이지 못하다.
• 스타일 G : 상품번호 조회하는 검색조건 2는 물론 고객번호로 조회하는 검색조건 1에도
  인덱스 스캔 비효율이 생긴다. 특히 변별력이 아주 좋은 고객번호로 조회할 때의 비효율은 그만큼
  더 크다고 하겠다.
• 스타일 H : 검색조건 2와 4에 대한 대비가 없고, 고객번호로 조회하는 검색조건 1에서도 인덱스 스캔 비효율이 크다
• 스타일 I : X2 인덱스 선두에 거래일자를 둠으로써 약간의 비효율은 생기겠지만 항상 사용되는
  컬럼이므로 모든 검색조건에 범용적으로 사용될 수 있다.
  대신 사용빈도가 높고 변별력이 아주 좋은 고객번호가 조회 조건에 포함될 때만이라도
  X1인덱스를 효과적으로 이용하려는 의도가 엿보인다.
  어떤 검색조건에 대해서도 테이블 램덤 액새스만큼은 최소화할 수 있어, 자주 임력되는
  거래일자 범위가 아주 넓지만 않다면 성능이 그다지 나쁘지 않다.
  모든 검색 조건을 만족하는면서도 인덱스 개수를 최소화했다는 측면에 높이 평가할 수 있다.
• 스타일 J : 인덱스 하나로 모든 조건절을 해결하려는 의도가 담긴 것 같다. 인덱스 활용성이 높고
  상황에 따라서 무리없이 잘 운영될 수 있지만, 근본적을 비효율을 안고 있어
  신중히 선택해야 할 설계 방식이다.

효율성 비교 분석 ( Page.184 그림 1-55 )

• 스타일 I 와 스타일 A 비교 분석
• 상품 : A부터 J까지 10개 상품이 하루에 한 번씩만 거래된다고 가정함
• 고객 : 1 ~ 20번 까지 평균적으로 이틀에 한번씩 거래한다고 가정함

-- 전략 1 : 스타일 A
X1 : 고객번호 + 거래일자
X2 : 상품번호 + 거래일자
X3 : 상품번호 + 고객번호 + 거래일자
X4 : 거래일자 

-- 전략 2 : 스타일 I
X1 : 고객번호 + 거래일자
X2 : 상품번호 + 상품번호 + 고객번호

• 검색조건 1( 고객번호 = 1, 거래일자 BETWEEN )
  조건에 해당하는 1번 고객의 거래 데이터가 2번과 3번 블록에 흩어져 있다.
  전략 1과 2 모두 X1 인덱스를 사용하게 될 것이므로 똑같이 두 번의 테이블 랜덤 액세스가
  발생한다.
• 검색조건 2(상품번호 = 'A', 거래일자 BETWEEN )
  조건을 만족하는 상품 A가 1번, 2번, 3번 블록에 흩어져 있다.
  따라서 전략 1에서 X2 인덱스를 사용하나 전략 2에서 X2 인덱스를 사용하나 테이블 랜덤 액세스
  는 똑같이 세 번 발생한다. 전략 2는 X2 인덱스에서 불필요한 상품번호까지 스캔하는 비효율이 있다.
• 검색조건 3(고객번호 = 1, 상품번호='A', 거래일자 BETWEEN)
  조건을 만족하는 거래 데이터는 2번 테이블 블록에서 단 한 건 있다.
  전략 1에서는 X3 인덱스를 사용해 그 한 건이 포함된 단 한 블록만 랜덤 액세스한다.
  반면, 전략 2에서 X1 인덱스를 이용하면 테이블 액세스가 두 번 발생한다.
  거래일자 범위가 넓지 않다면 인덱스 스캔 비효율을 약간 감수하더라도 X2 인덱스를
  사용해 불필요한 테이블 랜덤 액세스를 없애는 편이 나을 수 있다.
• 검색 4( 거래일자 BETWEEN )
  전략 1의 X4 인덱스나 전략 2의 X2 인덱스 둘 다 1~3번 블록을 모두 읽고 테이블
  랜덤 액세스 횟수도 똑같다. 참고로 이런 검색 조건이 자주 사용되고 넓은
  범위의 값이 입력될 가능성이 높다면 Range 파티션 구성을 고려해야 한다.
  인덱스 설계시 파티션 설계를 병행해야 하는 이유가 여기에 있다.

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

• 쿼리 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하
• 저장 공간
• 인덱스 관리 비용

select /*+ ordered use_nl( b ) */
       a.상품평, b.고객번호, b.거래일자, b.거래량, b.거래금액
 from  상품 a, 거래 b
 where a.상품분류 = '가전'
   and b.상품번호 = a.상품번호
   and b.거래일자 between '20090101' and '20090331'

• NL 조인에서 Inner 쪽 테이블로서 자주 액세스되지 않더라도, 고객번호나 상품번호를
  선두로 갖는 인덱스의 클러스터링 팩터가 의외로 좋다면 전략 1과 같은 인덱스 구성은
  그만큼 더 효과적인 것이 된다.
• 데이터량도 중요한 판단 기준이다. 데이터량이 적다면 인덱스를 많이 만들더라도
  저장 공간이나 트랜잭션 부하 측면에 그다지 문제 될 것이 없다.

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

• 목료 : 개별 쿼리 성능뿐만 아니라 그 개수를 최소화함으로써 DML 부하를 줄이는 것까지..

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

• 고객 : 100만명
• 거주지역 : 15개의 값( 서울, 부산, 광주, 대구, 대전, 인천, 경이, 강원, 경북, 경남, 전북, 전남, 충북, 충남, 제주 )
• 수행 빈도 : 1과 4는 매우 높고, 2와 3은 그다지 높지 않다.

-- 쿼리 1
select * from 고객
where 고객번호 = :no;

-- 쿼리 2
select * from 고객
where 연령 = :age
  and 성별 = :gender
  and 이름 like :name ||'%'
  
-- 쿼리 3
select * from 고객
where 연령 between :age1 and :age2
  and 거주지역 = :region
  and 등록일 like :rdate ||'%'

-- 쿼리 4
select /*+ ordered use_nl( b )  */ * from 주문 a, 고객 b
where a.거래일자 = :tdate
  and a.상품번호 = :good 
  and b.고객번호 = a.고객번호 
  and b.거주지역 = :region;
  

인덱스 설계 조정을 통한 튜닝 사례 - Right Growing 인덱스( Page.189 그림 1 - 56 )

• 전형적인 right growing 인덱스 아니다 : 변경일자에는 하루 동안 같은 일자가 계속 입력 되는데,
  그 뒤 서비스 변경코드 컬럼에 따라 몇 개 그룹으로 나뉘어 입력이 진행될 것이기 때문이다
  하지만 그중 특정 서비스 변경코드에 값이 몰리는 상황이다.
• 변경일자 : between 조건 사용 ( 리버스 인덱스 제외 )
• 해쉬 파티션 ( 고객번호 기준 8개 ) : 쓰기 작업 이상으로 읽기 부하가 심각 ( 8개 파티션 조회 : PK 인덱스 기준 부하 )

-- 오픈 이전 부하 테스트 중 경합 발생

PK : 고객번호 + 변겨일자 + 주요변경코드 + 일련번호
N1 : 변경일자 + 서비스변경코드 -- buffer busy waits 경합 ( 인덱스 분할 경합 )
N2 : 판매부서ID + 서비스변경코드 + 변경일자 + 고객번호 + 서비스코드
N3 : 고객번호 + 일련번호 + 서비스변경코드 + 서비스변경사유코드 + 주요변경코드

• 기막힌 아이디어 제시: N1 인덱서에 고개번호를 추가하자는 것
  • 인덱스 키 값이 같을 때 rowid 순으로 정렬되는데 뒤쪽에 변별력이 좋은 고객번호를 추가한다면
    rowid 이전에 고객번호에 따라 여러 블록에 골고루 분산되는 효과가 나타난다는 것이다.
• 결론 : N1 인덱스를 사용하는 SQL 조회 -> SQL 조건에 판매부서ID 조건 추가하여 N2 인덱스를 사용 -> N1 인덱스 삭제 ( ?? )

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

• 선택도가 낮다 : 선택되는 양이 적다
• 선택도가 높다 : 선택되는 양이 많다

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

• 고객번호 와 고객등급 '=' 조건, 거래일자는 between
• 거래유형 과 상품번호는 항상 사용되는 조건이 아니어서 인덱스를 아래와 같이 구성

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

• 변별력이 좋은 고객번호를 앞으로 둘 이유가 없다 : 둘다 = 조건

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

• IN-LIST OR INDEX Skip Scan : 튜닝할 여지가 생김 ( 인덱스 압축도 같음 )

상황에 따라 유불리가 바뀌는 경우 ( Page.193 )

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

• 고객 : 100만
• 상품 : 1만

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

-- x01이 유리
x01 : 고객번호 + 거래일자 + 상품번호
x02 : 상품번호 + 거래일자 + 고객번호

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

• 거래일자를 선두에 둔 '단 하나의'의 인덱스를 생성하기로 하였고, 후행 컬럼으로서 상품번호와 고객번호
  둘 중에 하나만 '선택'하라면 어쪽은 택하겠는가? ( 사용빈도 같음 )
• 결론적으로 결합 인덱스 컬럼 간 순서를 정할 때는 개별 컬럼의 선택도보다는 조건절에서 어떤 형태로
  자주 사용되는지, 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은지 등이
  더 중요한 판단기준이다.

-- 검색 조건 1
where 거래일자 = :a and 상품번호 = :b

-- 검색 조건 2
where 거래일자 = :a and 고객번호 = :b

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

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

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 t where a = 1 and b = 1 order by  a, c, b, d;  --소트 생략

--소트 생략
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;

--소트 생략 : IFS
select /*+ index( t ) */ * from t
where b between 2 and 3
and b <> 3
order by a, b, c, d;

--소트 발생
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) 인덱스 설계도 작성 ( Page.198 그림 1-57 )

• ㄷㄷ

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

• ㄷㄷ

문서에 대하여

• 최초작성자 : 이재현
• 최초작성일 : 2016년 06월 11일
• 이 문서는 오라클클럽 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법 I'를 참고하였습니다.*