[2020 정보처리기사 필기] 물리 데이터베이스 설계 (1)

85. 사전 조사 분석 ★★

1. 물리 데이터베이스 설계

- 논리적 데이터베이스를 물리적 저장장치에 젖아할 수 있는 물리적 구조의 데이터로 변환하는 과정

- 물리적 데이터베이스 구조의 기본적인 데이터 단위는 저장 레코드이다.

- 물리적 설계 단계에 꼭 포함되어야 할 것은 저장 레코드의 양식 설계, 레코드 집중의 분석 및 설계, 접근 경로 설계

- 여러 가지 타입의 저장 레코드 집합이라는 면에서 단순한 파일과 다름.

- 데이터베이스 시스템의 성능에 중대한 영향

- 물리적 설계 시 고려 사항(인덱스 구조, 레코드 크기, 레코드 개수, 트랜잭션의 갱신과 참조 성향, 스키마 변경 여부 검토, 수행속도, 변화가능성)

- 설계 전 데이터 명명 규칙, 시스템 자원, 데이터 베이스 관리 요소 등을 파악해야함

 

2. 데이터 명명 규칙 파악

- 물리 데이터 모델에 적용해야 하는 규칙으로, 조직마다 다를 수 있으므로 물리 데이터 모델의 설계 전에 파악해야 한다.

- 데이터 표준화 및 논리 데이터베이스 설계의 결과물 등을 통해 파악

- 물리 데이터베이스 설계와 논리 데이터베이스 설계에 적용되는 명명 규칙은 일관성을 유지해야 한다.

- 데이터 명명 규칙을 통해 중복 구축 방지 가능

- 명명 규칙을 파악하려면 도메인과 데이터 사전에 대한 지식이 필요함

- 도메인 : 객체(Entity)에 포함된 속성들의 데이터 타입, 크기 등을 표준화 규칙에 따라 일관성 있게 정의한 것을 의미

- 데이터 사전(데이터 용어 사전) : 데이터 속성의 논리명(Logical Name), 물리명(Physical Name), 용어 정의(Definition)를 기술해 놓은 것이다.

- 데이터 사전은 프로젝트에서 사용하는 명칭 부여의 근거로 사용된다.

 

3. 시스템 자원 파악

- 시스템 자원은 하드위어 자원, 운영체제 및 DBMS 버전, DBMS 파라미터 정보 등으로 구분한다.

- 하드웨어 자원 : 중앙처리장치, 메모리, 디스크, I/O Controller, 네트워크

- 운영체제 및 DBMS 버전 : 운영체제와 DBMS 버전은 데이터베이스 운영에 영향을 미칠 수 있으므로 잘 관리해야 한다.

- DBMS 파라미터(Parameter) 정보 : DBMS 파라미터는 데이터베이스 관리 시스템 별로 차이가 많고 관리 방법도 제 각각이므로 시스템별 DBMS 파라미터의 종류 및 관리 대상 등을 파악, 쿼리에서 활용하는 옵티마이저(Optimizer)의 사용 방법도 파악

 

4. 데이터베이스 관리 요소 파악

- 데이터베이스 구조, 이중화 구성, 분산 데이터베이스, 접근제어/접근통제, DB암호화 등의 범위와 특성을 파악한다.

 

핵심

- 물리적 단계 수행 사항 : 저장 레코드 양식 설계, 레코드 집중의 분석 및 설계, 접근 경로 설계

- 물리적 데이터베이스 설계 수행 시 결정 사항 : 인덱스, 변경 여부, 수행 속도)

- 데이터 명명 규칙은 일관성 있게 명명

---

86. 데이터베이스 저장 공간 설계 ★★

1. 테이블

- 행(Row)와 열(Column)으로 구성되어 있다.

- 데이터베이스의 모든 데이터는 테이블에 저장된다.

- 테이블은 논리 설계 단계의 개체(Entity)에 대응하는 객체

- DBMS 종류에 따라 테이블의 명칭과 기능 등은 약간씩 차이가 있다.

- 테이블의 종류 : 일반 테이블, 클러스터 인덱스 테이블, 파티셔닝 테이블, 외부 테이블, 임시 테이블

 

2. 일반 테이블

- 현재 사용되는 대부분 DBMS에서 표준 테이블로 사용

- 저장되는 데이터의 Row위치는 속성 값에 상관없이 데이터가 저장되는 순서에 따라 결정된다.

 

3. 클러스터드 인덱스 테이블(Clustered Index Table)

- 클러스터드 인덱스 테이블은 기본키(Primary Key)나 인덱스키의 순서에 따라 데이터가 저장되는 테이블

- 일반적인 인덱스를 사용하는 테이블에 비해 접근 경로가 단축됨

 

