[Database] 5. Physical Database Design

이번 포스팅에서는 Physical Database Design에 대해서 알아본다.

현재 단계는, DB 설계과정에서 아래 그림과 같다.

Physical Database Design의 목적은 데이터의 논리적 설명을 데이터 저장 및 검색을 위한 기술사양으로 변환하는 것이다.

 

목차

1. Physical Databse Design Process

2. Volume/Access Frequency Estimation

3. Designing Fields

4. Denormalization

5. Partitioning

6. File Organization

7. Rules for Using Indexes

 

 

또한, Physical Database Design의 목표는 적절한 성능을 제공하고 데이터 베이스 무결성, 보안 및 복구 성능을 보장하는 데이터 저장 설계를 생성하는 것이다. 

 

여기서 중요하게 알아둬야 할 점은 파일 및 데이터베이스의 구현이 아니라는 점, 프로그래머나 데이터베이스 관리자등이 구현 단계에서 사용할 기술 사양을 작성한다는 점이다. 

 

1. Physical Database Design Process

 

Input: 정규화된 Relations들, 부피 추정, 컬럼 설명, 응답 시간, 데이터 보안 요구사항, 백업/복구 요구사항, 무결성 예상, 사용되는 DBMS 기술

 

Output: 컬럼 데이터 타입, 항상 Logical Design과 일치하는 것은 아니지만, Physical Record Descriptions, 파일 조직, 인덱스와 데이터베이스 설계, 쿼리 최적화이다.

 

2. Volume/Access Frequency Estimation

 

Volume/Access Frequency Estimation 이란 크기/엑세스 빈도 측정인데, 여기서 Data Volume이란 데이터의 양을 의미하고, Access Frequencies란 접속 빈도를 의미한다. 이 단계를 진행하는 이유는 각 테이블의 Data Volume을 측정하고 각 테이블의 Access Frequencies를 측정하기 위함이다. 여기서 Access Frequencies에는 (Insert, Delete, Update Query)를 포함한다. 

 

Data Volume과 Access Frequency는 다음과 같은 특성을 가진다.

 

1. 특히 대규모 데이터베이스 구현의 경우 물리적 데이터베이스 설계 프로세스에 대한 중요한 입력사항이다.

2. 시스템 개발 프로세스의 시스템 분석 단계에서 생성된다.

3. Data Volume 통계는 사업의 규모를 나타내고, 몇년 동안의 사업 성장을 가정하여 계산된다.

4. Access Frequencies 통계는 이벤트, 트랜잭션 크기, 동시 사용자 수, 보고 및 쿼리 활동시기를 측정한다.

 

-> 여기서 중요한 건, 정확한 숫자는 필요하지 않고 중요한 건 숫자의 상대적인 크기이다. 

즉, 최고의 성능을 얻기 위해 Physical Database Design에서 어디에 집중해야 하는지 알려준다. 

 

Volume/Access Frequency Estimation Ex.

 

Volume/Access Frequency에 대해 쉬운 이해를 하기 위해 예시를 통해 알아본다. 

다음과 같은 ER Model이 있다고 하자. 여기서 Data Volume은 아래 그림과 같다.

 

 

즉, PART Entity의 전체 데이터 양이 3000인데, 제조 PART 데이터 양은 1200, 구입한 PART 데이터 양은 2100의 비율로 존재한다는 의미이다. 

 

아래 그림은 Access Frequency에 대해 알아본다.

 

전체 PART Entity로 들어오는 접속이 20000이라고 한다면, 구입한 PART 접속은 시간당 14000이다. 이때 40%, 70%로 나뉘었는데 합이 100%가 아닌 이유는 3장 포스팅 Enhanced ER Model에서 배웠듯, o표시(overlap) 는 중복을 허용하기 때문이다. 또한 구입한 PART부분에서 6000이라고 적힌 부분은 구입한 PART에 직접 엑세스한 것을 의미한다. 

 

이어서, SUPPLIES Entity에 8000이라고 적힌 엑세스는 시간당 20000의 엑세스에서 8000의 비율로 SUPPLIES Entity에 접속한 것을 의미한다. 이어 SUPPLIER Entity에는 7000의 비율로 SUPPLIER Entity에 접속한 것을 확인할 수 있다. 

 

또한, SUPPLIER Entity에 7500이 접속했고, SUPPLIES에 4000이 접속, 이후 PURCHASED PART에 4000이 접속했다. 

 

