[SQLD] 데이터 모델과 SQL (SQLD, D-13)

📌 개인적인 공간으로 공부를 기록하고 복습하기 위해 사용하는 블로그입니다.
정확하지 않은 정보가 있을 수 있으니 참고바랍니다 :😸
[틀린 내용은 댓글로 남겨주시면 복받으실거에요]

Chapter2 데이터 모델과 SQL

정규화

데이터베이스 이상현상

정규화되지 않은 테이블에 데이터를 삽입, 수정, 삭제할 때 데이터의 일관성이 깨질 수 있는데 이를 데이터베이스 이상현상이라고 한다.

1. 삽입 이상: 테이블에 데이터를 삽입할 때 의도하지 않은 정보까지 삽입해야 하는 현상
2. 갱신 이상: 중복 저장되어있는 데이터 중 하나만 갱신하고 다른 하나를 갱신하지 않을 때 나타나는 데이터의 불일치 현상
3. 삭제 이상: 테이블의 특정 데이터를 삭제할 때 의도하지 않은 정보까지 삭제되는 현상

이러한 현상을 방지하기 위해 데이터의 중복을 최소화 하면서 테이블을 보다 잘 조직된 상태로 분해하는 과정이 바로 정규화라고 한다.

정규화를 수행하면 데이터의 입력,수정, 삭제 성능은 일반적으로 올라가고 조회 성능은 조건에 따라 향상될 수 도 있지만 대부분 많은 조인이 발생하면서 하락하게 된다.

이 때, 조회 성능을 올리기 위해서 반정규화를 수행한다.

---

**논리적 데이터 모델링에서 Entity 라고 부르는 것을 물리적 데이터 모델링에서는 Table이라 부르며 관계형 데이터베이스의 경우 Relation 이라고 도 함. (즉 Entity = Table = Relation)

정규화 절차

| 정규화 절차 | Description | | — | — | | 제 1 정규화 | 릴레이션의 속성값이 모두 원자값만으로 구성되어야 한다.

• 중복값을 제거한다.

• 기본키를 설정한다.
  	제 2 정규화	기본키가 2개 이상의 속성으로 이루어진 경우 부분 함수종속성을 제거한다.
  	제 3 정규화	기본키를 제외한 컬럼간의 종속성을 제거한다. 이행 함수종속성을 제거한다.
  	BCNF	기본키를 제외하고 후보키가 있는 경우 후보키가 기본키를 종속시키면 분해한다.

제 1 정규형

• 모든 속성이 하나의 속성값만을 가지고 있으면서 유사한 속성이 반복되지 않는 상태
• 속성의 원자성이 확보되어 있는 경우
• 제 1 정규형으로 만드는 것을 제 1 정규화 (= 1차 정규화)라 한다.
• 1차 정규화 수행 후의 관계
  • 하나의 Entity 내에서 주식별자에 대한 일반 속성은 1:1 관계를 가져야 한다.
  • 만약 1:M 관계가 성립한다면 제 1정규화 대상이다.
  • 하나의 속성이 여러 속성값을 가질 때, 하나의 테이블에 유사한 속성이 반복될 때와 같기 때문
  • 예를 들어 주문 번호, 주문 일시, 주문 제품, 배송 요청 여부가 있을 때 주문 제품이 여러 개 이면 속성값이 여러 개가 되기 때문에 1차 정규화가 필요하다.

제 2 정규형

• 주식별자가 두 개 이상으로 구성된 복합 식별자인 경우, 일반 속성이 주식별자의 일부에만 종속성을 가질 때가 있는데 이를 부분 함수종속성이라고 한다.
• 제 2 정규형은 이런 부분 함수종속성을 제거한 상태이다.
• 또는 속성의 원자성이 확보되어 있고 주식별자가 단일 식별자인 경우라고 할 수 있다.
• 제 2 정규형으로 만드는 것을 제 2 정규화 (=2차 정규화)라 한다.

제 3 정규형

• 주식별자가 아닌 일반 속성 간에 함수종속성이 존재할 때 이를 함수종속성이라 한다.
• 이행 함수종속성이 제거된 상태가 제 3 정규형이며, 제 3 정규형을 수행하는 것을 제 3 정규화 (=3차 정규화) 라 한다.

반정규화

성능 데이터 모델링

성능 데이터 모델링이란 분석/설계 과정에서 부터 데이터베이스의 성능을 충분히 고려하여 데이터 모델링을 수행한다는 개념이며 아래와 같은 절차를 따른다.

1. 데이터 모델링 시 정규화를 정확하게 수행한다.
2. 데이터베이스 용량산정을 수행한다.
3. 데이터베이스에 발생되는 트랜잭션의 유형을 파악한다.
4. 용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.
5. 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등을 수행한다.
6. 성능관점에서 데이터 모델을 검증한다.

반정규화

정규화와 반대로 데이터의 중복을 허용하거나 데이터를 그룹핑하여 조회 성능을 높이는 것을 말한다.

• 조회성능의 향상, 개발과 운영의 단순화가 필요한 경우 반정규화를 진행한다.
• 데이터베이스의 정합성을 낮추어 또다른 문제를 발생시킬 수 있으므로 다른 대안을 충분히 검토 후 수행해야한다.
• 반정규화 수행절차
  • 반정규화 대상 조사: 범위처리 빈도수 조사, 통계성 프로세스 조사, 테이블 조인 개수 등
  • 다른 방법 검토: 뷰테이블 생성, 인덱스 조정, 클러스터링 적용, 응용 애플리케이션에서의 처리 등 대안을 먼저 검토
  • 반정규화 적용: 테이블 반정규화, 칼럼 반정규화, 관계 반정규화 등

