2020 정보처리기사 필기 - 3.2 물리 데이터베이스 설계(2)

인덱스 설계

인덱스의 개념

- 데이터 레코드를 빠르게 접근하기 위해 키값, 포인터 쌍으로 구성되는 데이터 구조

- 책의 목차와 유사

- 데이터가 저장된 물리적 구조와 밀접한 관계가 있음

- 파일의 레코드에 대한 액세스를 빠르게 수행할 수 있음

- 인덱스가 없으면 특정한 값을 찾기 위해 모든 데이터 페이지를 확인하는 TABLE SCAN이 발생

    -> TABLE SCAN : 데이터가 나올 때까지 모든 레코드를 순차적으로 읽는 것

- 레코드의 삽입과 삭제가 수시로 일어나는 경우에는 인덱스의 개수를 최소로 하는 것이 효율적

- 클러스터드 인덱스 : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식

- 넌클러스터드 인덱스 : 인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식

트리 기반 인덱스

- 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것으로 상용 DBMS에서는 트리 구조 기반의 B+ 트리 인덱스를 주로 활용

- B 트리 인덱스

    -> 일반적으로 사용하는 인덱스 방식

    -> 루트 노드에서 하위 노드로 키값의 크기를 비교하면서 데이터를 검색

    -> 모든 리프 노드의 레벨은 같음

- B+ 트리 인덱스

    -> 단말 노드가 아닌 노드로 구성된 인덱스 세트와 단말 노드로만 구성된 순차 세트로 구분

    -> 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공

    -> 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가리킴

    -> 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드 만을 이용한 순차 처리 가능

 

비트맵 인덱스

- 인덱스 칼럼의 데이터를 Bit 값인 0 또는 1로 변환하여 인덱스 키로 사용하는 방법

- 키 값을 포함하는 로우(Row)의 주소를 제공

- 데이터가 Bit로 구성되어 있어 효율적인 논리 연산이 가능하고 저장공간이 작음

 

함수 기반 인덱스

- 칼럼의 값 대신 칼럼의 특정 함수나 수식을 적용하여 산출된 값을 사용

- B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용

- 데이터를 입력하거나 수정할 때 함수를 적용하기 때문에 부하가 발생할 수 있음

- 사용자 정의 함수를 사용했을 경우 시스템 함수보다 부하가 더 크다

- 대소문자, 띄어쓰기 등에 상관없이 조회할 때 유용하게 사용

 

비트맵 조인 인덱스

- 다수의 조인된 객체로 구성된 인덱스

 

도메인 인덱스

- 개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로 확장형 인덱스라고도 함

 

인덱스 설계 순서

- 인덱스의 대상 테이블이나 칼럼 등을 선정 → 인덱스의 효율성을 검토하여 인덱스 최적화 수행 →  인덱스 정의서 작성

 

인덱스 테이블 선정 기준

- MULTI BLOCK READ 수에 따라 판단

    -> MULTI BLOCK READ : 테이블 액세스 시 메모리에 한 번에 읽어 들일 수 있는 블록의 수

- 랜덤 액세스가 빈번한 테이블

- 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블

- 다른 테이블과 순차적 조인이 발생되는 테이블

 

인덱스 서계 시 고려사항

- 새로 추가되는 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음

- 인덱스를 지나치게 만들면 오버헤드 발생

- 넓은 범위를 인덱스로 처리하면 많은 오버헤드 발생

- 인덱스를 만들면 추가적인 저장공간 필요

- 인덱스와 테이블 데이터의 저장 공간이 분리되도록 설계

 

뷰 설계

뷰의 개요

- 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블

- 물리적으로 존재하지는 않지만 사용자에게는 있는 것처럼 간주됨

- 데이터 보정 작업, 처리 과정 시험 등 임시적인 작업을 위한 용도로 활용

- 조인문의 최소화로 사용자 편의성을 최대화함

뷰의 특징

- 기본 테이블과 같은 형태의 구조를 사용하고 조작도 기본 테이블과 거의 같음

- 가상테이블이기 때문에 물리적으로 구현되어 있지 않음

- 데이터의 논리적 독립성을 제공할 수 있음

- 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해짐

- 뷰를 통해서만 데이터에 접근하게 되면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있음

- 뷰가 정의된 기본 테이블이나 뷰를 삭제 시 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제

 

뷰의 장단점

- 장점

    -> 논리적 데이터 독립성 제공

    -> 동일 데이터에 대해 동시에 여러 사용자의 상이한 요구를 지원

    -> 사용자의 데이터 관리가 용이

    -> 접근 제어를 통한 자동 보안 제공

