(정보처리기사 실기 정리) 2-02. 물리 데이터 설계

1. 반정규화 개념

• 반정규화 정의
  : 정규화에 충실하여 모델링을 수행하면 종속성과 활용성은 향상되나, 수행속도가 증가하는 경우가 발생하기 때문에 기를 극복하기 위해 성능에 중점을 두어 정규화 하는 방법
  
  
• 특징
  : 데이터 모델링 규칙에 얽매이지 않고 수행 / 시스템이 물리적으로 구현되었을 떄 성능 향상이 목적
  
  
• 사용시기
  - 정규화에 충실하였으나 수행속도에 문제 발생
  - 다량의 범위를 자주 처리
  - 특정 범위의 데이터만 자주 처리
  - 처리 범위를 줄이지 않고는 수행속도를 개선할 수 없는 경우
  - 요약 자료만 주로 요구되는 경우
  - 추가된 테이블의 처리를 위한 오버헤드를 고려
  - 인덱스의 조정이나 부분 범위 처리로 유도하고, 클러스터링을 이용하여 해결할 수 있는지 검토 후 결정
  
  

2. 반정규화 유형

• 용도

유형	기법	용도
테이블 반정규화	테이블 병합	부분 처리가 두 개 이상의 테이블에 대해 항상 같이 일어나는 경우
	테이블 분할	칼럼의 사용빈도 차리가 많은 경우 각각의 사용자가 각기 특정한 부분만 지속적으로 사용하는 경우
	테이블 추가	다량의 범위를 자주 처리하는 경우 특정 범위의 데이터만 자주 처리되는 경우 처리 범위를 줄이지 않고는 수행 속도를 개선할 수 없는경우
	테이블 제거	테이블 재정의나 칼럼의 중복화로 더 이상 액세스 되지 않는 테이블이 발생할 경우
Column 반정규화	중복 컬럼 추가	자주 사용되는 칼럼이 다른 테이블에 분산되어 있어 상세한 조건에도 불구하고 액세스 범위를 줄이지 못하는 경우
	파생 컬럼 추가	대량 데이터에서 Row별 연산 결과를 얻고자 할 때 성능 향상을 위한 파생칼럼을 추가할 경우
	이력 컬럼 추가	대량 데이터 처리 시 불특정 일자 조회나 최근 값을 조회할 때 나타날 수 있는 성능 저하 예방
	PK에 의한 컬럼 추가	기본키의 형태가 적절하지 않거나 너무 많은 칼럼으로 구성된 경우

• 방법

유형	기법	방법
테이블 반정규화	테이블 병합	해당 테이블을 통합하여 설계
	테이블 분할	수직 분할 : 칼럼별 사용빈도에 다라 분할 수평 분할 : 특정 범위별 사용 빈도의 차이가 많은 경우 해당 범위 별로                테이블을 분할
	테이블 추가	집계 테이블 추가 : 활용하고자 하는 집계 정보를 위한 테이블 추가 진행 테이블 추가 특정 부분만을 포함하는 테이블 추가
	테이블 제거	해당 테이블 삭제
Column 반정규화	중복 컬럼 추가	Join 성능 향상을 위해 중복 컬럼 추가
	파생 컬럼 추가	계산된 값을 저장하는 파생 컬럼 추가
	이력 컬럼 추가	최근 값, 시작 일자 등의 컬럼 추가
	PK에 의한 컬럼 추가	복합PK의 일부를 일반 속성으로 추가

 

 

3. 물리적 모델링 수행 순서

• 단위 엔티티를 테이블로 변환
  : 논리 모델에서 정의된 엔티티 -> 물리모델 테이블 

논리적 설계	물리적 설계	데이터베이스
엔티티 (Entity)	테이블 (Table)	Table
속성 (Attribute)	컬럼 (Column)	Column
주 식별자	Primary Key	Primary Key
외래 식별자	Foreign Key	Foreign Key

