Move Fast

  물리적 데이터베이스 설계

• 논리적인 설계의 데이터 구조를 보조 기억 장치상의 파일(물리적인 데이터 모델)로 사상한다.
• 하나의 파일에 여러 릴레이션이 저장될 수 있다.
• 예상 빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석한다.
• 데이터에 대한 효율적인 접근을 제공하기 위하여 저장 구조와 접근 방법들을 다룬다.
• 특정 DBMS의 특성을 고려하여 진행된다.
• 질의를 효율적으로 지원하기 위해서 인덱스 구조를 적절히 사용한다.

  보조 기억 장치의 역할

• 사용자가 원하는 데이터를 검색하기 위해서 DBMS는 디스크 상의 데이터베이스로부터 사용자가 원하는 데이터를 포함하고 있는 디스크 블록(block)을 읽어서 주기억 장치(RAM)로 가져온다.
• 데이터가 변경된 경우에는 블록들을 디스크에 다시 기록한다.
• 블록 크기는 512바이트부터 수 킬로바이트까지 다양하다.
• 전형적인 블록 크기는 4,096바이트
• 각 파일은 고정된 크기의 블록들로 나누어져서 저장된다. 즉, 여러 개의 블록들에 파일의 데이터가 분산되어 저장된다.
• 디스크는 데이터베이스를 장기간 보관하는 주된 보조 기억 장치이다. (Ex. HDD)

  자기 디스크의 역할

• 디스크는 자기 물질로 만들어진 여러 개의 판으로 이루어진다.
• 각 면마다 디스크 헤드가 존재한다.
• 각 판은 트랙과 섹터로 구분된다.
• 정보는 디스크 표면 상의 동심원(트랙)을 따라 저장된다.
• 여러 개의 디스크 면 중에서 같은 지름을 갖는 트랙들을 “실린더”라고 부른다.
• 블록은 여러 개의 섹터들로 이루어진다.
• 디스크에서 임의의 블록을 읽어오거나 기록하는데 걸리는 시간 = 탐구 시간(seek time) + 회전 지연 시간(rotational delay) + 전송 시간(transfer time)

  버퍼 관리와 운영 체제

• 디스크 입출력은 컴퓨터 시스템에서 가장 속도가 느린 작업이므로 입출력 횟수를 줄이는 것이 DBMS의 성능을 향상하는데 매우 중요하다.
• 가능하면 많은 블록들을 주기억 장치에 유지하거나, 자주 참조되는 블록들을 주기억 장치에 유지하면 블록 전송 횟수(디스크->주기억 장치)를 줄일 수 있다.
• 버퍼 : 디스크 블록들을 저장하는데 사용되는 주기억 장치 공간
• 버퍼 관리자는 운영 체제의 구성요소로서 주기억 장치 내에서 버퍼 공간을 할당하고 관리한다.
• 운영 체제에서 버퍼 관리를 위해 흔히 사용되는 LRU(Least Recently Used) 알고리즘은 데이터베이스를 위해 항상 우수한 성능을 보이지는 않는다.

  디스크 상에서 파일의 레코드 배치

• 릴레이션의 애트리뷰트는 고정 길이 또는 가변 길이의 필드로 표현된다.
• 연관된 필드들이 모여서 고정 길이 또는 가변 길이의 레코드가 된다.
• 한 릴레이션을 구성하는 레코드들의 모임은 파일이라고 부르는 블록들의 모임에 저장된다.
• 한 파일에 속하는 블록들의 위치가 반드시 인접해 있을 필요는 없다. (분산되어 저장될 수 있다.)
• 인접한 블록들을 읽는 경우에는 탐구 시간과 회전 지연 시간이 들지 않기 때문에 입출력 속도가 빠르므로 블록들이 인접하도록 한 파일의 블록들을 재조직할 수 있다.
• BLOB(Binary Large Object) 타입

- 이미지(GIF, JPG), 동영상(MPEG, AVI) 등 대규모 크기의 데이터를 저장하는데 사용된다.

- BLOB의 최대 크기는 MS SQL Server에서 2GB까지도 가능하다.

- DBMS 마다 제공하는 크기가 다르다.

• 채우기 인수

