by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

8. 인덱스 설계




인덱스 설계

가장 중요한 두 가지 선택 기준
  1. 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.
  2. '='조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다.
인덱스 설계는 공식이 아닌 전략과 선택의 문제
  1. 간단한 설계 예제
    1. 개별 쿼리 기준으로는 어떤 인덱스가 더 좋은지 명확히 구분 가능
    2. 시스템 전체적인 관점에서의 효율은 또는 다른 기준, 쿼리 수행 빈도, 업무상 중요도, DML부하, 저장 공간, 관리 비용 같은 상황적 요소
  2. 효율성 비교 분석(Block IO 발생 정도)
    1. 성능에 결정적인 영향을 미치는 테이블 Random액세스 측면
    2. 인덱스 스캔 효율 측면
  3. 스캔 효율성 이외의 판단 기준
    1. 쿼리 수행 빈도
    2. 업무상 중요도
    3. 클러스터링 팩터
    4. 데이터량
    5. DML부하(=기존 인덱스 개수, 초당 DML발생량, 자주 갱신되는 컬럼 포함 여부 등)
    6. 저장 공간
    7. 인덱스 관리 비용등
  4. 인덱스 설계는 공식이 아닌 전략과 선택의 문제
    1. 시스템 전체적인 시각에서 대안 전략들을 수립
    2. DML부하를 줄이는 것(개별 쿼리 성능뿐만 아니라 인덱스 개수를 최소화)
  5. 인덱스 전략 수립을 위한 훈련
    
    조건 : 
      1.고객은 100만 명이고, 거주지역으로는 15개의 값이 있으며 분포는 균일하다고 가정
      2.쿼리1과 쿼리4는 수행빈도가 매우 높고, 쿼리2와 3은 그다지 높지 않음
      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;
    
    
결합 인덱스 컬럼 순서 결정 시, 선택도 이슈

-선택도가 높은 인덱스는 생성해 봐야 효용가치가 별로 없다.

  1. 선택도가 액세스 효율에 영향을 주지 않는 경우
           '='조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미 없는 or 손해
       
  2. 선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우
       선택도가 높은 컬럼을 선두에 두면 나중에 이 컬럼이 조건절에서 누락되거나 범위검색 조건으로 조회되는 일이 생기더라도 오라클 9i부터 제공되는 Index Skip Scan을
    효과적으로 활용할 수 있어 유리함.(Index Skip Scan 또는 IN-List는 Distinct Value개수가 적고, 후행 컬럼의 Distinct Value개수가 많아야 효율적이다.)
           
    참고, 톰 카이트(Tom Kyte)의 설명에 의하면 변별력이 좋은 컬럼(선택도가 낮은 컬럼)을 선두에 두는 것이 좋다는 상식은 오라클 5 버전에서 사용하던 인덱스 압축 방식 
    때문에 생긴 것이고 그런 특징은 오라클 6에서 로우 단위 Lock이 구현되면서 사라졌다고 한다.
       
  3. 상황에 따라 유*불리가 바뀌는 경우
     
        -선택도가 높은 컬럼을 인덱스 선두에 두면 나중에 범위검색 조건이 사용되거나 아예 누락되더라도 Index Skip Scan또는 IN-List를 활용할 수 있어 유리한다. 
            (단, Distinct Value 개수가 충분히 적은지가 관건)  
    -선택도가 낮은 컬럼을 선두에 두면 이를 범위검색 조건으로 조회하는 일이 생겼을 때 불리하지만, 입력 값의 범위가 좁다면 비효율이 크지 않아 
    Index Skip Scan이나 IN-List를 활용하지 못하더라도 오리려 유리할 수 있다. 
       
  4. 선택도가 '낮은 컬럼'을 앞쪽에 두는 것이 유리한 경우
       -범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리함.
        예 : where 고객번호 = :a and 상품번호 =:b and 거래일자 between :c and :d
           
          조건 : 고객 : 100만명 , 삼품개수 : 10,000
     
            X01 인덱스 : 고객번호 + 거래일자 + 상품번호 => 변별력(선택도가 낮은)이 좋은 고객번호에 의해 스캔 범위가 최소화되기 때문에 비효율이 크지 않음
             X02 인덱스 : 상품번호 + 거래일자 + 고객번호 => 변별력(선택도가 낮은)이 좋은 고객번호가 필터 역할을 하므로 비효율적이다. 
       
  5. 선택도가 낮은 컬럼을 '선택'하는 것이 유리한 경우
        질문 : 아래와 같은 조건절 하에서 만약 거래일자를 선두에 둔 '단 하나'의 인덱스를 생성하기로 하였고, 후행 컬럼으로서 상품번호와 고객번호 둘 중 하나만 '선택'하라면 어느 쪽을 택하겠는가?
               (과정: 두 검색조건은 사용빈도가 같다. 고객 : 100만명 , 삼품개수 : 10,000)
      
           검색조건 1 : where 거래일자 = :a and 상품번호 =:b
           검색조건 2 : where 거래일자 = :a and 고객번호 =:b
    
         결론 : 선택도가 낮은 고객번호를 선택하는 것이 현명
                  - 더 적은 양의 테이블 Random 액세스가 발생
       

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

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

