Skip to content

Latest commit

 

History

History
493 lines (428 loc) · 22 KB

4_Database.md

File metadata and controls

493 lines (428 loc) · 22 KB

4_Database

데이터베이스

  • 일정한 규칙 혹인 규약을 통해 구조화되어 저장되는 데이터 모음
  • 실시간 접근과 동시 공유 가능
  • DBMS: 데이터 베이스를 제어, 관리하는 통합 시스템
    • DB <-> DBMS <-> Application
    • 각 DBMS마다 정의된 쿼리 언어로 접근

구성요소

  1. 릴레이션
    • 데이터베이스에서 정보를 구분하여 저장하는 기본 단위
    • 관계형 DB의 테이블, 비관계형 DB의 콜렉션
      • MySQL: 레코드-테이블-DB
      • NoSQL: 도큐먼트-컬렉션-DB
  2. 엔터티 (레코드, 튜플)
    • 사람, 장소, 물건, 사건, 개념 등 여러 개의 속성을 지닌 명사
    • 강한 엔터티/약한 엔터티(엔터티 존재 여부에 따라 종속적)
      • ex. 건물(강한 엔터티)-방(약한 엔터티)
  3. 속성 (필드)
    • 릴레이션에서 관리하는 구체적이며 고유한 이름을 갖는 정보
      • '차'라는 엔터티 > 차 넘버, 바퀴 수, 차 색깔 등
  4. 도메인
    • 릴레이션의 각각의 속성들이 가질 수 있는 값의 집합
      • 성별이라는 속성: {남, 여}

관계

여러 개의 테이블이 있고, 이러한 테이블은 서로의 관계가 정의됨

  1. 1:1 관계
    • 테이블이 하나씩 연결되어 있는 관계
    • 테이블을 두 개의 테이블로 나눠 테이블의 구조를 더 이해하기 쉽게 만듬
    • ex. 유저 테이블 - 유저 이메일 테이블
  2. 1:N 관계
    • 테이블 하나에 여러 개의 테이블이 연결
    • ex. 유저 테이블 - 상품 테이블
  3. N:M 관계
    • 테이블을 직접적으로 연결하여 구축하지는 않음
    • 1:N, 1:M 관계를 맺는 테이블 2개로 나누어 설정
    • ex. 학생 테이블 - 학생_강의 테이블 - 강의 테이블

키

  • 유일성: 중복되는 값이 없음
  • 최소성: 필드를 조합하지 않고, 최소 필드만 써서 키 생성
  1. 기본키
    • 최소성, 유일성 만족
    • 자연키: 자연스레 뽑다가 나오는 키. 언젠가는 변하는 속성
    • 인조키: 인위적으로 생성한 키. 변하지 않음. 유저ID
  2. 후보키
    • 기본키가 될 수 있는 후보. 최소성, 유일성 만족
  3. 대체키
    • 후보키가 두 개 이상일 경우 하나를 기본키도 지정하느 남은 후보키
  4. 슈퍼키
    • 유일성을 갖춘 키
  5. 외래키
    • 다른 테이블의 기본키를 그대로 참조하는 값
    • 중복되어도 괜찮음

데이터 모델링

ERD (Entity Relationship Diagram)

  • 개체와 개체 간의 관계를 시각적으로 표현
    • 가장 기초적으로 DB를 구성할 때 활용
    • 시스템의 요구 사항을 기반으로 작성
  • NoSQL DB를 표현할 수는 없음
  • 정규화 및 성능 향상을 위한 설계 결정에 도움

정규화 과정

  • 중복을 최소화하고 데이터의 일관성을 유지하기 위해 테이블을 분해하는 과정
    • 성능은 좋아질 수도 있고, 나빠질 수도 있음
  • 원칙
    • 같은 의미를 표현하는 릴레이션이지만 좀 더 좋은 구조로 구성
    • 자료의 중복성 감소
    • 독립적인 관계는 별개의 릴레이션으로 표현

