2-2 정규화와 성능

정규화Normalization

정규화는 최소한의 데이터 중복, 데이터의 일관성, 최대한의 데이터 유연성을 위해 데이터를 분행하는 과정이다. 따라서 데이터 중복을 제거하고 데이터 모델의 독립성을 확보하는 방법이다. 정규화를 통해 업무 상의 변화가 생겨도 데이터 모델의 변경을 최소화할 수 있다. 제1정규화부터 제5정규화까지 있지만 실질적으로는 제3정규화까지만 수행한다.

위 그림의 왼쪽 테이블에서 새로운 직원이 추가된다고 가정하자. 만약 부서 정보가 없으면 부서코드를 임의로 채워넣어야 한다. 즉 불필요한 정보를 꼭 추가해야 한다. 새로운 부서도 마찬가지인데, 사원 정보가 없기 때문에 임의의 값으로 사원번호를 입력하지 않으면 추가할 수 없다. 이를 이상현상Anomaly이라고 하며 테이블 분해가 필요하다.

오른쪽 테이블은 직원정보 테이블을 직원 테이블과 부서 테이블로 분리한 것으로 불필요한 데이터를 입력하지 않아도 되기 때문에 중복 데이터가 제거된다.

정규화 절차

정규화 절차	설명	함수적 종속성
제1정규화	속성의 원자성을 확보하며 PK를 설정한다	-
제2정규화	PK가 2개 이상의 속성으로 이루어진 경우 부분 함수 종속성을 제거한다	부분 함수 종속성
제3정규화	PK를 제외한 칼럼 간 종속성을 제거한다 = 이행 함수 종속성을 제거한다	이행 함수 종속성
BCNF	PK를 제외한 후보키가 있을 때 후보키가 PK를 종속시키면 분해한다	다중 종속성
제5정규화	조인에 의해 종속성이 발생되는 경우 분해한다	Join 종속성

함수적 종속성Functional Dependency과 정규화

1. 제1정규화

정규화는 함수적 종속성을 근거로 한다. 함수적 종속성이란 데이터들이 어떤 기준값에 의해 종속되는 현상이다. 이 때 기준값을 결정자Determinant라 하고 종속되는 값을 종속자Dependent라고 한다.

사람 엔터티는 주민등록번호, 이름, 출생지, 주소라는 속성이 존재한다. 여기서 이름, 출생지, 주소라는 속성은 주민등록번호 속성에 종속된다. 만약 어떤 사람의 주민등록번호가 신고되면 그 사람의 이름, 출생지, 주소가 생성되어 단지 하나의 값만을 가지게 된다. 즉 “주민등록번호가 이름, 출생지, 호주를 함수적으로 결정한다.”라고 말할 수 있다. 이를 기호로 표시하면, 다음과 같이 표현할 수 있다: 주민등록번호 -> (이름, 출생지, 주소)

위의 예시는 X(주민등록번호)가 Y(이름, 출생지, 주소)를 함수적으로 종속한다. 이 때 X가 기본키가 되었다. 이렇게 기본키를 잡는 것이 제1정규화이다.

2. 제2정규화

제2정규화는 부분 함수 종속성을 제거(분해)하는 것이다. 부분 함수 종속성이란 PK가 2개 이상의 칼럼으로 이루어진 경우에만 발생한다. PK가 한 칼럼이면 제2정규화는 생략한다. 즉 위의 예시에서는 제2정규화를 생략한다.

새로운 예시를 보자. PK인 회원 ID가 변경되면 이름도 변경된다. 회원 ID가 이름을 함수적으로 종속하는 것이다. 이런 경우를 부분 함수 종속성이라고 하며 분해가 필요하다.

부분 함수 종속성을 제거하면 위와 같다. 회원이라는 새로운 테이블이 도출되고 회원 ID가 PK가 된다.

3. 제3정규화

