데이터베이스database - 인덱스index란

파일에 대한 접근이 순차접근만 한다면 어떤 종류의 인덱스도 불필요

 

엔트리들을 탐색키 값의 오름차순으로 정렬된다.

인덱스는 DBMS가 파일 내의 특정 레코드를 빠르게 찾기위한 데이터 구조이다.

 

인덱스를 통해 질의를 수행하면 응답시간이 향상된다.

 

데이터베이스 응용에서는 디스크의 입출력이 많은 시간이 소요된다.

이를 줄여 성능을 향상시킬 수 있다.

 

인덱스는  임의접근을 필요로 하는 응용에 적합하다.

 

데이터파일과는 별도의 파일에 저장된다.

 

 

탐색키에 해당하는 일부 애트리뷰트만 인덱스에 포함되기때문에 인덱스의 크기는 데이터 파일의 크기에 비해 훨씬 작다.

 

하나의 파일에 여러 개의 인덱스를 정의할 수 있다.

 

인덱스가 정의된 필드를 탐색키라고 부른다.

 

탐색키의 값은 후보키 값처럼 반드시 고유하지 않다.

후보키와달리 두개이상의 튜플이 동일한 탐색키를 가질 수 있다.

 

키를구성하는 애트리뷰트뿐만 아니라 다른 애트리뷰트도 탐색키가 될 수 있다.

 

인덱스 파일은 데이터 파일보다 크기가 작아 데이터파일을 순차적으로 탐색하는 것보다 시간이 더 적게걸린다.

 

인덱스 엔트리는 키 값의 오름차순으로 정렬되었기 때문에 이진탐색을 이용할 수 있다.

인덱스는 파일이 매일 클 경우 이진탐색을 이용하면 더욱 유용

 

인덱스 전체를 주기억장치에 유지할 수 있을 때 특히 인덱스가 성능에 도움이 된다.

이경우 어떤 레코드를 찾는 질의도 한번의 디스크(데이터 파일에 대한) 접근 만 필요로 한다.

 

B+- 트리 인덱스는 RDBMS(관계 DBMS) 에서 가장 널리사용되는 다단계 인덱스 구조이다.

 

 

---

단일 단계 인덱스

---

 

 

 

 

 

• primary index 기본 인덱스

 

탐색키가 데이터 파일의 기본키인 인덱스

레코드는 기본키의 값에 따라 클러스터링됨

기본인덱스는 기본키의 값에 따라 정렬된 데이터 파일에 대해 정의된다.

기본 인덱스는 흔히 희소 인덱스로 유지할 수 있다.

 

희소 인덱스는 데이터 파일을 구성하는 각 블록마다 하나의 탐색 키 값이 인덱스 엔트리에 포함된다.

 

일반적으로 각 인덱스엔트리는 블록내의 첫 번째 레코드의 키 값(블록 앵커)blockanchor라고부름)을 갖는다.

첫번째 레코드의 키 값을 block anchor라고 함

 

 

각 릴레이션마다 최대 1개의 기본 인덱스만 가질 수 있음

 

 

 

• clustering index 클러스터링 인덱스

탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의된다.

 

각 데이터 블록대신 하나의 인덱스 엔트리가 각각의 상이한 키 값마다 인덱스에 포함되어 그 탐색 키 값을 갖는 첫 번째 레코드의 주소(또는 레코드가 들어 있는 블록의 주소)를 가리킨다.

 

범위 질의에 유용

범위 시작값에 해당하는 인덱스의 엔트리를 먼저 찾음

클러스터링 인덱스에서는 인접한 탐색 키 값을 갖는 레코드들이 디스크에서 가깝게 저장되어있음

범위에 속하는 인덱스 엔트리들을 따라가면서 레코드들을 검색할 때 디스크에서 읽어오는 블록 수가 최소화됨

어떤 인덱스 엔트리에서 참조되는 데이터 블록을 읽어오면 그 데이터 블록에 들어잇는 대부분의 레코드들은 범위를 만족함

 

인덱스 엔트리의 정렬 순서와 데이터 파일 레코드의 정렬 순서가 일치해서 범위를 만족하는 레코드를 검색할때

범위에 속하는 첫번째 레코드를 인덱스 엔트리에서 찾은 후 인덱스 엔트리의 포인터들을 따라가면 주어진 범위에 속하는 레코드들을 인접한 데이터블록들에서 빠르게 찾을 수 있음

 

 

 

이에반해 비 클러스터링 인덱스에서는 데이터파일의 레코드들이 탐색키와 무관하게 저장되어있다

데이터레코드를 검색할때 마다 매범 디스크 블록을 접근해야 하는 경우가 많다

 

 

 

 

• secondary index 보조 인덱스

 

한 파일은 기껏해야 한가지 필드들의 조합에 대해서만 정렬될 수 있따.

 

WHERE 절을 적용할 속성에대해 index를 적용하는것이 좋다.

 

흔히 한 릴레이션에 여러개의 인덱스를 정의할 필요성이있다.

 

보조인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의된다.

보조인덱스는 일반적으로 밀집 인덱스다.

기본인덱스보다 디스크 접근 횟수가 증가할 수 있다.

기본인덱스를 사용한 순차접근은 효율적이지만 보조 인덱스를 사용한 순차접근은 비효율적이다.

