[데이터베이스] CH 6 물리적 데이터베이스 설계 (2)

 단일 단계 인덱스

 

1. 단일 단계 인덱스(인덱스된 순차 화일)

 - 인덱스된 순차 화일은 인덱스를 통해서 임의의 레코드를 접근할 수 있는 화일

 - 단일 단계 인덱스의 각 엔트리는 <탐색 키, 레코드에 대한 포인터>

 - 엔트리들은 탐색 키 값의 오름차순으로 정렬됨

 - 인덱스는 데이터 화일과는 별도의 화일에 저장됨

 - 인덱스의 크기는 데이터 화일의 크기에 비해 훨씬 작음

 - 하나의 화일에 여러 개의 인덱스들을 정의할 수 있음

 - 인덱스가 정의된 필드를 탐색 키라고 부름

 - 탐색 키의 값들은 후보 키처럼 각 투플마다 반드시 고유하지는 않음

 - 키를 구성하는 애트리뷰트뿐만 아니라 어떤 애트리뷰트도 탐색 키로 사용될 수 있음

 - 인덱스의 엔트리들은 탐색 키 값의 오름차순으로 저장되어 있으므로 이진 탐색을 이용할 수도 있음

 

단일 단계 인덱스

 

2. 기본 인덱스 (primary index)

 - 탐색 키가 데이터 화일의 기본 키인 인덱스를 기본 인데스라고 부름

 - 기본 인덱스는 기본 키의 값에 따라 정렬된 데이터 화일에 대해 정의됨

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

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

 

 

3. 클러스터링 인덱스

 - 탐색 키 값에 따라 정렬된 데이터 화일에 대해 정의됨

 - 각각의 상이한 키 값마다 하나의 인덱스 엔트리가 인덱스에 포함됨

 - 범위 질의에 유용

 - 범위의 시작 값에 해당하는 인덱스 엔트리를 먼저 찾고, 범위에 속하는 인덱스 엔트리들을 따라가면서 레코드들을 검색할 때 디스크에서 읽어오는 블록 수가 최소화 됨

 

클러스터링 인덱스

 

 

4. 비클러스터링 인덱스

 

비클러스터링 인덱스

 

 

5. 보조 인덱스 (secondary index)

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

 - 보조 인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 화일에 대해 정의됨

 - 보조 인덱스는 일반적으로 밀집 인덱스이므로 같은 수의 레코드들을 접근할 때 보조 인덱스를 통하면 기본 인덱스를 통하는 경우보다 디스크 접근 횟수가 증가할 수 있음

 

보조(밀집) 인덱스

 

 

6. 희소 인덱스와 밀집 인덱스의 비교

 - 희소 인덱스는 각 데이터 블록마다 한 개의 엔트리를 갖고, 밀집 인덱스는 각 레코드마다 한 개의 엔트리를 가짐

 - 레코드의 길이가 블록 크기보다 훨씬 작은 일반적인 경우에는 희소 인덱스의 엔트리 수가 밀집 인덱스의 엔트리 수보다 훨씬 적음

 - 희소 인덱스는 일반적으로 밀집 인덱스에 비해 인덱스 단계 수가 1정도 적으므로 인덱스 탐색 시 디스크 접근 수가 1만큼 적을 수 있음

 - 희소 인덱스는 밀집 인덱스에 비해 모든 갱신과 대부분의 질의에 대해 더 효율적

 - 그러나 질의에서 인덱스 정의된 애트리뷰트만 검색하는 경우에는 데이터 화일에 접근할 필요 없이 인덱스만 접근해서 질의를 수행할 수 있으므로 밀집 인덱스가 희소보다 더 유리

 - 한 화일은 한 개의 희소 인덱스와 다수의 밀집 인덱스를 가질 수 있음

 

 

7. 밀집 인덱스

 

8. 클러스터링 인덱스 

데이터 화일에 대한 밀집 인덱스

 

 

9. 클러스터링 인덱스와 보조 인덱스의 비교

 - 클러스터링 인덱스는 희소 인덱스일 경우가 많으며 범위 질의 등에 좋음

 - 보조 인덱스는 밀집 인덱스이므로 일부 질의에 대해서는 화일을 접근할 필요 없이 처리할 수 있음

 

 

 

 다단계 인덱스

 

1. 다단계 인덱스

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

 - 인덱스 엔트리를 탐색하는 시간을 줄이기 위해서 단일 단계 인덱스를 디스크 상의 하나의 순서 화일로 간주하고, 단일 단계 인덱스에 대해서 다시 인덱스를 정의할 수 있음

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

 - 가장 상위 단계 인덱스를 마스터 인덱스라고 부름

 - 마스터 인덱스는 한 블록으로 이루어지기 때문에 주기억 장치에 상주할 수 있음

 - 대부분의 다단계 인덱스는 B+-를 사용

 

 

2. SQL의 인덱스 정의문

 - SQL의 CREATE TABLE문에서

  + PRIMARY KEY절로 명시한 애트리뷰트에 대해서는 DBMS가 자동적으로 기본 인덱스 생성

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

 - SQL2는 인덱스 정의 및 제거에 관한 표준 SQL을 제공하지 않음

 - 다른 애트리뷰트에 추가로 인덱스를 정의하기 위해서는 DBMS마다 다소 구문이 다른 CREATE INDEX문을 사용해야 함

 - 인덱스 생성

