Ⅲ 데이터 입출력 구현 - 물리 데이터 저장소 설계

물리 데이터 모델 설계 ⭐⭐⭐

물리 데이터 모델링 개념

· 논리모델을 적용하고자 하는 기술에 맞도록 상세화해가는 과정

· 예시 : DDL을 이용해 데이터 모델 정의

CREATE TABLE TEST(
	t_id		INT PRIMARY KEY,
    td_id		INT REFERENCES TEST_DETAIL(td_id),
    t_name		VARCHAR(20),
    reg_date	DATETIME
);

CREATE TABLE TEST_DETAIL(
	td_id		INT PRIMARY KEY,
    td_contents	VARCHAR(1000),
    stert_date	DATETIME,
    end_date	DATETIME,
    reg_date	DATETIME,
    udt_date	DATETIME
);

 

---

 

물리 데이터 모델링 변환 절차

순서	절차	변환방법
1	개체를 테이블로 변환	· 일반적으로 테이블명과 개체명을 동일하게 하는 것을 권고 · 개체는 한글명을 사용 · 테이블은 코드 가독성을 위해 영문명을 사용
2	속성을 컬럼으로 변환	· 개발자와 사용자 간 표준화된 약어를 사용하도록 권고 · SQL 예약어 사용은 피해야함 · SQL 문장 가독성을 높이기 위해 컬럼 명칭은 되도록 짧은 것을 권고 · 컬럼명으로 복합단어를 사용 할 경우 미리 정의된 표준에 의해 명명
3	UID를 기본키로 변환	· 개체의 UID에 해당하는 모든 속성에 대해 기본키로 선언 · Not Null, Unique등의 제약 조건을 추가로 정의 · 관계에 의한 외래키가 기본키에 포함될 수 있음
4	관계를 외래키로 변환	· 외래키명은 기본키 이름 그대로 사용하나 다른 의미를 가질 경우 변경 가능 · 순환 관계에서 자신의 기본키는 외래키로 정의
5	컬럼 유형과 길이 정의	· 적절한 유형을 정의하고, 데이터의 최대길이를 파악하여 길이를 설정
		데이터 유형	설명
		CHAR2	최대 2000바이트의 고정길이 문자열 저장 가능
		VARCHAR2	최대 4000바이트의 가변길이 문자열 저장 가능
		NUMBER	38 자릿수의 숫자 저장 가능
		DATE	날짜 값을 저장
		BLOB, CLOB	바이너리(Binary), 텍스트 데이터 최대 4gb까지 저장
6	반 정규화 수행	· 시스템 성능 향상과 개발 및 운영의 단순화를 위해 데이터 모델을 통합 * 상세 설명 : https://chlee21.tistory.com/214

 

---

 

물리 데이터 저장소 구성 ⭐

: 물리 데이이터 저장소 구성을 위해 DBMS를 선정한 이후의 활동으로 구성된다.

 

테이블 제약조건(Constraint) 설계

1. 참조무결성 제약조건

- 릴레이션과 릴레이션 사이에 대해 참조의 일관성을 보장하기 위한 조건

- 두 개의 릴레이션이 기본키, 외래키를 통해 참조 관계를 형성할 경우, 참조하는 외래키의 값은 항상 참조되는 릴레이션의 기본키로 존재한다.

내용	설명
제한(Restricted)	· 참조무결성 원칙을 위배하는 연산을 거절하는 옵션 · 예 : 참조되는 릴레이션의 튜플을 삭제하면 삭제 연산을 수행하지 않고 거절
연쇄(Casecade)	· 참조되는 릴레이션에서 튜플을 삭제하고, 참조되는 릴레이션에서 이 튜플을 참조하는 튜플들도 함께 삭제하는 옵션
널 값(Nullify)	· 참조되는 릴레이션에서 튜플을 삭제하고, 참조되는 릴레이션에서 NULL 값을 넣는 옵션 · 만일 릴레이션을 정의할 때 참조하는 릴레이션에서 NULL 값이 들어갈 애트리뷰트에 'NOT NULL'이라고 명시되어 있다면 삭제 연산을 거절한다.

- 참조 무결성 제약 조건 SQL 문법(삭제시)

ALTER TABLE 테이블 ADD
FOREIGN KEY (외래키)
REFERENCES 참조테이블(기본키)
ON DELETE [ RESTRICT | CASCADE | SET NULL ];

 

---

 

인덱스(Index) 설계

1. 인덱스 개념

- 검색 연산의 최적화를 위해 데이터베이스 내 열에 대한 정보를 구성한 데이터구조이다.

- 인덱스를 통해 전체 데이터의 검색 없이 필요한 정보에 대해 신속한 조회가 가능하다

 

2. 인덱스 적용 기준

- 인덱스 분포도가 10~15% 이내인 경우 아래 수식을 참고한다.

· 분포도 = (1 / (컬럼 값의 종류)) × 100
· 분포도 = (컬럼 값의 평균 Row 수) / (테이블의 총 Row 수)  × 100

- 분포도가 번위 이상이더라도 부분처리를 목적으로 하는 경우 적용한다.

- 조회 및 출력 조건으로 사용되는 컬럼인 경우 적용한다.

- 인덱스 자동생성 기본키와 Unique키의 제약 조건을 사용할 경우 적용한다.

* 분포도 : 특정 컬럼의 데이터가 테이블에 평균적으로 분포되어 있는 정도

 

3. 인덱스 컬럼 선정