제 1정규형

  • 테이블의 속성들이 원자 값으로 구성되어야 함
  • 다중 값 속성을 단일 값 속성으로 분리하고, 중첩된 테이블을 생성하여 반복되는 그룹을 제거
  • 예시
    • 기존
      유저번호 유저ID 수강명 성취도
      1 홍철 {코딩테스트,프런트특강} {90%,10%}
      2 범석 {코드포스특강, DS특강} {7%,8%}
    • 제 1정규형 적용
      유저번호 유저ID 수강명 성취도
      1 홍철 코딩테스트 90%
      1 홍철 프런트특강 10%
      2 범석 코드포스특강 7%
      2 범석 DS특강 8%
  • 이상 현상
    • 삽입 이상: 유저를 찾을 때, 유저번호와 유저 ID를 알아야 함 (불필요)
    • 수정 이상: 1번 학생이 유저ID를 변경할 때, '홍철'인 행을 모두 변경해야 함.
    • 삭제 이상: 홍철 학생이 코딩테스트 수강을 취소하면 해당 과목에 대한 정보가 모두 사라짐, 다시 따로 등록해야하는 번거로움

제 2정규형

  • 제 1정규형 + 부분함수의 종속성 제거 > 완전 함수 종속
    • 함수 종속성: A가 주어지면 B 값이 유일하게 정해진다면 A는 B에 종속 (과목코드, 과목명)
    • 부분 함수 종속성: 비기본 키 속성들이 기본 키 전체에 의존하는 것이 아니라 기본 키의 일부에만 의존해야 함
    • 완전 함수 종속: 기본 키가 모든 비 기본값을 결정
  • 예시

    • 기존
      주문 번호와 제품명에 종속되지 않는 제조사, 제조사 위치가 있음.

      주문 번호 제품명 제조사 제조사 위치 배송여부
      1 컴퓨터 ABC 미국 Y
      1 프린터 XYZ 일본 N
      2 컴퓨터 ABC 미국 Y
      2 마우스 XYZ 일본 Y
      3 컴퓨터 ABC 미국 N

    • 제 2정규형 적용

      주문 번호 제품명 배송여부
      1 컴퓨터 Y
      1 프린터 N
      2 키보드 Y
      2 마우스 Y
      3 컴퓨터 N
      제품명 제조사 제조사 위치
      컴퓨터 ABC 미국
      프린터 XYZ 일본
      마우스 XYZ 일본
  • 이상 현상
    • 삽입 이상: 새로운 제조사(+제조사 위치)를 추가하고 싶은데, 제품명을 NULL로 넣어야 함
    • 수정 이상: 제조사 위치를 변경할 때 같은 제조사의 위치를 모두 변경해야 함
    • 삭제 이상: 유일한 제조사를 가진 행을 삭제할 경우, 제조사의 정보가 모두 사라짐

제 3정규형

  • 제 2정규형 + 기본키가 아닌 모든 속성이 이행적 함수 종속을 만족하지 않는 상태
    • 이행적 함수 종속: A>B, B>C일 때, A>C인 상태
  • 예시

    • 기존
      제품명 > 제조사, 제조사 > 제조사 위치, 제품명 > 제조사 위치

      제품명 제조사 제조사 위치
      컴퓨터 ABC 미국
      프린터 XYZ 일본
      마우스 XYZ 일본

    • 제 3정규형 적용

      제품명 제조사
      컴퓨터 ABC
      프린터 XYZ
      마우스 XYZ
      제조사 제조사 위치
      ABC 미국
      XYZ 일본

보이스/코드 유형

  • 제 3정규형 + 함수 종속 관계에서 모든 결정자가 후보키인 상태
유저ID 수강명 강사
홍철 코딩테스트 큰돌
홍철 MEVM 재엽
범석 코딩테스트 큰돌
범석 MEVN 가영

조건

  1. 각 수강명에 대해 한 학생은 오직 한 강사의 강의만 수강한다
  2. 각 강사는 한 수강명만 담당한다. (강사: 결정자)
  3. 한 수강명은 한 강사 또는 여러 강사가 담당할 수 있다. (강사: 후보키 X)

이상 현상

  • 삽입 이상: 수강생이 없는 수강을 삽입이 불가능
  • 수정 이상: 교수가 수강명을 변경하면 모두 변경
  • 삭제 이상: 강사 정보 삭제가 유저 ID 삭제로 이어질 수 있음
