[정처기 필기] 「3」 | 물리 데이터베이스 설계 - (2.1) 트랜잭션, CRUD 분석

[정처기 필기] 「3」 | 물리 데이터베이스 설계 - (2.1) 트랜잭션, CRUD 분석

「1」 소프트웨어 설계

「2」 소프트웨어 개발

> 「3」 데이터베이스 구축

- 논리 데이터베이스 설계, > 물리 데이터베이스 설계, SQL 응용, SQL 활용, 데이터 전환

「4」  프로그래밍 언어 활용

「5」  정보시스템 구축 관리

 

> 1 트랜잭션 분석 / CRUD 분석

> 2 인덱스 설계

3 뷰(View) 설계

4 파티션 설계

5 분산 데이터베이스 설계

6 데이터베이스 보안 / 암호화

7 데이터베이스 보안 - 접근통제

8 데이터베이스 백업

9 스토리지

1. 트랜잭션 분석 / CRUD 분석

트랜잭션 정의

 

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

 

- 병행 제어 / 회복 작업 시 처리되는 작업의 논리적 단위로 사용

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

 

시작 ↓ 활동 ↙     ↘
부분 완료  →	→ 실패
↓              완료	↓             철회

 

활동(Active)

: 트랜잭션이 실행 중인 상태

실패(Failed)

: 트랜잭션 실행에 오류가 발생하여 중단된 상태

철회(Aborted)

: 트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태

부분 완료 (Partially Committed)

: 트랜잭션을 모두 성공적으로 실행한 후 Commit 연산이 실행되기 직전인 상태

완료(Committed)

: 트랜잭션을 모두 성공적으로 실행한 후 Commit 연산을 실행한 후의 상태

 

트랜잭션의 특성

 

데이터의 무결성(Integrity)을 보장하기 위해 DBMS의 트랜잭션이 가져야 할 특성

 

Atomicity(원자성)

: 데이터베이스에 모두 반영되도록 완료되든지 아니면 복구되어야 함

: 모든 명령은 반드시 완벽히 수행, 하나라도 오류 발생 시 전부 취소

Consistency(일관성)

: 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환

: 시스템의 고정 요소는 수행 전과 완료 후 상태가 같아야 함

Isolation(독립성, 격리성, 순차성)

: 병행 실행되는 경우, 하나의 실행 중 다른 연산이 끼어들 수 없음

: 완전히 완료될 때까지 수행 결과를 다른 트랜잭션에서 참조할 수 없음

Durability(영속성, 지속성)

: 성공적으로 완료된 트랜잭션의 결과는 고장나더라도 영구적으로 반영되어야 함

 

CRUD 분석

 

'생성(Create), 읽기(Read), 갱신(Update), 삭제(Delete)'의 앞글자, 테이블에 변화를 주는 트랜잭션 CRUD 연산에 대해 CRUD 매트릭스 작성하여 분석

 

- 테이블에 발생하는 트랜잭션의 주기별 발생 횟수 파악, 연관된 테이블 분석하면 테이블에 저장되는 데이터 양 유추 가능

- 많은 트랜잭션이 몰리는 테이블 파악 가능, 디스크 구성 시 유용한 자료로 활용

- 외부 프로세스 트랜잭션의 부하가 집중되는 데이터베이스 채널 파악, 분산하여 연결 지연 / 타임아웃 오류 방지

 

CRUD 매트릭스

 

2차원 형태의 표, 행에는 프로세서, 열에는 테이블, 행과 열이 만나는 위치는 변화를 표시

 

- 프로세스의 트랜잭션이 테이블에 수행하는 작업을 검증

- 각 셀에는 C > D > U > R의 우선순위를 적용하여 하나만 적지만, 목적에 따라 모두 기록 가능

- 아무것도 적히지 않은(입력된 것이 없으므로 삭제 검토) 행과 열, C가 없거나(입력된 것이 없는데 사용)나 R가 없는(입력되었지만 사용되지 않음) 열을 확인하여 불필요하거나 누락된 테이블, 프로세스 찾음

 

트랜잭션 분석

 

CRUD 매트릭스를 기반으로 트랜잭션 양을 분석, 데이터의 양 대략적으로 유추, DB용량을 산정하고 DB구조를 최적화

 

- 업무 개발 담당자가 수행, 프로세스가 과도하게 접근하는 테이블을 확인하여 여러 디스크에 배치함으로 디스크 입 / 출력 분산으로 성능 향상

 

트랜잭션 분석서

 

단위 프로세스, CRUD 매트릭스를 이용하여 작성

 

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

CRUD 연산 : C, R, U, D의 4가지 연산

테이블명, 칼럼명 : 프로세스가 접근하는 데이터베이스의 테이블명 기록, 필요시 '테이블.칼럼명'

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

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

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

2. 인덱스 설계

인덱스는 레코드에 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성

 

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

