SQLD - 과목1. (8) 반정규화와 성능

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

① 반정규화의 정의

 

▷ 반정규화 ( = 역정규화 )

    : 反 (되돌릴 반) 의 '반' 이다. 

     De - Normalization.

 

     정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해

     중복, 통합, 분리 등을 수행하는 데이터 모델링 기법이다.

 

     데이터를 중복하여 성능을 향상시키기 위한 기법이고, 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을

     수행하는 모든 과정이다.

 

 

 

▷ 비정규화

    : 정규화를 아예 수행하지 않은 모델이다.

 

 

 

---

※ 반정규화를 적용하는 목적

   : 데이터 무결성이 깨질 수 있는 위험이 있지만, 데이터를 조회할 때 디스크 I/O 량이 많아서 성능이 저하되거나,

     경로가 너무 멀어 조인으로 인한 성능 저하가 예상되거나, 칼럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우

     반정규화를 수행한다. 

 

     (1) 테이블의 중복성

     (2) 칼럼의 중복성

     (3) 관계의 중복성

 

반정규화

 

 

정규화는 입력/수정/삭제에 대한 성능을 향상시킬 뿐만 아니라, 조회에 대해서도 성능을 향상시킨다.

BUT, 정규화만을 수행하면 엔터티의 갯수가 증가, 관계가 많아져 여러개의 JOIN 이 걸려야 한다.

 

업무적으로 조회에 대한 처리성능이 중요할 때 부분적으로 반정규화를 한다.

데이터의 중복성을 증가시켜야만 데이터 조회의 성능을 향상시키는 경우가 있다.

 

 

 

 

 

 

 

※ 반정규화를 수행하지 않은 경우 발생하는 일

   (1) 성능이 저하된 데이터베이스가 생성된다.

   (2) 구축단계나 시험단계에서 반정규화를 적용할 때 수정에 따른 노력비용이 많이 들게 된다.

 

 

 

 

 

 

---

② 반정규화의 적용방법

 

프로젝트에서 칼럼 중복을 통해서만 반정규화를 수행한다.

SQL 단위 성능 저하가 예상 되서 다른 테이블에서 조인하여 가져와야 할 칼럼을 기준이 되는 테이블에 중복하여 SQL 문장을 단순하게 처리하도록 한다.

무분별하게 칼럼의 반정규화를 많이 하게 되는 것은 데이터에 대한 무결성을 깨뜨리는 결정적인 역할을 한다.

 

반정규화에 대한 필요성이 결정되면, 칼럼의 반정규화와 테이블의 반정규화와 관계의 반정규화를 종합적으로 고려해야 한다.

 (1) 칼럼의 반정규화

 (2) 테이블의 반정규화

 (3) 관계의 반정규화

 

반정규화를 막현하게 중복을 유도하는 것 보다는 성능을 향상시킬 다른 방법들을 고려 후 반정규화를 적용해야 한다.

 

 

반드시 데이터 무결성을 보장할 수 있는 방법을 고려 후, 반정규화를 적용해야 한다.

 

정규화와 반정규화 사이에는 Trade - Off  관계이다.

정규화만을 강조하면 성능의 이슈가 발생될 수 있고, 반정규화를 과도 적용하면 데이터 무결성이 깨지는 위험이 증가한다.

 

 

 

∴ 반정규화를 적용할 때, 데이터 무결성의 중요함과 데이터 무결성이 충분히 유지될 수 있도록 프로세스 처리의 안정성이 먼저 확인되야 한다.

 

 

 

 

 

반정규화 절차

 

 

