데이터베이스 - RDBMS 특징

데이터가 사라졌다

금요일 밤 11시, 장애 알림이 울렸다.

"DB 서버 응답 없음"

Court Alarm의 모든 예약 데이터가 저장된 MySQL 서버가 다운됐다. 급하게 서버에 접속했다.

mysql -u root -p
# ERROR 2002 (HY000): Can't connect to local MySQL server

MySQL이 죽어있었다. 재시작을 시도했다.

systemctl start mysql
# Job for mysql.service failed

로그를 확인했다.

tail -f /var/log/mysql/error.log

[ERROR] InnoDB: Corruption detected in tablespace reservations
[ERROR] InnoDB: Database page corruption
[ERROR] InnoDB: Cannot start InnoDB
[ERROR] Aborting

디스크 손상으로 데이터베이스가 깨졌다. 심장이 멎는 것 같았다. 수만 명의 예약 데이터가...

하지만 당황하지 않았다. RDBMS의 핵심 기능인 트랜잭션과 복구 메커니즘을 믿었다.

# 바이너리 로그로 복구
mysqlbinlog --start-position=12345 /var/log/mysql/binlog.000042 | mysql

# 백업에서 복원 + 바이너리 로그 재생

30분 후, 모든 데이터가 복구됐다. 단 한 건의 예약도 손실되지 않았다.

이 경험으로 RDBMS의 ACID 특성이 왜 중요한지, 어떻게 데이터 무결성을 보장하는지 뼈저리게 이해하게 됐다.

RDBMS란 무엇인가

관계형 데이터베이스의 기본

RDBMS(Relational Database Management System)는 관계형 모델을 기반으로 데이터를 관리하는 시스템이다.

핵심 개념:

테이블 (Table) = 릴레이션 (Relation)
┌──────┬──────────┬──────┬──────────┐
│ ID   │ Name     │ Age  │ Email    │  ← 속성 (Attribute)
├──────┼──────────┼──────┼──────────┤
│ 1    │ 규철     │ 30   │ a@a.com  │  ← 튜플 (Tuple)
│ 2    │ 민수     │ 25   │ b@b.com  │
│ 3    │ 영희     │ 28   │ c@c.com  │
└──────┴──────────┴──────┴──────────┘
        ↑
     기본키 (Primary Key)

관계 (Relationship):

사용자 (Users)                예약 (Reservations)
┌────┬────────┐              ┌────┬─────────┬────────┐
│ ID │ Name   │              │ ID │ User_ID │ Court  │
├────┼────────┤              ├────┼─────────┼────────┤
│ 1  │ 규철   │◄─────────────│ 1  │ 1       │ 코트A  │
│ 2  │ 민수   │              │ 2  │ 1       │ 코트B  │
└────┴────────┘              │ 3  │ 2       │ 코트A  │
                              └────┴─────────┴────────┘
                                    ↑
                                외래키 (Foreign Key)

주요 RDBMS

오픈소스:

• MySQL: 가장 널리 사용, 웹 서비스 표준
• PostgreSQL: 강력한 기능, SQL 표준 준수
• MariaDB: MySQL 포크, 호환성 좋음

상용:

• Oracle: 대기업 표준, 강력한 기능
• MS SQL Server: Windows 환경
• DB2: IBM 메인프레임

경량:

• SQLite: 임베디드, 서버 없음
• H2: Java 기반, 테스트용

ACID: RDBMS의 핵심

Atomicity (원자성)

트랜잭션의 모든 연산이 완전히 수행되거나 전혀 수행되지 않음:

-- 계좌 이체 트랜잭션
START TRANSACTION;

-- 1. A 계좌에서 출금
UPDATE accounts 
SET balance = balance - 10000 
WHERE account_id = 'A';

-- 2. B 계좌에 입금
UPDATE accounts 
SET balance = balance + 10000 
WHERE account_id = 'B';

COMMIT;  -- 둘 다 성공
-- 또는
ROLLBACK;  -- 둘 다 취소

중간 상태는 존재하지 않음:

시나리오: 출금 성공, 입금 실패

❌ 원자성 없음:
A 계좌: 100,000 → 90,000  (출금 O)
B 계좌: 50,000 → 50,000   (입금 X)
결과: 돈이 사라짐!

