[SQLD 정리]9DAY

과목 1 데이터 모델링의 이해

1장 데이터 모델과 성능

제 4절 대량 데이터에 따른 성능

1. 대량 데이터발생에 따른 테이블 분할 개요

# 대량 데이터가 발생하는 테이블의 문제점

- 설계가 잘 되어 있는 데이터 모델이라도 대량의 데이터가 하나의 테이블에 집약되어 있고 하나의 하드웨어 공간에 저장되어 있으면 성능 저하를 피하기 힘들다.

- 인덱스도 또한 트리가 커지고 깊이가 깊어져, 조회성능에 영향을 미치게 된다.

- 입력/수정/삭제의 트랜잭션인 경우도 인덱스의 특성상 일량이 증가하여, 더많은 성능저하를 유발하게 된다.

- 컬럼이 많아지게 되면 물리적인 디스크의 여러 블록에 걸쳐 데이터가 저장되게 되며, 로우 길이가 너무 길어서 로우체이닝과 로우 마이그레이션이 많아지게 되어 성능이 저하된다.

 

2. 한 테이블에 많은 수의 칼럼을 가지고 있는 경우

# 200개의 컬럼을 가진 도서정보 테이블이 있다고 가정하고, 하나의 로우 길이가 10K이고 블록이 2K단위로 쪼개져 있으면, 로우는 대략 5개의 블록에 걸쳐 저장된다.

# 이렇게 여러블록에 컬럼이 흩어져 있다면, 디스크 I/O가 많이 일어나게 된다.

# 트랜잭션 발생시 어떤 컬럼에 대해 집중적으로 발생되는지 분석하여 테이블 분할을 하면 디스크 I/O가 감소하여 성능을 개선할 수 있다.

 

3. 대량 데이터 저장 및 처리로 인한 성능

# 대량 데이터가 예상될 경우, 파티셔닝 및 PK에 의해 테이블을 분할하는 방법을 적용할 수 있다.

# 오라클의 경우 LIST PARTITION, RANGE PARTITION, HASH PARTITION, COMPOSITE PARTITION 등이 가능하다.

가. RANGE PARTITION

 

[ RANGE PARTITION ]

# 요금정보처럼 항상 월 단위로 데이터를 처리하는 특성을 가진 경우, PK인 요금일자의 년 + 월을 이용하여 12개의 파티션 테이블을 만들어서 성능개선을 유도한다.

# 가장 많이 사용하는 대상 테이블이 날짜 또는 숫자값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리된다면 적용한다.

# 데이터 보관주기에 따라 테이블에 데이터를 쉽게 지우는 것이 가능하므로 테이블관리가 용이하다.

 

나. LIST PARTITION 적용

 

[ LIST PARTITION ]

# 지점, 사업소, 사업장, 핵심적인 코드 값등으로 PK가 구성되어 있는 테이블이라면 값 각각에 의해 파티셔닝이 되는 LIST PARTITION을 적용할 수 있다.

# 특정 값에 따라 분리저장할 수 있으나 RANGE PARTITION과 괕이 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공될 수 없다.

 

다. HASH PARTITION

# HASH 조건에 따라 해시 알고리즘이 적용되어서 테이블이 분리되므로 설계자는 데이터가 어떤 테이블에 어떻게 들어갔는지 알 수없다.

# HASH PARTITION도 보관주기에 따라 쉽게 삭제하는 기능은 제공될 수 없다.

 

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

# 순서

1. 데이터 모델링을 완성한다.

2. 데이터베이스 용량산정을 한다.

3. 대량 데이터가 처리되는 테이블에 대해서 트랜잭션 처리 패턴을 분석한다.

4. 컬럼 단위로 집중화된 처리가 발생하는지, 로우 단위로 집중화된 처리가 발생되는지 분석하여 집중화된 단위로 테이블을 분리하는 것을 검토한다.

 

# 컬럼수가 너무 많은 경우는 테이블을 1:1형태로 분리할 수 있는지 검증하고 컬럼 수가 적지만 데이터 용량이 많아 성능저하가 예상되는 경우 테이블에 대해 파티셔닝 전략을 고려하도록 한다.

 

