2장 - 물리 데이터베이스 설계 | 섹션10. 트랜잭션 분석/CRUD 분석, 섹션11. 인덱스 설계

2024년도 시나공 필기 책 내용 정리

---

섹션10. 트랜잭션 분석/CRUD 분석

 

1.트랜잭션(Transaction) 정의

 

• 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미
• 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위로 사용됨

---

2. 트랜잭션의 상태

 

• 활동(Active): 트랜잭션이 실행 중인 상태
• 실패(Failed): 트랜잭션 실행에 오류가 발생하여 중단된 상태
• 철회(Aborted): 트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태
• 부분완료(Partially Committed): 트랜잭션을 모두 성공적으로 실행한 후 Commit 연산이 실행되기 직전인 상태
• 완료(Committed): 트랜잭션을 모두 성공적으로 실행한 후 Commit 연산을 실행한 후의 상태

---

 

3. 트랜잭션의 특성(데이터의 무결성을 보장하기 위한 특성)

Atomicity (원자성)	트랜잭션의 연산은 데이터베이스에 모두 반영되도록 완료(Commit)되든지 아니면 전혀 반영되지 않도록 복구(Rollback)되어야 함 트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며 모두가 완벽히 수행되지 않고 어느 하나라도 오류가 발생하면 트랜잭션 전부가 취소되어야 함
Consistency (일관성)	트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환함 시스템이 가지고 있는 고정 요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 함
Isolation (독립성, 격리성, 순차성)	둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션의 연산이 끼어들 수 없음 수행중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없음
Durability (영속성,지속성)	성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 함

---

4. CRUD분석

 

• 생성(Create), 읽기(Read), 갱신(Update), 삭제(Delete)의 앞 글자만 모아서 만든 용어이며 CRUD분석은 데이터베이스 테이블에 변화를 주는 트랜잭션의 CRUD 연산에 대해 CRUD 매트릭스를 작성하여 분석하는 것
• 테이블에 발생되는 트랜잭션의 주기별 발생 횟수를 파악하고 연관된 테이블들을 분석하면 테이블에 저장되는 데이터의 양을 유추할 수 있음
• 많은 트랜잭션이 몰리는 테이블을 파악할 수 있으므로 디스크 구성 시 유용한 자료로 활용할 수 있음
• 외부 프로세스 트랜잭션의 부하가 집중되는 데이터베이스 채널을 파악하고 분산시킴으로써 연결 지연이나 타임아웃 오류를 방지할 수 있음

---

5. CRUD 매트릭스

 

• 2차원 형태의 표로서 행(Row)에는 프로세스를, 열(Column)에는 테이블을, 행과 열이 만나는 위치에는 프로세스가 테이블에 발생시키는 변화를 표시하는 업무 프로세스와 데이터 간 상관 분석표
• 프로세스의 트랜잭션이 테이블에 수행하는 작업을 검증
• 각 셀에는 Create, Read, Update, Delete의 앞 글자가 들어가며 복수의 변화를 줄 때는 기본적으로 C > D > U > R의 우선순위를 적용하여 한가지만 적지만, 활용 목적에 따라 모두 기록할 수 있음
• CRUD 메트릭스가 완성되었다면 C,R,U,D 중 어느 것도 적히지 않은 행이나 열, C나 R이 없는 열을 확인하여 불필요하거나 누락된 테이블 또는 프로세스를 찾음

---

6. 트랜잭션 분석

 

• CRUD 매트릭스를 기반으로 테이블에 발생하는 트랜잭션양을 분석하여 테이블에 저장되는 데이터의 양을 유추하고 이를 근거로 DB용량을 산정하고 DB구조를 최적화함
• 업무 개발 담당자가 수행하며 프로세스가 과도하게 접근하는 테이블을 확인하여 여러 디스크에 배치함으로써 디스크 입/출력 분산을 통한 성능 향상을 가져올 수 있음

---

7. 트랜잭션 분석서

 

