4-15강.물리 데이터베이스 설계-데이터베이스 물리속성 설계

과목4. 데이터베이스 구축, 15강. 물리 데이터베이스 설계-데이터베이스 물리속성 설계

 

[ 목차 ]

1. 파티셔닝

2. 파티션 종류

3. 파티셔닝 범위

4. 파티셔닝 방법 - horizontal partitioning 수평 분할

5. 파티셔닝 방법 - vertical partitioning 수직 분할

6. 클러스터

7. 단일테이블 클러스터링

8. 다중테이블 클러스터링

9. 데이터베이스 백업

10. 구성방식에 따른 백업 분류

11. 백업의 방식에 따른 백업 분류

12. 데이터 백업시 대상 파일

13. 테이블 저장 사이징(db 저장 공간 설계)

14. 데이블 크기 산정방법

15. 데이터 지역화

16. 물리적 데이터베이스 설계

17. 보조 기억 장치의 역할

18. 디스크상에서 파일의 레코드 배치

 

1. 파티셔닝

1) 큰 테이블이나 인덱스를 관리하기 쉬운 단위로 분리하는 방법

2) 용량이 큰 테이블을 파티션이라는 작은 논리적 단위로 나눠 성능이 저하되는 것을 마고 관리르 수월하게 하는 것

3) 장점 

  1] 가용성 : 물리적 파티셔닝으로 인해 전체 데이터의 훼손 가능성이 줄고 데이터 가용성아 향상

  2] 관리용이성 : 큰 테이블들을 제거하고 파티션별로 데이터가 분산저장되어 관리가 쉬움

  3] 성능 : 엑세스 범위가 줄어들어 특정 DML과 쿼리의 성능이 향상시키며 많은 INSERT가 있는 OLTP 시스템에서 INSERT 작업을 분리된 파티션들로 분산시켜 경합을 줄임

OLTP online transaction processing : 대량의 데이터 처리에 좋은 기법(신용카드 조회, 자동 현금 지급 등)

4) 단점

  1] 테이블간의 조인에 대한 비용 증가 -> 용량이 작은 테이블에 파티셔닝을 수행하면 성능 저하

  2] 테이블과 인덱스를 별도로 파티션 할 수 없으며 테이블과 인덱스를 같이 파티셔닝 해야 함

 

2. 파티션 종류

1) 범위분할 range : 지정한 열의 값을 기준으로 분할

2) 해시분할 : 해시 함수에 따라 데이터를 분할하는 것으로 특정 파티션에 데이터가 집중되는 범위분할 단점을 보완

3) 조합분할 composite : 범위분할에 의해 데이터를 분할한 다음 해시 함수를 적용하여 다시 분할

 

3. 파티셔닝 범위

1) range partitioning 범위 분할

  1] 연속적인 숫자나 날짜 기준으로 파티셔닝

  2] 손쉬운 관리 기법 제공에 따른 시간을 단축

2) list partitioning 리스트 분할

  1] 특정 파티션에 저장될 데이터에 대한 명시적 제어 가능(특정 카럼의 특정 값으로 파티셔닝)

  2] 분포도가 비슷하며 많은 sql에서 해당 칼럼의 조건이 많이 들어오는 경우 유용

분포도 : 선택될 확률로 10~15%가 적절

  3] milti-caolumn partition key 제공이 힘듬

  4] 여러 칼럼으로 파티션 키 생성이 가능하지 않으며 오직 하나의 칼럼으로 구성

 

3) composite partitioning 조합 분할

  1] 조합 분할은 파티션의 서브-파티셔닝을 의미

  2] 큰 파티션에 대한 I/O요청을 여러 파티션으로 분산

  3] 범위분할로 할 수 있는 칼럼이 있지만 파티셔닝 결과 생성된 파티션이 너무커서 효과적으로 관리할 수 없을 때 유용

  4] range-list(범위분할 후 리스트분할), range-hash(범위분할 후 해쉬분할)

