[SQL] - 정규화, 반정규화, 분산 데이터베이스

SQLD를 준비하며 공부했던 내용으로, 2020 이기적 SQL 개발자(Developer) 이론서 + 기출문제를 참고했다.

정규화

• 정규화: 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 방법이며 데이터를 분해하는 과정, 데이터 모델의 독립성 확보, 정규화를 수행하면 비즈니스에 변화가 생겨도 변경을 최소화 할 수 있음

• 정규화 절차

  1) 제1정규화: 속성의 원자성을 확보, 기본키 설정

  2) 제2정규화: 기본키가 2개 이상의 속성으로 이루어진 경우 부분 함수 종속성을 제거(분해)

  3) 제3정규화: 기본키를 제외한 칼럼 간에 종속성을 제거함, 이행 함수 종속성을 제거함

  4) BCNF: 기본키를 제외하고 후보키가 있는 경우 후보키가 기본키를 종속시키면 분해

  5) 제4정규화: 여러 칼럼들이 하나의 칼럼을 종속시키는 경우 분해하여 다중값 종속성 제거

  6) 제5정규화: 조인에 의해 종속성이 발생되는 경우 분해
  

• 함수적 종속성 (Functional Dependency)

  (1) 제1정규화

  • 정규화는 함수적 종속성을 근거로 함
  • 기본키를 잡는 것

  (2) 제2정규화

  • 부분 함수 종속성이란, 기본키가 2개 이상의 칼럼으로 이루어진 경우에만 발생
  • 기본키가 하나의 칼럼으로 이루어지면 제2정규화는 생략
  • 부분 함수 종속성이 발생하면 분해하고, 새로운 테이블이 도출됨

  (3) 제3정규화

  • 이행 함수 종속성 (기본키를 제외하고 칼럼 간에 종속성이 발생하는 것) 제거

  • 제3정규화는 제1정규화, 제2정규화를 수행한 다음에 해야 함

  • ex. 관리점이 관리점 코드에 종속 되는 것

    (4) BCNF (Boyce-Codd Normal Form)

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

정규화와 성능

• 정규화의 문제점
  • 정규화는 테이블을 분해해서 데이터 중복을 제거하기 때문에 데이터 모델의 유연성을 높임
  • 정규화는 데이터 조회(SELECT)시에 조인(JOIN)을 유발하기 때문에 CPU와 메모리를 많이 사용함
  • 조인의 사용

    SELECT 사원번호, 부서코드, 부서명, 이름, 전화번호, 주소
    	FROM 직원, 부서
    WHERE 직원.부서코드 = 부서.부서코드;

    • 직원과 부서 테이블에서 부서코드가 같은 것을 찾는 것, 프로그램화 하면 중첩된 루프(Nested Loop) 사용

      for(i=0;i<N;i=i+1)
      	for(j=0;j<M;j=j+1)
      		if(직원_부서코드[i] == 부서_부서코드[j]){ }

    • 이러한 구조는 데이터 양이 증가하면 비교해야 하는 건수도 증가하고, 조인이 부하를 유발함

    • 정규화의 문제점을 해결하기 위해서 반정규화를 하여 하나의 테이블에 저장한다면 조인을 통한 성능 저하가 해결됨

• 정규화를 사용한 성능 튜닝
  • 반정규화를 통해 조인으로 인한 성능저하 문제를 해결할 수 있지만 반정규화는 데이터를 중복시키기에 다른 문제점을 발생시킴
  • 조회를 빠르게 할 수는 있지만 너무 많은 칼럼이 추가되면 한 개 행의 크기가 DBMS 입출력 단위인 블록 크기를 넘어서게 됨
  • 그러면 한 개의 행을 읽기 위해 여러 블록을 읽어야하고, 디스크 입출력이 증가하기 때문에 성능이 떨어짐
  • 이런 경우 테이블을 분해함(정규화)
  • 정규화는 입출력 데이터의 양을 줄여서 성능을 향상시킬 수 있는 것

반정규화(De-Normalization)

• 반정규화: 데이터베이스 성능 향상을 위해 데이터중복을 허용하고 조인을 줄이는 방법

• 조회(SELECT)속도를 향상하지만, 데이터 모델의 유연성은 낮아짐

• 반정규화를 수행하는 경우

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

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

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

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

• 반정규화 절차

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

  2) 다른 방법 검토: 반정규화를 수행하기 전 다른 방법(클러스터링, 뷰, 인덱스 튜닝 등)이 있는지 검토

  3) 반정규화 수행: 테이블, 속성, 관계 등을 반정규화함

  • 클러스터링: 인덱스 정보를 저장할 때 물리적으로 정렬해서 저장하는 방법, 조회 시에 인접 블록을 연속적으로 읽기 때문에 성능이 향상됨