✅ 원자성 있음:
A 계좌: 100,000 → 100,000 (롤백)
B 계좌: 50,000 → 50,000   (롤백)
결과: 트랜잭션 전체 취소

Court Alarm 예시:

@Transactional
fun createReservation(request: ReservationRequest): Reservation {
    // 1. 좌석 감소
    val court = courtRepository.findById(request.courtId)
    court.availableSlots--
    courtRepository.save(court)
    
    if (court.availableSlots < 0) {
        throw IllegalStateException("좌석 없음")
        // 자동 롤백: 좌석 감소 취소
    }
    
    // 2. 예약 생성
    val reservation = Reservation(
        userId = request.userId,
        courtId = request.courtId,
        time = request.time
    )
    
    return reservationRepository.save(reservation)
    
    // 둘 다 성공하거나 둘 다 실패
}

Consistency (일관성)

트랜잭션 실행 전후로 데이터베이스가 일관된 상태 유지:

제약 조건 (Constraints):

-- 1. PRIMARY KEY: 유일성, NULL 불가
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 0),
    balance DECIMAL(10, 2) CHECK (balance >= 0)
);

-- 2. FOREIGN KEY: 참조 무결성
CREATE TABLE reservations (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE  -- 사용자 삭제 시 예약도 삭제
);

-- 3. CHECK: 값 범위 제한
CREATE TABLE courts (
    id INT PRIMARY KEY,
    max_capacity INT CHECK (max_capacity BETWEEN 2 AND 100),
    available_slots INT,
    CHECK (available_slots <= max_capacity)
);

일관성 위반 예시:

-- ❌ 일관성 위반 시도
INSERT INTO users (id, email, age, balance) 
VALUES (1, 'test@test.com', -5, -1000);
-- ERROR: CHECK constraint violated

-- ❌ 참조 무결성 위반
INSERT INTO reservations (id, user_id) 
VALUES (1, 999);
-- ERROR: Foreign key constraint violated
-- (user_id 999가 users 테이블에 없음)

-- ❌ 유일성 위반
INSERT INTO users (id, email) 
VALUES (2, 'test@test.com');
-- ERROR: Duplicate entry for key 'email'

트리거로 복잡한 일관성 보장:

-- 예약 시 좌석 수 자동 감소
CREATE TRIGGER decrease_slots_on_reservation
AFTER INSERT ON reservations
FOR EACH ROW
BEGIN
    UPDATE courts 
    SET available_slots = available_slots - 1
    WHERE id = NEW.court_id;
    
    -- 좌석이 음수가 되면 에러
    IF (SELECT available_slots FROM courts WHERE id = NEW.court_id) < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '좌석 부족';
    END IF;
END;

Isolation (격리성)

동시 실행되는 트랜잭션들이 서로 영향을 주지 않음:

격리 수준 (Isolation Levels):

1. READ UNCOMMITTED (가장 낮음)

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance + 1000 WHERE id = 1;
-- 아직 COMMIT 안 함

-- Transaction B (다른 세션)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- Transaction A의 미완료 변경을 읽음 (Dirty Read)

문제: Dirty Read

• 커밋 안 된 데이터 읽기
• Transaction A가 롤백하면 B는 잘못된 데이터 읽은 것

2. READ COMMITTED (기본값)

-- Transaction A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 100,000

-- Transaction B
START TRANSACTION;
UPDATE accounts SET balance = 200000 WHERE id = 1;
COMMIT;

-- Transaction A (계속)
SELECT balance FROM accounts WHERE id = 1;  -- 200,000 (변경됨!)
COMMIT;

문제: Non-Repeatable Read

• 같은 트랜잭션 내에서 같은 쿼리가 다른 결과
• 다른 트랜잭션의 COMMIT 영향 받음

3. REPEATABLE READ (MySQL 기본)

-- Transaction A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 100000;  -- 5건

-- Transaction B
START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (10, 150000);
COMMIT;

-- Transaction A (계속)
SELECT * FROM accounts WHERE balance > 100000;  -- 여전히 5건
-- InnoDB의 MVCC로 스냅샷 읽기

SELECT * FROM accounts WHERE id = 10;  -- NULL (안 보임)
COMMIT;

InnoDB MVCC (Multi-Version Concurrency Control):