4) hash partitioning 해시분할

  1] 파티션 키의 해시값에 의한 파티셔닝으로 균등한 데이터 분할이 가능

  2] slect시 조건과 부관하게 병렬 처리를 제공하여 질의 성능 향상

  3] 특정 데이터가 어느 해시 파티션에 있는지 판단 불가

  4] 해시 파티션은 파티션을 위한 범위가 없는 데이터에 적합

  5] 기준이 없는 경우, 조건을 주기 힘든 경우, 파티션이 고르게 나누어지지 않아 밸런스를 유지하기 힘든 경우

 

4. 파티셔닝 방법 - horizontal partitioning 수평 분할

1) 장점

  1] 데이터의 개수를 기준으로 나누어 파티셔닝

  2] 데이터의 개수와 인덱스의 개수가 작아져 성능 향상

2) 단점

  1] 서버간 연결과정이 많아짐

  2] 데이터를 찾는 과정이 기존보다 복잡해 latency(지연)이 증가

  3] 하나의 서버가 고장나게 되면 데이터 무결성이 깨짐

 

5. 파티셔닝 방법 - vertical partitioning 수직 분할

1) 테이블의 칼럼을 기준으로 파티셔닝

2) 정규화하는 과정도 이와 비슷하지만 수직 분할은 이미 정규화된 데이터를 분리하는 과정

3) 자주 사용하는 칼럼등을 분리시켜 성능을 향상

 

6. 클러스터

1) 정해진 칼럼 값을 기준으로 동일한 값을 가진 하나 이상의 테이블의 로우를 같은 장소에 저장하는 물리적 기법

2) 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해 조인이나 자주 사용되는 테이블의 데이털르 디스크의 가튼 위치에 저장시키는 방법

3) 데이터 조회 성능을 향상시키지만 데이터 저장, 수정, 삭제, 한 테이블 전체 조회 성능을 감소

4) 장점

  1] 그룹된 칼럼 데이터 행들이 같은 데이터 블록에 저장되기 떄문에 디스크 I/O를 줄여줌

  2] 클러스터된 테이블 사이에 조인이 발생할 경우 그 처리 시간이 단축

  3] 크러스터키 열을 공유하여 한번만 저장하므로 저장 영역의 사용을 줄임

5) 특징

  1] 데이터 삽입, 수정, 삭제가 빈번한 경우 성능이 저하(클러스터는 검색 속도 향상을 위한 기법)

  2] 클러스터는 데이터의 분포도가 넓을수록 유리

  3] 대용량을 처리하는 트랜잭션은 전체 테이블을 스캔하는 일이 자주 발생 -> 클러스터링 하지 않는 것이 효과적

  4] 파티셔닝된 테이블은 클러스터링 할 수 없음(물리적으로 파티셔닝 된 상태이기 떄문)

  5] 클러스터링된 테이블에 클러스티드 인덱스(물리적으로 데이터를 정렬)를 생성하면 접근 성능이 향상

  6] ORDER BY, GROUP BY, UNION이 빈번한 테이블(묶음 조회)

6) 클러스터 하기 좋은 테이블

  1] 조회가 자주 발생하고 수정이 거의 없는 테이블

  2] 칼럼안의 많은 중복 데이터를 가지는 테이블(분포도가 넓을수록 유리)

  3] 자주 조인되는 테이블

7) 클러스터 키가 되기 좋은 칼럼

  1] 데이터 값의 범위가 큰 칼럼

  2] 테이블 간의 조인이 사용되는 칼럼

8) 클러스터 키가 되기 나쁜 칼럼

  1] 특정 데이터 값이 적은 칼럼

  2] 자주 데이터 수정이 발생하는 칼럼

  3] LONG, LONG RAW 칼럼을 포함 불가

 

7. 단일테이블 클러스터링

1) 하나의 클러스터에 하나의 테이블만 생성

2) 처리 범위가 넓을 경우 효과적(분포도 넓은)

3) 클러스터 인덱스를 경유하여 여러 건의 테이블 로우를 한 번의 스캔을 통하여 액세스ㅏ기에 랜덤 액세스 건수가 크게 감소

 

8. 다중테이블 클러스터링

1) 하나의 단위 클러스터에 여러 개의 테이블을 생성

2) 조인이 많이 발생하는 경우 효과적

 