- 각 블록에 레코드를 채우는 공간의 비율

- 채우기 인수 = 블록 1개의 크기 / 레코드 1개의 크기

- 나중에 레코드가 삽입될 때 기존의 레코드들이 이동하는 가능성을 줄이기 위해서 한 블록에 레코드를 가득 채우지 않고 빈 공간을 남겨둔다.

• 고정 길이 레코드

- i번째 레코드를 접근하기 위해서 (레코드 길이)*(i-1)+1 의 위치에서 레코드를 읽는다.

- 레코드를 삭제하여 여러 개의 레코드가 이동할 때는 (삭제한 레코드 개수)*(레코드 길이) 바이트 만큼 이동한다.

- 한 개의 레코드를 삭제하면 고정길이 바이트만큼 이동한다.

• 파일 내의 클러스터링(intra-file clustering)

- 한 파일 내에서 함께 검색될 가능성이 높은 레코드들을 디스크 상에서 물리적으로 가까운 곳에 모아두는 것

- 논리적으로 연관되어 함께 검색될 가능성이 높은 두 개 이상의 파일에 속한 레코드들을 디스크 상에서 물리적으로 가까운 곳에 저장하는 것 (두 릴레이션에서 연관된 투플들이 같은 블록에 저장된다.)

• 파일 조직의 유형

- 히프 파일(heap file)

- 순차 파일(sequential file)

- 인덱스된 순차 파일(indexed sequential file)

- 직접 파일(hash file) : DBMS가 내부적으로 사용자가 정의한 SQL을 빠르게 처리하고자 사용

  히프 파일(비순서 파일)

• 가장 단순한 파일 조직
• 일반적으로 레코드들이 삽입된 순서대로 파일에 저장된다.
• 삽입이 쉬우며 레코드들의 순서는 없다.
• 삽입 : 새로 삽입되는 레코드는 파일의 가장 끝에 첨부된다.
• 검색 : 원하는 레코드를 찾기 위해서는 모든 레코드들을 순차적으로 접근해야 한다.
• 삭제 : 원하는 레코드를 찾은 후에 그 레코드를 삭제하고, 삭제된 레코드가 차지하던 공간을 재사용하지 않는다.
• 빈 공간을 재사용하려면 레코드 삽입 시(또는 검색의 경우) 파일의 끝 대신에 파일 중간에서 빈 자리를 찾아야 하므로 레코드 삽입 시간이 증가한다.
• 좋은 성능을 유지하기 위해서 히프 파일을 주기적으로 재조직(빈 공간들을 회수하여 자유 공간에 반환)할 필요가 있다.
• 릴레이션에 데이터를 한꺼번에 적재할 때(bulk loading), 릴레이션에 단지 몇 개의 블록들만 있을 때, 모든 투플들이 검색 위주로 사용될 때 히프 파일이 주로 사용된다.

[히프 파일에 저장된 레코드의 모습]

• 히프 파일의 성능

- 히프 파일은 질의에서 모든 레코드들을 참조하고, 레코드들을 접근하는 순서는 중요하지 않을 때, 효율적이다.

SELECT * FROM EMPLOYEE;

- 특정 레코드를 검색하는 경우에는 히프 파일이 비효율적이다. 히프 파일에 b개의 블록이 있다면, 원하는 블록을 찾기 위해 평균적으로 b/2개의 블록을 읽어야 한다.

SELECT TITLE FROM EMPLOYEE WHERE EMPNO=1365;

- 몇 개의 레코드들을 검색하는 경우에도 비효율적이다. 조건에 맞는 레코드를 이미 한 개 이상 검색했더라도 파일의 마지막 블록까지 읽어서 원하는 레코드가 존재하는지 확인해야하기 때문이다.

SELECT EMPNAME, TITLE FROM EMPLOYEE WHERE DNO=2;

- 급여의 범위를 만족하는 레코드들을 모두 검색하는 아래의 질의도 EMPLOYEE 릴레이션의 모든 레코드들을 접근해야 한다.

SELECT EMPNAME, TITLE FROM EMPLOYEE

WHERE SALARY >= 3000000 AND SALARY <= 4000000;

