[DB/SQL] 데이터 모델과 성능

Joo·2024년 2월 20일

data modeling db sql

RDB & SQL

목록 보기

4/24

강의 링크 : https://www.youtube.com/watch?v=iAf9zQjW1NM&list=PLg_wJlcMiuKtGdlIaAZ0rOPPQuTDENnEQ&index=5

1. 성능 데이터 모델링 개요

정의

데이터베이스 성능을 고려하여 데이터 모델링을 수행하는 것
- 정규화, 반정규화, 테이블 통합 및 분할, join 구조, PK/FK 설정 등

수행 시점

빠를수록 좋음
- 분석/설계 단계에서 성능 모델링 수행 → 재업무 비용(cost) 최소화
일반적인 경우
- 대충 설계 → 성능 저하 → 해당 부분만 SQL 튜닝

성능 데이터 모델링 진행 순서

정규화를 정확하게 수행
- 주요 관심사별로 테이블을 분산시킴
데이터베이스 용량산정 수행
- 각 엔터티에 어느 정도의 액세스나 트랜잭션(해당 데이터에 접근하는 빈도)이 들어오는지 파악
데이터베이스에 발생되는 트랜잭션의 유형 파악
- CRUD 매트릭스 활용
용량과 트랜잭션의 유형에 따라 반정규화 수행
- 테이블, 속성, 관계 변경
이력모델의 조정, 인덱스를 고려한 PK/FK의 순서 조정, 슈퍼타입/서브타입 조정 등 수행
- 성능 관점에서 데이터 모델 최종 검증

DBMS의 발전
60s : 파일 구조
70s : 계층형, 망형 DB
80s : 관계형 DB
90s : 객체 관계형 DB

2. Relational Data Model

관계형 데이터베이스

70s 영국 수학자 E. F. Codd가 제안
기업의 핵심 데이터는 대부분 관계형 DB로 저장되어 있음
관계형 DB는 SQL 문장에 의해 관리됨

테이블

1) Constraints

도메인 제약 : 속성에 대한 제약
키 제약 : 테이블에 대한 제약
개체 무결성 제약 : PK에 대한 제약
참조 무결성 제약 : FK에 대한 제약

3. 정규화와 성능

1) 이상현상

이상현상을 방지하기 위해서는 DB 설계를 잘 해야하고, 잘 하기 위해서는 정규화 를 잘 해야함
과목코드 + 학번 : 복합 키(복합 식별자)
예시 테이블을 깔끔하게 만들려면, 주제에 따라 테이블을 나눠야 함(과목, 수강, 학생) = 정규화
삭제 이상(delete anomaly)
- 어떤 정보를 삭제하면서, 다른 정보도 원하지 않게 삭제되는 경우
삽입 이상(insert anomaly)
- 관련 없는 다른 정보가 존재하지 않아(null), 원하는 정보를 입력하지 못 하는 경우(key attribute가 null)
갱신 이상(update anomaly)
- 하나의 정보를 수정하는데, 여러번의 업데이트를 해야하는 경우

2) 함수종속 및 정규화

정규화(Normalization)

목적 : 삽입/삭제/갱신 이상현상 방지
함수적 종속성(FD, Functional Dependency)에 기반

(FDD의 예시)학번과 과목코드는 평점을 함수적으로 결정하고, 평점은 학번과 과목코드에 함수적으로 종속된다.
- 위의 diagram에서 빨간 선, 검정 선, 초록 선 모두 종속 관계를 나타냄
종류
- 1NF : 모든 값이 원자값을 가짐
  - multivalued attribute나 composite attribute가 없음
- 2NF : 부분함수종속(빨간색 선) 제거
  - 복합키가 공통이 아닌 개별로 함수적으로 결정하는 관계를 없애기
- 3NF : 이행함수종속(초록색 선) 제거
  - PK도 아닌 게 다른 일반 속성을 결정하는 관계를 없애기
  → 식별자가 아닌 속성(주식별자의 일부 또는 일반속성)이 결정자 역할을 하는 함수 종속 제거 → 3NF
- BCNF
- 4NF
  ※ 실무에서는 지나친 정규화를 선호하지는 않아 3NF 정도 까지가 적당하게 봄
  
  자기네들끼리 독립해 새로운 테이블을 만들면 됨. 주동자는 원래 테이블에 남아있어야 함.
  
  2NF와 동일하게 독립해 새로운 테이블을 만들면 됨.
효과
- 데이터 중복 감소 → 성능 향상
- 데이터가 관심사별로 묶임 → 성능 향상
- 조회 질의에서 join이 많이 발생 → 성능 저하
  → 정규화를 통해 일반적으로 성능이 향상되나, 조회의 경우 처리 조건에 따라 성능이 향상되거나 저하됨
