[정보처리기사 필기] 물리 데이터베이스 설계

목차

물리 데이터베이스 설계를 위한 사전 조사 분석

트랜잭션 분석 / CRUD 분석

인덱스 설계

뷰 설계

클러스터 설계

파티션 설계

출처

 

물리 데이터베이스 설계를 위한 사전 조사 분석

---

1. 물리 데이터베이스 설계

물리 데이터베이스 설계는 논리적 구조로 표현된 논리적 데이터베이스를 디스크 등의 물리적 저장장치에 저장할 수 있는 물리적 구조의 데이터로 변환하는 과정이다.

• 가장 기본적인 데이터 단위는 저장 레코드(Stored Record)
• 물리적 설계 단계에 꼭 포함되어야 할 것은 저장 레코드의 양식 설계, 레코드 집중(Record Clustering)의 분석 및 설계, 접근 경로 설계 등

 

데이터베이스 저장 공간에 대해 알아보기

[정보처리기사 필기] 데이터베이스 저장 공간 테이블, 컬럼, 테이블 스페이스 개념

 

2. 고려 사항

• 인덱스 구조
• 레코드 크기
• 파일에 존재하는 레코드 개수
• 파일에 대한 트랜잭션의 갱신과 참조 성향
• 성능 향상을 위한 개념 스키마의 변경 여부 검토
• 빈번한 질의와 트랜잭션들의 수행속도를 높이기 위한 고려
• 시스템 운용 시 파일 크기의 변화 가능성
• 물리적 설계 전에 기존 시스템의 분석하여 데이터 명명 규칙, 시스템 자원, 데이터베이스 관리 요소 등을 파악해야 한다

데이터 명명 규칙 파악

물리 데이터 모델에 적용해야 하는 규칙으로, 조직마다 다를 수 있으므로 물리데이터 모델의 설계 전에 파악해야 함

• 데이터 표준화 및 논리 데이터베이스 설계의 결과물 등을 통해 파악  

시스템 자원 파악

데이터 베이스 설치에 영향을 미칠 수 있는 물리적인 요소들로, 사전에 미리 파악해야 한다.

데이터베이스 관리 요소 파악

데이터베이스 운영과 관련된 관리 요소로, 데이터베이스 시스템의 환경에 따라 달라질 수 있으므로 미리 파악해야 한다.

• 시스템 조사 분석서를 기반으로 다음과같은 특성 파악
  • 데이터베이스 구조
  • 이중화 구성
  • 분산 데이터베이스
  • 접근 제어/접근 통제
  • DB암호화

스토리지(Storage) 알아보기

[정보처리기사 필기] 스토리지(Storage)란?

 

3. 데이터 베이스 용량 설계

• 데이터가 저장될 공간을 정의하는 것
• 디스크에 대한 입/출력 경합이 최소화 되도록 설계함으로써 데이터 접근성이 향상 된다.
• 데이터베이스에 생성되는 오브젝트의 익스텐트 발생을 최소화 하여 성능을 향상 시킨다.

익스텐트 : 기본용량 모두 찼을 경우 추가적으로 할당되는 공간

데이터 접근성을 향상시키는 방법

• 테이블의 테이블스페이스와 인덱스의 테이블스페이스를 분리하여 구성
• 테이블스페이스와 임시 테이블스페이스를 분리하여 구성
• 테이블을 마스터 테이블과 트랜잭션 테이블로 분류

 

4. 분산 데이터베이스 설계

논리적으로는 하나의 시스템에 속하지만 물리적으로는 네트워크를 통해 연결된 여러 개의 컴퓨터 사이트(Site)에 분산되어 있는 데이터베이스를 말한다.

구성요소

• 분산 처리기
• 분산 데이터 베이스
• 통신 네트워크

분산 데이터베이스의 목표

위치 투명성(Location Transparency) : 실제 위치 알 필요 없이 논리적인 명칭 만으로 액세스 가능

