[SQLD]데이터모델과 성능 : 성능모델링, 정규화, 반정규화, 파티셔닝, 슈퍼/서브타입 성능, PK/FK 성능, 분산데이터베이스

https://lotuus.tistory.com/42

[SQLD] 21.11.20 제43회 SQL개발자 후기, 공부방법, 정리본, 43회 출제문제

 

[목차]

💚성능 데이터 모델링의 개요
   성능 데이터 모델링 수행시점 : 분석/설계단계
   성능 데이터 모델링 고려사항
💚정규화와 성능
   정규화를 통한 성능 향상 전략
   함수적 종속성과 정규화 과정
💚반정규화와 성능
   반정규화를 통한 성능향상 전략
   반정규화 적용방법
     반정규화 대상조사, 다른방법 유도 검토
     반정규화 적용
💚대량 데이터에 따른 성능
   대량 데이터발생에 따른 테이블 분할  (Row Chaining, Row Migration)
   PK에 의한 테이블 파티셔닝  (Range, List, Hash 파티셔닝)
   테이블에 대한 수평/수직분할 절차
💚데이터베이스 구조와 성능
   슈퍼/서브타입 모델(=Extended ER모델)의 성능고려방법  (One to One, Plus, Single Type)
   인덱스 특성을 고려한 PK/FK 데이터베이스 성능 향상
💚분산 데이터베이스와 성능
   분산데이터베이스의 투명성
   분산 데이터베이스의 장단점
   분산 데이터베이스 적용 기법
   분산 데이터베이스 설계를 고려해야하는 경우

 

 

💚성능 데이터 모델링의 개요

데이터베이스 성능향상을 목적으로 설계단계부터 정규화, 반정규화, 테이블통합, 테이블분할, 조인구조, PK, FK 등 여러 가지 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것이다. 

 

성능 데이터 모델링 수행시점 : 분석/설계단계

성능 향상 비용은 사전에 할수록 저렴하다.

특히 분석/설계 단계에서 성능 데이터 모델링을 수행할 경우, 성능 저하에 따른 재업무 비용을 최소화할 수 있게된다.

데이터의 증가가 빠를수록 성능저하에 따른 성능개선비용은 기하급수적으로 증가하게 된다.

 

성능 데이터 모델링 고려사항

① 데이터 모델링을 할 때 정규화를 정확하게 수행한다.
② 데이터베이스 용량산정을 수행한다.
③ 발생되는 트랜잭션의 유형을 파악한다.
④ 용량과 트랜잭션의 유형에 따라 반정규화를 수행한다.
⑤ 이력모델의 조정, PK/FK조정, 슈퍼타입/서브타입 등등의 조정 등을 수행한다.
⑥ 성능관점에서 데이터 모델을 검증한다.

 

 

💚정규화와 성능

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

정규화는 최소한의 데이터 중복, 데이터의 유연성을 위해 데이터를 분리하는 과정이다.

하지만, 조인이 많이 발생하게되어 성능이 저하될 수도 있다. => 반정규화 수행하면 해결된다.

조회성능	처리조건에 따라 성능이 향상👍될 수 있음
	처리조건에 따라 성능이 저하👎될 수 있음
입력/수정/삭제 성능	성능이 향상됨👍

 

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

함수적 종속성은 데이터(종속자)들이 어떤 기준값(결정자)에 의해 종속되는 현상을 말한다.

EX. 이름, 출생지, 주소는 주민등록번호에 함수 종속성을 가지고 있다. = 이름, 출생지, 주소를 주민등록번호로 특정할 수 있다.

 

💚반정규화와 성능

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

반정규화는 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 기법이다.

 

데이터의 무결성이 깨지는 것을 감수하고 반정규화를 하는 이유는

데이터 조회 시 입출력량이 많아서 성능이 저하되거나, 

경로가 너무 멀어 조인으로 인해 성능이 저하되거나,

칼럼을 계산하여 읽을 때 성능이 저하될 경우에 반정규화를 수행한다.

 

반정규화 적용방법

반정규화 대상조사, 다른방법 유도 검토

