[SQLD자격증독학] 제 2장 데이터 모델과 성능

31. 성능데이터모델링

: 데이터베이스 성능 향상을 목적으로 설계단계의 데이터 모델링 때부터 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것

-> 성능이 저하된 결과를 대상으로 데이터 모델보다는 문제 발생시점의 SQL을 중심으로 집중하여 튜닝한다 XXXX!! 관련 X!!

- 데이터의 증가가 빠를 수록 성능저하에 따른 성능개선비용은 증가

- 데이터 모델은 성능을 튜닝하면서 변경이 될 수 있는 특징이 있다

- 분석/설계 단계에서 성능을 고려한 데이터 모델링을 수행할 경우 성능 저하에 따른 Rework 비용을 최소화 할 수 있는 기회를 가지게 된다.

33. 데이터 모델링의 순서

1) 데이터베이스 용량산정을 수행

2) 데이터 모델링을 할 때 정규화를 정확하게 수행

3) 데이터베이스에 발생되는 트랜잭션의 유형을 파악

4) 용량과 트랜잭션의 유형에 따라 반정규화를 수행

5) 이력모델의 조정, PK/FK조정, 슈퍼타입/서브타입 조정 등을 수행

6) 성능관점에서 데이터 모델을 검증

34. 성능데이터 모델링을 할 때 고려사항

- 정규화는 기본적으로 중복된 데이터를 제거함으로써 조회성능을 향상시킬 수 있음

- 용량산정은 전체적인 데이터베이스에 발생되는 트랜잭션의 유형과 양을 분석하는 자료가 되므로 성능데이터 모델링을 할 때 중요한 작업이 될 수 O

- 물리적인 데이터 모델링을 할 때 PK/FK의 칼럼의 순서조정, FK 인덱스 생성 등은 성능 향상을 위한 데이터 모델링 작업에 중요한 요소가 된다

- 이력 데이터는 시간에 따라 반복적으로 발생이 되기 때문에 대량 데이터일 가능성이 높아 특별히 성능을 고려하여 칼럼 등을 추가하도록 설계해야 한다.

35. 정규화

(비정규 릴레이션) -> 원자값이 아닌 도메인을 분해 -(1NF)-> 부분 함수 종속 제거 -(2NF)-> 이행적 함수 종속 제거 -(3NF)-> 결정자가 후보키가 아닌 함수 종속 제거 -(BCNF)-> 함수 종속이 아닌 다치 종속 제거 -(4NF)-> 후보키를 통하지 않은 조인 종속제거 -(5NF)

[보관금원장] : {관서번호, 납부자번호} -> {관리점번호, 관서명, 상태, 관서등록일자, 직급명, 통신번호}

- 함수 종속성(FD) : 

{관서번호, 납부자 번호} -> {직급명, 통신번호}

{관서번호} -> {관리점번호, 관서명, 상태, 관서등록일자}

- 관서정보를 조회할 때에 몇 차 정규화가 필요한가?

: 2차 정규화(부분함수종속제거) - 정규화테이블{관서번호, 관리점번호, 관서명, 상태, 관서등록일자}

37. [모델] : {모델코드} -> {모델명, 제품류코드, 물품가, 출하가, A유형기능분류코드1, B유형기능분류코드2, C유형기능분류코드3, ...., 바코드, 가로, 세로, 높이, 모델구분, ...}

- 동일한 유형의 속성이 칼럼단위로 반복되는 경우 -> 속성의 원자성을 위배 -> 제 1차 정규화의 대상

: 유형기능분류코드 각각에 대하여 개별로 Index를 모두 생성할 경우 입력, 수정, 삭제 때 성능이 저하되므로 제 1차 정규화를 수행한 후 인덱스를 적용하는 것이 좋다.

38. [일재고] : {물류센터코드, 재고일자} -> {월초재고수량, 장기재고1개월수량, 장기재고2개월수량, 장기재고3개월수량, 장기재고1개월주문수량, 장기재고2개월주문수량, ......}

- 1차 정규화가 필요한 엔터티로서 일재고와 일재고상세로 1:M의 관계가 될 수 있다.

39. [수강지도] : {학번, 과목코드} -> {성적, 지도교수명, 학과명}

- 함수종속성(FD)