제3정규화는 이행 함수 종속성을 제거하는 것이다. 이는 PK를 제거한 칼럼 간에 종속성이 발생하는 것으로 제1정규화와 제2정규화를 수행한 후 제3정규화를 수행해야 한다.

위처럼 관리점이 관리점 코드에 종속되는 것이 이행 함수 종속성이다. 이를 분리해보자.

제3정규화를 수행하면 이렇게 관리점 테이블이 도출되고 관리점 코드가 기본키가 된다.

4. BCNF(Boyce-Codd Normalization Form)

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

위의 왼쪽 그림을 보자. PK(학번, 과목코드)가 교수를 함수적으로 종속한다. 이때 교수가 후보키(최소성, 유일성 만족)이고 교수가 과목코드를 함수적으로 종속하므로 오른쪽처럼 분해한다. 

정규화의 문제점

정규화는 데이터 중복성을 제거하므로 데이터 모델의 유연성을 높이고, 성능 향상에 도움이 된다. 하지만 데이터 조회select 시에 조인join을 유발하기 때문에 CPU와 메모리 사용량이 크다. 이런 부분은 유의하여 반정규화를 적용하는 것이 좋다.

하지만 반정규화 역시 문제점이 있다.

왼쪽 예시를 보자. A 테이블의 칼럼이 계속 증가하면, 어쨌든 조인은 최소화되므로 조회 성능이 좋아진다. 하지만 너무 많은 칼럼이 추가되면 DBMS의 입출력 단위인 블록의 크기를 넘어서게 되어 한 행을 읽기 위해 여러 블록을 읽어야 한다. 따라서 I/O량이 증가되고 성능이 떨어진다. 

이럴 땐 또 정규화가 필요하다. 

 

 

반정규화된 테이블의 성능저하 사례1

정규화하여 조인이 발생하면 성능이 심각하게 저하되는가? 다음 예를 살펴보면 2차 정규화를 적용한 테이블에 대해서 조인을 하더라도 PK Unique Index를 이용하면 조인 성능 저하는 미미하게 발생된다.

[그림 Ⅰ-2-4]의 왼쪽 그림은 2차 정규화가 안 된 반정규화된 테이블의 모습이고 오른쪽 그림은 부분키 종속을 정규화하여 두 개의 테이블로 분리해 2차 정규화된 테이블의 모습이다.
2차 정규화가 안 된 테이블은 직급명과 함께 반정규화된 관서번호, 관서명을 조회하면 하나의 테이블에서 데이터가 조회가 된다. 2차 정규화된 테이블은 관서번호, 관서명이 관서테이블에만 존재하기 때문에 두 개의 테이블을 조인하여 처리해야 한다.
정부보관금관서원장에서 데이터를 조회하는 것이나, 관서와 정부보관금관서원장을 조인하여 데이터를 조회하나 처리 성능은 사용자가 느끼기에는 거의 차이가 나지 않는다. PK가 걸려있는 방향으로 조인이 걸려 Unique Index를 곧바로 찾아서 데이터를 조회하기 때문에, 하나의 테이블에서 조회하는 작업과 비교했을 때 미미하게 성능 차이가 날 뿐 사용자에게 크게 영향을 줄 만큼 성능이 저하되는 일은 없는 것이다.
게다가 위의 예를 ‘관서등록일자가 2010년 이후 관서를 모두 조회하라’는 SQL 구문을 처리하는 것으로 바꾸면, 2차 정규화된 테이블이 훨씬 빠르다. [그림 Ⅰ-2-5]에서와 같이 왼쪽 테이블에서는 불필요하게 납부자번호만큼 누적된 데이터를 읽어서 결과를 구분하여 보여주어야 하지만 오른쪽은 관서수만큼만 존재하는 데이터를 읽어 곧바로 결과를 보여주기 때문이다. 이렇게 단순한 예만 보아도 정규화를 수행하면 무조건 조회성능이 저하된다는 고정관념이 틀렸다는 것을 알 수 있다.

반정규화된 테이블의 성능저하 사례2

