CS

[CS] Database 정리 (2)

inhooo00 2025. 8. 2. 22:25
728x90

📍정규화

가장 큰 목표는 테이블 간 중복된 데이터를 허용하지 않는 것이다.

중복된 데이터를 만들지 않으면, 무결성을 유지할 수 있고, DB 저장 용량 또한 효율적으로 관리할 수 있다.

 

목적

  • 데이터의 중복을 없애면서 불필요한 데이터를 최소화시킨다.
  • 무결성을 지키고, 이상 현상을 방지한다.
  • 테이블 구성을 논리적이고 직관적으로 할 수 있다.
  • 데이터베이스 구조를 확장에 용이해진다.

정규화에는 여러가지 단계가 있지만, 대체적으로 1~3단계 정규화까지의 과정을 거친다.

 

제 1정규화(1NF)

테이블 컬럼이 원자값(하나의 값)을 갖도록 테이블을 분리시키는 것을 말한다.

만족해야 할 조건은 아래와 같다.

  • 어떤 릴레이션에 속한 모든 도메인이 원자값만으로 되어 있어야한다.
  • 모든 속성에 반복되는 그룹이 나타나지 않는다.
  • 기본키를 사용하여 관련 데이터의 각 집합을 고유하게 식별할 수 있어야 한다.

 

전화번호 테이블에 번호가 1개 이상으로 되어 있기 때문에 1 정규화를 만족시키지 못한다. 아래와 같이 행을 분리해야한다.

 

제 2정규화(2NF)

테이블의 모든 컬럼이 완전 함수적 종속을 만족해야 한다.

조금 쉽게 말하면, 테이블에서 기본키가 복합키(키1, 키2)로 묶여있을 때, 두 키 중 하나의 키만으로 다른 컬럼을 결정지을 수 있으면 안된다.

기본키의 부분집합 키가 결정자가 되어선 안된다는 것

Manufacturer과 Model이 기본키인 복합키 상태이다. 

하지만 Manufacturer Country는 Manufacturer로만 인해 결정된다. 이것이 부분 함수 종속이라는 것.

다르게 말하자면 Model은 복합키로서 같이 있을 의미가 없다는 뜻이다.

 

결국 완전 함수적 종속을 충족시키지 못하고 있는 테이블이다. 부분 함수 종속을 해결하기 위해 테이블을 아래와 같이 나눠서 2NF를 만족할 수 있다.

제 3정규화(3NF)

2NF가 진행된 테이블에서 이행적 종속을 없애기 위해 테이블을 분리하는 것이다.

이행적 종속 : A → B, B → C면 A → C가 성립된다

아래 두가지 조건을 만족시켜야 한다.

  • 릴레이션이 2NF에 만족한다.
  • 기본키가 아닌 속성들은 기본키에 의존한다.

현재 테이블에서는 Tournament와 Year이 기본키다.

Winner는 이 두 복합키를 통해 결정된다.

하지만 Winner Date of Birth는 기본키가 아닌 Winner에 의해 결정되고 있다.

따라서 이는 3NF를 위반하고 있으므로 아래와 같이 분리해야 한다.

 

 

역정규화는 왜 할까?

역정규화란 성능 향상이나 개발 생산성 향상을 위해, 정규화된 데이터 모델을 일부러 다시 합쳐 중복을 허용하는 것.

역정규화가 필요한 주요 이유

 

  • 성능 관점
    • 잦은 조인으로 인한 성능 저하 방지
    • 집계 쿼리 속도 향상을 위한 목적성(집계) 테이블
    • 예: 재고 테이블, 현재고 상태 유지
  • 개발 생산성 관점
    • 화면에서 자주 필요한 데이터를 쉽게 가져오기 위해
    • 예: 고객 연락처를 테이블 분리하지 않고 가로로 표시

 

역정규화의 문제점

 

  • 정합성 저하: 데이터 중복으로 인한 불일치 가능성
  • 확장성 저하: 구조 변경이 어렵고 유연하지 않음
  • 모델의 복잡도 증가: 유지보수 어려움