반정규화 대상조사	다른방법 검토
아래와 같은 경우가 발생하면 반정규화를 고려하자.	데이터 중복으로 인한 무결성을 깨트리지 않고 성능을 향상시킬 수 있는지 고려하자.
- 자주 사용되는 테이블에 접근 프로세스 수가 많고, 일정 범위만 조회하는 경우	- 클러스터링 적용 :   데이터를 입력/수정/삭제하는 경우 성능이 저하됨.   조회 중심의 테이블이 아니라면 권장하지 않는다.  - 인덱스를 조정한다.
- 대량의 데이터 범위를 자주 처리하는 경우	파티셔닝 적용 : 물리적으로 부분적인 테이블로 분리한다.  파티셔닝 키로 조회할 경우 성능이 좋아진다.
- 많은 조인이 걸려 데이터 조회 시 어려움이 있는 경우	view를 통해 접근할 수 있도록 한다.
- 통계성 프로세스에 의해 통계정보를 필요로 하는 경우	별도의 통계테이블을 생성한다.

 

반정규화 적용

테이블 반정규화	테이블 병합	1:1 관계 테이블 병합	분리된 테이블의 join연산으로 성능이 저하되었던 것을 병합하여 성능을 높인다.
		1:M 관계 테이블 병합
		슈퍼-서브타입 테이블 병합
	테이블 분할	수직분할	트랜잭션 처리 유형 파악이 선행되어야한다. 자주 사용하는 특정 칼럼들로 분할한다. 디스크 I/O를 분산처리하기 위함이다. ex. (일자, 사번, 방법, 코드, 번호) -> (일자, 방법), (사번, 코드, 번호)
		수평분할	자주 사용하는 특정 튜플들로 분할한다. ex. 데이터들을 월별로 분리 -> 수납이력 1월, 2월, 3월....
	테이블 추가	중복테이블 추가	다른 업무이거나 서버가 다른 경우, 원격조인을 제거하여 성능을 높인다.
		통계테이블 추가	SUM, AVG등을 미리 계산해 둠으로써 성능을 높인다.
		이력테이블 추가	마스터테이블의 레코드를 중복한다.
		부분테이블 추가	자주 사용하는 특정 칼럼들로 분할한다. 디스크 I/O를 줄이기 위함이다.
칼럼 반정규화	중복칼럼 추가		조인을 감소시키기 위해 추가한다.
	파생칼럼 추가		계산 성능저하를 위해 미리 값을 계산하여 저장한다.
	이력테이블 칼럼 추가		대량의 테이블에서 불특정한 날짜 조회나 최근 값을 조회할 때의 성능저하를 방지하기 위해 기능성칼럼(최근값 여부, 시작과 종료일자 등)을 추가한다
	PK에 의한 칼럼 추가		복합의미를 갖는 PK를 단일 속성을 구성했을 때 발생한다. PK의 값을 중복하여 일반속성으로 추가한다.
	오작동을 위한 칼럼 추가		잘못 처리할 경우 값을 복구하기 위해 이전 데이터를 임시적으로 중복하여 보관한다.
관계 반정규화	중복관계 추가		많은 조인 발생 시 성능저하를 방지하기 위해 추가적인 관계를 맺는다.

 

 

💚대량 데이터에 따른 성능

대량 데이터발생에 따른 테이블 분할(Row Chaining, Row Migration)

인덱스의 Tree 구조가 너무 커져 효율성이 떨어지게되고, 

데이터 처리(입력, 수정, 삭제, 조회) 시 I/O를 많이 유발하게된다.

Row Chaining	Row Migration
행 길이가 너무 길어서 데이터 블록 1개에 모두 저장되지 않고 다음 블록으로 넘어가 저장되는 형태이다.	저장공간이 부족한 상황에서 10만큼 저장되어있던 데이터를 15만큼으로 변경하고자 하면 다른 블록을 찾아 저장해야하는 형태
두 기법으로 성능저하가 발생하면, 트랜잭션을 분석하여 적절하게 1:1 관계로 분리하여 성능을 향상한다.

 

 

 

PK에 의한 테이블 파티셔닝(Range, List, Hash 파티셔닝)

Range 파티셔닝	데이터가 날짜 또는 숫자로 분리가 가능한 경우에 적용한다. SQL문을 실행할 땐 내부적으로 구분된 테이블에서 처리하여 성능이 개선된다. 데이터 보관주기에 따른 테이블 관리가 용이하다. ex. 년/월을 이용하여 12개의 파티션 테이블을 만든다.
List 파티셔닝	일련번호 등의 코드값으로 PK가 구성되어있는 경우에 적용한다. ex. 사업소코드별로 파티션 테이블을 만든다.
Hash 파티셔닝	지정된 hash조건에 따라 해슁 알고리즘이 적용된다. 설계자도 어떤 테이블에 어떤 데이터가 들어갔는지 알 수 없다.

 

