(SQLD) 데이터 모델과 성능 2

옵티마이저(Optimizer)란? 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다. 컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있다.

Index는 RDBMS에서 검색 속도를 높이기 위한 기술이다. TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색 시 해당 TABLE의 레코드를 Full Scan하는게 아니라 색인화 되어있는 INDX 파일을 검색하여 검색속도를 빠르게 한다.

 

정규화를 사용한 성능 튜닝

- 조인으로 인하여 성능이 저하되는 문제를 반정규화로 해결할 수 있다.

- 반정규화는 데이터를 중복시키기 때문에 또 다른 문제점을 발생시킨다.

 

반정규화(De-Normalization)

- 데이터베이스의 성능향상을 위하여, 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법이다.

- 반정규화는 조회(SELECT)속도를 향상시키지만, 데이터 모델의 유연성은 낮아진다.

 

반정규화를 수행하는 경우

- 정규화에 충실하면 종속성, 활용성은 향상되지만 수행 속도가 느려지는 경우

- 다량의 범위를 자주 처리해야 하는 경우

- 특정 범위의 데이터만 자주 처리하는 경우

- 요약/집계 정보가 자주 요구되는 경우

 

반정규화 절차

1) 반정규화 대상 조사 및 검토: 범위처리빈도수 조사, 대량의 범위 처리 조사, 통계성 프로세스 조사, 테이블 조인 개수. 데이터 처리 범위, 통계성 등을 확인해서 반정규화 대상을 조사한다.

2) 다른 방법유도 검토: 뷰(VIEW) 테이블, 클러스터링 적용, 인덱스의 조정, 응용애플리케이션. 반정규화를 수행하기 전에 다른 방법이 있는지 검토한다. 예를 들어 클러스터링, 뷰, 인덱스 튜닝, 응용 프로그램, 파티션 등을 검토한다.

3) 반정규화 수행: 테이블 반정규화, 속성의 반정규화, 관계의 반정규화. 테이블, 속성, 관계 등을 반정규화 한다.

 

클러스터링(Clustering)

- 클러스터링 인덱스라는 것은 인덱스 정보를 저장할 때 물리적으로 정렬해서 저장하는 방법이다.

- 따라서 조회 시에 인접 블록을 연속적으로 읽기 때문에 성능이 향상된다.

 

반정규화 기법

테이블의 반정규화

1) 테이블병합

- 1:1 관계 테이블병합: 1:1관계를 통합하여 성능향상

- 1:M 관계 테이블병합: 1:M 관계 통합하여 성능향상

- 슈퍼/서븥타입 테이블병합: 슈퍼/서브 관계를 통합하여 성능향상

2) 테이블분할

- 수직분할: 칼럼단위의 테이블을 디스크 I/O를 분산처리 하기 위해 테이블을 1:1로 분리하여 성능향상(트랜잭션의 처리되는 유형을 파악이 선행되어야 함)

- 수평분할: 로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O및 데이터접근의 효율성을 높여 성능을 향상하기 위해 로우단위로 테이블을 쪼갬(관계가 없음)

3)테이블추가

- 중복테이블 추가: 다른 업무이거나 서버가 다른 경우 동일한 테이블구조를 중복하여 원격조인을 제거하여 성능을 향상

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

- 이력테이블 추가: 이력테이블 중에서 마스터 테이블에 존재하는 레코드를 중복하여 이력테이블에 존재하는 방법은 반정규화의 유형

- 부분테이블 추가: 하나의 테이블의 전체 칼럼 중 자주 이용하는데 자주 이용하는 집중화된 칼럼들이 있을 때 디스크 I/O를 줄이기 위해 해당 칼럼들을 모아놓은 별도의 반정규화된 테이블을 생성

칼럼 반정규화

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

2) 파생칼럼 추가: 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 값을 계산하여 칼럼에 보관함. Derived Column이라고 함.

3) 이력테이블 칼럼추가: 대량의 이력데이터를 처리할 때 불특정 날 조회나 최근 값을 조회할 때 나타날 수 있는 성능저하를 예방하기 위해 이력테이블에 기능성 칼럼(최근값 여부, 시작과 종료일자 등)을 추가함

4) PK에 의한 칼럼 추가: 복합의미를 갖는 PK를 단일 속성으로 구성하였을 경우 발생됨. 단일 PK안에서 특정값을 별도로 조회하는 경우 성능저하가 발생될 수 있음. 이 때 이미 PK안에 데이터가 존재하지만 성능향상을 위해 일반속성으로 포함하는 방법이 PK의한 칼럼추가 반정규화임