[ 히프 파일에서 연산의 유형별 소요 시간 ]

  순차 파일

• 레코드들이 하나 이상의 필드 값에 따라 순서대로 저장된 파일 (필드별로 정렬되어 저장된다.)
• 레코드들이 일반적으로 레코드의 탐색 키(search key) 값의 순서에 따라 저장된다.
• 탐색 키는 순차 파일을 정렬하는데 사용되는 필드로, 고유하게 식별하는 기본 키와는 다르다.
• 삽입 : 삽입하려는 레코드의 순서를 고려해야 하기 때문에 시간이 많이 걸릴 수 있다.
• 삭제 : 삭제된 레코드가 사용하던 공간을 빈 공간으로 남기기 때문에 히프 파일의 경우와 마찬가지로 주기적으로 순차 파일을 재조직해야 한다.
• 기본 인덱스가 순차 파일에 정의되지 않는 한 순차 파일은 데이터베이스 응용을 위해 거의 사용되지 않는다.
• 필드별로 정렬되어 있으므로 이진 탐색이 가능하다.

[ 순차 파일에 저장된 릴레이션 - 기본 키를 탐색 키로 사용함 ]

• 순차 파일의 성능

- EMPLOYEE 릴레이션이 순차파일에 EMPNO의 순서대로 저장되어 있을 때 아래의 SELECT 문은 이진 탐색을 이용할 수 있다.

SELECT TITLE FROM EMPLOYEE WHERE EMPNO=1365;

- 아래의 SELECT문은 비효율적인데, 그 이유는 WHERE절에 사용된 SALARY는 저장 순서와 무관하기 때문에 파일 전체를 탐색해야하기 때문이다.

SELECT EMPNAME, TITLE FROM EMPLOYEE

WHERE SALARY >= 3000000 AND SALARY <= 4000000;

  단일 단계 인덱스

• 인덱스된 순차 파일은 인덱스를 통해서 임의의 레코드에 접근할 수 있다.
• 인덱스 자체가 파일을 의미하며, 인덱스는 데이터 파일과는 구분된다.
• 단일 단계 인덱스의 각 엔트리는 <탐색 키, 레코드에 대한 포인터> 로 이루어진다.
• 탐색 키(search key) : 인덱스가 정의된 필드
• 포인터는 탐색할 레코드가 저장된 디스크 블록을 참조한다.
• 탐색 키의 값들은 후보 키처럼 각 투플마다 반드시 고유하지 않다.
• 키를 구성하는 애트리뷰트뿐만 아니라 어떤 애트리뷰트도 탐색 키로 사용될 수 있다.
• 엔트리들은 탐색 키 값의 오름차순으로 정렬된다. (이진 탐색 가능)
• 데이터 파일에 들어 있는 여러 애트리뷰트들 중에서 탐색 키에 해당하는 일부 애트리뷰트만 인덱스에 포함되므로 인덱스의 크기는 데이터 파일의 크기에 비해 훨씬 작다.
• 인덱스가 데이터 파일보다 크기가 작으므로 인덱스를 순차적으로 찾는 시간은 데이터 파일을 순차적으로 탐색하는 시간보다 적게 걸린다.
• 인덱스는 DBMS가 파일 내의 특정 레코드들을 빠르게 찾을 수 있도록 하는 데이터 구조이므로 인덱스를 통하여 질의를 수행하면 응답 시간이 향상된다.
• 인덱스는 임의 접근을 필요로 하는 응용에 적합하다.
• 하나의 파일에 여러 개의 인덱스들을 정의할 수 있다.

[ 좌 - 인덱스(정렬된 탐색 키), 우 - 릴레이션 ]

  기본 인덱스(Primary Index)

• 탐색 키가 데이터 파일의 기본 키인 인덱스를 기본 인덱스라고 부른다.
• 기본 인덱스는 기본 키의 값에 따라 정렬된 데이터 파일에 대해 정의된다.
• 각 릴레이션마다 최대한 한 개의 기본 인덱스(기본 키가 1개이므로)를 가질 수 있다.
• 레코드들이 탐색 키(=기본 키)의 오름차순으로 정렬되어 있다.
• 제일 작은 키 값을 인덱스로 두게 되면, 다른 레코드들에 대해 인덱스를 저장할 필요가 없어진다. (블록 내 레코드들은 오름차순 정렬이므로 탐색이 쉬움)

