SQLD 공부 6일 차 (2)

정규화가 매우 중요한 내용이라 6일 차 공부 내용은 정규화와 나머지 개념들을 분리하여 적고자 합니다.

이번 글은 정규화를 제외한 나머지 복습 내용들입니다.

---

1. 관계와 조인의 이해

- 조인(Join)은 두개의 테이블의 공통된 식별자를 통해 테이블을 하나로 합치는 것을 의미한다.

- 조인은 데이터베이스의 성능 측면에서 부하가 높은 연산이다.

- 옵티마이저(Optimizer)와 인덱스(Index)를 활용하여 조인의 부하를 낮출 수 있다.

 

성능 데이터 모델링의 절차

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

 

---

1. 반정규화가 필요한 상황

- 조회 성능의 향상, 개발과 운영의 단순화가 필요한 상황에서 반정규화가 필요하다.

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

- 데이터베이스의 정합성을 낮추어 문제를 발생시킬 수 있으므로 대안을 검토한 뒤에 실행해야 한다.

 

반정규화의 수행절차

1) 반정규화 대상 조사: 범위처리 빈도성 조사, 통계성 프로세스 조사, 테이블의 조인 개수 등을 조사하여 반정규화 대상을 조사한다.
2) 대안 검토: 뷰 테이블 생성, 인덱스 조정, 클러스터링 등 대안을 우선적으로 검토한다.
3) 반정규화 적용: 테이블 반정규화, 칼럼 반정규화, 관계 반정규화 등 수행

 

---

2. 테이블 반정규화

- 테이블의 조인 빈도가 높아 여러 테이블을 하나로 합치는 것이 성능 향상에 효율적일 때 실행

1:1 병합

1:M 병합

- 1:M 병합의 경우 속성의 수가 너무 많을 때 많은 수의 중복이 발생하므로 대안을 먼저 찾아야한다.

 

 

 

 

One to One Type

Plus Type

Single Type

 

One to One Type:

- 개별로 발생하는 트랜잭션에 대해서 개별 테이블로 구성

- 슈퍼타입과 서브타입을 조회하는 경우가 둘 다 존재할 경우 사용

 

Plus Type:

- 서브타입을 조회할 때 슈퍼타입을 함께 조회할 경우

- 슈퍼타입을 서브타입에 병합하여 사용

- 해당 서브타입에 해당하지 않는 특성의 경우 Null 값을 부여

 

Single Type:

- 전체를 하나의 테이블로 통합하여 구성

- 대부분의 케이스를 함께 다룰 경우 사용

 

 

- 슈퍼타입: 여러 개의 개별 개체들이 공통으로 가지는 속성을 모아 만든 상위 개체

- 서브타입: 슈퍼타입을 기반으로 각 개체별로 특화된 속성을 가지는 하위 개체

 

---

3. 테이블 분할

- 특정 속성들에 대해서만 집중적으로 접근하는 경우 별도의 테이블로 분할을 실행한다.

 

1. 수직 분할

- 특정 속성들에 대해서만 접근이 빈번할 경우 해당 속성들을 별도의 테이블로 분할하여 1:1 관계로 만드는 방법

- 로우 체이닝(Row Chaining)이 발생할 경우 우선적으로 고려한다.

