인덱스 스캔과 테이블 스캔 간의 I/O 운영 비용은 데이터 액세스 방법, 읽기 페이지 수 및 쿼리 선택성 및 데이터 구성을 기반으로 한 효율성 측면에서 근본적으로 다릅니다.
인덱스 스캔에는 인덱스 구조 (종종 B- 트리)를 통과하여 데이터에 액세스하는 것이 포함됩니다. 여기서 비용에는 주로 인덱스 페이지에 액세스하는 비용과 해당 테이블 페이지를 가져 오는 비용의 두 가지 구성 요소가 포함됩니다. 인덱스 잎 노드가 디스크에서 물리적으로 인접하지 않아 임의의 I/O 작동을 초래하기 때문에 인덱스 페이지에는 일반적으로 임의의 액세스 패턴이 있습니다. 루트에서 잎으로 인덱스 트리를 내려가는 각 논리적 단계는 읽기 페이지를 필요로 하며이 비용은 시스템의 랜덤 페이지 비용 매개 변수를 사용하여 추정됩니다. 관련 행의 인덱스 항목을 찾은 후 시스템은 해당 테이블 데이터 페이지를 가져옵니다. 이러한 데이터 페이지의 수는 쿼리의 선택성과 저장된 데이터와 인덱스 순서 (상관 관계) 간의 물리적 상관 관계에 따라 다릅니다. 높은 상관 관계는 인덱스 항목 행 위치가 물리적 데이터 순서에 밀접하게 일치하여 임의의 I/O를 줄이고 인덱스 스캔을보다 효율적으로 만듭니다. 반면에 상관 관계가 낮 으면 많은 임의의 페치가 발생하여 I/O 비용이 크게 증가합니다.
인덱스 스캔의 CPU 비용에는 검색된 각 개별 행 처리가 포함되지만 주요 비용 문제는 I/O입니다. 가시성 측면도 있습니다. 데이터베이스에 모든 트랜잭션에 완전히 가시적되는 데이터 페이지 추적이있는 경우 시스템은 인덱스 전용 스캔 중에 특정 페이지를 읽는 경우 때로는 I/O 비용을 크게 줄일 수 있습니다.
반대로, 테이블 스캔 또는 전체 테이블 스캔은 테이블에서 순차적으로 모든 데이터 페이지를 읽음으로써 작동합니다. 여기의 I/O는 대부분 순차적이며, 이는 회전 디스크에서 임의의 I/O보다 빠르거나 SSD에 대한 처벌이 적은 경향이 있습니다. 전체 테이블 스캔 비용은 쿼리를 만족시키는 행의 수에 관계없이 전체 테이블을 읽기 때문에 비교적 일정합니다. 이 비용은 선택한 행 수보다는 테이블의 총 페이지 수에 따라 다릅니다. 전체 테이블 스캔은 선택성의 이점이 없습니다. 쿼리가 행을 크게 필터링하더라도 모든 페이지를 읽어야합니다. 그러나 멀티 블록 읽기, 병렬 읽기 및 스토리지 레이어 오프로드와 같은 최신 스토리지 기술 및 최적화를 통해 전체 테이블 스캔 비용은 테이블의 상당 부분을 검색하는 쿼리에 대한 인덱스 스캔보다 경쟁적이거나 훨씬 적을 수 있습니다.
CBO (Cost-Based Optimizer)는 인덱스 스캔 사용과 예상 비용에 따라 전체 테이블 스캔을 결정합니다. 적은 부분의 행만 검색 해야하는 고도로 선택적 쿼리의 경우 색인 스캔은 테이블 페이지가 적기 때문에 I/O 비용이 낮은 경향이 있습니다. 선택성 임계 값이 증가함에 따라 (필요한 줄이 많음), 더 많은 수의 임의의 페이지 페치 및 인덱스 트래버스로 인해 인덱스 스캔 비용이 상승하여 결국 전체 테이블 스캔 비용을 능가합니다. 이 시점에서 CBO는 순차적 I/O 비용이 많은 인덱스 조회의 임의의 I/O 부담보다 적기 때문에 테이블 스캔을 선호합니다.
I/O 비용 차이에 영향을 미치는 또 다른 중요한 요소는 "클러스터링 계수"또는 인덱스 키에 해당하는 행의 물리적 클러스터링입니다. 더 낮은 클러스터링 계수 (더 나은 클러스터링)는 인덱스를 통해 액세스하는 행이 물리적으로 서로 가까이 위치하여 임의의 I/O를 줄이고 인덱스 스캔 효율을 향상시킵니다. 클러스터링 계수가 높을수록 인덱스 스캔 중에 더 많은 임의의 I/O를 초래하고 테이블 스캔에 비해 이점을 줄입니다.
최신 데이터베이스 시스템은 I/O 및 CPU 리소스가 여러 작업자간에 공유되는 인덱스 및 전체 테이블 스캔 모두 병렬 스캔을 구현할 수 있으며, 이는 총 쿼리 시간을 줄일 수 있습니다. 그러나 인덱스 스캔 (임의의 페이지 액세스) 대 테이블 스캔 (대부분 순차적 페이지 액세스)에 대한 I/O의 기본 특성은 주요 차별화 요소로 남아 있습니다.
I/O 비용 차이에 대한 핵심 사항을 요약하려면 :
- 인덱스 스캔은 인덱스 페이지 (임의의 액세스)와 해당 데이터 페이지를 가져 오는 데있어 I/O 비용을 발생시킵니다 (잠재적으로 무작위 액세스). 비용은 선택성, 상관 관계 및 클러스터링 계수에 민감합니다.
- 전체 테이블 스캔은 선택성에 관계없이 비교적 안정적인 I/O 비용으로 모든 페이지를 읽는 순차 I/O를 수행합니다.
- 쿼리가 데이터의 작은 서브 세트를 대상으로하면 인덱스 스캔이 성능이 뛰어날 수 있지만 선택성이 상승함에 따라 임의의 I/O 오버 헤드로 어려움을 겪을 수 있습니다.
- 효율적인 순차 I/O로 인해 테이블의 많은 부분이 액세스 할 필요가 있으면 전체 테이블 스캔이 더 효율적일 수 있습니다.
- 가시성 맵 및 인덱스 전용 스캔은 데이터 페이지 읽기를 피함으로써 일부 인덱스 스캔 케이스의 I/O 비용을 줄일 수 있습니다.
- 병렬 처리는 두 스캔 유형을 모두 개선 할 수 있지만 기본 I/O 특성을 변경하지는 않습니다.
- Optimizer는 이러한 비용 요소의 균형을 유지하여 쿼리 및 데이터 특성을 기반으로 가장 효율적인 방법을 선택합니다.