인덱스 기본

 

1.인덱스 특징과 종류

원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념이다. 테이블을 기반으로 선택적으로 생성할 수 있는 구조이다.

목적 : 검색 성능의 최적화
그러나 DML작업은 테이블과 인덱스를 함께 변경해야 하기 때문에 오히려 느려질 수 있다는 단점이 존재한다.

 

  • 트리 기반 인덱스

오라클에서 트리 기반 인덱스는 B-Tree, Bitmap Index, Reverse Key Index, FBI(Function-Based Index)등이 존재한다.

SQL_244

  • 루트 블록
    브랜치 블록 중 가장 상위에 있는 블록.

  • 브랜치 블록
    분기를 목적으로 하는 블록. 다음 단계의 블록을 가리키는 포인터를 가지고 있다.

  • 리프 블록
    인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID, Record Indentifier/Rowid)로 구성. 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다. 데이터의 값이 동일하면 레코드 식별자 순서로 저장된다. 양방향 링크를 가지고있어 오름차순, 내림차순 검색을 쉽게 할 수 있다.

B-Tree인덱스는 ‘='로 검색하는 일치 검색과 Between, >등과 같은 연산자로 검색하는 범위(range)검색 모두에 적합한 구조이다.

 

  • 인덱스에서 원하는 값을 찾는 과정
  1. 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동.
  2. 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동.
  3. 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동.
  4. 리프 블록을 찾을때까지 1~3 반복.

SQL_245

인덱스를 생성할 때 동일 칼럼으로 구성된 인덱스를 생성할 수 없다. 그렇지만 인덱스 구성 칼럼은 동일하지만 칼럼의 순서가 다르면 서로 다른 인덱스로 생성할 수 있다.
ex) JOB + SAL 칼럼순서 인덱스와 SAL + JOB 칼럼 순서의 인덱스는 별도 생성 가능.
칼럼 순서는 SQL성능에 중요한 영향을 미친다.

 

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

SQL Server의 인덱스는 저장 구조에 따라 클러스터형(clustered) / 비클러스터형(nonclustered)으로 나뉜다.

 

  • 클러스터형 인덱스 주요 특징
  1. 인덱스의 리프 페이지가 곧 데이터 페이지다. 따라서 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다. (인덱스 키 칼럼과 나머지 칼럼을 리프 페이지에 같이 저장하기 때문에 테이블을 랜덤 액세스할 필요가 없다). 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.
  2. 리프 페이지의 모든 로우(=데이터)는 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장된다. 테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다. 그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다. (전화번호부 한 권을 상호와 인명으로 동시에 정렬할 수 없는 것과 마찬가지다.)

SQL_246

 

 

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

 

  • 전체 테이블 스캔

테이블에 존재하는 모든 블록의 데이터를 읽는다. (Full Table Scan연산이기 때문 - HWM까지의 블록내 모든 데이터를 읽음) 이렇게 읽은 블록들은 재사용성이 떨어지기 때문에 메모리에서 곧 제거될 수 있도록 관리된다.

SQL_247

HWM (High Water Mark) : 테이블의 고수위 마크. 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치 (지워져서 데이터가 존재하지 않을 수도 있음.).

 

  • 옵티마이저가 전체 테이블 스캔을 선택하는 이유
  1. SQL문에 조건이 존재하지 않는 경우.
  2. SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우.
  3. 옵티마이저의 취사 선택.
    ex) 조건만족 데이터가 많아서 대부분 블록을 액세스해야 한다고 판단하는 경우 등
  4. 그 외
    ex) 병렬처리 방식으로 처리하는 경우, 전체 테이블 스캔 방식의 힌트를 사용한 경우 등

 

 

  • 인덱스 스캔

인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법. 인덱스의 리프 블록을 읽으면 인덱스 구성 칼럼의 값과 테이브르이 레코드 식별자를 알 수 있다. 인덱스에 존재하지 않는 칼럼의 값이 필요한 경우에는 현재 읽은 레코드 식별자를 이용하여 테이블을 액세스해야 한다. SQL문에서 필요로 하는 모든 칼럼이 인덱스 구성 칼럼에 포함된 경우 테이블에 대한 액세스는 발생하지 않는다.

인덱스는 인덱스 구성칼럼의 순서로 정렬되어있다. A + B 라면 먼저 칼럼 A로 정렬되고 칼럼 A의 값이 동일할 경우에는 칼럼 B로 정렬된다. 칼럼 B까지 모두 동일하면 레코드 식별자로 정렬된다. (인덱스를 경유하여 데이터를 읽으면 그 결과 또한 정렬되어 반환된다.)

 

  • 1.인덱스 유일 스캔
    유일 인덱스 (Unique Index)를 사용하여 단 하나의 데이터를 추출하는 방식. 유일 인덱스 구성 칼럼에 대해 모두 ‘='로 값이 주어진 경우에만 가능한 인덱스 스캔 방식이다.

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

SQL_248

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

그 외 인덱스 전체 스캔(Index Full Scan), 인덱스 고속 전체 스캔(Fast Full Index Scan), 인덱스 스킵 스캔(Index Skip Scan)등이 존재한다.

 

 

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

데이터를 액세스 하는 방법은 크게 인덱스 스캔방식과 전체 테이블 스캔방식 두 가지다.

SQL_249

인덱스 스캔에서는 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽기 때문에 불필요하게 다른 블록을 더 읽을 필요가 없다. 한번에 I/O 요청에 한 블록씩 데이터를 읽는다.
전체 테이블 스캔은 데이터를 읽을 때 한번의 I/O요청으로 여러 블록을 한꺼번에 읽는다. (어차피 모든 데이터를 읽을 것이라면 한 번 읽기 작업을 할 때 여러 블록을 함께 읽는 것이 효율적이다.)