[ 좌 - 인덱스 블록, 우 - 데이터 블록 ]

• 기본 인덱스는 흔히 희소 인덱스로 유지할 수 있다.
• 희소 인덱스는 데이터 파일을 구성하는 각 블록을 대표하는 하나의 탐색 키 값이 인덱스 엔트리에 포함된다.

[ 희소 인덱스 ]

  클러스터링 인덱스(Clustering Index)

• 탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의된다.
• 각각의 상이한 키 값(애트리뷰트)마다 하나의 인덱스 엔트리가 인덱스에 포함된다.
• 범위 질의에 유용하다. (Ex. WHERE EMPNO >= 1000 AND EMPNO <= 1200)
• 인덱스 엔트리들의 정렬 순서와 데이터 파일의 레코드들의 정렬 순서가 일치한다.
• 클러스터링 인덱스에서는 인접한 탐색 키 값을 갖는 레코드들이 디스크에서 가깝게 저장된다.
• 범위의 시작 값에 해당하는 인덱스 엔트리를 먼저 찾고, 범위에 속하는 인덱스 엔트리들을 따라가면서 레코드들을 검색할 때, 디스크에서 읽어오는 블록 수가 최소화된다.
• 어떤 인덱스 엔트리에서 참조되는 데이터 블록을 읽어오면 그 데이터 블록에 들어 있는 대부분의 레코드들은 범위를 만족한다.
• 비 클러스터링 인덱스는 인덱스 엔트리들이 인접해 있어도 레코드들은 대부분의 경우 멀리 떨어져 있다. 따라서 최악의 경우 같은 블록을 여러 번 접근할 수 있다.

  보조 인덱스(Secondary Index)

• 한 파일은 기껏해야 한 가지 필드들의 조합에 대해서만 정렬될 수 있다.
• 보조 인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의된다.
• 보조 인덱스는 일반적으로 밀집 인덱스(인덱스 수가 희소 인덱스보다 많다)이므로 같은 수의 레코드들을 접근할 때 보조 인덱스를 활용하면 기본 인덱스를 활용하는 것보다 디스크 접근 횟수가 증가할 수 있다.
• 임의 접근(Random Access) 시 모든 레코드에 대해 바로 접근할 수 있다.
• 레코드가 없을 경우에도 인덱스만 보고 파악할 수 있다.
• Ex. 신용카드 회사에서는 신용카드 번호를 사용하여 고객의 레코드를 빈번하게 검색하므로 신용카드 번호에 대해 기본 인덱스를 생성한다. 또한 카드를 분실한 고객이 분실 신고를 할 때 신용카드번호를 기억하지 못한다면 주민등록번호를 사용하여 고객의 레코드를 빠르게 찾을 수 있어야 한다. 그러나 회사의 고객 파일에 기본 인덱스(신용카드번호)만 존재하고 주민등록번호에 보조 인덱스가 존재하지 않는다면 주민등록번호를 사용하여 특정 고객의 레코드를 찾을 때 몇십분씩 시간이 걸릴 수 있다. 주민등록번호는 신용카드번호만큼 특정 고객의 레코드를 찾는데 자주 사용되지는 않지만 주어진 주민등록번호를 갖는 레코드를 빠르게 찾기 위해서 보조 인덱스를 정의하는 것이 바람직하다.

[ 보조 인덱스 ]

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

• 희소 인덱스 : 일부 키 값(각 블록을 대표하는 탐색 키 값)에 대해서만 인덱스에 엔트리를 유지하는 인덱스

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