이러한 내용들을 통해 우리는 무엇을 할 수 있고 어떤 부분을 중요하게 생각해봐야 할까?

 

-> 위의 그림에서와 같이 3000개의 PART인스턴스가 있으므로, PART의 속성이 많고 일부 컬럼이 상당히 길다면 PART의 효율적인 스토리지가 중요할 수 있다. 

-> 위의 그림에서  SUPPLIER Entity에 7500이 접속했고, SUPPLIES에 4000이 접속, 이후 PURCHASED PART에 4000이 접속했다. 따라서, 이 공통 접속된 Entity들을 데이터베이스 테이블로 결합하는 것을 생각해볼 수 있다.

-> 위의 그림에서 제조 PART와 구입한 PART 사이에 오직 10%만 중복된다. 따라서, 이러한 Entity를 위한 두 개의 별도 테이블을 가지고 중복된 부품에 대한 데이터를 저장하는 것이 타당할 수 있다. 

 

3. Designing Fields

 

Field란? 프로그래밍 언어와 DBMS로 인식되는 최소 데이터 단위를 뜻한다. 필드는, 단일 구성요소를 나타낸다.

 

그렇다면 Field Design이란? 데이터유형을 선택하고, 코딩, 압축, 압호화, 데이터 무결성 제어, 누락된 데이터를 처리하는 것을 의미한다. 

 

데이터 유형을 선택하는 것은 4가지 목표를 가진다. 가능한 모든 값을 표시하고, 데이터 무결성을 향상, 모든 데이터 조작 지원 가능, 저장 공간을 최소하는 것이다. 

 

Designing Fields

 

오라클에서는 다음과 같이 데이터 타입을 선택할 수 있다.

 

Coding, Compression, Encrytion

이어 코드다.

 

코드는 공간을 절약하지만, 실제값을 얻기 위해 추가적인 조회가 필요하다.

 

Controlling Data Integrity

 

이어 데이터 무결성이다. 데이터 베이스 무결성이란 앞 포스팅들에서 여러번 설명했지만 한번 더 알아본다.

데이터 무결성이란 데이터베이스에 저장된 데이터 값과 그것이 포함하는 현실 세계의 실제 값이 일치하는 정확성을 의미한다. 

 

데이터 무결성을 유지하기 위해 4가지의 특성을 지켜야 한다.

첫번째로 기본값설정이다. 명시적 값이 없는 경우 가정된 값을 설정해야 된다는 것이다.

두번째로 범위 제어이다. 불규칙 또는 유효성 검사 규칙에 따라 허용값을 제한하는 것이다.

세번째로 Null 값 제어이다. 빈 필드 허용 또는 금지를 시키는 것이다.

네번째로 참조 무결성이다. Primary key와 Foreign key가 일치하는지, 혹은 Foreign key가 null값인지에 대한 것이다. 

 

Handling the missing data

 

누락된 데이터 처리에 대한 내용은 다음과 같이 진행한다. 평균과 같은 공식을 사용해 누락된 값의 추정치로 대체하거나, 누락된 값들을 나열하는 보고서를 구성, 프로그램에서 값이 중요하지 않으면 무시한다. 

 

4. Denormalization

 

Physical Database Design에서는 Denormalization이 중요하다. 앞 단에서 진행했던 정규화는 데이터의 중복을 최소한으로 줄이기 위해서 진행했다. 그런데, 정규화를 진행하면서 데이터를 조회하는데 사용되는 JOIN Operation이 많아지게 되는데 JOIN Operation은 리소스를 많이 잡아먹기 때문에 가장 비싼 Operation이다. 따라서, 이를 해결하기 위해 중복을 허용하고 데이터를 한테이블에 같이 넣는다면 이를 극복할 수 있고 이를 Denormalization이라고 한다. 

 

정리하자면, Denormalization은 정규화된 관계를 정규화되지 않은 물리적 기록 사양으로 변환하는 것이다. 테이블 조회수를 줄임으로써 성능향상이 가능하다(필요한 조인 쿼리수를 감소)

하지만, 데이터중복으로 인해 저장공간을 낭비할 수 있고, 데이터의 무결성/일관성을 위협할 수 있다. 

 

그렇다면, 이런 Denormalization이 가능한 경우에는 어떤 것이 있을까?

 

1. One-to-one relationship
2. Many-to-many relationship with non-key attributes (associative entity)
3. Reference data (1:N relationship where 1-side has data not used in any other
relationship)

 

예시를 통해 알아본다.

 

