데이터베이스 설계와 구축 2부 물리 설계 - 7. 데이터베이스 구축 준비

데이터베이스 설계와 구축 - 성능까지 고려한 데이터 모델링 Part 2 물리설계

 

 

Chapter 7. 데이터베이스 구축 준비

 

1. 관계형 테이블로 전환

 - 엔티티타입은 테이블로 전환

  (1) 독립 엔티티타입은 독립 테이블로 전환

  (2) 완전 종속 엔티티타입은 완전 종속 테이블로 전환

  (3) 부분 종속 엔티티타입은 부분 종속 테이블로 전환

 

 - 주식별자는 PK로 변환

  (1) 데이터 모델의 주식별자는 PK로 전환된다

  (2) 무결성 제약(Referential Integrity)을 유지하는 역할

  (3) PK는 테이블에 있는 각각의 로우를 유일하게 식별

  (4) PK는 Null 값을 갖지 않는다

  (5) 가능하면 모든 테이블에서 PK를 정의

  (6) PK는 변경되지 않음

 

 - 속성은 컬럼으로 변환

 

 - 관계에 의한 외부 식별자는 FK로 변환

  (1) 1:1 주식별자 관계 변환

  (2) 1:1 비식별자 관계 변환

  (3) 1:M 관계 변환

  (4) 자기 참조 관계 변환

  (5) 슈퍼타입/서브타입 관계 변환

    ➀ 각각의 테이블로 변환

    ➁ 서브타입 테이블로 변환

    ➂ 통합 테이블로 변환

 

2. 반정규화

 - 반정규화 주의사항

  * 무분별하게 진행하면 데이터 무결성이 깨져 추적이 불가하거나 정합성을 맞출 수 없는 경우가 생길 수 있음

  * 뷰 생성, 인덱스 조정, 파티셔닝 테이블 생성 등 여러 가지 방안을 먼저 고려한 후 반정규화를 고려

  * 일정한 기준을 정하여 반정규화 대상을 선정하는 작업 후, 반정규화 대상 테이블, 컬럼, 관계에 대해서는 지속적인 추적 관리

 

 - 반정규화 절차

  (1) 반정규화 대상 조사

   * 자주 사용되는 테이블에 접근하는 프로세스 수가 많고, 항상 일정 범위만을 조회하는 경우

   * 테이블에서 대량 데이터 범위를 자주 처리하는 경우 처리 범위를 일정하게 줄이지 않아 성능을 보장할 수 없을 경우

   * 통계성 프로세스에 통계 정보가 필요할 때 별도의 통계 테이블(반정규화 테이블) 생성

   * 지나치게 많은 조인이 걸려 데이터를 조회하는 작업이 기술적으로 어려울 경우

 

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

   * 지나치게 많은 조인이 걸려 데이터 조회 작업이 기술적으로 어려울 경우 뷰를 사용

   * 대량 데이터 처리, 부분 처리로 성능이 저하되는 경우 클러스터링 또는 인덱스 조정을 통해 성능 향상 가능

   * 대량의 데이터는 PK의 성격에 따라 부분적인 테이블로 분리(파티셔닝 기법 적용)

   * 어플리케이션에서 로직을 구사하는 방법을 변경함으로써 성능을 향상시킬 수 있다.

 

  (3) 반정규화 적용

  ● 테이블 반정규화 방법

   ➀ 테이블 병합

     * 두 개의 테이블에 발생하는 프로세스가 항상 SQL문장이 두 개 이상의 테이블을 조인하여 작성되는 테이블 병합

     * 두 테이블 관계가 1:1이고, 한쪽에 필수 관계일 경우 PK가 동일하므로 테이블 병합

     * 관계가 1:M 이라도 항상 두 개의 테이블을 조인하여 조회하는 경우 중복이 발생하더라도 병합

     * 슈퍼타입과 서브타입의 관계일 경우 병합 가능

 

   ➁ 테이블 분할

     - 테이블을 분할해야하는 경우

      * 테이블에 속한 모든 속상에 대해 특정 속성만 집중적으로 사용하는 경우

      * PK에 따라 특별한 범위만 집중적으로 사용하는 경우

     * 지역별 데이터 분산, 데이터 서버별 분산 전략에 의해 분할하는 경우

 

   - 테이블 분할 시 특징

    * full scan 범위가 축소되어 테이블에서 접근하는 양이 감소

    * 테이블에 걸린 잠금, 경합이 감소

    * 분할 테이블을 같이 조회해야 할 경우 SQL문장이 복잡해진다 (UNION, UNION ALL 사용)

    * 단일 테이블 속도는 향상되나 분할된 테이블을 모두 조회할 경우 처리속도 감소

 

   - 테이블 분할 방법

    ➀ 수직적 분할 : 모든 속성을 사용하지 않고 특정 속성만 사용하는 경우

    ➁ 수평적 분할 :  지역별, 시기별, 서버별 등 특정 구간을 선별하여 조회하는 경우

    ➂ 테이블 추가 : 수평, 수직 분할 모두 테이블이 추가적으로 설계되어 통계, 이력 관리 등이 추가적으로 발생할 수 있는 경우

 

   ● 컬럼 반정규화 방법

    (1) 중복 컬럼 방법

     * 인덱스 사용을 고려한 반정규화는 컬럼 반정규화의 특별한 이유

 

    (2) 중복 컬럼 특징

     * 데이터의 무결성 확보가 어려워짐. 데이터 읽는 것은 성능, SQL 문장 작성면에서 나아졌으나 데이터 입력/수정/삭제시의 무결성 확보 어려움

     * 중복 데이터를 저장하기 위한 추가적인 디스크 저장공간이 필요

     * 시스템 운영자가 중복 송성에 대한 소유권을 혼동할 수 있음

 

    (3) 중복 컬럼 추가

     * 해당 테이블에서 자주 사용하는 컬럼인 경우에 컬럼을 중복시킨다

     * 데이터를 조회하는 경로를 단축하기 위해 컬럼을 중복시킨다

 

    (4) 파생 컬럼 추가

     * 컬럼에 의한 파생 컬럼 추가

     * 로우에 의한 파생 컬럼 추가

 

    (5) 이력 데이터 모델의 컬럼 추가

     * 변경 이력, 발생 이력에 대한 최신 정보 컬럼 추가

     * 진행 이력에 대한 종료 일자 컬럼 추가

 

    (6) PK에 의한 컬럼 추가

     * 복합 PK에 의한 컬럼 추가

     * PK에 의한 컬럼 추가

 

    (7) 시스템 오작동 처리를 위한 컬럼 추가

 

   ● 관계 반정규화 방법

    * 테이블 관계가 5~6단계까지 내려가고 중간의 비식별자 관계로 연결되어 있음.

    * 빈번한 조인이 발생되는 경우 관계 중복을 고려

 

 