• 속성을 칼럼으로 변환
  : 논리 모델에서 정의된 속성 -> 물리모델 칼럼

Entity	변환 ->	Table
속성 (Attribute)		컬럼 (Column)
Primary UID		Primary Key
Secondary(Alternate) UID		Unique Key
String		Varchar
Integer		Number

• 주 식별자를 기본키로 변환
  : 논리 모델에서 정의된 주 식별자 -> 물리모델 기본키
   
  - 주 식별자에 해당하는 모든 속성 : 기본키로 선언
  - Not Null, Unique 등의 제약조건 추가 정의
  - 관계에 의한 외래키가 기본키에 포함될 수 있음
  
  
• 관계를 외래키로 변환
  : 논리모델에서 정의된 관계 -> 외래키로 변환
  
  - 1:M -> Primary Key : Foreign Key 로 변환
  - N:M -> 중간 테이블을 만들어서 N:1, 1:N 관계로 변환
  
  
• 칼럼 유형과 길이를 정의
  : 정의된 각 칼럼에 대해 적용 DMBS에서 제공하는 데이터 유형 중 적절한 유형을 정의하고 해당 데이터의 최대 길이를 파악하여 길이를 설정
  
  
• 반정규화
  - 중복 테이블 추가 : 집계 테이블 추가, 진행 테이블 추가, 특정 부분만을 포함하는 테이블 추가
  - 테이블 조합 : 1:1 관계의 테이블 조합, 1:M 관계의 테이블 좋바, 슈퍼타입 서브타입 테이블 조합
  - 테이블 분할 : 수직 분할, 수평 분할 
  - 테이블 제거 : 테이블 재정의 또는 칼럼의 중복화로 더 이상 액세스 되지 않는 테이블이 발생할 경우 제거
  - 칼럼의 중복화 : 일반적으로 조인 프로세스를 줄이기 위해 칼럼의 중복을 허용

4. 테이블 제약 조건 

• Delete Constraint
  - Cascade : 참조한 테이블에 있는 외부키와 일치하는 모든 Row 삭제
  - Restricted : 참조한 테이블에 있는 외부키에 없는 것만 삭제 가능
  - Nullify : 참조한 테이블에 정의 된 외부키와 일치하는 것을 Null로 수정
  
  
• Update Constraint
  - Cascade : 참조한 테이블에 있는 외부키와 일치하는 모든 Row 수정 
  - Restricted : 참조한 테이블에 있는 외부키에 없는 것만 수정 가능
  - Nullify : 참조한 테이블에 정의된 외부키와 일치하는 것은 Null로 수정

5. 인덱스 설계

• 인덱스 적용 기준
  - 인덱스 칼럼의 분포도가 10 ~ 15 % 인 경우
  - 분포도가 범위 이상이더라도 부분처리를 목적으로 하는 경우
  - 입출력 장표 등에서 조회 및 출력 조건으로 사용되는 칼럼인 경우
  
  
• 인덱스 컬럼 선정
  - 분포도가 좋은 칼럼은 단독적으로 생성하여 활용도를 향상시킨다.
  - 자주 조합되어 사용되는 칼럼은 결합 인덱스로 생성하여 활용
  - 결합 인덱스는 구성되는 칼럼순서 선정에 유의
  - 가능 한 한 수정이 빈번하지 않은 칼럼을 선정한다.
  
  
• 설계 시 고려사항
  - 새로 추가되는 인덱스가 기존 액세스 경로에 영향을 미칠 수 있음에 유의한다.
  - 지나치게 많은 인덱스는 오버헤드로 작용한다.
  - 인덱스는 추가적인 저장공간이 필요함을 고려해야 한다.
  - 넓은 범위를 인덱스 처리 시 오히려 전체 처리보다 많은 오버헤드를 발생시킬 수 있다.
  - 인덱스와 테이블 데이터의 저장 공간을 적절히 분리 될 수 있도록 설계해야 한다.
  
  