• 단위 프로세스와 CRUD 매트릭스를 이용하여 작성하며 구성 요소에는 단위 프로세서, CRUD연산, 테이블명, 컬럼명, 테이블 참조 횟수, 트랜잭션 수, 발생 주기 등이 있음
• 단위 프로세스: 업무를 발생시키는 가장 작은 단위의 프로세스
• CRUD연산: 프로세스의 트랜잭션이 데이터베이스 테이블에 영향을 주는 C, R, U, D의 4가지 연산
• 테이블명, 컬럼명: 프로세스가 접근하는 데이터베이스의 테이블명을 기록하며 필요한 경우 테이블의 컬럼명을 적음, 컬럼명을 적을 때는 마침표로 연결하여 테이블,컬럼명과 같이 적음
• 테이블 참조 횟수: 프로세스가 테이블을 참조하는 횟수
• 트랜잭션 수: 주기별로 수행되는 트랜잭션 횟수
• 발생 주기: 연, 분기, 월, 일, 시간 등 트랜잭션 횟수를 측정하기 위한 발생 주기

---

섹션11. 인덱스 설계

 

1. 인덱스(Index)의 개요

 

• 데이터 레코드를 빠르게 접근하기 위해 <키 값, 포인터> 쌍으로 구성되는 데이터 구조
• 데이터가 저장된 물리적 구조와 밀접한 관계가 있음
• 레코드가 저장된 물리적 구조에 접근하는 방법을 제공
• 파일의 레코드에 대한 액세스를 빠르게 수행할 수 있음
• 레코드의 삽입과 삭제가 수시로 일어나는 경우에는 인덱스의 개수를 최소로 하는것이 효율적
• 데이터 정의어(DDL)를 이용하여 사용자가 생성, 변경, 제거할 수 있음
• 인덱스가 없으면 특정한 값을 찾기 위해 모든 데이터 페이지를 확인하는 TABLE SCAN이 발생함
• 기본키를 위한 인덱스를 기본 인덱스라하고 기본 인덱스가 아닌 인덱스들을 보조 인덱스라함, 대부분의 관계형 데이터베이스 관리 시스템에서는 모든 기본키에 대해서 자동적으로 기본 인덱스를 생성함
• 레코드의 물리적 순서가 인덱스의 엔트리 순서와 일치하게 유지되도록 구성되는 인덱스를 클러스터드(Clustered)인덱스라함
• 인덱스를 구성하는 구조나 특징에 따라 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 비트맵 조인 인덱스, 도메인 인덱스 등으로 분류됨

클러스터드 인덱스(Clustered Index)

 

• 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
• 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있음
• 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야 함
• 한 개의 릴레이션에 하나의 인덱스만 생성할 수 있음

넌클러스터드 인덱스(Non-Clustered Index)

 

• 인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식
• 데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로 클러스터드 인덱스에 비해 검색 속도가 떨어짐
• 한 개의 릴레이션에 여러 개의 인덱스를 만들 수 있음

---

2. 트리 기반 인덱스

 

• 인덱스를 저장하는 블록들이 트리 구조로 이루고 있는 것으로 상용DBMS에서는 트리 구조 기반의 B+ 트리 인덱스를 주로 활용함
• B트리 인덱스
  
  
  • 일반적으로 사용되는 인덱스 방식으로 루트 노드에서 하위 노드로 키 값의 크기를 비교해나가면서 단말 노드에서 찾고자 하는 데이터를 검색
  • 키 값과 레코드를 가리키는 포인터들이 트리 노드에 오름차순으로 저장됨
  • 모든 리프 노드는 같은 레벨에 있음
  • 브랜치 블록(Branch Block)과 리프 블록(Leaf Block)으로 구성됨
  • 브랜치 블록: 분기를 위한 목적으로 사용되고 다음 단계를 가리키는 포인터를 가지고 있음
  • 리프 블록: 인덱스를 구성하는 컬럼 데이터와 해당 데이터의 행 위치를 가리키는 레코드 식별자로 구성됨
