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

Hyeri Park·2023년 1월 25일

SQLD SQLP sql 데이터모델링 반정규화 정규화

SQL

목록 보기

2/2

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

1) 성능 데이터 모델링

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

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

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

3) *트랜잭션 유형 파악

*CRUD 매트릭스를 보고 파악, 객체지향 모델링을 적용한다면 시퀀스 다이어그램을 이용
입력, 수정, 삭제, 조회에 따라 테이블 데이터 추리 유추
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) : 부분적 함수종속 제거, 복합식별자만 대상

테이블의 모든 컬럼이 완전함수적 종속을 만족한다.
즉, 모든 속성은 반드시 모든 기본키에 종속되어야 한다.
(기본키 일부에만 종속 되어서는 안됨)

주문 테이블
- 주문번호, 상품코드는 복합 기본키이다. (기본키 : {주문번호, 상품코드})
- 상품명은 상품코드에만 종속된다. (상품코드 : 결정자, 상품명 : 종속자)
- 즉, 상품코드는 상품명을 결정하고 상품코드는 상품명에 종속된다 할 수 있다. (상품코드 → 상품명)
- ‘부분함수 종속성’

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

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

🌱 제 2 정규화

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

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

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

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

테이블이 제 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)와 성능

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