[SQLD : Ⅴ. SQL 최적화 기본 원리] 2. 인덱스 기본

* 인덱스 특징과 종류

 - 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념

 -  테이블을 기반으로 선택적으로 생성할 수 있는 구조

 - 검색 조건을 만족하는 데이터를 인덱스를 통해 효과적으로 찾을 수 있도록 도움

 

 - 트리 기반 인덱스 : DBMS에서 가장 일반적인 인덱스는 B-트리 인덱스

  B-트리 인덱스는 브랜치 블록(Branch Block)과 리프 블록(Leaf Block)으로 구성

  브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록(Root Block)이라고 함

  브랜치 블록은 분기를 목적으로 하는 블록, 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가짐

  리프 블록은 트리의 가장 아래 단계에 존재,  리프 블록은 양방향 링크(Double Link)를 가짐 

  양방향 링크로 오름차순, 내림차순 검색을 쉽게할 수 있음

  B-트리 인덱스는 일치검색(=), 범위검색(BETWEEN, >, 등) 모두에 적합한 구조

    1단계. 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동

    2단계. 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동

    3단계. 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동

Oracle에서 트리 기반 인덱스에는 B-트리 인덱스 외에도 비트맵 인덱스, 리버스 키 인덱스, 함수기반 인덱스등이 존재

 

 - SQL server의 클러스터형 인덱스

  첫째, 인덱스의 리프 페이지가 곧 데이터 페이지다. 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다

         (인덱스 키 칼럼과 나머지 칼럼을 리프 페이지에 같이 저장하기 때문에 테이블을 랜덤 액세스할 필요가 없다).

         클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다. 

  둘째, 리프 페이지의 모든 로우(=데이터)는 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장된다.

         테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다.

         클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

         (전화번호부 한 권을 상호와 인명으로 동시에 정렬할 수 없는 것과 마찬가지다.)

 

* 전체 테이블 스캔과 인덱스 스캔

 

 - 전체 테이블 스캔

 1) SQL문에 조건이 존재하지 않는 경우

    SQL문에 조건이 존재하지 않는다는 것은 테이블에 존재하는 모든 데이터가 답이 된다는 것이다.

    그렇기 때문에 테이블의 모든 블록을 읽으면서 무조건 결과로서 반환하면 된다.

 2) SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하는 않는 경우

    사용 가능 인덱스가 존재하지 않으면 데이터 액세스할 수 있는 방법은 모든 데이터를 읽으면서 조건 만족을 검사

    사용 가능한 인덱스는 존재하지만 함수를 사용하여 인덱스 칼럼을 변형한 경우에도 인덱스를 사용할 수 없다

 3) 옵티마이저의 취사 선택

    조건을 만족하는 데이터가 많은 경우,

    결과를 추출하기 위해 대부분의 블록을 액세스해야 한다고 옵티마이저가 판단하면

    조건에 사용 가능한 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.

 4) 그 밖의 경우

    병렬처리 방식으로 처리하는 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우에

    전체 테이블 스캔 방식으로 데이터를 읽을 수 있다.

 

 - 인덱스 스캔

  1) 인덱스 유일 스캔은 유일 인덱스(Unique Index)를 사용하여 단 하나의 데이터를 추출하는 방식이다.

     유일 인덱스는 중복을 허락하지 않는 인덱스이다.

     유일 인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 된다.

     인덱스 유일 스캔은 유일 인덱스 구성 칼럼에 대해 모두 ‘=’로 값이 주어진 경우에만 가능한 인덱스 스캔 방식이다.

  2) 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식이다.

     유일 인덱스의 구성 칼럼 모두에 대해 ‘=’로 값이 주어지지 않은 경우와

     비유일 인덱스를 이용하는 모든 액세스 방식은 인덱스 범위 스캔 방식으로 데이터를 액세스하는 것이다.

 

 - 전체 테이블 스캔과 인덱스 스캔 방식의 비교

  3) 인덱스 역순 범위 스캔은 ([그림 Ⅱ-3-10] 오른쪽) 인덱스의 리프 블록의 양방향 링크로 내림 차순으로 읽는 방식

     이 방식을 이용하여 최대값을 쉽게 찾을 수 있음. 인덱스 범위 스캔의 일종

     이 외에도 인덱스 전체 스캔, 인덱스 고속 전체 스캔, 인덱스 스킵 스캔 등이 존재