📌 [정보처리기사] 물리 데이터베이스 설계 - 트랜잭션 분석 / CRUD 분석 / 인덱스 설계

 

 

목차

 

 

트랜잭션(Transaction) 정의

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

 

• 트랜잭션은 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위로 사용된다.
• 트랜잭션은 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업 단위로 사용된다.

 

 

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

 

 

 

트랜잭션의 특성

📌 다음은 데이터의 무결성(Integrity)을 보장하기 위하여 DBMS의 트랜잭션이 가져야 할 특성이다.

 

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

 

 

 

CRUD 분석

📌 CRUD는 '생성(Create), 읽기(Read), 갱신(Update), 삭제(Delete)'의 앞 글자만 모아서 만든 용어이며, CRUD 분석은 데이터베이스 테이블에 변화를 주는 트랜잭션의 CRUD 연산에 대해 CRUD 매트릭스를 작성하여 분석하는 것이다.

 

• CRUD 분석으로 테이블에 발생되는 트랜잭션의 주기별 발생 횟수를 파악하고 연관된 테이블들을 분석하면 테이블에 저장되는 데이터의 양을 유추할 수 있다.
• CRUD 분석을 통해 많은 트랜잭션이 몰리는 테이블을 파악할 수 있으므로 디스크 구성 시 유용한 자료로 활용할 수 있다.
• CRUD 분석을 통해 외부 프로세스 트랜잭션의 부하가 집중되는 데이터베이스 채널을 파악하고 분산 시킴으로써 연결 지연이나 타임아웃 오류를 방지할 수 있다.

 

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

 

 

 

CRUD 매트릭스

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

 

• CRUD 매트릭스를 통해 프로세스의 트랜잭션이 테이블에 수행하는 작업을 검증한다.
• CRUD 매트릭스의 각 셀에는 Create, Read, Update, Delete의 앞 글자가 들어가며, 복수의 변화를 줄 때는 기본적으로 'C>D>U>R'의 우선순위를 적용하여 한 가지만 적지만, 활용 목적에 따라 모두 기록할 수 있다.

 

🔔 예) '주문 변경' 프로세스를 실행하려면 테이블의 데이터를 읽은(Read) 다음 수정(Update) 해야 하므로 R(Read)과 U(Update)가 필요하지만 CRUD 매트릭스에는 우선순위가 높은 'U'만 표시한다.

 

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

 

🔔 예) 온라인 쇼핑몰의 CRUD 매트릭스 예시

프로세스/테이블	회원	상품	주문	주문목록	제조사
신규 회원 등록	C
회원정보 변경	R, U
주문 요청	R	R	C	C
주문 변경			R	R, U
주문 취소			R, D	R, D
상품 등록		C			C, R
상품정보 변경		R, U			R, U

 

 

 

트랜잭션 분석

📌 트랜잭션 분석의 목적은 CRUD 매트릭스를 기반으로 테이블에 발생하는 트랜잭션 양을 분석하여 테이블에 저장되는 데이터의 양을 유추하고 이를 근거로 DB 용량을 산정하고 DB 구조를 최적화하는 것이다.

 

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

 

💡 데이터 양 유추 : 직원이 10,000 명이고, 직원 한 명당 발령은 2번, 상여금 변경은 20번 발생한다면 발령 테이블에는 10,000 x 2 = 20,000건, 상여금 테이블에는 10,000 x 20 = 200,000 건의 데이터가 발생한다는 것을 유추할 수 있다.
※ 이 계산은 정확한 데이터 양을 계산한 것이 아니라 저장장치나 파일 등의 크기를 지정하기 위해 대략적으로 유추한 것이다.
💡 업무 개발 담당자는 애플리케이션을 분석 및 설계하는 일을 담당한다.

 

 

 

트랜잭션 분석서

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

 

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

 

🔔 예) '주문요청' 프로세스에 대한 트랜잭션 분석서 예시

프로세스	CRUD	테이블명	컬럼명	참조횟수	트랜잭션 수	주기
주문요청	R	회원	회원번호, 회원명, 주소	1	150	일
	R	상품	상품번호, 상품명, 재고량	1	150
	C	주문	주문번호, 일자, 회원번호	3	450
	C	주문목록	주문번호, 상품번호, 수량, 가격	5	750

 

 