3. 무결성 제약 정의

- 입력 참조 무결성

 * 입력 참조 무결성을 유지하기 위한 기능

  ➀ 의존(Dependent) : 테이블에 데이터 입력 시 참조 테이블의 PK가 존재해야만 입력 가능

  ➁ 자동(Automatic) : 테이블에 데이터 입력 시 참조 테이블의 PK가 존재하지 않으면 PK 생성 후 테이블에 데이터 입력

  ➂ 기본(Default) : 테이블에 레코드 입력 시 참조 테이블의 PK를 기본값으로 바꾼 후 자신의 레코드 입력

  ➃ 지정(Customized) : 사용자가 정의한 일정 조건을 만족한 후 자신의 레코드 입력

  ➄ Null : 자신 테이블의 레코드 입력 시 참조 테이블의 PK가 없어도 그대로 입력. FK 컬럼값은 Null이 됨

  ➅ 미지정 : 자신 테이블의 레코드 입력 시 참조 테이블의 PK가 없어도 그대로 입력. FK 컬럼값은 Null이 됨

 

- 수정 참조 무결성

 * 수정 참조 무결성을 유지하기 위한 기능

  ➀ 제한(Restrict) : 자신 테이블의 PK를 수정하면 자신을 참조하는 테이블의 FK가 없어야함.

                              테이블의 FK가 존재하면 자신 테이블 PK 수정 X.

  ➁ 연쇄(Cascade) : 자신 테이블의 PK를 수정하면 참조되는 모든 테이블의 FK를 수정하고 자신의 PK도 수정

 