정규화를 통한 성능 개선 예

학번을 알면 학생명과 학년을 알 수 있음(부분함수종속 발생)

4. 반정규화와 성능

반정규화의 정의

반정규화(=역정규화=Denormalization)
정규화된 엔터티, 속성, 관계에 대해 성능 향상을 목적으로 중복, 통합, 분리를 수행하는 데이터 모델링 기법
- cf) 비정규화 : 정규화를 아예 수행하지 않음

특징

테이블, 칼럼, 관계의 반정규화를 종합적으로 고려해야 함
- 일반적으로 속성(칼럼)의 중복을 시도함
과도한 반정규화 → 데이터 무결성을 침해하게 됨

반정규화의 사전 절차

반정규화 기법

칼럼 반정규화
- 중복칼럼 추가
- 파생칼럼 추가
- 이력테이블칼럼 추가
- PK의 의미적 분리를 위한 칼럼 추가
- 데이터 복구를 위한 칼럼 추가
테이블 반정규화
- 테이블 병합 - 관계 병합, 슈퍼/서브타입 병합
- 테이블 분할 - 수직 분할, 수평 분할
- 테이블 추가 - 중복 테이블 추가, 통계 테이블 추가, 이력 테이블 추가, 부분 테이블 추가
관계 반정규화
- 중복관계추가
  
  __※ 테이블 분할 절차__
데이터 모델링 수행
데이터베이스 용량 산정
대량 데이터가 처리되는 테이블에 대해 트랜잭션 처리 패턴 분석
트랜잭션이 칼럼 단위로 집중되는 경우 수직 분할, 로우 단위로 집중되는 경우 수평 분할 수행

※ 해시 함수(Hash Function)

임의의 길이의 데이터를 짧은 길이의 데이터로 매핑하는 함수

이력테이블 컬럼 추가 vs 이력테이블 추가

추가되는 최근 값이 (Y,N)이면 이력 테이블 컬럼 추가
추가되는 최근 값이 날짜 등이면 이력테이블 추가

1) 인덱스 특성을 고려한 PK/FK 설정

인덱스(Index)

Select 학번, 이름, 학년 # 테이블에 대해
from 학생 # 세로 필터링
where 학번 > 3000 and 학번 < 5000 # 가로 필터링

검색 속도 향상을 위한 기술 → 실제 테이블을 Full scan하지 않고 인덱스 테이블을 검색
지나치게 많은 인덱스 생성시 시간 및 공간 낭비
인덱스된 필드의 업데이트시 시간 증가
자동 생성(PK 또는 Unique 조건) / 수동 생성(Create Index 구문)

인덱스 스캔 구조 이해

PK의 속성 순서대로 인덱스가 정렬됨
FK 인덱스 설정을 통한 성능 향상

5. 분산 데이터베이스와 성능

분산 데이터베이스의 개념

물리적으로 분산된 데이터베이스를 하나의 논리적 시스템으로 사용

분산 데이터베이스의 장단점

분산 데이터베이스의 적용 기법

테이블 위치 분산
- 테이블을 각각 다른 장소에 위치시킴
테이블 분할(Fragmentation) 분산
- 각 테이블을 분할하여 분산함(수평 분할/수직 분할)
테이블 복제(Replication) 분산
- 동일 테이블의 복사본을 여러 서버에서 동시에 관리함
- 부분 복제(Segment Replication)
  - 본사는 통합 테이블 관리, 각 지사에서는 지사에 해당된 로우만 관리
  - 실제로는 지사에서 먼저 테이블 발생 → 본사에서 전체 통합
- 광역 복제(Broadcast Replication)
  - 동일한 테이블을 여러 곳에 복제하여 관리
  - 본사에서 데이터의 입력, 수정, 삭제 발생 → 지사에서 이를 반영
테이블 요약(Summarization) 분산
- 유사한 내용의 데이터를 서로 다른 관점/수준에서 요약하여 분산 관리
- 분석 요약(Rollup Replication)
  - 각 지사별 '동일한 주제'의 정보를 본사에서 통합하여 전체 요약 정보 산출
  - 예: 상품 A의 지사1, 지사2의 매출 정보 요약 → 전체 매출 정보 요약
- 통합 요약(Consolidation Replication)
  - 각 지사별 '상이한 주제'의 정보를 본사에서 단순 취합하여 제공
  - 예: 지사1의 상품A, 지사2의 상품B에 대한 매출 정보 요약 → 전체 매출 정보 요약