SQL 기본 및 활용 - 인덱스 기본

1. 인덱스 특징과 종류

인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념이다.

인덱스는 테이블을 기반으로 선택적으로 생성할 수 있는 구조이다.

트리 기반 인덱스

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

B-트리 인덱스는 브랜치 블록과 리프 블록으로 구성된다. 

브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록이라고 한다.

 

브랜치 블록은 분기를 목적으로 하는 블록이다.

브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다.

 

리프 블록은 트리의 가장 아래 단계에 존재한다.

리프 블록은 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자로 구성되어 있다.

인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.

리프 블록은 양방향 링크를 가지고 있어서 오름차순과 내림차순 검색을 쉽게 할 수 있다.

SQL Server의 클러스터형 인덱스

SQL Server의 인덱스 종류는 저장 구조에 따라 클러스터형 인덱스와 비클러스터형 인덱스로 나뉜다.

클러스터형 인덱스는 두 가지 중요한 특징이 있다.

1) 인덱스의 리프 페이지가 곧 데이터 페이지다.

따라서 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다. 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.

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

테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다. 그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

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

전체 테이블 스캔

전체 테이블 스캔 방식으로 데이터를 검색한다는 것은 테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식으로 검색한다

옵티마이저가 연산으로서 전체 테이블 스캔 방식을 선택하는 이유

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

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

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

4) 그 밖의 경우

인덱스 스캔

인덱스 스캔은 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법이다.

인덱스의 리프 블록은 인덱스 구성하는 칼럼과 레코드 식별자로 구성되어 있다.

따라서 검색을 위해 인덱스의 리프 블록을 읽으면 인덱스 구성 칼럼의 값과 테이블의 레코드 식별자를 알 수 있다.

 

인덱스 스캔 중에서 자주 사용되는 인덱스 유일 스캔, 인덱스 범위 스캔, 인덱스 역순 범위 스캔에 대해 간단히 설명하면 다음과 같다. 

제공되는 인덱스 스캔 방식은 데이터베이스 벤더마다 다를 수 있다.

1) 인덱스 유일 스캔은 유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식이다. 유일 인덱스는 중복을 허락하지 않으며 유일 인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이다. 인덱스 유일 스캔은 유일 인덱스 구성 칼럼에 대해 모두 '='로 값이 주어진 경우에만 가능한 인덱스 스캔 방식이다.

2) 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식이다. 유일 인덱스의 구성 칼럼 모두에 대해 '='로 주어지지 않은 경우와 비유일 인덱스를 이용하는 모든 엑세스 방식은 인덱스 범위 스캔 방식으로 데이터를 엑세스하는 것이다. 

3) 인덱스 역순 범위 스캔은 인덱스의 리프 블록의 양방향 링크를 이용하여 내림 차순으롣 데이터를 읽는 방식이다. 이 방식을 이용하여 최대값을 쉽게 찾을 수 있다. 이 또한 인덱스 범위 스캔의 일종이다.

 

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

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

규칙기반 옵티마이저

데이터를 엑세스하는 방법은 크게 두가지로 나눠 볼 수 있다.

인덱스를 경유해서 읽는 인덱스 스캔 방식과 테이블의 전체 데이터를 모두 읽으면서 데이터를 추출하는 전체 테이블 스캔 방식이다.

인덱스 스캔 방식은 사용 가능한 적절한 인덱스가 존재할 때만 이용할 수 있는 스캔 방식이지만 전체 테이블 스캔 방식은 인덱스의 존재 유무와 상관없이 항상 이용 가능한 스캔 방식이다.

글에서 나오는 코드와 내용은 책 SQL 전문가 가이드에서 가져옴을 알립니다.