제 5절 데이터베이스 구조와 성능

1. 슈퍼타입/서브타입 모델의 성능고려방법

가. 슈퍼타입/서브타입 데이터 모델의 개요

# 슈퍼타입/서브타입 모델 업무를 구성하는 데이터의 특징을 공통과 차이점의 특징을 고려하여 효과적으로 표현할 수 있으므로 자주 쓰이는 모델링의 방법이다.

# 이 모델은 논리데이터 모델에서 이용되는 형태이고 물리적인 데이터 모델을 설계하는 단계에서는 일정한 기준에 의해 변환을 해야한다.

# 아무런 기준없이 막연히 변환하는 것 자체가 성능 저하의 위험이 있다.

 

나. 슈퍼타입/서브타입 데이터 모델의 변환

# 슈퍼타입/서브타입에 대한 변환을 잘못하면 성능이 저하되는 이유는 트랜잭션 특성을 고려하지 않고 테이블이 설계되었기 때문이다.

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

# 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 성능저하

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

# 데이터의 양이 소량일 경우 처리의 유연성을 고려해 1:1 관계를 유지하는 것이 바람직하다.

# 대용량일 경우는 트랜잭션의 발생형태에 따라 3가지 변환방법을 참조하여 상황에 맞게 변환하도록 해야한다.

 

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

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

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

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

 

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

 

[ 변환타입 비교 ]

구분	One To One Type	Plus Type	Single Type
특징	# 개별 테이블 유지	# 슈퍼타입 + 서브타입 테이블	# 하나의 테이블
확장성	# 우수함	# 보통	# 나쁨
조인성능	# 나쁨	# 나쁨	# 우수함
I/O량 성능	# 좋음	# 좋음	# 나쁨
관리용이성	# 좋지않음	# 좋지않음	# 좋음
트랜잭션 유형에 따른 선택방법	# 개별 테이블로 접근이 많은 경우 선택	# 슈퍼형식 + 서브형식으로 데이터를 처리하는 경우 선택	# 전체를 일괄적으로 처리하는 경우 선택

 

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

가. PK/FK 칼럼 순서와 성능 개요

# 인덱스는 데이터를 접근할 때 경로를 제공하는 성능 측면에도 중요한 의미를 가지고 있기 때문에 설계단계 말에 컬럼의 순서를 조정할 필요가 있다.

# PK가 복합식별자인 경우, 앞쪽에 위치한 속성이 가급적 '=' 이거나 최소범위 'BETWEEN', '<>'가 들어와야 인덱스를 이용할 수 있다.

# 또한, FK라 하더라도 인덱스를 생성하도록하고 인덱스 칼럼의 순서도 조회조건을 고려하여 접근이 가장 효율적인 순서대로 생성한다.

 

나. PK컬럼의 순서를 조정하지 않으면 성능이 저하이유

 

[ 데이터모델링에서 설계된 테이블의 PK 순서대로 인덱스가 만들어진 경우 ]

# 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되었으므로 인덱스 범위를 넓게 이용하거나 FULL SCAN을 유발하게 된다.

 

다. PK순서를 잘못 지정하여 성능이 저하된 경우 - 간단한 오류

 

[ 간단한 오류 예시 ]

# 입시마스터 테이블의 PK: 수험번호 + 년도 + 학기

# 입시마스터 테이블을 조회할 때 조회조건에 수험번호가 들어오지 않아 FULL TABLE SCAN이 발생됨

# 년도와 학기에 대한 내용이 빈번하게 들어오므로 PK순서를 변경함으로써 인덱스를 정상적으로 이용할 수 있게한다.

 

라. PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류

 

[ 복잡한 오류 예시 ]

# 현금출급기실적의 PK는 거래일자 + 사무소코드 + 출급기번호 + 명세표번호로 구성

# 대부분의 SQL문장은 거래일자가 BETWEEN으로 들어오고 사무소코드가 '='로 들어와서 인덱스를 이용할 수 있으나 요율성이 떨어짐

# 인덱스 칼럼을 사무소코드 + 거래일자 + 출급기번호 + 명세표번호로 변경