- 단점

    -> 독립적인 인덱스를 가질 수 없음

    -> 뷰의 정의 변경 불가

    -> 뷰로 구성된 내용에 대해 INSERT, DELETE, UPDATE 연산에 제약이 따름

 

뷰 설계 순서

- 대상 테이블 선정 → 대상 칼럼 선정 → 정의서 작성

 

뷰 설계 시 고려사항

- 테이블 구조가 단순화될 수 있도록 반복적으로 조인을 설정하여 사용하거나 동일한 조건절을 사용하는 테이블을 뷰로 생성

- 동일한 테이블이라도 업무에 따라 테이블을 이용하는 부분이 달라질 수 있으므로 사용할 데이터를 다양한 관점에서 제시

- 데이터의 보안을 유지하며 설계

 

클러스터 설계

클러스터의 개요

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

- 클러스터링키로 지정된 칼럼 값의 순서대로 저장되고 여러 개의 테이블이 하나의 클러스터에 저장

 

클러스터의 특징

- 데이터 조회 속도는 향상시키지만 데이터 입력 수정 삭제에 대한 성능은 저하시킴

- 데이터의 분포도가 넓을수록 유리

- 대용량을 처리하는 트랜잭션은 전체 테이블을 스캔하는 일이 자주 발생하므로 클러스터링을 지양

- 파티셔닝 된 테이블에는 적용할 수 없음

 

클러스터 대상 테이블

- 분포도가 넓은 테이블

- 대량의 범위를 자주 조회하는 테이블

- 입력, 수정, 삭제가 자주 발생하지 않는 테이블

- 자주 조인되어 사용되는 테이블

- ORDER BY, GROUP BY, UNION이 빈번한 테이블

DML - SELECT
ORDER BY : 특정 속성을 기준으로 정렬하여 검색
GROUP BY : 특정 속성을 기준으로 그룹화하여 검색할 때 사용. 그룹 함수와 같이 사용
UNION : 두 SELECT 문의 결과를 통합하고 중복된 행은 한 번만 출력

 

파티션 설계

파티션의 개요

- 대용량의 테이블이나 인덱스를 작은 논리적 단위인 파티션으로 나누는 것

- 대용량 DB의 경우 테이블들을 작은 단위로 나눠 분산시키면 성능 저하를 방지하고 데이터 관리가 용이함

- 데이터 처리는 테이블 단위, 데이터 저장은 파티션 별로 수행

 

파티션의 장단점

- 장점

    -> 데이터 접근 시 액세스 범위를 줄여 쿼리 성능 향상

    -> 데이터가 분산되어 저장되므로 디스크 성능 향상

    -> 파티션별로 백업 및 복구를 수행하므로 속도 향상

    -> 시스템 장애 시 데이터 손상 정도 최소화

    -> 데이터 가용성 향상

    -> 파티션 단위로 입출력 분산

- 단점

    -> 하나의 테이블을 세분화하여 관리하기 때문에 세심한 관리가 요구됨

    -> 테이블 간 조인에 대한 비용이 증가

    -> 용량이 작은 테이블에 파티셔닝을 수행하면 성능이 저하됨

 

파티션의 종류

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

- 해시 분할 : 해시 함수를 적용한 결과 값에 따라 데이터를 분할

- 조합 분할 : 범위 분할로 분할한 다음 해시 함수를 적용하여 다시 분할

 

파티션 키 선정 시 고려사항

- 파티션 키는 테이블 접근 유형에 따라 파티셔닝이 이루어지도록 선정

- 데이터 관리의 용이성을 위해 이력성 데이터는 파티션 생성 주기와 소멸 주기를 일치시켜야 함

- 매일 생성되는 날짜 칼럼, 백업의 기준이 되는 날짜 칼럼, 파티션 간 이동이 없는 칼럼, I/O 병목을 줄일 수 있는 데이터 분포가 양호한 칼럼 등을 파티션 키로 선정

 

인덱스 파티션

- 파티션 된 테이블의 데이터를 관리하기 위해 인덱스를 나눈 것

- 파티션된 테이블의 종속 여부에 따른 구분

    -> Local Partitioned Index : 테이블 파티션과 인덱스 파티션이 1:1 대응되도록 파티셔닝 함

    -> Global Partitioned Index : 테이블 파티션과 인덱스 파티션이 독립적으로 구성되도록 파티셔닝 함

    -> Local Partitioned Index가 Global 보다 관리하기 용이함

- 인덱스 파티션 키 칼럼의 위치에 따른 구분

    -> Prefixed Partitioned Index : 인덱스 파티션 키와 인덱스 첫 번째 칼럼이 같음

    -> Non-Prefixed Partitioned Index : 인덱스 파티션키와 인덱스 첫 번째 칼럼이 다름

필기 정리

2020 정보처리기사 필기 정리