- 분포도가 좋은 컬럼은 단독적으로 생성한다.

- 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성한다.

- 결합 인덱스는 구성되는 컬럼 순서 선정(사용빈도, 유일성, 정렬 등)에 유의한다.

- 가능한 한 수정이 빈번하지 않은 컬럼을 선정한다.

 

4. 설계 시 고려 사항

- 지나치게 많은 인덱스는 오버헤드로 작용한다.

- 인덱스는 추가적인 저장 공간이 필요함을 고려해야 한다.

- 인덱스와 테이블의 저장 공간을 적절히 분리될 수 있도록 설계해야 한다.

 

 

---

 

뷰(View) 설계

1. 뷰 속성

속성	설명
REPLACE	뷰가 이미 존재하는 경우 재생성
FORCE	본 테이블의 존재 여부에 관계없이 뷰 생성
NOFORCE	기본 테이블이 존재할 때 뷰 생성
WITH CHECK OPTION	서브 쿼리 내의 조건을 만족하는 행만 변경
WITH READ ONLY	데이터 조작어 작업 불가

 

2. 뷰 설계 시 고려 사항

- 뷰 사용에 따라 수행속도에 문제가 발생할 수 있다.

- 뷰의 조건은 최적의 액세스 경로를 사용할 수 있도록 한다.

 

---

 

클러스터(Cluster) 설계 :  대상이 되는 범위의 요소를 몇 개 모은 단위체

1. 적용 기준

- 인덱스의 단점을 해결한 기법, 분포도가 넓을수록 유리

- 액세스 효율 향상을 위한 물리적 저장 방법

- 분포도가 넓은 테이블의 클러스터링은 저장 공간의 절약이 가능

- 대량의 범위를 자주 액세스 하는 경우 적용

- 인덱스를 사용한 처리 부담이 되는 넓은 분포도에 활용

- 여러 개의 테이블이 빈번하게 조인을 일으킬 때 활용

 

2. 클러스터 설계 시 고려 사항

- 검색 효율은 높여주나 입력, 수정, 삭제 시는 부하가 증가함을 고려

- UNION, DISTINCT, ORDER BY, GROUP BY가 빈번한 컬럼, 수정이 자주 발생하지 않는 컬럼이면 검토 대상

- 처리 범위가 넓어 문제가 발생하는 경우 : 단일 테이블 클러스터링, 조인이 많아 문제가 발생되는 경우 : 다중 테이블 클러스터링을 고려

 

---

 

파티션(Partition) 설계

1. 파티션의 종류

종류	설명
레인지 파티셔닝 ( = 범위 파티셔닝)	· 연속된 숫자나 날짜를 기준으로 하는 파티셔닝 기법 · 손쉬운 관리 기법을 제공하여 관리 시간의 단축이 가능 · 예) 우편번호, 일별, 월별, 분기별 등의 데이터에 적합
해시 파티셔닝	· 파티션 키의 해시 함수 값에 의한 파티셔닝 기법 · 균등한 데이터 불할이 가능하고 질의 성능이 향상 가능 · 예) 파티션을 위한 범위가 없는 데이터에 적합
리스트 파티셔닝 ( = 목록 파티셔닝)	· 특정 파티션에 저장 될 데이터에 대한 명시적 제어가 가능한 파티셔닝 기법 · 분포도가 비슷하고 데이터가 많은 SQL에거 컬럼의 조건이 많이 들어오는 경우 유용함 · 예) 한국, 일본, 중국 → 아시아 / 노르웨이, 스웨덴, 핀란드 → 북유럽
컴포지트 파티셔닝 ( = 조합 파티셔닝)	· 레인지, 해시, 리스트 파티셔닝 중 2개 이상의 파티셔닝을 결합한 파티셔닝 · 큰 파티션에 대한 I/O 요청을 여러 파티션으로 분산할 수 있다. · 예) 레인지 파티셔닝할 수 있는 컬럼이나, 파티션이 너무 커서 효과적으로 관리할 수 없을 때 유용
라운드로빈 파티셔닝	· 라운도로빈 분할로 회전하면서 새로운 행이 파티션에 할당하는 방식 · 파티션에 행의 고른 분포를 원할 때 사용

 

2. 파티션의 장점

속성	설명
성능 향상	· 데이터 액세스 범위를 줄여 성능 향상
가용성 향상	· 전체 데이터의 훼손 가능성이 감소 및 데이터 가용성 향상
백업 가능	· 분할 영역을 독립적으로 백업하고 복구가능
경합 감소	· 디스크 스트라이핑으로 입출력 성능을 향상 · 디스크 컨트롤러에 대한 경합의 감소 * 디스트 스트라이핑 : 성능 향상을 위해 데이터를 1개 이상의 디스크 드라이브에 저장하여 드라이브를 병렬로 사용할 수 있는 기술

 

---

 

디스트 구성 설계

· 정확한 용량을 산정하여 디스크 사용의 효율을 높인다.

· 업무량이 집중되어 있는 디스크를 분리 설계한다.

· 입출력 경합을 최소화하여 데이터의 접근 성능을 향상시킨다.

· 디스크 구성에 따라 테이블스페이스 개수와 사이즈 등을 결정한다.

· 파티션 수행 테이블은 별도로 분류한다.

 

 

 

 

※ 해당 글은 수제비 2022 도서 참고하였습니다.

수제비- IT 커뮤니티 (정보처리기사... : 네이버 카페