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

6. 다단계 인덱스

• 인덱스 자체가 클 경우에는 인덱스를 탐색하는 시간도 오래 걸릴 수 있음
• 인덱스 엔트리를 탐색하는 시간을 줄이기 위해 단일 단계 인덱스를 디스크 상의 하나의 순서 파일로 간주하고, 단일 단계 인덱스에 대해서 다시 인덱스를 정의할 수 있음
• 다단계 인덱스는 가장 상위 단계의 모든 인덱스 엔트리들이 한 블록에 들어갈 수 있을 때까지 이런 과정을 반복함
• 가장 상위 단계 인덱스를 마스터 인덱스(master index)라고 부름
• 마스터 인덱스는 한 블록으로 이루어지기 때문에 주기억 장치에 상주할 수 있음
• 대부분의 다단계 인덱스는 B+-트리를 사용 (꼭 알아야 한다)

다단계 인덱스

* 얻는 게 있으면 잃는 것도 있다 : Trade-off

* index maintenance overhead : 다단계 인덱스에서 데이터를 추가하거나 수정할 때 모든 인덱스를 수정해야 하는 상황이 될 수 도 있다.

* skewed tree (편향된 트리) → 깊이가 각각이 다른 문제점이 생김 → b+-트리로 사용한다. (balanced tree)

→ node로부터 leaf node까지 가는 시간이 동일함을 보장해준다.

 

다단계 인덱스와 단일 단계 인덱스의 시간 차이 비교

 

※  SQL의 인덱스 정의문

• SQL의 CREATE TABLE 문에서 PRIMARY KEY절로 명시한 애트리뷰트에 대해서는 DBMS가 자동적으로 기본 인덱스를 생성
• UNIQUE로 명시한 애트리뷰트에 대해서는 DBMS가 자동적으로 보조 인덱스를 생성
• SQL2는 인덱스 정의 및 제거에 관한 표준 SQL문을 제공하지 않음
• 다른 애트리뷰트에 추가로 인덱스를 정의하기 위해서는 DBMS마다 다소 구문이 다른 CREATE INDEX문을 사용해야 함

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

• 한 릴레이션에 속하는 두 개 이상의 애트리뷰트들의 조합에 대하여 하나의 인덱스를 정의할 수 있음
• 예 : CREATE INDEX EmpIndex ON EMPLOYEE (DNO, SALARY);
• 이 인덱스는 아래의 질의에 활용될 수 있음                                                                                                                         SELECT * FROM EMPLOYEE WHERE DNO=3 AND SALARY=4000000;
• DNO에 대한 조건만 들어간 질의(앞의 애트리뷰트)에는 활용 가능하다.
• SALARY만 갖고는 질의에 활용할 수 없다. (DNO가 필요함)

※  인덱스의 장점과 단점

• 인덱스는 검색 속도를 향상시키지만 인덱스를 저장하기 위한 공간이 추가로 필요하고 삽입, 삭제, 수정 연산의 속도는 저하시킴
• 소수의 레코드들을 수정하거나 삭제하는 연산의 속도는 향상됨
• 릴레이션이 매우 크고, 질의에서 릴레이션의 tuple들 중에 일부(예, 2%~4%)를 검색하고, WHERE절이 잘 표현되었을 때 특히 성능에 도움이 됨

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

1. 기본 키는 클러스터링 인덱스를 정의할 훌륭한 후보
2. 외래 키도 인덱스를 정의할 중요한 후보
3. 한 애트리뷰트에 들어 있는 상이한 값들의 개수가 거의 전체 레코드 수와 비슷하고, 그 애트리뷰트가 동등 조건에 사용된다면 비 클러스터링 인덱스를 생성하는 것이 좋음
4. 투플이 많이 들어 있는 릴레이션에서 대부분의 질의가 검색하는 투플이 2%~4%미만인 경우에는 인덱스를 생성
5. 자주 갱신되는 애트리뷰트에는 인덱스를 정의하지 않는 것이 좋음
6. 갱신이 빈번하게 이루어지는 릴레이션에는 인덱스를 많이 만드는 것을 피함
7. 후보 키는 기본 키가 갖는 모든 특성을 마찬가지로 갖기 때문에 인덱스를 생성할 후보
8. 인덱스는 파일의 레코드들을 충분히 분할할 수 있어야 함
9. 정수형 애트리뷰트에 인덱스를 생성
10. VARCHAR 애트리뷰트에는 인덱스를 만들지 않음
11. 작은 파일에는 인덱스를 만들 필요가 없음
12. 대량의 데이터를 삽입할 때(벌크 데이터 로드)는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스들을 다시 생성하는 것이 좋음
13. ORDER BY절 , GROUP BY절에 자주 사용되는 애트리뷰트는 인덱스를 정의할 후보

※  언제 인덱스를 사용하지 않는가?

• 시스템 카탈로그(데이터베이스가 데이터에 대한 정보를 저장하는 것, 메타데이터)가 오래 전의 데이터베이스 상태를 나타냄
• DBMS의 질의 최적화 모듈이 릴레이션의 크기가 작아서 인덱스가 도움이 되지 않는다고 판단함
• 인덱스가 정의된 애트리뷰트에 산술 연산자가 사용됨
• DBMS가 제공하는 내장 함수가 집단 함수 대신에 사용됨 (SELECT * FROM EMPLOYEE WHERE SUBSTRING(EMPNAME, 1, 1) = '김';
• 널값에 대해서는 일반적으로 인덱스가 사용되지 않음

※  질의 튜닝을 위한 추가 지침

• DISTINCT절의 사용을 최소화하라
• GROUP BY절과 HAVING절의 사용을 최소화하라
• 임시 릴레이션의 사용을 피하라
• SELECT * 대신에 SELECT 절에 애트리뷰트 이름들을 구체적으로 명시하라