역정규화는 언제 적용해야 하나?

  • 논리 모델링 단계: 기본적으로 정규화된 상태로 모델링
  • 물리 모델링 단계 이후:
    • 실제 SQL 성능이 느릴 때 (2.5초 이상 등 목표 기준 미달)
    • 데이터 이관과 인덱스 설계까지 끝난 후
    • → 이때 실제로 느릴 때만 역정규화 적용

❗ "물리모델링 단계에서 무조건 역정규화를 해야 한다"는 잘못된 관행
→ 감, 경험, 책에만 의존한 판단은 위험

정리된 결론

  • 역정규화는 최소화되어야 한다
  • 데이터 정합성과 유연성 유지가 우선
  • 성능/생산성 향상이 “실제로 필요한 상황”일 때만 적용
  • SQL 성능 측정 가능한 시점(데이터+인덱스 후)에서 결정해야 가장 정확
  • 고객번호 | 자택전화     | 회사전화     | 휴대폰전화
    --------|--------------|-------------|-------------
    C001    | 010-1234-1111 | 02-9876-2222 | 010-3333-4444
    이런 식의 예시에서 조회할 때마다 PIVOT 또는 복잡한 JOIN/CASE가 들어가서
    성능이나 개발 생산성이 떨어질 수 있기에 사용!

 

📍DB 트랜잭션(Transaction)

트랜젝션 = 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위

 

상태를 변화시킨다는 것 → SQL 질의어를 통해 DB에 접근하는 것

- SELECT
- INSERT
- DELETE
- UPDATE

 

작업 단위 → 많은 SQL 명령문들을 사람이 정하는 기준에 따라 정하는 것

즉, 하나의 트랜잭션 설계를 잘 만드는 것이 데이터를 다룰 때 많은 이점을 가져다준다.

 


트랜잭션 특징(ACID)

  • 원자성(Atomicity)
  • 트랜잭션이 DB에 모두 반영되거나, 혹은 전혀 반영되지 않아야 된다.
  • 일관성(Consistency)
  • 트랜잭션의 작업 처리 결과는 항상 일관성 있어야 한다.
  • 독립성(Isolation)
  • 둘 이상의 트랜잭션이 동시에 병행 실행되고 있을 때, 어떤 트랜잭션도 다른 트랜잭션 연산에 끼어들 수 없다.
  • 지속성(Durability)
  • 트랜잭션이 성공적으로 완료되었으면, 결과는 영구적으로 반영되어야 한다.

Commit

하나의 트랜잭션이 성공적으로 끝났고, DB가 일관성있는 상태일 때 이를 알려주기 위해 사용하는 연산


Rollback

하나의 트랜잭션 처리가 비정상적으로 종료되어 트랜잭션 원자성이 깨진 경우

transaction이 정상적으로 종료되지 않았을 때, last consistent state (예) Transaction의 시작 상태) 로 roll back 할 수 있음.

 

DBMS 구조

핵심 개념 1. DBMS는 크게 2부분으로 나눌 수 있다.

Query Processor 사용자의 SQL을 해석하고 실행계획을 세움
Storage System 데이터를 저장하고 관리 (→ 트랜잭션 관리, 복구 등 포함)

 

핵심 개념 2. 페이지 단위 입출력 & 버퍼

 

  • 디스크 I/O는 작은 단위(=페이지) 로 이루어짐 (예: 4KB)
  • 자주 접근하는 페이지는 메인 메모리 버퍼(pool) 에 올려서 사용
  • 이걸 관리하는 게 Buffer Manager

 

Transaction 관리를 위한 DBMS의 전략

 

예를 들자면, 데이터를 작업하다가 컴퓨터가 갑자기 꺼졌을 때 내가 이걸 다시 불러올지, 그냥 다 없애고 새로 작성할지 결정하는 전략이 있다.

 

1. REDO = 데이터 다시 불러오기

  • FORCE:
    수정했던 모든 페이지를 Transaction commit 시점에 디스크에 반영
    → 이미 저장됐으니 다시 저장할 필요 없음
    → 👉 REDO 필요 없음
  • ¬FORCE:
    Commit 했지만, 디스크에는 아직 안 저장됨
    → 👉 컴퓨터가 꺼지면 저장 안 됐던 걸 다시 저장해야 함
    → 👉 REDO 필요함