테이블에 대한 수평/수직분할 절차

- 트랜잭션 처리패턴을 분석한다.

- 칼럼단위의 처리가 발생되는건지, 로우단위의 처리가 발생되는건지 분석한다.

 

 

💚데이터베이스 구조와 성능

슈퍼/서브타입 모델(=Extended ER모델)의 성능고려방법

슈퍼/서브타입 모델은 공통부분을 슈퍼타입으로 모델링하고, 내용을 상속받아 서브타입 엔터티를 만들어 

업무의 내용을 정확하게 표현하면서 물리적 데이터 모델로 변환 시 선택의 폭이 넓다는 장점을 가지고 있다.

슈퍼/서브타입의 변환 기준은 데이터 양과 트랜잭션의 유형이다.

	One to One Type 1:1 타입	Plus Type 슈퍼+서브 타입	Single Type All in One 타입
설명	슈퍼타입과 서브타입 각각 트랜잭션이 발생하면  개별 테이블로 구성하여 1:1 관계를 갖도록 한다. ex. 슈퍼타입에 이름을 넣으면, 서브타입으로 세부정보가 나오는 경우	슈퍼타입과 서브타입 함께 트랜잭션이 발생하면 슈퍼타입의 PK와 각 서브타입의 속성을 묶어 N개의 테이블을 구성한다. ex. 슈퍼타입과 서브타입이 1개 테이블로 통합되어있고 A칼럼만 개별적으로 많이 처리하는데 B, C 등의 칼럼내용까지 포함되어있어서 필요없는 처리가 발생하게되는 경우. (PK당사자번호, 이해관계인, 대리인, 매수인) -> (FK당사자번호, 이해관계인) -> (FK당사자번호, 대리인) -> (FK당사자번호, 매수인)	왼쪽 예제에서 당사자번호, 이해관계인,..의 속성을 항상 통합처리하거나 슈퍼+서브타입을 정확히 지정하지 못하는 상황에서는 하나의 테이블로 구성한다.
확장성	좋음	보통	나쁨
조인성능	나쁨	나쁨	좋음
I/O량 성능	좋음	좋음	나쁨
관리용이성	나쁨	나쁨	좋음(1개)

📌 슈퍼타입과 서브타입 데이터 모델을 물리적인 테이블 형식으로 변환할 때 설명으로 부적절한 것은?

1) 트랜잭션은 항상 전체를 대상으로 일괄 처리하는데 테이블은 서브타입별로 개별 유지하는 것으로 변환하면 UNION 연산에 의해 성능이 저하될 수 있다.
2) 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합하여 변환하면 불필요하게 많은 양의 데이터가 집적되어있어 성능이 저하될 수 있다.
3) 트랜잭션은 항상 슈퍼+서브타입을 함꼐 처리하는데 개별로 유지하면 조인에 의해 성능이 저하될 수 있다.
4) 트랜잭션은 항상전체를통합하여 분석 처리하는데 하나로 통합되어 있으면 데이터 집적으로 인해 성능이 저하될 수 있다.  //조인이 감소하기 때문에 성능이 더 우수해질 수 있다.

 

인덱스 특성을 고려한 PK/FK 데이터베이스 성능 향상

PK/FK는 데이터 접근 경로를 제공하는 성능의 측면에서도 중요한 의미를 가지고 있다.

테이블에 발생되는 트랜잭션의 조회 패턴을 분석하여 PK/FK 칼럼의 순서를 조정해야한다.

Primary Key	Foreign Key
PK는 테이블의 인덱스(Unique Index)를 자동 생성한다. 앞쪽에 위치한 속성의 값이 비교자(=, between, <> ...)로 있어야 좋은 효율을 나타낼 수 있다. 인덱스 특징을 고려하지 않고 PK를 지정하면, 효율적이지 않은 인덱스가 생성되어 FULL TABLE SCAN이 유발된다. => 성능저하	FK는 조인으로 이용되는 경우가 많이 있기때문에 FK인덱스를 생성해야 성능이 좋아진다. FK인덱스가 미생성되면 FULL TABLE SCAN이 발생하여 성능이 저하된다. 하지만, FK인덱스가 거의 활용되지 않는다면 FK인덱스를 지우는 것도 적절하다.

 

 

