SQL자격검정실전문제_데이터 모델과 성능(2)

#1 성능 데이터 모델링에 대해

데이터베이스 성능 향상을 목적으로 설계 단계의 데이터 모델링 때부터 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것 (정규화, 반정규화, 테이블 통합 및 분할, 조인 구 조, PK/FK 설정 등)

 

- 성능 데이터모델링의 수행시점

분석/설계 단계에서 데이터베이스 처리 성능을 향상시킬 수 있는 방법을 고려해야함

 

- 성능 데이터모델링 고려사항

1) 정규화: 무조건해야함 (이상현상, 함수종속 및 정규화)_데이터에 대한 중복성을 제거해준다--> 조회성능향상

    - 이상현상: DB 설계를 잘못했을 때 생김(정규화 필요)

       1) 삭제이상: 어떤 정보를 삭제하면서 다른 정보도 같이 삭제되는 경우

       2) 삽입 이상: key값이 존재하지 않는 학생을 삽입하고 싶을때 key값이 존재하지 않기 때문에 삽입 불가능

       3) 갱신 이상: 학생의 연락처가 변경되었을 경우 자동으로 업데이트되지 않고 하나하나 바꿔줘야할 경우 

    - 함수의 종속성: 데이터들이 어떤 기준 값에 의해 종속되는 현상을 지칭 x->y

       ex. 결정자: 주민등록번호 / 종속자: 이름, 출생지, 주소

       ex. 혈액형 하나에 대해 학번이 여러개: 혈액형은 학번을 함수적으로 결정하지 못함 따라서 학번은 혈액형에 함수적으로 종속되지 않음

       (하나의 학번은 하나의 혈액형을 가질 경우 함수적으로 종속된다고 할 수 있음)

 

    1) 제1 정규화: 모든 값이 원자값을 갖는다

    2) 제2 정규화: 부분함수종속 제거 (함수적으로 종속되는 아이들은 1개만 남기자)_테이블 나눌 수 있으면 2차로 보기

    3) 제3 정규화: 이행함수 종속 제거 (PK가 아닌데 함수 종속을 띄는 경우 제거) 

     * 정규화 효과: 1) 데이터 중복 감소(성능 향상) 2) 데이터가 관심사 별로 묶임(성능 향상) 3) 조회 질의에서 조인이 많이 발생(성능 저하)

    *일반적으로 정규화가 잘되어있으면 입력/수정/삭제의 성능이 향상되고 반정규화를 많이하면 조회의 성능이 향상된다

 

2) 반정규화: DB용량 산정과 트랜잭션 유형 파악을 통해 시행 _(정규화를 되돌리기|정규화를 하지 않은 것)

    *특징: 중복성을 살린다 --> 과도한 반정규화는 데이터 무결성을 침해하게 됨

   (테이블/칼럼/관계 반정규화, 인덱스 특성을 고려한 PK/FK 설정)

 

- 성능 데이터모델링 수행절차

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

(-->주요 관심사별로 테이블 분산)

2) 데이터베이스 용량산정을 수행한다

(-->각 엔터티에 어느정도의 트랜잭션이 들어오는지 파악/hot한 엔터티에 초점을 맞춰서 수정 진행)

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

(-->CRUD(create, revise, update, delete) 매트릭스 활용, 어떤 트랙잭션을 사용하고 있는가? ex.수정,갱신)

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

(-->테이블, 속성, 관계 변경/ 앞단의 과정이 수행된 후 수행되야됨)

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

(물리적인 데이터 모델링 단계에서/ 이력데이터는 시간에 따라 반복적으로 발생이 되기 때문에 대량 데이터일 가능성이 높아 특별히 성능을 고려하여 칼럼 등을 추가하도록 설계해야함)

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

 

#2  반정규화

 

- 반정규화의 정의

정규화된 엔터티, 속성, 관계에 대해 시스템의 성능 향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법

 

- 반정규화 대상 조사

1) 값이 아닌 범위 처리인 경우

2) mean, sum, avg등을 고려해야하는 경우

3) 테이블 조인 갯수가 많을 경우

 

- 다른 방법 유도 검토

1) view (= 테이블을 만드는 코드)를 저장시켜놓기

2) 클러스터링: 자주 사용되는 테이블의 데이터를 디스크의 같은 블록에 저장 (여러 저장 공간에 접근하지 않아도 됨)

3) 인덱스 조정: 순서의 중요성

     - 앞쪽에 위치한 속서의 값이 비교자로 있어야 효율이 좋음. 가급적 '=' 아니면 BTWEEN

     - FK의 순서를 인덱스 특징에 맞게 고려되어 생성해야함(인덱스의 범위를 넓게 이용-->full scan을 유발하여 성능 저하)

4) 응용어플리케이션: 데이터 처리를 위한 로직 변경 (ex. 어떤 학생을 찾을 때 성별, 학번, 이름을 찾는 경우 학번만 찾으면 나머지 찾을 수 있도록 만드는 것) 

 

