2-3 반정규화와 성능

반정규화De-Normalization

DB의 성능 향상을 위해 데이터 중복을 허용하고 조인을 줄이는 것으로, 반정규화를 통해 조회 성능은 좋아지지만 데이터 모델의 유연성을 낮아진다. 또 데이터 무결성을 깨뜨릴 위험이 있다.

반정규화를 수행하는 경우

• 정규화에 충실하면 종속성, 활용성을 향상되지만 수행 속도가 느려질 때
• 다량의 범위, 대량의 데이터를 자주 처리할 때
• 특정 범위의 데이터만 자주 처리할 때
• 통계성 프로세스에 의해 통계/요약/집계 정보가 자주 요구될 때
• + 문제 지문: 반정규화 정보에 대한 재현의 적시성으로 판단한다. 예를 들어 빌링의 잔액은 다수 테이블에 대한 다량의 조인이 불가피하므로 데이터 제공의 적시성 확보를 위한 필수 반정규화 대상이다.

반정규화의 적용방법

반정규화 절차	설명
반정규화 대상 조사	위의 4가지 고려사항(데이터 처리 범위, 통계성 등)을 통해 대상을 조사한다
다른 방법으로 유도 검토	반정규화 외에 다른 방법(클러스터링, 뷰, 인덱스 튜닝, 파티션, 응용 애플리케이션의 로직 등)이 있는지 검토한다.
반정규화 수행	테이블, 속성, 관계 등을 반정규화 한다

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

반정규화의 기법

1. 테이블 반정규화: 테이블 병합/분할/추가

*수평분할

특정 값에 따라 테이블을 분할한다

*Super type과 Sub type

개인고객과 법인고객으로 분류되는 고객 엔터티가 있다고 가정하다. 이때 고객 엔터티는 슈퍼 타입, 개인고객과 법인고객은 서브 타입이다. 즉 부모-자식 관계가 나타나는 것이다. 슈퍼/서브 관계에는 고객이 개인고객이거나 법인고객인 경우인 배타적 관계, 고객이 개인고객일 수도 법인고개일 수도 있는 포괄적 관계가 있다.

*슈퍼-서브타입 변환 방법

변환법	설명
OneToOne Type	슈퍼타입과 서브타입을 개별 테이블로 도출한다. 테이블 수가 많아서 조인도 많고 관리가 어렵다
Plus Type	슈퍼타입과 서브타입 테이블로 도출한다. 조인이 많고 관리가 어렵다
Single Type	슈퍼-서브 타입을 한 테이블로 도출한다. 조인 성능이 좋고 관리가 편하지만 I/O량이 많다

*파티셔닝

파티션 기법으로 테이블을 분할할 수도 있다. 파티션을 사용하면 논리적으로는 한 테이블이지만 여러 개의 데이터 파일에 분산되어 저장된다. 데이터값의 범위를 기준으로 하는 Range 파티션, 특정 값을 지정하는 List 파티션, 해시함수를 적용하는 Hash 파티션, 범위와 해시를 복합적으로 사용하는 Composite 파티션이 있다. 

파티션 테이블의 장점은 다음과 같다.

• 데이터 조회 시 액세스 범위가 줄어 성능이 향상된다 
• 데이터가 분할되어 있어 I/O 성능이 향상된다
• 각 파티션을 독립적으로 백업하거나 복구할 수 있다

2. 칼럼 반정규화

3. 관계 반정규화

테이블과 칼럼의 반정규화는 데이터 무결성에 영향을 미치지만 관계의 반정규화는 데이터 무결성을 깨뜨릴 위험없이 데이터처리의 성능을 향상시킬 수 있다. 데이터 모델 전체가 관계로 연결되어 있고 관계가 서로 먼 친척간 조인관계가 빈번해서 성능저하가 예상된다면 관계의 반정규화를 통해 성능을 향상시킬 수 있다.

*문제 선지 - 반정규화를 통한 성능 향상과 관련있는 것

1. Nested Loop 방식의 조인을 조회해서 반정규화를 수행하여 조인을 제거했다
2. 데이터의 중복을 허용하여 select문이 간단하게 변경되었다
3. 야간에 배치로 합계를 미리 계산 후 select 문을 사용한다

*로우 체이닝

로우의 데이터가 insert된 후 delete 되어 한 행이 삭제되고, 해당 블록에 빈 공간이 생겼을 때 새로운 데이터가 입력된다고 가정하자. 새로운 데이터가 입력될 때 처음엔 빈 공간이 있는 블록에 입력되고 그 공간이 부족하면 나머지 데이터가 새로운 블록에 입력되는 데 이를 로우 체이닝이라고 한다.

*로우 마이그레이션

행에 입력될 수 있는 데이터 영역에 데이터가 모두 입력되어 저장 공간이 부족한 경우 기존 데이터의 변경 작업이 일어난다고 가정하자. 변경 작업에 의해 공간이 더 필요한데 저장 공간이 없을 경우 새로운 블록으로 이동시켜 변경 작업을 수행하는 것을 로우 마이그레이션이라고 한다.

정규화가 잘 정의된 데이터 모델에서 성능이 저하될 수 있는 경우

[그림 Ⅰ-2-14]는 공급자라고 하는 엔터티가 마스터이고 전화번호와 메일주소 위치가 각각 변경되는 내용이 이력형태로 관리되는 데이터 모델이다. 이 모델에서 공급자정보를 가져오는 경우를 가정해 보자.

