Post

[SQL] 정규화(Normalization)

Posted
Preview Image
By
21 min read
[SQL] 정규화(Normalization)

데이터 모델링에서 정규화는 가장 기초적이지만 필수적으로 이루어져야하는 작업이다. 성능을 위해 반정규화를 하기도 하지만, 그 이전에 정규화가 왜 필요한지를 반드시 알아야 한다. 다음 몇가지 사례를 통해 정규화가 무엇인지와 그 필요성을 알아보자.

정규화란?

관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다. 데이터베이스 정규화의 목표는 이상이 있는 관계를 재구성하여 작고 잘 조직된 관계를 생성하는 것에 있다. 일반적으로 정규화란 크고, 제대로 조직되지 않은 테이블들과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 것을 포함한다.


정규화의 목적

  • 데이터베이스의 변경시 이상 현상 제거

    테이블 수정(갱신, 삽입, 삭제)시, 원치 않던 부작용이 발생할 수 있다. 이 부작용은 충분히 정규화되지 않은 테이블에서 발생하며, 때문에 충분히 정규화 되지 않은 테이블은 갱신이상, 삽입이상, 삭제이상 문제를 가지고 있다. 그래서 해당 문제들을 해소하기 위해서는 정규화가 필요하다.

  • 데이터베이스 구조 확장시 재 디자인 최소화

    정규화된 데이터베이스 구조에서는 새로운 데이터 형의 추가로 인한 확장시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 되는 경우가 있다. 이는 이 데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 주며, 응용 프로그램의 생명을 연장시킨다.

  • 사용자에게 데이터 모델을 더욱 의미있게 제공

    정규화된 테이블들과 정규화된 테이블들간의 관계들은 현실세계에서의 개념들과 그들간의 관계들을 반영한다. 즉 데이터 모델을 사용자에게 더욱 의미있게 한다.

  • 다양한 질의 지원

    정규화된 테이블은 일반적인 목적의 질의에 적합하다. 이는 테이블에 대하여 세부사항이 예측되지 않은 장래의 질의를 포함한 어떠한 질의도 지원한다는 의미이다. 반대로 정규화되지 않은 테이블은 (향후 발생할 수 있는) 어떤 질의들은 지원하지 않을 수 있다.


정규화의 장단점

장점

  • 데이터베이스 변경 시 이상 현상(Anomaly)을 제거할 수 있다.
  • 정규화된 데이터베이스 구조에서는 새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 된다.
  • 데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되어 응용프로그램의 생명을 연장시킨다.

단점

  • 릴레이션의 분해로 인해 릴레이션 간의 JOIN연산이 많아진다.
  • 질의에 대한 응답시간이 느려질 수도 있다. 데이터의 중복 속성을 제거하고 결정자에 의해 동일한 의미의 일반 속성이 하나의 테이블로 집약되므로 한 테이블의 데이터 용량이 최소화되는 효과가 있다. 
  • 따라서 데이터를 처리할 때 속도가 빨라질 수도 있고 느려질 수도 있다.
  • 만약 조인이 많이 발생하여 성능저하가 나타나면 반정규화(De-normalization)를 적용할 수도 있다.


정규형

정규형은 정규화된 결과를 말한다. 정규형은 기본 정규형과 고급 정규형으로 나뉘며, 상세한 내용은 아래와 같다.

기본 정규형

기본 정규형에는 제 1 정규형(1NF), 제 2 정규형(2NF), 제 3 정규형(3NF), 보이스/코드 정규형(BCNF)이 있다.

  • 제 1 정규형(1NF)

    릴레이션에 속한 모든 속성의 도메인이 더 이상 분해되지 않는 원자값으로만 구성된 정규형이다.

    제 1 정규형의 예

    제 1 정규형의 예
  • 제 2 정규형(2NF)

    릴레이션이 제1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제2정규형에 속한다. 제1정규형에 속하는 릴레이션이 제2정규형을 만족하려면, 부분 함수 종속을 제거하고 모든 속성이 기본키에 완전 함수 종속되도록 릴레이션을 분해하는 정규화 과정을 거쳐야 한다.

    제 2 정규형의 예

    제 2 정규형의 예

    알고가기

    • 완전 함수 종속
      어떤 속성이 기본키에 대해 완전히 종속일 때
    • 부분 함수 종속
      어떤 속성이 기본키가 아닌 다른 속성에 종속되거나, 기본키가 여러 속성으로 구성되어 있을경우 기본키를 구성하는 속성 중 일부만 종속될 때
  • 제 3 정규형(3NF)

    릴레이션이 제2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제3정규형에 속한다.

    제 3 정규형의 예

    제 3 정규형의 예

    알고가기

    • 이행적 함수 종속
      • A → B , B → C 인 경우 A → C 가 성립될 때
      • 즉, A를 알면 B를 알고 그를 통해 C를 알 수 있는 경우를 의미
  • 보이스/코드 정규형(BCNF)

    릴레이션의 함수 종속 관계에서 모든 결정자가 후보키이면 BCNF에 속한다.

    하나의 릴레이션에 여러개의 후보키가 존재할 수도 있는데, 이런 경우는 제3정규형까지 모두 만족하더라도 이상 현상이 발생할 수 있다. 이러한 이상현상을 해결하기 위해 제3정규형보다 좀 더 엄격한 제약조건을 제시한 것이 BCNF이다.

    보이스/코드 정규형의 예

    보이스/코드 정규형의 예

