정보처리기사 필기 3. 데이터베이스 구축 - Chapter 4. 물리 데이터베이스 설계

1 물리 요소 조사 분석

1 스토리지

1 개념

정보를 보존하는 저장장치

2 구성

• DAS(Direct Attached Storage)
  • 데이터 서버와 외장형 저장 장치를 전용 케이블로 직접 접속
  • 장점: 성능 보장, 안정성
  • 단점: 저장 장치에 따라 다른 접속 방법 -> 공유 문제
• NAS(Network Attacked Storage)
  • LAN을 통해 스토리지와 서버를 접속
  • 파일서버를 통한 파일시스템 공유
  • 장점: 데이터 저장 장소와 서버 간의 독립성 유지
  • 단점: 데이터 접근 요청에 의한 파일 서버의 병목현상 발생 가능, 파일 서버 종료 시 스토리지 접근 불가
• SAN(Storage Area Network)
  • 서버가 광섬유 채널을 통하여 스토리지를 연결
  • 장점: DAS의 접속한계성 극복, n개의 서버가 m개의 저장장치 접속 가능
  • 단점: 이기종 서버환경 지원 X, 공유 가능한 파일시스템과 데이터 형식 제한

2 분산 데이터베이스

1 개념

네트워크의 활용을 통해 단일한 데이터베이스 관리 시스템으로 제어

논리적으로 집중, 물리적으로 분산

2 특징

장점

• 분산 제어 용이
• 지역 자치성
• 효용성과 융통성
• 데이터베이스 복제 및 분산 -> 성능 향상
• 데이터 유실 복구
• 시스템 확장

단점

• 복잡성 증가
• 성능 저하(메시지 교환 오버헤드)
• 개발 비용

3 구성

• 전역 스키마: 분산 데이터베이스에 포함된 모든 데이터 정의
• 분할 스키마: 분산을 위해 단편화된 구성에대한 정의
• 할당 스키마: 단편들의 위치 정보, 중복/비중복 결정
• 지역 스키마: 물리적 이미지를 해당 지역 DBMS가 관리할 수 있도록 저장하기 위해 변환

4 투명성

• 위치 투명성: 데이터의 물리적 위치를 몰라도 됨
• 복제 투명성: 복제 여부를 몰라도 됨
• 병행 투명성: 동시에 분산 데이터베이스에 대한 트랜잭션을 수행하는 경우 이상 발생 X
• 분할 투명성: 하나의 논리적인 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 장소에 저장
• 장애 투명성: 분산되어 있는 각 지역의 시스템이나 통신망에 이상이 생기더라도 데이터 무결성 보존, 2PC(Phase Commit) 활용

3 데이터베이스 이중화

1 개념

로컬 데이터베이스의 변경된 내용을 원격 데이터베이스에 복제하고 관리

2 목적

• 데이터베이스의 무정지 서비스
• 사용자는 하나의 데이터베이스에 대해서만 작업 수행
• 여러 개의 데이터베이스 동시 관리

3 형태

• 액티브-액티브: 여러 노드가 동시에 가동
• 액티브: 스탠바이: 하나의 노드만 가동, 나머지 노드들은 대기

4 데이터베이스 암호화

1 개념

데이터베이스에 저장된 데이터를 권한이 없는 사람이 볼 수 없도록 저장

2 유형

• 응용 프로그램 자체 암호화: 암복호화 모듈이 API 형태로 각 애플리케이션 서버에 설치, 응용프로그램에서 호출
• DB 서버 암호화: 암복호화 모듈이 DB 서버에 설치, DB 서버에서 호출
• DBMS 자체 암호화: DB 서버의 DBMS 커널이 자체적으로 암복호화 기능 수행
• DBMS 암호화 기능 호출: 응용 프로그램에서 DB 서버의 DBMS 커널이 제공하는 암복호화 API 호출
• 운영체제 암호화: OS에서 발생하는 물리적인 입출력을 이용한 암복호화

3 적용 방식

컬럼 암호화

• API: 애플리케이션 서버
• 플러그인: DB 서버
• 하이브리드: 플러그인 방식의 단점인 배치 업무의 성능 저하를 보완하기 위해 API 방식 이용

블록 암호화

