[SQLP] 데이터 모델과 성능 1

Hyeri Park·2023년 1월 25일
0

SQL

목록 보기
2/2
post-thumbnail
post-custom-banner

1. 성능 데이터 모델링 개요

1) 성능 데이터 모델링

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


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

  1. 데이터 모델링을 할때 정규화를 정확하게 수행한다. (분산효과)
  2. 데이터베이스 용량 산정을 수행한다. [테이블 (엔티티) 데이터 집중 파악]
  3. 데이터베이스에 발생되는 트랜잭션의 유형을 파악한다.
  4. 용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.
    (테이블, 속성, 관계에 대한 포괄적인 반 정규화)
  5. 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등을 수행한다.
    (우수한 컬럼순위 지정)
  6. 성능 관점에서 데이터 모델을 검증한다.

3) *트랜잭션 유형 파악

  1. *CRUD 매트릭스를 보고 파악, 객체지향 모델링을 적용한다면 시퀀스 다이어그램을 이용
  2. 입력, 수정, 삭제, 조회에 따라 테이블 데이터 추리 유추
  3. SQL문 조인관계 테이블에서 데이터 조회의 컬럼 파악에 따른 성능고려

✔️ CRUD 매트릭스란?

2차원 형태의 표로서, 행에는 프로세스를, 열에는 테이블을, 행과 열이 만나는 위치에는 프로세스가 테이블에 발생시키는 변화를 표시하는 업무 프로세스와 데이터 간 상관 분석표이다.

2. 정규화와 성능

  • 데이터 중복(이상현상)을 최소화 하기 위해 릴레이션을 분해하는 과정
  • 제 1정규형 ~ 제 6정규형, BCNF가 있지만 대체적으로 실무에서는 대체로 1~3 정규화까지의 과정을 거친다.
  • 정규화는 논리적 설계 단계에서 수행
  • 정규형의 차수가 높아질수록 데이터 중복이 줄어 이상현상이 발생하지 않는 릴레이션이 됨
  • 즉, 정규형의 차수가 높아질수록 제약조건이 높다.

✔️ 이상현상 ?

불필요한 데이터 중복으로 인해 릴레이션에 대한 데이터 삽입, 수정, 삭제 연산을 할 때 발생할 수 있는 부작용


1) 정규화 수행과 성능

  • 조회 성능 : 처리 조건에 따라 향상될 수 있고 아닐 수 있다.
  • 입력, 수정, 삭제 성능 : 성능이 향상 된다.

2) 정규화

(1) 제 1정규화 (1NF) : 종속 속성 제거, 일반 속성 대상

릴레이션에 속한 모든 속성의 도메인이 원자값으로만 구성되어 있어야 한다.
즉, 모든 속성은 반드시 하나의 값만 가져야 한다.

테이블(Relation)이 제 1정규형을 만족했다는 것은 아래 세 가지 조건를 만족했다는 것을 의미한다.

  • 어떤 Relation에 속한 모든 Domain이 원자값(atomic value)만으로 되어 있다.
  • 모든 attribute에 반복되는 그룹(repeating group)이 나타나지 않는다.
  • 기본 키를 사용하여 관련 데이터의 각 집합을 고유하게 식별할 수 있어야 한다.

예시 1. 다중값을 가진다 (1번 사항 위배)

  • 비정규화 테이블
    • 고객아이디, 이름은 복합 기본키 (기본키 : {고객아이디, 이름})

예시 2. 반복 그룹을 가진다 (2번사항 위배)

  • 비정규화 테이블
    - 고객아이디, 이름은 복합 기본키 (기본키 : {고객아이디, 이름})

🌱 제 1 정규화

단, 기본키가 유일하지 않다는 것을 알 수 있다. 
따라서 3번 조건을 만족하기 위해서 아래와 같이 테이블을 나누어 디자인 하는 것이 좋다. 
(예시가 전화번호 테이블이기 때문에 실 업무에서는 더 생각해 볼 필요가 있음)
  • 정규화 테이블 (1정규화)

위 테이블은 고객정보 테이블과 고객 전화번호 테이블이 1 : N 관계를 형성하는 것을 알 수 있다.


(2) 제 2정규화 (2NF) : 부분적 함수종속 제거, 복합식별자만 대상

  • 테이블의 모든 컬럼이 완전함수적 종속을 만족한다.
    즉, 모든 속성은 반드시 모든 기본키에 종속되어야 한다.
    (기본키 일부에만 종속 되어서는 안됨)
  • 주문 테이블
    - 주문번호, 상품코드는 복합 기본키이다. (기본키 : {주문번호, 상품코드})
    - 상품명은 상품코드에만 종속된다. (상품코드 : 결정자, 상품명 : 종속자)
    - 즉, 상품코드는 상품명을 결정하고 상품코드는 상품명에 종속된다 할 수 있다. (상품코드 → 상품명)
    - ‘부분함수 종속성’

