SQLD 자격증 # 데이터 모델과 성능(과목1)_반정규화와 성능

1. 반정규화를 통한 성능 향상 전략

​ 가. 반정규화의 정의

데이터를 중복하여 성능을 향상하기 위한 기법이라고 정의할 수 있고

더 넓은 의미의 반정규화는 성능을 향상하기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정을 의미한다.

 

데이터 무결성이 깨질 수 있어도 반정규화를 적용하는 이유

• 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하될 때
• 경로가 너무 멀어 조인으로 인한 성능저하가 예상될 때
• 칼럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우 반정규화를 수행

 

정규화만을 수행하면 엔터티의 갯수가 증가하고 관계가 많아져 일부 여러 개의 조인이 걸려야만 데이터를 가져오는 경우가 있다. 

 

이러한 경우 조회에 대한 처리성능이 중요하다고 판단될 때 부분적으로 반정규화를 고려하게 되는 것이다.

 

반정규화를 기술적으로 수행하지 않는 경우의 현상

• 성능이 저하된 데이터베이스가 생성될 수 있다.
• 구축 단계나 시험단계에서 반정규화를 적용할 때 수정에 따른 노력비용이 많이 들게 된다.

 

나. 반정규화의 적용방법

       

반정규화를 적용하기 위해서는 그림 Ⅰ-2-13에 나타난 것처럼 먼저 반정규화의 대상을 조사하고 다른 방법을 적용할 수 있는지 검토하고 그 이후에 반정규화를 적용하도록 한다.

 

1)  반정규화의 대상을 조사한다.

 데이터가 대량이고 성능이 저하될 것으로 예상이 되면 다음 4가지 경우를 고려하여 반정규화를 고려

• 자주 사용되는 테이블에 접근(Access)하는 프로세스의 수가 많고 항상일정한 범위만을 조회하는 경우에 반정규화를 검토한다.
• 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우에 처리 범위를 일정하게 줄이지 않으면 성능을 보장할 수 없을 경우에 반정규화를 검토한다.
  ☞ 해결 : 인덱스를 조정해보고 그래도 성능향상이 불가능하면 클러스터링을 고려, 파티셔닝 기법(Primary Key의 성격에 따라 부분적인 테이블로 분리)
  
• 통계성 프로세스에 의해 통계 정보를 필요로 할 때 별도의 통계테이블(반정규화 테이블)을 생성한다.
• 테이블에 지나치게 많은 조인(JOIN)이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 반정규화를 검토한다.
  ☞ 해결 : 뷰(VIEW)를 사용하면 이를 해결할 수도 있다.

 

3) 반정규화를 적용한다.

반정규화를 하는 대상으로는 테이블, 속성, 관계에 대해 적용할 수 있으며 꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 반정규화가 아니고 테이블, 속성, 관계를 추가할 수도 있고 분할할 수도 있으며 제거할 수도 있다.

 

 

2. 반정규화의 기법

가. 테이블 반정규화

​

나. 칼럼 반정규화

​

다. 관계 반정규화

 

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

그림 Ⅰ-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 구문은 쉽게 나올 수 있다.

위의 모델을 적절하게 반정규화를 적용하면 즉, 가장 최근에 변경된 값을 마스터에 위치시키면 다음과 같이 아주 간단한 SQL 구문이 작성된다.

     

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

​

 

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

그림 Ⅰ-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 조인이 발생하지 않아 성능이 개선되었다.

반정규화를 적용할 때 기억해야 할 내용은 데이터를 입력, 수정, 삭제할 때는 성능이 떨어지는 점을 기억해야 하고 데이터의 무결성 유지에 주의를 해야 한다.

 

---

✅ REFERENCE

제 3절 반정규화와 성능