[데이터베이스] 6장 물리적 데이터 베이스 설계

BBaeng·2022년 6월 12일
데이터베이스학과공부
0

데이터베이스

목록 보기
5/7

6. 물리적 데이터베이스 설계

논리적인 설계의 데이터 구조를 물리적인 데이터 모델로 사상한다.

  • 예상 빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석한다.
  • 분석을 통한 효율적인 접근을 제공하기 위해 저장 구조와 접근 방법들을 다룬다.
  • 특정 DBMS의 특성을 고려한다.
  • 질의의 효율적 지원을 위해 인덱스 구조를 적절히 사용한다.

6.1 보조 기억 장치

데이터 검색을 위해 DBMS는 데이터베이스로부터 원하는 데이터를 포함하고 있는 블록을 읽어서 주기억 장치로 가져온다.

자기디스크

자기물질로 만들어진 여러 개의 판으로 이루어져 있다

  • 면마다 디스크 헤드 존재
  • 각 판은 트랙과 섹터로 구분된다.
  • 정보는 트랙(동심원)을 따라 저장된다.
  • 같은 지름을 갖는 트랙들을 실린더라고 부른다.
  • 시간 = 탐구 시간(arm assembly가 이동하는 시간) + 회전 지연 시간(원판이 회전해 오는 시간) + 전송 시간(읽고 전송)
  • 섹터 : 조각
  • 트랙 : 원
    • 트랙은 여러 섹터로 구성
  • 실린더 : 지름이 같은 서로 다른 트랙들
    • 그림에서 볼 때 1개의 실린더에는 6개의 트랙이 존재한다. (3 * 2[양면])
  • 읽고 쓰는 헤드(arm assembly
  • spindle(축)이 회전하여 원판이 움직이는 구조

💡 탐색시간을 줄이는 방법
함께 엑세스 되는 데이터들은 동일 실린더에 저장하여 arm이 움직이지 않고 볼 수 있다.

6.2 버퍼 관리와 운영 체제

  • 디스크 입출력은 가장 속도가 느린 작업이기에 입출력 횟수를 줄여야 DBMS의 성능을 향상시킬 수 있다.
  • 가능한 많은 블록들 주기억 장치에서 유지 또는 자주 참조되는 블록들을 주기억 장치에 유지하면 블록 전송 횟수를 줄일 수 있다.
  • 버퍼 : 디스크 볼록들을 저장하는데 사용되는 주기억 장치 공간
  • LRU 알고리즘 존재 but 데이터 베이스에서 우수 X

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

  • 애트리뷰트들은 고정 길이 또는 가변 길이의 필드로 표현
  • 연관 필드가 모여서 고정 길이 또는 가변 길이의 레코드가 된다.
  • 레코드들의 모임이 화일이라는 블록들의 모임에 저장된다.

애트리뷰트 -> 필드 -> 레코드 -> 화일


위의 그림에서 가변 길이의 모임도 가능하다.

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

  • 블록들이 반드시 인접할 필요 x
  • 탐구 시간, 회전 지연 시간을 줄이기 위해 인접하도록 주기적으로 재조직한다.

FAT (File Allocation Table)

하나의 블록에 대해 디스크 상 몇번째 블록이 저장되는지 나타내는 표

BLOB(Binary Large Object)

이미지, 동영상 등 대규모 크기의 데이터 저장에 사용된다.

채우기 인수

  • 각 블록에 레코드를 채우는 공간의 비율
  • 기존의 레코드 이동 가능성 줄이기 위해서 어느정도 비워둔다.

  • 오른쪽의 100% 채움의 경우 다른 페이지(블록)에 할당해야한다.

    100% 채움에서 새로운 레코드가 들어올 때 순서가 바뀌어 기존의 레코드가 다른 블록으로 할당되는 경우가 발생한다. 이를 방지하기 위해 왼쪽과 같이 어느 정도의 빈 공간을 둔다.

고정 길이 레코드

간단한 산술계산으로 레코드의 위치를 찾아 읽을 수 있다.

  • 순서 유지의 장점 존재
  • 여러 개의 레코드를 이동하는 단점 존재.

  • 순서가 중요하지 않을 때
  • 레코드 위치 이동에 대한 주기적인 처리 필요하다.

이러한 경우를 대비해 지연 관리 방법이 존재한다.

지연 관리 방법

  • 이동이 필요 없다.
  • 삭제된 공감을 관리하기 위해 free list를 관리한다.

화일 내의 클러스터링

  • 한 화일 내에서 함께 검색될 가능성이 높은 것들은 가까운 곳에 모아둔다.

화일 간의 클러스터링

  • 두 개 이상의 화일 또한 논리적으로 함께 검색될 가능성이 높다면 가까이 저장한다.

6.4 화일 조직

  • 히프 화일
  • 순차 화일
  • 인덱스된 순차 화일
  • 직접 화일

히프 화일(비순서 화일)

  • 가장 단순

  • 삽입된 순서대로 화일에 저장된다.

    • 특정 애트리뷰트 값에 따른 순서가 아니다.

  • 삽입/검색/삭제

    • 삽입 : 화일의 가장 끝에 첨부
    • 검색 : 순차적을 접근
    • 삭제 : 검색 후 삭제된 레코드가 차지하던 공간 재사용하지 않는다.

  • 좋은 성능을 위해서는 주기적으로 재조직해야한다.

성능

  • 모든 레코드를 참조하고 레코드 순서가 중요하지 않을 때 사용한다.
  • 특정 레코드들을 검색하는 경우에는 비효율적이다.
  • 점 질의(특정), 범위 질의(범위) 모두 비효율적이다.

순차 화일

  • 순서대로 저장
  • 레코드의 탐색 키 값의 순서에 따라 저장된다.
  • 탐색 키 : 순차 화일을 정렬하는데 사용되는 필드
  • 삽입/삭제/검색
    • 삽입 연산은 삽입하려는 레코드의 순서를 고려로 시간이 걸린다.
      • 넣을 공간 찾는 시간 + 다른 레코드로 이동하는 시간
    • 삭제 연산은 삭제된 공간 빈 공간으로 남기기에 주기적으로 순차화일 재조직이 필요하다.
    • 검색 연산은 탐색 키의 유무에 따라 시간소요가 다르다.
      • 탐색 키 기반 탐색 : 효율적(이진탐색)
      • 탐색 키가 아닌 필드 사용 : 시간 많이 소요

성능

SELECT TITLE
FROM EMPLOYEE
WHERE EMPNO = 1365;
  • 위의 SELECT 문은 이진 탐색을 이용 (빠르게 접근)
SELECT EMPNAME, TITLE
FROM EMPLOYEE
WHERE SALARY >= 3000000 AND SALARY <= 4000000;
  • 두번 째 SELECT문은 WHERE절 조건이 저장 순서와 무관하기에 화일 전체를 탐색해야한다.

연산 시간 효율 높이기

삽입: 100% 미만의 채우기 인수로 소요시간을 줄일 수 있다.
삭제: FREE LIST를 이용해서 삭제된 공간 관리, 삭제표시 + 재조직으로 시간을 줄일 수 있다.
검색: 탐색 키 이용

6.5 단일 단계 인덱스

이진 탐색의 문제점

  • 정렬비용이 높게 들기에 사용을 많이 하지 않는다.
  • 주로 기본 인덱스에서만 사용
  • 인덱스를 통해서 임의의 레코드를 접근할 수 있는 화일
  • 엔트리 모양
    • <탐색 키, 레코드에 대한 포인터>
  • 탐색 키 값의 오름차순으로 정렬

인덱스

탐색 키를 가진 레코드의 빠른검색을 위해 엔트리모양으로 관리하는 구조

  • 데이터 화일과 별도의 화일에 저장
  • 인덱스의 크기는 데이터 화일 크기에 비해 작다.
    • 주로 탐색 키 + 포인터로 구성
  • 하나의 화일에 여러 인덱스들을 정의할 수 있다.

탐색 키

  • 인덱스가 정의된 필드
  • 후보 키처럼 반드시 고유하지 않다.
  • 모든 애트리뷰트(키 구성 + 키 구성 x) 탐색 키로 사용 가능
  • 인덱스 엔트리들은 탐색 키 값의 오름차순 정의이기에 이진탐색 가능하다.

기본 인덱스

탐색 키가 데이터 화일의 기본 키인 인덱스

  • 희소 인덱스로 불리기도 한다.
  • 각 릴레이션 마다 최대 한 개의 기본 인덱스 생성 가능
  • 기본 키 값에 의해 정렬된 데이터 화일에 대해 정의

  • 레코드 포인터가 아닌 블록 포인터로 존재한다.
  • 레코드 포인터 : 데이터 화일의 모든 레코드 가르킨다.
    • 블록 포인터(50) + 레코드 오프셋(2)
  • 블록 포인터 : 각 데이터 블록의 첫번째 레코드를 가르킨다.
    • 데이터 블록 안에서 레코드 오프셋을 통해 접근
  • 인덱스 또한 인덱스 블록으로 구성되어 있다.
  • 데이터 블록에 있는 레코드들 중에서 하나의 레코드들만 인덱스 엔트리에 존재한다.
    • 주로 첫번째 엔트리

표시가 없는 레코드(ex 20) 찾을 때

  • 인덱스를 통해 저장된 데이터 블록 쉽게 찾기가 가능하다.
  • 10 과 30 사이 존재 => 10 블록 포인터로 데이터 블록에 접근

클러스터링 인덱스

  • 중복이 가능한 탐색키에 의해 정렬된 화일 위에서 정의
  • 범위 질의에 유용
  • 범위 시작 값에 해당하는 인덱스 엔트리를 찾고, 인덱스 엔트리들을 따라가면서 레코드를 검색하면 디스크에서 읽는 블록 수가 최소화 된다.


👆 사원 이름에 따라 정렬

  • index 이용 줄여 블록 순차적으로 접근하여 검색 가능


👆 탐색키 순서에 따라 정렬 x

  • 랜덤하게 접근
  • 블록 Access가 과다 => 탐색 시간, 참조 시간이 길다.

보조 인덱스

  • 탐색 키 값에 따라 정렬되지 않은 데이터 화일에 대해 정의
  • 보조 인덱스는 일반적으로 밀집 인덱스
  • 디스크 접근 횟수가 기본 인덱스보다 높다.

  • 레코드 포인터로 구성
  • 데이터 화일 레코드 수와 동일한 인덱스 수

탐색 키가 기본 키인 경우 (기본 인덱스)


기본 인덱스를 밀집 인덱스으로 표현한 예시이다.

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

  • 엔트리
    • 희소 인덱스는 데이터 블록마다 한 개
    • 밀집 인덱스는 각 레코드 마다 한 개
  • 일반적으로 희소 인덱스의 엔트리 수가 밀집 인덱스의 엔트리 수보다 훨씬 적다.
  • 희소 인덱스가 인덱스 탐색시 디스크 접근 수는 1만큼 적다.
    • 희소 인덱스가 밀집 인덱스에 비해 인덱스 단계 수가 1정도 적기 때문
  • 희소 인덱스가 모든 갱신과 대부분의 질의에 대해 더 효율적
    • 인덱스가 정의된 애트리뷰트만 검색(COUNT질의)의 경우 데이터 화일에 접근할 필요가 없이 인덱스만 접근하기에 밀집 인덱스가 희소 인덱스보다 유리하다.
  • 한 화일에는 한 개의 희소 인덱스와 다수의 밀집 인덱스를 가질 수 있다.

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

  • 클러스터링 인덱스는 희소 인덱스일 경우가 많다.
    • 범위 질의에 좋다.
  • 보조 인덱스는 거의 밀집 인덱스이므로 화일 접근 없이 처리가 가능하기도 하다.

6.6 다단계 인덱스

  • 인덱스 자체가 큰 경우 인덱스 엔트리 탐색시간을 줄이기 위해 단일 단계 인덱스에 대해 다시 인덱스 정의

    • 단일 단계 인덱스를 순서화일로 간주

  • 가장 상위 단계 인덱스 : 마스터 인덱스

    • 마스터 인덱슨느 한 블록이기에 주기억 장치에 상주가능
    • 주기억 장치에 상주하기에 디스크 접근이 필요없다.

  • 주로 B+- 트리 사용

  • 2, 3 단계 희소인덱스
  • 1단계 인덱스 : 희소 or 밀집 인덱스
  • 인덱스 블록 인수만큼 단계 올라갈수록 수가 줄어든다.
    • 블로킹 인수 10 이면 1/10씩 인덱스 수들이 줄어든다.
  • 우리는 항상 마스터 인덱스에서 논의한다.

SQL 인덱스 정의문

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

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

두개 이상의 애트리뷰트들의 조합에 대해 하나의 인덱스 정의 가능

CREATE INDEX EMPINDEX ON EMPLOYEE (DNO, SALARY);
  • DNO 기준 먼저 정렬
  • DNO 같은 것들에 대해 SALARY 정렬
  • 오름차순
  • 범위 조건 (DNO >= 2), 동등 조건 (DNO = 2) 모두에 활용 가능
  • 인덱스 정의어와 관련 없는 부분에 대한 조건질의에는 활용 불가
    • SALARY >= 300000 AND SALARY <= 400000

인덱스의 장단점

  • 장점
    • 검색 속도 향상
    • 소수 래코드 수정, 삭제 연산의 속도가 향상
  • 단점
    • 인덱스 저장을 위한 공간이 추가로 필요
    • 삽입, 삭제, 수정의 연산의 속도는 저하시킨다.

릴레이션이 매우 크고 , 투플들 중 일부를 검색, WHERE절이 잘 표현되어 있는 경우 성능에 큰 도움

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

  • 바람직한 성능들을 고려하여 인덱스 선정
  • 가장 중요한 질의들을 차례로 고려, 현재의 인덱스가 최적의 계획에 적합한지 고려

인덱스 결정에 도움이 되는 지침

  • 기본 키는 클러스터링 인덱스를 정의할 훌륭한 후보
  • 외래 키도 인덱스를 정의할 중요한후보
    • 주로 JOIN에 자주 사용
  • 투플이 많이 들어 있는 릴레이션에서 대부분의 질의가 검색하는 투플이 2%~4%인 경우 인덱스 생성
  • 자주 갱신되는 애트리뷰트에는 인덱스 정의 X
    • 인덱스 갱신에 오버헤드 발생
  • 갱신이 빈번한 릴레이션에도 인덱스 정의 X
  • 한 애트리뷰트에 상이한 값들의 개수가 거의 전체 레코드와 비슷하며, 그 애트리뷰트가 동등 조건에 사용된다면 비 클러스터링 인덱스 생성
  • 후보 키는 기본 키가 갖는 모든 특성을 가지기에 인덱스를 생성할 후보이다.
    • 이 때, 후보 키가 운영에 중요한지 고려하여 결정한다.
  • 인덱스는 화일의 레코드를 충분히 분할해야한다.
  • 정수형 에트리뷰트에 인덱스 생성
  • VARCHAR 애트리뷰트에는 인덱스 생성 X
  • 작은 화일에는 인덱스 X
    • 데이터 화일에 순차적 스캔보다 성능이 개선될 때에만 인덱스 생성
  • 대량의 데이터 삽입 시 모든 인덱스를 제거한다.
    • 삽입 후 인덱스 재생성

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

  • 시스템 카탈로그가 오래 전의 데이터베이스 상태를 나타낸 경우
    • 예전 정보는 정확하지 않기에
  • 릴레이션의 크기가 작아서 인덱스가 도움되지 않는 경우
    • DBMS의 질의 최적화 모듈이 판단
  • 인덱스가 정의된 애트리뷰트에 산술연산자가 사용
  • DBMS가 제공하는 내장 함수가 사용된 경우
  • NULL값에 대해서는 주로 인덱스 사용 X
    • DBMS에 따라 NULL 관리 X 이기에

질의 튜닝을 위한 추가 지침

  • DISTINCT 절 사용 최소화
  • GROUP BY절과 HAVING절의 사용 최소화
  • 임시 릴레이션 사용 X
  • SELECT * 보다 SELECT절에 애트리뷰트 이름 구체적 명시
profile
BBaeng
이전 포스트

[데이터베이스] 5.4 논리적 설계

다음 포스트

[데이터베이스] 7장 릴레이션 정규화

0개의 댓글