8. 인덱스 설계
2011.03.06 20:00
08 인덱스 설계
(1)가장 중요한 두 가지 기준
1. 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.
2. '=' 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다
(2)인덱스 설계는 공식이 아닌 전략과 선택의 문제
▶간단한 설계 예제 - 전제조건 총 고객수 100만명, 상품은 10만개
------------------------------------------------------------------
| 검색조건 1 | WHERE 고객번호 = 1 |
| | AND 거래일자 BETWEEN '20090101' AND '20091231' |
------------------------------------------------------------------
| 검색조건 2 | WHERE 상품번호 = 'A' |
| | AND 거래일자 BETWEEN '20090101' AND '20091231' |
------------------------------------------------------------------
| 검색조건 3 | WHERE 고객번호 = 1 |
| | AND 상품번호 = 'A' |
| | AND 거래일자 BETWEEN '20090101' AND '20091231' |
------------------------------------------------------------------
| 검색조건 4 | WHERE 거래일자 BETWEEN '20090101' AND '20091231' |
------------------------------------------------------------------
▶Review(p182 참조)
A : '=' 조건 컬럼을 모두 선두에 사용할려는 인덱스 스캔 효율을 100% 달성하려는 스타일
나중에 새로운 액세스 유형이 도출되었을 때 문제가 될 수 있다.
C : 검색조건 3일 때도 불필요한 테이블 액세스가 발생하지 않도록 하려고 X1 인덱스에
상품번호를 추가한 점이 좋은 전략이라고 평가할 수 있다.
(중간 생략...)
I : X2 인덱스 선두에 거래일자를 둠으로 약간의 비효율은 생기겠지만 모든 검색조건에
범용적으로 사용될 수 있다. 또한 모든 검색 조건을 만족하면서도 인덱스 개수를
최소화 했다는 측면에서 높이 평가할 수 있따.
▶효율성 비교 분석 - Data상황 및 전제조건 참조 (그림 1-55 참조)
-----------------------------------------------------------------------------
[전략1 : 스타일 A] | [전략2 : 스타일 I]
-----------------------------------------------------------------------------
X1 : 고객번호 + 거래일자 | X1 : 고객번호 + 거래일자
X2 : 상품번호 + 거래일자 | X2 : 거래일자 + 상품번호 + 고객번호
X3 : 상품번호 + 고객번호 + 거래일자 |
X4 : 거래일자 |
-----------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| 검색조건 1 | 1번 고객의 거래 데이터가 2,3 블럭에 흩어져 있다.
| | 따라서 전략1,2 모두 X1 인덱스를 사용함으로 동일함
--------------------------------------------------------------------------------------
| 검색조건 2 | 상품 A가 1,2,3 블럭에 흩어져 있다.
| | 전략 1,2 모두 X2 인덱스 사용. Random Access또한 3번 동일
| | 다만 전략 2의 x2는 불필요한 상품번호까지 스캔(인덱스의 구성차이)
--------------------------------------------------------------------------------------
| 검색조건 3 | 조건을 만족하는 데이터는 2번 테이블 블록에 단 한건 존재
| | 전략 1의 X3 인덱스는 단 한 블록만 Random Access
| | 전략 2의 x1 인덱스 이용시는 테이블 Access가 2번 발생
| | 거래일자 범위가 넓지 않다면 인덱스 비효율을 감소하더라도
| | X2 인덱스를 사용해 불필요한 테이블 Random Access를 고려할 수 있음
--------------------------------------------------------------------------------------
| 검색조건 4 | 전략 1의 X4 인덱스나 전략 2의 X2 인덱스 둘다 1~3 블럭을 동일하게
| | 처리함. Random Access 횟수도 똑같음
--------------------------------------------------------------------------------------
(3)결함 인덱스 컬럼 순서 결정 시, 선택도 이슈
선택도(Selectivity) : 전체 레코드 중에서 조건절에 의해 선택되는 비율. 여기서는 인덱스
컬럼이 모드 '='로 조회할 때 평균적으로 선택되는 비율
인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준임이 틀림없지만
결합 인덱스 컬럼 간 순서를 정할 때 변별력이 높은 쪽을 앞에 두는 것이 유리한지는 상황에
따라 다르다.
▶선택도가 액세스 효율에 영향을 주지 않는 경우
eg) IDX01 : 고객등급 + 고객번호 + 거래일자 + 거래유형 + 상품번호
예를 들어 고객번호와 고객등급은 '=' 조건으로, 거래일자는 between으로 사용된다.
'=' 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에
두려는 노력은 의미가 없거나 오히려 손해힐 수 있다. 여기선 첫번째 범위 검색(Range Scan)
조건(위에선 거래일자 between으로 Range Scan)까지의 인덱스 레코드들은 모두 한 곳에 모여
있기에 스캔 범위는 최소화될 것이고 인덱스 액세스 효율에 영향을 미치지 않는다.
▶선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우
위의 예제에서 [고객등급]을 선두에 두면 나중에 이 조건절이 누락되거나 범위검색으로 조회
되는 일이 생기더라도 Index Skip Scan을 효과적으로 할용할 수 있어서 유리하다.
참고로 Index Skip Scan은 선행 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct
Value 개수가 많아야 효율적이다. 또한 ISS(Index Skip Scan)가 아니더라도 첫 컬럼에 대해 In-List로 값을
제공할 수 있기에 튜닝의 여지가 생긴다.
▶상황에 따라 바뀌는 경우
위의 예제에선 두 컬럼 모두가 '=' 조건인 경우에 대해서 살펴 봄
아래는 두 컬럼 모두가 '='가 아닌 최소한 한 컬럼이 Range로 비교되는 경우는 상황에 따라 정리
CREATE TABLE TRADE_T
NOLOGGING
AS
SELECT CEIL(DBMS_RANDOM.VALUE(1, 50000)) AS CUST_ID
, DBMS_RANDOM.STRING('U', 1) AS CUST_GR
, SYSDATE - DBMS_RANDOM.VALUE(1, 365*3) AS TR_DATE
FROM DUAL
CONNECT BY LEVEL < 1000001
;
CREATE INDEX TRADE_T_IDX01 ON TRADE_T(CUST_ID, CUST_GR, TR_DATE) NOLOGGING;
CREATE INDEX TRADE_T_IDX02 ON TRADE_T(CUST_GR, CUST_ID, TR_DATE) NOLOGGING;
DROP TABLE GRADE_T;
CREATE TABLE GRADE_T NOLOGGING
AS
SELECT CHR(64 + ROWNUM) AS CUST_GR FROM DUAL CONNECT BY LEVEL < 27
;
SELECT * FROM TRADE_T WHERE CUST_ID = 2000;
-------------------------------------------------------------------------------------
조 건 절 | 인덱스 구성
-----------------|-------------------------------------------------------------------
고객번호|고객등급| 고객번호 + 고객등급 + 거래일자 | 고객등급 + 고객번호 + 거래일자
--------|--------|---------------------------------|--------------------------------
| | 스캔범위가 넓어 불리하지만 고객 | 유리
| | 번호에 입력범위가 좁을 때는 |
>= | = | 비효율이 크지 않음. 입력범위가 |
| | 넓을 때가 문제이고 ISS 또는 |
| | In-List 기법을 활용하지 못하는 |
| | 단점이 있음 |
--------|--------|---------------------------------|---------------------------------
| | 유리 | 선택도가 높은 고객등급 스캔범위
= | >= | | 가 결정되어 불리하지만, ISS 또는
| | | In-List를 활용한다면 큰 비효율은
| | | 없음
--------|--------|---------------------------------|---------------------------------
| | 고객번호에 입력범위가 좁을 때는 | 선택도가 높은 고객등급 스캔범위
| | 비효율이 크지 않음. 입력범위가 | 가 결정되어 불리하지만, ISS 또는
>= | >= | 넓을 때가 문제이고 ISS 또는 | In-List를 활용한다면 큰 비효율은
| | In-List 기법을 활용하지 못하는 | 없음
| | 단점이 있음 |
--------|--------|---------------------------------|---------------------------------
= | (누락) | 유리 | ISS 또는 In-List 활용가능
--------|--------|---------------------------------|---------------------------------
(누락) | = | Full Table Scan 이 유리 | Full Table Scan 이 유리
-------------------------------------------------------------------------------------
종합해 보면, 선택도가 높은 컬럼(예, 고객등급)을 선두에 두면 나중에 범위검색 조건이 사용되거나
아예 조건절에서 누락되더라도 ISS(Index Skip Scan) 또는 In-List를 활용할 수 있어 유리하다.
▶선택도가 [낮은 컬럼]을 앞쪽에 두는 것이 유리한 경우 - 실무에서 많이 적용됨
범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다
eg)고객 : 100만명, 상품 : 1만개
WHERE 고객번호 = :a
AND 상품번호 = :b
AND 거래일자 BETWEEN :c
AND :d
X01 : 고객번호 + 거래일자 + 상품번호
X02 : 상품번호 + 거래일자 + 고객번호
중간에 놓인 거래일자가 BETWEEN 조건이어서 변별력이 좋은 컬럼에 의해 스캔범위가 최소화
되기에 X01이 유리하다.
▶선택도가 [낮은 컬럼]을 [선택]하는 것이 유리한 경우 - 비 현실적이나 참고할 만함
결론적으로 [결합 인덱스] 컬럼 간 순서를 정할 때는, 개별 컬럼의 선택도보다는 조건절에서
어떤 형태로 자주 사용되는지, 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는 데에
어느 쪽 효용성이 높은지 등이 더 중요한 판단기준이다.
(4)소트 오퍼레이션을 생략하기 위한 컬럼 추가
인덱스는 항상 절력 상태를 유지하므로 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수
있도록 해준다. 따라서 조건절에 사용하지 않은 컬럼이더라도 소트 연산을 대체할 목적으로
인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수 있다.
CREATE TABLE T
NOLOGGING
AS
SELECT ROWNUM A, ROWNUM B, ROWNUM C, ROWNUM D, ROWNUM E
FROM DUAL
CONNECT BY LEVEL <= 100000;
CREATE INDEX T_IDX ON T(A, B, C, D);
SELECT * FROM T WHERE A = 1 ORDER BY A, B, C
--UNION ALL
SELECT * FROM T WHERE A = 1 AND B = 1 ORDER BY C, D
--UNION ALL
SELECT * FROM T WHERE A = 1 AND C = 1 ORDER BY B, D
--UNION ALL
SELECT * FROM T WHERE A = 1 AND B = 1 ORDER BY A, B, C, D;
▶PLAN을 살펴보면 SORT ORDER BY가 없음
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 65 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
단, ORDER BY 절의 순서가 INDEX 순서와 다를 경우는 SORT 발생함
SELECT * FROM T WHERE A = 1 ORDER BY C, B, A;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 65 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 65 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
또한, 아래처럼 ORDER BY 절에 기술한 순서가 인덱스 순서와 일치하기만 한다면 조건절에서
어떤 연산자로 비교되더라도 정렬작업이 생략될 수 있다
--------------------------------------------------------------------
INDEX RANGE SCAN(A) | INDEX FULL SCAN(B)
--------------------------------------------------------------------
SELECT * FROM T | SELECT /*+ INDEX(T, T_IDX) */ * FROM T
WHERE A BETWEEN 1 AND 2 | WHERE B BETWEEN 2 AND 3
AND B NOT IN (1, 2) | ORDER BY A, B, C, D;
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; |
--------------------------------------------------------------------
▶PLAN을 살펴보면 SORT ORDER BY가 없음
위 쿼리들에 대해 정렬작업을 생략할 수 있다는 것이지 옵티마이저가 항상 그런 선택을
한다는 뜻은 아니다. 통계정보를 기반으로 비용을 계산한 결과 옵티마이져가 Table Full Scan을
선택하거나 다른 인덱스를 선택한다면 정렬작업은 별도로 수행된다.
▶CASE A - 3번째 컬럼 기준
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 845 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 13 | 845 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A">=1 AND "A"<=2)
filter("B"<>3)
▶CASE B
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 325 | 430 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5 | 325 | 430 (1)| 00:00:06 |
|* 2 | INDEX FULL SCAN | T_IDX | 456 | | 428 (1)| 00:00:06 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B">=2 AND "B"<=3)
filter("B">=2 AND "B"<=3)
▶아래의 SQL은 인덱스로 소트오퍼레이션 대체 불가한 경우의 예제이다. 즉 ORDER BY에 기술된
컬럼의 순서가 INDEX 순서와 맞질 않거나, 조건절의 인덱스 컬럼이 ORDER BY절에 생략된 경우이다.
SELECT * FROM T WHERE A = 1 ORDER BY C;
SELECT * FROM T WHERE A = 1 AND B BETWEEN 2 AND 3 ORDER BY C, D;
SELECT * FROM T WHERE A = 1 AND B BETWEEN 1 AND 2 ORDER BY A, C, B
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 65 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 65 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
(기타참고)
http://blog.naver.com/bacchusl?Redirect=Log&logNo=50002996263
옵티마이저의 인덱스 선택기준
※ 인덱스 매칭률
Where절에 첫째 컬럼부터 연속된 컬럼에 대해서 '='조건으로 상수가 사용된 인덱스 컬럼의 개수
-----------------------------------------------------------------------------------------
인덱스 컬럼의 총 개수
1. '=' 조건으로 매칭되는 where 조건절의 컬럼의 매칭율(컬럼=상수인 컬럼수/인덱스컬럼의 수)이 높은것
예) 컬럼 3개로 구성된 결합인덱스의 컬럼이 where절에서 2개가 = 로 사용되더라도
컬럼 1개로 구성된 인덱스컬럼이 '=' 로 사용되면 66%(2/3)와 100%(1/1) 비교로 인해
컬럼 1개로 구성된 인덱스가 옵티마이져에의해 선택됨
2. 인덱스 컬럼의 매칭율이 동일할 경우 단일컬럼보다는 여러개의 컬럼으로 구성된 결합인덱스를 더선호하게됨
3. 인덱스 컬럼의 매칭율과 컬럼수도 같다면, 최근에 생성된것을 더 선호함
http://www.dator.co.kr/data24hh/textyle/44413
결합 인덱스 생성시 컬럼 순서
특정 테이블에 인덱스를 생성하는 경우, 특히 여러 개의 컬럼을 결합하여 인덱스를 생성할 때
다음과 같은 방법으로 컬럼을 선정하는 것을 권고합니다.
1. 항상 사용되는 컬럼인가?
▶ 당연히 사용되지 않는 컬럼을 인덱스에 포함할 필요는 없겠죠?
2. 항상 '=' 조건으로 사용되는 컬럼인가?
▶ 인덱스에서 선두 컬럼의 조건이 범위로 들어오는 경우 해당 컬럼 이후의 모든 컬럼들은
필터로 처리되기 때문에 처리 범위를 줄여줄 수 없습니다.(★★★)
따라서 가능하면 '=' 조건으로 사용되는 컬럼을 선두로 하는 것이 유리합니다.
SELECT * FROM T
WHERE A = 2
AND B = 3
UNION ALL
SELECT * FROM T
WHERE A BETWEEN 1 AND 2
AND B = 3;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 260 | 4 (75)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 65 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 3 | 195 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"=2 AND "B"=3)
5 - access("A">=1 AND "B"=3 AND "A"<=2)
filter("B"=3)
3. 액세스(Access) 조건으로 사용될 수 있는 컬럼은 어느 컬럼이며 어떤 컬럼이 처리범위를 줄여줄 수 있는가?
▶ 인덱스에서 액세스 범위를 줄여줌
4. 자주 정렬되는 컬럼의 순서는?
▶ order by에 대한 부담을 제거할 수 있으며, 부분 범위 처리로 유도할 수 있음
5. 부가적으로 추가될 수 있는 컬럼은 무엇인가?
▶ 인덱스에서 필터 조건으로 사용될 수 있거나
▶ select list절에서 사용될 수 있는지(Covered Index)
▶ 인덱스에서 테이블 랜덤 액세스에 대한 횟수를 줄여줄 수 있음
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 20 | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3397 |
| 19 | 6. IOT, 클러스터 테이블 활용 | 휘휘 | 2011.02.26 | 2800 |
| 18 | 6. 히스토그램 | 실천하자 | 2011.04.24 | 11008 |
| 17 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4640 |
| 16 |
6. Sort Area를 적게 사용하도록 SQL 작성
 | 실천하자 | 2011.06.13 | 8928 |
| 15 | 7. 인덱스 스캔 효율 [1] | 휘휘 | 2011.03.08 | 17043 |
| 14 | 7. 비용 | 휘휘 | 2011.05.02 | 6218 |
| 13 | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 9617 |
| 12 | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.13 | 15240 |
| » | 8. 인덱스 설계 | 멋진넘 | 2011.03.06 | 8389 |
| 10 | 7. 조인을 내포한 DML 튜닝 | 실천하자 | 2011.04.03 | 7228 |
| 9 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.29 | 32211 |
| 8 | 8. 공통 표현식 제거 | darkbeom | 2011.06.06 | 5296 |
| 7 |
9. 비트맵 인덱스
| 실천하자 | 2011.03.05 | 12417 |
| 6 |
8. 고급 조인 테크닉-1
[1] | darkbeom | 2011.04.03 | 13367 |
| 5 | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.06 | 7927 |
| 4 |
8. 고급 조인 테크닉-2
| suspace | 2011.04.05 | 7074 |
| 3 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5572 |
| 2 | 11. 집합 연산을 조인으로 변환 | suspace | 2011.06.07 | 6039 |
| 1 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.02 | 6702 |