4. 파티셔닝 테이블(Partitioning Table)

- 대용량 테이블을 작은 논리적 단위인 파티션(Partition)으로 나눈 테이블

- 파티션 키를 잘못 구성하면 성능 저하 등의 역효과를 초래

- 파티셔닝 방식에 따라 범위분할, 해시분할, 조합분할 등으로 나뉨

 

5. 외부 테이블(External Table)

- 외부 테이블은 데이터베이스에서 일반 테이블처럼 이용할 수 있는 외부 파일로, 데이터베이스 내에 객체로 존재한다.

- 외부 테이블은 데이터웨어하우스(Data Warehouse)에서 ETL(Extraction, Transformation, Loading) 등의 작업에 유용하게 사용된다.

 

6. 임시 테이블(Temporary Table)

- 트랜잭션(한꺼번에 수행되어야 하는 일련의 연산들)이나 세션별로 데이터를 저장하고 처리할 수 있는 테이블

- 임시 테이블에 저장된 데이터는 트랜잭션이 종료되면 삭제된다.

- 임시 테이블은 절차적인 처리를 위해 임시로 사용하는 테이블

 

7. 컬럼(Column)

- 컬럼은 테이블의 열을 구성하는 요소로 데이터 타입(Data Type), 길이(Length) 등으로 정의된다.

- 도메인에 따라 데이터의 타입과 길이가 정의됨

- 두 컬럼을 비교하는 연산에서 두 컬럼의 데이터 타입이나 길이가 다르면 DBMS 내부적으로 데이터 타입을 변환한 후 비교연산을 수행한다.

- 참조 관계인 컬럼들은 데이터 타입과 길이가 일치해야 한다.

- 데이터 타입과 길이 지정 시 고려 사항 : 가변 길이 데이터 타입(예상되는 최대 길이로 정의), 고정 길이 데이터 타입(최소 길이로 지정), 소수점 이하 자릿수(반올림 해서 저장)

- 데이터 타입에 따른 컬럼의 물리적인 순서 : 고정 길이 컬럼이고 NOT Null인 컬럼(앞쪽), 가변 길이 컬럼(뒤쪽), Null값이 많을 것으로 예상되는 컬럼(뒤쪽)

 

8. 테이블스페이스(Tablespace)

- 테이블스페이스는 테이블이 저장되는 논리적인 영역으로, 하나의 테이블스페이스에 하나 또는 그 이상의 테이블을 저장할 수 있다.

- 테이블을 저장하면 논리적으로는 테이블스페이스에 저장되고, 물리적으로는 해당 테이블스페이스와 연관된 데이터 파일(Data File)에 저장된다.

- 데이터베이스를 테이블, 테이블스페이스, 데이터 파일로 나눠 관리하면 논리적 구성이 물리적 구성에 종속되지 않아 투명성이 보장된다.

- 데이터베이스에 저장되는 내용에 따라 테이블, 인덱스, 임시(Temporary) 등의 용도로 구분하여 설계한다.

- 테이블과 인덱스는 분리하여 저장한다.

- LOB(Large Object) 타입의 데이터는 독립적인 공간으로 저장한다.

 

핵심

- 테이블은 논리 데이터 모델의 개체에 대응하는 객체

- 컬럼은 데이터 타입과 길이로 정의된다.

- 데이터 타입과 길이가 다른 컬럼의 값은 비교할 수 있다.

- 테이블스페이스는 논리적인 영역, 데이터 파일은 물리적인 영역

---

87. 트랜잭션 분석 / CRUD 분석 ★★

1. 트랜잭션(Transaction) 정의

- 트랜잭션은 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미

- 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적인 단위로 사용

- 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업 단위로 사용

 

2. 트랜잭션의 특성

- Atomicity(원자성) : 완료(Commit)되던지 복구(Rollback)되어야 한다. 하나가 오류 생기면 전부 취소해야 한다.

- Consistency(일관성) : 트랜잭션이 성공하면 언제나 일관성 있는 데이터베이스 상태로 변환한다.

- Isolation(독립성) : 트랜잭션이 병행 실행되는 경우 하나가 실행중이면 다른 하나는 끼어들 수 없다. 참조도 못한다.

- Durability(영속성, 지속성) : 성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나도 영구적으로 반영되어야 한다.

 

3. CRUD 분석

- 생성(Create), 읽기(Read), 갱신(Update), 삭제(Delete)

- CRUD 분석으로 트랜잭션의 주기별 발생 횟수를 파악하고 연관된 테이블들을 분석하면 테이블에 저장되는 데이터 양을 유추할 수 있다.

- 많은 트랜잭션이 몰리는 테이블을 파악할 수 있어 유용한 자료로 사용할 수 있다.