6. 뷰 설계

• 뷰 속성
  - REPLACE : 뷰가 이미 존재하는 경우 재생성
  - FORCE : 기본 테이블의 존재 여부에 관계없이 뷰 생성
  - NOFORCE : 본 테이블이 존재할 때에만 뷰 생성 
  - WITH CHECK OPTION : Sub Query 내의 조건을 만족하는 행만 변경
  - WITH READ ONLY : DML 작업 불가
  
  
• 뷰 설계 시 고려사항
  - 최종적으로는 테이블을 액세스 하는 것이므로 사용에 따라 수행속도에 문제가 발생할 수 있음.
  - 뷰 내의 SELECT문의 조건은 가능한 한 최적의 액세스 경로를 사용할 수 있도록 해야함
  - 위 조건을 만족할 수 없다면, 뷰를 사용한 SQL의 WHERE절에서는 반드시 양호한 액세스 경로가 되도록 해야함 

7. 클러스터 설계

• 적용 기준
  - 분포도가 넓을수록 오히려 유리한 기법
  - 액세스 효율 향상을 위한 물리적 저장 방법
  - 분포도가 넓은 테이블의 클러스터링은 저장공간의 절약 가능
  - 다중 블록 이상의 테이블에 적용 (일반적으로 6 블록)
  - 대량의 범위를 자주 액세스 하는경우 적용
  - 인덱스를 사용한 처리 부담이 되는 넓은 분포도에 활용
  - 여러 개의 테이블이 번번히 조인을 일으킬 떄 활용
  - 반복 칼럼이 정규화에 의해 어쩔 수 없이 분할된 경우 활용
  
  
• 클러스터 설계 시 고려사항
  - 검색 효율은 높여줌 / 입력, 수정, 삭제 시에는 부하가 증가
  - Union, Distinct, Order by, Group by가 빈번한 칼럼이면 고려해야 함
  - 수정이 자주 발생하지 않은 칼럼은 고려 대상
  - 처리 범위가 넓어 문제가 발생하는경우 : 단일 테이블 클러스터링 
  - 조인이 많아 문제가 발생되는 경우 : 다중 테이블 클러스터링

8. 파티션 설계

• 파티션의 종류
  - 범위분할(Range Partitioning) : 지정한 열의 값을 기준으로 분할
  - 해시분할(Hash Partitioning) : 해시 함수에 따라 데이터를 분할
  - 조합 분할(Composite Partitioning) : 범위 분할에 의해 데이터를 분할한 다음 해시 함수를 적용하여 다시 분할
  
  
• 장점
  - 데이터 액세스 범위를 줄여 성능 향상
  - 전체 데이터의 훼손 가능성 감소 및 데이터 가용성 향상
  - 각 분할 영역을 독립적으로 백업 및 복구 가능
  - Disk Striping으로 I/O성능 향상
  
  
• 파티셔닝 순서
  파티션의 종류와 결정 -> 파티션 키의 선정 -> 파티션 수 결정
  
  

9. 디스크 구성 설계 

• 구성 설계 방법
  - 정확한 용량을 산정하여 디스크 사용의 효율을 높임
  - 업무량이 집중되어 있는 디스크를 분리하여 설계함으로써 집중화 된 디크에 대한 입출력 부하를 분산한다.
  - 입출력 경함을 최소화하여 데이터의 접근 성능을 향상시킨다.
  - 테이블 객체를 위한 테이블 스페이스와 인덱스 객체를 위한 테이블 스페이스를 분리 구성한다.
  - 테이블 스페이스와 템포러리 스페이스를 분리 구성한다.
  - 테이블을 마스터 테이블과 트랜잭션 테이블로 분류한다.
  - 시스템의 구성에 따라 테이블 스페이스의 개수와 사이즈 등을 결정한다.
  - 파티션 할 테이블은 별도로 분류한다.
  
  출처 : 이기적 정보처리기사 실기 핵심이론 1권