고급 정규형

기본 정규형에는 제 4 정규형(4NF), 제 5 정규형(5NF)이 있다.

  • 제 4 정규형(4NF)

    릴레이션이 BCNF를 만족하면서 다치 종속(MVD: Multi Valued Dependency)을 제거하면 제 4 정규형에 속한다.

    제 4 정규형의 예

    제 4 정규형의 예
  • 제 5 정규형(5NF)

    릴레이션이 제4정규형을 만족하면서 후보키를 통하지 않는 조인 종속(JD: Join Dependency)을 제거하면 제 5 정규형에 속한다.

    제 5 정규형의 예

    제 5 정규형의 예


반정규화란?

반정규화는 정규화를 반대로 하는 것으로 역정규화라고도 한다. 정규화는 데이터의 중복을 최소화했다면, 반정규화는 성능을 위해 데이터 중복을 허용하는 것이다. 그러므로 성능이 문제될 때 주로 반정규화에 대해 논의하게 된다. 하지만 반정규화가 항상 성능을 향상시키는가? 아니다. 조회성능을 향상시킬 수 있을지 모르겠으나 그로인한 입력·수정·삭제 성능은 저하될 수 있다. 이 부분을 염두해 두고 반정규화를 진행해야 한다.


반정규화가 성능에 미치는 영향

성능이 향상 될 수 경우

성능저하 정규화 모델의 예

성능저하 정규화 모델의 예

위 이미지는 주문과 결제에 대한 모델이다. 위 모델에서 생소한 속성만 설명하면, 주문 엔터티에서 주문상태 코드는 주문 상태에 대한 코드값으로 ‘주문·취소·반품·교환’ 등의 정보를 관리하고, 결제일시 속성은 실제 결제를 진행한 일시정보를 관리한다. 결제 엔터티에서 결제수단구분코드 속성은 ‘카드결제·계좌이체·핸드폰결제’ 등을 관리하는 코드값이다. 결제수단번호 속성은 결제수단구분코드에서 사용한 실제 ‘카드번호·계좌번호·핸드폰번호’ 등을 관리하는 속성값이다.

자 그럼, 본 모델에서 다음과 같은 요건을 생각해보자. 고객의 편의를 위해 주문서 작성 시 최근 결제 정보를 미리 세팅하여 보여주고 싶다. 실제로 쇼핑몰에 들어가 보면, 최근 사용한 결제 정보가 자동으로 세팅되는 곳이 많다. 이는 고객 경험을 위해 많이 하는 방법이다. 최근 신용카드 정보를 미리 세팅하는 요건일 경우 다음과 같은 SQL을 작성하게 된다.

1
2
3
4
5
6
7
8
9
10

SELECT A.결제수단번호
  FROM (
        SELECT B.결제수단번호
          FROM 주문 A, 결제 B
         WHERE A.주문번호 = B.주문번호
           AND A.고객번호 = 1234
           AND B.결제수단구분코드 = '신용카드'
         ORDER BY B.결제일시 DESC
       ) A
 WHERE ROWNUM = 1;

위 쿼리는 고객번호가 1234인 고객의 주문정보를 결제 테이블과 조인으로 가져온 후, 신용카드 결제 정보를 결제일시로 내림차순 정렬해 최근 1건의 결제수단번호를 가져오는 쿼리이다. 이와 같은 쿼리는 어떠한 성능 문제가 야기될까?

