물리적 데이터베이스 설계

물리적 데이터베이스 설계

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

 

보조 기억 장치

• 각 파일은 고정된 크기의 블록들로 나누어져서 저장됨
• 사용자가 원하는 데이터를 검색하기 위해서 DBMS는 디스크 상의 데이터베이스로부터 사용자가 원하는 데이터를 포함하고 있는 블록을 읽어서 주기억 장치로 가져옴
• 블록 크기는 512바이트부터 수 킬로바이트까지 다양하나 전형적인 블록 크기는 4096바이트
• 데이터가 변경된 경우에는 블록들을 디스크에 다시 기록함
• 디스크는 데이터베이스를 장기간 보관하는 주된 보조 기억 장치

 

자기 디스크

• 디스크는 자기 물질로 만들어진 여러 개의 판으로 이루어짐
• 각 판은 트랙과 섹터로 구분됨
• 각 면마다 디스크 헤드가 있음
• 정보는 디스크 표면 상의 동심원(트랙)을 따라 저장됨
• 여러 개의 디스크 면 중에서 같은 지름을 갖는 트랙들을 실린더라고 부름
• 블록은 한 개 이상의 섹터들로 이루어짐
• 디스크에서 임의의 블록을 읽어오거나 기록하는 데 걸리는 시간은 탐구 시간(seek time), 회전 지연 시간(rotational delay), 전송 시간(transfer time)의 합
• DBMS의 성능은 주로 디스크 입출력 연산의 횟수에 좌우됨

 

버퍼 관리와 운영 체제

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

 

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

• 릴레이션의 애트리뷰트는 고정 길이 또는 가변 길이의 필드로 표현됨
• 연관된 필드들이 모여서 고정 길이 또는 가변 길이의 레코드가 됨
• 한 릴레이션을 구성하는 레코드들의 모임이 파일이라고 부르는 블록들의 모임에 저장됨
• 한 파일에 속하는 블록들이 반드시 인접해 있을 필요는 없음
• 인접한 블록들을 읽는 경우에는 탐구시간과 회전 지연 시간이 들지 않기 때문에 입출력 속도가 빠르므로 블록들이 인접하도록 한 파일의 블록들을 재조직 가능

 

BLOB(Binary Large Object)

• 이미지, 동영상 등 대규모 크기의 데이터를 저장하는 데 사용됨
• 최대 크기는 오라클에서 8TB ~ 128TB

 

채우기 인수

• 각 블록에 레코드를 채우는 공간의 비율
• 나중에 레코드가 삽입될 때 기존의 레코드들을 이동하는 가능성을 줄이기 위해

 

고정 길이 레코드

• 레코드 i를 접근하기 위해서는 n*(i-1)+1의 위치에서 레코드를 읽음

 

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

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

 

파일 간의 클러스터링(inter-file clustering)

• 논리적으로 연관되어 함께 검색될 가능성이 높은 두 개 이상의 파일에 속한 레코드들을 디스크 상에서 물리적으로 가까운 곳에 저장하는 것

 

파일 조직의 유형

• 힙 파일(heap file)
• 순차 파일(sequential file)
• 인덱스 된 순차 파일(indexed sequential file)
• 직접 파일(hash file)

---

힙 파일(heap file)

• 가장 단순한 파일 조직
• 일반적으로 레코드들이 삽입된 순서대로 파일에 저장됨
• 삽입: 새로 삽입되는 레코드는 파일의 가장 끝에 첨부됨
• 검색: 원하는 레코드를 찾기 위해서는 모든 레코드들을 순차적으로 접근해야 함
• 삭제: 원하는 레코드를 찾은 후에 그 레코드를 삭제하고, 삭제된 레코드가 차지하던 공간을 재사용하지 않음

 

힙 파일의 성능

• 힙 파일은 질의에서 모든 레코드들을 참조하고, 레코드들을 접근하는 순서는 중요하지 않을 때
• 특정 레코드를 검색하는 경우에는 힙 파일이 비효율적
  • 힙 파일에 B개의 블록이 있다고 가정했을 때, 원하는 블록을 찾기 위해서 평균적으로 B/2개의 블록을 읽어야 함.
  

 

순차 파일(sequential file)

• 레코드들이 하나 이상의 필드 값에 따라 순서대로 저장된 파일
• 레코드들이 일반적으로 레코드의 탐색 키(search key) 갑의 순서에 따라 저장됨
• 탐색 키는 순차 파일을 정렬하는 데 사용되는 필드를 의미

 

순차 파일의 성능

• EMPLOYEE파일이 EMPNO의 순서대로 저장되어 있을 때 첫 번째 SELECT문은 이진 탐색을 이용할 수 있고, 두 번째 SELECT문의 WHERE절에 사용된 SALARY는 저장 순위와 무관하기 때문에 파일 전체를 탐색해야 함

 

단일 단계 인덱스

• 인덱스 된 순차 파일은 인덱스를 통해서 임의의 레코드를 접근할 수 있는 파일
• 단일 단계 인덱스의 각 엔트리는 <탐색키, 레코드에 대한 포인터>
• 엔트리들은 탐색 키 값의 오름차순으로 정렬됨

