반응형
아래 글은 데이터베이스 관련 개념 정리 및 작성자 공부를 위해 작성되었습니다.
📃 참고자료
https://www.youtube.com/watch?v=wEcpra-g8W8&list=PLimVTOIIZt2aP6msQIw0011mfVP-oJGab&index=7
# 데이터베이스 설계 과정
- 요구조건 분석
- 데이터 베이스에 저장할 내용을 정하기 위해 사용자의 요구사항 분석 - 개념적 설계
- DBMS에 독립적인 개념 스키마 설계
- ER다이어그램 - 논리적 설계
- DBMS에 맞는 논리적 구조 설계
- 정규화(스키마 정제)
- 개념적 설계단계에서 바로 구현을 하면 문제 발생(이상 현상)
* 삽입이상, 중복이상 등 - 물리적 설계
- DBMS에 맞는 물리적 구조 설계
- 데이터베이스의 성능을 끌어 올리는 단계(극대화) - 구현
- DDL(SQL 명령)로 실제 데이터베이스 구현
# 물리적 데이터베이스 모델링
- 논리적 설계 단계를 거쳐 논리적 구조로 표현된 데이터를 물리 저장장치에저장할 수 있는
물리적 구조로 변경해주는 단계 - 실제로 데이터베이스를 생성하기 위한 저장구조와 접근경로의 설계
- 예상빈도를 포함하여 데이터베이스 질의와 트랜잭션들을 분석
- 사용하고자 하는 DBMS를 결정하고 해당 DBMS의 특성을 고려하여 진행
* 저장구조의 설계: 해당 데이터의 타입, 사이즈를 정한다.
* 질의: 쿼리
* 트랜잭션: 데이터베이스에 행해지는 연산(트랜잭션이란?)
# 물리적 DB 설계 시 고려사항
- 응답 시간의 최소화
- 저장 공간의 효율화
- 트랜잭션 처리도
# 물리적 DB 설계의 기능
- 저장 레코드 양식 설계
- 저장된 데이터의 유형(Type)과 크기(Size) - 레코드 집중의 분석 및 설계
- 검색이 많이 일어지는 데이터는 가급적 모아서 관리(클러스터링) - 접근 경로 설계
- 최적화 작업
# 반정규화(역정규화, De-normalization)
- 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 기존 설계를 재구성
- 반정규화를 수행하기 전에 각각의 엔터티타입과 속성, 관계에 대해 데이터의 정합성과 데이터의 무결성을
우선으로 할지 데이터베이스 구성의 단순화와 성능을 우선으로 할지 결정해야 함 - 업무분석을 통해 트랜잭션 처리도를 분석하여 사용량이 많아 시스템 성능에 크게 영향을 주는 테이블만
선정하여 반정규화 진행
# 반정규화의 과정
- 반정규화 대상 조사
- 범위처리 빈도수 조사
- 테이블 조인 개수 - 다른 방법 검토
- 뷰(View)테이블
- 클러스터링 적용
- 인덱스의 조정 - 반정규화 적용
- 테이블 반정규화
- 속성의 반정규화
- 관계의 반정규화
# 상관 모델링
- 업무 프로세스와 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)범위가 축소되므로 한 테이블에서 접근하는 양이 감소
- TABLE의 수직적 분할(Vertical Partitioning)
- 테이블에 속한 모든 속성을 사용하지 않고 특정 속성들만 집중적으로 사용하는 경우
- 특정 속성에만 계속 트랜잭션이 발생하면 수직적 분할
- 집중적으로 트랜잭션이 발생하는 속성을 따로 구성 - 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에서 수정해야
하므로 시간과 자원이 더 소모
데이터검색
- FTS(Full Table Scan)
- 전체 데이터를 쭉 읽어서 찾는 방법
- 모든 테이블을 읽어서 찾는 방법
- 많은 데이터를 찾을 때 효율적이다 - INDEX SCAN
- 전체 데이터 중 하나를 찾는 다면 인덱스 스캔이 효율적이다
클러스터드 인덱스(Index)
- 클러스터드 인덱스가 생성되는 컬럼을 기준으로 데이터 페이지에 저장된 데이터를 물리적으로 정렬
- 기본적으로 넌 클러스터드 인덱스보다 검색 속도가 빠르며 특히 범위 조회(Range Query)를
할 경우 속도가 빠름 - 한 테이블에 하나의 클러스터드 인덱스만 생성 가능
- 기본키를 만들면 기본적으로 기본키에 클러스터드 인덱스가 만들어짐
넌 클러스터드 인덱스(Index)
- 데이터 페이지의 데이터 그대로의 위치 정보를 인덱스로 구성
- 리프 레벨은 정렬된 상태로 관리되며 하위 데이터 페이지에 대한 포인터 정보를 갖는다.
- 상위 레벨들은 하위 인덱스 페이지들의 첫번째 레코드에 대한 정보를 관리
- 데이터 페이지와는 별도로 인덱스 페이지가 생성
- 클러스터드 인덱스보다 검색 속도가 느리며 범위 조회(Range Query)를 할 경우 거의
인덱스의 도움을 받을 수 없음
데이터를 조회할 때
- 클러스터드 인덱스: 루트 => 리프
- 넌 클러스터드 인덱스: 루트 => 리프 => 데이터 페이지
- 클러스터드 인덱스보다는 넌 클러스터드 인덱스가 더 많은 공간을 차지한다.
- 클러스터드 인덱스의 추가 필요 공간은 테이블 크기의 5 ~ 10% 정도 넌 클러스터드 인덱스의 추가 필요
공간은 테이블 크기의 10 ~ 20% 정도이다.
# 선택성(Selectivity)
- Data의 종류의 수와 실제 입력된 데이터 수의 비율, 즉 선택될 수 있는 빈도
- 예를 들어서 1000건의 데이터가 들어 있는 컬럼에서 유일한 값을 반환하는 경우에는 선택성이 0.1%이며,
이러한 경우에는 선택성이 너무 낮기 때문에, 인덱스를 지정하지 않는 것이 좋다. - 반대로 선택성이 너무 높은 경웅에도 인덱스를 지정하지 않는 것이 좋다.
(만약 성별에 대해서 인덱스를 지정하는 것 역시, 성능향상에는 도움이 되지 않음) - 주로 선택성이 5% 정도로 이루어지는 컬럼에 인덱스를 지정하는 것이 일반적이다.
반응형