2.UNDO = 잘못된 데이터 되돌리기

  • STEAL:트랜잭션이 끝나기 전에라도 수정된 내용을 디스크에 저장할 수 있음
    → 만약 트랜잭션이 실패하면
    → 👉 저장된 내용을 지워야 함
    → 👉 UNDO 필요함
  • ¬STEAL:
    트랜잭션이 완전히 끝나기 전에는 절대 디스크에 안 씀
    → 실패해도 저장된 게 없음
    → 👉 UNDO 필요 없음

‼️ 실무에서는 대부분 STEAL + ¬FORCE 조합을 사용.

"트랜잭션이 실패하거나 시스템이 꺼져도 기록은 어딘가 남아 있을 수 있다.

→ 그래서 나는 필요에 따라 UNDO 또는 REDO를 결정해서 복구해야 한다." 구조.

📍트랜잭션 격리 수준(Transaction Isolation Level)

트랜잭션에서 일관성 없는 데이터를 허용하도록 하는 수준

 

Isolation level의 필요성

데이터베이스는 ACID 특징과 같이 트랜잭션이 독립적인 수행을 하도록 한다.

따라서 Locking을 통해, 트랜잭션이 DB를 다루는 동안 다른 트랜잭션이 관여하지 못하도록 막는 것이 필요하다.

하지만 무조건 Locking으로 동시에 수행되는 수많은 트랜잭션들을 순서대로 처리하는 방식으로 구현하게 되면 데이터베이스의 성능은 떨어지게 될 것이다.

그렇다고 해서, 성능을 높이기 위해 Locking의 범위를 줄인다면, 잘못된 값이 처리될 문제가 발생하게 된다.
-> 따라서 최대한 효율적인 Locking 방법이 필요함!

 

Read Uncommitted (레벨 0)

SELECT 문장이 수행되는 동안 해당 데이터에 Shared Lock이 걸리지 않는 계층

트랜잭션에 처리중이거나, 아직 Commit되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용함

사용자1이 A라는 데이터를 B라는 데이터로 변경하는 동안 사용자2는 아직 완료되지 않은(Uncommitted) 트랜잭션이지만 데이터B를 읽을 수 있다

데이터베이스의 일관성을 유지하는 것이 불가능함

 

Read Committed (레벨 1)

SELECT 문장이 수행되는 동안 해당 데이터에 Shared Lock이 걸리는 계층

트랜잭션이 수행되는 동안 다른 트랜잭션이 접근할 수 없어 대기하게 됨

Commit이 이루어진 트랜잭션만 조회 가능

대부분의 SQL 서버가 Default로 사용하는 Isolation Level임

사용자1이 A라는 데이터를 B라는 데이터로 변경하는 동안 사용자2는 해당 데이터에 접근이 불가능함

 

Repeatable Read (레벨 2)

트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리는 계층

트랜잭션이 범위 내에서 조회한 데이터 내용이 항상 동일함을 보장함

다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 불가능

MySQL에서 Default로 사용하는 Isolation Level

 

Serializable (레벨 3)

트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리는 계층

완벽한 읽기 일관성 모드를 제공함

다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 및 입력 불가능

 

낮은 단계의 Isolation Level(격리 수준) 을 사용할 경우,
트랜잭션 간 동시성 제어가 느슨해지기 때문에 다양한 이상현상(anomalies) 이 발생할 수 있다.

 

Dirty Read 커밋되지 않은 데이터를 읽음 Read Uncommitted 트랜잭션 A가 데이터를 수정했지만 아직 커밋 안 한 상태 → 트랜잭션 B가 그 값을 읽음

아직 uncommitted 한 데이터도 읽기 때문에 나타나는 현상.

Non-Repeatable Read 같은 조건의 SELECT 결과가 다르게 나옴 Read Uncommitted, Read Committed 트랜잭션 A가 같은 쿼리를 두 번 했는데, 중간에 트랜잭션 B가 값을 수정함

