정보처리기사/데이터베이스 구축

물리 데이터베이스 설계 2

RangA 2023. 5. 28. 19:03

02. 저장 레코드 형식 설계

01. 저장 테이블(Stored Table)

1) 저장 테이블

1. 저장 테이블의 개념

가장 기본적 데이터베이스 객체인 테이블은 행과 열로 구성되며, 데이터베이스의 모든 데이터는 테이블에 저장됨
상용 DBMS에서 제공되는 테이블들은 DBMS마다 명칭과 기능 등이 약간의 차이가 있음
데이터의 저장 형태와 파티션 여부, 데이터의 유지 기간 등에 따라 테이블을 다양하게 분류할 수 있음

2. Heap-Organized Table(일반 유형 테이블)

대부분의 상용 DBMS에서 표준 테이블로 사용하고 있는 형태
테이블 내에서 튜플의 저장 위치는 특정 속성의 값에 기초하지 않고 해당 튜플이 삽입될 때 결정됨

3. Clustered Index Table(클러스터 인덱스 테이블)

기본키 및 인덱스 키 값 등의 순서를 기반으로 데이터가 저장되는 테이블
Clustered Index는 B 트리의 리프 노드에 RID가 아닌 데이터 페이지가 있는 구조로 이루어짐
검색하고자 하는 키 값의 순서로 정렬되는 데이터 페이지는 프리패치가 가능하고, 인덱스에서 테이블로 탐색 및 조회하는 경로가 상대적으로 단축되어 인덱스를 이용하는 방법보다 데이터 액세스를 빠르게 할 수 있음
- 프리패치(Prefetch) : 레코드들을 특정 파일에 미리 저장하여, 빠른 처리를 할 수 있도록 지원하는 파일
데이터가 입력될 때 키 순서에 따라 지정된 위치에 저장이 이루어져야 하므로 데이터 페이지 유지에 많은 비용이 발생함
Heap-Organized Table을 사용하던 중 Clustered Index Table로 전환하게 되면 데이터 페이지의 갱신이 일어나고 모든 관련 인덱스의 RID가 클러스터 인덱스의 기본키로 변환되므로 다수의 오버헤드가 발생함

2) 파티셔닝 테이블(Partitioning Table)

1. 파티셔닝(Partitioning)

대용량의 테이블을 작은 논리적인 단위로 나눔으로써 성능의 저하 방지 및 관리를 용이하게 하기 위한 테이블
대용량 데이터베이스는 몇 개의 중요한 트랜잭션 테이블에서 데이터가 증가하게 되며, 이것을 보다 작은 단위로 나눔으로써 성능 저하 방지와 관리를 수월하게 할 수 있음
파티셔닝은 파티셔닝 방식 결정, 파티션 키의 선정, 파티션 수의 결정 순으로 진행함
파티셔닝 방식은 범위 분할, 해시 분할, 조합 분할이 있음

2. 접근(Access) 유형 파티셔닝 키의 결정

파티션 키는 접근 유형에 따라 파티셔닝이 이뤄지는 것을 고려하여 선정해야 함
분포도가 낮아 인덱스의 사용이 곤란한 경우 테이블 스캔이 이루어져야 함
스캔 시에 대상이 대용량 테이블일 경우 절대적 작업량으로 인해 성능 문제를 해소하기 어렵게 됨
검색 범위와 파티션 단위가 일치하면 인덱스의 활용 없이 원하는 범위에 대한 데이터를 조회할 수 있음

3. 이력 정보 파티셔닝 키의 결정

이력에 대한 데이터 파티셔닝의 경우 파티션 생성 및 소멸 주기를 일치시켜야 함
이력 관리 데이터는 데이터 관리 전략 및 업무 규칙에 따라 수명이 종료되면 별도의 저장 장치에 저장이 이뤄지고, 데이터베이스에서는 삭제가 이루어짐
활용 가치를 기준으로 이력 데이터의 생성 및 소멸 주기가 결정되므로 주기를 고려하여 데이터베이스를 정리해야 함
삭제 대상 데이터가 복수의 파티션에 분산된 경우 대상 데이터의 추출 및 삭제에 상대적으로 큰 노력과 시간이 필요함
파티션이 데이터의 생성 및 소멸 주기와 일치하는 경우 파티션 대상의 작업을 수행하므로 관리가 쉬워질 수 있음

4. 파티셔닝의 유의 사항

대용량 데이터 관리에 매우 효과적이지만 무조건 파티셔닝만 시행한다고 파티셔닝의 모든 장점을 얻을 수 있는 것은 아님
불량 인덱스가 처리 속도를 오히려 저하시키듯 파티션 키의 구성 방법에 따라 비효율을 높이는 결과를 초래할 수도 있어 파티션 키의 결정은 전략적 관점에서 고려되어야 함
많은 입출력 작업의 분산을 어떻게 할 것인가를 고려하여 선정함