• TDE 방식: DBMS에 추가 기능으로 제공되는 암호화 기능 이용
• 파일 암호화 방식: OS 상에서 확인 가능한 개체인 파일을 암호화

5 접근제어

1 개념

불법적인 데이터의 접근으로부터 데이터베이스 보호

2 정책

자원에 접근하는 사용자의 접근 모드 및 모든 접근 제한 조건 정의

• 신원기반 접근제어 정책(DAC): 주체나 그들이 속해있는 그룹들의 신분에 근거하여 접근 제한
  • IBP: 단일 사용자가 하나의 객체에 대해 허가를 부여받아 사용
  • GBP: 복수 사용자가 하나의 객체에 대해서 같은 허가를 함께 부여받아 사용
• 규칙기반 접근제어 정책(MAC): 객체에 포함된 정보의 비밀성과 비밀성의 접근정보에 대하여 주체가 갖는 권한에 근거하여 객체에 대한 접근 권한 제한
  • MLP: 사용자 및 객체가 각각 부여된 기밀 분류에 따른 정책
  • CBP: 조직 내 특정 집단별로 구분된 기밀 허가에 따른 정책
• 역할기반 접근제어 정책(RBAC): 중앙관리자가 주체와 객체의 상관관계 제어

3 매커니즘

• ACL: 주체가 특정 시스템 객체에 접근할 수 있는 허가 받은 접근 종류들이 기록된 목록
• CL: 주체에게 허가된 자원 및 권한의 목록

4 보안 모델

• 접근 제어 행렬: 임의적 접근 제어 관리를 위한 보안 모델
• 기밀성 모델: 기밀성에 중심을 둔 수학적 모델
• 무결성 모델: 정보 비밀성을 위해 정보의 일방향 흐름 제어를 활용

---

2 DB 물리 속성 설계

1 파티셔닝

1 개념

대용량의 테이블을 파티션으로 나눔으로써 성능 저하 방지 및 관리에 용이

2 특징

• 전체 데이터 훼손 가능성 감소, 데이터 가용성 향상
• 관리 편의
• 성능 향상

3 유형

• 레인지 파티셔닝: 분할 키 값
• 해시 파티셔닝: 해시 함수의 값
• 리스트 파티셔닝: 값 목록
• 컴포지트 파티셔닝: 2개 이상의 파티셔닝 결합

2 클러스터링

1 개념

• 지정된 컬럼 값의 순서대로 데이터 행 저장
• 여러 테이블을 같은 클러스터 내 저장 가능
• 액세스 효율 향상을 위한 물리적 저장 방법
• 검색 효율 향상, 입력/수정/삭제 시 부하 증가 가능
• 분포도가 넓을수록 유리

2 유형

• 단일 클러스터링: 클러스터에 하나의 테이블만 생성
• 다중 클러스터링: 단일 클러스터에 두 개 이상의 테이블 저장, 조인 속도 향상

3 설계 시 고려사항

• 대량 범위 자주 액세스
• 넓은 분포도
• 여러 개의 테이블이 자주 조인
• 반복 컬럼이 정규화에 의해 어쩔 수 없이 분할
• UNION, DISTINCT, ORDER BY, GROUP BY가 빈번
• 수정이 자주 발생 X

3 데이터베이스 백업

1 개념

데이터가 손상되거나 손실될 경우 피해를 최소화하기 위해 저장

2 종류

• 전체 백업
• 차분 백업: 마지막 전체 백업 이후 변경된 모든 데이터를 백업
• 트랜잭션 로그 백업: 로그 파일에 기록, 느린 속도(데이터 복사 X, DML 다시 수행)

4 테이블 저장 사이징

1 필요성

정확한 데이터 용량 예측 -> 효과적, 확장성 보장

디스크 채널 병목 현상 최소화

용량 설계를 통해 적합한 저장 옵션 지정

2 계산 방법

1. 레코드의 평균 길이 계산

   레코드 평균 길이 = 레코드 헤더 사이즈 + 열 헤더 사이즈 + 열 데이터 사이즈