1. 첫번째로, One-to-one relationship이다. 

Normalization 과정에서는 Student 테이블과 Application 테이블을 나눴지만, Denormalization 과정에서는 Application에서 사용되던 Application Date, Qualifications의 null을 허용하되 한 테이블로 묶어 성능을 향상시킬 수 있다.

 

2. 두번째로 Many-to-many relationship with non-key attributes (associative entity)이다.

 

키가 없는 속성과함께 다대다관계일때인데, 그림을 통해 알아본다. 

기존 M-M에서는 하나의 테이블을 뽑는 것이 정규화 방식이였지만, 이 만들어진 테이블의 속성을 한쪽 테이블과 결합하여 성능을 향상시킬 수 있다. 이때 Description 속성은 null일 수 있다.

 

3. 세번째로, Reference data (1:N relationship where 1-side has data not used in any other
relationship)이다.

 

 

위와 같이 테이블을 결합함으로써 성능을 향상시킬 수 있다. 

 

5. Partitioning

 

Partitioning이란 테이블이 있을 때 데이터를 쪼개는 것을 의미한다. 단순하게 짜를 수도 있지만 그렇지 않을 수도 있다. 

 

Partitioning의 종류에는 3가지가 있다. Horizontal Partitioning, Vertical Partitioning, Combination of Horizontal and Vertical이다. 

 

1. Horizaontal Partitioning이란 테이블의 행을 여러개의 개별 파일로 분배한다. 여기서 파일이란 테이블로 이해해도 된다. 이는 서로 다른 사용자가 서로 다른 행에 엑세스 해야 하는 상황에 유용하다.

 

2. Vertival Partitioning이란 테이블의 열을 여러개의 테이블로 분배하는 것이다. 이때, Primary key는 모든 테이블에 포함이 되어 있어야 한다. 서로 다른 사용자가 서로 다른 열에 엑세스하는 상황에 유용하다. 

 

3. Combination Partitioning이란 Horizaontal Partitioning, Vertival Partitioning를 같이 하는 것을 의미한다.

 

-> Partition은 종종 User Schema(User view)와 일치한다.

 

Partitioning의 장점으로는 다음과 같다. 

1. 함께 사용된 Records들은 함께 그룹화 된다.

2. 성능을 위해 각 파티션을 최적화 할 수 있다.

3. 사용자와 관련이 없는 데이터가 분리된다. -> 보안성이 향상된다. 

4. 작은 파일을 사용하기 때문에 복구와 백업의 시간이 단축된다.

5. Load Balancing, 서로 다른 디스크에 저장된 파티션으로 load가 balance가 이루어진다. -> 응답시간 최적화가 가능하다.

 

Partitioning의 단점으로는 다음과 같다. 

1. 파티션간 느린 검색으로 인해 Access의 속도가 다를 수 있다.

2. 명확하지 않은 파티셔닝으로 인해 복잡도가 향상된다.

3. Vertical Partitioning에서 Primary key가 중복되기 때문에 추가공간이 필요하다. 

 

Oracle Horizontal Partitioning Methods

 

오라클에서 Horizaontal Partitioning하는 방법들에 대해 알아본다.

 

Range Partitioning, Hash Partitioning, List Partitioning, Compostie Partitioning이 있다.

 

1. Range Partitioning이란 범위에 따라서 쪼개는 것을 의미한다. ex) A~B, D~F와 같다. 

필드값의 범위에 따라 정의된 파티션인데, 어떤 특정 값에 몰릴 수 있다는 특성이 있다. 예를 들어 kim, Lee씨가 무리하게 많은데 나머지는 적을 수 있는 경우이다.

또한, 비슷한 값들이 공유될 수 잇다. 

 

2. Hash Partitioning이란 해쉬 알고리즘에 따라서 나뉘는 것이다.

해쉬함수에 의해 파티션이 정해지는 것인데, record가 balance하게 집어 넣어진다.

파티션에는 매우 다양한 값의 필드가 포함될 수 있다. 이는, 장치간에 데이터를 균등하게 배포하는 것이 목표일 경우 잘 작동한다.

 

3. List Partitioning이란 미리 정해진 값에 기반으로 파티셔닝을 하는 것이다.

 

4. Composite Partitioning이란 위의 여러가지 파티셔닝 기법들을 섞는 것이다.

 

위 그림은 파티셔닝의 예시이다.

 

6. File Organizations

 

