목표
1.단일 단계 인덱스에 대해 알아본다.
2.다단계 인덱스에 대해 알아본다.
3.인덱스 선정 지침과 데이터베이스 튜닝에 대해 알아본다.
단일 단계 인덱스
만일 데이터베이스 접근을 오직 순차적으로만 한다면 인덱스는 필요하지 않을것이다. 인덱스된 순차 파일은 인덱스를 통해 임의의 레코드를 접근할 수 있는 파일이다. 단일 단계 인덱스의 각 엔트리는 <탐색 키, 레코드에 대한 포인터>로 구성된다. 따라서 인덱스를 잘 활용하면 쿼리의 응답시간이 빨라진다.
엔트리들은 탐색 키 값의 오름차순으로 정렬된 상태이다.
인덱스는 기본적으로 데이터 파일으 10% 내외의 크기를 가지므로 순차탐색시 훨씬 빠르게 찾을 수 있으며 정렬되어 있으므로 이진 탐색을 이용할 수 있다.
인덱스는 데이터 파일과는 별도로 다른 파일에 저장된다.(당연히 크기는 데이터 파일에 비해 훨씬 작다.) 예시를 보자.
인덱스가 정의된 필드를 탐색 키 라고 부르는데, 후보키처럼 각 튜플마다 반드시 고유하지는 않다. 따라서 어떤 애트리뷰트도 탐색 키로 쓸 수 있다.
-기본 인덱스
먼저 데이터 파일이 <키> 순으로 순차정렬이 되어 있어야 한다.(전제조건) 일정한 레코드의 묶음을 데이터 블록이라고 정의하면, 블록당 하나의 엔트리를 가진다. 블럭당 가장 첫번째 레코드 혹은 가장 마지막 레코드의 첫번째 필드값을 탐색 키 값으로 갖는다. 즉 전체 파일에 대한 탐색이 아니라 각 블록당 하나의 엔트리(블록의 앵커 엔트리에 대한 키 값) 을 가지므로, 기본 인덱스는 희소인덱스 이다.
탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의된다. (위 그림과 같은 상황) 각각의 상이한 키 값마다 하나의 인덱스 엔트리가 인덱스에 포함된다.
범위 질의에 유용하다. 시작 값에 해당하는 인덱스 엔트리를 먼저 찾고, 범위에 속하는 인덱스 엔트리들을 따라가면서 레코드들을 검색할 때 데스크에 읽어오는 블록의 수가 최소화 될 수 있다.
위 그림은 클러스터링 된 인덱스이다. 이해가 안가면 비-클러스터링 인덱스 그림을 살펴보자.
보조 인덱스(Secondary Index)
한 파일은 기껏해야 하나의 필드들의 조합에 대해서만 정렬된다. 보조 인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의된다.
보조 인덱스는 일반적으로 밀집 인덱스이므로, 같은 수의 레코드들을 접근할 때 보조 인덱스를 통하면 기본 인덱스를 통하는 경우보다 디스크 접근 회수가 증가한다.
희소 인덱스와 밀집 인덱스를 비교해보자.
희소 인덱스는 각 데이터 블록마다 한 개의 엔트리를 갖는다면, 밀집 인덱스는 각 레코드마다 한 개의 엔트리를 가진다.
레코드의 길이가 블록 킈보다 훨씬 작은 일반적인 경우, 희소 인덱스의 엔트리 수가 밀집 인덱스 엔트리 수보다 훨씬 적다.
희소 인덱스는 일반적으로 밀집 인덱스에 비해 인덱스 단계 수가 1정도 적으므로 디스크 접근 수가 1만큼 적을 수 있다.
희소 인덱스는 밀집 인덱스에 비해 거의 모든 갱신과 대부분의 질의에 대해 더 효율적이다. 그러나 데이터 파일에 접근하지 않고 인덱스가 정의된 애트리뷰트만 검색(예르(ㅇ예를 들어 COUNT)라면 밀집 인덱스가 더 유리하다.
다단계 인덱스
때로는 인덱스 자체가 크다면 인덱스를 탐색하는 시간도 오래 걸릴 수 있다. 이를 줄이기 위해, 단일 단계 인덱스를 디스크 상의 하나의 순서 파일로 간주하고, 단일 단계 인덱스에 대해서 다시 인덱스를 정의할 수 있다. 다단계 인덱스는 가장 상위 단계의 모든 인덱스 엔트리들이 한 블록에 들어갈 수 있을때까지 이 과정을 반복한다.
가장 상위 인덱스는 '마스터 인덱스'이다. (하나의 블록으로 이루어져 있어서 주 메모리에 상주할 수 있다.) 보통 B+트리를 사용한다.
SQL의 인덱스 정의문
SQL의 CREATE TABLE 문에서 PRIMARY KEY로 생성한 기본키 애트리뷰트에 대해서는 DBMS가 자동으로 기본 인덱스를 생성한다.
또한 UNIQUE로 명시한 애트리뷰트에 대해서는 자동으로 보조 인덱스를 형성한다. 추가로 인덱스를 정의하기 위해서는 CREATE INDEX문을 사용한다.
혹은, 한 릴레이션에 속하는 두개 이상의 애트리뷰트들의 조합에 대하여 하나의 인덱스를 정의할 수 있다.
인덱스의 장점과 단점
인덱스는 검색 속도를 향상시키지만, 인덱스를 저장하기 위한 공간이 추가로 필요하고, 삽입, 삭제, 수정에 관한 연산 속도는 저하시킨다.
소수의 레코드들을 수정하거나 삭제하는 연산의 속도는 향상된다.
도움이 되는 경우는 릴레이션이 매우 크고, 질의에서 릴레이션의 튜플들 중에 일부(2~4%)를 검색하고, WHERE절이 잘 표현되었을때 특히 성능에 도움이 된다.
인덱스 선정 지침과 데이터베이스 튜닝
가장 중요한 질의들와 수행 빈도, 가장 중요한 갱신들과 이들의 수행빈도 등 질의와 갱신에 대한 바람직한 성능을 고려하여 인덱스를 선정한다.
워크로드 내의 각 질의에 대해 이 질의가 어떤 릴레이션들에 접근되는가, 어떤 애트리뷰트들을 검색하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트가 포함되는가, 등등등 고려하기도 한다.
또한, 어떤 릴레이션에 인덱스를 생성해야 하는가, 어떤 애트리뷰트를 탐색키로 선정하는가, 몇 개의 인덱스를 생성하는가, 각 인덱스를 클러스터링,밀집/희소 인덱스 중 어느 유형을 택할것인가도 고려한다.
아래는 인덱스를 결정하는데 도움이 되는 몇 가지 지침이다.
-DISTINCT절의 사용을 최소화하라.
-GROUP BY와 HAVING 절을 최소화 하라.
-SELECT * 대신에 애트리뷰트의 이름을 구체적으로 명시하라.
'컴퓨터공학 > 데이터베이스' 카테고리의 다른 글
| 트랜잭션 (0) | 2018.10.31 |
|---|---|
| 뷰(VIEW) (0) | 2018.10.31 |
| 데이터베이스 정규화 (0) | 2018.10.30 |
| SQL (0) | 2018.10.28 |
| 관계 데이터 모델과 제약조건 (0) | 2018.10.27 |
| 데이터베이스 시스템 개요 (0) | 2018.10.27 |