두 개의 엔터티가 통합되어 반정규화된 또 다른 경우를 살펴본다. 이 업무에서는 어떤 물건을 매각할 때 매각일자를 정하고 그 일자에 해당하는 매각시간과 매각장소가 결정하는 속성의 성격을 가지고 있다. 즉 매각일자가 결정자가 되고 매각시간과 매각장소가 의존자가 되는 함수적 종속관계가 형성되는 관계이다. 매각일자는 5천 건이 있고 일자별매각물건은 100만 건이 있는 것으로 가정하자.

예를 들어 [그림 Ⅰ-2-6]의 모델에서 ‘서울 7호’에서 매각된 총매각금액, 총유찰금액을 산출하는 조회용 SQL문장을 작성하면 다음과 같다.

SELECT B.총매각금액 , B.총유찰금액 FROM (SELECT DISTINCT 매각일자 FROM 일자별매각물건 WHERE 매각장소 = '서울 7호') A, <== 100만건의 데이터를 읽어 DISTINCT함 매각일자별매각내역 B WHERE A.매각일자 = B.매각일자 AND A.매각장소 = B.매각장소;

대량의 데이터에서 조인 조건이 되는 대상을 찾기 위해 인라인뷰를 사용하기 때문에 성능이 저하된다. 이를 정규화하려면 복합식별자 중에서 일반속성이 주식별자 속성 중 일부에만 종속관계를 가지고 있으므로 2차 정규화의 대상이 된다. 2차 정규화를 적용하면 [그림 Ⅰ-2-7]과 같은 모델이 된다.

2차 정규화를 적용하여 매각일자를 PK로 하고 매각시간과 매각장소는 일반속성이 되었다. 정규화를 적용했기 때문에 매각일자를 PK로 사용하는 매각일자별매각내역과도 관계가 연결된다. 따라서 업무흐름에 따른 적확한 데이터 모델링 표기도 가능해지고, 드라이빙이 된 테이블 이 5천 건의 매각기일 테이블이 되므로 성능도 향상된다.
만약 위의 모델에서 ‘서울 7호’에서 매각된 총매각금액, 총유찰금액을 산출하는 조회용 SQL문장을 작성하면 다음과 같이 나온다.

SELECT B.총매각금액 , B.총유찰금액 FROM 매각기일 A, 매각일자별매각내역 B WHERE A.매각장소 = '서울 7호' <== 5천건의 데이터를 읽음 AND A.매각일자 = B.매각일자 AND A.매각장소 = B.매각장소;

매각기일 테이블이 정규화 되면서 드라이빙이 되는 대상 테이블의 데이터가 5천 건으로 줄어들어 조회 처리가 빨라진다.

반정규화된 테이블의 성능저하 사례3

다음 사례는 동일한 속성 형식을 두 개 이상의 속성으로 나열하여 반정규화한 경우에 해당한다. 계층형 데이터베이스를 많이 사용했던 과거 데이터 모델링의 습관이 남아서인지 관계형 데이터베이스에서도 동일한 속성을 한 테이블에 속성1, 속성2, 속성3 데이터 모델링을 하는 경우가 많이 있다.
[그림 Ⅰ-2-8]을 보면, 모델이라고 하는 테이블에 업무적으로 필요한 8개의 인덱스가 이미 생성되어 있다. 데이터는 30만 건이고 온라인 환경의 데이터베이스라고 가정하자. 유형기능분류코드에 따라 데이터를 조회하는 경우가 많이 나타나 인덱스를 생성하려면 유형기능분류코드 각각에 대해 인덱스를 생성해야 하므로 9개나 되는 인덱스를 추가 생성해야 한다.

