[카카오테크 부트캠프] 5/26 TIL: DB, ERD, Index, Full Text Index, Transaction

1. DB의 등장 배경

데이터를 기록하고 관리하기 위한 필요에서 출발했다. 기존 파일 시스템 방식의 문제점은 다음과 같다.

• 데이터 중복 (학생 이름이 여러 파일에 분산)
• 검색 어려움 (파일이 커지면 Full Scan 필요)
• 보안·권한 부족
• 동시 수정 불가
• OS 간 호환성 문제

DB는 이 문제들을 중앙 집중 관리, 인덱스를 통한 효율적 검색, 트랜잭션과 락을 통한 동시 제어, 제약 조건을 통한 무결성 보장으로 해결했다.

Database / RDB / RDBMS 차이

용어	설명
Database	구조화된 데이터 집합 그 자체
RDB	데이터를 테이블(행/열) 형태로 저장하고 테이블 간 관계로 연결한 DB
RDBMS	RDB를 만들고 관리하는 소프트웨어 시스템 (ex. MySQL, PostgreSQL)

MySQL vs PostgreSQL

항목	MySQL	PostgreSQL
소유	오라클	커뮤니티 운영
라이선스	GPL (상업 사용 주의)	완전 자유
ACID 보장	설정에 따라 다름	모든 구성에서 완전 보장
읽기 성능	단순 쿼리에 강함	파티셔닝 시 더 빠름
쓰기 성능	튜닝 필요	기본값이 효율적
확장 방향	수평 확장 유리	수직 확장 유리
요즘 트렌드	유지 또는 소폭 하락	꾸준히 점유율 상승

MySQL은 단순 조회가 많은 서비스(뉴스 포털, 커머스)에, PostgreSQL은 데이터 정확성이 중요한 서비스(금융, 의료, 결제)에 적합하다.

SQL 분류

분류	설명	주요 명령어
DDL	데이터 정의어	CREATE, ALTER, DROP, TRUNCATE
DML	데이터 조작어	SELECT, INSERT, UPDATE, DELETE
DCL	데이터 제어어	GRANT, REVOKE
TCL	트랜잭션 제어어	COMMIT, ROLLBACK

UPDATE, DELETE에서 WHERE를 빠뜨리면 전체 데이터가 영향받는다. 실무 사고 1순위.

---

2. ERD (Entity Relationship Diagram)

개체와 관계를 한눈에 알아볼 수 있도록 그려놓은 도표다. 코드 작성 전에 설계 오류를 잡고 팀 간 소통 수단으로 활용된다.

DB 설계 3단계

1단계: 정보(개념) 모델링
       Entity, Attribute, Relationship 정의
       도구 없이 메모 수준으로 정리

2단계: 논리적 모델링
       Crow's Foot 표기법으로 ERD 구체화
       테이블 수, 컬럼, PK/FK, 관계 종류 확정
       특정 DB를 고려하지 않는 범용 설계

3단계: 물리적 구현
       CREATE TABLE로 실제 DB에 구현
       이 단계에서 MySQL/PostgreSQL 선택이 중요해짐

Crow's Foot 표기법 기호

기호	의미
Zero or One	없거나 딱 1개 (0..1)
One and Only One	반드시 1개만 (1..1)
Zero or Many	없거나 여러 개 (0..N)
One or Many	반드시 1개 이상 (1..N)

FK는 항상 N 쪽에 들어간다

-- Teacher(1) - Course(N) 관계에서
CREATE TABLE Course (
    course_id  INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(50),
    teacher_id INT,                              -- FK는 N 쪽인 Course에 들어감
    FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id)
);

식별 관계 vs 비식별 관계

	식별 관계	비식별 관계
자식 PK	부모 PK 포함	자기만의 PK
부모 없이 존재	불가	가능
ERD 표현	실선	점선
실무 선호도	낮음	높음

식별 관계를 사용하면 복합 PK로 인해 JOIN 조건이 복잡해지고 성능이 저하될 수 있다. 생성 시 부모가 필요한 경우라도 자체 PK가 있으면 비식별 관계이며, 이때 NOT NULL로 "반드시 부모가 있어야 한다"는 조건을 표현한다.

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(200),
    user_id INT NOT NULL,  -- 반드시 유저가 있어야 함 (비식별 + NOT NULL)
    FOREIGN KEY (user_id) REFERENCES users(id)
);

참조 무결성 (정보의 완결성)

관계를 맺는 데이터가 반드시 존재해야 한다는 개념이다. FK 제약으로 보장한다.

부서코드: 1 (개발)  사원번호: 1, 부서번호: 1  ← 정상
부서코드: 2 (기획)  사원번호: 2, 부서번호: 99 ← ❌ 99번 부서가 없음 → 무결성 위반

---

3. Index