accounts 테이블 실제 데이터:
┌────┬─────────┬────────────┐
│ ID │ Balance │ TX_ID      │
├────┼─────────┼────────────┤
│ 1  │ 100,000 │ 100 (Old)  │
│ 1  │ 200,000 │ 105 (New)  │ ← Transaction B가 생성
└────┴─────────┴────────────┘

Transaction A (TX_ID: 103):
- TX_ID 103 이전 데이터만 읽음
- Balance = 100,000 계속 보임 (스냅샷)

Transaction B (TX_ID: 105):
- 새 버전 생성 (Balance = 200,000)
- COMMIT 후 다른 트랜잭션이 볼 수 있음

여전히 남는 문제: Phantom Read

-- Transaction A
START TRANSACTION;
SELECT COUNT(*) FROM accounts WHERE balance > 100000;  -- 5

-- Transaction B
INSERT INTO accounts (id, balance) VALUES (10, 150000);
COMMIT;

-- Transaction A
SELECT COUNT(*) FROM accounts WHERE balance > 100000;  -- 5 (InnoDB)
-- 하지만...
UPDATE accounts SET balance = balance + 1000 WHERE balance > 100000;
-- 6건 업데이트됨! (Phantom)

4. SERIALIZABLE (가장 높음)

-- Transaction A
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 100000;
-- 범위 락 (Range Lock) 획득

-- Transaction B
INSERT INTO accounts (id, balance) VALUES (10, 150000);
-- 대기... (Transaction A가 COMMIT할 때까지)

완전 격리:

• Phantom Read 방지
• 성능 최악 (직렬화)

격리 수준 비교:

격리 수준 Dirty Read Non-Repeatable Read Phantom Read

READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	방지	발생	발생
REPEATABLE READ	방지	방지	발생(InnoDB는 일부 방지)
SERIALIZABLE	방지	방지	방지

Court Alarm 선택:

// 예약 생성: REPEATABLE READ (기본값)
@Transactional(isolation = Isolation.REPEATABLE_READ)
fun createReservation(...) {
    // 좌석 확인 후 예약
    // 다른 트랜잭션의 변경 안 보임 (스냅샷)
}

// 통계 조회: READ COMMITTED
@Transactional(
    isolation = Isolation.READ_COMMITTED,
    readOnly = true
)
fun getStatistics() {
    // 최신 데이터 읽기
    // 성능 우선
}

Durability (지속성)

커밋된 트랜잭션은 영구적으로 반영됨:

Write-Ahead Logging (WAL):

1. 트랜잭션 시작
   UPDATE accounts SET balance = 200000 WHERE id = 1

2. Redo Log에 먼저 기록 (디스크)
   [Log] UPDATE accounts id=1 balance=200000

3. 메모리(Buffer Pool)에 변경
   [Memory] id=1 balance=200000

4. COMMIT
   - Redo Log를 디스크에 fsync() (강제 쓰기)
   - 사용자에게 "성공" 응답

5. 백그라운드로 실제 데이터 파일에 쓰기
   (나중에, 느긋하게)

서버 죽으면?

재시작 시:
1. Redo Log 확인
2. 커밋된 트랜잭션 찾기
3. 데이터 파일에 재적용 (Redo)
4. 커밋 안 된 트랜잭션 취소 (Undo)
→ 데이터 손실 없음!

MySQL InnoDB 설정:

-- Durability 설정
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
-- 1: 매 트랜잭션마다 fsync() (가장 안전, 느림)
-- 0: 1초마다 fsync() (빠름, 1초치 손실 가능)
-- 2: OS 버퍼에만 쓰기 (중간)

Court Alarm 경험:

장애 상황:
- 디스크 손상으로 데이터 파일 깨짐
- MySQL 재시작 실패

복구:
1. Redo Log 확인 (손상 안 됨)
2. 최근 풀백업 복원 (어제 백업)
3. Binary Log 재생 (오늘의 변경사항)

결과:
- 모든 커밋된 트랜잭션 복구
- 데이터 손실: 0건

정규화 (Normalization)

왜 정규화가 필요한가

비정규화 테이블의 문제:

-- ❌ 비정규화: 중복 데이터
CREATE TABLE reservations_bad (
    id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),      -- 중복!
    user_email VARCHAR(100),    -- 중복!
    court_id INT,
    court_name VARCHAR(50),     -- 중복!
    court_location VARCHAR(100),-- 중복!
    reservation_time DATETIME
);

-- 데이터
┌────┬─────────┬──────┬────────────┬──────────┬────────┬──────────┬─────────────────────┐
│ ID │ User_ID │ Name │ Email      │ Court_ID │ Name   │ Location │ Time                │
├────┼─────────┼──────┼────────────┼──────────┼────────┼──────────┼─────────────────────┤
│ 1  │ 1       │ 규철 │ a@test.com │ 10       │ 코트A  │ 서울     │ 2024-01-10 10:00:00 │
│ 2  │ 1       │ 규철 │ a@test.com │ 11       │ 코트B  │ 서울     │ 2024-01-10 11:00:00 │
│ 3  │ 2       │ 민수 │ b@test.com │ 10       │ 코트A  │ 서울     │ 2024-01-10 12:00:00 │
└────┴─────────┴──────┴────────────┴──────────┴────────┴──────────┴─────────────────────┘

문제점:

1. Update Anomaly (갱신 이상)

-- 규철의 이메일 변경
UPDATE reservations_bad 
SET user_email = 'new@test.com' 
WHERE user_id = 1;

-- 문제: 예약 2개 모두 업데이트 필요
-- 하나만 업데이트하면 데이터 불일치

1. Insert Anomaly (삽입 이상)

-- 새 코트 추가하고 싶지만
-- 예약이 없으면 추가 불가능
INSERT INTO reservations_bad (court_name, court_location) 
VALUES ('코트C', '부산');
-- ERROR: id, user_id가 NULL이면 안 됨

1. Delete Anomaly (삭제 이상)

-- 예약 1 삭제
DELETE FROM reservations_bad WHERE id = 1;

-- 문제: 코트A 정보도 함께 사라질 수 있음
-- (마지막 예약이었다면)

1. 저장 공간 낭비

규철의 이름, 이메일이 예약마다 중복 저장
코트A 정보가 예약마다 중복 저장

제1정규형 (1NF)

각 컬럼이 원자값(Atomic Value)만 가짐:

-- ❌ 1NF 위반: 반복 그룹
CREATE TABLE users_bad (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(100)  -- "010-1234-5678, 010-9876-5432"
);

-- ✅ 1NF: 별도 테이블
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE user_phones (
    id INT PRIMARY KEY,
    user_id INT,
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

제2정규형 (2NF)

부분 함수 종속 제거:

-- ❌ 2NF 위반
CREATE TABLE order_items_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),    -- product_id에만 종속
    product_price DECIMAL(10, 2), -- product_id에만 종속
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 문제: 
-- product_name은 (order_id, product_id)가 아닌
-- product_id에만 종속됨 (부분 종속)

-- ✅ 2NF: 테이블 분리
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

제3정규형 (3NF)

이행적 함수 종속 제거:

-- ❌ 3NF 위반
CREATE TABLE employees_bad (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    department_name VARCHAR(50),   -- department_id에 종속
    department_location VARCHAR(100) -- department_id에 종속
);

-- 문제:
-- id → department_id → department_name (이행적 종속)

-- ✅ 3NF: 테이블 분리
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    location VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Court Alarm 정규화 예시

초기 (비정규화):

-- ❌ 하나의 거대한 테이블
CREATE TABLE reservations_all (
    reservation_id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),
    user_email VARCHAR(100),
    user_phone VARCHAR(20),
    court_id INT,
    court_name VARCHAR(50),
    court_address VARCHAR(200),
    court_city VARCHAR(50),
    court_capacity INT,
    reservation_date DATE,
    reservation_time TIME,
    status VARCHAR(20)
);

정규화 후:

-- ✅ 제3정규형
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE courts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(200) NOT NULL,
    city_id INT NOT NULL,
    capacity INT NOT NULL,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

CREATE TABLE reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    court_id INT NOT NULL,
    reservation_date DATE NOT NULL,
    reservation_time TIME NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'confirmed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (court_id) REFERENCES courts(id),
    UNIQUE KEY (court_id, reservation_date, reservation_time)
);

장점:

