[오라클 DB] 인덱스 선정 지침과 데이터베이스 튜닝

 

물리적 데이터베이스 설계는 끊임없이 이루어지는 작업으로, 성능을 향상시키기 위한 고민을 지속적으로 해야 한다.

 

인덱스 선정 지침

 

1. 어떤 릴레이션에 인덱스를 생성해야 하는가

2. 어떤 애트리뷰트를 탐색 키로 선정해야 하는가

3. 몇 개의 인덱스를 생성해야 하는가

4. 각 인덱스에 대해 클러스터링 인덱스, 밀집/희소 인덱스 중 어느 유형을 선택할 것인가

 

 

인덱스를 선정하는 한 가지 방법은 가장 중요한 질의들을 차례로 고려해보고, 현재의 인덱스가 최적의 계획에 적합한지 고려해보고, 인덱스를 추가하면 더 좋은 계획이 가능한지 알아보는 것이다.

 

 

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

 

지침 1 : 기본 키는 클러스터링 인덱스를 정의할 훌륭한 후보이다.

 

지침 2 : 외래 키도 인덱스를 정의할 중요한 후보이다.

 

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

 

지침 4 : 튜플이 많이 들어 있는 릴레이션에서 대부분의 질의가 검색하는 튜플이 2% ~ 4% 미만인 경우에는 인덱스를 생성한다.

 

지침 5 : 자주 갱신되는 애트리뷰트에는 인덱스를 정의하지 않는 것이 좋다.

 

지침 6 : 갱신이 빈번하게 이루어지는 릴레이션에는 인덱스를 많이 만드는 것을 피한다.

 

지침 7 : 후보 키는 기본 키가 갖는 모든 특성을 마찬가지로 갖기 때문에 인덱스를 생성할 좋은 후보이다.

 

지침 8 : 인덱스는 파일의 레코드들을 충분히 분할할 수 있어야 한다.

 

지침 9 : 정수형 애트리뷰트에 인덱스를 생성한다. (Integer : 4Byte, 인덱스는 작은 Byte의 애트리뷰트이거나 Integer이 최적)

 

지침 10  : VARCHAR 애트리뷰트에는 인덱스를 만들지 않는다. ( 지침 9와 같은 이유 )

 

지침 11 : 작은 파일에는 인덱스를 만들 필요가 없다.

 

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

 

 

 

인덱스가 사용되지 않는 경우

 

인덱스가 사용되지 않는 경우는 어떤 경우가 있을까?

 

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

 

말 그대로, 릴레이션의 크기가 작아 인덱스를 굳이 생성할 필요가 없으면 사용되지 않는다.

 

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

 

SELECT   *
FROM      EMPLOYEE
WHERE   SALARY * 12 > 40000000;

 

 

SALARY 연산 결과를 가지고 비교하기 때문에, 모든 레코드의 SALARY 값에 연산한 결과를 비교해야 한다. 

 

 

Case 3. DBMS가 제공하는 내장 함수가 사용됨

 

SELECT   *
FROM      EMPLOYEE
WHERE   SUBSTR(EMPLOYEE, 1, 1) = '김'

 

내장 함수 중 하나인 SUBSTR을 사용한 모습이며, EMPNAME의 1번째 글자가 '김'인지 모든 레코드를 순회하며 확인해보아야 하기 때문에 인덱스가 사용되지 않는다.

 

 

Case 4. 널 값

 

SELECT   *
FROM      EMPLOYEE
WHERE   MANAGER IS NULL;

 

오라클에서는 NULL 값을 인덱스에서 제외시키기 때문에 NULL인지 확인하기 위해서는 모든 레코드를 순회해야 한다.

 

---

 

질의 튜닝을 위한 추가 지침

 

1. DISTINCT절의 사용을 최소화하라 (중복 제거 수행속도가 느리다)

2. GROUP BY절과 HAVING절의 사용을 최소화하라

3. 임시 릴레이션의 사용을 피해라

4. SELECT * 대신에 SELECT절에 애트리뷰트 이름들을 구체적으로 명시하라