5) 응용시스템 오작동을 위한 칼럼 추가: 업무적으로는 의미가 없지만 사용자가 데이터처리를 하다가 잘못 처리하여 원래 값으로 복구하기를 원하는 경우 이전 데이터를 임시적으로 중복하여 보관하는 기법. 칼럼으로 이것을 보관하는 방법은 오작동 처리를 위한 임시적인 기법이지만 이것을 이력데이터 모델로 풀어내면 정상적인 데이터 모델의 기법이 될 수 있음

관계 반정규화

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

테이블과 칼럼의 반정규화는 데이터 무결성에 영향을 미치게 되나 관계의 반정규화는 데이터 무결성을 깨트릴 위험을 갖지 않고서도 데이터처리의 성능을 향상시킬 수 있는 반정규화의 기법이 된다. 데이터 모델 전체가 관계로 연결되어 있고 관계가 서로 먼 친척간에 조인관계가 빈번하게 되어 성능저하가 예상이 된다면 관계의 반정규화를 통해 성능향상을 도모할 필요가 있다.,u

 

1) 계산된 칼럼 추가: 배치 프로그램으로 총판매액, 평균잔고, 계좌평가 등을 미리 계산하고, 그 결과를 특정 칼럼에 추가한다.

2) 테이블 수직분할: 하나의 테이블을 두 개 이상의 테이블로 분할한다. 즉, 칼럼을 분할하여 새로운 테이블을 만드는 것이다.

 3) 테이블 수평분할: 하나의 테이블에 있는 값을 기준으로 테이블을 분할하는 방법이다.

파티션 기법*

- 데이터베이스에서 파티션을 사용하여 테이블을 분할할 수 있다. 파티션을 사용하면 논리적으로는 하나의 테이블이지만 여러 개의 데이터 파일에 분산되어서 저장된다.

- Range Partition: 데이터 값의 범위를 기준으로 파티션을 수행한다.

- List partition: 특정한 값을 지정하여 파티션을 수행한다.

- Hash Partition: 해시함수를 적용하여 파티션을 수행한다.

 > 해시 함수는 임의의 길이를 갖는 메시지를 입력 받아 고정된 길이의 해시 값을 출력하는 함수이다. 암호 알고리즘에는 키가 사용되지만, 해시 함수는 키를 사용하지 않으므로 같은 입력에 대해서는 항상 같은 출력이 나오게 된다. 이러한 해시함수를 사용하는 목적은 메시지의 오류나 변조를 탐지할 수 있는 무결성을 제공하기 위해 사용된다.

- Composite Partition: 범위와 해시를 복합적으로 사용하여 파티션을 수행한다.

※파티션 테이블의 장점

- 데이터 조회 시에 엑세스(Access) 범위가 줄어들기 때문에 성능이 향상된다.

- 데이터가 분할 되어 있기 때문에 I/O(Input/Output)의 성능이 향상된다.

- 각 파티션을 독립적으로 백업 및 복구가 가능하다.

4) 테이블 병합

- 1대1 관계의 테이블을 하나의 테이블로 병합해서 성능을 향상시킨다.

- 1대N 관계의 테이블을 병합하여 성능을 향상시킨다. 하지만 많은 양의 데이터 중복이 발생한다.

- 슈퍼타임과 서브타임 관계가 발생하면 테이블을 통합하여 성능을 향상시킨다.

> Super type과 Subtype

- 고객 엔터티는 개인고객과 법인고객으로 분류된다. 이때 고객 엔터티는 슈퍼 타입이고 개인고객과 법인 고객은 서브타입이 된다. 즉, 부모와 자식 간의 관계가 나타난다.

- 슈퍼 타입과 서브타입의 관계는 배타적 관계와 포괄적 관계가 있는데, 배타적 관계는 고객이 개인고객이거나 법인고객인 경우를 의미한다. 포괄적 관계는 고객이 개인고개일 수도 있고 법인고객일 수도 있는 것이다.

- 변환 방법

1)  OneToOne Type(1:1 타입): 슈퍼타입과 서브타입을 개별 테이블로 도출한다. 테이블의 수가 많아서 조인이 많이 발생하고 관리가 어렵다.

2) Plus Type(슈퍼 + 서브타입): 슈퍼 타입과 서브타입 테이블로 도출한다. 조인이 발생하고 관리가 어렵다.

3) Single Type(All in One 타입): 슈퍼 타입과 서브타입을 하나의 테이블로 도출한다. 조인성능이 좋고 관리가 편하지만, 입출력 성능이 나쁘다.

 