• B+ 트리 인덱스
  
  • B트리 인덱스의 변형으로  단말 노드가 아닌 노드로 구성된 인덱스 세트(Index Set)와 단말 노드로만 구성된 순차 세트(Sequence Set)로 구분됨
  • 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공되며 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가르킴
  • 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드만을 이용한 순차 처리가 가능함

---

3. 비트맵 인덱스

 

• 인덱스 컬럼의 데이터를 Bit 값인 0 또는 1로 변환하여 인덱스 키로 사용하는 방법
• 키 값을 포함하는 로우(실제 로우의 물리적 위치)의 주소를 제공하는 것이 목표
• 분포도가 좋은 컬럼에 적합하며, 성능 향상 효과를 얻을 수 있음
• 데이터가 Bit로 구성되어 있기 때문에 효율적인 논리 연산이 가능하고 저장 공간이 작음
• 다중 조건을 만족하는 튜플의 개수 계산에 적합하며 동일한 값이 반복되는 경우가 많아 압출 효율이 좋음

---

4. 함수 기반 인덱스

 

• 컬럼의 값 대신 컬럼에 특정 함수(Function)나 수식(Expresstion)을 적용하여 산출된 값을 사용하는 것으로 B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용함
• 함수 기반 인덱스는 데이터를 입력하거나 수정할 때 함수를 적용해야 하므로 부하가 발생할 수 있음
• 사용된 함수가 사용자 정의 함수일 경우 시스템 함수보다 부하가 더 큼
• 대소문자, 띄어쓰기 등에 상관없이 조회할 때 유용하게 사용 됨
• 산술식(Arithmetic Expression), 사용자 정의 함수, PL/SQL Function, SQL Function, Package, C callout 등의 함수를 적용할 수 있음

---

5. 비트맵 조인 인덱스

 

• 다수의 조인된 객체로 구성된 인덱스로 단일 객체로 구성된 일반적인 인덱스와 액세스 방법이 다르지만 비트맵 인덱스와 물리적 구조가 동일함

---

6. 도메인 인덱스

 

• 개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로 확장형(Extensible Index) 인덱스 라고도함
• 프로그램에서 제공하는 인덱스 처럼 사용할 수도 있음

---

7. 인덱스 설계

 

• 분명하게 드러난 컬럼에 대해 기본적인 인덱스를 먼저 지정한 후 개발 단계에서 필요한 인덱스의 설계를 반복적으로 진행
• 인덱스 설계 순서
  
  
  1. 인덱스의 대상 테이블이나 컬럼 등을 선정
  2. 인덱스의 효율성을 검토하여 인덱스 최적화를 수행
  3. 인덱스 정의서를 작성

---

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

 

• MULTI BLOCK READ(테이블 액세스 시 메모리에 한 번에 읽어 들일 수 있는 블록의 수)수에 따라 판단
• 랜덤 액세스가 빈번한 테이블
• 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블
• 다른 테이블과 순차적 조인이 발생되는 테이블

---

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

 

• 인덱스 컬럼의 분포도가 10 ~ 15% 이내인 컬럼 [분포도 = (컬럼값의 평균 Row수 / 테이블의 총 Row수) x 100]
• 분포도가 10 ~ 15% 이상이어도 부분 처리를 목적으로 하는 컬럼
• 입/출력 장표 등에서 조회 및 출력 조건으로 사용되는 컬럼
• 인덱스가 자동 생성되는 기본키와 Unique키 제약 조건을 사용한 컬럼
• 가능한 한 수정이 빈번하지 않은 컬럼
• ORDER BY, GROUP BY, UNION이 빈번한 컬럼
• 분포도가 좁은 컬럼은 단독 인덱스로 생성
• 인덱스들이 자주 조합되어 사용되는 경우 하나의 결합 인덱스(Concatenate Index)로 생성

---

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

 

• 새로 추가되는 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음
• 인덱스를 지나치게 많이 만들면 오버헤드가 발생함
• 넓은 범위를 인덱스로 처리하면 많은 오버헤드가 발생함
• 인덱스를 만들면 추가적인 저장 공간이 필요함
• 인덱스와 테이블 데이터의 저장 공간이 분리되도록 설계