친절한 SQL 튜닝 - 2장 인덱스 확장 기능 사용법

2.3 인덱스 확장 기능 사용법

• 인덱스 스캔 방식은 Index Range Scan 뿐만 아니라 Index Full Scan, Index Unique Scan, Index Skip Scan, Index Fast Full Scan 등이 존재한다. 각 스캔 방식의 주요 특징에 대해서 알아보자.

2.3.1 Index Range Scan

• Index Range Scan은 B+Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식이다.
• 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 필요한 범위만 스캔한다.

SELECT * FROM EMP WHERE DEPNO = 20;

• 앞서 강조한 그대로 인덱스를 Range Scan 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다. (선두 컬럼을 가공하지 않은 상태로 사용하면 무조건 Index Range Scan 가능)
• 가공하지 않으면 Index Range Scan을 타기 때문에 성능도 좋다라고 당연하게 생각하면 안된다.
• 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄이는지가 관건이기 때문이다.

2.3.2 Index Full Scan

• Index Full Scan 은 위의 그림처럼 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
• 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

CREATE INDEX EMP_ENAME_SAL_IDX ON EMP (ENAME, SAL);

SELECT * FROM EMP
WHERE SAL > 2000
ORDER BY ENAME;

• 위의 예제 SQL에서는 인덱스 선두 컬럼인 ENAME이 존재하지 않으므로 Index Range Scan은 불가능하고, 뒤쪽에 SAL이 존재하므로 Index Full Scan을 통해 Sal이 2000보다 큰 레코드를 찾을 수 있다.

2.3.3 Index Unique Scan

• Index Unique Scan은 그림처럼 수직적 탐색만으로 데이터를 찾는 스캔 방식이며 Unique 인덱스를 = 조건으로 탐색하는 경우에 작동한다.

CREATE UNIQUE INDEX PK_EMP ON EMP(EMPNO);

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = 7788;

• 해당 인덱스 키 컬럼을 모두 '=' 조건으로 검색할 때는 데이터를 한 건 찾는 순간 더 이상 탐색할 필요가 없다.
• Unique 인덱스라고 해도 범위검색 조건(between, like)으로 검색할 떄는 Index Range Scan으로 처리된다.
• 예를 들어, empno >= 7788 조건은 수직적 탐색만으로는 조건에 해당하는 레코드를 찾을 수 없고, PK 인덱스를 [주문일자 + 고객ID + 상품ID] 로 구성했는데 주문일자과 고객ID로만 검색하는 경우도 Index Range Scan에 해당한다.

2.3.4 Index Skip Scan

• 인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다. 또는 Table Full Scan 보다 I/O를 줄일 수 있으면 Index Full Scan을 사용한다.
• 오라클은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식을 9i 버전부터 지원하는데 Index Skip Scan이 바로 그것이다.
• 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 떄 유용하다.
  • ex) 고객테이블에서 성별이 Distinct Value가 적은 것, 고객번호가 Distinct Value 개수가 많은 것

• 위의 그림은 Index Skip Scan 을 설명하기 위한 인덱스 루트 블록과 리프 블록을 그린 그림이다.
  ```sql
  • 성별과 연동 두 컬럼으로 구성된 결합 인덱스
    SELECT * FROM 사원 WHERE 성별 = '남' AND 연봉 BETWEEN 2000 AND 4000
• 성별 = '남' 이면서 연동 >= 2000 인 첫 번째 레코드를 먼저 찾는다.
• 루트 블록 네 번째 레코드가 가리키는 4번 리프 블록으로 찾아가면 성별 = '남' 이면서 연봉 >= 5000 인 레코드를 만나게 되므로 바로 직전에 위치한 세 번째 레코드가 가리키는 3번 리프 블록으로 찾아가야 한다.
• 3번 리프 블록에서 성별 = '남' 이면서 연봉 >= 2000인 첫 번째 레코드를 만났다면, 거기서부터 리프 블록을 차례로 스캔하다가 성별 = '남' 이면서 연봉 > 4000인 첫 번쨰 레코드를 만나는 순간 스캔을 멈추게 된다.
• Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스하는 스캔 방식이다.
• 위의 그림을 Index Skip Scan 과정에 따라가보자.
  1. 첫 번쨰 남 & 800 이하는 액세스하지 않아도 될 것 같다('남'보다 작은 성별값이 존재한다면 액세스한다)
  2. 두 번째 남 & 800 이상, 남 & 1500 이하 레코드는 2000 <= 연봉 <= 4000 에 포함하지 않으므로 Skip
  3. 세 번째 남 & 1500 이상, 남 & 5000 이하 레코드 스캔
  4. 네 번째 남 & 5000 이상, 남 & 8000 이하 레코드 Skip
     ...
  5. 여섯 번째 남 & 10000 이상은 조건절에 부합하지 않지만 여자 중에서 연봉 < 3000 이거나 남과 여 사이에 다른 성별이 혹시 존재한다면 리프 블록에 저장된다.

특이 케이스인 여섯 번째 같은 Index Skip Scan에 주의하자

• Index Skip Scan 을 요약하자면 위의 그림과 같다.

SELECT 
    기준일자, 업종코드, 체결건수, 체결수량, 거래대금
FROM 
    일별업종별거래 A
WHERE 
    기준일자 BETWEEN '20080501' AND '20080531'
    AND 업종유형코드 = '01'

• 만약 위 SQL에 Index Range Scan을 사용한다면, 기준일자 BETWEEN 조건을 만족하는 인덱스 구간을 모두 스캔해야 한다.
• 하지만 Index Skip Scan을 사용하면, 기준일자 BETWEEN 조건을 만족하는 인덱스 구간에서 업종유형코드 = '01'인 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스할 수 있다.

인덱스는 기본적으로 최적의 Index Range Scan을 목표로 설계해야하고 다른 스캔 방식은 차선책으로 활용하는 방식이 바람직하다.

2.3.5 Index Fast Full Scan

• 일반적인 Index Full Scan은 인덱스 논리적 구조를 따라 루트 -> 브랜치 1 -> 2 ~ 9 -> 109 순으로 블록을 읽어드린다.
• 하지만 Index Fast Full Scan은 Muitiblock I/O 방식으로 디스크로부터 대량의 인덱스 블록을 읽을 때 큰 효과를 발휘한다.
• 속도는 빠르지만, 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽기 때문에 결과 집합이 인덱스 키 순서대로 정렬되지 않는다.
• 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을 때만 사용할 수 있다는 점도 기억할 필요가 있다.

Index Full Scan과 Index Fast Full Scan 차이점

2.3.6 Index Range Scan Descending

• Index Range Scan과 기본적으로 동일 스캔방식이나, 인덱스를 뒤에서 앞으로 스캔하기 때문에 내림차순으로 결과집합을 얻는다.