SQL 개발자 (SQLD) 자격증 따기 Part 1 섹션 2.

데이터 모델과 성능

- 정규화 기법

- 반 정규화 기법

 

정규화 : 기본 목표 테이블 간 중복 데이터를 허용하지 않겠다. ~ 무결성 유지, DB 저장공간 절약

- 정규화는 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 방법, 데이터를 분해하는 과정

-> 데이터 중복을 제거하고 데이터 모델의 독립성을 확보하기 위한 방법

- 1 ~ 5 정규화까지 있으나, 실질적으로 3 정규화까지만 수행한다.(4, 5 정규화도 시험에 나올 수는 있다)

 

이상현상(Anomaly) : 정규화 이전 문제가 발생한 상황

이상현상을 해결하기 위해 테이블을 분해해야 한다.

 

제 1 정규화

- 속성의 원자성 확보

- 기본 키 설정

 

제 2 정규화

- 기본 키가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성을 분해 ~ 속성이 1개면 제 2 정규화를 필수로 수행할 필요가 없다

 

제 3 정규화

- 기본 키를 제외한 칼럼 간 종속성 제거 ~ 이행 함수 종속성 분해

 

BCNF

- 기본 키를 제외하고 후보 키가 있는 경우, 후보 키가 기본 키를 종속시키면 분해

 

제 4 정규화

- 여러 칼럼들이 하나의 칼럼을 종속시키는 경우 분해하여 다중 값 종속성 분해

 

제 5 정규화

- 조인에 의해 종속성이 발생되는 경우 분해

 

함수의 종속성

- 제 1 정규화

정규화는 함수적 종속성을 근거로 한다. 함수적 종속성이란, X -> Y면 Y는 X에 함수적으로 종속된다

~ 회원 ID가 이름을 함수적으로 종속한다.

기본 키를 잡고 원자성을 가지는 것이 제 1 정규화다.

 

- 제 2 정규화

부분 함수 종속성이란, 기본 키가 2개 이상의 칼럼으로 이루어진 경우에만 발생, 기본 키가 하나의 칼럼이라면 제 2 정규화는 생략 가능

~ 회원 ID가 이름을 함수적으로 종속하는 데, 테이블의 기본 키는 계좌번호가 하나 더 있다. 이런 경우 제 2 정규화를 통해 테이블을 분리해야 한다.

 

- 제 3 정규화

제 3 정규화는 이행 함수 종속성을 제거한다. 이행 함수 종속성이란, 기본 키를 제외하고 칼럼 간 종속성이 발생하는 것이다. 제 3 정규화는 제 1, 2 정규화 이후 수행해야 한다.

 

- BCNF

BCNF는 복수의 후보 키가 있고, 후보 키들이 복합 속성이어야 하며, 서로 중첩되어야 한다.

=> 모든 결정자가 Key인 경우, 키가 아닌 다른 모든 속성은 후보 키에 대해 함수적으로 종속해야 한다.

https://yaboong.github.io/database/2018/03/10/database-normalization-2/

데이터베이스 정규화 - BCNF

 

제 1 정규화 예제 : 속성을 보고 한 개의 속성으로 유일성을 만족할 수 있는 지 확인한다.

~ 예제 : 제품번호와 주문번호 속성이 중복되는 테이블

-> 하나의 속성으로 유일성을 만족할 수 없다

-> 제품번호 + 주문번호 조합으로는 유일성을 만족하는 식별자가 되므로 제 1 정규화를 달성할 수 있다.

 

제 2 정규화 예제 : 정규화는 데이터 조회 시 조인을 유발하기 때문에 CPU와 메모리를 많이 사용한다.

~ 예제 : 직원 테이블과 부서 테이블이 나눠져있다. 두 테이블의 모든 속성을 조회하려면 조인해야 한다. 

 

정규화의 문제점 예제 : 다수의 테이블을 조인해서 조회하는 경우, 이중 for문을 사용해야 하는데, 이는 비효율을 만들어내므로 인덱스와 옵티마이저를 사용한다. (인덱스, 옵티마이저 : 단답형 문제 빈출)

옵티마이저 : 옵티마이저는 가장 효율적인 방법으로 SQL을 처리하도록 경로를 생성하는 DBMS의 핵심 엔진이다. DBMS의 두뇌 역할을 한다.

인덱스는 RDBMS에서 검색속도를 높이기 위한 기술로, Table 컬럼을 색인화(따로 파일로 저장)해 검색시 해당 Table 레코드를 Full Scan하는 것이 아니라 인덱스 파일을 검색해 속도를 향상한다.

 

결론적으로 조인이 부하를 유발하지만, 반 정규화(역 정규화)를 통해 하나의 테이블에 저장하면 조인으로 나타나는 성능 저하는 해결할 수 있다.

 

정규화를 사용한 성능 튜닝

- 조인으로 인해 성능이 저하되는 문제를 반 정규화로 해결할 수 있다.

~ 반 정규화는 데이터를 중복하므로 완전한 해결방법은 아니다

한 테이블에 너무 많은 데이터가 추가되면 반 정규화도 문제가 발생 ~ 테이블을 분해해야 한다. 따라서 정규화는 입출력 데이터의 양을 줄여 성능을 향상시킬 수 있다.

 

반 정규화

- 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법이다.

- 반 정규화는 조회 속도를 향상시키지만, 데이터 모델의 유연성은 낮아진다.

반 정규화를 수행하는 경우

- 정규화에 충실하면 종속성, 활용성은 향상되지만 수행 속도가 느려지는 경우

- 다량의 범위를 자주 처리해야하는 경우

- 특정 범위의 데이터만 자주 처리하는 경우

- 요약/ 집계 정보가 자주 요구되는 경우