각 레코드를 접근하기 위해서 디스크에서 블록을 읽어올 필요가 있을 수 있다.

 

보조인덱스는 탐색키는 아니지만 빠르게 조회할 필요가 있는 속성에 대해 정의하는것이 좋다.

 

 

보조인덱스는 기본 인덱스처럼 레코드를 빠르게 찾는다는 동일한 목적을 달성

 

 

 

 

• sparse index 희소 인덱스

일부 키값에 대해서만 인덱스에 엔트리를 유지하는 인덱스

 

인덱스 엔트리에는 각 블록마다 한 개의 탐색 키 값이 인덱스 엔트리에 포함된다.

 

 

 

 

 

• dense index 밀집 인덱스

각 레코드의 키 값에 대해서 인덱스에 엔트리를 유지하는 인덱스

 

데이터 파일의 각 레코드의 탐색 키 값이 인덱스 엔트리에 포함된다.

 

 

 

 

 

 

---

다단계 인덱스

---

인덱스 자체가 클경우 인덱스를 탐색하는 시간도 오래걸릴 수 있음

이때 사용하는게 다단계 인덱스

단일 단계 인덱스에 대해 다시 인덱스를 정의한것

 

1단계 인덱스는 밀집인덱스,희소인덱스 모두 가능

2단계이상의 인덱스는 희소 인덱스만 가능

 

다단계 인덱스는 가장 상위 단계의 모든 인덱스 엔트리들이 한 블록에 들어갈 수 있을때까지 이런 과정을 반복

 

가장 상위 단계 인덱스를 마스터 인덱스(master index) 라고 한다.

마스터 인덱스는 한 블록으로 이루어져있다.

따라서 주기억장치에 상주할 수 있다.

 

새로운 인덱스엔트리를 추가하거나 기존 인덱스 엔트리를 삭제하면 단일 단계 인덱스의 경우보다 처리과정이 복잡해진다

 

다단계 인덱스의 각 단계는 하나의 순서 파일이다.

 

대부분의 다단계 인덱스는 B+- 트리(균형이진탐색트리) 사용

너비 높이차이가 +-1 차이인것

B+-트리의 각 노드는 다수의 자식 노드들을 가진다.

 

각 노드는 한 개의 디스크 블록을 차지한다.

 

일반적으로 한 블록에 자식 노드들에 대한 포인터를 수백 개 저장할 수 있다.

 

B+-트리는 추가될 인덱스 엔트리들을 위해 각 인덱스 블록에 예비공간을 남긴다.

 

인덱스의 각 단계들이 오름차순으로 유지되어야 하므리 다단계 인덱스의 갱신은 단일 단계 인덱스보다 시간이 오래걸리고 복잡하다.

 

대부분의 데이터베이스 응용에서는 검색비율이 갱신비율보다 월등히 높으므로 모든 DBMS에서는 인덱스를 다단계 인덱스로 유지한다.

 

 

 

 

---

SQL 인덱스 정의문

---

릴레이션의 기본키는 DBMS가 자동으로 기본 인덱스를 생성한다.

UNIQUE로 명시한 애트리뷰트에 대해서는 DBMS가 자동으로 보조 인덱스를 생성한다.

 

일반적으로 뷰는 인덱스를 만들지 못한다.

 

CREATE INDEX 인덱스이름 ON 테이블 (속성,...)

 

 

두개 이상의 애트리뷰트조합에 대해 하나의 인덱스를 정의할 수 있다.

이 경우 해당 속성들의 WHERE절을 이용한 탐색에 도움이 된다.

=> 이경우 첫번째 애트리뷰트만을 이용한 WHERE절에는 이 인덱스 엔트리를 사용할 수 있지만

두번째 이후의 애트리뷰트만을 이용한 WHERE절에는 인덱스가 사용될 수 없다.

 

 

일반적으로 3개 이하의 애트리뷰트를 사용하라고 권고한다.

애트리뷰트의 개수가 늘어날수록 이 인덱스를 활용하는 탐색 조건이 복잡해지고, 인덱스 엔트리의 길이가 늘어나기 때문에 탐색 성능이 저하된다.

 

 

 

 

---

인덱스의 장점과 단점

---

장점

인덱스는 검색 속도를 향상시킨다

 

단점

인덱스를 저장하기 위한 공간이 추가로 필요하다.

삽입,삭제,수정 연산의 속도는 저하시킨다.

데이터 파일이 갱신되는 경우에는 파일에 대해 정의된 모든 인덱스들에도 갱신사항이 반영되어야 한다.

인덱스의 갱신은 데이터베이스의 성능을 저하시킨다.

 

인덱스의 개수를 3개 이내로 유지하는것이 좋다.

 

소수의 레코드들을 수정하거나 삭제하는 연산의 속도는 향산한다.

=>수정하거나 삭제하려면 먼저 해당 레코드를 찾아야 하기 때문

인덱스를 사용하면 찾는 효율이 증가되므로 일반적으로 인덱스를 갱신하는 데 필요한 추가 오버헤드를 무시할 수 있다.

 

릴레이션이 매우 크고, 질의에서 릴레이션의 투플들 중에 일부를 검색하고 WHERE절이 잘 표현되어있을 때 특히 성능에 도움이 된다.