[SQLD 암기] 데이터 모델과 성능

* 성능 데이터 모델링

 - 데이터베이스 성능 향상을 목적으로

   설계 단계의 데이터 모델링 때부터 성능 관련된 사항이 데이터 모델링에 반영되도록 하는 것

 - 프로젝트 초기에 운영환경에 대비한 테스트 환경을 구현하고 그곳에 트랜잭션을 발생시켜 실제 성능을 테스트해야 함

 - 데이터 모델의 구조도 변경하면서 어떤 구조가 해당 사이트에 성능상 가장 적절한 구조인지 검토

 - 데이터 증가가 빠를수록 성능저하에 따른 성능 개선 비용은 증가

 - 데이터모델은 성능을 튜닝하면서 변경이 될 수 있는 특징이 있음

 - 분석/설계 단계에서 성능을 고려한 데이터모델링을 수행할 경우,

   성능저하에 따른 rework 비용을 최소화할 수 있는 기회를 가짐

 

* 성능 데이터 모델링 수행 절차

 1. 데이터모델링을 할 때 정규화를 정확하게 수행

 2. 데이터베이스 용량산정 수행

 3. 데이터베이스에 발생되는 트랜잭션의 유형을 파악

 4. 용량과 트랜잭션의 유형에 따라 반정규화 수행

 5. 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등을 수행

 6. 성능관점에서 데이터 모델을 검증

 

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

 - 용량 산정은 전체적인 데이터베이스에 발생되는 트랜잭션의 유형과 양을 분석하는 자료가 되므로 중요한 작업이다

 - 물리적인 데이터 모델링 시

    PK/FK 컬럼 순서조정, FK인덱스 생성 등은 성능 향상을 위한 데이터 모델링 작업에 중요한 요소이다

 - 이력데이터는 시간에 따라 반복적으로 발생돼서 대량일 가능성이 높아 성능을 고려하여 컬럼 등을 추가하도록 설계

 - 정규화 정확히 수행, 용량산정 수행, 트랜잭션 유형 파악, 반정규화 수행,

   이력모델 조정, PK/FK, 슈퍼타입/서브타입 조정, 성능관점 데이터모델 검증

 

* 정규화

 - 제 1 정규형 : 릴레이션의 모든 속성 값이 원자 값 (반복적인 속성 값을 갖는 형태는 원자성을 위배한 것)

 - 제 2 정규형 : 1NF이고, 기본 키가 아닌 속성이 기본 키에 완전 함수 종속성

 - 제 3 정규형 : 2NF이고, 기본 키가 아닌 속성이 기본 키에 비 이행적으로 종속

 - BCNF : 릴레이션이 함수 종속성 X->Y가 성립할 때 모든 결정자 X가 후보 키

 - 제 4 정규형 : 다치 종속 A->->B가 존재할 때 릴레이션 모든 속성이 A에 함수종속일 때

                     (R의 모든 속성 X가 A->X, A가 후보 키)

 - 제 5 정규형 : 릴레이션의 모든 조인종속이 릴레이션의 후보 키를 통해 성립

 

* 반정규화

 - 정규화 된 엔티티, 속성, 관계에 대해 시스템의 성능향상과

   개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법

 - 데이터를 중복하여 성능을 향상시키기 위한 기법

 - 성능을 향상시키기 위해 정규화 된 데이터 모델에서 중복, 통합, 분리 등을 수행

 - 데이터 무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 반정규화를 적용하는 이유는

   데이터 조회 시 디스크 IO량이 많아서 성능이 저하되거나

   경로가 너무 멀어서 조인으로 인한 성능 저하가 예상되거나

   컬럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우 반정규화를 수행한다

 

* 반정규화 고려 시 판단 요소

 - 다량 데이터 탐색의 경우 파티션, 데이터 클러스터링 등 다양한 기법으로 성능 개선 가능

 - 하나의 결과 셋을 추출하기 위해 다량 데이터를 탐색하는 처리가 반복적이고 빈번하면 반정규화 고려

 - 이전 또는 이후 위치의 레코드에 대한 탐색은 윈도우 함수로 접근 가능

 - 집계 테이블 외에도 다양한 유형에 대해 적용 (다수 테이블의 키 연결 테이블 등)

 

* 테이블의 반정규화

 - 테이블 병합 (1:1 관계 테이블 병합, 1:M 관계 테이블 병합, 슈퍼/서브타입 테이블 병합)

 - 테이블 분할 

   수직분할 – 디스크 IO 분산 처리를 위해 테이블을 1:1로 분리

   수평분할 – 로우 단위로 집중 발생되는 트랜잭션 분석으로 디스크 IO 및 데이터 접근 효율성을 높임, 로우 분리

 - 테이블 추가 

   중복테이블 추가 – 다른 업무이거나 서버가 다른 경우 동일한 테이블 구조 중복하여 원격 조인 제거

   통계테이블 추가 – SUM,AVG등을 미리 수행하여 계산해 둠으로써 조회 시 성능 향상

   이력테이블 추가 – 이력테이블 중 마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재하게 함

   부분테이블 추가 – 집중화 된 컬럼이 있을 때 디스크 IO를 줄이기 위해 해당 컬럼들을 모아 놓은 별도의 테이블

 