• 중복 데이터 제거
• 데이터 일관성 보장
• 저장 공간 절약
• 유지보수 쉬움

단점:

• JOIN 필요 (성능 저하 가능)
• 쿼리 복잡해짐

역정규화 (Denormalization)

성능을 위해 의도적으로 중복 허용:

-- 예약 목록 조회가 너무 느림 (JOIN 3개)
SELECT 
    r.id,
    u.name AS user_name,
    c.name AS court_name,
    ci.name AS city_name
FROM reservations r
JOIN users u ON r.user_id = u.id
JOIN courts c ON r.court_id = c.id
JOIN cities ci ON c.city_id = ci.id
WHERE r.reservation_date = '2024-01-10';

역정규화:

-- ✅ 자주 조회되는 데이터 중복 저장
CREATE TABLE reservations_denormalized (
    id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),  -- 중복!
    court_id INT,
    court_name VARCHAR(50), -- 중복!
    city_name VARCHAR(50),  -- 중복!
    reservation_date DATE,
    reservation_time TIME,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (court_id) REFERENCES courts(id)
);

-- 빠른 조회 (JOIN 없음)
SELECT * FROM reservations_denormalized
WHERE reservation_date = '2024-01-10';

트레이드오프:

• 장점: 조회 빠름
• 단점: 저장 공간, 데이터 일관성 관리 필요

Court Alarm 선택:

• 마스터 데이터: 정규화 (users, courts)
• 로그/이력: 역정규화 (reservation_logs)
• 통계: 역정규화 (daily_statistics)

인덱스 (Index)

인덱스가 없으면

-- 인덱스 없음
SELECT * FROM users WHERE email = 'test@test.com';

-- Full Table Scan
1. 테이블 전체 읽기 (1백만 행)
2. 각 행의 email과 비교
3. 일치하는 행 반환

시간: 1,000ms (1초)

인덱스 구조: B+Tree

                  [50, 100]
                 /    |    \
               /      |      \
        [10,20,30]  [60,70] [110,120]
         / | | \      / \      / | \
       ...데이터...  ...데이터... ...데이터...

검색 과정:

SELECT * FROM users WHERE id = 65;

1. Root: 50 < 65 < 100 → 중간 노드
2. 중간: 60 < 65 < 70 → 리프 노드
3. 리프: id=65 찾음 → 실제 데이터 위치 반환

시간: 3ms (333배 빠름)
깊이: log(N) - 100만 행도 깊이 3~4

인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_email ON users(email);

-- 복합 인덱스
CREATE INDEX idx_court_date ON reservations(court_id, reservation_date);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 인덱스 확인
SHOW INDEX FROM users;

인덱스가 사용되는 경우

-- ✅ 인덱스 사용 (인덱스: email)
SELECT * FROM users WHERE email = 'test@test.com';

-- ✅ 인덱스 사용 (인덱스: court_id, reservation_date)
SELECT * FROM reservations 
WHERE court_id = 10 AND reservation_date = '2024-01-10';

-- ✅ 인덱스 일부 사용 (court_id만)
SELECT * FROM reservations WHERE court_id = 10;

-- ❌ 인덱스 미사용 (선행 컬럼 없음)
SELECT * FROM reservations WHERE reservation_date = '2024-01-10';

-- ❌ 인덱스 미사용 (함수 사용)
SELECT * FROM users WHERE LOWER(email) = 'test@test.com';

-- ❌ 인덱스 미사용 (LIKE '%test')
SELECT * FROM users WHERE email LIKE '%test.com';

-- ✅ 인덱스 사용 (LIKE 'test%')
SELECT * FROM users WHERE email LIKE 'test%';

실행 계획 확인

EXPLAIN SELECT * FROM reservations 
WHERE court_id = 10 AND reservation_date = '2024-01-10';

결과:

+----+-------------+--------------+-------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table        | type  | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+--------------+-------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | reservations | ref   | idx_court_date   | idx_court_date   | 7       | const |   10 | NULL  |
+----+-------------+--------------+-------+------------------+------------------+---------+-------+------+-------+

주요 필드:

• type:
  • const: PK, Unique로 단일 행 (최고)
  • ref: Non-Unique 인덱스로 여러 행
  • range: 범위 검색 (BETWEEN, <, >)
  • index: 인덱스 풀 스캔
  • ALL: 테이블 풀 스캔 (최악)