• 밀집 인덱스는 데이터 레코드들이 탐색 키 값이 증가하는 순서대로 정렬되어 있는 경우 클러스터링 인덱스이기도 하다.
• 희소 인덱스는 각 데이터 블록마다 한 개의 엔트리를 갖고, 밀집 인덱스는 각 레코드마다 한 개의 엔트리를 가진다.
• 레코드의 길이가 블록 크기보다 훨씬 작은 일반적인 경우(한 블록에 들어가는 레코드 수가 많은 경우)에는 희소 인덱스의 엔트리 수가 밀집 인덱스의 엔트리 수보다 훨씬 적다.
• 희소 인덱스는 일반적으로 밀집 인덱스에 비해 인덱스 단계 수가 1정도 적으므로 인덱스 탐색 시 디스크 접근 수가 1만큼 적을 수 있다.
• 희소 인덱스는 밀집 인덱스에 비해 모든 갱신과 대부분의 질의에 대해 더 효율적이다.
• 그러나 질의에서 인덱스가 정의된 애트리뷰트만 검색(COUNT() 사용)하는 경우에는 데이터 파일을 접근할 필요 없이 인덱스만 접근해서 질의를 수행할 수 있으므로 밀집 인덱스가 희소 인덱스보다 유리하다.(모든 레코드가 인덱스와 대응하므로)
• 한 파일은 한 개의 희소 인덱스와 다수의 밀집 인덱스를 가질 수 있다.
• 희소 인덱스는 디스크 상에서 데이터 파일의 물리적인 순서에 의존한다.

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

• 클러스터링 인덱스는 희소 인덱스일 경우가 많으며 범위 질의 등에 좋다.
• 릴레이션의 중간에 투플이 삽입되어 오버플로를 야기하고, 이로 인해서 인덱스 엔트리들의 정렬 순서와 데이터 파일의 레코드들의 정렬 순서가 불일치하는 경우 범위 질의에 적절하지 못하므로 클러스터링 인덱스는 무용지물이된다.
• 클러스터링 인덱스를 정의할 경우 채우기 인수에 낮은 값을 지정하여 추가로 삽입되는 레코드들에 대비하는 것이 바람직하다. (블록에 빈 공간을 두는 것)
• 보조 인덱스는 밀집 인덱스이므로 일부 질의에 대해서는 데이터 파일(디스크 블록)을 접근할 필요 없이 처리할 수 있다. (탐색 키인 애트리뷰트만 검색하는 경우)

  다단계 인덱스

• 인덱스 자체가 클 경우에는 인덱스를 탐색하는 시간도 오래 걸릴 수 있다.
• 다단계 인덱스 : 인덱스 엔트리를 탐색하는 시간을 줄이기 위해서 단일 단계 인덱스를 디스크 상의 하나의 순서 파일로 간주하고, 단일 단계 인덱스에 대해 다시 인덱스를 정의하는 방법이다.
• 2단계 이상의 인덱스는 희소 인덱스만 가능하다. 처음 정의한 인덱스를 1단계 인덱스(기존의 인덱스)라 하고 이 인덱스에 대해서 정의한 추가 인덱스를 2단계 인덱스라 한다.
• 다단계 인덱스는 가장 상위 단계의 모든 인덱스 엔트리들이 한 블록에 들어갈 수 있을 때까지 단일 단계 인덱스에 대해 다시 인덱스를 정의하는 과정을 반복한다.
• 마스터 인덱스(master index) : 가장 상위 단계 인덱스
• 마스터 인덱스는 한 블록으로 이루어지기 때문에 주기억 장치에 상주할 수 있다.
• 다단계 인덱스의 각 단계는 하나의 순서 파일이다.
• 새로운 인덱스 엔트리를 추가하거나 기존의 인덱스 엔트리를 삭제하면 단일 단계 인덱스의 경우보다 처리 과정이 복잡해진다.
• 대부분의 다단계 인덱스는 균형 탐색 이진트리를 일반화한 트리를 사용한다. 트리는 추가될 인덱스 엔트리들을 위해 각 인덱스 블록에 예비 공간을 남겨둔다.
• 인덱스의 각 단계들이 오름차순으로 유지되어야 하므로 다단계 인덱스의 갱신은 단일 단계 인덱스의 갱신보다 시간이 오래 걸리고 복잡해진다. 하지만 대부분의 데이터베이스 응용에서 검색 비율이 갱신 비율보다 월등히 높으므로 모든 DBMS에서는 인덱스를 다단계 인덱스로 유지한다.

[디스크접근횟수 3번]

  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;

- 단, “WHERE SALARY=4000000 AND DNO=3”처럼 DNO와 SALARY의 순서가 바뀌면 질의는 실행되지 않는다.