중복 투명성(Repliction Transparency) : 동일 데이터 여러곳에 중복되어 있더라도 사용자는 마치 하나의 데이터만 존재하는 것처럼 사용하고, 시스템은 자동으로 여러 자료에 대한 작업 수행

병행 투명성(Concurrency Transparency) : 다수의 트랜잭션들이 동시 실행 돼도 결과는 영향 받지 않음

분산 데이터베이스 설계 방법

테이블 위치 분산 : 테이블을 각기 다른 서버에 분산 배치

분할(Fragmentation) : 테이블의 데이터를 분할하여 분산

할당(Allocation) : 동일한 분할을 여러 개의 서버에 생성. 중복과 비중복 있음

 

 

 

트랜잭션 분석 / CRUD 분석

---

1. 트랜잭션(Transaction) 정의

트랜잭션은 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미한다.

• 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위로 사용된다.
• 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업 단위로 사용된다.

 

2. 트랜잭션의 특성

• 데이터의 무결성(Integrity)을 보장하기 위하여 DBMS의 트랜잭션이 가져야할 특성(ACID)

Atomicity(원자성)	트랜잭션의 연산은 데이터베이스에 모두 반영되도록 완료(Commit)되든지 아니면 전혀 반영되지 않도록 복구(Rollback)되어야 한다. 트랜잭션내의 모든 명령은 반드시 수행되어야 하며, 모두가 완벽히 수행되지 않고 어느 하나라도 오류가 발생하면 트랜잭션 전부가 취소 되어야 한다.
Consistency(일관성)	트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환 시스템이 가지고 있는 고정 요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 한다
Isolation (독립성, 격리성, 순차성)	둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션의 연산이 끼어들 수 없다. 수행중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다.
Durability (영속성, 지속성)	성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영 되어야 한다.

 

3. CRUD 분석

CRUD는 '생성(Create), 읽기(Read), 갱신(Update), 삭제(Delete)' 의 앞 글자만 모아서 만든 용어이며, CRUD 분석은 데이터베이스 테이블에 변화를 주는 트랜잭션의 CRUD 연산에 대해 CRUD 매트릭스를 작성하여 분석하는 것이다.

 

• 테이블에 발생하는 트랜잭션의 주기별 발생 횟수를 파악하고 연관된 테이블들을 분석하면 테이블에 저장되는 데이터의 양을 유추할 수 있다.
• 많은 트랜잭션이 몰리는 테이블을 파악할수 있어 디스크 구성 시 유용한 자료로 활용할 수 있다.
• 외부 프로세스 트랜잭션의 부하가 집중되는 데이터베이스 채널을 파악하고 분산시킴으로써 연결 지연이나 타임아웃 오류를 방지할 수 있다.

CRUD 매트릭스

2차원 형태의 표로서, 행 에는 프로세스를, 열에는 테이블을, 행과 열이 만나는 위치에는 프로세스가 테이블에 발생시키는 변화를 표시하는 업무 프로세스와 데이터간 상관 분석표이다.

• C, R, U, D 가 다없는 행이나 , C, R 이 없는 열을 확인하여 불필요하거나 누락된 테이블 또는 프로세스를 찾는다.

 

4. 트랜잭션 분석

트랜잭션 분석의 목적은 CRUD 매트릭스를 기반으로 테이블에 발생하는 트랜잭션양을 분석하여 테이블에 저장되는 데이터의 양을 유추하고 이를 근거로 DB용량을 산정하고 DB구조를 최적화 하는 것이다.

트랜잭션 분석서

• 단위 프로세스와 CRUD 매트릭스를 이용하여 작성한다
• 구성요소
  • 단위 프로세스
  • CRUD 연산
  • 테이블명, 컬럼명
  • 테이블 참조 횟수
  • 트랜잭션 수
  • 발생 주기

 

 

 

인덱스 설계

---

1. 인덱스(Index) 의 개념

데이터 레코드를 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성되는 데이터 구조

데이터가 저장된 물리적 구조와 밀접한 관계