* 컬럼의 반정규화

 - 중복 컬럼 추가 : 조인에 의해 처리할 때 성능 저하를 예방하기 위해, 조인을 감소시키기 위해 중복된 컬럼 위치시킴

 - 파생 컬럼 추가 : 트랜잭션 처리 시점에 계산에 의해 발생되는 성능저하 예방을 위해 미리 계산, Derived column

 - 이력테이블 컬럼 추가 : 대량 이력데이터 처리 시 불특정 날 조회나 최근 값 조회 시 성능저하 예방을 위해 추가

 - PK에 의한 컬럼 추가 : PK에 이미 데이터가 존재하지만 성능향상을 위해 일반속성으로 포함하는 방법

 - 응용 시스템 오작동을 위한 컬럼 추가 : 이전 데이터를 임시적으로 중복하여 보관하는 기법

 

* 관계의 반정규화

 - 중복관계 추가 : 데이터 처리를 위해 여러 경로 조인 시 발생하는 성능저하 예방을 위해 추가적인 관계를 맺는 방법

 

* 반정규화 절차

 1. 반정규화 대상조사 : 범위처리 빈도수 조사, 대량 범위처리 조사, 통계성 프로세스 조사, 테이블 조인 개수

 2. 다른 방법유도 검토 : 뷰 테이블, 클러스터링 적용, 인덱스 조정, 응용 어플리케이션

 3. 반정규화 적용 : 테이블 반정규화, 속성 반정규화, 관계 반정규화

 

* 반정규화 대상에 대해 다른 방법으로 처리

 - 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려운 경우 뷰를 사용하면 해결 가능

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

 - 대량 데이터는 PK의 성격에 따라 부분적인 테이블로 분리할 수 있음, 파티셔닝 기법이 적용되어 성능 저하 방지

 - 응용 애플리케이션에서 로직을 구사하는 방법을 변경하여 성능 향상

 

* 컬럼수가 많은 테이블

 - 데이터가 물리적으로 저장되는 디스크 상에 넓게 분포, 디스크 IO가 대량으로 발생, 성능 저하

 - 트랜잭션이 접근하는 컬럼유형을 분석해서

   자주 접근하는 컬럼들과 상대적으로 접근 빈도가 낮은 컬럼을 구분해서 1:1 테이블 분리

 - 컬럼 위치 조정으로 NULL이 존재하는 컬럼을 뒤로 모아서 로우의 길이 감소시킬 수 있음

 - NULL 상태이던 컬럼에 데이터가 채워지면 더 많은 로우 체인이 발생할 수 있어서 바람직하지 않음

 

* 파티셔닝

 - 하나의 테이블에 많은 양의 데이터가 저장되면 인덱스를 추가하고 테이블을 몇 개로 쪼개도 성능이 저하되는 경우

 - 논리적으로 하나의 테이블이지만 물리적으로 여러 테이블로 분리

 - 액세스 성능 향상, 데이터 관리방법 개선하도록 테이블에 적용하는 기법

 - range partition : 날짜나 숫자로 분리가 가능, 데이터 보관 주기에 따라 데이터를 쉽게 drop 가능

 - list partition : 특정 값에 따라 분리 저장, 보관주기에 따라 쉽게 삭제 기능은 제공 안됨

 - hash partition : 설계자는 정확히 어떻게 들어갔는지 알 수 없음. 데이터 보관 주기에 따라 쉽게 삭제 가능

 

* 수평/수직 분할 절차

  1) 데이터 모델링 완성

  2) 데이터베이스 용량산정

  3) 대량 데이터가 처리되는 테이블에 대해 트랜잭션 처리 패턴 분석

  4) 컬럼 단위로 집중화된 처리가 발생하는지, 로우 단위로 집중화된 처리가 발생하는지 분석하여  

     집중화된 단위로 테이블을 분리하는 것을 검토

 

* 슈퍼/서브 타입 데이터 모델 변환

 - 트랜잭션은 항상 일괄로 처리하는데, 테이블은 별개로 유지되어 union 연산에 의해 성능 저하

 - 트랜잭션은 서브타입 개별로 처리하는데, 테이블은 하나로 통합되어 불필요한 많은 데이터가 집약되어 성능 저하

 - 트랜잭션은 슈퍼+서브타입을 공통으로 처리하는데, 개별로 유지되어 있거나 하나의 테이블로 집약되어 성능 저하

 

* 슈퍼/서브 타입 데이터 모델 변환기술

 - 개별로 발생되는 트랜잭션에 대해 개별 테이블로 구성

 - 슈퍼타입 + 서브타입에 대해 발생되는 트랜잭션에 대해 슈퍼타입 + 서브타입 테이블로 구성

 - 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성

 