- 정의한 인덱스에서는 DNO에 대해 오름차순으로 정렬한 뒤 DNO가 같으면 SALARY에 대해 오름차순으로 정렬했기 때문이다.

- 위에서 정의한 인덱스는 아래의 질의에도 활용될 수 있다.

SELECT * FROM EMPLOYEE WHERE DNO>=2 AND DNO <=3

AND SALARY >= 3000000 AND SALARY <= 4000000;

SELECT * FROM EMPLOYEE WHERE DNO=2;  (또는 DNO에 대한 범위 질의)

- 위에서 정의한 인덱스는 아래의 질의에는 활용될 수 없다.

SELECT * FROM EMPLOYEE

WHERE SALARY >= 3000000 AND SALARY <= 4000000;  (또는 SALARY에 대한 동등 조건)

• 복합 애트리뷰트에 인덱스를 정의할 때 3개 이하의 애트리뷰트를 사용하는 것이 바람직하다. 인덱스가 정의된 복합 애트리뷰트에 포함된 애트리뷰트의 개수가 늘어날수록 이 인덱스를 활용하는 탐색 조건이 복잡해지고, 인덱스 엔트리의 길이가 늘어나기 때문에 탐색 성능이 저하된다.

  인덱스의 장점과 단점

• 인덱스는 검색 속도를 향상시키지만 인덱스를 저장하기 위한 공간이 추가로 필요하고 삽입, 삭제, 수정 연산(인덱스를 만든 컬럼 값 변경 시)의 속도는 저하시킨다.
• 소수의 레코드들을 수정하거나 삭제하는 연산의 속도는 향상된다. 레코드를 수정하거나 삭제하려면 먼저 해당 레코드를 찾아야 하기 때문에 파일에 많은 인덱스가 정의되어 있지 않는 한, 인덱스를 사용하여 레코드를 찾는 효율이 증가되므로 일반적으로 인덱스를 갱신하는 데 필요한 추가 오버헤드를 무시할 수 있다.
• 릴레이션이 매우 크고, 질의에서 릴레이션의 투플들 중 일부를 검색하고, WHERE절이 잘표현되었을 때(인덱스를 만든 컬럼의 순서가 적절할 때) 특히 성능에 도움이 된다.

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

• 가장 중요한 질의들과 이들의 수행 빈도, 가장 중요한 갱신들과 이들의 수행 빈도, 이와 같은 질의와 갱신들에 대한 바람직한 성능들을 고려하여 인덱스를 선정한다.
• 워크로드 내의 각 질의에 대해 이 질의가 어떤 릴레이션들을 접근하는가, 어떤 애트리뷰트들을 검색하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력(Selectivity)은 얼마인가 등을 고려한다.
• 워크로드 내의 각 갱신에 대해 이 갱신이 어떤 릴레이션들을 접근하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력은 얼마인가, 갱신의 유형(INSERT/DELETE/UPDATE), 갱신의 영향을 받는 애트리뷰트 등을 고려한다.
• 어떤 릴레이션에 인덱스를 생성해야 하는가, 어떤 애트리뷰트를 탐색 키로 선정해야 하는가, 몇 개의 인덱스를 생성해야 하는가, 각 인덱스에 대해 클러스터링 인덱스, 밀집 인덱스, 희소 인덱스 중 어느 유형을 선택할 것인가 등을 고려한다.
• 인덱스를 선정하는 한 가지 방법은 가장 중요한 질의들을 차례로 고려해보고, 현재의 인덱스가 최적의 계획에 적합한지 고려해보고, 인덱스를 추가하면 더 좋은 계획이 가능한지 알아본다.
• 물리적 데이터베이스 설계는 끊임없이 이루어지는 작업이다.

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

