데이터베이스[물리적 데이터베이스 모델링]

 

 

아래 글은 데이터베이스 관련 개념 정리 및 작성자 공부를 위해 작성되었습니다.

 

 

📃 참고자료

https://www.youtube.com/watch?v=wEcpra-g8W8&list=PLimVTOIIZt2aP6msQIw0011mfVP-oJGab&index=7 

 

 

 

 

# 데이터베이스 설계 과정

 

1. 요구조건 분석
   - 데이터 베이스에 저장할 내용을 정하기 위해 사용자의 요구사항 분석
   
   
2. 개념적 설계
   - DBMS에 독립적인 개념 스키마 설계
   - ER다이어그램
   
   
3. 논리적 설계
   - DBMS에 맞는 논리적 구조 설계
   - 정규화(스키마 정제)
   - 개념적 설계단계에서 바로 구현을 하면 문제 발생(이상 현상)
    * 삽입이상, 중복이상 등
4. 물리적 설계
   - DBMS에 맞는 물리적 구조 설계
   - 데이터베이스의 성능을 끌어 올리는 단계(극대화)
   
   
5. 구현
   - DDL(SQL 명령)로 실제 데이터베이스 구현
   
   

 

 

 

# 물리적 데이터베이스 모델링

 

• 논리적 설계 단계를 거쳐 논리적 구조로 표현된 데이터를 물리 저장장치에저장할 수 있는
  물리적 구조로 변경해주는 단계
• 실제로 데이터베이스를 생성하기 위한 저장구조와 접근경로의 설계
• 예상빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석
• 사용하고자 하는 DBMS를 결정하고 해당 DBMS의 특성을 고려하여 진행

 

* 저장구조의 설계: 해당 데이터의 타입, 사이즈를 정한다.

* 질의: 쿼리

* 트랜잭션: 데이터베이스에 행해지는 연산(트랜잭션이란?)

 

 

 

 

# 물리적 DB 설계 시 고려사항

 

1. 응답 시간의 최소화
2. 저장 공간의 효율화
3. 트랜잭션 처리도

 

 

 

# 물리적 DB 설계의 기능

 

• 저장 레코드 양식 설계
  - 저장된 데이터의 유형(Type)과 크기(Size)
  
  
• 레코드 집중의 분석 및 설계
  - 검색이 많이 일어지는 데이터는 가급적 모아서 관리(클러스터링)
• 접근 경로 설계
• 최적화 작업

 

 

# 반정규화(역정규화, De-normalization)

 

• 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 기존 설계를 재구성
• 반정규화를 수행하기 전에 각각의 엔터티타입과 속성, 관계에 대해 데이터의 정합성과 데이터의 무결성을
  우선으로 할지 데이터베이스 구성의 단순화와 성능을 우선으로 할지 결정해야 함
• 업무분석을 통해 트랜잭션 처리도를 분석하여 사용량이 많아 시스템 성능에 크게 영향을 주는 테이블만
  선정하여 반정규화 진행

 

 

 

# 반정규화의 과정

 

1. 반정규화 대상 조사
   - 범위처리 빈도수 조사
   - 테이블 조인 개수
   
   
2. 다른 방법 검토
   - 뷰(View)테이블
   - 클러스터링 적용
   - 인덱스의 조정
   
   
3. 반정규화 적용
   - 테이블 반정규화
   - 속성의 반정규화
   - 관계의 반정규화

 

 

 

# 상관 모델링

 

• 업무 프로세스와 DB의 상관관계 모델링
• 정보화 시스템을 구축하기 위하여 그 업무에 존재하는 무엇에 대해 무슨 일이 행해지고 있는지,
  무슨 일에 의해 무엇이 영향 받는지 분석하는 방법
• 업무가 처리되는 과정에 따라 데이터가 어떻게 영향을 받고 있는지 분석하여 설계

 

 

 

# CRUD MATRIX

 

