2014년 7월 14일 월요일

Index Fundmentals

제 2 절 인덱스 기본


1. 인덱스 특징과 종류


인덱스 장점 : 검색 성능의 최적화
인덱스 단점 : DML(Insert, Update, Delete) 작업은 테이블과 인덱스를 함께 변경해야 하기 때문에 오히려 느려진다.

가. 트리 기반 인덱스


B tree 인덱스 : 브랜치 블록 + 리프 블록, 브랜치 블록 중에서 가장 상위에 있는 블록은 루트 블록이라고 함.

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


< 리프 블록 >

1. 가장 아래 단계에 존재
2. 인덱스를 구성하는 컬럼의 데이터 + 해당 데이터를 가지고 있는 행의 위치를 가리키는 RID로 구성됨
3. 인덱스 데이터는 인덱스를 구성하는 컬럼의 값으로 정렬, 값이 동일하면 RID 순으로 저장
4.  양방향 링크를 통해 오름차순과 내림 차순 검색이 용이함
5. 일치 검색( '=' 연산자), 범위 검색( 'Between','>','<' 등과 같은 연산자)

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

<인덱스 검색 예제>




인덱스 스캔을 해서 반환된 결과의 데이터는 인덱스 데이터와 동일한 순서로 갖게 된다.
인덱스 생성시 동일 컬럼으로 구성된 인덱스를 중복해서 생성할 수 없다.
인덱스 구성 컬럼은 동일하지만 컬럼의 순서가 다르면 서로 다른 인덱스로 생성할 수 있다.
예) JOB + SAL 컬럼으로 구성된 인덱스와 SAL+JOB으로 구성된 인덱스는 다른 인덱스로 취급된다.
인덱스 컬럼의 순서는 성능에 중요한 영향을 미친다.
오라클은 B-tree 인덱스외에도 비트맵 인덱스(Bitmap Index), reverse key 인덱스, 함수 기반 인덱스 등을 지원
알티베이스는 B-Tree 인덱스와 R-Tree 인덱스를 지원

< SQL Server의 클러스터형 인덱스 >

SQL Server 의 인덱스는 clustered Index와 Non-clustered Indexf로 나뉨

< Clustered Index의 특징 >

1. 인덱스의 리프페이지가 곧 데이터 페이지, 그러므로, RID가 없고, 추가적으로 테이블 access할 필요 없음.
2. 리프 페이지의 모든 로우(=데이터)는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장됨. 클러스터 인덱스는 테이블당 한개만 생성 가능


2. Full Table scan과 Index scan


가. Full Table scan


 - 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식으로 검색
- 테이블의 High water Mark 아래의 모든 블록을 읽는다. 이런 블록들은 재사용성이 떨어지고, Buffer cache에서 금방 제거됨

<옵티마이저가 Full table scan을 선택하는 경우>
1. SQL문에 조건이 없는 경우
2. SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우
3. 인덱스가 존재하더라도 함수를 사용하여 컬럼 변형이 일어난 경우
4. 옵티마이저 취사 선택 -  인덱스가 존재하더라도 테이블이 가진 대부분의 블록을 읽어야 할 경우
5. 병렬 처리 방식으로 처리하는 경우
6. Full Table scan 힌트 사용한 경우


나. Index scan

인덱스는  인덱스 대상 컬럼 데이터와 RID로 구성되어 있기 때문에 인덱스 스캔 후 RID를 참조하여 테이블을 액세스한다.
만일, SQL문에서 필요로 하는 컬럼이 인덱스 구성 컬럼에 모두 포함된 경우 인덱스 스캔만 한다. DB2에서는 Index only scan 이라고 지칭

인덱스 스캔하여 SQL 걀과를 가져올 경우, 결과 데이터가  인덱스 정렬 순서와 동일하기 때문에 경우에 따라 불필요한 정렬 작업을 제거할 수 있다.

< Index Unique Scan >
- Unique Index를 이용해 단 하나의  데이터만 추출
- '=' 연산자가 주어진 경우

< Index Range Scan >
- Index를  이용해 한 건 이상의 데이터를 추출하는 방식
- '=' 연산자가 주어지지 않은 경우

< Index Range Scan Descending >
- 인덱스의 리프 블록의 양방향 링크를 이용하여 내림 차순으로 데이터를 읽는 방식
- 최대값 찾을 때 용이




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


다. Full Table Scan과 Index Scan 방식의 비교
 


Index scan의 특징
- RID를 이용해 검색하는 데이터의 정확한 위치를 알고 있기 때문에 블필요한 블록을 읽지 않는다.
- 한번의 I/O 요청에 한 블록씩 읽음

Ful Table scan의 특징
- 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽음

- 거의 모든 테이블을 읽어야 할 경우 Full table scan이 유리할 수 있다.

댓글 없음:

댓글 쓰기