정보처리기사 31일차

3. 데이터베이스

 3.4 물리 데이터베이스 설계

  3.4.3 데이터베이스 물리속성 설계

  3.4.4 데이터베이스 모델링

  3.4.5 데이터베이스 반정규화

 

1. 파티셔닝

 

1.1 파티셔닝의 정의

큰 table  이나 인덱스를 관리하기 쉬운 단위로 분리하는 방법이다.

 

1.2 파티셔닝의 장점

가용성 : 물리적인 patitioning으로 인해 전체 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상

관리용이성 : 큰 table들을 제거하여 관리를 쉽게 함

성능 : 특정DML과 Query의 성능을 향상시킴, 주로 대용량 Data Write 환경에서 효율적이며 많은 insert가 있는 OLTP 시스템에서 Insert 작업들을 분리된 파티션들로 분산시켜 경합을 줄임

 

1.3 파티셔닝의 단점

Table 간의 join에 대한 비용이 증가한다.

테이블과 인덱스를 별도로 파티션 할 수는 없다. 테이블과 인덱스를 같이 파티셔닝 하여야 한다.

 

1.4 파티셔닝의 범위

 

1. Range partitioning

연속적인 숫자나 날짜 기준으로 파티셔닝 한다

손쉬운 관리 기법 제공에 따른 관리 시간을 단축한다

 

2. List partitioning

특정 파티션에 저장될 Data에 대한 명시적 제어 가능하다.

분포도가 비슷하며, 많은 SQL에서 해당 Column의 조건이 많이 들어오는 경우 유용하다.

Muti-Column partition key 제공하기 힘들다.

 

3. Composite partitioning

파티션의 sub-partitioning을 말한다.

큰 파티션에 대한 I/O 요청을 여러 파티션으로 분산한다.

Range partitioning 할 수 있는 Column이 있지만, Partitioning 결과 생성된 파티션이 너무 커서 효과적으로 관리할 수 없을때 유용하다.

 

4. Hash partitioning

파티션 키의 해시 값에 의한 partitioning

select시 조건과 무관하게 병령 Degree 제공

특정 데이터가 어느 해시 파티션에 있는지 판단 불가

Hash Partition은 파티션을 위한 범위가 없는 데이터에 적합

 

1.5 파티셔닝 방법

 

1.Horizontal Partitioning

장점

데이터의 개수를 기준으로 나누어 파티셔닝 한다

데이터의 개수가 작아지고 따라서 인덱스의 개수도 작아지게 된다. 자연스럽게 성능은 향상된다

 

단점

서버간의 연결과정이 많아진다

데이터를 찾는 과정이 기존보다 복잡하기 때문에 latency가 증가하게 된다

하나의 서버가 고장나게 되면 데이터의 무결성이 깨질 수 있다.

 

2. Vertical Patitinoning

테이블의 컬럼을 기준으로 나누어 파티셔닝 한다.

정규화하는 과정도 이와 비슷하다고 볼 수 있지만 Vertical Patitioning은 이미 정규화된 Data를 분리하는 방법이다.

자주 사용하는 컬럼등을 분리시켜 성능을 향상시킬 수 있다.

 

2. 클러스터링

 

2.1 클러스터 정의

어떤 정해진 컬럼 값을 기준으로 동일한 값을 가진 하나 이상의 테이블의 로우를 같은 장소에 저장하는 물리적인 기법

디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용하는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법

 

2.2 클러스터의 장점

그룹된 컬럼 데이터 행들이 같은 데이터 Block에 저장되기 때문에 디스크 I/O를 줄여 준다. 

클러스터된 테이블 사이에 조인이 발생할 경우 그 처리 시간이 단축된다.

클러스터키 열을 공유하여 한번만 저장하므로 저장 영역의 사용을 줄인다.

 

2.3 테이블 클러스터링의 특징

클러스터는 데이터 조회 성능을 향상시키지만 데이터 저장, 수정, 삭제 또는 한 테이블 전체 Scan의 성능을 감소시킨다.

 