CREATE INDEX ENMPDNO_IDX ON EMPLOYEE(DNO);

  

 

3. 다수의 애트리뷰트를 사용한 인덱스 정의

 - 한 릴레이션에 속하는 두 개 이상의 애트리뷰트들의 조합에 대하여 하나의 인덱스를 정의할 수 있음

CREATE INDEX EmpIndex ON EMPLOYEE (DNO, SALARY);

- 위의 예시를 아래의 질의들에 활용 가능

SELECT *
FROM EMPLOYEE
WHERE DNO=3 AND SALARY=4000000;

SELECT *
FROM EMPLOYEE
WHERE DNO>=2 AND DNO <=3 AND SALARY >=3000000 AND SALARY<=4000000;

SELECT *
FROM EMPLOYEE
WHERE DNO=2;

SELECT *
FROM EMPLOYEE
WHERE SALARY>=3000000 AND SALARY <= 40000000;

 

 

4. 인덱스의 장점과 단점

 - 인덱스는 검색 속도를 향상시키지만 인덱스를 인덱스를 저장하기 위한 공간이 추가로 필요하고 삽입, 삭제, 수정 연산의 속도는 저하시킴

 - 소수의 레코드들을 수정하거나 삭제하는 연산의 속도는 향상됨

 - 릴레이션이 매우 크고, 질의에서 릴레이션의 투플들 중에 일부(2%~4%)를 검색하고, WHERE절이 잘 표현되었을 때 특히 성능에 도움이 됨

 

 

 인덱스 선정 지침과 데이터베이스 튜닝

 

1. 인덱스 선정 지침과 데이터베이스 튜닝

 - 가장 중요한 질의들과 이들의 수행 빈도, 가장 중요한 갱신들과 이들의 수행 빈도, 이와 같은 질의와 갱신들에 대한 바람직한 성능들을 고려하여 인덱스를 선정함

 - 워크로드 내의 각 질의에 대해 이 질의가 어떤 릴레이션들을 접근하는가, 어떤 애트리뷰트들을 검색하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력은 얼마인가 등을 고려함

 - 워크로드 내의 각 갱신에 대해 이 갱신이 어떤 릴레이션들을 접근하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력은 얼마인가, 갱신의 유형, 갱신의 영향을 받는 애트리뷰트 등을 고려함

 - 어떤 릴레이션에 인덱스를 생성해야 하는가, 어떤 애트리뷰트를 탐색 키로 선정해야 하는가, 몇 개의 인덱스를 생성해야 하는가, 각 인덱스에 대해 클러스터링 인덱스, 밀집 인덱스/희소 인덱스 중 어느 유형을 선택할 것인가 등을 고려함

 - 인덱스를 선정하는 한 가지 방법은 가장 중요한 질의들을 차례로 고려해보고, 현재의 인덱스가 최적의 계획에 적합한지 고려해보고, 인덱스를 추가하면 더 좋은 계획이 가능한지 알아봄. 인덱스를 생성하기 전에 워크로드 내의 갱신에 미치는 영향도 고려

 - 물리적 데이터베이스 설계는 끊임없이 이루어지는 작업

 

 

2. 인덱스를 결정하는데 도움이 되는 몇 가지 지침

 1) 기본 키는 클러스터링 인덱스를 정의할 훌륭한 후보

 2) 외래 키도 인덱스를 정의할 중요한 후보

 3) 한 애트리뷰트에 들어 있는 상이한 값들의 개수가 거의 전체 레코드 수와 비슷하고, 그 애트리뷰트가 동등 조건에 사용된다면 비클러스링 인덱스를 생성하는 것이 좋음

 4) 투플이 많이 들어 있는 릴레이션에서 대부분의 질의가 검색하는 투플이 2%~4% 미만인 경우에는 인덱스 생성

 5) 자주 갱신되는 애트리뷰트에는 인덱스 정의하지 않는 것이 좋음

 6) 갱신이 빈번하게 이루어지는 릴레이션에는 인덱스를 많이 만드는 것을 피함

 7) 후보 키는 기본 키가 갖는 모든 특성을 마찬가지로 갖기 때문에 인덱스를 생성할 후보

 8) 인덱스는 화일의 레코드들을 충분히 분할할 수 있어야 함

 9) 정수형 애트리뷰트에 인덱스를 생성

 10) VARCHAR 애트리뷰트에는 인덱스 생성 X

 11) 작은 화일에는 인덱스를 만들 필요 X

 12) 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스들을 다시 생성하는 것이 좋음

 

 

3. 언제 인덱스가 사용되지 않는가?

 - DBMS 질의 최적화 모듈이 릴레이션 크기가 작아서 인덱스가 도움되지 않는다고 판단

 - 인덱스가 정의된 애트리뷰트에 산술 연산자가 사용됨

 - 내장함수가 집단함수 대신에 사용됨

 - 널값에 대해서는 거의 인덱스 사용 X
 

 

4. 질의 튜닝을 위한 추가 지침

 - DISTINCT절의 사용을 최소화하기

 - GROUP BY절과 HAVING절의 사용을 최소화하기

 - 임시 릴레이션의 사용 피하기

 - SELECT * 대신 애트리뷰트 이름들을 구체적으로 명시하기