5. 파티셔닝의 장점

데이터 접근 범위가 줄어들어 성능이 향상됨
전체 데이터의 파손 가능성이 감소되고 데이터의 가용성이 향상됨
각 분할 영역을 독립적으로 백업하고 복구할 수 있음
Disk Striping은 Disk 컨트롤러에 대한 경합이 감소되기 때문에 입출력 성능이 향상됨

3) 외부 테이블(External Table)

데이터베이스 내의 일반 테이블과 같은 형태로 이용할 수 있는 데이터베이스의 객체
외부 파일을 연결하여 외부 파일에 SQL을 수행할 수 있는 DBMS의 테이블
데이터웨어하우스에서 ETL 등의 작업에 유용하게 활용할 수 있는 테이블
- 데이터웨어하우스(Data Warehouse)
  - 기업의 정보 자산을 효율적으로 활용하기 위한 하나의 패러다임으로써, 기업의 전략적 관점에서 효율적인 의사 결정을 지원하기 위해 데이터의 시계열적 축적과 통합을 목표로 하는 기술의 구조적, 통합적 환경
- ETL(Extraction, Transformation, Loading)
  - 의료, 교통, 제조 등 여러 분야에서 센서들과 행위 기록들에 대한 데이터를 생성함
  - 인터넷에서 SNS, UCC, 온라인 쇼핑 등의 다양한 행위를 통해 많은 양의 데이터가 생성됨
  - 다양한 소스 시스템으로부터 필요한 데이터를 추출하고 변환하여 저장하거나 분석 등의 모든 과정을 포함함

4) 임시 테이블(Temporary Table)

트랜잭션 및 세션별로 데이터를 저장하고 처리할 수 있는 테이블
저장된 데이터는 트랜잭션이 종료되면 사라지는 휘발성의 속성을 보이며, 다른 세션에서 처리되는 데이터의 경우에는 공유할 수 없음
절차적 처리를 위해 임시적으로 사용할 수 있는 테이블

2. 컬럼 타입(Column Type)

1) 컬럼

컬럼은 테이블을 구성하는 요소로, 데이터 타입 및 길이 등으로 정의됨
데이터 타입은 데이터 일관성 유지에 가장 기본적 기능
표준화된 도메인을 정의한 경우 그에 따라 데이터의 타입과 길이를 정의함
DBMS는 일반적으로 내장 및 확장 데이터 형식을 지원함
비교 연산으로 두 컬럼 사이에 데이터 타입과 길이가 다른 경우 DBMS는 내부적으로 데이터 타입을 변형한 후 비교 연산을 수행해야 함
컬럼이 상호 참조 관계일 경우 가능한 동일 데이터 타입과 길이를 사용해야 함
다른 데이터 타입을 사용한 경우 조인 또는 비교 연산에 인덱스가 있어도 사용이 어렵게 되거나 실행 계획 예측이 어려울 수 있음

2) 컬럼 데이터 타입에 따른 물리적 순서 조정

NOT NULL인 고정 길이 컬럼은 앞쪽에 배치함
가변 길이 컬럼은 뒤 쪽에 배치함
NULL 값이 많을 것으로 예상하는 컬럼은 뒤 쪽에 배치함
물리적 순서 조정은 DBMS마다 차이가 있지만 값이 변경될 대 체인(Chain) 발생을 억제하고 저장 공간의 효율적인 사용을 위해 필요함
- 체인(Chain) 발생 : 값이 변경되거나 구조가 변경되면 연속된 위치에 데이터를 저장할 수 없으므로 변경된 정보를 연결 리스트로 연결함

3) 데이터 타입과 길이 지정 시 고려사항

가변 길이 데이터 타입은 예상되는 최대 길이로 정의함
고정 길이 데이터 타입은 최소 길이를 지정함
소수점 이하 자릿수의 정의는 반올림되어 저장되므로 정확성을 확인하고 정의함

4) 문자열 비교 방법

길이가 짧은 컬럼 끝에 공백을 추가하여 길이를 같게 한 후 비교하는 방식
공백의 추가 없이 비교하는 방식

5) 데이터 타입의 변환 비교

CHAR & NUMBER 데이터 타입 변환 비교
- 데이터 타입이 NUMBER인 경우 CHAR인 경우보다 우선순위가 높기 때문에 내부적으로 데이터 타입을 CHAR에서 NUMBER로 변환하여 비교함
- 문자열 컬럼에 알파벳 등이 혼용되어 변환이 어려운 경우 SQL 오류가 발생함
CHAR & NUMBER 데이터 타입의 LIKE를 사용한 비교
- NUMBER 데이터 타입과 비교되는 CHAR 데이터 타입에 숫자로 변환이 불가능한 문자를 포함하고 있는 경우 변환 비교가 불가능함
- LIKE를 사용한 경우 대상을 모두 문자열로 변환하여 비교하므로 비교가 가능해짐