1. 클러스터 하기 좋은 테이블

주로 조회가 자주 발생하고 수정이 거의 발생하지 않는 테이블

컬럼안의 많은 중복 데이터를 가지는 테이블

자주 join되는 테이블

 

2. 클러스터 key가 되기 좋은 컬럼

데이터 값의 범위가 큰 컬럼

테이블 간의 조인에 사용되는 컬럼

 

3. 클러스터 key가 되기 나쁜 컬럼

특정 데이터 값이 적은 컬럼

자주 데이터 수정이 발생하는 컬럼

LONG,LONG RAW 컬럼은 포함할 수 없다.

 

2.4 단일테이블 클러스터링

하나의 클러스터에 하나의 테이블만 생성시킨다.

 

2.5 다중테이블 클러스터링

하나의 단위 클러스터에 여러 개의 테이블을 생성한다.

기준되는 컬럼의 값이 동일한 각각의 테이블의 로우들이 하나의 단위클러스터에 저장되므로 기준 컬럼값으로 관련 테이블을 조인할 경우 추가적인 각 테이블들의 로우들이 흩어져 저장되는 경우 발생하는 추가적인 액세스가 불필요하여 높은 성능의 조인이 가능하다.

 

3. 데이터베이스 백업

 

3.1 데이터베이스 백업의 정의

DB의 고장, 데이터의 손실 등으로 인한 피해를 최소화하기 위하여 현재의 데이터를 저장하는 활동

 

3.2 백업의 종류

 

1. 백업을 받는 대상에 따라서 분류

시스템 백업 : 운영체제와 같은 시스템 백업을 말한다.

데이터 백업 : 데이터베이스 백업과 같은 파일 시스템 백업을 말한다.

 

2. 구성 방식에 따른 분류

직접연결 백업 : 서버와 백업장비를 일대일 관계로 직접연결하여 백업

네트워크 백업 : LAN을 통해서 백업장비와 서버를 접속하는 개념

SAN 백업 : 백업받을 데이터 용량이 증가함에 따라 백업용 테이프 장치를 SAN을 통해 인식시켜서 FC를 통해서 백업장치로 직접 백업받는 방식

 

3. 백업 방식에 따른 분류

핫백업 : DB서버를 온라인 상태로 유지한 채 데이터 백업

콜드백업 : DB서버를 중지한 후 데이터 백업

물리백업 : 파일 자체를 그대로 백업

논리백업 : 각 오브젝트를 SQL문 등으로 저장

 

3.3 데이터 백업시 대상 파일

 

Data file : 데이터를 저장한 파일

Control file : 데이터베이스 구조나 정보에 대한 상태를 저장한 바이너리 파일

Redo log file : 데이터 변경 처리사항을 저장한 파일

 

여기서 Redo log file은 데이터베이스 백업시 사용되는 파일이다.

핫백업이 진행되면 수많은 Redo log가 작성되어 디스크 용량을 차지하지만 이 파일을 사용하여 백업 이후 발생한 데이터의 변경 사항을 유연하게 처리할 수 있게 된다. 반대로 콜드 백업시에는 Redo log가 작성되지 않는다.

 

3.4 백업방법

 

1. phpMyAdmin

관리 패널에서 버튼 클릭만으로 콜드 백업 가능한 툴(Mysql)

 

4. 테이블 저장 사이징

데이터 예상치, 보관주기 및 각 컬럼사이즈 및 테이블 스토리지 옵션 등에 따라서 테이블의 크기를 예측해야 스토리지 용량산정이 가능하다.

 

4.1 사전 정보

테이블 크기 산정에 필요한 사전 정보로 Row의 데이터 길이와 항목별 크기 정보가 필요하다.

 

1. Row의 데이터 길이

테이블 크기를 산정하기 위해서는 컬럼의 데이터 타입별 실제 소비 크기와 저장되는 길이를 산정해야 한다. 이 정보는 테이블 크기를 산정하기 위한 항목별 크기 산정방법에 사용된다.