# 테이블의 PK속성이 A + B와 B + A의 형태로도 빈번하게 조회되는 경우, 더 자주이용되는 형태로PK순서를 구성하고 순서를 바꾼 인덱스를 추가로 생성하는 것이 필요

 

3. 물리적인 테이블에 FK 제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하

# 두 테이블 사이에 FK 참조 무결성 관계가 걸려있지 않더라도 데이터 모델관게에 의해 상속받은 FK속성들은 조인조건으로 이용하는 경우가 많으므로 FK인덱스를 생성하는 것을 기본정책으로 하는 것이 좋다.

# FK인덱스 생성을 기본정책으로 하되 향후 트랜잭션에 의해 거의 활용되지 않았을 때만 자우는 것이 적절한 방법이 된다.

 

제 6절 분산 데이터베이스와 성능

1. 분산 데이터베이스의 개요

# 분산 데이터베이스란?

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

- 논리적으로 동일한 시스템에 속하나 네트워크를 통해 물리적으로 분산되어있는 데이터들의 모임

- 데이터베이스를 연결하는 빠른 네트워크 환경을 이용하여 데이터베이스를 여러 지역 및 노드로 위치시켜 사용성/성능을 극대화시킨 데이터베이스

 

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

# 분할 투명성

- 하나의 논리적 관계가 여러 단편으로 분할되어 각 단편의 사본이 여러 사이트에 저장

 

# 위치 투명성

- 사용하려는 데이터의 저장장소를 명시가 필요하지 않음

- 위치정보는 SYSTEM CALTALOG에 유지되어야함.

 

# 지역사상 투명성

- 지역 DBMS와 물리적 DB 사이의 MAPPING 보장

- 각 지역시스템 이름과 무관한 이름 사용가능

 

# 중복 투명성 

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

 

# 장애 투명성 

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

 

# 병행 투명성

- 다수 트랜잭션 동시 수행 시 결과의 일관성 유지

- TIME STAMP

- 분산 2단계 LOCKING을 이용하여 구현

 

# 6가지 특징을 모두 만족하며 구축하는 사례가 드물다.

# 최근에는 분산환경의 데이터베이스를 구축하기보다 통합하여 데이터베이스를 구축하는 사례가 더 많이 늘었음

# 그럼에도 업무적인 특징 및 지역적인 특징에 따라 적절히 활용하면 다양한 장점을 가지고 있으므로 대량 데이터의 지역적/글로벌 처리 등에서 유용하게 활용되고 있음

 

3. 분산 데이터베이스의 적용방법 및 장단점

가. 분산 데이터베이스 적용방법

# 우수한 성능으로 현장에서 가치있게 사용하는 방법은 업무의 흐름을 보고 업무구성에 따른 아키텍쳐 특징에 따라 데이터베이스를 구성하는 것이다.

# 단순히 분산환경에서 데이터베이스를 구축하는 것이 목적이 아니라 업무의 특징에 따라 데이터베이스 분산구조를 선택적으로 설계하는 능력이 필요하다.

# 이러한 측면에서 데이터베이스 분산설계라는 측면보다 데이터베이스 구조설계라는 의미로 이해해도 무방하다.

 

나. 분산 데이터베이스 장단점

# 장점

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

- 신뢰성과 가용성

- 효용성과 융통성

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

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

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

 

# 단점

- 소프트웨어 개발 비용

- 오류의 잠재성 증대

- 처리비용의 증대

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

- 불규칙한 응답 소고

- 통제의 어려움

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

 

4. 분산 데이터베이스의 활용 방향성

# 업무적 특징에 따라 활용해야한다.

 

5. 데이터베이스 분산구성의 가치

# 통합된 데이터베이스에서 제공할 수 없는 빠른 성능을 제공하는 것이 가능해진다.

 

6. 분산 데이터베이스의 적용기법

# 분산 종류

- 테이블 위치분산

- 테이블 분할분산

- 테이블 복제분산

- 테이블 요약분석전략

 

# 가장 많이 사용하는 방식은 테이블 복제분산 방식이며 성능이 저하되는 많은 데이터베이스에서 가장 유용하게 적용할 수 있는 기술적인 방법이다.