- 삭제 참조 무결성

 * 삭제 참조 무결성을 유지하기 위한 기능

  ➀ 제한(Restrict) : 자신 테이블의 레코드를 삭제하려면 자신을 참조하는 테이블의 레코드가 없어야함. 있다면 삭제 허용 X

  ➁ 연쇄(Cascade) : 자신 테이블의 레코드를 삭제하려면 참조되는 모든 테이블의 레코드를 삭제하고 자신을 삭제

  ➂ 기본(Default) : 자신 테이블의 레코드를 삭제하려면 참조되는 모든 테이블의 레코드를 기본값으로 바꾼 후 레코드 삭제

                             (비식별자 관계에 한함)

  ➃ 지정(Customized) : 사용자가 정의한 일정 조건을 만족한 후 자신의 레코드 삭제

  ➄ Null : 자신의 테이블의 레코드를 삭제할 때 참조되는 모든 테이블의 레코드를 Null로 바꾼 후 자신의 레코드 삭제

               (비식별자 관계에 한함)

  ➅ 미지정 : 자신의 테이블의 레코드를 삭제할 때 참조되는 모든 테이블의 레코드를 Null로 바꾼 후 자신의 레코드 삭제

                  (비식별자 관계에 한함)

 

- 참조 무결성 적용 시 주의 사항

 * 참조 무결성 원칙을 과다하게 적용하면 성능 저하의 원인이 될 수 있음

 * FK 제약이 걸려 있으면 PK가 수정, 삭제 될 때 마다 관련 테이블들을 무결성 규칙에 따라 점검

 * FK 제약이 걸려 있으면 FK가 수정, 삭제 될 때 마다 관련 테이블의 PK를 참조 무결성 규칙에 따라 점검

 

- FK 제약이 걸려 있는 컬럼들의 인덱스 생성

 * FK 제약이 걸려 있는 컬럼들은 가능하면 인덱스를 걸어줄 것을 권유

 

 

4. 트랜잭션 분석

- 트랜잭션 정의 : 데이터베이스에 행해지는 작업의 논리적인 단위(Logical Unit Of Work)

- 트랜잭션 분석 : 단위프로세스와 크러드 매트릭스(CRUD MATRIX)를 이용하여 트랜잭션 분석서 작성

- 트랜잭션 분석도 이용

 * 용량 산정의 근거 자료로 이용 : 각 테이블에 저장되는 데이터양을 유추

 * 디스크 구성의 이용 : 각 테이블에 얼마만큼의 프로세싱이 발생할 것인지 예측 가능

 * 데이터베이스와 연결되는 채널의 분산 : 대기나 에러현상을 방지

 

 

5. 뷰 설계

 - 뷰의 특징

  가) 복잡한 테이블 구조를 단순화한다.

  나) 다양한 관점에서 데이터를 제시할 수 있다.

  다) 데이터의 보안을 유지한다.

  라) 논리적인 데이터의 독립성을 제공한다

 

 - 그 외 특징

 * 데이터 조회 기능(SELECT)에는 제한이 없다.

 * 데이터를 입력, 수정, 삭제 하는 기능에는 허용하지 않는 뷰가 존재한다.

 * 뷰에는 인덱스, 클러스터링, 해시 클러스터를 지정할 수 없다.

 * 테이블에 뷰를 이용하여 적절한 테이블 접근 방법을 유도할 수 있다.

 

 

6. 인덱스 설계

● 인덱스 대상 선정

  * 대상 테이블 선정 : 테이블이 다른 테이블에 의해 참조되는 관계거나 조인에 의해 처리되어야 하는 경우 인덱스 생성

  * PK 컬럼 인덱스 : PK에는 반드시 인덱스 생성

 

✓ PK와 유니크 인덱스 선택

(1) PK와 유니크 인덱스의 비교

 

(2) 유니크 인덱스만 이용했을 때의 장단점

 - 장점

  * DBA가 데이터베이스를 관리하기 쉽다.

  * 개발 시점에 데이터 제약이 없으므로 개발이 용이하다.

  * PK/FK를 이용하지 않으므로 성능이 다소 좋아질 수 있다.

 

 - 단점

  * 데이터 무결성이 깨질 수 있다.

  * 무결성이 깨짐으로 인해 데이터 전환 작업 시 데이터 정리 작업이 필요하다

  * 데이터 모델과 테이블의 관계가 일치하지 않는다.

  * 유니크 인덱스는 한 테이블에 여러 개 만들 수 있으므로 테이블 만을 보고 PK를 구별할 수 없다.

 