1. 테이블 반정규화

1. 테이블 병합
   • 조인의 발생빈도가 높아서 아예 여러 테이블을 하나로 합치는 것이 성능 향상에 효율적일 때는 테이블을 병합한다.
   • 1:1 테이블 병합, 1:M 테이블 병합, 슈퍼타입/서브타입 테이블 병합
   • 1:M을 병합 시에 속성의 개수가 너무 많은 경우 다른 대안을 먼저 찾아야 한다.
2. 테이블 분할
   • 테이블의 특정 속성들에 대해서만 집중적으로 접근 하는 경우 별도의 테이블로 분할
   • 수직분할 : 열을 쪼갠다고 해서 수직 분할
   • 수평분할 (파티셔닝): 인스턴스를 그룹핑 하여 나누어질 수 있도록 분할

3. 테이블 추가

   조회에 필요한 속성을 포함하는 새로운 테이블을 추가

   • 중복 테이블 추가: 다른 업무나 서버에 있는 테이블과 동일한 구조의 테이블을 중복 추가하여 원격 조인을 제거
   • 통계 테이블 추가: 통계값에 해당하는 연산( ex, 평균, 합)을 미리 계산하여 저장하는 별도의 테이블을 추가
   • 이력 테이블 추가: 변경 이력 등 이력 데이터를 관리할 수 있는 테이블을 별도로 추가하는 방법
   • 부분 테이블 추가: 하나의 테이블 내에 특별히 자주 사용하는 속성들만 별도로 모아서 새로운 테이블로 추가하는 방법

2. 컬럼 반정규화

1. 중복 컬럼 추가: 조인 감소를 위해 자주 사용하는 컬럼 중복 추가
2. 파생 칼럼 추가: 트랜잭션 처리 시 계산에 의한 부하 발생을 줄이기 위해 계산 값을 별도의 컬럼으로 미리 추가하는 기법
3. 이력 테이블 칼럼 추가: 대량의 데이터를 처리할 때 조회 조건에 해당하는 기능성 컬럼을 추가하는 기법
4. PK에 의한 칼럼 추가: 복합 의미를 갖는 pk를 단일 속성으로 구성했을 때 PK를 파싱해서 추가 내용을 조회해야 하는 경우 성능 하락이 있을 수 있으므로 이를 일반 속성으로 추가하는 기법
5. 응용 시스템 오동작을 위한 칼럼 추가: 이전 데이터를 임시로 중복하여 보관하는 기법

3. 관계 반정규화

여러 관계를 거쳐 다수의 조인을 통해 처리가 가능하지만 성능저하를 막기 위해 추가적으로 중복된 관계를 맺는 방법

트랜잭션

• 데이터베이스에 데이터를 삽입, 수정, 삭제할 때 원자적으로 하나의 단위로 실행되어야 전체적으로 데이터의 정합성이 깨지지 않는다.
• 데이터베이스에 저장되는 데이터의 정합성이 무엇보다 중요하므로 데이터를 읽고 쓸 때 트랜잭션 단위로 처리되어야 한다.
• 데이터베이스에 데이터를 읽고 쓸 때 한번에 수행되어야 하는 논리적인 작업 단위를 트랜잭션이라 한다.

1. 트랜잭션의 특성

   특성	Description
   원자성 (Atomic)	하나의 트랜잭션으로 묶인 연산들은 모두 실행되든지 아니면 전혀 실행되지 않아야 한다.
   일관성(Consistency)	트랜잭션의 결과는 데이터베이스의 정합성을 깨지 않는 다는 것으로 트랜잭션 이전에 데이터베이스에 오류가 없다면 트랜잭션 이후에도 오류가 없다.
   고립성(Isolation)	트랜잭션은 독립적으로 수행되며 다른 트랜잭션이 실행 중간에 간섭하거나 영향을 미치지 않는다.
   영속성(Durabiltiy)	트랜잭션의 결과는 데이터베이스에 영구적으로 저장되어 유지된다.

2. 트랜잭션의 격리수준이 낮을 때 문제점

   문제점	Description
   Dirty Read	트랜잭션에 의해 수정 되었으나 아직 커밋이 되지 않은 상태에서 다른 트랜잭션이 해당 데이터를 읽게되면 발생하는 데이터 불일치 현상
   Non-Repeatable Read	한 트랜잭션 내에서 같은 쿼리 두 번 실행할 때 그 사이에 다른 트랜잭션이 값을 수정하거나 삭제하면서 첫 번째와 두 번째 쿼리의 실행결과가 달라지는 현상
   Phantom Read	한 트랜잭션 내에서 같은 쿼리를 두 번 실행할 때 그사이에 다른 트랜잭션이 값을 삽입하면서 두 번째 쿼리에서 이전에 없던 레코드가 나타나는 현상

---

Transaction 관련 SQL 명령어 (TCL)

• Commit : 작업을 정상적으로 처리 완료하여 DB에 반영한다.
• ROLLBACK: 작업을 취소하고 이전 상태로 되돌린다.
• SAVEPOINT: ROLLBACK시 부분 작업 취소를 위한 저장점을 지정한다.

본질식별자 vs 인조식별자

• 인조식별자를 만들어 주식별자로 추가하는 경우
  • 장점: 추가 연산 없이 시퀀스나 키 제약조건 등을 통해 주식별자를 생성할 수 있으므로 개발 편의성이 향상될 수 있다
  • 단점: 데이터 중복과 별도 인덱스 생성이 필요하다.

---

정규화/ 반정규화 계속 헷갈렸는데 그래도 이번 기회에 제대로 공부한듯!

Database 카테고리 내 다른 글 보러가기