# 설계하는 방법은 일단 통합모델링을 하고 각 테이블별로 업무적 특징에 따라 지역또는 서버별로 테이블을 분산 또는 복제배치하는 형태로 설계할 수 있다.

 

가. 테이블 위치분산

# 테이블의 구조는 변하지 않으며 다른 데이터베이스에 중복되어 생성되지도 않는다.

# 다만 설계된 테이블의 위치를 각각 다르게 위치시키는것이다.

 

나. 테이블 분할분산

# 단순히 테이블의 위치만 다른 곳에 두는 것이 아니라 각각의 테이블을 쪼개어 분산하는 방법이다.

# 수평분할

- 테이블을 특정 칼럼의 값을 기준으로 ROW를 분리한다.

- 칼럼으로 분리되지 않으며 데이터를 한군데 집합시켜 놓아도 PK에 의해 중복발생이 일어나지 않음

- 각 지사별로 사용하는 ROW가 다를 때 이용한다.

- 각 지사에 존재하는 테이블에 대해 통합처리하는 경우 JOIN이 발생하여 성능저하가 예상되므로 통합처리 프로세스가 많지 않은 경우에만 이용

 

# 수직분할

- 특정 칼럼값을 기준으로 칼럼을 분리한다.

- 칼럼을 기준으로 분할하였으므로 각각의 테이블에 동일한 PK구조와 값을 가지고 있어야한다.

- ex) 제품의 재고량은 각 지사별로 관리하고 단가는 본사에서 관리하는 경우

- 실제 프로젝트에는 이런 환경을 구성하는 사례는 드물다.

 

다. 테이블 복제분산

# 동일한 테이블을 다른 지역이나 서버에 동시에 생성하여 관리하는 유형이다.

# 부분복제

- 테이블의 일부 내용만 다른 지역이나 서버에 위치시키는 방법

- ex) 본사 데이터베이스에는 테이블의 전체 내용이 들어가고 각 지사 데이터베이스는 각 지사별로 관계된 데이터만 들어가는 경우이다.

- 보통 지사에서 데이터가 먼저 발생되고 본사에는 지사데이터를 이용하여 통합하여 발생된다.

- 실제 프로젝트에서 많이 사용하는 분산기법에 해당됨

-  다른 지역 간 데이터를 복제하는데는 많은 시간이 소요되므로 야간배치작업을 통해 수행되며 본사 지사 양쪽 데이터를 수정하여 전송하는 경우 정합성을 일치시키는 것이 어렵기 때문에 한쪽에서 데이터 수정이 발행하여 본사로 복제하도록한다.

 

# 광역복제

- 테이블의 내용을 각 지역이나 서버에 위치시키는 방법

- 본사와 지사 모두 동일한 정보를 가지고있어 데이터처리에 특별한 제약을 받지 않는다.

- 실제 프로젝트에서 많이 사용하는 분산기법에 해당됨

- 본사에서 데이터가 입력, 수정, 삭제가 되어 지사에서 이용하는 형태이다.

- 부분 복제와 마찬가지로 데이터를 복제하는데 많은 시간이 소요되고 많은 부하가 발생되므로 배치를 통해 복제가 되도록한다.

 

라. 테이블 요약분산

# 테이브 요약분산은 지역 간 또는 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우가 있다.

# 분석요약

- 동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식

- 각 지사별로 존재하는 요약정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법

 

# 통합요약

- 분산되어 있는 다른 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식

- 각 지사별로 존재하는 다른 내용의 정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법

 

7. 분산 데이터베이스를 적용하여 성능이 향상된 사례

# 적용시 효과적인 경우

- 성능이 중요한 사이트

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

- 실시간 동기화가 요구되지 않을 때, 거의 실시간의 업무적인 특징을 가지고 있을 때도 구성가능

- 특정 서버가 부하집중 시 부하를 분산할 때

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

 

[출처]

http://wiki.gurubee.net/pages/viewpage.action?pageId=26743561

http://wiki.gurubee.net/pages/viewpage.action?pageId=26743563

http://wiki.gurubee.net/pages/viewpage.action?pageId=26743516