---

 

 

🎯 인덱스 설계

 

인덱스(Index)의 개념

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

 

💡 TABLE SCAN은 테이블에 있는 모든 레코드를 순차적으로 읽는 것으로 FULL TABLE SACAN이라고도 한다. 일반적으로 적용 가능한 인덱스가 없거나 분포도가 넓은 데이터를 검색할 때는 FULL TABLE SCAN을 사용한다.

 

 

클러스터드 인덱스  / 넌 클러스터드 인덱스

• 클러스터드 인덱스(Clustered Index)
  • 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식이다.
  • 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있다.
  • 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야 한다.
  • 한 개의 릴레이션에 하나의 인덱스만 생성할 수 있다.
• 넌 클러스터드 인덱스(Non-Clustered Index)
  • 인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식이다.
  • 데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로 클러스터드 인덱스에 비해 검색 속도가 떨어진다.
  • 한 개의 릴레이션에 여러 개의 인덱스를 만들 수 있다.

 

트리 기반 인덱스

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

 

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

구분	B-tree	B+tree
데이터 저장	리프 노드, 브랜치 노드 모두 데이터 저장 가능	오직 리프 노드에만 데이터 저장 가능
트리의 높이	높은	낮음(한 노드 당 key를 많이 담을 수 있음)
풀 스캔 시, 검색 속도	모든 노드 탐색	리프 노드에서 선형 탐색
키 중복	없음	있음(리프 노드에 모든 데이터가 있기 때문)
검색	자주 access되는 노드를 루트 노드 가까이 배치할 수 있고, 루트 노드에서 가까울 경우, 브랜치 노드에도 데이터가 존재하기 때문에 빠름	리프 노드까지 가냐 데이터 존재
링크드 리스트	없음	리프 노드끼리 링크드 리스트로 연결

 

 

 

비트맵 인덱스

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

 

• 비트맵 인덱스의 목적은 키 값을 포함하는 로우(Row)의 주소를 제공하는 것이다.
• 비트맵 인덱스는 분포도가 좋은 컬럼에 접합하며, 성능 향상 효과를 얻을 수 있다.
• 데이터가 Bit로 구성되어 있기 때문에 효율적인 논리 연산이 가능하고 저장 공간이 작다.
• 비트맵 인덱스는 다중 조건을 만족하는 튜플의 개수 계산에 접합하다.
• 비트맵 인덱스는 동일한 값이 반복되는 경우가 많아 압축 효율이 좋다.

 

💡 로우(Row)의 주소 : 비트맵에서 비트의 위치는 테이블에서 로우(Row)의 상대적인 위치를 의미한다. 해당 테이블이 시작되는 물리적인 주소를 기반으로 실제 로우의 물리적 위치를 계산할 수 있다.
💡 분포도, 선택성(Selectivity) : (조건에 맞는 레코드 수 / 전체 릴레이션 레코드 수) x 100, 분포도가 10~15%인 경우 효율적인 인덱스 검색을 할 수 있다. 분포도를 선택성(Selectivity)이란 용어로 사용하기도 한다.

 

 

 

함수 기반 인덱스

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

 

• 함수 기반 인덱스는 데이터를 입력하거나 수정할 때 함수를 적용해야 하므로 부하가 발생할 수 있다.
• 사용된 함수가 사용자 정의 함수일 경우 시스템 함수보다 부하가 더 크다.
• 함수 기반 인덱스는 대소문자, 띄어쓰기 등에 상관없이 조회할 때 유용하게 사용된다.
• 적용 가능한 함수의 종류 : 산술식(Arithmetic Expression), 사용자 정의 함수, PL/SQL Function, SQL Function, Package, C callout 등

 

💡 C allout은 어떤 함수를 호출한다라는 의미이다.

 

 

 

비트맵 조인 인덱스

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

 