• rows: 예상 스캔 행 수 (적을수록 좋음)
• Extra:
  • Using index: 커버링 인덱스 (최고)
  • Using where: WHERE 절 필터링
  • Using filesort: 정렬 필요 (느림)
  • Using temporary: 임시 테이블 (느림)

Court Alarm 인덱스 전략

초기 (인덱스 없음):

-- 예약 조회 쿼리
SELECT * FROM reservations 
WHERE user_id = 1 
ORDER BY reservation_date DESC;

-- Full Table Scan
-- 100만 행 스캔 → 2,500ms

인덱스 추가:

-- 1. user_id 기준 조회가 많음
CREATE INDEX idx_user_id ON reservations(user_id);
-- 2,500ms → 150ms (16배 개선)

-- 2. 날짜 정렬도 빠르게
CREATE INDEX idx_user_date ON reservations(user_id, reservation_date);
-- 150ms → 5ms (30배 개선)

-- 3. 코트 + 날짜 + 시간 조회 (중복 예약 방지)
CREATE UNIQUE INDEX idx_court_datetime 
ON reservations(court_id, reservation_date, reservation_time);
-- 중복 체크: 1,000ms → 2ms (500배 개선)

커버링 인덱스:

-- 자주 조회되는 컬럼만 포함
CREATE INDEX idx_cover 
ON reservations(user_id, reservation_date, status);

SELECT user_id, reservation_date, status 
FROM reservations 
WHERE user_id = 1;

-- Extra: Using index
-- 테이블 접근 없이 인덱스만으로 처리 (초고속)

인덱스의 단점

1. 쓰기 성능 저하:

INSERT INTO reservations (user_id, court_id, ...) 
VALUES (1, 10, ...);

-- 인덱스 3개 있으면:
1. 테이블에 데이터 삽입
2. idx_user_id 업데이트
3. idx_court_datetime 업데이트
4. idx_user_date 업데이트

쓰기 시간: 1ms → 4ms (4배 증가)

2. 저장 공간:

-- 테이블: 1GB
-- 인덱스 3개: 각 300MB = 900MB
-- 총: 1.9GB

3. 메모리 사용:

-- 인덱스도 메모리에 캐시
-- Buffer Pool: 테이블 + 인덱스

트레이드오프:

• 읽기 많음 (90%) → 인덱스 많이
• 쓰기 많음 (50%+) → 인덱스 최소화

Court Alarm 전략:

• 핵심 인덱스만 (5개)
• 정기적으로 사용률 확인
• 안 쓰는 인덱스 삭제

-- 인덱스 사용 통계
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'court_alarm';

트랜잭션 격리와 락

락의 종류

1. Shared Lock (S-Lock, 공유 락)

-- Transaction A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- Shared Lock 획득

-- Transaction B
SELECT * FROM accounts WHERE id = 1;  -- OK (읽기 가능)

UPDATE accounts SET balance = 200000 WHERE id = 1;  
-- 대기... (Exclusive Lock 필요)

2. Exclusive Lock (X-Lock, 배타 락)

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = 200000 WHERE id = 1;
-- Exclusive Lock 획득

-- Transaction B
SELECT * FROM accounts WHERE id = 1;  -- OK (MVCC로 읽기)

UPDATE accounts SET balance = 300000 WHERE id = 1;  
-- 대기... (X-Lock 충돌)

락 호환성:

S-Lock X-Lock

S-Lock	✅ OK	❌ 대기
X-Lock	❌ 대기	❌ 대기

행 락 vs 테이블 락

-- ✅ 행 락 (Row Lock)
UPDATE accounts SET balance = 200000 WHERE id = 1;
-- id=1 행만 락
-- 다른 행은 동시 수정 가능

-- ❌ 테이블 락 (Table Lock)
LOCK TABLES accounts WRITE;
UPDATE accounts SET balance = 200000 WHERE id = 1;
UNLOCK TABLES;
-- 전체 테이블 락
-- 모든 다른 트랜잭션 대기

InnoDB는 기본적으로 행 락:

-- Transaction A
UPDATE accounts SET balance = 200000 WHERE id = 1;