공급자와 전화번호, 메일주소, 위치는 1:M 관계이므로 한 명의 공급자당 여러 개의 전화번호, 메일주소, 위치가 존재한다. 따라서 가장 최근에 변경된 값을 가져오기 위해서는 조금 복잡한 조인이 발생될 수 밖에 없다.
다음 SQL은 위와 같은 조건을 만족하는 SQL구문이 된다.

SELECT A.공급자명, B.전화번호, C.메일주소, D.위치 FROM 공급자 A, (SELECT X.공급자번호, X.전화번호 FROM 전화번호 X, (SELECT 공급자번호, MAX(순번) 순번 FROM 전화번호 WHERE 공급자번호 BETWEEN '1001' AND '1005' GROUP BY 공급자번호) Y WHERE X.공급자번호 = Y.공급자번호 AND X.순번 = Y.순번) B, (SELECT X.공급자번호, X.메일주소 FROM 메일주소 X, (SELECT 공급자번호, MAX(순번) 순번 FROM 메일주소 WHERE 공급자번호 BETWEEN '1001' AND '1005' GROUP BY 공급자번호) Y WHERE X.공급자번호 = Y.공급자번호 AND X.순번 = Y.순번) C, (SELECT X.공급자번호, X.위치 FROM 위치 X, (SELECT 공급자번호, MAX(순번) 순번 FROM 위치 WHERE 공급자번호 BETWEEN '1001' AND '1005' GROUP BY 공급자번호) Y WHERE X.공급자번호 = Y.공급자번호 AND X.순번 = Y.순번) D WHERE A.공급자번호 = B.공급자번호 AND A.공급자번호 = C.공급자번호 AND A.공급자번호 = D.공급자번호 AND A.공급자번호 BETWEEN '1001' AND '1005'

정규화 된 모델이 적절하게 반정규화 되지 않으면 위와 같은 복잡한 SQL구문은 쉽게 나올 수 있다. 이른바 A4용지 5장으로 작성된 SQL이 쉽지 않게 발견될 수 있는 것이다.
위의 모델을 적절하게 반정규화를 적용하면 즉, 가장 최근에 변경된 값을 마스터에 위치시키면 다음과 같이 아주 간단한 SQL구문이 작성 된다.

 

위에서 복잡하게 작성된 SQL문장이 반정규화를 적용하므로 인해 다음과 같이 간단하게 작성이 되어 가독성도 높아지고 성능도 향상되어 나타났다.

SELECT 공급자명, 전화번호, 메일주소, 위치 FROM 공급자 WHERE 공급자번호 BETWEEN '1001' AND '1005'

결과만 보면 너무 당연하고 쉬운 것 같지만 기억해야 할 사실은 위 내용들은 모두 실제로 프로젝트를 할 때도 이와 같이 SQL문장의 성능과 단순성을 고려하지 않고 무모하게 설계되는 경우가 많이 있다는 점이다.

정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우

업무의 영역이 커지고 다른 업무와 인터페이스가 많아짐에 따라 데이터베이스서버가 여러 대인 경우가 있다. [그림 Ⅰ-2-16]은 데이터베이스서버가 분리 되어 분산데이터베이스가 구성되어 있을 때 반정규화를 통해 성능을 향상시킬 수 있는 경우이다.
서버A에 부서와 접수 테이블이 있고 서버B에 연계라는 테이블이 있는데 서버B에서 데이터를 조회할 때 빈번하게 조회되는 부서번호가 서버A에 존재하기 때문에 연계, 접수, 부서 테이블이 모두 조인이 걸리게 된다. 게다가 분산데이터베이스 환경이기 때문에 다른 서버간에도 조인이 걸리게 되어 성능이 저하되는 것이다.

위의 모델을 통해 서버B의 연계테이블에서 부서명에 따른 연계상태코드를 가져오는 SQL구문은 다음과 같이 작성된다.

SELECT C.부서명, A.연계상태코드 FROM 연계 A, 접수 B, 부서 C <== 서버A와 서버B가 조인이 걸림 WHERE A.부서코드 = B.부서코드 AND A.접수번호 = B.접수번호 AND B.부서코드 = C.부서코드 AND A.연계일자 BETWEEN '20040801' AND '20040901'

Oracle의 경우 DB LINK 조인이 발생하여 일반조인보다 성능이 저하될 것이다.
위의 분산 환경에 따른 데이터 모델을 다음과 같이 서버A에 있는 부서테이블의 부서명을 서버B의 연계테이블에 부서명으로 속성 반정규화를 함으로써 조회 성능을 향상시킬 수 있다.

[그림 Ⅰ-2-17]의 모델에 대한 SQL구문은 다음과 같이 작성된다.

SELECT 부서명, 연계상태코드 FROM 연계 WHERE 연계일자 BETWEEN '20040801' AND '20040901'

SQL구문도 간단해지고 분산되어 있는 서버간에도 DB LINK 조인이 발생하지 않아 성능이 개선되었다.
반정규화를 적용할 때 기억해야 할 내용은 데이터를 입력, 수정, 삭제할 때는 성능이 떨어지는 점을 기억해야 하고 데이터의 무결성 유지에 주의를 해야 한다.