- 트랜잭션의 부하가 집중되는 데이터베이스 채널을 파악하고 분산시킴으로써 연결 지연이나 타임 아웃 오류를 방지할 수 있다.

 

4. CRUD 매트릭스

- 2차원 형태의 표로서, 행(Row)에는 프로세스를, 열(Column)에는 테이블을, 행과 열이 만나는 위치에는 프로세스가 테이블에 발생시키는 변화를 표시하는 업무 프로세스와 데이터 간 상관 분석표이다.

- 복수의 변화를 줄 때는 C - D - U - R 의 우선순위를 적용하여 한가지만 적지만, 활용 목적에 따라 모두 기록할 수 있음.

- CRUD 매트릭스가 완성되었다면 C,R,U,D 중 아무것도 적히지 않은 행이나 열, C나 R이 없는 열을 확인하여 불필요하거나 누락된 테이블 또는 프로세스를 찾는다.

 

5. 트랜잭션 분석

- 테이블에 발생하는 트랜잭션 양을 분석하고 테이블에 저장되는 양을 유추하고 이를 근거로 DB 용량을 산정하고 DB 구조를 최적화하는 것이다.

- 업무 개발 담장자가 수행한다.

- 트랜잭션 분석을 통해 프로세스가 과도하게 접근하는 테이블을 확인하여 여러 디스크에 배치함으로써 디스크 입출력 분산을 통한 성능 향상을 가져올 수 있다.

 

6. 트랜잭션 분석서

- 단위 프로세스와 CRUD 매트릭스를 이용하여 작성, 구성 요소에는 단위 프로세스, CRUD 연산, 테이블명, 컬럼명, 테이블 참조 횟수, 트랜잭션 수, 발생 주기 등이 있다.

- 단위 프로세스 : 업무를 발생시키는 가장 작은 단위의 프로세스

- CRUD 연산 : 프로세스의 트랜잭션이 데이터베이스 테이블에 영향을 주는 CRUD의 4가지 연산

- 테이블명, 컬럼명 : 프로세스가 접근하는 데이터베이스의 테이블명을 기록한다. 필요한 경우 테이블의 컬럼명을 적는다. '테이블.컬럼명'의 형식으로 적는다.

- 테이블 참조 횟수 : 프로세스가 테이블을 참조하는 횟수

- 트랜잭션 수 : 주기별로 수행되는 트랜잭션 횟수

- 발생 주기 : 트랜잭션 횟수를 측정하기 위한 발생 주기

 

핵심

- 원자성

- 우선순위 : C - D - U - R

- 프로세스에 C 또는 R이 없는 경우는 검토사항이 아니다. 테이블에 C 또는 R이 없는 경우는 검토사항이다.

---

88. 인덱스 설계 ★★★

1. 인덱스(Index)의 개념

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

- 인덱스는 데이터가 저장된 물리적 구조와 밀접한 관계가 있다.

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

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

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

- 기본키를 위한 인덱스를 기본 인덱스라 하고, 기본 인덱스가 아닌 인덱스들은 보조 인덱스라고 한다. 대부분 모든 기본 키에 대해 자동적으로 기본 인덱스를 생성한다.

- 레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하게 유지되도록 구성하는 인덱스를 클러스터드(Clustered) 인덱스라고 한다.

- 인덱스는 인덱스를 구성하는 구조나 특징에 따라 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 비트맵 조인 인덱스, 도메인 인덱스 등으로 구분된다.

 

2. 트리 기반 인덱스

- 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것으로 B+ 트리 인덱스를 주로 활용한다.

- B 트리 인덱스 : 일반적으로 사용하는 인덱스 방식으로, 루트 노드에서 하위 노드로 키 값의 크기를 비교해 나가면서 단말 노드에서 찾고자 하는 데이터를 검색한다. 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장된다. 모든 리프 노드는 같은 레벨에 있다.

- B+ 트리 인덱스 : B 트리의 변형으로 단말 노드가 아닌 노드로 구성된 인덱스 세트(Index Set)와 단말 노드로만 구성된 순차 세트(Sequence Set)로 구분된다. 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공되며, 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가리킨다. 단말 노드만을 이용한 순차 처리가 가능하다.

 

3. 비트맵 인덱스

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

- 비트맵 인덱스의 목적은 키 값을 포함하는 로우(Row)의 주소를 제공하는 것

- 비트맵 인덱스는 분포도가 좋은 컬럼에 적합하며, 성능 향상 효과를 얻을 수 있음.

- 데이터가 Bit로 구성되어 있어 효율적인 논리 연산 가능

- 비트맵 인덱스는 다중 조건을 만족하는 튜플의 개수 계산에 적합하다.

- 비트맵 인덱스는 동일한 값이 반복되는 경우가 많아 압축 효율이 좋다.

 

