물리적 스키마 설계
Updated:
강의
http://www.kocw.net/home/search/kemView.do?kemId=317898
물리적 스키마 설계
- 논리적 스키마로부터 효율적이로 구현 가능한 물리적 DB구조로 설계하는 과정
- DB성능과 매우 밀접한 관련이 있음
- 저장레코드의 양식, 순서, 접근경로, 저장공간의 할당을 표현함
1. 물리적 설계 방법
1.1 물리적 설계의 필요성
- 질의어 최적화
- 검색 최적화
- 데이터베이스 성능 튜닝
1.2 물리적 설계
- 저장 레코드양식 설계
- 데이터 타입, 데이터 값의 분포, 사용될 응용 접근 빈도 등을 고려함
- 레코드 집중의 분석 및 설계 (레코드 == 튜블 == 행)
- 레코드 집중 물리적 순차성 설계(연속된 저장공간 할당, 블록 크기 결정)
- 레코드 크기와 물리적 저장장치의 특성에 의존
- 예) 순차 처리 데이터는 큰 블록, 임의 처리 데이터는 작은 블록으로 설계
1.3 접근 경로 설계
- 구성요소
- 저장구조(인덱스를 통한) 접근 방법과 저장 레코드를 칭함
- 탐색기법 응용을 위한 적절한 접근 경로를 정의함
- 기본 및 보조 접근 경로 설계
- 기본 접근 경로
- 기본 키에 의한 검색으로 초기 레코드 적재, 레코드의 물리적 위치 등과 연관
- 주요 응용의 효율적 처리를 위해 설계됨
- 보조 접근 경로
- 보조 키에 의한 보조인덱스를 활용함
- 인덱스 저장을 위한 추가 저장공간과 인덱스 관리를 위한 오버헤드 문제가 발생할 수 있음
- 기본 접근 경로
2. 인덱스
- 인덱스란 테이블 또는 클러스트와 관련하여 선택적으로 생성 가능한 구조를 말함
- 인덱스는 물리적으로 SQL문장의 조회 성능을 향상하기 위한 것임
- 인덱스는 적절히 활용되었을 때 디스크 I/O를 줄이는 가장 효율적인 방한 중 하나
2.1 특성
- 한 테이블에 대해 다수의 컬럼으로 인덱스 구성 가능
- 한 테이블에 대해 다수의 인덱스 생성가능
- 동일한 컬럼들에 대해 다른 순서로 구성된 인덱스 생성가능
- 인덱스의 유무가 SQL 문장 작성에 영향을 주지 않음
- 인덱스는 관련 테이블과 논리적, 물리적으로 독립적
- 조회 속도는 데이터 건수의 증가에 관계없이 일정하게 유지됨
- 한 테이블에 많은 인덱스가 존재하는 경우 입력, 삭제, 갱신 속도 저하
2.2 종류
- 기본 인덱스
- 탐색키가 데이터 파일의 기본키인 인덱스
- 레코드는 기본키의 값에 따라 클러스터링됨
- 클러스터링 인덱스
- 키가 아닌 필드 값에 따라 정렬된 데이터 파일에 대해 정의됨
- 범위 질의에 유용
- 희소 인덱스일 경우가 많음
- 릴레이션 중간에 튜플 삽입 시 오버플로 발생
- 보조 인덱스
- 탐색키 값에 따라 정렬되지 않은 데이터 파일에 대해 정의됨
- 인덱스상의 탐색키 값들은 정렬되어 있음
- 밀집 인덱스임
- 희소 인덱스
- 일부 키 값에 대해서만 인덱스에 엔트리를 유지하는 인덱스
- 블록당 한 개의 엔트리
- 밀집 인덱스
- 각 레코드 키 값에 대해서 인덱스에 엔트리를 유지하는 인덱스
- 레코드당 한 개의 엔트리
- 다단계 인덱스
- 최상위 단계의 모든 인덱스 엔트리들이 한 블록에 들어갈 때까지 인덱스 단계를 계층화홤
- 최상위 단계 인덱스를 마스터 인덱스라 함
- 새로운 인덱스 엔트리를 추가하거나 기존 엔트리를 삭제하면, 단일 단계 인덱스의 경우보다 오버헤드가 심함
- 리눅스의 경우 3단계 인덱스를 사용
2.3 인덱스의 선정
2.3.1 인덱스 대상 테이블의 선정 기준
- 테이블 크기가 작은 경우 인덱스 만들지 않아도 무방
- join의 연결고리가 되는 컬럼은 인덱스 생성
- 무작위 접근이 빈번한 경우는 인덱스 생성
- 특정 범위의 데이터 스캔이 요구되는 경우는 인덱스 생성
- 특정 순서로 스캔이 요구되는 경우
- 다른 테이블과 중첩루프가 발생되는 테이블은 인덱스 생성
2.3.2 인덱스 컬럼 선정 기준
- 일반적으로 하나의 컬럼만으로 독립된 인덱스를 생성하는 경우는 손익 분기점 이내의 아주 양호한 분포도를 가져야 함
- 결합 인덱스의 첫 번째 컬럼이 조건절에 없다면, 인덱스는 사용되지 않으므로 결합 인덱스의 순서는 ‘=’로 사용 되는 것을 먼저 찾고, 그 중에서 분포도가 좋은 것을 찾을 것
- 일반적으로 인덱스 합병을 사용하는 것보다 분포도가 좋은 하나의 인덱스만 사용하는 것이 더 유리함
- 인덱스 생성 시에는 상대적인 분포도가 아니라 절대적인 행 수를 기준으로 판단하는 것이 현실적임
2.4 인덱스 적용 절차
- 해당 테이블의 접근 형태를 수집
- 대상 컬럼 선정 및 분포도를 조사
- 반복 수행되는 접근 경로의 해결안을 도출
- 집중 인덱스 적용을 검토
- 인덱스 컬럼의 조합 및 순서를 결정
- 시험 생성 및 테스트를 수행
- 수정이 필요한 애플리케이션 조사 및 수정
- 일괄 적용