- 반정규화 적용_마지막에 적용됨!

1) 테이블의 반정규화가 필요한가?

2) 속성의 반정규화가 필요한가?

3) 관계의 반정규화가 필요한가?

 

- 반정규화 기법

1) 칼럼 반정규화

  - 중복 칼럼 추가: 사람들이 동시에 조회하는 열이 존재할 경우 칼럼추가 (이행함수 종속 일어남: 3정규화-->2정규화)

  - 파생 칼럼 추가: 질의가 예상되는 갑을 미리 계산해서 저장 (로우를 기준으로 파생 만들지말기_순서는 언제든 바뀔 수 O)

    (연결(연관) 엔터티--> 식별자 이외의 일반속성이 존재하지 않는 엔터티로 원래는 바람직하지 않으나, 예외적으로 인정)

  - 이력테이블(모든 이력이 쌓이는 데이터_삭제나 업데이트 없음) 칼럼 추가

    (ex.최근 등록여부 체크하는 열을 만들기)

  - PK의 의미적 분리를 위한 칼럼 추가: 차량번호라는 pk가 있지만 지역 찾는 물음이 많을 경우, 따로 지역을 뽑아서 저장 

  - 데이터 복구를 위한 칼럼 추가 (성능향상X) : 사용자의 실수 또는 응용프로그램의 오류로 인한 데이터 손실 대비

 

2) 테이블 반정규화

  -테이블 병합: 관계병합, 슈퍼/서브타입 병합

    1) 관계 병합: 열에서 많이 조회하는 것이 아닌, 거의 테이블 모든 곳에서 조회가 많다면 그냥 테이블 합치기

        (3정규형-->2정규형)

    2) 슈퍼/서브타입 모델의 변환: 일반화관계를 표현(부모-자식관계)

 

- one to one type: 개별 트랜잭션이 많은 경우 부모-자식유지

 

 

 

 

 

 

- plus type: 슈퍼타입+서브타입 접근 트랜잭션이 많은 경우

 

 

 

 

 

-single type: 여러 서브타입에 대한 동시 접근이  많을 때 굳이 부모를 두지 않고 전부 다 합침

 

*대량의 데이터

 -로우 체이닝: 로우의 길이가 너무 길어 데이터 블록 하나에 데이터를 모두 저장하지 않고 두개 이상의 블록에 걸쳐서 하나의 로우가 저장되는 형태

  - 로우마이그레이션: 데이터 블록에서 수정이 발생하면, 수정된 데이터를 해당 데이터 블록에 저장하지 못하고 다른 블록의 빈공간을 찾아서 저장하는 방식

 

-테이블 분할:

  1) 수직 분할: 트랜잭션이 칼럼 단위로 집중되는 경우 

  2) 수평분할: 트랜잭션이 로우 단위로 집중되는 경우

       - range partition: 1~1000/ 1001~2000 ...

       - list partition: 값으로 분할 (지역: 서울, 대구, 부산..)

       - hash partition: 해쉬 함수로 분할  (ex. 123456 -->56 이렇게 짧은 길이 데이터로 매핑하는 함수)

 

-테이블 추가: 중복 테이블 추가, 통계 테이블 추가, 이력 테이블 추가, 부분 테이블 추가(자주이용하는 칼럼들을 별도로 모아놓는 반정규화 기법)

 

3) 관계 반정규화

  -중복관계 추가: 조인을 통해 정보 조회가 가능하지만, 조인 경로를 단축하기 위해 중복 관계를 추가

 

Q) 칼럼수가 많은 테이블에 대한 설명으로 적절한 것? --> 디스트 I/O가 대량으로 발생하여 생기는 성능저하를 의미

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

 

Q) 하나의 테이블에 많은 양의 데이터가 저장되면 인덱스를 추가하고 테이블을 몇개로 쪼개도 성능ㅇ이 저하되는 경우가 있다. 이때 논리적으로는 하나의 테이블이지만, 물리적으로는 여러개의 테이블로 분리하여 데이터 액세스 성능도 향상시키고, 데이터 관리방법도 개선할 수  잇도록 테이블에 적용하는 기법을 무엇이라하는가?

A) 파티셔닝

 

# 3 분산 데이터 베이스 <-->Global Single Instance(GSI) 통합 데이터 베이스

- 장점

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

2) 신뢰성과 가용성

3) 효용성과 융통성

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

5) 데이터의 가용성과 신뢰성 증가

6) 시스템 규모의 적절한 조절

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

 

-단점

1) 소프트웨어 개발 비용

2) 오류의 잠재성 증대

3) 처리 비용의 증대

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

5) 불규칙한 응답 속도

6) 통제의 어려움

7) 데이터 무결성에 대한 위협

*데이터 무결성? 데이터의 정확성, 일관성, 유효성이 유지되는 것을 의미(정확성이란, 중복이나 누락이 없는 상태/ 일관성이란, 원인과 결과의 의미가 연속적으로 보장되어 변하지 않는 상태를 뜻함)