• 반정규화 기법

  1) 계산된 칼럼 추가: 총판매액 등을 미리 게산하고 특정 칼럼에 추가함

  2) 테이블 수직분할:하나의 테이블을 두 개 이상의 테이블로 분할, 칼럼을 분할하여 새로운 테이블을 만듦 (칼럼을 나눔)

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

  • 파티션 기법: 논리적으로는 하나의 테이블이지만 여러 개의 데이터 파일에 분산되어 저장됨
  • Range Partition: 데이터 값의 범위를 기준으로 파티션 수행
  • List Partition: 특정한 값을 지정하여 파티션 수행
  • Hash Partition: 해시 함수를 적용하여 파티션 수행
  • Composite Partition: 범위와 해시를 복합적으로 사용하여 파티션 수행
  • 파티션 테이블의 장점: 데이터 조회 시 엑세스 범위가 줄어들어 성능 향상, 데이터가 분할되어 있어 I/O(Input/Output)성능 향상, 각 파티션을 독립적으로 백업 및 복구 가능

  4) 테이블 병합: 1대1 관계의 테이블을 하나의 테이블로 병합해서 성능 향상, 1대N 관계의 테이블을 병합하여 성능 향상(많은 양의 데이터 중복 발생), 슈퍼 타입과 서브 타입 관계가 발생하면 테이블을 통합하여 성능 향상

  • Super type과 Sub type: 고객 엔터티는 개인고객과 법인 고객으로 분류될 때, 고객 엔터티는 슈퍼타입이고 개인고객과 법인고객은 서브 타입임, 부모 자식 간의 관계, 배타적 관계와 포괄적 관계 존재

  • 슈퍼 타입 및 서브 타입 변환 방법

    1) OneToOne Type: 슈퍼 타입과 서브 타입을 개별 테이블로 도출, 테이블의 수가 많아 조인이 많이 발생하고 관리가 어려움

    2) Plus Type: 슈퍼 타입과 서브 타입 테이블로 도출, 조인이 발생하고 관리가 어려움

    3) Single Type: 슈퍼 타입과 서브 타입을 하나의 테이블로 도출, 조인 성능이 좋고 관리가 편리하지만, 입출력 성능이 나쁨
    

분산 데이터베이스

• 중앙 집중형 데이터베이스: 데이터베이스 시스템 구축 시에 한 대의 물리적 시스템에 DBMS를 설치하고 여러 명의 사용자가 DBMS에 접속하여 데이터베이스를 사용하는 구조

• 분산 데이터베이스: 물리적으로 떨어진 데이터베이스에 네트워크로 연결하여 단일 데이터베이스 이미지를 보여 주고 분산된 작업 처리를 수행하는 데이터베이스

• 분산 데이터베이스를 사용하는 고객은 시스템이 네트워크로 분산되어 있는지의 여부를 인식하지 못하며 자신만의 데이터베이스를 사용하는 것처럼 사용할 수 있음, 데이터베이스는 투명성을 제공해야함

• 분산 데이터베이스의 투명성 종류

  1) 분할 투명성: 고객은 하나의 논리적 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 시스템에 저장되어 있음을 인식할 필요 없음

  2) 위치 투명성: 고객이 사용하려는 데이터의 저장 장소를 명시할 필요 없음, 고객은 데이터가 어느 위치에 있더라도 동일한 명령으로 데이터에 접근할 수 있어야 함

  3) 지역 사상 투명성: 지역 DBMS와 물적 데이터베이스 사이의 사상이 보장됨에 따라 각 지역 시스템 이름과 무관한 이름 사용 가능

  4) 중복 투명성: 데이터베이스 객체가 여러 시스템에 중복되어 존재함에도 고객과는 무관하게 데이터 일관성 유지

  5) 장애 투명성: 데이터베이스가 분산되어 있는 각 지역의 시스템이나 통신망에 이상이 발생해도 데이터의 무결성 보장

  6) 병행 투명성: 여러 고객의 응용 프로그램이 동시에 분산 데이터베이스에 대한 트랜잭션을 수행하는 경우에도 결과에 이상이 없음
  

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

  • 상향식 설계 방식: 지역 스키마 작성 후 향후 전역 스키마를 작성하여 분산 데이터베이스 구축

  • 하향식 설계 방식: 전역 스키마 작성 후 해당 지역 사상 스키마를 작성하여 분산 데이터베이스 구축

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

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

  • 분산 데이터베이스 장점

    1) 데이터베이스 신뢰성과 가용성이 높음

    2) 분산 데이터베이스가 병렬 처리를 수행하기에 빠른 응답 가능

    3) 분산 데이터베이스를 추가하여 시스템 용량 확장 용이
    

  • 분산 데이터베이스 단점

    1) 데이터베이스가 여러 네트워크를 통해 분리되어 있어 관리와 통제가 어려움

    2) 보안관리가 어려움

    3) 데이터 무결성 관리가 어려움

    4) 데이터베이스 설계가 복잡함