File Organization이란 파일이 디스크에 저장되어 있을 때 레코드를 파일에 배치하는 방법이다, 데이터 베이스 테이블에 데이터를 저장하는 방식이라고 이해하면 된다.

 

즉, 보조 저장소에 있는 파일의 레코드를 물리적으로 정렬하는 기술이다.

 

File Organization의 선택 요인은 다음과 같다.

 

1. 빠른 데이터 검색 및 처리량의 목적인지?

2. 데이터공간을 효율적으로 활용하는게 중요한 것인지?

3. 장애 및 데이터 손실로부터 보호하는 것이 중요한 것인지?

4. 데이터를 재정렬하는 것을 최소화 하는 것이 중요한지?(Ex. Insertion 했을 때 데이터가 밀리는경우?)

5. 데이터가 양이 늘어나는 것이 잘 이루어져야 하는 것이 중요한지?

6. 보안성이 중요한지?

 

File Organization의 종류에는 3가지가 있다.

 

1. Sequential

2. Indexed

3. Hashed 

 

Sequential File Organization

순차적으로 데이터를 저장한다는 이야기이다.

만약 위의 그림에서 정렬되어 있을 때 파일의 레코드는 기본 키 필드값 순서대로 저장된다. Flyers가 삽입, 삭제라면 나머지 데이터들은 재정렬이 필요하다, 즉 모든 삽입, 삭제시에 정렬되어 있다면 재정렬이 필요하고, 정렬되어 있지 않을 때 원하는 레코드를 찾기 위한 평균 시간은 n/2이다. 최악의 경우 n번, 최선의 경우 1번이다. 

 

-> 유연성이 떨어지기 때문에 데이터베이스에서 사용되지 않지만 데이터베이스에서 데이터를 백업하는 파일에 더 많이 사용된다. 효율성이 좋지 않다. 

 

Indexed File Organization

소프트웨어가 개별 레코드를 찾을 수 있는 인덱스를 사용하여 순차적 혹은 비순차적으로 레코드를 저장하는 방식이다.

인덱스란, 파일에서 어떤 조건을 만족시키는 인덱스의 위치를 결정하는 데 사용되는 테이블을 뜻한다. 

Primary key가 자동으로 색인화된다.

다른 필드 또는 필드 조합도 인덱싱 할 수 있어서, 이를 보조키 인덱스라고 한다. 

 

원하는 값을 찾기 위한 평균시간은 트리구조이기 때문에 트리의 깊이만큼, 시간 복잡도 O(logn)이다. 최악의 경우 O(n)이다. 

 

Hashed File Organization

해쉬된 파일 구성에서, 해쉬 알고리즘을 사용하여 각 레코드의 구조를 결정하는 것이다. 

Hash Algorithm이란 Primary key값을 레코드 주소로 변환해주는 루틴이다. 대부분의 경우에서 레코드들은 해쉬 알고리즘에 의해 지시된 대로 sequential하지 않는다.

 

일반적으로 기록 위치를 결정하기 위해 분할 제거기를 사용하낟. 같은 위치에 있는 레코드는 기본키 필드 값에 따라 순서대로 목록으로 그룹화 된다.

 

FIle Organization을 표로 정리하자면 다음과 같다.

 

 

7. Rules for Using Indexes

인덱스를 고려할 때 어떤 것에 중점을 맞춰야 하는지에 대해 알아본다.

 

1. 큰 테이블에 사용한다.

2. 각각 테이블의 Primary key에 사용한다.

3. where 절에 자주 있는 필드를 인덱스로 사용한다.

4. ORDER BY, GROUP BY에서 참조하는 필드에 대해 사용한다.

5. 데이터의 양이 100이상일 때 사용하고 30미만일 땐 사용하지 않는 것이 좋다.

6. 값이 긴 필드에는 인덱스를 사용하지말고, 값을 압축해서 사용한다.

7. DBMS는 테이블당 인덱스 수와 바이트 수에 제한이 있을 수 있다.

8. 많은 DBMS가 인덱스 검색에서 NULL값을 인식하지 못한다. 따라서 이를 주의하도록 한다. 

 

Physical Database Design에 대해서 알아보았다. 데이터의 양이 많아진다면 성능을 고려하기 위해 이를 처리하는 과정들이 중요하고, 기존의 작은 규모의 사이드 프로젝트들을 진행해보면서는 접해보기 힘든 내용이라는 생각이 들었다. 하지만, 나중에 실무에 들어갈 때 Physical Database Design에 대한 내용들을 다시 한 번 접할 기회가 올 것 같다!