유저ID 강사
홍철 큰돌
홍철 재엽
범석 큰돌
범석 가영
수강명 강사
코딩테스트 큰돌
MEVM 재엽
코딩테스트 큰돌
MEVN 가영

논리적 모델링

  • 개념적 모델링을 중심으로 DB 구조를 정의하는 과정
    • 테이블, 열, 제약 조건 등을 포함하는 데이터베이스 스키마를 정의
  • 과정
    1. 개체를 테이블로 변환
      • 개체>테이블 / 속성>열 로 매핑
    2. 관계를 외래 키로 변환
      • 관계는 테이블 간의 관계로 변환 (1:1, 1:N, N:N)
      • 관계의 매핑을 위해 외래 키(다른 테이블의 기본 키를 참조하는 열)를 사용합니다.
    3. 테이블 간의 관계와 제약 조건 정의
      • 일관성과 무결성을 유지하기 위해 기본 키, 외래 키, 고유 제약 조건을 정의

무결성과 트랜잭션

무결성

  • 데이터의 정확성, 일관성, 유효성을 유지하는 것
  • 현실 세계의 실제 값이 일치하는 지 신뢰에 바탕이 됨
  • 종류
    1. 개체 무결성: 기본 키(primary key)의 고유성과 널(null) 값의 허용 여부를 확인
    2. 참조 무결성: 서로 참조 관계의 두 테이블의 데이터는 항상 일관된 값 유지
    3. 도메인 무결성: 속성에 대해 고유한 값을 가지도록 조건이 주어지면 속성 값은 모두 고유한 값을 가짐

트랜잭션

  • 논리적 기능을 수행하기 위한 작업의 단위
  • ACID 특징을 가짐
    • 원자성, 일관성, 독립성, 지속성

1. 원자성(Atomicity)

  • 모두 수행되었거나 수행되지 않았거나를 보장 (all or nothing)
  • 외부 API 호출 주의 (만약 있다면 롤백 시 어떻게 해야 할 것인지 해결 방법 생각)
  • 예시
    1. 홍철 잔고를 조회한다
    2. 홍철 잔고에서 500만원을 뺀다
    3. 규영에게 500만원을 넣는다

    여기서 2번까지 수행되고, 3번이 수행되지 않으면 문제가 발생

  • 데이터의 무결성 보장
    • 커밋: 여러 쿼리가 성공적으로 처리되었다고 확정하는 명령어. 변경 내용이 영구적으로 저장됨
    • 롤백: 에러나 이슈로 인해 커밋 전 상태로 되돌림

2. 일관성(Consistency)

  • 허용된 방식으로만 데이터를 변경해야하는 것
  • DB의 모든 데이터는 여러 가지 조건, 규칙에 따라 유효함을 가져야 함
  • 예시
    • 홍철이가 통장에 0원이 있을 때, 500만원을 뺄 수 없음 (통장 잔액은 양수의 값을 가져야하기 때문)

3. 격리성(Isolation)

  • 트랜잭션 수행 시 서로 끼어들지 못하는 것
  • 복수의 병렬 트랜잭션이 마치 순차적으로 실행되는 것처럼 동작
격리 수준 Dirty Read Non-Repeatable Read Phantom Read
Read Uncommitted O O O
Read Committed - O O
Repeatable Read - - O
Serializable - - -

격리 수준

  • Serializable
    • 가장 높은 수준의 격리
    • 트랜잭션을 순차적으로 진행
    • 트랜잭션이 동시에 같은 행에 접근할 수 없음
    • 성능이 떨어지고, 교착 상태가 일어날 확률이 높음
  • Repeatable_Read
    • Phantom Read(추가되는 행은 막지 않음) 를 허용
      • 발생하는 현상 예시
        1. 사용자 A가 age가 12 이상인 회원을 조회 > 3개 테이블 조회
        2. 사용자 B가 age가 15인 회원 삽입
        3. 사용자 A가 다시 조회 > 4개 테이블 조회
  • Read_Committed
    • 가장 많이 사용되는 격리 수준
    • 커밋 완료된 데이터에 대해서만 조회를 허용
    • Phantome Read, Non-Repeatable Read(행 값이 변경될 수 있음) 허용
      • 발생하는 현상 예시
        1. 사용자 A가 age가 12 이상인 회원을 조회 > 3개 테이블 조회
        2. 사용자 B가 age가 15인 회원 삽입
        3. 사용자 A가 다시 조회 > 4개 테이블 조회
  • Read_Uncommitted
    • 트랜잭션이 커밋되기 전 다른 트랜잭션에 노출되는 문제가 있음
    • 가장 빠르다
    • 몇몇 행이 제대로 조회되지 않더라도 괜찮은 거대한 양의 데이터를 '어림잡아' 집계하는 데 좋음