레코드 삽입, 삭제가 수시로 일어나는 경우 인덱스의 개수를 최소로 하는 것이 효율적

DDL을 이용하여 사용자가 생성, 변경, 제거 가능

인덱스 없으면 TABLE SCAN 발생

레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하게 유지되도록 구성되는 인덱스를 클러스터드(Clustered) 인덱스 라고 한다

 

2. 인덱스의 종류

트리기반 인덱스

• 인덱스를 저장하고 있는 블록들이 트리 구조를 이루고 있는 것으로, 상용 DBMS에서는 트리 구조 기반의 B+ 트리 인덱스를 주로 활용
• 브랜치 블록(Branch Blcok)과 리프 블록(Leaf Block) 으로 구성
  • 브랜치 블록 : 분기를 위한 목적. 다음 단계를 가리키는 포인터 가짐
  • 리프 블록 : 인덱스를 구성하는 컬럼 데이터와 해당 데이터의 행 위치를 가리키는 레코드 식별자로 구성
• B+ 트리는 B 트리의 변형으로 단말 노드가 아닌 노드로 구성된 인덱스 세트와 단말 노드로만 구성된 순차 세트로 구성

비트맵 인덱스

인덱스 컬럼의 데이터를 Bit값인 0 또는 1로 변환하여 인덱스 키로 사용하는 방법

함수 기반 인덱스

컬럼 값 대신 컬럼에 특정 함수나 수식을 적용하여 산출된 값을 사용하는 것으로,  B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용

비트맵 조인 인덱스

다수의 조인된 객체로 구성된 인덱스로, 단일 객체로 구성된 일반적인 인덱스와 액세스 방법다름

도메인 인덱스

개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로, 확장형 인덱스(Extensible Index)라고도 한다

 

 

 

3. 인덱스 설계

인덱스 설계는 컬럼에 대해 기본적인 인덱스 먼저 지정한 후 개발 단계에서 필요한 인덱스의 설계 반복적으로 진행한다.

순서

1. 인덱스의 대상 테이블이나 컬럼 등을 선정한다.
2. 인덱스의 효율성을 검토하여 인덱스 최적화를 수행한다.
3. 인덱스 정의서를 작성한다.

 

인덱스 대상 테이블 선정 기준

• MULTI BLOCK READ 수에 따라 판단 (예: MULTI BLOCK READ 가 16이면 테이블 크기 16블록 이상일 경우 인덱스 필요)
• 랜덤액세스 빈번 테이블
• 특정 범위나 특정 순서로 데이터 조회 필요한 테이블
• 다른 테이블과 순차적 조인 발생되는 테이블

MULTI BLOCK READ : 한번에 읽어 들일 수 있는 블록의 수

 

인덱스 대상 컬럼 선정 기준

• 인덱스 컬럼 분포도가 10~15% 이내인 컬럼 - 분포도 = (컬럼값의 평균 Row 수 / 테이블의 총 Row 수) * 100
• ORDER BY, GROUP BY, UNION 이 빈번한 컬럼

 

 

 

뷰 설계

---

뷰(View) 는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블 이다.

 

1. 뷰(View) 특징

• 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 같다.
• 가상 테이블이기 때문에 물리적 아님
• 데이터 논리적 독립성 제공 가능
• 기본 테이블의 기본키 포함한 속성(열) 집합으로 뷰를 구성해야만 삽입, 삭제, 갱신 연산 가능 하다
• 뷰 정의는 CREATE, 제거할 때는 DROP문

 

2. 뷰(View) 장/단점

 

장점	단점
논리적 데이터 독립성을 제공한다. 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원해 준다. 데이터 관리 간단하게 해줌 접근 제어를 통한 자동 보안 제공	독립적인 인덱스 가질 수 없다. 뷰의 정의 변경 불가 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신 연산에 제약

 

 

 

클러스터 설계

---

데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 데이터 블록에 저장하는 물리적 저장 방법

 

1. 클러스터(Cluster)의 특징