• 기본 키는 클러스터링 인덱스를 정의할 훌륭한 후보
• 외래 키도 인덱스를 정의할 중요한 후보
• 한 애트리뷰트에 들어 있는 상이한 값들의 개수가 거의 전체 레코드 수와 비슷하고, 그 애트리뷰트가 동등 조건에 사용된다면 비 클러스터링 인덱스를 생성하는 것이 좋다. (Ex: 학번)
• 투플이 많이 들어 있는 릴레이션에서 대부분의 질의가 검색하는 투플이 2~4% 미만인 경우에는 인덱스를 생성하는 것이 좋다.
• 자주 갱신되는 애트리뷰트에는 인덱스를 정의하지 않는 것이 좋다. (Ex: 주소)
• 갱신이 빈번하게 이루어지는 릴레이션에는 인덱스를 많이 만드는 것을 피하는 것이 좋다. (3개 이하가 적당하다.)
• 가능하면 릴레이션당 인덱스의 개수를 3개 이내로 유지하는 것이 좋다.
• 후보 키는 기본 키가 갖는 모든 특성을 마찬가지로 갖기 때문에 인덱스를 생성할 후보이다.
• 인덱스는 파일의 레코드들을 충분히 분할할 수 있어야 한다.
• 정수형 애트리뷰트에 인덱스를 생성하는 것이 좋다. 그 다음에는 고정 길이 애트리뷰트에 인덱스를 만드는 것이 좋다.
• VARCHAR 애트리뷰트에는 인덱스를 만들지 않는 것이 좋다. (구글의 권장사항, Ex. CHAR(10)인 name 컬럼에서 10바이트 미만의 이름을 검색하면 빈 공간을 채워서 주기 때문에 검색 시 못찾는 경우가 발생한다.)
• 날짜형, 실수형 애트리뷰트에는 인덱스를 만들지 않도록 한다.
• 작은 파일에는 인덱스를 만들 필요가 없다.
• 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스들을 다시 생성하는 것이 좋다.
• 클러스터링 인덱스를 정의하기 전에는 클러스터링 인덱스를 정의할 애트리뷰트를 기준으로 먼저 데이터 파일을 정렬하는 것이 좋다.
• ORDER BY절(정렬 속도 향상), GROUP BY절(그룹화 속도 향상)에 자주 사용되는 애트리뷰트는 인덱스를 정의할 후보이다.
• 특정 레코드 검색 시 WHERE 절에서 확률(전체 레코드 수/컬럼의 상이한 값의 개수)이 낮은 인덱스를 사용하는 것이 좋다. 상이한 값이 많을수록 읽어오는 레코드 수가 적기 때문에 성능(시간 절약)이 향상된다.

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

• 시스템 카탈로그가 오래 전의 데이터베이스 상태를 나타낼 때
• DBMS의 질의 최적화 모듈이 릴레이션 크기가 작아서 인덱스가 도움이 안된다고 판단할 때
• 인덱스가 정의된 애트리뷰트에 산술 연산자가 사용될 때

SELECT * FROM EMPLOYEE WHERE SALARY*12 > 4000000;

단, 아래의 질의에는 인덱스가 사용된다.

SELECT * FROM EMPLOYEE WHERE SALARY > 4000000/12;

• DBMS가 제공하는 내장 함수가 집단 함수 대신에 사용될 때

SELECT * FROM EMPLOYEE WHERE SUBSTR(EMPNAME, 1, 1)=‘김’;

(SUBSTR은 MS SQL Server 내장 함수이다.)

아래의 질의처럼 집단 함수를 사용할 경우 인덱스가 사용된다.

SELECT * FROM EMPLOYEE FROM EMPNAME LIKE ‘김%’;

• 널값에 대해서는 일반적으로 인덱스가 사용되지 않는다. (검색되지 않음)

SELECT * FROM EMPLOYEE WHERE MANAGER IS NULL;

  질의 튜닝을 위한 추가 지침

• DISTINCT절의 사용을 최소화
• GROUP BY절과 HAVING절의 사용을 최소화
• 임시 릴레이션의 사용을 피할 것

위의 두 SELECT문 대신에 아래와 같은 한 개의 SELECT 문이 바람직하다.

• SELECT * 대신에 SELECT 절에 애트리뷰트 이름들을 구체적으로 명시할 것 (컬럼 추가/삭제 시 검색한 내용을 활용하는 소스 코드에서 오류가 발생할 수 있다. 또한 불필요한 애트리뷰트가 전송되지 않으므로 네트워크 트래픽이 감소된다.)