-인덱스는 항상 정렬 상태를 유지(order by, group by를 위한 소트 연산을 생략가능)
-조건절에 사용되지 않은 컬럼이더라도 소트 연산을 대체할 목적으로 인덱스 구성에 포함시킴


-order by, group by 절에서 소트 오퍼레이션 생략 조건
1. 인덱스 컬럼 구성과 같은 순서로 누락 없이(뒤쪽 컬럼이 누락되는 것은 상관없음) order by절에 기술해 주어야함.
   단. 인덱스 구성 컬럼이 조건절에서 '='연산자로 비교된다면, 그 컬럼은 order by절에서 누락되거나 인덱스와 다른 순서로 기술하더라도 상관없음.


SQL> create table t
  2  as
  3  select rownum a, rownum b, rownum c, rownum d, rownum e
  4  from dual
  5  connect by level < 100
  6  /

테이블이 생성되었습니다.

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

인덱스가 생성되었습니다.

SQL> select * from t;

         A          B          C          D          E 
---------- ---------- ---------- ---------- ---------- 
         1          1          1          1          1 
         2          2          2          2          2 
			|
			|
			|
        99         99         99         99         99 

99 개의 행이 선택되었습니다.


Execution Plan
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=99 Bytes=6435)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=3 Card=99 Bytes=6435) 
   

예)인덱스 : create index t_idx on t( a, b, c, d);

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

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



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


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



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


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65) 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



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


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65) 
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



2. order by절에 기술한 순서가 인덱스 순서와 일치하기만 한다면 조건절에서 어떤 연산자로 비교되더라도 정렬 작업이 생략가능
       
SQL> select * from t
  2  where a between 1 and 2
  3    and b not in(1, 2)
  4    and c between 2 and 3
  5  order by a, b, c, d;


Execution Plan
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)  
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1) 

SQL> select * from t
  2   where a between 1 and 2
  3     and c between 2 and 3
  4  order by a, b, c;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=65)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 1 Bytes=65)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1) 

 
SQL> select * from t
  2   where a between 1 and 2
  3     and b <> 3
  4  order by a, b, c;

Execution Plan
----------------------------------------------------------   
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=130 )
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=2 Bytes=130)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



3. 정렬 작업이 생략되지만 인덱스 선두 컬럼이 조건절에 누락됐으므로 Index Full Scan방식으로 처리
   
SQL> select /*+ index(t) */ * from t
  2   where b between 2 and 3
  3  order by a, b, c, d;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=130)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card= 2 Bytes=130)
   2    1     INDEX (FULL SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



4. 소트 오퍼레이션 대체가 불가능한 경우

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


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=65)
   1    0   SORT (ORDER BY) (Cost=3 Card=1 Bytes=65) 
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65) 
   3    2       INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



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


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=65)
   1    0   SORT (ORDER BY) (Cost=3 Card=1 Bytes=65)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65) 
   3    2       INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



SQL> select * from t
  2   where a = 1
  3     and b between 1 and 2
  4  order by a, c, b;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=65)
   1    0   SORT (ORDER BY) (Cost=3 Card=1 Bytes=65)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=65) 
   3    2       INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)  
인덱스 설계도 작성

그림1-57 참조

  • 조건절에 항상 사용되는 컬럼이 인덱스 선두로서 1순위 후보지만 '=' 조건이어야 한다는 사실

문서에 대하여

  • 최초작성자 : 헌쓰
  • 최초작성일 : 2010년 08월 20일
  • 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.