* 슈퍼/서브타입 데이터 모델의 변환 타입 비교

 - onetoone type : 개별 테이블 유지, 확장성 우수, 조인성능 나쁨, io성능 좋음,

                          관리용이성 좋지 않음, 개별 테이블로 접근이 많은 트랜잭션 선택

 - plus type : 슈퍼+서브타입 테이블, 확장성 보통, 조인성능 나쁨, io성능 좋음,

                  관리용이성 좋지 않음, 슈퍼+서브 형식으로 데이터를 처리하는 트랜잭션 선택

 - single type : 하나의 테이블, 확장성 나쁨, 조인성능 우수, io성능 나쁨,

                     관리용이성 좋음, 전체를 일괄적으로 처리하는 트랜잭션 선택

 

* PK/FK 컬럼 순서

 - 일반적으로 B+TREE 구조 많이 사용

 - 순서 조정 없이 DDL 생성 시 데이터 처리 성능 문제 유발 가능

 - FK는 반드시 인덱스 생성, 조회 조건 고려하여 인덱스 컬럼 순서를 효율적으로 생성해야 함

 - 물리적으로 FK를 사용하지 않아도 SQL WHERE 절에서 조인으로 이용되는 경우가 많음

 - 물리적 테이블에 FK 제약을 걸었을 때 반드시 FK 인덱스 생성, FK 제약이 없으면 FK인덱스 생성을 기본정책으로 함

 - 발생되는 트랜잭션에 의해 거의 활용되지 않을 때만 FK 인덱스를 지우는 방법이 적절

 

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

 - 분할 투명성 : 단편화, 하나의 논리적 릴레이션이 단편으로 분할되어 각 단편 사본이 여러 사이트에 저장

 - 위치 투명성 : 위치정보가 시스템 카탈로그에 유지, 데이터 저장 장소 명시 불필요

 - 지역사상 투명성 : 지역DBMS와 물리적 DB 간의 매핑 보장

 - 중복 투명성 : DB 객체가 여러 사이트에 중복 되어 있는지 알 필요가 없음

 - 장애 투명성 : 구성요소의 장애에 무관한 트랜잭션의 원자성 유지

 - 병행 투명성 : 다수 트랜잭션 동시 수행 시 결과의 일관성 유지, 분산 2단계 locking을 이용하여 구현

 

* 분산 데이터베이스 장점

 - 지역 자치성, 점증적 시스템 용량 확장

 - 신뢰성과 가용성

 - 효용성과 융통성

 - 빠른 응답 속도와 통신 비용 절감

 - 데이터의 가용성과 신뢰성 증가

 - 시스템 규모의 적절한 조절

 - 각 지역 사용자의 요구 수용 증대

 

* 분산 데이터베이스 단점

 - 소프트웨어 개발 비용

 - 오류의 잠재성 증대

 - 설계, 관리의 복잡성과 비용

 - 불규칙한 응답 속도

 - 통제의 어려움

 - 데이터 무결성에 대한 위협

 

* 분산 데이터베이스

 - 공통코드, 기준정보 등 마스터 데이터는 분산 데이터베이스에 복제분산을 적용

 - 거의 실시간 업무적인 특성을 가지고 있을 때 분산 데이터베이스를 사용하여 구성

 - 백업 사이트를 구성할 때 간단하게 분산 기능을 적용하여 구성

 

* 테이블 위치 분산

 - 테이블 구조는 변하지 않음, 중복 생성X, 설계된 테이블 위치를 각각 다르게 위치

 

* 테이블 분할 분산

 - 각 테이블을 쪼개서 분산

 - 수평분할 : 컬럼 분리X, 집합시켜도 중복X, 데이터 수정 시 다른 곳 데이터는 수정 안함,

                 통합처리 시 조인으로 성능 저하 예상, 데이터 무결성 보장

 - 수직분할 : 컬럼을 분리, 동일한 PK구조와 값을 가져야 함, 동일한 PK 하나로 표현되면 데이터 중복X, 사례 드뭄

 

* 테이블 복제 분산

 - 동일한 테이블을 동시 생성하여 관리

 - 부분 복제 : 일부만 다른 지역이나 서버에 위치, 중복 발생 가능,

                  복제 시간 많이 소요, 서버에 부하, 야간에 배치 작업으로 수행

 - 광역 복제 : 마스터 데이터베이스의 테이블 내용을 각 지역이나 서버에 존재시킴,

                  본사에서 데이터 수정, 지사에서 이용, 배치

 

* 테이블 요약 분산

 - 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우, 분산된 동일한 내용의 데이터를 이용하여 통합

 - 분석 요약 : 지사의 요약정보를 본사에서 재산출하여 통합, 야간에 수행하여 생성

 - 통합 요약 : 지사의 정보를 본사에서 통합하여 요약정보 산출,

                  모든 지사 데이터 조인 시 성능 지역, 서버 부하, 업무 장애 , 야간에 수행

 

* FK

 - 데이터베이스에 생성하는 FK 제약조건은 데이터 모델 상에 표현된 논리적인 관계에 따라

   관련 인스턴스 간에 일관성을 보장하기 위해 설계된 제약조건을 구현하도록 DBMS가 제공해주는 하나의 지원기능

 

* GSI

 - global single instance 는 통합된 한 개의 인스턴스

 - 통합 데이터베이스 구조를 의미하므로 분산 데이터베이스와는 대치되는 개념