💚분산 데이터베이스와 성능

분산 데이터베이스 :

여러 곳으로 분산되어있는 데이터베이스를 하나의 가상 시스템으로 사용할 수 있게하는 데이터베이스.

물리적으로는 분리되어있지만, 논리적으로는 동일한 시스템인 데이터베이스.

 

분산 데이터베이스는 통합된 데이터베이스에서 제공할 수 없는 빠른 성능을 제공한다.

 

분산데이터베이스의 투명성

분산 데이터베이스는 아래 6가지 투명성을 만족해야한다.

분할	하나의 논리적 릴레이션이 여러개로 분할되어 있다.
위치	사용하려는 데이터의 저장장소 명시가 불필요하다.  System Catalog에 위치정보가 저장되어야한다.
지역사상	지역DBMS와 물리적DB 사이의 MAPPING을 보장한다. 각 지역시스템 이름과 무관한 이름을 사용할 수 있다.
중복	DB객체가 여러 시스템에 중복되어있는지 알 필요 없다.
장애	DBMS 또는 컴퓨터의 장애에 무관하게 트랜잭션의 원자성이 보장되어야한다.
병행	다수 트랜잭션을 동시에 수행해도 결과는 일관성있어야 한다.

 

분산 데이터베이스의 장단점

장점	단점
- 신뢰성, 가용성, 효용성, 융동성이 높다 - 병렬처리 수행하여 빠른 응답 가능하다 - 시스템 용량 확장이 쉽다 - 각 지역 사용자들의 요구 수용이 빠르다	- 설계와 관리가 복잡하다, 비용이 높다 - 데이터 무결성에 대한 위협이 있다 - 보안, 관리, 통제 어렵고 오류 잠재성이 크다 - 응답속도가 불규칙하다.

 

분산 데이터베이스 적용 기법

분산 데이터베이스의 동기화는 실시간 처리보다는 야간 배치작업으로 복제된다.

테이블 위치 분산		테이블의 구조는 변하지 않는다.  테이블이 다른 데이터베이스에 중복되어 생성되지 않는다. 테이블의 위치를 다르게 위치시킨다.	ex. 자재품목은 본사DB에, 생산제품은 지사DB에 위치
테이블 분할 분산	수평분할	테이블을 특정 행을 기준으로 쪼개어 분산한다.	ex. 지사별로 사용하는 행이 다를 때 이용한다.
	수직분할	테이블을 특정 칼럼을 기준으로 쪼개어 분산한다. 테이블에 동일한 PK가 있어야 한다.	ex. 재고량은 각 지사에서 관리하고, 단가는 본사에서 관리
테이블 복제 분산 (가장 많이 사용한다)	부분복제	마스터 테이블의 일부만 다른지역이나 서버에 동일하게 생성하여 관리한다.	ex. 지사의 데이터 중 고객정보만 본사에 복제
	광역복제	마스터 테이블의 전부를 다른지역이나 서버에 동일하게 생성하여 관리한다.	ex. 지사의 데이터 전부를 본사에 복제
테이블 요약 분산	분석요약	지역간, 서버간 데이터가 비슷하지만 서로 다른 유형으로 존재한다. 지역별로 존재하는 동일한 정보를 본사에서 통합한다.	ex. 각 지사별로 판매 제품이 같고, 판매실적을 본사에서 통합한다.
	통합요약	지역간, 서버간 데이터가 비슷하지만 서로 다른 유형으로 존재한다. 지역별로 존재하는 다른 정보를 본사에서 통합한다.	ex. 각 지사별로 판매 제품이 다르고, 판매실적을 본사에서 통합한다.

 

분산 데이터베이스 설계를 고려해야하는 경우

- 성능이 중요한 사이트이다.

- 공통코드, 기준정보, 마스터 데이터 등에 대해 분산환경을 구성하면 성능이 좋아진다.

- 실시간 동기화를 요구하지 않는다.

- 특정 서버에 부하가 집중된다.

- 백업 사이트를 구성한다.

 

 

 

 

#SQLD #성능데이터모델링 #정규화 #함수종속성 #반정규화 #RowChainning #RowMigration #Range파티셔닝 #List파티셔닝 #Hash파티셔닝 #슈퍼/서브타입모델 #OnetoOneType #PlusType #SingleType #PK/FK인덱스특성 #분산데이터베이스