1) 학번 || 과목번호 -> 성적

2) 학번 -> 지도교수명

3) 학번 -> 학과명

- PK에 대해 반복이 되는 그룹이 존재하지 않으므로 1차 정규형이라고 할 수 O

- 부분 함수 종속 규칙을 가지고 있으므로 2차 정규형 X

- 1차 정규형 - 2차 정규화 대상

40. 데이터 모델에 대한 반정규화를 고려할 때 판단요소에 대한 설명

- 반정규화 정보에 대한 재현의 적시성으로 판단

ex) 빌링의 잔액은 다수 테이블에 대한 다량의 조인이 불가피하므로 데이터 제공의 적시성 확보를 위한 필수 반정규화 대상 정보이다.

- 다량 데이터 탐색의 경우 인덱스가 아닌 파티션 및 데이터 클러스터링 등의 다양한 물리 저장 기법을 활용하여 성능 개선을 유도할 수 있다.

-> 다만, 하나의 결과셋을 추출하기 위해 다량의 데이터를 탐색하는 처리가 반복적으로 발생한다면 이때는 반정규화를 고려하는 것이 좋다

- 이전 또는 이후 위치의 레코드에 대한 탐색은 window function으로 접근 가능

- 집계 테이블 이외에도 다양한 유형(다수 테이블의 키 연결 테이블 등)에 대하여 반정규화 테이블 적용이 필요할 수 있다.

41. 하나의 테이블의 전체 칼럼 중 자주 이용하는 집중화된 칼럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 칼럼들을 별도로 모아놓는 반정규화 기법으로 가장 적절한 것은? 테이블 추가 - 부분테이블추가

- 테이블의 반정규화

기법분류	반정규화 기법
테이블 병합	1:1 관계 테이블 병합
	1:M 관계 테이블 병합
	슈퍼/서브타입 테이블 병합
테이블 분할	수직분할
	수평분할
테이블 추가	중복테이블 추가
	통계테이블 추가
	이력테이블 추가
	부분테이블 추가

- 칼럼의 반정규화

반정규화 기법

중복칼럼 추가

파생칼럼 추가

이력테이블 칼럼 추가

PK에 의한 칼럼 추가

응용시스템 오작동을 위한 칼럼 추가

42. FK에 대한 속성을 추가한다 - FK관계에 해당하는 속성을 추가하여 조인 성능을 높인다

: 데이터 모델링에서 관계를 연결할 때 나타나는 자연스러운 현상

45. 로우 체이닝

: 단일 데이터 베이스 블록으로 들어가기에는 너무 큰 Row가 있을 수 있다.

: 데이터 베이스의 Blocksize가 4KB를 사용하는 경우 8KB 짜리 Row를 삽입할 경우

-> 2개의 Block에 4KB씩 저장한다. 2개의 Block은 로우체이닝이 된다.

: 로우체이닝이 발생할 정도로 한 테이블에 많은 칼럼들이 존재할 경우 조회성능 저하가 발생할 수 있다. 트랜잭션이 접근하는 칼럼 유형을 분석하여 1:1로 테이블을 분리하면 디스크 I/O가 줄어들어 조회 성능을 향상 시킬 수 O

반정규화의 대상에 대해 다른 방법으로 처리

- 지나치게 많은 조인 -> VIEW(뷰)를 사용하면 해결

- 대량의 데이터처리나 부분처리 -> 클러스터링을 적용

        -> 인덱스를 조정

- 대량의 데이터는 Primary Key의 성격에 따라 부분적인 테이블로 분리 할 수 있다. 파티셔닝 기법이 적용

- 응용 애플리케이션에서 로직을 구사하는 방법을 변경

슈퍼/서브 타입 데이터 모델의 변환기술

- 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성

- 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성

- 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성

47. 데이터 모델과 SQL문에 대해 개선해야 할 사항

[긴급사건] : {긴급사건 번호} -> {사건명, 발생일시}

[특수사건] : {특수사건 번호} -> {사건명, 발생일시} 

[일반사건] : {일반사건 번호} -> {사건명, 발생일시} 

SELECT 긴급사건번호, 사건명 FROM 긴급사건 WHERE 발생일시 = '20150905' UNION ALL