• 단위 프로세스가 엔티티타입에 영향을 주는 방법으로 신규(Create), 조회(Read), 수정(Update), 삭제(Delete)의
  4가지 유형으로 업무가 진행되는 절차에 따른 데이터의 상관관계를 분석

 

*단위 프로세스: 업무 프로세스에서 하나의 일이 이뤄지는 단위
  ex) 회원가입, 회원탈퇴, 주문 등

단위프로세스＼엔티티타입	고객	주문목록	주문	제품
신규고객이 등록	C
제품을 주문	R	C	C	R
주문량 변경		R	U
주문 취소		D	D

 

C = Create, R = Read, U = Update, D = Delete

 

 

단위 프로세스	테이블	컬럼	CRUD	트랜잭션 수
제품주문	고객	고객번호, 고객명	R	200
	주문	주문번호, 주문일자, 고객번호	C	200
	주문목록	주문번호, 제품번호, 단가	C	1000
	제품	제품번호, 제품명, 재고량	R	1000

 

 

 

 

# TABLE 반정규화

 

TABLE 병합

• 정규화 과정에 의해 분리 된 두 테이블에 많은 트랜잭션이 발생하여 JOIN 연산으로 인해 시스템 저하가 일어날 수
  있으므로 이런 경우 두 테이블을 병합

 

 

 

TABLE 분할(파티셔닝, Partitioning)

- 테이블을 분할하면 전체적인 스캔(Scan)범위가 축소되므로 한 테이블에서 접근하는 양이 감소

 

1. TABLE의 수직적 분할(Vertical Partitioning)
   - 테이블에 속한 모든 속성을 사용하지 않고 특정 속성들만 집중적으로 사용하는 경우
   - 특정 속성에만 계속 트랜잭션이 발생하면 수직적 분할
   - 집중적으로 트랜잭션이 발생하는 속성을 따로 구성
2. TABLE의 수평적 분할(Horizontal Partitioning)
   - 릴레이션 스키마는 동일하지만, 그 안에 들어있는 데이터값을 이용하는 방법이 행(ROW)별로 구분지어
     이용되는 경우 테이블을 행(ROW) 단위로 나누는 수평분할

 

 

# 컬럼(Column) 반정규화

 

중복 컬럼 방법

• 자주 조인이 발생하는 컬럼이나 간단한 컬럼만 읽어도 되는데, 접근경로가 너무 복잡할 경우는 컬럼값을 
  더하여 또 하나의 컬럼을 만들어 테이블에 대한 읽기 성능을 향상
• 해당 테이블에서 자주 사용하는 컬럼인 경우에 컬럼을 중복

 

파생 컬럼 추가

• 필요에 의해 특정 속성값으로 만들어지는 파생 컬럼을 추가

공사번호	공사일자	공사비	공사비 누적
2015-001	2015년 1월 1일	7,500,000원	7,500,000원
2015-002	2015년 3월 5일	2,000,000원	9,500,000원
2015-003	2015년 10월 4일	9,721,239원	19,221,239원

 

 

 

# 뷰(View) 설계

 

• 데이터베이스 사용자들이 접근하도록 테이블을 이용하여 만든 가상(Virtual)테이블
• 복잡한 테이블 구조를 단순화
• 다양한 관점에서 데이터를 제시 가능
• 데이터의 보안 유지
• 논리적인 데이터의 독립성 제공

 

* SQL문 예시

CREATE VIEW 주문목록(주문번호, 신청자명, 주문일자, 제품번호, 단가) AS
SELECT A.주문번호, A.신청자명, A.주문일자, B.제품번호, B.단가
FROM 주문 A, 주문목록 B
WHERE A.주문번호 = B.주문번호

 

 

주문 테이블

주문번호	신청자명	주문일자
A101	전지현	2005-9-28
A102	김태희	2005-10-30

 

 

주문 목록 테이블

주문번호	제품번호	단가
A101	B900	25,000원
A101	A100	50,000원
A102	C300	10,000원
A102	D400	30,000원

 

사용자에게 보여지는 뷰