-- Transaction B (동시 실행)
UPDATE accounts SET balance = 300000 WHERE id = 2;  -- OK!
-- id=1, id=2는 다른 행 → 동시 수정 가능

데드락 (Deadlock)

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = 100000 WHERE id = 1;
-- id=1 락 획득

-- Transaction B
START TRANSACTION;
UPDATE accounts SET balance = 200000 WHERE id = 2;
-- id=2 락 획득

-- Transaction A
UPDATE accounts SET balance = 150000 WHERE id = 2;
-- id=2 락 대기... (Transaction B가 가짐)

-- Transaction B
UPDATE accounts SET balance = 250000 WHERE id = 1;
-- id=1 락 대기... (Transaction A가 가짐)

-- 데드락!
-- MySQL이 자동 감지하고 한쪽 롤백
-- ERROR 1213 (40001): Deadlock found

데드락 방지:

// ✅ 항상 같은 순서로 락 획득
@Transactional
fun transfer(fromId: Long, toId: Long, amount: BigDecimal) {
    // ID 순서대로 정렬
    val (first, second) = if (fromId < toId) {
        fromId to toId
    } else {
        toId to fromId
    }
    
    // 순서대로 락 획득
    val account1 = accountRepository.findByIdForUpdate(first)
    val account2 = accountRepository.findByIdForUpdate(second)
    
    // 이체 로직
    if (fromId < toId) {
        account1.balance -= amount
        account2.balance += amount
    } else {
        account2.balance -= amount
        account1.balance += amount
    }
}

면접에서 이렇게 답하자

1단계: ACID 설명 (30초)

"ACID는 RDBMS의 트랜잭션 특성입니다. Atomicity는 All or Nothing, Consistency는 제약조건 유지, Isolation은 동시 트랜잭션 격리, Durability는 커밋 후 영구 저장입니다."

2단계: 격리 수준 (1분)

"격리 수준은 4단계입니다. READ UNCOMMITTED는 Dirty Read 허용, READ COMMITTED는 커밋된 것만 읽기, REPEATABLE READ는 스냅샷 읽기로 일관성 보장, SERIALIZABLE은 완전 격리입니다. MySQL InnoDB는 REPEATABLE READ가 기본이며 MVCC로 성능과 일관성을 모두 달성합니다."

3단계: 실무 경험 (2분)

"Court Alarm에서 디스크 손상으로 데이터베이스가 깨졌을 때, WAL과 Binary Log 덕분에 모든 데이터를 복구했습니다. 커밋된 트랜잭션은 Redo Log에 먼저 기록되므로, 백업에서 복원 후 Binary Log를 재생해서 단 한 건의 예약도 손실 없이 복구했습니다."

"인덱스 최적화로 예약 조회를 2,500ms에서 5ms로 500배 개선했습니다. user_id와 reservation_date의 복합 인덱스를 생성하고, 커버링 인덱스로 테이블 접근을 제거했습니다. 하지만 쓰기 성능 저하를 고려해 핵심 인덱스 5개만 유지하고 있습니다."

핵심 팁

ACID 구체적으로:

• ❌ "트랜잭션이 안전해요"
• ✅ "WAL로 Durability, MVCC로 Isolation, 제약조건으로 Consistency 보장"

숫자로 말하기:

• ❌ "인덱스로 빨라졌어요"
• ✅ "2,500ms → 5ms, 500배 개선, 복합 인덱스 + 커버링 인덱스 활용"

트레이드오프:

• 정규화 vs 역정규화
• 인덱스 개수 (읽기 vs 쓰기)
• 격리 수준 (일관성 vs 성능)

정리

RDBMS는 데이터 무결성과 일관성의 핵심이다.

ACID:

• Atomicity: All or Nothing
• Consistency: 제약조건 유지
• Isolation: MVCC, 격리 수준
• Durability: WAL, Redo Log

정규화:

• 1NF: 원자값
• 2NF: 부분 종속 제거
• 3NF: 이행 종속 제거
• 역정규화: 성능 vs 일관성

인덱스:

• B+Tree 구조
• 복합 인덱스 순서 중요
• 커버링 인덱스 최고 성능
• 쓰기 성능 트레이드오프

격리와 락:

• 4가지 격리 수준
• MVCC로 동시성
• 행 락 기본
• 데드락 방지 (순서)