● FK 컬럼 인덱스 : 제약 때문에 Full Table Scan 발생. 데이터 입력/수정/삭제 시 테이블 블록을 유발하므로 반드시 인덱스를 걸어준다

 * 인덱스 대상 컬럼 선정

  ✓ 테이블 내에서 자주 이용되며 분포도가 좋은 컬럼(평균 분포도가 10 ~ 15% 정도의 컬럼)

  ✓ 분포도(%) = { ( 데이터별 평균 로우 수 ) / ( 테이블의 총 로우 수 ) } X 100

 

● 인덱스 최적화

 * 인덱스 효율 검토

  ✓ 조회에 대한 처리는 좋은 효율을 가지나 입력, 수정, 삭제가 자주 일어나는 데이터에 대해서는 성능 저하를 유발, 디스크 용량도 많이 차지함

  ✓ 평균 분포도가 10~15%이내 이더라도 불규칙적이고 기형의 분포를 가지면 인덱스 설정 X

 

 * 인덱스 데이터타입 적용

  ✓ 적절한 인덱스 활용을 위해 데이터 타입을 변경할 수 있다

  ✓ 데이터가 변하는 컬럼은 반드시 가변 길이 타입을 사용한다

 

 * 인덱스 정렬

  ✓ 인덱스 유형에 따라 unique, non, cluster index가 존재하고 정렬 상태에 따라 asc, desc index가 존재

 

* 클러스터링 검토

  ✓ 인덱스를 사용할 수 없는 경우는 클러스터링을 사용하는 것을 검토

 ✓ 클러스터링은 분포도가 넓은 것이 좋다. 따라서 대량의 데이터를 처리하는 트랜잭션은 가능하면 클러스터링을 사용하지 않는 것이 낫다.

 

● 인덱스 정의서 작성

 * 설계 단계에서 각 인덱스에 발생하는 숫자를 계산하여 반영하는 경우

 * 간단히 PK 와 FK 인덱스, 명백한 인덱스에 대해 기록하는 유형

 

7. 데이터베이스 용량 설계

- 데이터베이스 용량 분석의 목적

 * 정확한 데이터 용량을 산정하여 디스크 사용 효율을 높인다.

 * 업무량이 집중된 디스크를 분리하여 설계함으로써 집중화된 디스크에 대한 입출력 부하를 분산.

 * 똑같은 자원에 여러 프로세스가 동시 접근 할 때 발생하는 디스크 입출력 경합을 최소화 하여 데이터 접근 성능 향상

 * 데이터베이스 오브젝트의 익스텐드 발생을 줄인다.

 

- 데이터베이스 용량 분석 절차

 (1) 용량 분석을 위한 기초 데이터를 수집한다.

  * 논리 모델링 작업 또는 물리 설계단계에서 조사. 보통 모델링 작업 중 미리 용량 산정 후 ERD에 반영하여 관계 테이블로 전환시 판단근거로 사용

 (2) 기초 데이터를 이용하여 DBMS에 이용하는 오브젝트별로 용량을 산정한다.

  ➀ 오브젝트 설계

  ➁ 테이블 스페이스 용량 산정

  ➂ 디스크 용량 산정

 

8. 접근 방법 설계

- 스캔 방식

 * 전체 테이블 검색은 Full scan, 특정 부분만 스캔하는 것은 범위 스캔(Range scan)

- B-Tree 인덱스

- 비트맵 인덱스

(대용량데이터베이스솔루션 정리자료 5-6페이지 참고)

 

- 트리와 비트맵 인덱스 비교

 

- 해싱 기법 적용

 * 간단한 해시 알고리즘

  ➀ 나눗셈을 이용한 나머지 방법

    테이블에 들어갈 데이터 항목의 개수를 추정한다.

   항목 개수는 각 데이터값에서 몫과 나머지를 추출하기 위한 제수로 사용한다.

  ➁ 접기

    원래의 데이터값을 쪼개어 여러 부분으로 나눈다.

   각 부분들을 함께 더하고 난 이후 끝에서부터 제자리값을 해시값으로 이용한다.

  ➂ 기수 변환

   값이나 키가 디지털이라면 다른 순서의 숫자열을 만들도록 기수를 변경할 수 있다.

  ➃ 데이터의 자리 재배열

   어떤 데이터에 대해 일정한 범위의 자리를 원래의 순서에서 역으로 배열하여 그 값에 대해 해시값을 도출한다.

 