2. 블록에 들어가는 레코드 수 계산

   블록의 빈 공간 = 블록 사이즈 - 블록 헤더 사이즈 - 예비영역 사이즈

   ​ 블록 사이즈: 기본적으로 8KB

   ​ 블록 헤더 사이즈 = 90 + 트랜잭션 예약 공간

   ​ 예비영역 사이즈 = 블록 사이즈 - 블록 헤더 사이즈 * 예비영역 비율

   블록에 들어가는 레코드 수 = 예비영역 사이즈 / 레코드 평균 길이

3. 테이블 용량 계산

   테이블 용량 = 예상 레코드 수 / 블록에 들어가는 레코드 수 * 블록 사이즈

3 옵션의 고려사항

• 트랜잭션 관련 옵션: 트랜잭션 예약 공간(INITRANS) 파라미터 값 조정
• 예비 영역 옵션: 예비 영역 비율(PCT_FREE) 파라미터 값 조정

5 데이터 지역화

1 개념

데이터베이스 저장 데이터를 효율적으로 이용할 수 있도록 저장하는 방법

필요한 위치에 가까이 배치

2 조건

모든 코드나 데이터를 균등하게 접근하지 않는다

3 종류

• 시각적 지역화: 반복문에 사용하는 조건 변수
• 공간적 지역화: 배열에 연속 접근
• 순차적 지역화분기가 없는 데이터가 기억장치에 저장된 순서대로 순차적으로 인출되고 실행

4 활용 사례

• 기억장치 계층구조: CPU -> 캐시 메모리 -> 메인 메모리 순으로 접근시간 단축
• 캐시 접근시간 단축: 캐시 적중률 극대화
  • LRU: 가장 적게 사용된 데이터 교체
  • 선인출 방식: 필요한 정보와 예상 정보를 미리 인출
• 작업세트: 하나의 페이지가 자주 접근하는 페이지들의 집합

---

3 물리 데이터베이스 모델링

1 데이터베이스 무결성

1 개념

데이터베이스에 저장된 데이터 값과 실제 값이 일치하는 정확성

권한이 있는 사용자로부터 데이터베이스 보호

cf ) 보안: 권한이 없는 사용자로부터 데이터베이스 보호

2 종류

• 개체 무결성: 기본키 중복 불가, 기본키 Null 불가
• 참조 무결성: 외래키가 참조하는 키가 기본키 또는 Null
• 속성 무결성: 속성의 값은 기본, Null, 도메인이 지정된 규칙을 준수
• 사용자 무결성: 사용자의 의미적 요구사항 준수
• 키 무결성: 중복 튜플 불가

2 컬럼

1 개념

테이블을 구성하는 요소, 자료형 및 길이로 정의

2 구성요소

• 내장 데이터 형식: 문자, 숫자, 시간, 대형 객체 등
• 확장 데이터 형식: 행, 컬렉션, 사용자 정의, 데이터 형식 등

3 고려사항

• 타입에 따른 물리적 순서 조정
  • 고정 길이 & Not Null인 컬럼은 앞에 배치
  • 가변 길이는 뒤에 배치
  • Null 값이 많을 것으로 예상되는 컬럼은 뒤에 배치
• DBMS별 물리적 순서 조정: 체인 발생을 억제하고 저장 공간의 효율적인 사용을 위해 필요
• 데이터 타입, 길이 지정 고려
  • 가변 길이는 최대 길이로 정의
  • 고정 길이는 최소 길이 지정
  • 소숫점 이하 반올림을 고려하여 정확성 확인
• 컬럼 간 비교 방법

3 키

1 개념

다른 튜플과 구별할 수 있는 기준이 되는 속성

2 특성

• 유일성: 식별자에 의해 엔터티 타입 내에 모든 엔터티들이 유일하게 구분 (유일한 튜플)
• 최소성: 최소한의 속성으로 식별자 구성

3 종류

• 기본키(Primary Key): 테이블의 각 튜플을 고유하게 식별하는 컬럼, Not Null
• 대체키(Alternate Key): 후보키 중 기본키로 선택되지 않은 키
• 후보키(Candidate Key): 테이블에서 각 튜플을 구별하는 기준이 되는 컬럼(기본키 + 대체키)
• 슈퍼키(Super Key): 유일성은 만족하지만 최소성은 만족하지 못하는 키
• 외래키(Foreign Key): 다른 릴레이션의 기본키로 이용되는 키, 참조 무결성을 위한 제약 조건