크기산정 테이블은 5개의 컬럼으로 구성되었다. 데이터 타입별 크게 산정은 DB의 종류에 따라 다를 수 있으므로 가정치로 수치를 조정한다.

 

4.2 테이블 크기 산정방법

한 Row 저장에 필요한 공간, 한블록의 데이터 공간, 한블록에 들어갈 수 있는 Row 개수, 총 데이터 Row 개수를 차례로 구하면서 필요한 총 데이터 공간을 산정한다.

 

5. 데이터 지역화

데이터베이스의 저장 데이터를 효율적으로 이용할 수 있도록 저장하는 방법이다.

물리적 데이터베이스의 설계에서 데이터 지역화를 고려한 보조 기억 장치의 설계는 중요하다

물리적 데이터베이스 설계, 보조 기억 장치의 역할, 디스크상의 파일의 배치를 지역화 관점에서 검토한다.

 

5.1 물리적 데이터베이스 설계

논리적인 설계에 데이터 구조를 보조 기억 장치상의 파일로 사상한다.

하나의 파일에 여러 릴레이션이 저장될 수 있다.

예상 빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석한다.

데이터에 대한 효율적인 접근을 제공하기 위하여 저장 구조와 접근 방법들을 다룬다.

질의를 효율적으로 지원하기 위해서 인덱스 구조를 적절히 사용한다.

 

5.2 보조 기억 장치의 역할

각 파일은 고정된 크기의 블록들로 나누어져서 저장된다. 즉 ,여러 개의 블록들에 파일의 데이터가 분산되어 저장된다.

디스크는 데이터베이스를 장기간 보관하는 주된 보조 기억 장치이다.

 

-물리 데이터베이스 모델링

 

1. 물리 E-R 다이어그램

 

1.1 논리 데이터모델의 물리 데이터 모델로 변환

엔티티는 테이블, 속성은 컬럼 등으로 변환한다.

 

1.2 슈퍼 타입 기존 엔티티 통합

슈퍼 타입 엔티티를 중심으로 서브 타입 엔티티와 단일 테이블로 통합한다.

 

1.3 서브 타입 기준 엔티티 통합

슈퍼 타입 속성들을 각각의 서브 타입에 추가하여 서브 타입마다 하나의 테이블로 변환한다.

 

1.4 개별 타입 기준 변환

 

1.슈퍼타입과 서브타입을 각각 테이블로 변환한다.

슈퍼타입과 서브 타입 각각의 테이블 사이에는 1대1 관계가 형성된다.

 

2. 개별 타입 기준 테이블 변환을 사용하는 경우

전체 데이터에 대한 처리가 자주 발생하는 경우 사용한다.

서브 타입 처리가 대부분 독립적으로 발생하는 경우 사용한다.

통합하는 테이블의 컬럼 수가 지나치게 많은 경우 사용한다.

서브 타입 컬럼 수가 다수인 경우 사용한다.

 

1.5 속성 및 컬럼 변환

 

1. 일반 속성 변환

 

2. Primary UID 기본 키 변환

논리 모델링에서의 Primary UID는 물리 모델링에서 기본 키로 생성된다 DDL에서는 오브젝트가생성되어 기본 키 제약 조건의 속성을 갖는다.

 

3. Primary UID 기본 키 변환

Primary UID의 속성 중에는 해당 엔티티에서 생성된 속성도 존재하지만 다른 엔티티와의 관계로 인해 생성되는 UID 속성도 존재할 수 있다. 이러한 관계 속성 UID의 변환은 일반적인 속성 UID 변환과는 차이가 있따.

 

4. Secondary UID 유니크 키 변환

논리 모델링에서 정의된 Secondary UID 및 Alternate key들은 해당 엔티티와 상대 앤티티의 선택적 관계를 갖는데 중요한 역할을 한다. 이러한 Secondary UID 들은 물리 모델에서는 유니크 키로 생성된다.

 

1.6 관계 변환

 

1. 1대M 변환

논리 데이터 모델의 관계에서 가장 보편적 형태의 관계이며 M측의 관계 컬럼에 선택사항이 결정되게 된다.

 