Non-Repeatable Read는 DBMS가 읽을 때만 커밋된 데이터인지 확인하고,
그 이후에는 해당 데이터를 다른 트랜잭션이 수정할 수 있도록 허용하는 격리 수준에서 발생하는 현상이다.\

 

Phantom Read 같은 조건의 SELECT에서 행 개수가 다름 Read Uncommitted, Read Committed, Repeatable Read 트랜잭션 A가 "WHERE age > 30" 조건으로 SELECT 했는데, 중간에 트랜잭션 B가 age = 35인 행을 INSERT함

Phantom Read는 조건에는 맞지만 원래는 없던 행이 갑자기 나타나거나 사라지는 현상
트랜잭션이 조건 범위 자체를 잠그지 않으면 생김.

즉, 내가 조회한 결과와 실제 DB에 저장된 데이터가 트랜잭션 도중에 달라지는 것.

📍저장 프로시저(Stored PROCEDURE)

DB에 저장된 함수 같은 쿼리 묶음이다.

 

  • 쿼리가 여러 줄 필요할 때,
    매번 그걸 복붙해서 쓰는 대신 “이름 붙여서 저장” 해두고
  • 필요할 때마다 “함수 호출하듯” 실행하는 것.

예를 들어 쇼핑몰에서 사용자 주문 정보를 처리하려면 다음 쿼리가 필요하다고 해보자.

INSERT INTO orders ... UPDATE stock ... INSERT INTO logs ...

이걸 매번 애플리케이션이나 SQL창에 쓰는 건 귀찮고 위험하다.
👉 그래서 이걸 한 덩어리로 저장해서 process_order()처럼 부르면 끝!

 

프로시저 종류

▶ IN (입력만 받는 경우)

CREATE OR REPLACE PROCEDURE say_hello(name IN VARCHAR2)
IS
BEGIN
    dbms_output.put_line('안녕하세요, ' || name || '님!');
END;

👉 실행: EXEC say_hello('지훈');
👉 출력: 안녕하세요, 지훈님!

이런 식으로 나옴.

 

▶ OUT (값을 바깥으로 돌려주는 경우)

CREATE OR REPLACE PROCEDURE get_name(name OUT VARCHAR2)
IS
BEGIN
    name := '밍기적';
END;
-- 호출 방법
DECLARE
    output_name VARCHAR2(100);
BEGIN
    get_name(output_name);
    dbms_output.put_line('내 이름은 ' || output_name);
END;

👉 출력: 내 이름은 밍기적

 

프로시저의 장점

♻️재사용 여러 번 쓰는 쿼리를 하나로 저장해서 계속 호출 가능
🏃속도 빠름 첫 실행만 컴파일하고, 그다음부터는 캐시에서 바로 실행됨
🙌유지보수 쉬움 쿼리 바꿀 일이 생기면 저장된 프로시저만 수정하면 됨
🤓트래픽 줄임 쿼리 문장 전체를 보내는 대신 함수명+파라미터만 보내면 됨
🔐보안 강화 사용자가 직접 테이블에 접근하지 않아도 됨

 

프로시저의 단점

❗호환성 낮음 DB마다 문법이 달라서 코드 이식이 어려움
❗속도 느림 복잡한 계산은 C/Java 같은 언어보다 느릴 수 있음
❗ 디버깅 어려움 어디서 오류났는지 찾기 어렵고 도구도 부족한 편

 

=> 즉 프로시저를 해놓으면 SQL 해석은 최초 한번만 해놓기 때문에, 자주 실행되는 로직이면서 수정이 적은 로직에 해놓으면 좋다. 더해서 인덱스와 별개로, 이미 해석해 놓은 쿼리가 실행시킬 때 인덱스가 관여하기 때문에 둘이 같이 쓰는게 베스트다.

 

 

728x90

'CS' 카테고리의 다른 글

[CS] Database 정리 (1)  (4) 2025.08.01

'CS'의 다른글

  • 현재글[CS] Database 정리 (2)

관련글

티스토리툴바