4. 함수 기반 인덱스

- 컬럼의 값 대신 컬럼에 특정함수(Function)나 수식(Expression)을 적용하여 산출된 값을 사용하는 것으로, B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용한다.

- 데이터 입력이나 수정 시 함수를 적용해야 하므로 부하가 발생할 수 있음

- 사용된 함수가 사용자 정의 함수일 경우 시스템 함수보다 부하가 크다.

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

- 적용 가능한 함수의 종류 : 산술식, 사용자 정의 함수, PL/SQL Funciton, SQL Function, Package, C callout 등

 

5. 비트맵 조인 인덱스

- 다수의 조인된 객체로 구성된 인덱스로, 단일 객체로 구성된 일반적인 인덱스와 액세스 방법이 다르다.

- 비트맵 인덱스와 물리적 구조가 동일하다.

 

6. 도메인 인덱스

- 도메인 인덱스는 개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로, 확장형 인덱스(Extensible Index)라고도 한다.

- 개발자가 필요에 의해 만들었지만 프로그램에서 제공하는 인덱스처럼 사용할 수 있다.

 

7. 인덱스 설계

- 인덱스를 설계할 때는 분명하게 드러난 컬럼에 대해 기본적인 인덱스를 먼저 지정한 후 개발 단계에서 필요한 인덱스의 설계를 반복적으로 진행

- 인덱스 설계 순서 : 인덱스의 대상 테이블이나 컬럼 등을 선정 -> 인덱스 최적화 -> 인덱스 정의서를 작성

 

8. 인덱스 대상 테이블 선정 기준

- MULTI BLOCK READ(테이블 액세스 시 메모리에 한 번에 읽어 들일 수 있는 블록의 수) 수에 따라 판단

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

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

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

 

9. 인덱스 대상 컬럼 선정 기준

- 인덱스 컬럼의 분포도가 10~15% 이내인 컬럼

- 수정이 빈번하지 않은 컬럼

- ORDER BY, GROUP BY, UNION이 빈번한 컬럼

- 인덱스들이 자주 조합되어 사용되는 경우 하나의 결합 인덱스로 생성

 

10. 인덱스 설계 시 고려사항

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

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

 

핵심

- 인덱스는 데이터베이스의 물리적구조와 밀접한 관계

- 삽입과 삭제가 수시로 일어나는 경우에는 인덱스의 개수를 최소화

- 함수 기반 인덱스로 B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용

---

84. 뷰(View)의 설계 ★★★

1. 뷰(View)의 개요

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

- 저장장치 내에 물리적으로 존재하지 않지만, 사용자에게 있는 것처럼 간주된다.

- 임시적인 작업을 위한 용도로 활용

- 뷰는 조인문의 사용 최소화로 사용상의 편의성을 최대화

- 생성된 뷰 이름을 질의어에서 사용할 경우 질의어가 실행될 때 뷰에 정의된 기본 테이블로 대체되어 기본 테이블에 대해 실행된다.

 

2. 뷰(View)의 특징

- 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 같다.

- 물리적으로 구현되어 있지 않다.

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

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

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

- 기본 테이블의 기본키를 포함한 속성(열) 집합으로 뷰를 구성해야만 삽입, 삭제, 갱신 연산이 가능하다.

- 일단 정의된 뷰는 다른 뷰의 정의에 기초가 될 수 있다.

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

 

3. 뷰(View)의 장단점

- 장점 : 논리적 데이터 독립성 제공, 동일한 데이터에 대해 여러 사용자의 상이한 응용이나 요구를 지원해줌, 데이터 관리가 간단, 접근 제어를 통한 자동 보안 제공

- 단점 : 독립적인 인덱스를 가질 수 없음, 뷰의 정의 변경 불가능, 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신 연산에 제약이 따름

 

4. 뷰 설계 순서

- 대상 테이블을 선정(외부 시스템과 인터페이스에 관여하는 테이블, 자주 조인되어 접근되는 테이블, SQL 문 작성시 거의 모든 문장에서 인라인 뷰방식으로 접근되는 테이블

- 대상 컬럼을 선정(보안을 유지해야 하는 컬럼은 주의하여 선별)

- 정의서를 작성

 

5. 뷰 설계 시 고려 사항

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

- 사용할 데이터를 다양한 관점에서 제시해야 한다.

- 데이터의 보안 유지를 고려하여 설계한다.

 

핵심

- 뷰 테이블 정의

- 뷰의 정의는 변경이 불가능함

- 나의 뷰를 제거하면 그 뷰를 기초로 정의된 다른 뷰도 자동 삭제

- 뷰는 논리적 데이터 독립성을 제공

- 뷰는 물리적 테이블이 아닌 가상 테이블