03. 테이블 스페이스(Table Space)

1) 테이블 스페이스의 개념

데이터베이스의 테이블을 생성할 때 테이블들이 저장되는 물리적인 위치(디스크)와 공간을 사용자가 직접 지정하여 투명성 있게 사용하는 방법
테이블 스페이스는 용량을 늘리거나 위치를 변경하면서 사용할 수 있음
테이블은 테이블 스페이스라는 논리적 단위를 활용하여 관리함
테이블 스페이스는 물리적인 데이터 파일을 지정하여 저장됨
테이블, 테이블 스페이스, 데이터 파일로 분리하여 관리하는 경우 논리적 구성이 물리적 구성에 종속되지 않고 투명성에 대한 보장이 가능함
데이터베이스에 저장되는 내용에 따라 테이블 스페이스는 테이블, 인덱스, 임시 등으로 구분하여 설계함
테이블 스페이스는 백업 및 공간 확장 단위인 물리적 파일 크기의 적정한 유지를 하기 위함
테이블 스페이스는 데이터 용량을 관리하는 단위로 이용할 수 있음

2) 테이블 스페이스 설계

테이블을 마스터 테이블과 트랜잭션 테이블로 분류함
테이블이 저장되는 테이블 스페이스는 업무별로 지정함
예상 레코드 건수에 따라 2 ~ 4개로 분류함
대용량 테이블은 독립적인 테이블 스페이스를 지정함
테이블과 인덱스는 분리하여 저장함
시스템의 구성에 따라 테이블 스페이스의 개수와 크기 등을 결정함
분류에 따라 테이블 스페이스의 용량을 결정하고 저장 장치를 결정함
테이블 스페이스의 확장 크기를 테이블에서 동일하게 적용하거나 정수배로 결정함

04. 물리 데이터베이스 용량 산정

1) 용량 설계

1. 용량 설계의 목적

정확한 데이터 용량을 예측하여 저장 공간을 효율적으로 사용하고 확장성을 보장하여 가용성을 높이기 위함
하드웨어 특성을 고려한 용량 설계를 통해 디스크 채널 병목을 최소화할 수 있음
디스크 입출력을 분산한 용량 설계를 통해 접근 성능을 향상시킬 수 있음
용량 설계를 통해 테이블 및 인덱스별로 적합한 저장 옵션을 지정할 수 있음

2. 테이블 저장 옵션의 고려사항

초기 크기, 증가 크기 : 데이터베이스의 초기 및 증가 크기를 고려함
트랜잭션 관련 옵션 : 데이터베이스에서 제공 및 생성된 옵션을 고려함
최대 크기와 자동 증가 : 데이터베이스에서 제공 및 설정한 최대 크기 및 자동 증가를 고려함

2) 용량 분석의 목적 이해

정확한 용량을 산정하여 디스크 사용의 효율을 높여야 함
업무량이 집중되어 있는 디스크를 분리하여 설계함으로써 집중화된 디스크에 대한 입출력 부하를 분산시켜야 함
입출력 경합을 최소화하여 데이터의 접근 성능을 향상시키야 함
데이터베이스 오브젝트의 익스턴스 블록 발생을 줄려야 함
- 익스턴스 블록(Extent Block)
  - 블록 : 물리적인 입출력 단위
  - 익스턴트 블록 : 블록의 집합
  - 세그먼트 블록 : 익스턴트 블록의 집합

3) 기초 데이터 수집하기

분산별, 개체 타입명, 테이블명, 로우 길이, 보존 기간, 초기 건수 및 발생 건수, 발생 주기, 연 증가율 등 용량 분석을 위한 기초 자료를 수집함
기초 데이터를 이용하여 DBMS에 이용하는 오브젝트별로 용량을 산정함
오브젝트별 용량을 산정하기 위해 오브젝트 설계와 테이블 스페이스, 디스크 등의 용량 산정을 파악해야 함

4) 용량 계산 항목

테이블 용량 계산 항목
- 총 블록 헤드 계산
- 블록 당 가능한 데이터 영역 계산
- 평균 행의 전체 길이 계산
- 총 평균 행 크기 계산
- 데이터 블록 내의 평균 행의 수 계산
- 테이블에 요구하는 블록과 바이트 수 계산
인덱스 용량 계산 항목
- 총 블록 헤더 계산
- 블록 당 가능한 데이터 영역 계산
- 결합된 열의 길이 계산
- 인덱스 값 크기의 전체 평균 계산

저작자표시 비영리 변경금지 (새창열림)