4. 지속성(Durability)

  • 성공적으로 수행된 트랜잭션은 영원히 반영
  • 시스템에 장애가 발생해도 원래 상태로 복구하는 회복 기능 필요
    • 체크섬 (중복 검사의 형태. 오류 정정을 통해 송신된 자료의 무결성 보호)
    • 저널링 (변경 사항을 커밋하기 전에 로깅)
    • 롤백

DB 종류

관계형 DB

  • 행과 열을 가지는 표 형식의 데이터를 저장
  • SQL 언어 활용

MySQL

  • 대부분의 운영 체제와 호환
  • 가장 많이 사용하는 DB (커뮤니티가 잘 발달됨)
  • C, C++으로 개발, B-트리 기반의 인덱스, 스레드 기반의 메모리 할당 시스템, 매우 빠른 조인, 최대 64개의 인덱스 제공
  • 롤백, 커밋, 이중 암호 지원 보안 등의 기능 제공

PostgreSQL

  • 데이터 행마다 버전관리가 진행
    • 과거 행을 삭제하고, 변경된 데이터를 추가해야함으로 성능이 좋지 않고, 필요없는 데이터를 장치인 VACUUM 작업이 주기적으로 필요
  • 최대 테이블의 크기는 32TB
  • SQL, JSON을 이용하여 데이터 접근 가능
  • 지정 시간에 복구, 로깅, 접근 제어, 중첩된 트랜잭션, 백업 등의 기능 제공
  • 복잡한 쿼리 요구에 유리

NoSQL DB

  • 관계형 DB를 지양하며, 스키마가 없거나 느슨한 스키마를 제공하는 저장소
  • 복잡한 구조를 쉽게 저장할 수 있음
  • 대량의 분산된 데이터를 저장하고, 조회하는데 특화됨

Key-value 모델

  • 가장 기본적인 형태. 저장과 조회라는 가장 간단한 원칙에 충실한 DB.
  • 값에 모든 데이터 타입을 허용함 → 개발자들이 데이터 입력 단계에서 검증로직을 제대로 구현해야 함
  • Redis. AWS DynamoDB.

Document Model

  • key-value Model의 확장한 구조. 여기서 문서란 JSON이나 XML같은 표준 형식을 가진 문서이다. (Key-Value와 차이점)
  • B 트리 인덱스를 사용하여 2차 인덱스를 생성한다.
    • 쓰기의 성능이 떨어짐
    • 이상적인 상황 = 읽기:쓰기 = 7:3
  • MongoDB.

Column Model

  • Row형으로 저장하는 대신 Column으로 저장하는 방식
  • 하나의 키에 여러 개의 칼럼 이름과 칼럼 값의 쌍으로 이루어진 데이터를 저장하고 조회
    • 저장의 기본 단위: Column
    • Column 수가 많다면 이를 묶어 Column Family로 구성한다.
  • 다수의 클러스터에서 운영된다. 만약 단일서버에서 운영해도 될만큼 데이터가 적다면 적합하지 않다.
  • 읽기보다 쓰기에 특화되어 있다. 빠른 시간 안에 대량의 데이터를 입력하고 조회하는 서비스를 구현할 때 가장 좋은 성능을 보인다.
  • BigTable. Cassandra. HBase.

관계형 DB vs NoSQL DB