주문번호	신청자명	주문일자	제품번호	단가
A101	전지현	2005-9-28	B900	25,000원
A101	전지현	2005-9-28	A100	50,000원
A102	김태희	2005-10-30	C300	10,000원
A102	김태희	2005-10-30	D400	30,000원

• 실제로 이렇게 저장된 테이블은 없다.
• 가상 테이블 뷰라고 부른다
• 사용자 입장에서 보여지는 데이터베이스의 구조

 

 

 

 

 

# 인덱스(INDEX) 설계

 

인덱스(INDEX)란?

• 데이터베이스에서 원하는 데이터를 좀더 빨리 찾아줄 수 있도록 데이터의 위치정보를 모아놓은 개체
• 항상 정렬되어 있는 상태로 유지
• 시스템의 성능 향상을 가져올 수 있음
• 인덱스에 사용 할 컬럼은 수정이 자주 발생되는 컬럼을 선정
• 데이터의 입력, 수정, 삭제가 많이 발생하면 데이터 영역뿐만 아니라 인덱스에 있는 정보도 DBMS에서 수정해야
  하므로 시간과 자원이 더 소모

 

 

데이터검색

1. FTS(Full Table Scan)
   - 전체 데이터를 쭉 읽어서 찾는 방법
   - 모든 테이블을 읽어서 찾는 방법
   - 많은 데이터를 찾을 때 효율적이다
   
   
2. INDEX SCAN
   - 전체 데이터 중 하나를 찾는 다면 인덱스 스캔이 효율적이다

 

 

클러스터드 인덱스(Index)

• 클러스터드 인덱스가 생성되는 컬럼을 기준으로 데이터 페이지에 저장된 데이터를 물리적으로 정렬
• 기본적으로 넌 클러스터드 인덱스보다 검색 속도가 빠르며 특히 범위 조회(Range Query)를
  할 경우 속도가 빠름
• 한 테이블에 하나의 클러스터드 인덱스만 생성 가능
• 기본키를 만들면 기본적으로 기본키에 클러스터드 인덱스가 만들어짐

 

 

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

• 데이터 페이지의 데이터 그대로의 위치 정보를 인덱스로 구성
• 리프 레벨은 정렬된 상태로 관리되며 하위 데이터 페이지에 대한 포인터 정보를 갖는다.
• 상위 레벨들은 하위 인덱스 페이지들의 첫번째 레코드에 대한 정보를 관리
• 데이터 페이지와는 별도로 인덱스 페이지가 생성
• 클러스터드 인덱스보다 검색 속도가 느리며 범위 조회(Range Query)를 할 경우 거의
  인덱스의 도움을 받을 수 없음

 

 

데이터를 조회할 때

 

• 클러스터드 인덱스: 루트 => 리프
• 넌 클러스터드 인덱스: 루트 => 리프 => 데이터 페이지
  - 클러스터드 인덱스보다는 넌 클러스터드 인덱스가 더 많은 공간을 차지한다.
  - 클러스터드 인덱스의 추가 필요 공간은 테이블 크기의 5 ~ 10% 정도 넌 클러스터드 인덱스의 추가 필요
    공간은 테이블 크기의 10 ~ 20% 정도이다.

 

 

 

 

# 선택성(Selectivity)

 

• Data의 종류의 수와 실제 입력된 데이터 수의 비율, 즉 선택될 수 있는 빈도
• 예를 들어서 1000건의 데이터가 들어 있는 컬럼에서 유일한 값을 반환하는 경우에는 선택성이 0.1%이며,
  이러한 경우에는 선택성이 너무 낮기 때문에, 인덱스를 지정하지 않는 것이 좋다.
• 반대로 선택성이 너무 높은 경웅에도 인덱스를 지정하지 않는 것이 좋다. 
  (만약 성별에 대해서 인덱스를 지정하는 것 역시, 성능향상에는 도움이 되지 않음)
• 주로 선택성이 5% 정도로 이루어지는 컬럼에 인덱스를 지정하는 것이 일반적이다.