• 인덱스는 데이터 파일과는 별도의 파일에 저장됨
• 인덱스의 크기는 데이터 파일의 크기에 비해 훨씬 작음
• 하나의 파일에 여러 개의 인덱스들을 정의할 수 있음
• 인덱스가 정의된 필드를 탐색 키라고 부름
• 탐색 키의 값들은 후보 키처럼 각 튜플마다 반드시 고유하지는 않음

 

기본 인덱스(primary index)

• 탐색 키가 데이터 파일의 기본 키인 인덱스를 기본 인덱스라고 부름
• 기본 인덱스는 기본 키의 값에 따라 정렬된 데이터 파일에 대해 정의됨
• 기본 인덱스는 흔히 희소 인덱스로 유지할 수 있음
• 각 릴레이션마다 최대한 한 개의 기본 인덱스를 가질 수 있음

 

클러스터링 인덱스(clustering index)

• 탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의됨
• 각각의 상이한 키 값마다 하나의 인덱스 엔트리가 인덱스에 포함됨
• 범위 질의에 유용
• 범위의 시작 값에 해당하는 인덱스 엔트리를 먼저 찾고, 범위에 속하는 인덱스 엔트리들을 따라가면서 레코드들을 검색할 때 디스크에서 읽어오는 블록 수가 최소 화됨

 

비 클러스터링 인덱스

• 비 클러스터링 인덱스에서는 데이터 파일의 레코드들이 탐색 키의 값과 무관하게 저장
• 인덱스 엔트리들이 인접해 있어도 관련 레코드들이 대부분 멀리 떨어져 있음

 

보조 인덱스(secondary index)

• 한 파일은 기껏해야 한 가지 필드들의 조합에 대해서만 정렬될 수 있음
• 보조 인덱스는 탐색 키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의됨
• 보조 인덱스는 일반적으로 밀집 인덱스이므로 같은 수의 레코드들을 접근할 때 보조 인덱스를 통하면 기본 인덱스를 통하는 경우보다 디스크 접근 횟수가 증가할 수 있음

 

클러스터링 인덱스 VS 보조 인덱스

• 클러스터링 인덱스는 키가 아닌 필드 값에 따라 정렬된 데이터 파일에 대해 정의되는 반면 보조 인덱스는 정렬되지 않은 데이터 파일에 대해 정의됨
• 클러스터링 인덱스는 희소 인덱스일 경우가 많으며 범위 질의 등에 좋음
• 클러스터링 인덱스를 정의할 때는 채우기 인수에 낮은 값을 저장하여 추가로 삽입되는 레코드들에 대비하는 것이 바람직
• 보조 인덱스는 밀집 인덱스이므로 일부 질의에 대해서는 파일을 접근할 필요 없이 처리할 수 있음

 

희소 인덱스(Sparse index)

• 일부 키 값에 대해서만 인덱스 엔트리를 유지하는 인덱스
• 일반적으로 각 블록마다 한 개의 탐색 키 값이 인덱스 엔트리에 포함됨
• 키 값이 K인 레코드를 찾는 방법
  • 이진 탐색으로 인덱스에서 K와 같거나 작으면서 가장 큰 엔트리 i를 찾음
  • 엔트리 i의 포인터를 따라 데이터 블록을 접근해 탐색 키 값이 K인 레코드를 찾음

 

밀집 인덱스(Dense index)

• 각 레코드의 키 값에 대해서 인덱스에 엔트리를 유지하는 인덱스
• 데이터 파일의 각 레코드의 탐색 키 값이 인덱스 엔트리에 포함됨

 

희소 인덱스 VS 밀집 인덱스

• 희소 인덱스는 각 데이터 블록마다 한 개의 엔트리를 갖고, 밀집 인덱스는 각 레코드마다 한 개의 엔트리를 가짐
• 밀집 인덱스 내의 엔트리 수는 희소 인덱스 내의 엔트리 수에 블록당 평균 레코드 수를 더한 것
• 레코드의 길이가 블록 크기보다 훨씬 작은 일반적인 경우에는 희소 인덱스의 엔트리 수가 밀집 인덱스의 엔트리 수보다 훨씬 적음
• 희소 인덱스는 밀집 인덱스에 비해 모든 갱신과 대부분의 질의에 대해 더 효율적
• 그러나, 질의에서 인덱스가 정의된 애트리뷰트만 검색하는 경우에는 데이터 파일을 접근할 필요 없이 인덱스만 접근해서 질의를 수행할 수 있으므로 희소 인덱스보다 유리함
• 한 파일은 한 개의 희소 인덱스와 다수의 밀집 인덱스를 가질 수 있음
  • 희소 인덱스는 디스크 상에서 데이터 파일의 물리적인 순서에 의존하기 때문

---

다단계 인덱스

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

 

SQL의 인덱스 정의문

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

 

인덱스의 장단점

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

 

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

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

 

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

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

 

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

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

 

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

• 시스템 카탈로그가 오래 전의 데이터베이스 상태를 나타냄
• DBMS의 질의 최적화 모듈이 릴레이션의 크기가 작아서 인덱스가 도움이 되지 않는다고 판단
• 인덱스가 정의된 애트리뷰트에 산술 연산자가 사용됨
• DBMS가 제공하는 내장 함수가 집단 함수 대신에 사용됨

 

질의 튜닝을 위한 추가 지침

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

출처

데이터베이스 <2014, 용환승 교수님>