• 클러스터링 된 테이블은 데이터 조회 속도는 향상시키지만 데이터 입력, 수정, 삭제에 대한 성능은 저하시킨다.
• 데이터 분포도가 넓을수록 유리하다(인덱스는 좁을수록 유리)
• 저장공간 줄어듦
• 대용량 트랜잭션은 클러스터링 하지 않는것이 좋다
• 클러스터링된 테이블에 클러스터드 인덱스를 생성하면 접근 성능이 향상된다.

 

2. 클러스터 대상 테이블

• 분포도가 넓은 테이블
• 대량 범위 자주 조회하는 테이블
• 입력, 수정, 삭제 자주 발생하지 않는 테이블
• 자주 조인되어 사용되는 테이블
• ORDER BY, GROUP BY, UNION이 빈번한 테이블

 

 

 

파티션 설계

---

데이터베이스에서 파티션은 대용량의 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것을 말한다

• 대용량 DB의 경우 중요한 몇 개의 테이블에만 집중되어 데이터가 증가되므로, 이런 테이블들을 작은 단위로 나눠 분산시키면 성능 저하를 방지할 뿐만 아니라 데이터 관리도 쉬워진다.
• 테이블이나 인덱스를 파티셔닝하면 파티션키 또는 인덱스키에 따라 물리적으로 별도의 공간에 데이터가 저장된다.
• 데이터 처리는 테이블 단위, 데이터 저장은 파티션별 수행

 

2. 파티션의 장/단점

장점	데이터 접근 시 액세스 범위를 줄여 쿼리 성능 향상 디스크 성능 향상 파티션별로 백업 및 복구를 수행하므로 속도 빠르다 시스템 장애 시 데이터 손상 정도 최소화 가능 데이터 가용성 향상 파티션 단위로만 입/출력 분산 시킬 수 있다.
단점	하나의 테이블을 세분화하여 관리하므로 세심한 관리 요구 테이블간 조인에 대한 비용 증가 용량 작은 테이블에 파티션 수행하면 오히려 성능 저하

 

3. 파티션의 종류

범위 분할(Range Partitioning)	지정한 열의 값을 기준으로 범위를 지정하여 분할한다. 예) 일별, 월별, 분기별 등
해시 분할 (Hash Partitioning)	해시 함수를 적용한 결과 값에 따라 데이터 분할 특정 파티션에 데이터 집중되는 범위분할의 단점을 보완한 것으로, 데이터를 고르게 분산할 때 유용하다. 특정 데이터가 어디있는지 판단 불가 고객번호, 주민번호 같이 데이터가 고른 컬럼에 유용
조합 분할(Composite Partitioning)	범위 분할로 분할한 다음 해시 분할 다시 적용 범위 분할만 할때 파티션이 너무 커서 관리 어려울 때
목록 분할(List Partitioning)	지정한 열 값에 대한 목록 만들어 이를 기준으로 분할 예) '국가'라는 열에 한국 미국 일본 있는 경우 '미국' 을 제외할 목적으로 '아시아' 목록 만들어 분할
라운드 로빈 분할(Round Robin Partitioning)	레코드 균등하게 분배 각 레코드가 순차적으로 분배되며, 기본키 필요없음

 

 

4. 인덱스 파티션

파티션된 테이블의 데이터를 관리하기 위해 인덱스를 나눈 것

• 종속여부에 따라 
  • Local Partitioned Index : 테이블 파티션과 인덱스 파티션이 1대1 대응 되도록. 글로벌보다 데이터 관리 쉽다
  • Global Partitioned Index : 테이블 파티션과 인덱스 파티션이 독립적으로 구성되도록
• 컬럼의 위치에 따라
  • Prefixed Partitioned Index : 인덱스 파티션 키와 인덱스 첫 번째 컬럼이 같다
  • Non - Prefixed Partitioned Index : 다르다

 

---

목차로 이동

출처

자료 참고: 

시나공 정보처리기사 필기 2022를 참고하여 작성되었습니다

표지