※ 반정규화 절차

 

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

        : 전체 데이터의 양을 조사하고 그 데이터가 해당 프로세스를 처리할 때 성능저하가 나타날 수 있는지 검증해야 한다.

          데이터가 대량이고 성능이 저하될 것으로 예상이 되면 4 가지 경우를 고려한다.

 

          ⓐ 자주 사용되는 테이블에 접근하는 프로세스의 수가 많고 항상 일정한 범위만을 조회하는 경우, 반정규화를 검토한다.

          ⓑ 테이블에 대량의 데이터가 있고 대량의 데이터 범위를 자주 처리하는 경우, 처리 범위를 일정하게 줄이지 않으면 성능을

              보장할 수 없을 경우에 반정규화를 검토한다.

          ⓒ 통계성 프로세스에 의해 통계 정보를 필요로 할 때 별도의 통계테이블 ( 반정규화 테이블 ) 을 생성한다.

          ⓓ 테이블에 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우 반정규화를 검토한다.

 

 

   (2) 반정규화의 대상에 대해 다른 방법으로 처리할 수 있는지 검토한다.

        : 가급적 데이터를 중복하여 데이터 무결성을 깨뜨릴 위험을 제어하기 위해 반정규화를 결정하기 이전에

          성능을 향상시킬 수 있는 다른 방법을 찾는다.

 

          ⓐ 지나치게 많은 JOIN 이 걸리면 뷰 (VIEW) 를 사용하여 해결한다.

          ⓑ 대량의 데이터 처리나 부분 처리에 의해 성능이 저하되는 경우, 클러스터링을 적용하거나 인덱스를 조정하여

              성능을 향상시킨다.

          ⓒ 대량의 데이터는 Primary Key 의 성격에 따라 부분적인 테이블로 분리 가능하다.

              Partitioning (파티셔닝) 기법이 적용되어 성능저하를 방지한다.

          ⓓ 응용 애플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킨다.

              데이터 처리를 위한 값을 캐쉬하거나, 데이터 캐쉬, 공유하여 성능을 향상시킨다.

 

 

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

        : 반정규화의 세 가지 규칙을 고려하여 반정규화를 적용한다.

          반정규화를 하는 대상 ( 테이블, 속성, 관계 )

          꼭 테이블과 속성, 관계에 대해 중복으로 가져가는 방법만이 아니라, 

          테이블, 속성, 관계를 추가 / 분할 / 제거 할 수 있다.

 

 

 

 

 

 

 

 

 

---

2. 반정규화의 기법

① 테이블 반정규화

 

기법분류	기법	내용
테이블 병합	1:1 관계 테이블병합	1:1 관계를 통합하여 성능 향상
	1:M 관계 테이블병합	1:M 관계를 통합하여 성능 향상
	슈퍼/서브타입 테이블병합	슈퍼/서브 관계를 통합하여 성능 향상
테이블 분할	수직분할	칼럼 단위의 테이블을 디스크 I/O 를 분산처리 하기 위해 테이블을 1:1 로 분리하여 성능향상 ( 트랜잭션이 처리되는 유형 파악이 선행 )
	수평분할	로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O 및 데이터 접근의 효율성을 높여 성능을 향상하기 위해 로우 단위로 테이블을 쪼갬 ( 관계가 없음 )
테이블 추가	중복 테이블 추가	다른 업무이거나 서버가 다른 경우 동일한 테이블 구조를 중복하여 원격 조인을 제거하여 성능 향상
	통계 테이블 추가	SUM, AVG 등을 미리 수행하여 계산해 둠으로써 조회 시 성능 향상
	이력 테이블 추가	이력 테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력 테이블에 존재하는 방법은 반정규화의 유형
	부분 테이블 추가	하나의 테이블의 전체 칼럼 중 자주 이용하는 집중화된 칼럼들이 있을 때 디스크 I/O 를 줄이기 위해 해당 칼럼들을 모아놓은 별도의 반정규화 된 테이블을 생성

 

 

 

---

② 칼럼 반정규화

 

