물리 데이터 모델은 논리적 모델을 특정 데이터베이스로 설계하여 생성되는 데이터를 저장할 수 있는 물리적 스키마이다.
물리 데이터 모델링 변환 절차
- 개체를 테이블로 변환
- 테이블과 개체 명칭을 같게 하는 것이 권장됨
- 테이블명은 코드의 가독성을 위해 영문명을 사용
- 속성을 컬럼으로 변환
- 개발자와 사용자 간 의사소통을 위해 표준화된 약어 사용 권장
- SQL 예약어 사용을 피해야 함
- UID를 기본키로 변환
- 개체의 UID에 해당하는 모든 속성에 대해 기본키로 선언
- Not Null, Unique 등의 제약조건을 추가로 정의
- 관계를 외래키로 변환
- 외래키명은 기본키 이름을 그대로 사용하나 다른 의미를 가질 경우 변경 가능
- 순환 관계에서 자신의 기본키는 외래키로 정의
- 컬럼 유형과 길이 정의
- 적절한 유형을 선택하고, 데이터의 최대 길이를 파악하여 길이 설정
- CHAR: 최대 2000바이트의 고정 길이 문자열 저장 가능
- VARCHAR2: 최대 4000바이트의 가변 길이 문자열 저장 가능
- NUMBER: 38 자릿수의 숫자 저장 가능
- DATE: 날짜 값을 저장
- BLOB, CLOG: 바이너리(Binary), 텍스트 데이터 최대 4GB까지 저장
- 반 정규화 수행
- 시스템 성능 향상과 개발 및 운영의 단순화를 위해 데이터 모델을 통합하는 반 정규화 수행
- 중복 테이블 추가
- 집계 테이블 추가
- 특정 부분만 포함하는 테이블 추가
- 테이블 조합
- 1:1 관계 테이블 조합
- 1:M 관계 테이블 조합
- 슈퍼타입 / 서브타입 테이블 조합
- 테이블 분할
- 테이블 제거
- 컬럼 중복화
테이블 제약조건
참조 무결성 제약조건
- 릴레이션 간 참조의 일관성을 보장하기 위한 조건
- 두 개의 릴레이션이 기본키, 외래키를 통해 참조 관계를 형성할 경우, 참조하는 외래키의 값은 항상 참조되는 릴레이션에 기본키로 존재해야 함
참조 무결성 제약조건 옵션
- 제한(Restricted)
참조 무결성 원칙을 위배하는 연산을 거절하는 옵션
튜플 삭제시 참조무결성 제약조건을 위배할 때 삭제 연산을 수행하지 않는다.
- 연쇄(Cascade)
참조되는 릴레이션에서 튜플을 삭제하면 해당 튜플을 참조하는 타 릴레이션의 튜플을 함께 삭제한다.
- 널 값(Nullify)
참조되는 릴레이션에서 튜플을 삭제하면 해당 튜플을 참조하는 타 릴레이션의 튜플 외래키에 NULL 값을 넣는다.
다만 해당 컬럼에 NOT NULL 제약조건이 있다면 삭제 연산이 수행되지 않는다.
인덱스(Index) 설계
인덱스
- 검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터 구조이다.
- 인덱스를 통해 전체 데이터의 검색 없이 필요한 정보에 대해 신속한 조회가 가능하다.
인덱스 적용 기준
-
인덱스 분포도 기준 10%~15%보다 낮을 때 인덱스를 설정하기 적합하다.
- 인덱스 분포도: 특정 컬럼의 데이터가 테이블에 평균적으로 분포한 정도
-
인덱스 분포도 산출식
- 1 / 해당 컬럼의 distinct 데이터 개수(컬럼 값의 종류) * 100
- 데이터별 평균 로우수(컬럼 값의 평균 로우수) / 테이블의 총 로우수 * 100
-
테이블의 크기가 적은 것(5~6 블럭 이하)
다만 조인의 연결고리가 되는 컬럼에 인덱스가 없으면 조인의 방향이 달라질 수 있어 연결고리가 되는 컬럼은 인덱스를 생성시키는 게 좋다.
-
인덱스 분포도가 낮다는 것은 컬럼 내 같은 값을 지닌 로우가 적다는 것이다.
-
랜덤 액세스가 빈번한 경우
-
특정 범위의 데이터나 특정 순서로 스캔이 요구되는 경우(부분처리)
-
다른 테이블과 순차적 조인(Nested Join)이 발생되는 경우
-
단순 보관용이거나 전체 조회용일 경우엔 인덱스를 생성하지 않는다.
인덱스 생성시 유의사항
- 지나치게 많은 인덱스는 오버헤드로 작용할 수 있다.
- 인덱스는 추가적인 저장 공간이 필요하다.
뷰(View) 설계
- 일반적으로 조회를 목적으로 하는 가상 테이블로 기존 테이블 혹은 다른 뷰에서 접근가능한 전체 데이터 중 일부에 접근할 수 있ㄷ록 제한하는 기법이다.
- 뷰는 데이터 값이 아니라 질의 문장만을 지닌다. 디스크 공간이 할당되지 않으며 데이터 딕셔너리 테이블에 뷰에 대한 정의만 저장된다.
명령어
- REPACE: 뷰가 이미 존재할시 재생성
- FORCE: 본 테이블 존재여부와 무관하게 뷰 생성
- NOFORCE: 기본 테이블이 존재할시 뷰 생성
- WITH CHECK OPTION: 서브 쿼리 내 조건을 만족하는 행 변경
- WITH READ ONLY: 데이터 조작어 작업 불가
클러스터(Cluster) 설계
- 고가용성(Transactional)
- DB의 가용성은 DB가 동작하는 시간과 정지한 시간의 비율이다. 99.99%와 같이 표현한다.
고가용성을 실현하기 위해선 DB 시스템을 구성할 서버나 스토리지 장비를 2대 이상으로 구성하여 한쪽에 장애가 발생하더라도 단시간 내 운용을 재개할 수 있도록 해야 한다.
- 병렬처리(Analytic)
- 처리할 데이터량이나 테이블 개수가 많아 SQL문 실행에 많은 시간이 소요될 때 병렬로 처리하여 연산 속도를 향상시킨다. 주로 ALTER SESSION 명령어나 PARALLEL 힌트 사용의 방식이 사용된다.
- 성능향상(Online)
- DB 유저수가 많을 때 데이터베이스의 복사본을 만들어 참조 처리는 복사한 DB를 사용하게 하는 방식으로 해당 상황을 대비할 수 있다.
이처럼 여러 서버를 조합하여 위 3가지 요구를 만족하는 시스템을 DB 클러스터라 한다.
파티션(Partition) 설계
- DB가 대용량화되면서 성능이 하락하는 문제점이 발생했다. VLDB와 같이 한 DBMS에 너무 큰 테이블이 들어가며 용량과 성능에서 많은 이슈가 발생했고, 이를 해결하기 위해 테이블을 파티션이란 작은 단위로 나눠 관리하는 파티셔닝 기법이 나타난다.
DBMS는 파티셔닝을 위해 여러 기법을 제공한다. 파티셔닝을 위해 파티셔닝 키를 사용한다.
- 레인지 파티셔닝
- 파티셔닝 키값이 범위 내 있는지의 여부로 구분한다.
- 우편 번호를 분할 키로 수평 분할하는 경우
- 리스트 파티셔닝
- 파티셔닝할 항목을 관리자가 직접 지정한다.
- [한국, 중국, 일본 -> 아시아], [노르웨이, 스웨덴, 핀란드 -> 북유럽]
테이블에 포함된 컬럼의 주소 값에서 도시 이름을 기준으로 파티션에 저장한 것이다.
- 해시 파티셔닝
- 해시 함수의 값에 따라 파티션을 나눈다.
- 균등한 데이터 분할로 질의 성능 향상을 도모할 수 있다.
- 컴포지트 파티셔닝
- 위 기술을 결합하는 것이며, 레인지 파티셔닝 후 해시 파티셔닝을 하는 등의 방식이다.
- 파티셔닝을 해도 파티션이 너무 크다면 사용할 것을 고려할 수 있다.
파티션의 목적
- 성능 향상
- 가용성 향상
- 전체 데이터의 훼손 가능성 감소 및 데이터 가용성 향상
- 백업 가능
- 파티셔닝 영역을 독립적으로 백업하고 복구 가능
- 경합 감소
- 디스크 스트라이핑으로 입출력 성능 향상
- 디스크 컨트롤러에 대한 경합 감소
디스크(Disk) 구성 설계
- 정확한 용량을 산정하여 디스크 사용의 효율을 높인다.
- 업무량이 집중된 디스크를 분리하여 설계한다.
- 입출력 경합을 최소화하여 데이터의 접근 성능을 향상시킨다.
- 디스크 구성에 따라 테이블스페이스 개수와 사이즈 등을 결정한다.
- 파티션 수행 테이블은 별도로 분류한다.