2-5장. 데이터 입출력 구현-물리 데이터저장소 설계(2)

[ 목차 ]

1. 논리 -> 물리 데이터 변환 순서

2. 테이블 제약조건 설계

3. 인덱스 적용 기준

4. 인덱스 칼럼 선정

5. 인덱스 설계 고려 사항

6. 뷰 설계

7. 뷰 설계 고려사항

8. 클러스터 설계

9. 클러스터 적용 기준

10. 클러스터 설계 시 고려 사항

11. 조인

12. 파티션

13. 파티션의 장점

14. 디스크 구성 설계

 

1. 논리 -> 물리 데이터 변환 순서

1) 단위 엔터티를 테이블로 변환

2) 속성을 칼럼으로 변화

3) UID를 기본키로 변환

  1] 엔터티의 UID(식별자)에 해당하는 모든 속성에 대해 기본키로 선언하고 NOT NULL, UNIQUE 등의 제약조건을 추가

4) 관계를 외래키로 변환

  1] n 관계에서 1영역에 있는 기본키를 n 영역의 외래키로 선언

5) 칼럼의 유형과 길이를 정의

  1] 각 칼럼에 대해 적용 dbms에서 제공하는 데이터 유형 중 적절한 유형을 정의하고 최대 길이를 파악하여 설정

  2] char : 고정길이 문자열, 2000byte까지

  3] varchar2 : 가변길이, 4000byte까지

  4] number : 38자리 숫자

6) 반정규화

  1] 논리모델링 과정에서 수행된 정규화된 모델이 시스템의 성능 향상, 개발 과정의 편의성, 운영의 단순화를 목적으로 수행되는 의도적인 정규화 원칙의 위배 행위

  2] 성능 향상과 관리의 효율성이 증가하는 장점이 있으나 데이터 일관성, 정합성, 무소순성 저하와 유지를 위한 비용 별도 비용이 발생

 

2. 테이블 제약조건 설계

1) 참조 무결성 제약조건 : 외래키는 기본키와 값이 같거나 null 값이어야 함

2) 개체 무결성 제약조건 : 기본키는 중복되어선 안되거 null값이지 않아야 함

3) 삭제 제약조건 : 참조된 기본키의 값이 삭제될 경우의 처리내용을 저의

  1] cascade : 연쇄적으로 제거

  2] restriced : 참조한 테이블에 있는 외부키에 없는 것만 삭제

  3] nullify : 참조한 테이블에 정의된 외부키와 일치하는 것을 null로 수정

4) 수정 제약조건 : 참조된 기본키의 값이 수정될 경우의 처리내용을 정의
  1] 2] 3] 위와 동

 

3. 인덱스 적용 기준

1) 인덱스 적용 대상 칼럼은 전체 로우(튜플) 값대비 분포도가 10~15% 이내인 경우가 적합

2) 분포도(선택성) : (칼럼 평균 로우 수 / 테이블 총 로우 수) X 100

3) 좋은(좁은) 분포도를 가진 인덱스를 고객번호 칼럼에 있는 인데긋와 같이 인덱스 키 당 하나 또는 소수읙 값을 지님

4) 분포도 범위 이상이더라도 부분처리 목적으로 칼럼의 경우 인덱스를 적용

5) 부분 처리 : 전체 데이터 중 조건에 만족하는 범위만 검색한 후 데이터를 추출

6) 조회 및 출력 조건으로 사용하는 경우 인덱스 사용

7) 기본키의 경우 인덱스 자동 생성

8) 유니크키의 제약조건을 사용하는 칼럼의 경우 인덱스 사용

 

4. 인덱스 칼럼 선정

1) 분포도가 좋은 칼럼은 단독으로 생성하여 활용도를 향상

2) 자주 조합되어 사용되는 칼럼은 결합 인덱스로 생성되어 활용

3) 결합 인덱스를 구성하는 칼럼순서 선정에 유의(사용빈도가 높으면 앞으로)

4) 가능한 한 수정이 빈번하지 않은 칼럼을 선정

 

5. 인덱스 설계 고려 사항

1) 새로운 인덱스가 기존 액세스 경로에 영향을 미칠 수 있음을 유의

2) 지나치게 많은 인덱스는 오버헤드로 작용

3) 인덱스는 추가적인 저장공간이 필요함을 고려

4) 넓은 범위 인덱스 처리 시 오히려 전체 처리보다 많은 오버헤드를 발생시킬 수 있음

5) 인덱스와 테이블 데이터의 저장 공간이 적절히 분리되도록 설계

 

6. 뷰 설계

1) 가상의 테이블이기 때문에 점선으로 표현

2) LEPLACE : 뷰가 이미 존재하는 경우 재생성

3) FORCE : 기본 테이블의 존재 여부에 관계없이 뷰 생성

4) NOFORCE : 기본 테이블이 존재할 때만 뷰를 생성

5) WITH CHECK OPTION : 서브쿼리 내의 조건을 만족하는 행만 변경

6) WITH READ ONLY : 갱신이 불가능한 읽기 전용

 

7. 뷰 설계 고려사항

