[2020 정보처리기사 실기 - 데이터 입출력 구현] 2. 물리적 데이터 저장소 설계하기

 

 

 

 

물리적 데이터저장소 설계 절차 : 물리 속성조사 -> 물리 스키마 설계 -> 물리 용량 산정 -> 데이터베이스 반정규화

 

1) 물리 속성 조사 

- 용어 사전기반 명명 규칙 파악

- 시스템 운영체계, 자원 조사

- DB 시스템 조사 분석서 작성

 

2) 물리 스키마 설계

- ERD변환(논리 -> 물리) : 엔티티 테이블 변환 -> 속성 컬럼 변환 -> 관계 변환 -> 데이터 타입, 표준 선택

  (1) 엔티티 -> 테이블 변환 : 서브를 슈퍼에 통합해 테이블 1개, 엑세스 용이, 속도 향상, 가공 용이, 조인 감소,

                                      테이블 인덱스 크기 증가, 처리마다 서브타입 구분 필요

  (2) 서브타입 기준 변환 : 슈퍼를 서브에 복제, 단위 테이블 크기 작음, 속성들이 프로세스에서 관련 없을 때 유리,

                                  서브타입 구분 안하면 UNION 발생 가능, 복잡한 처리의 SQL 통합 어려움

  (3) 개별타입 기준 변환 : 슈퍼, 서브 타입을 각 테이블로 변환, 모든 테이블 조회 시 SQL 복잡,

                                 트랜잭션 고르게 발생할 때 CRUD 빈번, 서브처리가 독립적일 때, 컬럼 수가 너무 많을 때,

                                 트랜잭션 주로 슈퍼에서 할 때 유리

 - 속성 -> 컬럼 : 엔티티의 PRIMARY UID는 PRIMARY KEY, SECONDARY UID는 UNIQUE KEY,

                       STRING -> VARCHAR, INT -> NUMBER

 - 관계 변환 : 1대M은 PRIMARY:FOREIGN, N대M은 N:1:M으로 중간에 테이블을 하나 더 생성

 - 데이터 타입 변환 : 물리적 데이터 타입으로 변환

 - 인덱스 설계 : PRIMARY, FOREIGN 테이블 접근 경로가 명확한 컬럼은 기본적 인덱스로 지정

                     개발 단계에서 SQL 문자구조 검토와 인덱스 설계

   (1) 인덱스 설계 순서 : 인덱스 대상 선정 -> 인덱스 최적화 -> 인덱스 정의서 작성

   (2) 트리기반, 해시기반 비트맵, 함수기반, 조인, 도메인 등의 인덱스, 일반 상용 DBMS는 B+트리 인덱스 사용

   (3) 인덱스 적용 대상 테이블 선정 : MULTIBLOCK READ수가 16일 때, 테이블 크기가 16블록 이상일 경우

   (4) 인덱스 적용 대상 컬럼 선정 : 분포도가 10~15%인 것 (분포도 = 데이터별 평균 ROW수/테이블총 ROW수*100)

   (5) 결합 인덱스 : 여러 인덱스가 해나의 인덱스가 하나 인덱스를 구성

 - 뷰 설계

  원칙 : 단순화, 다양한 관점, 데이터 보안유지, 독립성

  대상 : 외부 시스템 & 인터페이스 관여, CRUD MATRIX 여러 테이블을 동시에 자주 조인

          거의 모든 문장에서 인라인을 방식으로 접근하는 테이블

 - 클러스터 설계 

   컬럼 값을 순서대로 데이터 행을 저장하는 방법, 하나 이상의 테이블 같은 클러스터 저장 가능

   데이터 접근 효율 향상을 위한 물리적 저장 방식, 검색효율 향상, 입력 수정 삭제시 부하 증가

   분포도 넓을수록 유리, 저장공간 절약, 6블록이상 테이블, 대량 볌위를 자주 엑세스할 때

   인덱스 사용 처리 부담되는 넓은 분포동에 활용, 빈번히 조인할 때, 반복컬럼이 정규화로 분할됐을 때

 - 파티션 설계

   파티션 종류 -> 파티션 키 -> 파티션 수 결정 순서

   트랜잭션 테이블에서 데이터 증가 -> 작은 단위로 나눠서 성능 저하 방지, 관리 수월하게 하기

   데이터 접근 범위 작고 성능 향상, 훼손 가능성 적음, 데이터 가용성 증가, 분할영역 백업 복구 가능

   테이블 파티션 종류(오라클) : 범위 분할(열의 값 기준), 해시분할, 조합분할

 

3) 물리 용량 산정

 - 데이터 베이스 물리 용량 설계

   기초데이터 수립 : 테이블 용량 설계, 인덱스 용량 설계, 테이블 스페이스 용량 설계

 - 물리 데이터 저장소 분산 설계 : 트랜잭션 분석(DB용량 산정하여 구조 최적화)

 - 트랜잭션 분석 활용 : 용량 산정 근거자료, 디스크 구성에 이용, DB와 연결된 채널 분산

 - 무결성 설계 : 정확성, 일관성, 유효성, 신뢰성 등과 무효 갱신으로부터 데이터 보호

                     실체무결성, 영역무결성, 창조무결성, 사용자정의무결성

 - 데이터 무결성 강화 : 어플리케이션 (생성 수정 삭제 시 무결성 검증), DB트리거(트리거 이벤트 시 저장 SQL), 제약조건

 - 분산 설계 : 1개 논리적 데이터베이스가 여러개 물리적 데이터베이스로 분산 시 통합, 공유되는 데이터베이스

                   분할 투명성, 위치 투명성, 장애 투명성, 중복 투명성, 병행 투명성

 

4) 데이터베이스 반정규화

 - 개발 편의성, 운영단순화를 위한 의도적 정규화 원칙 위배

 - 테이블 반정규화 

   테이블 병합 : 조인이 많아서 합치는 게 나을 때, 1:1:1:M 관계 테이블 병합, 슈퍼/서브 타입 관계 테이블 병합

   테이블 분할 : 특정 속성들만 집중적 접근, 접근빈도 잠김현상 경합현상이 적음, 전체 조회 시 UNION 사용 성능 적음

   테이블 수직분할 : 특정 속성만 접근이 잦으면 컬럼을 쪼갬

   테이블 수평분할 : ROW 별로 이용 방법이 다른 경우

   테이블 추가 : 중복테이블 추가(원격 조인 제거로 성능 향상), 통계테이블 추가(SUM, SVG 미리, 조회 시 성능 향상)

 - 컬럼 반정규화

   중복 컬럼 추가 : 조인 성능 저하 예방을 위해 자주 사용하는 컬럼

   파생 컬럼 추가 : 트랜잭션 처리 시점 계산에 의한 성능 저하 예방

   이력 테이블 컬럼 추가 : 대량 데이터 처리 시 불특정 일자나 최근 값 조회 시 성능저하 예방