테이블 조회 속도를 높이기 위해 특정 컬럼의 값과 레코드 위치를 별도로 관리하는 자료구조다.

인덱스 없을 때: Full Scan → O(N)
인덱스 있을 때: B+Tree 탐색 → O(log N)

데이터 100만 건 기준:
Full Scan   → 최대 1,000,000번 탐색
B+Tree 탐색 → 약 20번 탐색

Cardinality와 Selectivity

Cardinality는 특정 컬럼의 고유값 개수다. Selectivity = 고유값 수 / 전체 행 수다.

컬럼	Selectivity	인덱스 효과
주민등록번호	1.0	최고 ✅
이메일	높음	좋음 ✅
이름	중간	보통
성별	0.000002	없음 ❌

Selectivity가 낮은 컬럼에 인덱스를 걸면 Full Scan이 오히려 빠를 수 있다.

인덱스 자료구조 비교

	B+Tree	Hash Index
자료 구조	균형 트리	해시 테이블
단일 검색	O(log N)	O(1)
범위 검색	O(log N + K)	❌ 불가
정렬	✅ 가능	❌ 불가
주요 용도	RDB 기본 인덱스	NoSQL, 캐시, 등가 비교 특화

B+Tree 구조

          [30 | 70]           ← 내부 노드 (키만 존재, 탐색 기준)
         /    |    \
     [10|20] [40|50] [80|90] ← 내부 노드
      ↓  ↓    ↓  ↓    ↓  ↓
    [Leaf] → [Leaf] → [Leaf] ← 리프 노드 (실제 데이터, LinkedList로 연결)

리프 노드끼리 LinkedList로 연결되어 있어 범위 검색 시 트리를 다시 탐색하지 않아도 된다. BETWEEN, >, <, ORDER BY에 최적화된 이유다.

Clustered vs Non-Clustered Index

	Clustered Index	Non-Clustered Index
데이터 순서	인덱스 순서 = 디스크 저장 순서	별도 인덱스 테이블 + 포인터
테이블당 개수	1개 (PK 설정 시 자동 생성)	여러 개 가능
저장 공간	추가 불필요	별도 저장 공간 필요
조회 단계	1단계 (바로 접근)	2단계 (인덱스 → 포인터 → 데이터)

InnoDB의 Secondary Index는 디스크 주소 대신 PK 값을 저장한다. Secondary Index 조회 시 PK로 클러스터형 인덱스를 한 번 더 탐색하는 Double Lookup이 발생한다.

Covering Index

일반 인덱스: 인덱스 탐색 → 포인터 → 테이블 접근 (2단계)
Covering Index: 인덱스 탐색 → 끝 (1단계, 테이블 접근 없음)

SELECT 컬럼이 모두 인덱스에 포함되어 있으면 자동 적용된다. EXPLAIN에서 Extra = Using index로 확인할 수 있다.

CREATE INDEX idx_name_age ON users(name, age);

-- Covering Index 적용 (name, age 모두 인덱스에 있음)
SELECT name, age FROM users WHERE name = 'User_1';

-- Covering Index 미적용 (email은 인덱스에 없음)
SELECT name, age, email FROM users WHERE name = 'User_1';

EXPLAIN 결과 해석

EXPLAIN SELECT * FROM users WHERE name = 'User_1';
EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'User_1'; -- 실제 실행 결과

type 컬럼 (중요도 순)

type	의미
const	PK/Unique로 1건 정확히 조회 (최고) ✅
eq_ref	조인 시 PK 기반 1:1 매칭
ref	인덱스로 특정 값 탐색
range	BETWEEN, >, < 범위 탐색
fulltext	FULLTEXT 인덱스 사용
ALL	전체 테이블 스캔 (최악) ❌

Extra 컬럼

Extra	의미
Using index	Covering Index 적용 ✅
Using filesort	별도 정렬 발생 → 성능 저하 주의
Using temporary	임시 테이블 생성 → 성능 저하 주의

---

4. Full Text Index

LIKE '%검색어%' 방식은 앞에 %가 붙으면 B+Tree 인덱스를 탈 수 없어 Full Scan이 발생한다. Full Text Index는 이 문제를 해결하기 위한 텍스트 전용 인덱스다.

동작 원리는 텍스트를 단어 단위로 쪼개고, 각 단어가 어느 문서에 있는지 Inverted Index(역색인)로 저장하고, 검색 시 색인으로 바로 찾는 방식이다.

검색 모드 비교

	NATURAL LANGUAGE MODE	BOOLEAN MODE
우선 기준	관련성 점수	조건 만족 여부
반드시 포함	아님 (관련도 기반)	+연산자로 강제 가능
연산자 사용	불가	+, -, * 사용 가능
불용어 처리	영향 받음	영향 적음

-- 기본 검색 (Natural Language)
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('health');

-- 정밀 검색 (Boolean Mode)
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('+health -nutrition' IN BOOLEAN MODE);
-- health 포함, nutrition 제외