위 주문 테이블은 이상현상이 발생한다.

  1. 입력이상 : 주문이 발생하지 않으면 상품명이 입력 불가
  2. 수정이상 : 상품명이 변경될 경우 해당되는 주문 ROW UPDATE 필요
  3. 삭제이상 : 상품 삭제시 주문까지 삭제

🌱 제 2 정규화

주문과 관계없이 상품의 데이터입력, 수정, 삭제가 가능하다.
따라서 현재 테이블의 핵심 주제와 상관없는 컬럼을 제거하면 된다.



(3) 제 3정규화 (3NF) : 이행함수 종속성 제거

기본키가 아닌 모든 속성간에는 서로 종속될 수 없다.

테이블(Relation)이 제 3정규형을 만족한다는 것은 아래 두 가지 조건을 만족하는 것을 의미한다.

  1. 테이블이 제 2정규형을 만족해야한다.
  2. 테이블 내의 모든 속성이 기본키에만 의존해야하며, 다른 후보키에 의존하지 않는다.

즉, 이행적 함수종속은 기능적 종속으로 X⟶ Y 이고 Y⟶ Z 에 의해서 X⟶ Z (X가 Z를 결정한다) 가 되는 것이다.

  • 상품 테이블
    - 상품코드는 유일한 기본키이다. (기본키 : 상품코드)
    - 제공업체는 상품코드에 종속되지 않고 상품명(후보키)에 종속된다.
    - 데이터 이상 현상 발생 가능!

🌱 제 3 정규화

제공업체는 주문번호(기본키)와 상관이 없는 컬럼이기 때문에 제거하면된다.

(4) BCNF 정규화 : 결정자가 후보키가 아닌 함수 종속성 제거 → BCNF를 만족한다.

릴레이션의 함수 종속관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속한다.

결정자가 후보키가 아니고 종속자가 기본키의 부분집합인 속성을 분해
BCNF 정규형인 모든 릴레이션은 제 3정규형에 속한다. 그 역은 성립되지 않는다.

  • 주문테이블

제공업체가 상품을 제공하겠다고 결정해야 주문번호가 일어난다.
만약 주문이 일어났는데 제공업체가 상품을 제공하지 않는다면 주문을 받을 수가 없다.
즉, 제공업체도 후보키가 되어야한다. (결정자가 후보키가 아닌 속성 분리)

🌱 BCNF

따라서 상품테이블이 먼저 결정이된 후 주문테이블이 생성될 수 있게 만들어 주는 구조를 BCNF라고한다.

(5) 제 4정규화 (4NF) : 다중값 종속성을 제거한다

하나의 결정자가 다른속성에서 여러 종속자를 결정할 때 다치종속 이라고한다.

한 릴레이션에서 1:N, 1:N 두 개 이상으로 다치종속이 구성되어 있을때 이상현상이 발생한다.
중심이 되는 속성을 기준으로 릴레이션을 분리해줘야한다.

  • 개발자 테이블

🌱 제 4정규화

개발자마다 자격증 값들이 여러개 존재하고, 개발자마다 언어 값들이 여러개 존재하는 경우 다치 종속 관계라고 한다.
이러한 경우 개발자, 자격증 / 개발자, 언어 테이블로 분리하여 관리하며 다치종속 관계를 제거하기 때문에 4차 정규화를 만족한다.

(6) 제 5정규화 (5NF) : 조인 종속성을 제거한다

릴레이션을 셋으로 분해 하여야만 조인을 통해서 원본 조회가 가능하다.

4차정규화 테이블에 대해 조인연산을 수행하면 4차 정규화 수행전 데이터와 다르게 되는 문제인 조인 종속이 발생한다.

  • 4차 정규화 테이블

🌱 제 5정규화

조인 종속 관계를 제거하기 위해서는 모든 속성 관계인 개발자,자격증 / 개발자, 언어 / 자격증, 언어 관계에 대한 테이블을 만들어 줌으로써 조인을 했을때 정확히 원래의 데이터로 복원할 수 있게 한다.

3. 반정규화 (De-Normalization)와 성능

* 중복 데이터를 데이터베이스에 추가하여 데이터 검색에 필요한 조인 수를 줄여 성능을 향상시키는 프로세스
* 정규화된 엔티티, 속성, 관계에 대해 성능 향상과 개발 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법
* 비정규화, 역정규화라고 불린다.
* 조회 성능 향상 - 중복성의 원리를 활용하여 데이터 조회시 성능을 향상시키는 역할을 할 수 있음.
(테이블의 중복성,  컬럼의 중복성, 관계의 중복성)

(1) 반정규화 절차

  1. 반정규화 대상조사

    범위처리빈도수 조사
    대량의 범위처리 조사
    통계성 프로세스 조사(통계성 테이블생성)
    테이블 조인 개수

  2. 다른 방법유도 검토

    뷰테이블
    클러스터링 적용(조회중심- 인덱스로 안될경우)
    인덱스의 조정
    파티셔닝 기법 적용
    응용 애플리케이션
    반정규화 적용(기법)

(2) 반정규화 적용(기법)





profile
Backend Developer
post-custom-banner

0개의 댓글