• 비트맵 조인 인덱스는 비트맵 인덱스와 물리적 구조가 동일하다.
• 오라클 9i 에서는 이 비트맵 인덱스를 한 단계 더 발전시켜 비트맵 조인 인덱스(Bitmap Join Index)기능을 제공하고 있다.
• 테이블들 사이의 조인 결과에 비트맵 인덱스를 생성하는 것으로 질의 처리를 위한 조인을 피함으로써 성능 향상을 제공한다.
• Bitmap Join Index를 사용하면 실제 join 작업이 발생하지 않도록 할 수 있다.

 

 

도메인 인덱스

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

 

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

 

💡 도메인 인덱스 : 오라클 8i에서부터 새롭게 도입된 개념으로 개발자가 자신이 원하는 인덱스 타입을 생성할 수 있게 함으로써 오라클의 인덱스 시스템에 많은 확장을 가져다 주었다. 즉, 데이터베이스에는 아직 존재하지도 않는 새로운 인덱스 타입을 자신이 스스로 정의하여 오라클에서 지원하는 인덱스 처럼 사용할 수가 있다는 것이다.

 

 

 

인덱스 설계

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

 

• 인덱스 설계 순서
  1. 인덱스의 대상 테이블이나 컬럼 등을 선정한다.
  2. 인덱스의 효율성을 검토하여 인덱스 최적화를 수행한다.
  3. 인덱스 정의서를 작성한다.

🔔 예) 인덱스 정의서 예시

엔티티명	테이블명	인덱스명	컬럼명	타입	테이블 스페이스	엔덱스 유형	정렬
사원	EMP	PK_EMP	EMPNO	NUMBER(5)	ISTEXT01	UNIQUE	ASC
		1_EMP2	DEPTNO	NUMBER(5)	ISTEXT01	NOT UNIQUE	ASC
고객	CUST	PK_CUST	CUSTNO	NUMBER(5)	ISTEXT01	UNIQUE	ASC
부서	DEPT	PK_DEPT	DEPTNO	NUMBER(3)	ISTEXT01	UNIQUE	ASC
주문	ORDER	PK_ORDER	ORDERNO	NUMBER(6)	ISTEXT01	UNIQUE	ASC

 

💡 인덱스 설계 : 프로젝트 진행 중에는 데이터나 프로세스 내용 등에 변경이 많기 때문에 처음부터 모든 인덱스를 한꺼번에 정의하는 것은 불가능 하다. 인덱스 설계 단계에서는 기본적인 인덱스를 지정하고 프로젝트를 진행하면서 SQL 문장 구조 등을 검토하여 반복적으로 인덱스 설계를 진행해야 한다.

 

 

 

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

• MULTI BLOCK READ 수에 따라 판단
  • 예) MULTI BLOCK READ가 16이면, 테이블의 크기가 16블록 이상일 경우 인덱스 필요
• 랜덤 액세스가 빈번한 테이블
• 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블
• 다른 테이블과 순차적 조인이 발생되는 테이블

 

💡 MULTI BLOCK READ란 ? 테이블 액세스 시 메모리에 한 번에 읽어 들일 수 있는 블록의 수를 말한다.

 

 

 

인덱스 대상 컬럼 선정 기준

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

 

💡 ORDER B, GROUP BY, UNION : ORDER BY는 정렬, GROUP BY는 그룹 지정, UNION은 통합 지정 시 사용하는 SQL 명령어이다.
💡 결합 인덱스 : 결합 인덱스란 한 릴레이션 내에 존재하는 여러 컬럼들을 묶어 하나의 인덱스로 만드는 것을 말한다.
결합 인덱스는 컬럼 순서에 따라 액세스하는 범위가 달라질 수 있으므로 유의해야 한다.
💡 컬럼 순서 우선순위
1. 항상 사용되는 컬럼
2. '=' 연산이 되는 컬럼
3. 분포도가 좋은 컬럼
4. 정렬이 자주 발생하는 컬럼

 

 

 

인덱스 설계 시 고려사항

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

 

💡 인덱스와 테이블 분리 : 인덱스와 테이블을 분리하는 형태는 데이터베이스의 가장 일반적인 형태로, 데이터 저장 시 인덱스의 영향을 받지 않아 저장이 빠르다.