티스토리 뷰
촐처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3902763
08 인덱스 설계
(1) 가장 중요한 두 가지 선택 기준
인덱스에 사용할 컬럼을 선택하고, 순서를 정하는 데는 중요한 기준이 두 가지 있다.
(2) 인덱스 설계는 공식이 아닌 전략과 선택의 문제
- 공식은 기본이 되는 기준이다. 공식만 지키다 보면, 인덱스 개수만 자꾸 늘어간다.
- 인덱스 설계에는 정답이 없다. 그러므로, 인덱스 구성을 왜 그렇게 했는지에 대해 판단근거를 논리적으로 제시할 수 있으면 된다.
- 최소의 인덱스의 개수로 다수의 DML의 성능을 만족시키는 것이 궁극의 목표다.
간단한 설계 예제
- 거래 테이블을 액세스하는 조건절이 아래 4가지만 있다고 가정하면, 어떻게 인덱스를 구성하는 것이 최적일까?
(고객수:100만명, 상품:10만개, 거래일자의 검색범위는 유동적)
<검색조건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 거래일자 between '20090101'and '20090331'
- 스타일 A: 모든 조건절 경우 만큼, 인덱스 만들거야? DML부하도 있고 관리도 힘들고.
- 스타일 B: 조건3일때, X1을 사용하면, 테이블필터링이 발생하나, 고객번호가 워낙 변별력이 좋으니 괜찮겠지? 허나 아주 넓은 거래일자를 범위검색하면 상품번호 필터링때문에 다소 비효율적
- 스타일 C: 그래서!! 스타일 B에서 조건3을 비효율을 만회하고자, X1인덱스에 상품번호를 추가하였어..
- 스타일 D: 스타일B와 유사한 전략으로 여기도 조건3의 비효율을 만회하려고, X2인덱스에 고객번호를 추가했는데, 상품번호의 선택도가 고객번호보다 높아 C보다는 인덱스스캔이 많을걸.
- 스타일 E: between 조건 뒤는 거의 인덱스 필터역할만 하므로 둘중하나만 있어도 무방. 그럼 스타일 I랑 동일
- 스타일 F: 4개의 조건에는 그닥 효과없음
- 스타일 G: 검색조건 1,2 에서 인덱스 스캔 비효율.
- 스타일 H: 검색조건 2,4에대한 대비 없고, 검색조건1에서도 인덱스 스캔 비효율큼
- 스타일 I: 변별력이 좋은 고객번호가 조건에 포함되면 X1인덱스를 효과적으로 이용가능, X2의 선두에 거래일자가 약간의 비효율이 있으나 범용적으로 사용하는 컬럼이므로 좋게 평가.
- 스타일 J: 인덱스 활용성이 높으나, 근본적으로 비효율이 있으므로 신중히 고려
효율성 비교분석
- 스타일 A와 I의 설계의 효율 비교
스타일A 스타일I X1:고객번호+거래일자
X2:상품번호+거래일자
X3:상품번호+고객번호+거래일자
X4:거래일자X1:고객번호+거래일자
X2:거래일자+상품번호+고객번호
| 조건 | 스타일A | 스타일I | 비고 |
|---|---|---|---|
| 조건1 | X1 | X1 | 두번의 테이블 Random Access |
| 조건2 | X2 | X2-불필요한 상품번호까지 스캔하는 비효율존재 | 세번의 테이블 Random Access |
| 조건3 | X3-단 한건의 Random Access | X1-두번의 테이블액세스, 거래범위가 좁으면 X2를 이용해 불필요한 Random Access를 없애는게 좋을수도 | |
| 조건4 | X4 | X2 | 동일한 테이블 Random Access, Range파티션 고려 |
- 테이블 Random 액세스 측면에서 보면, 스타일I가 조건3에서 약간불리. 하지만 고객번호가 변별력이 워낙 좋으니 큰 차이가 안날 수 있다. 부하가 심하면 X2사용하시고!
- 인덱스스캔효율 측면에서 보면, 스타일I가 조건2, 조건3에서 비효율적. 하지만 인덱스 블록에는 하나당 수백개의 레코드가 담기므로 괜찮을것이다.
스캔 효율성 이외의 판단 기준
- 인덱스를 설계할 때는 인덱스 스캔의 효율성 뿐만아니라, 시스템 전체적인 관점에서의 아래와 같은 추가적인 요소들을 고려해야한다.
- 쿼리수행빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML부하(=기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)
- 저장공간
- 인덱스 관리비용 등
인덱스 설계는 공식이 아닌 전략과 선택의 문제
- 인덱스 설계는 개별 쿼리 성능 뿐만 아니라, 인덱스 개수를 최소화하여 DML부하를 줄이는 것도 목표로 삼아야 한다.
- 그리고 왜 그런 선택을 했는지, 전략적 판단 근거가 무엇인지 답할 수 있어야 한다.
- 실무적으로는 파티션 설계를 먼저 진행하거나, 최소한 인덱스 설계와 병행하는 것이 바람직하다.
인덱스 전략 수립을 위한 훈련
- 고객 테이블에 대해 인덱스 구성전략 수립해보기.
<쿼리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 /*+ orderd use_nl(b) */ * from 주문 a, 고객 b where a.거래일자 = :tdate and a.상품번호 = :good and a.고객번호 = b.고객번호 and b.거주지역 = :region;
- 그대가 생각하는 최선의 인덱스 구성은 ?
IX01: 고객번호+거주지역(쿼리1, 쿼리4)
IX02: 연령+거주지역(쿼리2, 쿼리3)
(3) 결합 인덱스 컬럼 순서 결정 시, 선택도 이슈
- 선택도 : 전체레코드 중에서 조건절에 의해 선택되는 비율
- 선택도가 높은 인덱스는 생성해봐야 효용가치가 없음 (Full Table Scan이 비용이 더 적게드니까..)
- 결합 인덱스 컬럼간에 순서를 정할 때, 선택도가 낮은 컬럼을 앞에 두는게 항상 유리할까?
그때 그때 달라요!!
선택도가 액세스 효율에 영향을 주지 않는 경우
- '='조건으로 항상 사용되는 컬럼들중에 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미없거나, 더 손해일 수 있다.
| IDX01 : 고객등급 + 고객번호 + 거래일자 + 거래유형 + 상품번호 |
항상 '=' 조건으로 사용하는 컬럼 : 고객등급, 고객번호(고객등급은 선택도가 높고, 고객번호는 선택도가 낮다.)
between으로 사용하는 컬럼 : 거래일자
- 선택도가 낮은 고객번호를 고객등급보다 앞에 두는게 유리할까? 아니. 별로 상관없다.
- 수직적 탐색과정에서는 모든 인덱스 컬럼이 비교조건으로 사용하므로 스캔시작지점은 어느것을 앞에 두더라도 동일하게 결정된다.
- 선행컬럼이 모두 '='조건이면 범위검색까지의 인덱스 레코드는 한곳에 모여 있으며, 선택도가 높은 컬럼(고객등급)을 앞에두더라도 수평적 탐색을 위한 스켄범위는 최소화 될 것이므로 인덱스 엑세스 효율에 영향을 미치지 않는다.
선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우
- 오히려 선택도가 높은 컬럼(고객등급)을 선두에 두면 오라클 9i부터는 Index Skip Scan을 효과적으로 사용할 수 있어 유리.
(Index Skip Scan은 선행컬럼의 Distinct Value개수가 적고, 후행컬럼의 Distinct Value개수가 많아야 효율적이기 때문) - 그 밖에도 IN-List로 값을 제공함으로써 쉽게 튜닝할 여지를 준다.
- 인덱스 압축기능을 고려하더라도 고객등급을 앞으로 두는게 유리
상황에 따라 유,불리가 바뀌는 경우
- 선택도가 높은 컬럼을 선두에 두면 나중에 범위검색조건이 사용되거나 조건절에 누락되더라도 ISS 또는 IN-List를 활용할 수 있어 유리
- 선택도가 낮은 컬럼을 선두에 두면 범위검색조건으로 조회할 때는 불리하나, 입력값의 범위가 좁다면 비효율이 크지않아 ISS나 IN-List를 활용못해도 오히려 유리할 수 있다.
선택도가 '낮은 컬럼'을 앞쪽에 두는 것이 유리한 경우
- 범위조건을 사이에 둔 컬럼은 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다.
| where 고객번호 = :a and 상품번호 = :b and 거래일자 between :c and :d |
- 위와 같은 조건절을 사용하는 쿼리에서 두가지 공식에 입각하여 아래와 같은 인덱스 중 하나만 선택해야 한다면?
X01 : 고객번호 + 거래일자 + 상품번호 => 이거!!
X02 : 상품번호 + 거래일자 + 고객번호
=>최선의 인덱스 설계는 고객번호+상품번호+거래일자
선택도가 낮은 컬럼을 '선택'하는 것이 유리한 경우
| <검색조건1> where 거래일자 = :a and 상품번호 = :b <검색조건2> where 거래일자 = :a and 고객번호 = :b |
- 위와 같은 조건절에서 거래일자를 선두에 둔 단하나의 인덱스를 생성한다고 한다면 어떤 인덱스 선택할래?
거래일자+상품번호
거래일자+고객번호
=>사용빈도를 보고 선택, 빈도까지 같다면, 선택도가 낮은 컬럼(고객번호)를 선택하는 것이 현명
(4) 소트 오퍼레이션을 생략하기 위한 컬럼 추가
- 인덱스는 항상 정렬상태를 유지한다. 인덱스를 이용하면 우리가 select절에 기술한 order by, group by의 연산을 생략할 수도 있다.
- 인덱스를 이용하여 소트연산을 대체하려면, 인덱스 컬럼 구성과 같은 순서로 누락없이 order by절에 기술한다. 단, 인덱스 구성 컬럼이 조건절에서 '='조건으로 비교되면, 누락되거나, 다른 순서로 기술해도 상관없다.
SQL> create table t as select rownum a, rownum b, rownum c, rownum d, rownum e from dual connect by level <= 100000; 테이블이 생성되었습니다. SQL> 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;
SQL> explain plan for
2 select * from t
3 where a between 1 and 2
4 and b not in (1, 2)
5 and c between 2 and 3
6 order by a, b, c, d;
해석되었습니다.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
SQL> explain plan for
2 select * from t
3 where a between 1 and 2
4 and c between 2 and 3
5 order by a, b, c;
해석되었습니다.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
SQL> explain plan for
2 select * from t
3 where a between 1 and 2
4 and b <> 3
5 order by a, b, c;
해석되었습니다.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Index Full Scan 방식으로 정렬작업 생략
SQL> explain plan for
2 select /*+ index(t) */ * from t
3 where b between 2 and 3
4 order by a, b, c, d;
해석되었습니다.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3778778741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 434 (2)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 434 (2)| 00:00:06 |
|* 2 | INDEX FULL SCAN | T_IDX | 1 | | 433 (2)| 00:00:06 |
-------------------------------------------------------------------------------------
인덱스로 소트오퍼레이션 대체 불가
(CASE 1)
SQL> explain plan for
2 select * from t where a=1 order by c;
해석되었습니다.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1454352066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 24 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
(CASE 2)
SQL> explain plan for
2 select * from t
3 where a=1
4 and b between 1 and 2
5 order by c, d;
해석되었습니다.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1454352066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 24 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
(CASE 3)
SQL> explain plan for
2 select * from t
3 where a=1
4 and b between 1 and 2
5 order by a, c, b;
해석되었습니다.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1454352066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 24 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
- 단, 정렬연산을 생략할 수 있다는 것이지, 항상 그런건 아니다. 옵티마이저 판단에 의해 Full Table Scan을 선택하거나, 다른 인덱스를 선택한다면 얘기는 또 달라진다.
(5) 인덱스 설계도 작성
- 인덱스 설계시에는 시스템 전체효율을 고려하자.
인덱스 설계도 작성을 통한 튜닝 사례
p198 그림 1-57 참고
문제점 분석
- 액세스 경로가 총 25개인데 인덱스가 15개?
- 대부분 액세스 경로에 입력일 > = 이 포함되어 있는데, 대부분 인덱스의 선두 컬럼이 입력일이라는 거?
- 선두컬럼 입력일 조건에 해당하는 범위가 인덱스 전체 레코드에서 95%이상
문제점 해결
- 입력일 컬럼을 제거하고 인덱스를 5개로 줄임
(과거 데이터 정확히 커팅 안된 것들 잘 정리 했으니까, 입력일 컬럼을 제거한거겠지????
어찌 어찌해서 5개로 줄였다.. 머 이런건 설명이 별루 없네..)
댓글
공지사항
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
링크