- 로우 체이닝(Row Chaining)이란?
테이블에 너무 많은 칼럼(Row이 존재할 경우 한 행의 크기가 너무 커서 디스크의 데이터 블록 하나에 다 저장되지 못하는 형태

 

 

 

2. 수평 분할(파티셔닝)

- 속성의 값에 따라서 구분하여 조회하는 경우 사용

- 인스턴스들을 그룹핑하여 나누어질 수 있도록 분할하는 방법

- 분할된 테이블은 기존의 테이블과 동일한 구조를 가지고 있다

 

 

---

4. 테이블 추가

1. 중복 테이블 추가

- 다른 업무나 서버에 있는 테이블과 동일한 구조의 테이블을 중복해서 추가하여 원격 조인을 제거하는 방법

 

2. 통계 테이블 추가

- SUM, AVG 등 통계갑에 해당하는 연산을 미리 계산하여 저장하는 별도의 테이블을 추가하는 방법

 

3. 이력 테이블 추가

- 이력 데이터를 관리할 수 있는 별도의 테이블 추가

 

4. 부분 테이블 추가

- 디스크 I/O를 줄이기 위해 하나의 테이블 내에 자주 사용되는 속성들만 별도로 모아서 새로운 테이블로 추가하는 방법

I/O란?
Input 과 Output이라는 의미

 

---

4. 칼럼 반정규화

1. 중복 칼럼 추가

- 조인을 감소시키기 위해 자주 사용되는 칼럼을 중복 추가하는 기법

- 가독성을 위한 것으로 외래키가 아닌 일반 속성으로 추가하여 별도의 관계생성은 하지 않는다.

 

 

'회원번호' 칼럼을 상품 리뷰에 추가하여 기존에 상품 리뷰를 작성한 회원을 알기 위한 방법(상품리뷰(상품코드) 상품정보(상품리뷰, 회원 코드) 회원정보(회원코드)를 사용한 조인)을간편화 하였다.

 

2. 파생 칼럼 추가

계산 값을 별도의 칼럼으로 미리 추가하는 방법

 

 

기존에 구매가격을 알기 위해선 '구매목록'에서 상품코드를 통해 조인된 결과들을 다 SUM하는 과정이 필요했지만, 계산된 결과를 구매목록에 구매가격이라는 칼럼을 추가하여 시간 및 성능을 향상시켰다.

 

3. 이력테이블 추가

이력테이블 조회 조건에 해당하는 기능성 칼럼 추가하는 기법

'상태정보'를 통하여 배송정보의 이력을 조회함에 있어서 '배송중', '배송완료', '반품' 등 특정 상태에 따른 이련 정보만 조회가 가능하다.

 

4. PK에 의한 칼럼 추가

PK를 단일 속성으로 구성했을 때 PK를 파싱해서 추가적인 내용을 조회해야하는 경우 성능 하락을 피하기 위하여 일반속성으로 추가하는 기법

 

주문정보가 언제 주문된 정보인지 알기 위해선 주문번호 파싱을 통해 주문일자를 분리해야만 가능했습니다. 이를 주문일자 칼럼을 일반속성으로 추가하여 이 과정을 단축했습니다.

 

5. 응용 시스템 오동작을 위한 칼럼 추가

잉용 시스템에서 데이터 처리 도중 오류가 발생한 경우 처리를 하기 위해 이전 데이터를 임시적으로 중복하여 보관하는 것

 

∴ 백업과 유사한 개념

---

5. 관계 반정규화

여러 관계를 거쳐 다수의 조인을 통해 처리가 가능하지만 이때 발생할 수 있는 성능 저하를 예방하기 위해 추가적으로 중복된 관계를 맺는 것

 

회원정보와 상품정보를 동시에 보기 위해선 기존에는 불필요한 주문, 주문목록, 재고 등의 테이블과의 조인을 통해야만 가능했지만, 상품 정보에 회원코드를 추가하여 단계를 감소시켰다.

---

6. 트랜젝션의 이해

- 논리적인 작업 단위

- 데이터베이스는 하나의 단위(트랜잭션)로 데이터의 조작이 발생하고 하나의 일관된 상태에서 다른 일관된 상태로 변환된다

 

TCL: 트랜잭션 관련 SQL 명령
COMMIT: 작업을 정상적으로 처리 완료하여 DB에 반영한다
ROLLBACK: 작업을 취소하고 이전상태로 되돌린다
SAVEPOINT: ROLLBACK시 저장점을 지정한다

 

트랜잭션의 특성

1) 원자성(Atonomicity)

- 하나의 트랜잭션으로 묶인 연산은 모두 실행되거나 전혀 실행되지 않아야한다

 

2) 일관성(Consistency)

- 트랜잭션의 결과는 데이터베이스의 정합성을 깨지 않는다.

- 트랜젝션 이전의 데이터베이스에 오류가 없다면 트랜잭션의 실행 후에도 오류가 없다

 

3) 고립성(Isolation)

- 트랜잭션은 독립적으로 실행된다

 

4) 영속성(Durability)

- 트랜잭션의 결과는 영구적으로 저장되어 유지된다

 

 

트랜잭션의 격리수준이 낮을때 발생하는 문제

1) Dirty Read

: 트랜잭션이 수정되었으나 커밋되지 않은 상태에서 다른 트랜잭션이 해당 데이터를 읽을 떄 발생하는 데이터 불일치 현상

 

2) Non-Repeatable Rea

: 한 트랜잭션 내에서 같은 쿼리를 두 번 실행 할 때 그 사이 다른 트랜잭션이 값을 수정하거나 삭제하면서 두 쿼리의 결과가 달라지는 현상

 

3) Phantom Read

: 한 트랜잭션 내에서 같은 쿼리를 두 번 실행 할 때 그 사이 다른 트랜잭션이 새로운 값을 추가하여 두 번째 쿼리에서 이전에 없던 레코드가 생기는 현상

---

7. Null 속성의 이해

 

- Null이란 정해지지 않은 값을 의미한다(0이 아님)

 

1. 단일행 연산 (예시: 사칙연산)

- Null 값과의 연산수행 결과는 Null

- 하나의 인스턴스에 대한 계산

 

2. 다중행 연산 (예시: 통계)

- Null 값인 행을 제외하고 연산 수행

- 여러 인스턴스에 대한 계산

 

예시 문제

 

SELECT sum(국어+영어+수학+과탐+사탐) FROM 과목;
1. 각각의 단일행 연산 실행
-> 50, Null, Null, Null
2. 단일행 연산 결과를 다중행 연산 실행
-> 50

SELECT sum(국어)+sum(영어)+sum(수학)+sum(과탐)+sum(사탐) FROM 과목;
1. 각각의 다중행 연산 실행
->37, 24, 12, 24, 39
2. 다중행 연산 결과를 단일행 연산 실행
->136