9. 데이터베이스 백업

1) DB의 고장, 데이터의 손실 등으로 인한 피해를 최소화하기 위해서 현재의 데이터를 저장하는 활동

2) 백업을 받는 대상에 따라서 분류

  1] 시스템 백업 : 운영체제와 같은 시스템 백업

  2] 데이터 백업 : 데이터베이스 백업과 파일 시스템 백업

 

10. 구성방식에 따른 백업 분류

1) 직접연결 백업

  1] 서버와 백업장비를 1:1관계로 직접 연결하여 백업

  2] 서버마다 별도의 백업 소프트웨어를 통해서 활용하는 방식

2) 네트워크 백업

  1] LAN을 통해서 백업장비와 서버를 접속하는 개념

  2] 별도의 백업 관리 서버에 백업 장비를 연결하고 각각의 서버는 백업 서버를 통해서 백업을 수행

3) SAN 백업

  1] 백업받을 데이터 용량이 증가함에 따라 백업용 테이프 장치를 SAN을 통해 인식시켜 FC를 통해 백업 상태로 직접 백업받는 방식

 

11. 백업의 방식에 따른 백업 분류

1) 핫 백업 hot backup/open backup : DB 서버를 온라인 상태로 유지한 채 데이터 백업

2) 콜드 백업 cold/close : DB 서버를 중지한 후 데이터 백업

3) 물리 백업 physical : 파일 자체를 그대로 백업

4) 논리 백업 logical : 각 오브젝트를 SQL문 등으로 저장

 

12. 데이터 백업시 대상 파일

1) data file : 데이터를 저장한 파일

2) control file : 데이터베이스의 구조나 정보에 대한 상태를 저장한 바이너리 파일

3) redo log file : 데이터 변경 처리사항을 저장한 파일(복구 시 사용)

4) redo log file은 데이터베이스 백업시 사용되는 파일

5) 핫 백업이 진행되면 수많은 redo log가 작성되어 디스크 용량을 차지하지만 이 파일을 사용하여 백업 이후 발생한 데이터의 변경 사항을 유연하게 처리할 수 있음

6) 콜드 백업 시에는 redo log가 작성되지 않음(그러나 redo log 파일을 동시에 백업은 가능)

 

13. 테이블 저장 사이징(db 저장 공간 설계)

1) 데이터 예상치, 보관주기 및 각 칼럼사이즈 및 테이블 스토리지 옵션 등에 따라서 테이블의 크기를 예측해야 스토리지 용량산정이 가능

2) 테이블 크기 산정에 필요한 사전 정보로 row의 데이터 길이와 항복별 테이블 크기 정보가 필요

3) row의 테이터 길이

  1] 테이블 크기를 산정하기 위해서는 칼럼의 데이터 타입별 실제 소비 크기와 저장되는 길이를 산정해야 함

  2] 이 정보는 테이블 크기를 산정하기 위한 항목별 크기 산정방법에 이용

  3] 크기산정 테이블은 5개의 칼럼으로 구성되어 있으며 데이터 타입별 크기 산정은 db의 종류에 따라 다를 수 있으므로 가정치를 수치로 조정

4) 항복별 크기 산정

  1] intrans : 블록에 동시에 엑세스 가능한 트랜잭션의 초기수

  2] pctfree : 한 블록 내에 칼럼 변경을 대비한 여유 공간(백분율)

  3] itl : 헤더 내부 트랜잭션 정보를 관리하는 list 

  4] 250바이트 이하 및 초과 칼럼 개수는 row의 데이터 길이 테이블을 참조

  5] 초기 예산 건수, 월중 예상 추가 건수, 데이터 보관 월 수는 운영할 시스템의 사용자 환경의 정보

 

14. 데이블 크기 산정방법

1) 한 로우 저장에 필요한 공간, 한 블록의 데이터 공간, 한 블록에 들어갈 수 있는 로우 개수, 총 데이터 로우 개수를 차례로 구하면서 필요한 총 데이터 공간을 산정

2) 한 로우 저장에 필요한 공간

3) 한 블록의 데이터 공간

