[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 최적화 기본 원리 #2

<목차>

• 인덱스 특징과 종류

  • 트리 기반 인덱스

  • SQL Server의 클러스터형 인덱스

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

  • 전체 테이블 스캔

  • 인덱스 스캔

  • 비교

<< 인덱스 기본 >>

 

1. 인덱스 특징과 종류

* 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 개념이다.

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

* 기본적인 목적은 검색 성능의 최적화.

* INSERT, UPDATE, DELETE와 같은 DML 작업은 테이블과 인덱스 또한 함께 변경하므로 오히려 느려질 수 있기에 주의해서 사용한다.

 

1-1. 트리 기반 인덱스

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

B-트리 인덱스 구조

* Root 블록, Branch 블록, Leaf 블록으로 구성되어 있다.

• Root 블록

  * 가장 상위에 존재하는 블록.

• Branch 블록

  * 분기를 목적으로 하는 블록.

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

• Leaf 블록

  * 트리의 가장 아래 단계에 존재한다.

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

  * 양방향 링크를 가지고 있어, 오름 차순 및 내림 차순 검색이 쉽다.

[B-트리 인덱스의 특징]

* '='로 검색하는 일치 검색과 BETWEEN, '>' 등과 같은 연산자로 검색하는 범위 검색 모두에 적합한 구조이다.

* 10% 이하의 데이터를 검색할 때 유리하다.

 

* Branch 블록이 3개의 포인터로 구성된 B-트리 인덱스를 살펴보자.

B-트리 인덱스 검색

* 예시 : 인덱스에서 원하는 값을 찾는 과정을 살펴본다.

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

   찾고자 하는 값이 Branch 블록의 값 사이에 존재하면 가운데 포인터로 이동,

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

   * Leaf 블록을 찾을 때까지 반복한다.

2. 37을 찾고자 할 때,

   Root 블록에서 왼쪽으로 이동, Branch 블록의 값 사이에 존재하므로 가운데 포인터로 이동, Leaf 블록 중 37이 존재하는지 검색한다.

3. BETWEEN 37 AND 50 을 찾고자 할 때,

   2의 Leaf 블록에서 50보다 큰 값을 만날 때까지 오른쪽으로 이동한다. (Leaf 블록이 양방향 링크로 연결되어 있기 때문에 가능함.)

* 인덱스 생성 시, 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없다.

* 하지만, 인덱스 구성 칼럼의 순서가 다르면 서로 다른 인덱스로 생성할 수 있다.

 

1-2. SQL Server의 클러스터형 인덱스

* 저장 구조에 따라 클러스터형 인덱스, 비클러스터형 인덱스로 분류한다. 여기서는 클러스터형 인덱스에 대해서만 알아볼 예정.

 

* 특징

1. 인덱스의 Leaf 페이지가 곧 데이터 페이지. 즉, 테이블 탐색에 필요한 레코드 식별자가 Leaf 페이지에 없다.
   (인덱스 키 칼럼과 나머지 칼럼을 Leaf 페이지에 같이 저장하므로 테이블을 랜덤 액세스할 필요가 없다.)
   * Leaf 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.

2. Leaf 페이지의 모든 Row는 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장된다.
   (그러므로 클러스터형 인덱스는 테이블당 한 개만 생성가능 ; 두 가지 이상의 조건으로 동시에 테이블을 정렬할 수 없기 때문에)

Employees_pk 클러스터형 인덱스

 

 

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

2-1. 전체 테이블 스캔

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

전체 테이블 스캔

* Oracle의 경우 위 그림과 같이 검색 조건에 맞는 데이터를 찾기 위해 테이블의 고수위 마크(HWM ; High Water Mark) 아래의 모든 블록을 읽는다.

---> 고수위 마크(HWM)는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치를 의미.

 

* 옵티마이저가 전체 테이블 스캔을 하는 이유

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

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

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

  * 가용 인덱스가 존재하지 않는다면 데이터를 액세스할 수 있는 방법은 테이블의 모든 데이터를 읽으면서 주어진 조건을 만족하는지를 검사하는 방법 뿐이다.

• 옵티마이저의 취사 선택

  * 조건을 만족하는 데이터가 많을 때, 결과를 추출하기 위해서 테이블의 대부분의 블록을 액세스해야 한다고 옵티마이저가 판단하면 가용 인덱스가 존재해도 전체 테이블 스캔 방식을 사용한다.

• 그 밖의 경우

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

 

2-2. 인덱스 스캔

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

---> 트리 기반 인덱스

 

* 인덱스의 Leaf 블록은 인덱스를 구성하는 칼럼과 레코드 식별자(RID)로 구성되어 있다.

---> 인덱스의 Leaf 블록을 읽으면 인덱스 구성 칼럼의 값과 레코드 식별자를 알 수 있다.

 

* 인덱스에 존재하지 않는 칼럼의 값이 필요한 경우에는 테이블 액세스를 해야 한다.

---> SQL문에서 필요로 하는 모든 칼럼이 인덱스 구성 칼럼에 포함된 경우에는 테이블 액세스는 발생하지 않는다.

 

* 인덱스는 구성 칼럼의 순서로 정렬되어 있다.

ex) 구성 칼럼이 A+B 라면 A 칼럼으로 정렬되고, 동일한 경우 B 칼럼으로 정렬한다. B까지 동일한 경우 레코드 식별자로 정렬된다.

 

* 인덱스 유인 스캔 (Index Unique Scan), 인덱스 범위 스캔 (Index Range Scan), 인덱스 역순 스캔 (Index Range Scan Descending), 인덱스 전체 스캔 (Index Full Scan), 인덱스 고속 전체 스캔 (Fast Full Index Scan), 인덱스 스킵 스캔 (Index Skip Scan) 등이 존재.

 

A. 인덱스 유인 스캔 (Index Unique Scan)

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

* 구성 칼럼에 대해 모두 '=' 연산자로 값이 주어진 경우에만 가능한 인덱스 스캔 방식.

 

B. 인덱스 범위 스캔 (Index Range Scan)

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

 

C. 인덱스 역순 스캔 (Index Range Scan Descending)

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

* 인덱스 범위 스캔의 한 부분이다.

인덱스 오름/내림차순 범위 스캔

 

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

전체 테이블 스캔 / 인덱스 스캔 SQL 처리 흐름도 표현 예시

 

A. 인덱스 스캔 방식

• 가용 인덱스가 존재할 때만 사용이 가능하다.

• 인덱스에 존재하는 레코드 식별자를 이용해 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽는다.
  ---> 불필요하게 다른 블록을 더 읽을 필요가 없다. 즉, 한 번의 I/O 요청에 한 블록씩 데이터를 읽는다.

• 전체 데이터 중 극히 일부의 데이터를 찾을 때는 몇 번의 I/O만으로 원하는 데이터를 쉽게 찾을 수 있는 인덱스 스캔 방식이 유리하다.

 

B. 전체 테이블 스캔 방식

• 가용 인덱스의 유무에 관계 없이 항상 사용이 가능하다.

• 한 번의 I/O 요청에 여러 블록을 한꺼번에 읽는다.

• 테이블의 대부분의 데이터를 찾을 때는 한 블록 씩 읽는 인덱스 스캔 방식 보다는 한 번에 여러 블록씩 읽는 전체 테이블 스캔 방식이 유리하다.

 

 

 

 

 

출처

www.dbguide.net/db.db?cmd=view&boardUid=148209&boardConfigUid=9&categoryUid=216&boardIdx=136&boardStep=1

데이터 전문가 지식포털 DBGuide.net