SELECT 특수사건번호, 사건명 FROM 특수사건 WHERE 발생일시 = '20150905' UNION ALL

SELECT 일잔사건번호, 사건명 FROM 일반사건 WHERE 발생일시 = '20150905'

- 개별 테이블을 모두 조회하는 트랜잭션이 대부분이라는 가정이 있으므로

UNION ALL / UNION 할 경우 성능저하 발생

- 긴급사건, 특수사건, 일반사건을 하나의 테이블로 통합하고 PK를 사건 분류코드+사건번호로 조합하여 구성하도록 한다

UNION ALL : 별도의 중복 제거 과정을 거치지 않고 그냥 결과를 내려준다

UNION ( UNION DISTINCT ) : 중복되는 레코드를 제거하고 내려준다.

48. 논리데이터모델의 슈퍼타입과 서브타입 데이터모델을 물리적인 테이블 형식으로 변환할 때 설명

- 트랜잭션은 항상 전체를 대상으로 일괄 처리 하는데 테이블은 서브타입 별로 개별 유지하는 것으로 변환하면 Union 연산에 의해 성능이 저하 될 수 있다

- 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합하여 변환하면 불필요하게 많은 양의 데이터가 집적되어 있어 성능이 저하 될 수 있다

- 트랜잭션은 항상 슈퍼+서브 타입을 함계 처리하는데 개별로 유지하면 조인에 의해 성능이 저하 될 수 있다

- 트랜잭션은 항상 전체를 통합하여 분석 처리하는 데 슈퍼-서브타입이 하나로 통합되어 있으면 하나의 테이블에 집적된 데이터만 읽어 처리할 수 있기 때문에 다른 형식에 비해 더 성능이 우수하다 

49. FK 에 대한 설명

[수강신청] : {강의번호, 학번} -> {학사기준번호(FK), 성명, 연락처1, 연락처2, 등록년도, 감면코드}

[학사기준] : {학사기준번호} -> {년도, 학기, 특이사항}

- 학사기준번호는 부모 테이블에 이미 인덱스가 존재하나 수강신청과 조인에 의한 성능저하 예방을 위해 상속받아 생긴 수강신청에도 학사기준번호 칼럼에 대한 별도의 인덱스가 필요 O

- 데이터 모델에서는 관계를 연결하고 데이터베이스에 FK 제약조건 생성을 생략하는 경우에도 데이터의 조인 관계가 피요하므로 학사기준번호에 대한 인덱스를 생성할 필요 O

- FK Constraints를 생성했는지 여부와 상관없이 조인 성능을 향상시키기 위한 인덱스를 생성해주는 것이 좋다

52. 분산데이터 베이스 장단점

- 장점

: 지역자치성, 점증적 시스템 용량 확장

: 신뢰성과 가용성

: 효용성과 융통성

: 빠른 응답 속도와 통신비용 절감

: 데이터의 가용성과 신뢰성 증가

: 시스템 규모의 적절한 조절

: 각 지역 사용자의 요구 수용 증대

- 단점

: 소프트웨어 개발 비용

: 오류의 잠재성 증대

: 처리 비용의 증대

: 설계, 관리의 복잡성과 비용

: 불규칙한 응답 속도

: 통제의 어려움

: 데이터 무결성에 대한 위협

- 데이터가 여러 지역에 분산되어 있지만 하나의 데이터베이스처럼 사용하기를 원하는 분산데이터베이스 환경에서 데이터베이스 분산설계를 적용하여 효율성을 증대

: 공통코드, 기준정보 등 마스터 데이터는 분산데이터베이스에 복제 분산을 적용

-> 마스터데이터를 한 곳에 두고 운영하는 경우 원격지에서의 접근이 빈번할 수록 실시간 업무처리에 대해 좋은 성능을 얻기가 어려울 수 있기에 분산데이터베이스에 복제 분산을 적용

: 거의 실시간 업무적인 특성을 가지고 있을 때 분산 데이터베이스를 사용하여 구성 할 수 O

: 백업 사이트를 구성할 때 간단하게 분산 기능을 적용하여 구성할 수 O

Global Single Instance(GSI)는 통합된 한 개의 인스턴스 즉, 통합 데이터베이스 구조를 의미하므로, 분산 데이터베이스와는 대치되는 개념