구분 관계형 DB NoSQL DB
데이터 저장 SQL을 통해서 테이블에 저장 다양한 형식으로 데이터를 저장
스키마 고정된 형식의 스키마가 필요 동적으로 스키마 형태를 관리
쿼리 테이블 형식과 관계에 맞춰 데이터를 요청(구조화) 데이터 그룹 자체를 조회(비구조화/속도가 느림)
확장성 수직적으로 확장(복잡하고, 시간 많이 소모) 수평적 확장(많은 트래픽 편리하게 관리)
데이터 처리 정렬,탐색,분류가 빠름 쓰기와 읽기 성능이 빠름

관계형 DB를 사용하는 경우

  1. ACID 성질을 준수하는 경우
    • 트랜잭션에 의한 상태의 변화를 수행하는 과정에서 안정성을 보장
    • 예외적인 상황을 줄이고, 무결성을 보호
    • 전자 상거래 등에 자주 쓰임
  2. 데이터가 구조적이고 일관된 경우
    • 규모가 많은 서버를 필요로 하지 않고 일관된 데이터를 사용하는 경우

NoSQL DB를 사용하는 경우

  1. 데이터 구조가 거의 없는 대용량 데이터 저장하는 경우
    • 데이터 유형에 제한이 없음
    • 수평적 확장이 가능하여 효율적인 트래픽 처리 가능
  2. 빠르게 서비스를 구축하는 과정에서 구조를 자주 업데이트하는 경우
    • 시장에 빠르게 프로토타입을 출시해야하는 경우

성능 최적화

  1. 적절한 인덱스 생성:검색 속도 향상
  2. 쿼리 튜닝: 쿼리 실행 계획을 분석하고 인덱스를 최적화하거나 조인 방식을 변경하여 쿼리의 성능을 향상
  3. 데이터베이스 캐싱: 데이터베이스의 자주 액세스되는 데이터를 메모리에 캐시하여 접근 속도를 향상
  4. 파티셔닝: 대용량의 테이블을 파티셔닝하여 작은 조각으로 나누는 것은 쿼리의 성능을 향상
  5. 트랜잭션 관리
  6. 적절한 하드웨어 구성
  7. 정규화와 역정규화

인덱스

  • 추가적인 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키기 위한 자료구조
    1. 만약 DB 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸림
    2. 특정 열의 값과 해당 레코드의 주소를 가진 인덱스를 만들어 데이터 탐색을 빠르게 한다.

특징

  • 장점
    1. 테이블 조회하는 속도 향상
    2. 시스템 부하를 줄임
  • 단점
    1. 추가 저장 공간 필요
    2. 정렬된 상태를 유지하기 위해 추가 작업 필요
    3. 잘못 활용할 경우, 역효과
  • 권장 케이스
    1. 데이터 양이 많은 테이블
    2. 업데이트보다 조회가 잦은 테이블
    3. 조건문이나 정렬이 잦은 테이블

최적화

  1. 인덱스는 비용
    • 인덱스는 두 번 탐색 (인덱스 리스트 > DB 테이블)
    • 테이블 수정 시, 인덱스도 수정되어야함
  2. 항상 테스팅하라
    • 서비스마다 최적의 인덱스는 다르다
  3. 복합 인덱스를 신경쓰자
    • 여러 필드를 기반으로 조회 할 때 복합 인덱스 생성
    • 순서가 있고, 생성 순서에 따라 인덱스 성능이 달라짐
    • 같음(==) > 정렬 > 다중 값 > 카디널리티 (유니크한 값의 정도)

PK와 비교

Category PK Index
목적 데이터 중복을 막기 위함 검색을 속도 개선을 위함 (중복 허용)
데이터 데이터 내에 존재함 별도의 디스크 공간에 존재함 (인덱스 테이블)
검색 모든 데이터를 불러와야함 Index 데이터만 가져와서 원하는 값을 얻을 수 있음

인덱스 종류

B-Tree

  • 자식 노드 2개 이상의 균형 트리 (비균형일 때 균형을 맞출 필요가 있음)
  • Root/Branch/Leaf Node로 나뉨
  • 하향식 검색을 수행
  • 부모 노드 요소 개수보다 자식 노드가 많아지면 부모 노드로 중앙 값을 부모 노드로 올림

삽입과 제거: https://rebro.kr/169