반 정규화 절차

대상 조사 및 검토 : 데이터의 처리 범위, 통계성 등을 확인해서 반 정규화 대상을 조사한다.

다른 방법 검토 : 반 정규화를 수행하지 전에 다른 방법이 있는 지 검토 ~ 클러스터링, 뷰, 인덱스, 튜닝, 응용 프로그램, 파티션 등

반 정규화 수행 : 테이블, 속성, 관계 등을 반 정규화한다.

 

클러스터링 : 클러스터링 인덱스는 인덱스 정보를 저장할 때 물리적으로 정렬 후 저장하는 방법 ~ 조회 시 인접 블록을 연속적으로 읽을 수 있어 조회 속도가 향상된다.

 

반 정규화 기법

1) 계산된 칼럼 추가 : 배치 프로그램으로 각종 계산 후 그 결과를 특정 칼럼에 추가

2) 테이블 수직 분할 : 하나의 테이블을 두 개 이상의 테이블로 분할 ~ 칼럼을 분할해 새로운 테이블을 만든다

3) 테이블 수평 분할 : 하나의 테이블에 있는 값을 기준으로 테이블을 분할

+ 파티션 기법 : 데이터베이스에 파티션을 사용해 테이블 분할 ~ 논리적으로는 하나의 테이블이지만 분산 저장

*시험 빈출

- 레인지 파티션 : 데이터 값의 범위 기준

- 리스트 파티션 : 특정 값을 지정

- 해시 파티션 : 해시 함수를 적용

- 컴포지트 파티션 : 범위와 해시를 복합적으로 사용

파티션 기법의 장점

- 데이터 조회 시 엑세스 범위가 줄어들기 때문에 성능 향상

- 데이터가 분할되어 있기 때문에 입출력 성능 향상

- 각 파티션을 독립적으로 백업/ 복구가 가능

4) 테이블 병합

- 1대 1 관계의 테이블을 하나의 테이블로 병합해서 성능을 향상시킨다

- 1대 N 관계의 테이블을 병합해 성능을 향상시킨다.(데이터 중복 발생 가능)

- 슈퍼 타입과 서브 타입 관계가 발생하면 테이블을 통합해 성능을 향상시킨다

ex) 고객 엔터티

고객 엔터티는 개인 고객과 법인 고객으로 분류된다. 이때 고객 엔터티는 슈퍼 타입이고 개인 고객과 법인 고객이 서브 타입이다. ~ 부모 자식 관계

슈퍼 타입과 서브 타입은 배타적 관계와 포괄적 관계가 있는데, 배타적 관계는 고객이 개인 고객이거나 법인 고객인 경우이고 포괄적 관계는 고객이 개인 고객일 수도 있고 법인 고객일 수도 있는 것을 의미한다

변환 방법

1) 1:1 타입(원투원 타입) : 슈퍼 타입과 서브 타입을 개별 테이블로 도출한다. 조인이 많이 발생하고 관리가 어렵다

2) 슈퍼 + 서브 타입(플러스 타입) : 슈퍼 타입과 서브 타입 테이블로 도출한다. 조인이 발생하고 관리가 어렵다

3) 올인원 타입(싱글 타입) : 슈퍼 타입과 서브 타입을 하나의 테이블로 도출한다. 조인 성능이 좋고 관리가 편리하지만, 입출력 성능이 나쁘다

 

분산 데이터베이스

- 데이터베이스 시스템 구축 시 한 대의 물리적 시스템에 데이터베이스 관리 시스템을 설치하고 여러 명의 사용자가 데이터베이스 관리 시스템에 접속하여 데이터베이스를 사용하는 구조를 중앙 집중형 데이터베이스라고 한다. 또한 물리적으로 떨어진 데이터베이스에 네트워크로 연결해 단일 데이터베이스 이미지를 보여주고 분산된 작업 처리를 수행하는 데이터베이스를 분산 데이터베이스라고 한다. 데이터베이스는 투명성을 제공해야 한다. 투명성은 분산 데이터베이스에서 중요한 요소이며, 종류에는 분할, 위치, 지역사상, 중복, 장애 및 병행 투명성이 있다

- 사상은 매핑과 유사한 개념. 특정 스키마가 바뀌어도 매핑을 다시함으로 다른 계층의 스키마 내용이 변하지 않아도 된다

출처 : 강의자료

분산 데이터베이스의 설계 방식

1) 상향식 : 지역 스키마 작성 후 전역 스키마를 작성

2) 하향식 : 전역 스키마 작성 후 지역 스키마를 작성

분산 데이터베이스를 하향식 접근방식으로 구축한다는 것은 기업 전체의 전사 데이터 모델을 수렴해 전역 스키마를 생성하고 각 지역별 스키마를 생성해 분산 데이터베이스를 구축한다는 의미이다. 상향식은 지역 DB를 구축 후 전역으로 통합하는 것이다

분산 데이터베이스를 구축하거나 운영할 때 동일한 데이터베이스 관리 시스템으로 분산 데이터베이스를 구축하는 것은 크게 어렵지 않다. 하지만 기업에 여러 종류의 데이터베이스 관리 시스템이 있으면 이기종 데이터베이스 관리 시스템으로 연동해야 한다. 이기종 데이터베이스 시스템으로 연동하기 위해서는 데이터베이스 미들웨어(ODBC, JDBC)를 사용해야 한다.

 

장점 : DB 신뢰성과 가용성이 높다. 병렬 처리를 수행하므로 빠른 응답이 가능하다. 시스템 용량 확장이 쉽다.

단점 : DB의 관리 통제가 어렵다. 보안 관리 어려움. 데이터 무결성 관리 어려움. 데이터베이스 설계 복잡도 증가