-- 관련도 점수로 정렬
SELECT title, MATCH(title, body) AGAINST('health') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('health')
ORDER BY score DESC;

관련도 점수 계산 요소

1. 등장 빈도 — 문서 안에서 검색어가 많이 나올수록 높음
2. 등장 위치 — 제목이 본문보다 가중치 높음
3. 희소성(IDF) — 전체 문서에서 드문 단어일수록 높음

검색 정렬 전략

전략	적합한 서비스
최신순 기본	커뮤니티, 게시판 (활동성 중요)
관련도순 기본	문서 검색, 지식 검색 (정확도 중요)
둘 다 제공	뉴스 검색, 공지 검색

Full Text Index의 한계

새로운 글이 INSERT될 때마다 Inverted Index를 재생성해야 해서 기본적으로 무겁다. 전문 검색 품질을 원한다면 Elasticsearch 같은 별도 검색 엔진을 사용해야 한다.

---

5. Transaction

여러 개의 DB 작업을 하나의 단위로 묶어 처리하는 것이다. 전부 성공하면 COMMIT, 하나라도 실패하면 ROLLBACK으로 전부 되돌린다.

START TRANSACTION;

UPDATE account SET balance = balance - 100000 WHERE username = 'void'; -- 출금
UPDATE account SET balance = balance + 100000 WHERE username = 'yaro'; -- 입금

COMMIT;    -- 둘 다 성공 시 확정
ROLLBACK;  -- 실패 시 전부 되돌림 (보통 try-catch로 처리)

ROLLBACK은 개발자가 try-catch로 직접 판단해 실행하는 게 일반적이다. 서버 크래시나 데드락 발생 시에는 DB가 자동으로 ROLLBACK한다.

트랜잭션은 최소 범위로 사용해야 한다. DB 연결 수는 제한되어 있어 트랜잭션을 오래 유지하면 커넥션이 고갈된다.

트랜잭션 상태

Active            → 실행 중, 성공/실패 미결정
Partially Committed → 모든 SQL 완료, 디스크 저장 전 (장애 시 롤백 가능)
Committed         → 디스크 영구 저장 완료, 되돌릴 수 없음
Failed            → 오류로 진행 불가
Aborted           → 롤백 완료, 이전 상태로 복원

ACID

특성	설명
Atomicity (원자성)	전부 성공하거나 전혀 수행되지 않아야 함
Consistency (일관성)	트랜잭션 전후로 DB의 규칙이 항상 지켜져야 함
Isolation (독립성)	동시에 여러 트랜잭션이 실행되어도 서로 간섭하면 안 됨
Durability (지속성)	COMMIT된 데이터는 서버가 죽어도 살아있어야 함

Isolation Level

레벨	이름	Dirty Read	Non-Repeatable Read	Phantom Read	성능
0	Read Uncommitted	발생	발생	발생	가장 빠름
1	Read Committed	방지	발생	발생	빠름
2	Repeatable Read	방지	방지	발생	보통
3	Serializable	방지	방지	방지	가장 느림

MySQL InnoDB 기본값은 Repeatable Read, PostgreSQL 기본값은 Read Committed다. 무조건 Serializable로 올리면 동시 처리 성능이 급격히 저하된다.

데이터 읽기 이상 현상

Dirty Read — 커밋되지 않은 데이터를 읽는 현상
A가 수정 중(미확정)인 값을 B가 읽고, A가 롤백하면 B는 존재하지 않는 값을 읽은 것이 된다.

Non-Repeatable Read — 같은 트랜잭션에서 같은 데이터를 읽었는데 값이 달라지는 현상
A가 읽는 사이에 B가 수정 후 커밋하면 A가 다시 읽을 때 값이 바뀐다.

Phantom Read — 같은 조건으로 범위 조회를 했는데 행의 수가 달라지는 현상
A가 조회하는 사이에 B가 새 데이터를 INSERT 후 커밋하면 결과 건수가 달라진다.

실무 트랜잭션 범위 설계

판단 기준: "이 작업은 부분 성공이 허용되는가?"

허용되지 않으면 하나의 트랜잭션으로 묶어야 한다.

패턴	예시
원본 + 파생 데이터 동시 변경	좋아요 추가 + like_count 증가
생성 + 연결 동시 처리	게시글 INSERT + 파일 INSERT + 파일 연결
여러 SQL이 하나의 의미	댓글 INSERT + comment_count 증가

// 실무 패턴: try-catch로 ROLLBACK 처리
try {
    await db.query('START TRANSACTION');
    await db.query('UPDATE accounts SET balance = balance - 10000 WHERE id = 1');
    await db.query('UPDATE accounts SET balance = balance + 10000 WHERE id = 2');
    await db.query('COMMIT');
} catch (error) {
    await db.query('ROLLBACK');
}