2. 변환

논리 데이터 모델에서 1대1 관계는 일반적이지 않은 형태의 관계로 1대1 관계를 물리 모델링에서 변환하는 과정은 관계의 기수성으로 인해 다른 방법이 적용된다.

 

3. 1대M 순환 관계 변환

일반적으로 데이터 계층 구조의 표현에 주로 사용하는 관계의 형태로 특성상 최상위관계속성은 항상 Optional의 형태를 보여야 한다. 그러나 경우에 따라 최상위 관계에 특정 값을 설정하는 경우도 있다.

 

4. 배타적 관꼐 변환

실제 데이터 환경에서는 빈번하게 등장하는 논리 데이터 모델의 배타적 관계 모델은 물리 모델링에서 생성하는 방법이 일반적인 관계 변환 방법과는 차이가 있다.

 

외래키 분리 

관계별로 관계 컬럼을 생성하는 방법으로 실제 외래 키의 제약 조건이 생성 가능하다는 장점이 있으나 키 컬럼들이 각각 Optional 의 형태여야 한다 또한 체크 제약 조건의 추가적 생성이 필요하다.

 

외래키 결합

관계들을 하나의 관계 컬럼으로 통합 생성하는 방법으로 실제 외래 키 제약 조건의 생성은 불가능 하고 다수의 관계를 선별하여 구분하기 위한별도의 컬럼이 필요하다.

 

1.7 관리 목적의 컬럼의 추가

논리모델링에서는 필요가 없으나 관리 또는 데이터베이스를 이용하는 프로그래밍의 수행 속도를 향상을 위해 추가되는 테이블인 ㅏ컬럼으로 관리상 필요한 데이터를 등록한 일자, 시스템 번호 등을 추가한다.

 

1.8 데이터 타입 선택

데이터 형식을 설정하는 것은 논리 모델링에서 정의된 논리적인 데이터 타입을 물리적인 DBMS 특성과 성능의 고려를 통해 최적 데이터 타입을 선택하는 작업이다.

주요 타입은 문자타입, 숫자타입, 날짜타입으로 구분할 수 있다

 

1.9 데이터 표준화

명명규칙 및 표준 용어 사전을 활용하여 각 객체의 데이터 표준화를 수행한다.

표준화 적용 대상은 데이터베이스, 스토리지 그룹, 테이블 스페이스, 테이블, 컬럼, 인덱스, 뷰이다.

 

2. 컬럼 속성

 

2.1 컬럼 속성 정의

정보를 나타내는 최소의 단위로써 앤티티의 성질, 분류, 수량, 상태, 특성 등을 나타내는 세부항목을 말한다.

 

2.2 속성의 유형

기본속성 : 해당 엔티티가 원래 가지고 있는 속성

실제속성 : 원래 업무에는 조냊하지 않지만시스템의 효율성을 위해 임의로 추가되는 속성

파생속성 : 다른 속성으로부터 계산이나 변형되어 생성되는 속성

               데이터 중복성 및 무결성 확보를 위해 가급적 적게 정의

 

2.3 속성 정의시 고려 사항

 

1. 앤티티가 관리할 특성들인가?

주문자 성명이란 속성은 주문 앤티티에 별도로 있어야 할까?

고객 엔티티와 관계만을 맺으면 될까를 확인해야 한다.

 

2. 의미적으로 독립된 최소단위인가?

속성은 멀티 파트가 되어서는 안된다. 멀티 파트라는 의미는 속성이 여러 개의 부분들로 이루어져 있는 경우와 하나의 의미를 가지는 여러 개의 속성이 존재하는 경우이다.

 

3. 하나의 값만을 가지고 있는가?

속성의 111워칙 : 엔티티 타입내에서 하나의 속성은 한시점에 한 개의 값만을 가져야 한다. 하나의 속성이 시간에 따라 여러 개의 값을 가지면 그 값을 해당업무에서 관리해야 할 필요가 있으면 새로운 엔티티를 생성한다.

 

- 데이터베이스 반정규화

 