1234고객의 주문 내역이 많을 수록 성능이 나빠지는 문제가 존재한다. 최종결과는 1건을 가져오지만, 주문내역이 많을 수록 해당 주문테이블과 결제테이블의 조인 건수가 증가하게 되며, 조인된 결제정보를 모두 읽고 내림차순 정렬하여 최근 1건의 데이터를 가져온다. 즉 주문내역이 많을 수록 조인에 대한 부하가 증가하여 성능이 나빠지는 구조다. 그렇다면 어떻게 모델을 구성해야 성능 부하를 개선할 수 있을까? 그렇다. 결제 엔터티에 고객번호 속성을 반정규화함으로써 조인에 대한 성능 부하를 개선하는 것이다.

성능개선된 반정규화 모델 예시

성능개선된 반정규화 모델 예시

위 이미지는 앞전의 모델을 반정규화한 것이다. 이에 따른 쿼리는 다음과 같이 수정된다.

1
2
3
4
5
6
7
8
9

SELECT A.결제수단번호
  FROM (
        SELECT B.결제수단번호
          FROM 결제 A
         WHERE A.고객번호 = 1234
           AND A.결제수단구분코드 = '신용카드'
         ORDER BY A.결제일시 DESC
       ) A
 WHERE ROWNUM = 1;

결제 테이블에 ‘고객번호 + 결제수단 구분코드 + 결제일시’ 로 인덱스를 생성하고 ‘Index Range Scan Descending’으로 최종 1건의 데이터만 읽어 결제수단번호를 가져올 수 있다. 최적의 쿼리로 성능 부하를 극적으로 개선할 수 있는 것이다.

이처럼 정규화가 항상 정답인 것만은 아니며, 요건에 따라 반정화를 진행할 수 도 있다. 하지만 반정규화를 남용한다면, 자칫 더 큰 문제를 야기할 수 있다. 기본적으로 정규화를 고려하고 반정규화가 꼭 필요한 대상인지를 검증하고, 다른 방법은 없는지를 검토한 후 반정규화를 적용해야 한다. 즉 반정규화는 꼭 필요한 때에만 적용해야 한다.

성능이 저하될 수 있는 경우

이번엔 반정규화 시 성능이 저하되는 경우를 살펴보자.

정규화한 모델 예시

정규화한 모델 예시

위 이미지는 주문과 배송에 대한 모델이다. 업무적으로 생각해보면 고객이 주문하면 이후 판매자가 배송을 한다. 대부분의 쇼핑몰은 고객이 주문한 주문내역에 대해 배송정보를 조회할 수 있는 기능을 갖추고 있다. 즉 현재 내가 주문한 상품이 어디쯤 배송되었는지를 조회할 수 있는 화면이 존재한다. 이러한 화면을 개발한다고 가정하고 위 모델을 보자. 주문정보는 주문 엔터티에서 가져올 수 있고, 송장번호는 배송 엔터티에서 가져올 수 있다. 즉 주문과 배송 엔터티를 함께 조인해야 상품이 어디쯤 배송되었는지 조회가능하다. 그런데 여기서! 성능을 위해 주문 엔터티에 송장번호를 반정규화를 해보자.

반정규화한 모델 예시

반정규화한 모델 예시

위 이미지는 주문엔터티에 송장번호를 반정규화한 것이다. 지금부터 업무프로세스를 다시 생각해보자. 고객이 주문하면 판매자가 배송을 진행한다고 앞서 설명하였다. 그리고 고객이 주문을 하였다해도 판매자가 그 즉시 배송을 하지않는다. 경우에 따라 판매자의 상품판매 취소가 진행될 수 도 있다. 그렇다면 위와같이 모델을 구성하면 주문이 발생하여 주문번호가 생성되면, 송장번호는 처음에 NULL값이 들어갈 것이고, 배송준비가 완료되어야 갱신을 통해 송장번호가 들어갈 것이다.
어? 정규화한 모델에서는 없었던 갱신로직이 반정규화를 하고나서 추가가 되었네..? 그렇다. 반정규화는 항상 성능을 개선하는 것이 아니라, 새로운 로직을 추가시켜 불필요한 트랜잭션을 일으키게 할 수도 있다. 때문에 반정규화는 업무를 잘 진단하여 적절하게 활용하는 것이 좋다.

읽어주셔서 감사합니다. 😊

Reference
SQL 전문가 가이드 - Kdata 한국데이터산업진흥원
정규화란? - Tistory
데이터베이스 정규화 - wikipedia
데이터베이스 정규화 - Tistory

Language, SQL
sql 정규화 Normalization
This post is licensed under CC BY 4.0 by the author.