SQL 이론 - 1과목 2장 (3)

1. 반정규화   

 

- 정규화했다가 성능땜에 다시 거꾸로 돌아가는 것

- 정규화된 엔터티, 속성, 관계에 대해 중복, 통합, 분리 등을 수행

- 조인에 의한 성능 저하를 막기 위해 수행

2. 반정규화 절차

 

- 반정규화 대상조사

 

1) 프로세스 수가 많고 일정범위 조회하는 경우

 

2) 대량의 데이터 범위를 처리하는 경우

 

3) 통계성 정보를 필요한 경우

 

4) 테이블 조인 많아져서 조회작업 어려운 경우

 

 

- 다른 방법유도 검토

 

1) VIEW 사용

: 많은 조인이 걸려 데이터 조회 작업이 어려울 경우 VIEW를 사용하면 해결할 수도 있다

  (VIEW 자체가 성능향상X)

 

2) 클러스터링

: 대량의 데이터처리로 성능이 저하되는 경우 클러스터링 적용 or 인덱스 조정

  (조회가 대부분일 때 클러스터링 적용)

 

3) 파티셔닝

: 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리가능, 파티셔닝 키에 의해 물리적 저장공간 분리

 

4) 캐시

: 데이터 처리를 위한 로직 변경

 

 

** 인덱스 조정 **

 

- 인덱스는 PK순서대로 정렬 (PK는 자주 조회되는 순서로 만들 것)

 

- FK 인덱스 설정했을 경우의 효과 생각하기

 

 

3. 칼럼 반정규화 (중-파-이-P-응)

 

- 중복칼럼 추가

: 조인 감소를 위해 중복칼럼

 

- 파생칼럼 추가

: 계산에 의한 성능저하 때문에 미리 계산

 

- 이력테이블 칼럼 추가

: 대량 데이터처리, 최근값, 불특정 날 조회 예방

: 최근값 여부, 시작과 종료일, 등 칼럼에 추가

 

- PK에 의한 칼럼 추가

: 복합적 의미 PK에 단일속성인 경우

 

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

: 원래 값으로 복구할 때 이전 데이터 임시 보관

 

 

[파생칼럼 추가 예시]

 

- 칼럼에 의한 파생

 

 

- 빨간 네모칸은 없어도 되지만 자주 사용될 것 같아 파생 칼럼 추가

 

- 식별자 외에 다른 속성이 존재 하지않으면 안돼 하지만 연관엔터티는 예외

 

* 연관엔터티 = <주문목록>

: 속성을 나타내는게 중요하지 않음

: 각각의 엔터티들을 연결시켜주는 역할만 한다

 

 

[중복칼럼 추가 예시]

 

 

    : 비식별자관계

    : 정규화된 상태

    : 반정규화 필요

    : 제 3 정규형

 

 

- 사원 이름과 소속 지점명을 조회하는 질의가 많을 경우

 

- <사원>에서 이름, 지점번호를 찾고 다시 <지점>으로 가서 지점명 찾기 = 두 개의 테이블이 필요 = 조인관계

 

                                           

                                                                     반정규화 실행 후

                                                                              ⇩
 

 

 

    : 제 2 정규형

    : 이행함수종속

    : 반정규화 완료

 

 

 

- 중복되더라도 지점명을 <사원> 일반속성에 복사

 

- PK가 1개 = 2차 정규화 필요 X = 제 2 정규형

 

-일반속성끼리 종속성 O = 3차 정규화 필요

 

- 이름을 안다고 경력 알 수 X = 종속 X = 3차 정규화 필요

 

- 지점번호 알면 지점명 알 수 O = 종속 O

 

 

4. 테이블 반정규화 (병-분-추)

- 테이블 병합

 

1) 1:1 타입

 

 

: 위가 부모, 아래가 자식

: 부모의 엔터티는 자식들이 공통적으로 포함된다

: 부모의 식별자만 자식들이 그대로 물려받고 남은 일반 속성들은

  각자 다르다

: 개별 엔터티 접근 트래잭션이 많을 경우

= one to one type

 

 

 

 

2) 슈퍼+서브타입

 

: 슈퍼+서브 접근 트래잭션이 많을 경우

: <학생>+<학부생> 같이 물어보거나 <학생>+<대학원생> 같이 물어보거나

= plus type

 

 

3) All in one 타입

 

: 여러서브 타입에 접근이 많을 때

= single type

 

 

- 테이블 분할 절차

 

1) 데이터모델링 수행

 

2) DB 용량 산정

 

3) 대량데이터 테이블에 대해 트래잭션 패턴 분석

 

4) 트래잭션 칼럼 단위로 집중 = 수직분할

    트래잭션 로우 단위로 집중 = 수평분할

    - 컬럼 많음 -> 1:1 분리

    - 데이터 많음 -> 파티셔닝

 

* 칼럼 = 열 , 로우 = 행

 

* 수평분할

: 테이블이 많은 데이터를 갖는 경우

: 파티셔닝 필요

: Range Partition (범위로 분할)

: List Partition (값으로 분할)

: Hash Partition (해쉬 함수로 분할)

 

 

- 테이블 추가

 

1) 부분테이블 추가

: 집중적으로 자주 사용되는 속성을 별도 테이블에 추출

: “테이블 수직분할”과 차이점

= 부분테이블은 추가는 원본테이블이 그대로 유지된다 (분할과 추출 차이)

 

 

 

2) 이력테이블 추가

 

 

 

     : 최근에 변경된 값을 자주 조회

       = 이력테이블 추가로 최근값을 중복되게 복사해!

 

 

                                                                  ⇩

 

 

: “이력테이블 칼럼추가”와 차이점

= 이력테이블 칼럼추가는 최근값을 중복되게

   저장하는 것 X, 최근인지 묻는 여부에 대해서만

   칼럼으로 추가

 

 

 

 

(예시)

 

 

5. 관계 반정규화

 

- 중복관계 추가 : 조인을 통해 조회가 가능하나 조인 경로 단축을 위해 중복관계를 추가 (테이블은 그대로)

 

** 참고 사이트 **

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=5&mod=document&uid=333