반정규화 기법	내용
중복칼럼 추가	조인에 의해 처리할 때 성능저하를 예방하기 위해 ( 조인을 감소시키기 위해 ) 중복된 칼럼을 위치시킨다.
파생칼럼 추가	트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능 저하를 예방하기 위해 미리 값을 계산하여 칼럼에 보관한다. ( Derived Column )
이력테이블 칼럼 추가	대량의 이력데이터를 처리할 때 불특정 날 조회나, 최근 값을 조회할 때 나타날 수 있는 성능 저하를 예방하기 위해 이력테이블에 기능성 칼럼 ( 최근값 여부, 시작과 종료일자 등 ) 을 추가한다.
PK 에 의한 칼럼 추가	복합의미를 갖는 PK 를 단일 속성으로 구성하였을 경우 발생된다. 단일 PK 안에서 특정 값을 별도로 조회하는 경우 성능저하가 발생될 수 있다. 이 때 이미 PK 안에 데이터가 존재하지만 성능 향상을 위해 일반속성으로 포함하는 방법이 PK 의한 칼럼 추가 반정규화 이다.
응용시스템 오작동을 위한 칼럼 추가	업무적으로는 의미가 없지만 사용자가 데이터 처리를 하다가 잘못 처리하여 원래 값으로 복구하기 원하는 경우, 이전 데이터를 임시적으로 중복하여 보관하는 기법. 칼럼으로 이것을 보관하는 방법은 오작동 처리를 위한 임시적인 기법이지만, 이것을 이력데이터 모델로 풀어내면 정상적인 데이터 모델의 기법이 될 수 있다.

 

 

 

---

③ 관계 반정규화

 

반정규화 기법	내용
중복관계 추가	데이터 처리를 위한 여러 경로를 거쳐 조인이 가능하지만, 이 때 발생할 수 있는 성능 저하를  예방하기 위해 추가적인 관계를 맺는 방법이 관계의 반정규화 이다.

 

 

 

 

 

 

 

---

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

 

정규화 모델의 문제접

 

공급자라는 엔터티 = 마스터

전화번호, 메일주소, 위치가 변경되는 내용 = 이력형태로 관리되는 데이터 모델

 

 

공급자와 전화번호, 메일주소, 위치는 1:M 관계이므로 한 명의 공급자 당 여러 개의 전화번호, 메일주소, 위치가 존재한다.

가장 최근에 변경된 값을 가져오려면 복잡한 JOIN 이 필요하다.

 

가장 최근에 변경된 값을 마스터에 위치시키도록 적절하게 반정규화를 적용하면,

SQL 구문이 간단하게 된다.

 

 

 

 

반정규화를 통한 성능향상 사례

     

 

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

반정규화를 통해 가독성이 높아지고 성능이 향상되었다.

 

 

 

 

 

 

 

 

---

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

 

다른 서버간 정규화 된 사례

 

서버 A 에 부서와 접수 테이블이 있고, 서버 B 에 연계 테이블이 있다.

서버 B 에서 데이터 조회할 때 빈번하게 조회되는 " 부서번호 " 가 서버 A 에 존재하기 때문에 연계, 접수, 부서 테이블이 모두 JOIN 이 걸린다.

분산 데이터베이스 환경이여서 다른 서버간에도 조인이 걸려 성능이 저하된다.

 

 

 

Oracle 의 경우 DB LINK 조인이 발생하여 일반 조인보다 성능이 저하된다.

서버 A 의 부서테이블의 부서명을 서버 B 의 연계테이블에 부서명으로 속성 반정규화를 하면 조회 성능을 향상시킬 수 있다.

 

 

 

 

다른 서버 간 반정규화 된 사례

 

SELECT 부서명, 연계상태코드 FROM 연계 WHERE 연계일자 BETWEEN '20210301' AND '20210401';

SQL 구문이 간단해지고 분산되어 있는 서버간에도 DB LINK 조인이 발생하지 않아 성능이 개선된다.

 

반정규화를 적용할 때 데이터의 무결성 유지에 주의해야 한다.

 

 

 

 

 

 

 

 

 

---