인덱스 스캔

인덱스 스캔

인덱스를 활용하여 데이터를 읽을 때에는 여러 방식이 있다.

어떤 상황에 어떤 스캔을 하고, 효율적으로 인덱스를 사용하기 위해서는 이를 알아둘 필요가 있다.

기본적으로 수직 탐색 후 수평 탐색, 그리고 테이블 I/O의 과정을 거쳐 데이터 레코드를 가져오게 된다.

Index Range Scan

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

Index Range Scan을 사용하려면

• 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 함
• 반대로, 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하면 Index Range Scan은 무조건 가능함

하지만, Range Scan이 중요한 것이 아니라, 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐로 결정된다.

Index Full Scan

수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.

• 대개 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됨
• 인덱스 선두 컬럼을 조건절에 사용하지 않으면 기본적으로 Table Full Scan을 선택함
• Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면 Index Full Scan을 사용하기도 함

idx (ename, sal)
----
SELECT * FROM emp
WHERE sal > 2000
ORDER BY ename;

Index Full Scan의 효용성

1. 데이터 저장 공간 : 컬림 길이 * 레코드 수에 의해 결정되므로 인덱스가 차지하는 면적은 테이블보다 훨씬 적다.

   1. 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 액세스하는 상황이라면, 면적이 큰 테이블보다 인덱스를 스캔하는 쪽이 유리하다.

      

2. 인덱스를 이용한 소트 연산 생략
   • 부분 범위 처리가 가능한 상황에서 극적인 성능 개선 효과
   • 부분 범위 처리

     전체 범위 처리:
     - 모든 데이터를 읽고 정렬
     - 그 다음 LIMIT 적용
     - 100만 건 정렬 후 100건 추출 ❌
     
     부분 범위 처리:
     - 정렬된 상태로 읽으면서
     - 필요한 만큼만 읽고 즉시 종료
     - 100건만 읽고 종료 ✅

     결국, 정렬과 LIMIT !
     
   • 커버링 인덱스 사용 가능

인덱스 풀 스캔 vs 테이블 풀 스캔

• 인덱스 풀 스캔 vs 테이블 풀 스캔 : 무조건 테이블 풀 스캔이 낫지 않나?
  1. 커버링 인덱스 (가장 일반적)
  2. 정렬이 필요한 경우 (LIMIT 100)
  3. 인덱스 크기가 테이블보다 훨씬 작은 경우
  4. 선택적 컬럼 조회 + 대부분의 행이 필요한 경우
  5. MIN/MAX 같은 집계 함수

Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식으로, Unique 인덱스를 ‘=’ 조건으로 탐색하는 경우에 작동한다.

• Unique 인덱스라고 해도 범위 검색 조건(between, 부등호, like)으로 검색할 때는 Index Range Scan으로 처리됨
  • 복합 Unique 인덱스(c1, c2, c3)에서 c1, c2로만 검색하는 경우가 해당됨

Index Skip Scan

루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 ‘가능성이 있는’ 리프 블록만 골라서 액세스하는 스캔 방식이다.

• 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 스캔 방식
• Distinct Value 개수가 적은 선두 컬럼이 조건절에 없고, 후행 컬럼의 Distinct Value가 많을 때 효과적임
• 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용함
  • idx (성별, 연봉)
  • select * from emp where sal between 2000 and 4000;

Index Skip Scan 작동 조건

1. 선두 컬럼이 조건절에 없고, 후행 컬럼이 조건절에 있을 때

2. 선두 컬럼에 대한 조건절은 있고, 중간 컬럼에 대한 조건절이 없는 경우

   idx (c1, c2, c3)
   ----
   SELECT * FROM ?
   WHERE c1 = 1
   AND c3 BETWEEN '19990101' AND '19990131'

   c3 기준 선행 컬럼 c2가 없다고 생각해도 좋을 듯!

   • 위 경우 Range Scan 한다면.. c1 = 1인 것 모두 읽어야 함
   • Skip Scan 한다면 BETWEEN '19990101' AND '19990131' 조건을 ‘포함할 가능성이 있는’ 리프 ‘블록’만 골라서 액세스할 수 있음

   idx (c1, c2, c3)
   ----
   SELECT * FROM ?
   WHERE c3 BETWEEN '19990101' AND '19990131'

   또한, c1, c2 모두 조건절에 없는 경우에도 유용하게 사용할 수 있음

3. 선두 컬럼이 부등호, BETWEEN, LIKE같은 범위 검색 조건인 경우

   idx (날짜, 유형 코드)
   ----
   SELECT * FROM ?
   WHERE 날짜 BETWEEN '19990101' AND '19990131'
   AND 유형 코드 = '01'

   • 마찬가지로 Range Scan을 한다면 BETWEEN 조건 만족하는 구간을 모두 스캔해야 함
   • Skip Scan 한다면 유형 코드 조건을 포함할 가능성이 있는 리프 ‘블록’만 골라서 액세스 할 수 있음

Index Range Scan이 불가능하거나 효율적이지 못한 상황에서 Index Skip Scan이 종종 빛을 발한다. 부분 범위 처리가 가능하다면 Index Full Scan이 도움 되기도 한다.

Index Skip Scan은 사용하는 것만이 아니라 선두 컬럼과 후행 컬럼의 Distinct Value 개수가 중요하다는 점 잊지 말 것 !

기본적으로 최적의 Index Range Scan을 목표로 하되, 이 방법이 오히려 비효율적일 때 이러한 스캔 방식을 차선책으로 활용하는 것이 바람직하다.

Index Fast Full Scan

논리적인 인덱스 트리 구조를 무시하고, 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔하는 방식이다.

1. 논리적인 인덱스 트리 구조?
   1. 루트 - 정렬된 중간 브랜치 블록들 - 정렬된 리프 블록들
   2. 하지만, 실제 디스크에는 섞여있음
      1. 섞여있는데 어떻게 찾아가냐? ‘논리적인 인덱스 트리 구조’ 각 블록은 ‘하위 블록의 주소’를 가지고 있기 때문에 가능함
2. ‘인덱스 세그먼트’ 전체를 ‘Multiblock I/O’?
   1. 인덱스 세그먼트에는 루트 - 중간 브랜치 블록들 - 리프 블록들 의 순서는 같지만, 이들이 정렬되어 있지 않음

특징

• 순서를 생각하지 않고 전체를 한 번에 가져오는 Multiblock I/O를 사용하므로 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘함
• 속도는 빠르지만, 결과 집합의 정렬은 되지 않음
• 쿼리에 사용한 컬럼이 모두 인덱스에 포함되어 있을 때만 사용할 수 있음
• Index range Scan, Index Full Scan과 달리 인덱스가 파티션 되어 있지 않더라도 병렬 쿼리가 가능함
  • 병렬 쿼리 시에는 Direct I/O를 사용하므로 I/O 속도가 더 빨라짐

Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 스캔 방식이다. 내림차순으로 정렬된 결과 집합을 얻는다는 점만 다르다.

• ORDER BY 내림차순이 있을 때
• MAX 값을 구할 때

등의 상황에서 사용되곤 한다.