데이터베이스 구조와 성능

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

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

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

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

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

 

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

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

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

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

 

분산 데이터베이스

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

- 논리적으로 동일한 시스템에 속하지만, 컴퓨터 네트워크를 통해 물리적으로 분산되어 있는 데이터들의 모임. 물리적 Site 분산, 논리적으로 사용자 통합,공유

- 데이터베이스 시스템 구축 시에 한 대의 물리적 시스템에 데이터베이스 관리 시스템(DBMS)을 설치하고 여러 명의 사용자가 데이터베이스 관리 시스템에 접속하여 데이터베이스를 사용하는 구조를 중앙 집중형 데이터베이스라고 한다.

- 또한 물리적으로 떨어진 데이터베이스에 네트워크로 연결하여 단일 데이터베이스 이미지를 보여주고 분산된 작업 처리를 수행하는 데이터베이스를 분산 데이터베이스라고 한다.

- 분산 데이터베이스를 사용하는 고객은 시스템이 네트워크로 분산되어 있는지의 여부를 인식하지 못하면서, 자신만의 데이터베이스를 사용하는 것처럼 사용할 수 있다. 이처럼 데이터베이스는 투명성을 제공해야 한다.

- 투명성은 분산 데이터베이스에서 중요한 요소이며 투명성의 종류에는 분할, 위치, 지역사상, 중복, 장애 및 병행 투명성이 있다.

- 사상이라는 것은 매핑과 유사한 개념이라고 생각하면 된다. 특정 스키마가 바뀌더라도 매핑을 다시 함으로써 다른 계층의 스키마의 내용은 변하지 않아도 되는 것이다.

 

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

1) 분할 투명성(단편화): 고객은 하나의 논리적 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 시스템에 저장되어 있음을 인식할 필요가 없다.

2) 위치 투명성: 고객이 사용하려는 데이터의 저장 장소를 명시할 필요가 없다. 고객은 데이터가 어느 위치에 있더라도 동일한 명령을 사용하여 데이터에 접근할 수 있어야 한다.

3) 지역 사상 투명성: 지역 DBMS와 물리적 데이터베이스 사이의 사상이 보장됨에 따라 각 지역 시스템 이름과 무관한 이름이 사용 가능하다.

4) 중복 투명성: 데이터베이스 객체가 여러 시스템에 중복되어 존재함에도 고객과는 무관하게 데이터의 일관성이 유지된다.

5) 장애 투명성: 데이터베이스가 분산되어 있는 각 지역의 시스템이나 통신망에 이상이 발생해도, 데이터의 무결성은 보장된다.

6) 병행 투명성: 여러 고객의 응용 프로그램이 동시에 분산 데이터베이스에 대한 트랜잭션을 수행하는 경우에도 결과에 이상이 없다.

 

분산 데이터베이스 설계 방식

1) 상향식 설계 방식: 지역 스키마 작성 후 향후 전역 스키마를 작성하여 분산 데이터베이스를 구축한다.

2) 하향식 설계 방식: 전역 스키마 작성 후 해당지역 사상 스키마를 작성하여 분산 데이터베이스를 구축한다.

분산 데이터베이스를 하향식 접근방식으로 구축한다는 것은 기업 전체의 전사 데이터 모델을 수렴하여 전역 스키마를 생성하고, 그 다음 각 지역별로 지역 스키마를 생성하여 분산 데이터베이스를 구축하는 것이다. 상향식 접근방식은 지역별로 데이터베이스를 구축한 후에 전역 스키마로 통합하는 것이다.

분산 데이터베이스를 구축하거나 운영할 때 동일한 데이터베이스 관리 시스템으로 분산 데이터베이스를 구축하는 것은 크게 어렵지 않다. 하지만 기업에 여러 종류의 데이터베이스 관리 시스템이 있으면 이기종 데이터베이스 관리 시스템으로 연동해야 한다. 이기종 데이터베이스시스템으로 연동하기 위해서는 데이터베이스 미들웨어(ODBC, JDBC)를 사용해야 한다.

 

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

1) 장점

- 데이터베이스 신뢰성과 가용성이 높다.

- 분산 데이터베이스가 병렬 처리를 수행하기 때문에 빠른 응답이 가능하다.

- 분산 데이터베이스를 추가하여 시스템 용량 확장이 쉽다.

2) 단점

- 데이터베이스가 여러 네트워크를 통해서 분리되어 있기 때문에 관리와 통제가 어렵다.

- 보안관리가 어렵다.

- 데이터 무결성 관리가 어렵다.

- 데이터베이스 설계가 복잡하다.