---

4 DB 반정규화

1 반정규화

1 개념

정규화된 엔터티, 속성, 관계에 대해 성능 향상과 단순화를 위해 중복, 통합, 분리

2 특징

• 장점: 성능 향상과 관리 효율성
• 단점: 이로간성 및 정합성 저하, 유지 비용 발생

3 기법

테이블

• 테이블 병합: 1:1 관계, 1:M 관계 통합 -> 조인 횟수 감소 -> 성능 향상
• 테이블 분할: 테이블을 수직 또는 수평으로 분할
• 중복 테이블 생성: 집계함수를 사용하여 효과적인 수행을 위해 테이블 추가

컬럼

• 중복 컬럼 생성: 조인 성능 저하 예방

관계

• 중복 관계 추가: 조인 시 발생할 수 있는 성능 저하를 예방하기 위해 관계 추가

---

5 물리 데이터 모델 품질 검토

1 물리 데이터 모델 품질 기준

1 개념

물리 데이터 모델은 시스템 성능에 직접적 영향 -> 성능 문제 사전 검토 필요

2 요소

• 정확성
• 완전성
• 준거성
• 최신성
• 일관성
• 활용성

물리 ERD

1 엔터티와 테이블의 물리 데이터 모델로 변환

논리적 설계	물리적 설계	데이터베이스
엔터티	테이블	테이블
속성	컬럼	컬럼
주 식별자	기본키	기본키
외래 식별자	외래키	외래키
관계	관계
관계의 카디널리티	관계의 카디널리티
관계의 참여도	관계의 참여도

2 관계 변환

• 1:1 변환
• 1:N 변환
• N:M 변환

N:M 변환 케이스

• 외래키 분리: 관계별로 관계 컬럼을 생성
• 외래키 결합: 관계들을 하나의 관계 컬럼으로 통합

3 관리 목적 테이블 또는 컬럼의 추가

프로그래밍의 수행 속도 향상을 위해 추가되는 테이블이나 컬럼으로 관리 가능

4 데이터 타입 선택

논리 모델링에서 정의된 논리적인 데이터 타입을 물리적인 DBMS 특성과 성능 고려를 통해 최적 데이터 타입 선택

5 데이터 표준 적용

1. 요구사항 수집
2. 표준 정의
3. 표준 확정
4. 표준 관리

3 CRUD 분석

1 개념

시스템 개발 시 프로세스와 데이터베이스에 저장되는 데이터 사이의 관계를 CRUD로 분석

2 CRUD 매트릭스

1 개념

프로세스와 데이터 사이의 관계 의존성을 CRUD로 표현한 매트릭스

2 필요성

• 모델링 작업 검증: 분석 단계의 데이터 모델과 프로세스 모델에 대한 작업 검증 역할 수행
• 중요 산출물: 시스템 구축 단계에서 애플리캐이션 개발 시 필요
• 테스트 시 사용 산출물: 애플리케이션을 객관적 자료를 사용하여 테스트 시 중요한 테스트 케이스 역할 수행
• 인터페이스 현황 파악: 전체 업무의 인터페이스 파악

3 구성요소

• 엔터티 타입: 프로세스에 영향을 받는 데이터
• 단위 프로세스: 엔터티에 영향을 주는 업무 단위
• CRUD: 프로세스가 엔터티에 주는 영향

4 규칙

• 엔터티 타입
  • 모든 엔터티에 C 존재: 데이터는 1번 생성
  • 모든 엔터티에 R 존재: 최소 한번 읽기
  • 모든 엔터티에 CRUD 존재: CRUD 중 최소 하나
• 프로세스
  • 두 개 이상의 단위 프로세스가 하나의 엔터티 타입 생성 불가: 같은 엔터티에 C 두 개 이상 불가
  • 모든 단위 프로세스는 하나 이상 엔터티 타입에 표기: 단위 프로세스에 CRUD 중 최소 하나

4 SQL 성능 튜닝

1 개념

최소의 자원을 이용하여 데이터베이스로부터 최적의 성능을 제공하는 개선활동

2 기법

• 옵티마이저 조정
• 힌트 사용
• 부분 범위 처리 사용
• 인덱스 활용