4) 한 블록에 들어갈 수 있는 로우의 개수

5) 총 데이터 로우 개수

6) 필요한 총 데이터 공간

 

15. 데이터 지역화

1) 데이터베이스의 저장 데이터를 효율적으로 이용할 수 있도록 저장하는 방법

2) 물리적 데이터베이스의 설계에서 데이터 지역화를 고려한 보조 기억 장치의 설계는 중요

3) 물리적 데이터베이스 설계, 보조 기억 장치의 역할, 디스크상의 파일의 배치를 지역화 관점에서 검토

시간 구역성 : 최근에 참조된 기억 장소가 가까운 장래에도 계속 참조될 가능성이 높음

공간 구역성 : 하나의 기억 장소가 참조되면 그 근처의 기억 장소가 계속 참조될 가능성이 높음

 

16. 물리적 데이터베이스 설계

1) 논리적인 설계의 데이터 구조를 보조 기억 장치상의 파일(물리적 데이터 모델)로 사상

2) 하나의 파일에 여러 릴레이션이 저장 가능

3) 예상 빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석

4) 데이터에 대한 효율적인 접근을 제공하기 위해 저장 구조와 접근 방법을 다룸

5) 질의를 효율적으로 지원하기 위해 인덱스 구조를 적절히 사용

 

17. 보조 기억 장치의 역할

1) 각 파일은 고정된 크리의 블록들로 나우어져서 저장 즉, 여러 개의 블록들에 파일의 데이터가 분산되어 저장

2) 디스크는 데이터베이스를 장기간 보관하는 주된 보조 기억 장치

 

18. 디스크상에서 파일의 레코드 배치

1) 릴레이션의 어트리뷰트는 고정 길이 또는 가변 길이의 필드로 표현

2) 연관된 필드들이 모여서 고정 길이 또는 가변 길이의 레코드가 됨

3) 한 릴레이션을 구성하는 레코드들의 모임은 파일이라고 부르는 블록들의 모임에 저장

4) 한 파일에 속하는 블록들의 위치가 반드시 인접해 있을 필요 없음(저장 가능을 의미)

5) 인접한 블록들을 읽는 경우에는 탐구 시간과 회전 지연 시간이 들지 않기 때문에 입출력 속도가 빠르므로 블록들이 인접하도록 한 파일의 블록들을 재조직

6) BLOB 타입 bonaty lange object

  1] 이미지, 동영상 등 대규모 크기의 데이터를 저장하는데 사용

  2] blob의 최대 크기는 ms sql server에서 2gb까지도 가능

  3] dbms마다 제공하는 크기가 다름

7) 채우기 인수

  1] 각 블록에 레코드를 채우는 공간의 비율

  2] 채우기 인수 = 블록 1개의 크기 / 레코드 1개의 크기

  3] 후에 레코드가 삽입될 때 기존의 레코드들이 이동할 가능성을 줄이기 위해 한 브록에 레코드를 가득 채우지 않고 빈 공간을 남겨둠

8) 고정길이 레코드

  1] i번째 레코드를 접근하기 위해서 레코드길이*(i-1)*레코드 길이

  2] 레코드를 삭제하여 여러 개의 레코드가 이동할 때는 삭제한 레코드 개수*레코드 길이 바이트 만큼 이동

  3] 한 개의 레코드를 삭제하면 고정길이 바이트만큼 이동

9) 파일 내의 클러스터링 intra file clustering

  1] 한 파일 내에서 함께 검색될 가능성이 높은 레코드들을 디스크 상에서 물리적으로 가까운 곳에 모아두는 것

  2] 논리적으로 연관되어 함게 검색될 가능성이 높은 두 개 이상의 파일에 속한 레코드들을 디스크 상에서 물리적으로 가까운 곳에 저장하는 것(두 릴레이션에서 연관된 튜플들이 같은 블록에 저장)

10) 파일 조직의 유형

  1] 히프 파일 heap 

  2] 순차 파일 sequential

  3] 인덱스된 순차 파일 indexed swquential

  4] 직접 파일 hash : dbms가 내부적으로 사용자가 정의한 sql을 빠르게 처리하고자 사용