* 해시 인덱스의 특징

 ✓ 6블록 이상 물리적인 블록의 크기를 갖는 테이블에 적용

 ✓ 정렬 순서에 따른 접근 방식이 아니라 임의대로 접근하는 경우가 많이 발생되는 경우에 적용

 ✓ 자주 변경되지 않는 컬럼값에 대해 해시키를 적용

 ✓ 클러스터키를 사용하는 비슷한 검색 조건으로부터 해시 클러스터 인덱스는 인덱스 클러스터 보다 훨씬 빠른 성능을 제공

 ✓ 하나의 테이블에는 하나의 해시키만 가질 수 있으므로 가장 많이 조회되거나 중요한 컬럼에 대해 해시키를 지정

 ✓ 해시 알고리즘은 값의 범위로 표시될 때는 사용할 수 없음

 ✓ 정렬되어 테이블의 데이터를 조회할 때 해시 알고리즘은 이용되지 않음

 ✓ 여러 개의 컬럼을 하나의 해시키로 구성하였을 때 만약 일부에 대해서만 비교한다면 해시 알고리즘은 이용되지 않음

 

- 클러스터링

* 데이터베이스 뿐만 아니라 서버나 소프트웨어를 구성할 때 비슷한 종류의 무엇인가를 묶어준다는 개념

* 클러스터링의 특징

 ✓ 6 블록 이상의 테이블에 적용

 ✓ 인덱스만을 이용하여 처리하려면 데이터에 분포도가 낮은 경우에 적용

 ✓ 일정한 순서로 조회되는 경우가 많을 때 적용

 ✓ 입력, 수정, 삭제가 자주 발생되지 않을 때 적용

 ✓ 클러스터링을 생성한 기준값은 수정되지 않아야 함

 ✓ 테이블이 분할되어 있지만, 거의 동시에 조인하여 조회하는 때가 많은 경우에 적용

 ✓ Full table Scan시 일반적인 테이블보다 저장 공간을 많이 차지하게 되므로 클러스터링을 적용한 테이블의 검색 속도가 더 느림

 

* 클러스터링을 적용하면 안되는 경우

 ✓ 테이블에 대해 전체 스캔이 종종 발생하는 경우

 ✓ 파티셔닝을 적용하는 경우

 ✓ 동일한 클러스터 키를 가진 클러스터링된 데이터의 크기가 하나의 블록을 초과할 경우

 

9. 데이터베이스 분산 설계

- 테이블 위치 분산

 * 테이블 구조는 변하지 않음

 * 다른 데이터베이스에 중복되어 생성되지 않음

 * 본사와 지사 단위로 분산 시키는 경우

 * 정보 이용 형태가 각 위치별로 차이가 있을 경우

 * 테이블의 위치가 다르므로 테이블 위치를 파악할 수 있는 도식화된 위치별 데이터베이스 문서가 필요

 

- 테이블 분할 분산

 * 수평 분할(Horizontal Fragmentation)

  ✓ 지사별 테이블의 특정 컬럼값을 기준으로 로우를 분리

  ✓ 컬럼은 분리되지 않으며 모든 데이터가 각 지사별로 분리되어 있음

  ✓ 데이터를 한 군데 집합시켜 놓아도 PK에 의해 중복이 발생하지 않음

 

 * 수직 분할(Vertical Fragmentation)

  ✓ 지사에 따라 테이블 컬럼을 기준으로 컬럼 분리

  ✓ 로우단위로는 분리되지 않으며 모든 데이터가 각 지사별로 분리되어 있음

  ✓ 각 테이블은 동일한 PK구조와 값을 가짐

  ✓ 쪼개진 테이블을 조합하면 PK가 동일한 데이터 조합이 가능해야 하며, 하나의 완전한 테이블이 구성됨ㅁ

  ✓ 데이터 중복은 없음

 

- 테이블 복제 분산

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

 * 부분 복제

  ✓ 통합 테이블을 본사에 가지고 있으면서 각 지사별로 해당 로우를 가지고 있는 형태

  ✓ 본사 데이터 = 각 지사 데이터들의 합

  ✓ 본사와 지사간은 데이터의 중복이 항상 발생

 

 * 광역 복제

  ✓ 통합된 테이블을 본사에 가지고 있으면서 각 지사에서도 본사와 동일한 데이터를 모두 가지고 있는 형태

  ✓ 본사와 지사 간 데이터양은 동일하며 특별한 데이터 처리에 제약을 받지 않음

 

- 테이블 요약 분산

 * 분석 요약

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

 

 * 통합 요약

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