참고로, 한 테이블에 인덱스가 많아지면 조회 성능은 향상되지만 데이터 입력/수정/삭제 성능은 저하된다. 그래서 일반 업무처리(온라인성 업무)에서는 인덱스 수를 가급적 7~8개가 넘지 않도록 하는 것이 좋다. 그런데 [그림 Ⅰ-2-8]의 모델은 다른 필요한 인덱스 이외에 유형기능분류코드 속성에 해당하는 인덱스를 9개나 추가적으로 생성해야 하므로 실전프로젝트에서는 어쩔 수 없이 인덱스를 생성하지 않거나 A유형기능분류코드1 하나만 인덱스를 생성하는 경우가 생긴다. 이에 따라 A유형기능분류코드1, A유형기능분류코드2, A유형기능분류코드3...을 이용하면 SQL의 성능이 저하되는 경우가 많다.
만약 각 유형코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL문장을 작성한다면 다음과 같이 나온다.

SELECT 모델코드, 모델명 FROM 모델 WHERE ( A유형기능분류코드1 = '01' ) OR ( B유형기능분류코드2 = '02' ) OR ( C유형기능분류코드3 = '07' ) OR ( D유형기능분류코드4 = '01' ) OR ( E유형기능분류코드5 = '02' ) OR ( F유형기능분류코드6 = '07' ) OR ( G유형기능분류코드7 = '03' ) OR ( H유형기능분류코드8 = '09' ) OR ( I유형기능분류코드9 = '09' )

각 유형별로 모두 인덱스가 걸려 있어야 인덱스에 의해 데이터를 찾을 수 있다. 이런 모델은 다음과 같이 정규화를 적용해야 한다.
중복속성에 대한 분리가 1차 정규화의 정의임을 고려하면 모델 테이블은 1차 정규화의 대상이 된다. 로우단위의 대상도 1차 정규화의 대상이 되지만 칼럼 단위로 중복이 되는 경우도 1차 정규화의 대상이 된다. 따라서 모델에 대해 1차 정규화를 적용하면 [그림 Ⅰ-2-9]와 같이 분리될 수 있다.

하나의 테이블에 9개가 반복적으로 나열이 되어 있을 때는 인덱스 생성이 어려웠지만 정규화되어 분리된 이후에는 인덱스 추가 생성이 0개가 되었다. 또한 분리된 테이블 모델기능분류코드에서 PK인덱스를 생성하여 이용함으로써 성능이 향상될 수 있다.
만약 각 유형코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL문장을 작성한다면 다음과 같이 작성된다.

SELECT A.모델코드, A.모델명 FROM 모델 A, 모델기능분류코드 B WHERE ( B.유형코드 = 'A' AND B.기능분류코드 = '01' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'B' AND B.기능분류코드 = '02' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'C' AND B.기능분류코드 = '07' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'D' AND B.기능분류코드 = '01' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'E' AND B.기능분류코드 = '02' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'F' AND B.기능분류코드 = '07' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'G' AND B.기능분류코드 = '03' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'H' AND B.기능분류코드 = '09' AND A.모델코드 = B.모델코드 ) OR ( B.유형코드 = 'I' AND B.기능분류코드 = '09' AND A.모델코드 = B.모델코드 )

위 SQL구문은 유형코드+기능분류코드+모델코드에 인덱스가 걸려 있으므로 인덱스를 통해 데이터를 조회함으로써 성능이 향상된다.
실전 프로젝트에서도 많은 데이터 모델이 칼럼 단위에서 중복된 경우가 발견된다. 이에 대한 파급효과 계산 없이 무조건 칼럼 단위로 COL1, COL2, COL3... 이런 식으로 데이터 모델링을 하는 것은 근본적으로 SQL문장의 성능을 나쁘게 하는 결과를 초래할 수도 있으므로 인덱스 생성 영향도를 파악한 이후에 적용하는 것이 좋은 방법이 된다

반정규화된 테이블의 성능저하 사례4

[그림 Ⅰ-2-10]과 같은 경우도 동일한 사례이다.

일재고와 일재고 상세를 구분함으로써 일재고에 발생되는 트랜잭션의 성능저하를 예방할 수 있게 되었다.