1. 반정규화

 

1.1 반정규화의 정의

정규화된 엔티티, 속성, 관계를 시스템의 성능 향상, 개발과 운영일 단순화하기 위해 데이터 모델을 통합하는 프로세스 이다.

즉 데이터의 정합성과 무결성을 우선으로 할지 데이터베이스 구성의 단순화와 성능을 우선으로 할지를 결정한다.

 

1.2 반정규화 절차

 

1. 반정규화 대상 조사

 

범위 처리 빈도수 조사 : 자주 사용되는 테이블에 접근하는 프로세스 수가 많고, 항상 일정한 범위만을 조회하는 경우

대량의 범위 처리 조사 : 대량이 데이터 범위를 자주 처리하는 경우

통계성 프로세스 조사 : 별도의 통계 테이블 고려

테이블 조인 개수 : 지나치게 많은 조인이 걸려 데이터 조회 작업이 어려운 경우

 

2. 다른 방법 유도 검토

뷰테이블 : 지나치겜 낳은 조인이 걸려 데이터 조회 작업이 어려운 경우

클러스터링 또는 인덱스 적용 : 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리할 수 있다.

어플리케이션 : 로직을 변경함으로써 성능을 향상시킬 수 있다.

 

3. 반정규화 적용

테이블 반정규화

속성의 반정규화

관계의 반정규화

 

1.3 반정규화 방법

반정규화 방법은 반정규화 대상에 따라 테이블 반정규화, 컬럼 반정규화, 관계 반정규화로 나눈다.

 

1. 테이블 반정규화

테이블 반정규화 방법은 테이블 병합과 분할의 방법이 있다.

 

테이블 병합

조인되는 경우가 많아서 테이블을 합치는 것이 성능에 유리할 경우 병합

 

테이블 분할

테이블에서 특정 속성들만 집중적으로 접근할 경우 테이블을 분할

 

테이블 추가

중복테이블 추가 : 다른업무이거나 서버가 다른 경우 동일한 테이블 구조를 중복하여 우너격조인을 제거하여 성능 향상

통계테이블 추가 : SUM, AVG등을 미리 수행하여 자동계산해둠으로써 조회시 성능 향상

이력테이블 추가 : 마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재하는 방법

부분테이블 추가 : 하나의 테이블의 전체 컬럼 중 자주 이용하는 집중화된 컬럼들이 있을때  디스크의 I/O를 줄이기 위해 해당 컬럼 들을 모아 놓은 별도의 반정규화 된 테이블

 

2. 컬럼 반정규화

 

중복컬럼 추가

조인에 의한 성능저하를 예방하기 위해 즉 조인을 감소시키기위해 중복된 컬럼을 추가한다.

 

파생컬럼 추가

트랜잭션이 처리되는 시점에 계싼에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산하여 컬럼에 보관

 

이력 테이블 컬럼 추가

대량의 데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능저하를 예방하기위해 이력테이블에 가능성 컬럼을 추가함

 

PK에 의한 컬럼 추가

복합의미를 갖는 PK을 단일 속성으로 구성하였을 경우 발생됨 단일 PK안에서 특정 값을 별도로 조회하는 경우 성능저하가 발생될 수 있음. 이때 이미 PK안에 데이터가 존재하지만 성능향상을 위해 일반속성으로 포함하는 방법이 PK에 의한 컬럼추가이다.

 

응용시스템 오동작을 위한 컬럼 추가

업무적으로는 의미가 없지만 사용자가 데이터를 처리하다가 잘못 처리하여 원래 값으로 복구하기 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 기법

 

3. 관계 반정규화

데이터를 처리하기 위한 여러 경로를 거쳐 조인이 가능하지만 이 때 발생할 수 있는 성능 저하를 예방하기 위해 중복 관계를 추가하는 방법

테이블과 컬럼의 반정규화는 데이터 무결성에 영향을 미치게 되나 관계의 반정규화는 데이터 무결성을 깨뜨릴 위험을 갖지 않고서도 데이터처리의 성능을 향상시킬 수 있음