- 레코드가 저장된 물리적 구조에 접근하는 방법 제공

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

- 데이터 정의어(DDL) 이용, 사용자가 생성, 변경, 제거 가능, BETWEEN 등 범위 검색에 활용 가능

- 인덱스가 없으면 모든 데이터 페이지를 확인하는 TABLE SCAN 발생

- 인덱스는 하나 이상의 필드로 만들어도 됨

- 독립적인 저장공간 보유

- 기본키를 위한 인덱스를 기본 인덱스, 아닌 인덱스를 보조 인덱스, 모든 기본키에 대해 자동적으로 기본 인덱스 생성

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

- 구조나 특징에 따라 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 비트맵 조인 인덱스, 도메인 인덱스

 

>클러스터드 인데스 / 넌클러스터드 인덱스<

 

클러스터드 인덱스

: 인덱스 키의 순서에 따라 데이터 정렬되어 저장, 실제 데이터가 순서대로 저장

: 인덱스를 검색하지 않아도 원하는 데이터 빠르게 찾을 수 있음

: 데이터 삽입, 삭제 발생 시 순서 유지를 위해 재정렬

: 한 개의 릴레이션에 하나의 인덱스만 생성

 

넌클러스터드 인덱스

: 인덱스의 키 값만 정렬, 실제 데이터는 정렬되지 않음

: 먼저 인덱스를 검색하여 실제 데이터 위치를 확인하므로 검색 속도가 떨어짐

: 한 개의 릴레이션에 여러 개의 인덱스 생성 가능

 

트리 기반 인덱스

 

인덱스를 저장하는 블록이 트리 구조, 상용 DBMS에서는 트리 구조 기반의 B+ 트리 인덱스 활용

 

- B 트리 인덱스

: 루트 노드에서 하위 노드로 키 값의 크기를 비교하며 단말 노드에서 찾고자 하는 데이터 검색

: 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장

: 모든 리프 노드는 같은 레벨에 있음

: 브랜치 블록(Branch Block) - 분기를 위한 목적, 다음 단계 가리키는 포인터 가짐

: 리프 블록(Leaf Block) - 인덱스를 구성하는 칼럼 데이터, 해당 데이터의 행 위치 가르키는 레코드 식별자

- B+ 트리 인덱스

: B 트리의 변형, 노드로 구성된 인덱스 세트(Index Set), 단말 노드로만 구성된 순차 세트(Sequence Set)

: 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 경로로만 제공, 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소 가리킴

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

 

비트맵 인덱스

 

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

 

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

- 분포도가 좋은(전체 레코드 중 조건에 맞는 레코드 숫자가 적은 경우) 칼럼에 적합, 성능 향상 효과

- Bit로 구성되므로 효율적인 논리 연산 가능, 저장 공간이 작음

- 다중 조건을 만족하는 튜플 개수 계산에 적합

- 동일한 값이 반복되는 경우가 많아 압축 효율이 좋음

 

함수 기반 인덱스

 

칼럼에 특정 함수나 수식을 적용하여 산출된 값을 사용, B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용

 

- 데이터를 입력, 수정 시 함수 적용하므로 부하 발생 가능

- 사용자 정의 함수일 경우 시스템 함수보다 부화가 더 큼

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

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

 

비트맵 조인 인덱스

 

다수의 조인된 객체로 구성된 인덱스, 비트맵 인덱스와 물리적 구조가 동일

 

도메인 인덱스

 

개발자가 필요한 인덱스를 직접 만들어 사용, == 확장형 인덱스(Extensible Index)

 

- 프로그램에서 제공하는 인덱스처럼 사용 가능

 

인덱스 설계

 

기본적인 인덱스를 먼저 지정, 개발 단계에서 필요한 인덱스의 설계를 반복적으로 진행

 

1 인덱스의 대상 테이블이나 칼럼 등 산정

2 인덱스의 효율성을 검토하여 인덱스 최적화 수행

3 인덱스 정의서 작성

 

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

 

- MULTI BLOCK READ(한 번에 읽어 들일 수 있는 블록 수)에 따라 판단

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

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

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

 

인덱스 대상 칼럼 선정 기준

 

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

- 10 ~ 15% 이상이어도 부분 처리를 목적으로 하는 칼럼

- 입 / 출력 장표에서 조회 / 출력 조건으로 사용되는 칼럼

- 인덱스가 자동 생성되는 기본키, Unique키 제약 조건을 사용한 칼럼

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

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

- 분포도가 좁은 칼럼은 단독 인덱스로 생성

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

 

인덱스 설계 시 고려사항

 

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

- 지나치게 많이 만들면 오버헤드 발생

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

- 추가적인 저장 공간 필요

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

 

 

 

 

 

 

 

 

출처 | <시나공> 정보처리기사 필기 2024 기본서 (길벗알앤디)