[SQLD] 29. 인덱스 기본

제2절 인덱스 기본

1. 인덱스 특징과 종류

• 인덱스의 기본적인목적 - 검색 성능의 최적화, DML작업은 테이블과 인덱스를 함게 변경해야 하기 때문에 성능이 느려질수 있다

가. 트리기반 인덱스

• B-트리 인덱스
  

  

• 리프블록 - 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID,Record Identifier/Rowid)로 구성
• 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.(인덱스데잍터값이 동일하면 레코드 식별자 순으로 저장)
• 양방향 링크, 오름차순과 내림차순검색을 쉽게 할 수 있다.
• B-트리인덱스는 '='로 검색하는 일치 검색과 범위 검색 모두에 적합한 구조
  

  

• 브랜치 블록이 3개의 포인터로 구성된 B-트리 인덱스의 예
• 인덱스에서 원하는 값을 찾는 과정

  1단계	브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동
  2단계	찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동
  3단계	오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동

• 이 과정을 리프 블록을 찾을 때 까지 반복, 리프 블록에서 찾고자 하는 값이 존재하면 해당 값을 찾은 것, 해당 값이 없으면 해당 값은 존재하지 않아 검색실패
  ex)37을 찾고자 한다면 루트블록에서 50보다 작으므로 왼쪽 포인터로 이동
  37은 왼쪽브랜치 블록의 11과 40사이의 값이므로 가운데 포인터로 이동
  37과 50사이의 값을 찾고자한다면 37을찾고 50보다 큰값을 만날때까지 오른쪽으로 이동하면서 인덱스를 읽음

• 트리기반 인덱스 : B-tree index, bitmap index, reverse key index, FBI

나. SQL Server의 클러스터형 인덱스

• SQL Server의 인덱스 종류는 저장구조에따라 클러스터형인덱스와 비클러스터형 인덱스로 나뉜다.
  중요한 특징
  1. 첫째 : 인덱스 리프 페이지가 곧 데이터 페이지다. 데이블 탐색에 필요한 레코드 식별자가 리프페이지에 없다.
  클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다. 클러스터형 인덱스를 사전에 비유한다.(색인과는 다름)
  2. 둘째 : 리프페이지의 모든 로우(데이터)는 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장된다.
  테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다. 그러므로 클러스터형 인덱스는 테이블 당 한 개만 생성할 수 있다.
  전화번호부 한 권을 상호와 인명으로 동시에 정렬할 수 없는 것과 마찬가지
  

  

2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

• Full table scan일 경우 검색조건에 맞는 데이터를 찾기위해 HWM 아래 모든 블록을 읽는다.

• 옵타마이저가 Full table scan을 선택하는 이유
  1) SQL문에 조건이 존재하지 않은경우
  2) 사용 가능한 인덱스가 존재하지 않는경우
  3) 옵티마이저의 취사 선택 - 조건을 만족하는 데이터가 많을경우
  4) 그밖의 경우 - 병렬처리 방식으로 처리 or 힌트사용

나. 인덱스 스캔(트리기반중심)

• 인덱스 스캔은 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 방식
• 인덱스 리프 블록은 인덱스를 구성하는 칼럼과 레코드 식별자로 구성
• SQL문에 필요로한느 모든 칼럼이 인덱스 구성 칼럼에 포함된 경우 테이블 엑세스는 발생하지 않는다.
• 인덱스는 인덱스 구성 칼럼의 순서로 정렬되어 있다.

# Index unique scan

• 중복을 허락하지 않는다. 구성칼럼은 모두 '='로 구성, 최대 1건

# Index range scan

• 인덱스를 이용하여 한건이상 데이터를 추출하는 방식

# Index range scan descending

• 인덱스 리프블록의 양방향 링크를 이용하여 내림차순으로 데이터를 읽는 방식
  

  

• 이외에도 Index full scan, Fast full index scan, index skip scan 등이 있다.

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

• 대용량 데이터 중 극히 일부의 데이터를 찾을 때는 인덱스 스캔방식을 이용하여 몇번의 I/O만으로 데이터를 찾고
• 전체 테이블 스캔은 모든 데이터를 읽으면서 원하는 데이터를 찾아야하므로 한번에 여러블록씩 읽는 전체 테이블 스캔 방식이 유리하다.

 

 

 

 

[출처]

wiki.gurubee.net/pages/viewpage.action?pageId=27428159