1) 최종적으로 테이블을 액세스하는 것이므로 사용에 따라 수행속도에 문제가 발생할 수 있음

 

8. 클러스터 설계

1) 데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 동일한 테이터 블록에 저장하는 물리적 저장법

2) 저장 장치로부터 데이터를 읽어오는 시간을 줄이기 위해 조인이나 자주 사용되는 테이블의 데이터를 저장 장치 내 같은 위치에 저장시키는 방법

3) 그룹된 칼럼 데이터 행들이 같은 데이터 블록에 저장되기 때문에 디스크 I/O를 감소

4) 조회 속도가 빠르지만 매번 동일한 성격의 데이터를 묶어서 저장하기에 데이터 갱신 시 성능 저하가 발생

5) 자주 조인되는 테이블, 갱신이 자주 발생하지 않는 테이블을 대상으로 설정

 

9. 클러스터 적용 기준

1) 분포도가 넓을수록 오히려 유리한 기법

2) 액세스 기법이 아닌 액세스 효율 향상을 위한 물리적 저장 방법

3) 분포도가 넓은 테이블의 클러스터링은 저장 공안을 절약

4) 다중 블록 이상의 테이블에 적용

5) 대량의 범위를 자주 액세스 하는 경우 사용

6) 인덱스를 사용한 처리가 부담이 되는 넓은 분포도에 활용

7) 여러 개의 테이블이 빈번히 조인을 일으킬 때 활용

 

10. 클러스터 설계 시 고려 사항

1) 검색 효율은 높여주나 입력, 수정, 삭제 시 부하가 증가함

2) UNION, DISTINCE, ORDER BY, GROUP BY가 빈번한 칼럼은 고려

3) 수정이 자주 발생하지 않은 칼럼을 사용

4) 처리 범위가 넓어 문제가 발생할 경우 단일 테이블 클러스링을 적용

5) 조인이 많아 문제가 발생할 경우 다중 테이블 클러스트링을 적용

다중 클러스터 : 조인에 필요한 칼럼을 클러스터 키로 생성해 같은 클러스터 내의 카럼은 미리 조인
단일 클러스터 : 하나의 테이블에서 특정 칼럼을 클러스터 키로 생성해 액세스의 효율을 높임

 

11. 조인

1) 하나의 SQL 명령문으로 여러 테이블에 저장된 데이터를 한번에 검색할 수 있는 기능

2) 잘못 사용하면 질의 처리 성능을 저하

3) 두 개 테이블을 연결하기 위해서는 반드시 두 개 테이블을 연결하는 조인 속성이 있어야 함

4) 조인에서 속성명 중복은 테이블 이름과 열 사이에 .으로 구분

 

12. 파티션

1) 용량이 큰 테이블을 파티션이라는 작은 논리적 단위로 나눠 성능이 저하되는 것을 방지하고 관리를 보다 수월하게 하는 것

2) 범위분할 RANGE : 지정한 열의 값을 기준으로 분할  ex. 일자별, 성적별

3) 해시분할 HASH : 해시 함수에 따라 데이터를 분할(범위분할의 단점을 보완해 데이터를 분산)

4) 조합분한 COMPOSITE : 범위분할에 의해 데이터를 분할한 후 해시함수를 적용해 다시 분할

 

13. 파티션의 장점

1) 데이터 액세스 범위를 줄여 성능 향상

2) 전체 데이터의 훼손 가능성 감소

3) 데이터 가용성(장애 없이 정상 운영) 증가

4) 각 분할 영역을 독립적으로 백업하고 복구 가능

5) disk stripiong으로 I/O 성능 향상(디스크 컨트롤러에 대한 경합 감소)

디스크 스트라이핑
1) 2~32개의 개별 디스크에 하나의 가상적 스트라이프를 작성해 이들 디스크를 컴퓨터의 운영 체계가 단일의 디스크 구동 장치로 인식하도록 하는 것

2) 다중 입출력 동작이 동시에 진행될 수 있게 되어 성능이 크게 향상하며 장애 허용성이 없음

3) 읽기, 쓰기 성능이 향상하지만 안정성(복구)은 낮음

 

14. 디스크 구성 설계

1) 정확한 용량을 산정하여 디스크 사용의 효율을 높임

2) 업무량이 집중되어 있는 디스크를 분리하여 설계해 집중화된 디스크에 대한 입출력 부하를 분산

3) 테이블과 인덱스, 테이블당 트랜잭션 양과 보존기간 드을 고려해서 DB가 저장될 디스크의 용량을 산정

4) 트랜잭션이 과도하게 집중되는 테이블은 각각 다른 테이블 스페이스에 배치해서 디스크 I/O를 분산

5) 입출력 경합을 최소화해 데이터의 접근 성능을 향상

  - 테이블을 위한 스페이스와 인덱스를 위한 스페이스를 분리 구성

  - 테이블 스페이스와 템포퍼리 스페이스를 분리 구성

  - 테이블을 마스터 테이블과 트랜잭션 테입르로 분류

6) 디스크의 구성에 따라 테이블 스페이스의 개수와 사이즈 등을 결정

7) 파티션할 테이블을 별도로 분류