B+ Tree

  1. B-Tree의 확장
  2. 리프 노드만 인덱스-값을 가짐. Linked List로 연결됨
    • 리프 노드에서 선형 탐색
  3. 한 노드에 많은 key를 담을 수 있어 트리 높이가 낮아짐

해시 인덱스

  • 특정 값을 해시 함수에 입력하면 해당 값에 대한 인덱스 위치가 계산되어 빠른 검색이 가능
  • <, LIKE, BETWEEN 등의 범위 조건문은 사용할 수 없음

쿼리 튜닝

  • 실행 속도가 느린 쿼리를 최적화하여 성능을 향상시키는 과정
  • 단계
    1. 쿼리 실행 계획 분석: 데이터베이스 시스템은 쿼리를 실행하기 위해 실행 계획을 생성하는데, 이 계획은 쿼리의 최적 실행 경로를 결정
    2. 인덱스 분석: 쿼리가 자주 사용하는 조건에 해당하는 인덱스가 존재하는지 확인
    3. 조인 최적화: 조인 조건을 최적화하여 조인 연산의 효율성을 높이고 중복되는 작업을 피할 수 있음. 조인 방식을 변경하거나 필요에 따라 조인 힌트를 사용할 수 있음.
    4. 서브쿼리 최적화: 서브쿼리를 적절히 최적화하고 필요에 따라 인라인 뷰 등으로 변경하여 실행 속도를 향상
    5. 데이터베이스 통계 최적화: 테이블의 행 수, 칼럼의 고유 값 수 등의 통계 정보를 업데이트하여 실행 계획을 더 정확하게 생성

조인의 원리

  1. 중첩 루프 조인
    • 중첩 for문 + 조건에 맞는 조인
    • 랜덤 접근에 의한 비용이 많은 증가 (대용량에서 부적절)
    for each row in t1 matching reference key{
    for each row in t2 matching reference key{
        if row satisfies join conditions, send to client
    }
}
  2. 정렬 병합 조인
    • 조인할 필드 기준으로 정렬하고 정렬이 끝난 이후에 조인 작업
    • 적절한 인덱스가 없고, 조인 조건으로 범위 비교 연산자(<,>)가 있을 때 사용
  3. 해시 조인
    • 해시 테이블 사용
    • 동등(==) 조건에서만 사용
    • 테이블을 한번씩만 읽어 중첩 루프 조인보다 보통 성능이 좋음
    • 과정
      1. 빌드 단계
        테이블 하나를 메모리에 온전히 삽입 (비교적 작은 테이블)
      2. 프로브 단계
        레코드 읽기를 통해 기준과 일치하는 레코드를 찾아 결괏값 반환

DB 튜닝

  • 리팩토링을 통해 시간을 줄이고, SQL 튜닝을 통해 속도 보장

  1. 인덱스를 타지 않는 경우

    • 대부분 좌변을 가공하는 경우
    • 상수나 우변을 가공할 방법을 모색해야함
    select * from TB where TRIM(USER_NO)='0001' --인덱스를 타지 않음
select * from TB where USER_NO='0001'

  2. IN vs EXISTS

    • 두 연산 모두 해당하는 값이 있는지에 대해 확인하는 연산
    • IN(모든 집합에서 충족한 집합을 찾아냄), EXISTS(명시된 기준을 충족하는 단일 행의 조건)
    • EXISTS가 연산에서 더 유리
    SELECT * FROM TB A 
WHERE A.USER_ID IN (
    SELECT X.USER_ID FROM TB_USER X
)

SELECT * FROM TB A 
WHERE EXISTS (
    SELECT 1 FROM TB_USER X WHERE X.USER_ID=A.USER_ID
)

  3. JOIN의 순서를 생각하지 않은 경우

    • 작은 것부터 연산하는 것이 성능상 유리
    SELECT
    *
FROM 
    TB_USER a
        INNER JOIN TB_ORDER B   --USER 테이블과 ORDER 테이블은 1:N관계. 
        ON A.USER_ID = B.USER_ID    -- INNER JOIN을 통해 테이블을 작게 만든다
WHERE
    B.ORDER_DT = '20210101'     -- 그 후 조건문 사용