C
Database 목록
이전4 / 6다음
Database/트랜잭션_인덱스/Lesson 04

트랜잭션 + 인덱스 — ACID·격리수준·MVCC·B+Tree

60분·theory

트랜잭션 + 인덱스 — ACID·격리수준·MVCC·B+Tree

🎯 이 lesson 을 읽고 나면

이 lesson 을 다 읽고 나면 아래 3가지를 자신 있게 할 수 있습니다.

  • ▸✅ ACID 4성질 + 격리 수준 4단계 (RU/RC/RR/Serializable)
  • ▸✅ B-Tree 인덱스 동작 + 복합 인덱스의 Leftmost Prefix 규칙
  • ▸✅ 낙관적 락 vs 비관적 락 선택 기준

학습 목표를 체크리스트로 두고 다 답할 수 있게 되면 lesson 을 닫으세요.

트랜잭션이 뭐냐 — *전부 성공 아니면 전부 취소*

핵심 한 줄

트랜잭션 (Transaction) = 논리적으로 하나로 묶인 작업 단위. 안에 있는 모든 작업이 성공해야 진짜 반영되고, 하나라도 실패하면 전부 취소 됩니다.

왜 필요한가 — 송금 사례

내가 친구에게 10만원 송금 한다고 해봅시다. DB 가 해야 할 일:

1. 내 계좌에서 10만원 차감
2. 친구 계좌에 10만원 추가

만약 1번이 성공한 후 시스템 장애로 2번이 실패하면? 내 돈 10만원이 허공으로 사라집니다. 친구는 못 받았고, 나는 잃었죠.

트랜잭션이 있으면 둘 다 성공 해야 commit (확정) 되고, 하나라도 실패 하면 rollback (되돌리기) — 마치 아무 일도 없었던 것처럼 됩니다.

sql
BEGIN;
  UPDATE accounts SET balance = balance - 100000 WHERE id = 1;  -- 내 차감
  UPDATE accounts SET balance = balance + 100000 WHERE id = 2;  -- 친구 입금
COMMIT;
-- 중간 에러 시: ROLLBACK;

ACID — 트랜잭션의 4가지 약속

이름은 외워두는 게 좋습니다. 면접 단골입니다.

A — Atomicity (원자성): 전부 성공 OR 전부 실패. 위 송금 예시. 부분 성공은 없습니다.

C — Consistency (일관성): 트랜잭션 전후로 DB 의 규칙 (외래키·UNIQUE·CHECK) 이 깨지지 않습니다. 예를 들어 잔액이 음수가 될 수 없다 같은 제약이 항상 지켜집니다.

I — Isolation (격리성): 동시에 실행되는 다른 트랜잭션끼리 서로 영향 안 받음. 두 사람이 동시에 같은 상품을 구매해도, 재고가 정확히 차감됩니다.

D — Durability (영속성): commit 된 데이터는 전원이 꺼져도 사라지지 않음. 디스크에 확실히 저장 된 후에만 성공으로 응답.

이 4가지가 있어야 돈을 다루는 시스템 을 만들 수 있습니다. 결제·재고·예약 모두 ACID 없으면 위험합니다.

격리 수준 — 얼마나 엄격하게 격리할까

ACID 의 I (격리성) 는 완벽하게 격리 가 가장 안전하지만 느립니다. 그래서 4단계로 얼마나 엄격할지 선택할 수 있습니다.

  • ▸READ UNCOMMITTED: 거의 격리 X. 남이 아직 commit 안 한 데이터도 보임. 거의 안 씀.
  • ▸READ COMMITTED: PostgreSQL·Oracle 기본. commit 된 것만 보임. 가장 흔함.
  • ▸REPEATABLE READ: MySQL 기본. 한 트랜잭션 안에서 같은 쿼리·같은 결과 보장.
  • ▸SERIALIZABLE: 가장 엄격. 트랜잭션들이 순차 실행된 것처럼 보장. 안전하지만 느림.

수준이 올라갈수록 안전 ↑ 동시성 ↓. 일반 웹 서비스는 READ COMMITTED 면 충분. 은행·결제 같은 엄격한 일관성 필요하면 SERIALIZABLE 까지 올립니다.

MVCC — 현대 DB 의 동시성 비밀

옛 DB 는 읽기와 쓰기가 서로 막았습니다. 누가 행을 쓰면 다른 사람은 읽기조차 못 했죠.

현대 PostgreSQL·Oracle·MySQL InnoDB 는 MVCC (Multi-Version Concurrency Control) 를 씁니다. 각 행에 여러 버전 을 둬서:

  • ▸쓰는 사람 → 새 버전 추가
  • ▸읽는 사람 → 옛 버전 조회 (변경 무관)

서로 안 막습니다. 동시성이 폭발적으로 향상되죠. 다만 옛 버전이 쌓이면 디스크 낭비 → autovacuum 같은 백그라운드 청소 작업이 필요합니다.

흔한 함정

1. 트랜잭션을 너무 길게: BEGIN 후 10초간 다른 작업을 하면, 그 동안 락이 잡힌 채 다른 사람이 대기. 트랜잭션은 짧게.

2. 트랜잭션 밖에서 commit: Spring @Transactional 을 내부 호출 에 붙이면 적용 안 됨 (프록시 한계). public 메서드 외부에서 호출돼야 합니다.

3. 무한 락 대기: 데드락이나 느린 쿼리로 영원히 기다림. SET LOCAL lock_timeout = '3s'; 같은 타임아웃 필수.

한 번 정리

트랜잭션은 돈 다루는 시스템의 기본기. ACID 4가지를 알고, 격리 수준은 READ COMMITTED 가 기본, MVCC 가 동시성을 가능하게 한다는 정도가 최소 지식. 실제 코드는 Spring @Transactional 한 줄로 끝나지만, 원리를 알고 쓰는 것 과 모르고 쓰는 것 은 디버깅에서 큰 차이가 납니다.

인덱스 — *책의 색인* 같은 것

인덱스가 뭐냐

도서관에서 책 한 권을 찾을 때 서가를 처음부터 끝까지 뒤지면? 만 권이면 만 번. 색인 카드를 보면? 한 번에 위치를 찾습니다.

DB 인덱스 도 같은 원리입니다. 테이블의 어떤 컬럼에 인덱스를 만들어 두면, 그 컬럼으로 검색하는 속도가 극적으로 빨라집니다. 100만 행 검색이 0.001초 vs 10초 차이 — 1만배.

B+Tree — 가장 흔한 인덱스 구조

대부분의 DB 가 B+Tree 라는 자료구조를 씁니다. 균형 트리 — 어떤 검색이든 같은 깊이를 거치므로 일관된 O(log n) 시간.

code
        [50]              ← 루트
       /    \
    [25]    [75]          ← 중간 노드
    / \    / \
  ...  ...  ...  ...      ← 잎 노드 (실제 데이터 포인터)

잎 노드들은 서로 연결 돼 있어서, 범위 검색 (예: 25 ~ 75) 도 빠릅니다. 처음 위치만 찾고 옆으로 쭉 따라가면 끝.

언제 인덱스를 추가할까

  • ▸✅ WHERE 절에 자주 등장 — WHERE email = ?
  • ▸✅ JOIN 의 조건 — ON orders.user_id = users.id
  • ▸✅ ORDER BY 컬럼 — 정렬을 이미 인덱스가 해줌
  • ▸❌ 쓰기가 매우 잦은 테이블 — 인덱스도 매번 업데이트 (느려짐)
  • ▸❌ 카디널리티 낮음 (예: 성별) — 인덱스 효과 미미

복합 인덱스 — 순서가 중요하다

여러 컬럼을 함께 인덱스로:

sql
CREATE INDEX idx_orders ON orders(user_id, created_at);

이 인덱스는 왼쪽부터 차례로 매칭됩니다 (Leftmost Prefix).

  • ▸WHERE user_id = ? ✅ 사용
  • ▸WHERE user_id = ? AND created_at > ? ✅ 사용
  • ▸WHERE created_at > ? ❌ 안 씀 (user_id 빠짐)

전화번호부를 생각하면 됩니다. 성·이름 순으로 정렬돼 있어서 성을 알아야 빠릅니다. 이름만 알고는 처음부터 뒤져야 하죠.

인덱스가 안 타는 5가지 함정

이걸 모르고 쓰면 인덱스 만들어 놓고도 효과 X.

1. 함수 사용 — WHERE UPPER(email) = '[email protected]'. 인덱스는 원본 값 으로 정렬돼 있으니 함수 결과를 모릅니다.

2. 좌측 와일드카드 — WHERE name LIKE '%홍%'. 앞부터 매칭이 안 되니 인덱스 무용지물. LIKE '홍%' 는 OK.

3. 형 변환 — WHERE user_id = '42' (문자열) 인데 컬럼이 숫자. 암시적 변환이 일어나면 인덱스 X.

4. NOT·!= — 부정 조건 은 보통 인덱스 못 씀. 정의상 대부분 일치하니까.

5. OR 묶음 — WHERE a = 1 OR b = 2. 둘 다 인덱스 있어도 한쪽만 쓰거나 둘 다 안 쓰는 경우 잦음. UNION 으로 분리하면 됨.

> 💡 EXPLAIN ANALYZE 로 확인하는 습관이 쿼리 성능 디버깅의 출발점 이다.

EXPLAIN — 쿼리의 실행 계획 보기

DB 가 어떻게 쿼리를 실행할지 미리 보여줍니다.

sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

결과에서 봐야 할 것:

  • ▸Seq Scan = 전체 테이블 스캔. 큰 테이블에선 위험 신호.
  • ▸Index Scan = 인덱스 사용. ✅
  • ▸rows=100 추정 vs actual rows=10000 = 통계 오차. ANALYZE 테이블; 로 갱신 필요.

EXPLAIN 결과를 읽을 줄 아는 능력 이 SQL 성능 디버깅의 알파 입니다.

N+1 문제 — ORM 의 클래식 함정

python
users = User.query.all()           # 1번 쿼리
for u in users:
    print(u.orders.count())        # 사용자마다 1번 → N번 추가

사용자 100명이면 101번 쿼리 발생. 페이지가 수십 배 느려집니다.

해결: ORM 의 eager loading 기능 사용:

  • ▸JPA: @Query + JOIN FETCH
  • ▸Django: prefetch_related
  • ▸SQLAlchemy: joinedload
  • ▸Prisma: include

한 번의 JOIN 쿼리로 모든 데이터를 가져와 N+1 → 1 으로.

한 번 정리

인덱스는 조회 속도의 핵심. 단, 함부로 추가 하면 쓰기 성능 ↓. EXPLAIN 으로 확인 하면서 꼭 필요한 곳 에만. N+1 같은 ORM 함정도 같은 관점에서 실제 쿼리 를 봐야 발견됩니다.

💾 실전 데모 1 — 인덱스 효과 (EXPLAIN ANALYZE)

인덱스 *전후* 의 실행 계획 차이를 확인하세요. ▶ 실행 으로 결과 확인.
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: users length: 411 chars 
-- 100만 행 테이블에서 email 로 검색

-- ❌ 인덱스 없음 (Seq Scan)
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';
-- Seq Scan on users  (cost=0..18334)
-- Execution Time: 850 ms

-- ✅ 인덱스 추가 후
CREATE INDEX idx_users_email ON users(email);

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';
-- Index Scan using idx_users_email  (cost=0..8)
-- Execution Time: 0.5 ms

-- → 1700배 빨라짐
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
21ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 64ms
📤 출력
✓ 2 rows 3 cols 64ms
stageplantime_ms
인덱스 XSeq Scan (1,000,000 rows)850
인덱스 OIndex Scan (1 row)0.5
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.

💾 실전 데모 2 — 트랜잭션·롤백 (송금 시나리오)

잔액 부족 시 트랜잭션이 *자동 롤백* 되는 모습.
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: accounts length: 312 chars 
-- 초기: 계좌 1 = 50,000원, 계좌 2 = 0원

BEGIN;

-- 차감
UPDATE accounts SET balance = balance - 100000 WHERE id = 1;

-- 잔액 음수 검증 (CHECK 제약)
-- balance >= 0 위반 → 에러
-- → 자동 ROLLBACK

-- 만약 통과했다면 입금
UPDATE accounts SET balance = balance + 100000 WHERE id = 2;

COMMIT;

-- 결과 확인
SELECT * FROM accounts WHERE id IN (1, 2);
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
21ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 64ms
📤 출력
✓ 2 rows 3 cols 64ms
idbalancestatus
150000(롤백됨, 원상복구)
20(롤백됨, 원상복구)
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.

💾 실전 데모 3 — N+1 vs JOIN FETCH (성능 차이)

사용자 100명의 주문 수 조회 — N+1 vs 단일 JOIN.
📥 입력 DB 서버로 전송되는 쿼리
SELECT FROM: users length: 276 chars 
-- ❌ N+1 패턴
-- 1번: SELECT * FROM users;
-- N번: SELECT COUNT(*) FROM orders WHERE user_id = ?   (100번)
-- → 101 쿼리, 5초

-- ✅ 단일 JOIN
SELECT
  u.id,
  u.name,
  COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- → 1 쿼리, 30ms
⚙️ 처리 단계 DB 엔진 내부 동작
📋
1. 파싱
SQL 문법 분석 → AST 생성
10ms
🧠
2. 옵티마이저
인덱스·조인 순서 최적화 (통계 기반)
25ms
⚙️
3. 실행
Storage Engine이 데이터 읽기
21ms
📊
4. 결과 반환
버퍼에 모아서 클라이언트에 전달
8ms
총 64ms
📤 출력
✓ 2 rows 3 cols 64ms
patternqueriestime
N+1 (옛 ORM 기본)1015,000 ms
JOIN FETCH130 ms
💡 학습 포인트: 실제 DB는 파싱 → 옵티마이저 → 실행 → 반환 4단계를 거칩니다. 인덱스가 있으면 '옵티마이저' 단계가 수천배 빨라집니다.
🌐 본인 환경(PostgreSQL·MySQL 등)에서 직접 실행해 ms 차이를 체감해보세요.
💻 📌 자주 쓰는 인덱스·트랜잭션 코드 (참고용) 
-- ============================================
-- 인덱스 생성·관리
-- ============================================
CREATE INDEX idx_users_email ON users(email);              -- 단일
CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);  -- 유니크
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);  -- 복합
CREATE INDEX idx_orders_paid ON orders(user_id) WHERE status='PAID';    -- 부분
CREATE INDEX CONCURRENTLY idx_new ON users(email);         -- 락 없이 (운영 환경 필수)

DROP INDEX CONCURRENTLY idx_old;

-- 인덱스 사용 통계 (안 쓰는 인덱스 찾기)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname='public' AND idx_scan = 0;

-- ============================================
-- 트랜잭션
-- ============================================
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 또는 ROLLBACK;

-- 격리 수준 명시
BEGIN ISOLATION LEVEL READ COMMITTED;
-- ...
COMMIT;

-- 타임아웃 (영원히 안 기다리도록)
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';

인덱스 설계 실전 — 카디널리티 · 복합 인덱스

카디널리티 (Cardinality) — 값의 종류 수

  • ▸높은 카디널리티: email (거의 모두 다름). 인덱스 효과 폭발적.
  • ▸낮은 카디널리티: gender (M/F 둘뿐). 인덱스 효과 미미. 안 만드는 게 나음.
  • ▸중간: country (수백 개). 케이스 바이 케이스.

카디널리티가 낮은 컬럼에 인덱스를 만들면 디스크만 차지하고 효과는 없습니다.

인덱스 설계 5가지 원칙

1. WHERE 절에 자주 쓰이는 컬럼

sql
SELECT * FROM users WHERE email = ?;
→ CREATE INDEX idx_email ON users(email);

2. JOIN 키 (FK 는 보통 자동 생성)

sql
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
→ orders.user_id 에 인덱스 (FK 자동)

3. ORDER BY · GROUP BY 컬럼

인덱스가 있으면 정렬 단계 생략 가능.

4. 복합 인덱스 — 순서가 중요

sql
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

가장 자주 쓰이는 컬럼을 앞에. 위 인덱스로 활용 가능한 쿼리:

  • ▸WHERE user_id = ? ✅
  • ▸WHERE user_id = ? AND status = ? ✅
  • ▸WHERE user_id = ? AND status = ? AND created_at > ? ✅
  • ▸WHERE status = ? ❌ (앞 컬럼 빼먹음)
  • ▸WHERE created_at > ? ❌

맨 왼쪽부터 순서대로 조건 이 있어야 인덱스 사용. 이걸 Leftmost Prefix 라고 부릅니다.

5. 너무 많이 만들지 마라

인덱스가 많으면 INSERT/UPDATE/DELETE 가 느려짐 — 모든 인덱스를 갱신해야 하니까. 읽기 빈도와 쓰기 빈도의 균형.

안 좋은 인덱스 5가지

1. 카디널리티 낮은 컬럼 — is_deleted, gender 단독
2. 너무 긴 컬럼 — TEXT 직접 인덱싱 (Prefix Index 또는 Full-text 검토)
3. 자주 안 쓰는 컬럼 — 만들기 전 EXPLAIN 으로 검증
4. 함수 인덱스 미사용 — WHERE YEAR(date) = 2026 → 인덱스 안 탐. WHERE date >= '2026-01-01' 로 바꾸기.
5. 중복 인덱스 — (a) + (a, b) 면 (a) 는 중복.

인덱스 통계 확인

sql
SHOW INDEX FROM users;
SELECT * FROM information_schema.statistics WHERE table_name = 'users';

Cardinality 컬럼이 총 행 수에 가까울수록 좋은 인덱스.

🤖 AI 에게 이렇게 요청해보세요

이 lesson 의 개념을 알면 AI 에게 구체적으로 지시할 수 있습니다. 막연한 "고쳐줘" 가 아니라 어휘를 가진 요청 — 그게 토큰 절약의 출발점입니다.

  • ▸"이 쿼리 (WHERE user_id + status + created_at) 에 맞는 복합 인덱스 만들어줘"
  • ▸"이 작업을 격리수준 READ_COMMITTED 로 트랜잭션 감싸줘"
  • ▸"데드락 가능성이 있는데 INDEX 와 락 순서 관점에서 분석해줘"

왜 이게 토큰을 줄이나

개념을 모를 땐 AI 답변을 받고도 "그게 뭐예요?" 를 다시 물어야 합니다. 그 "다시 물음" 이 토큰을 잡아먹습니다. 개념 한 번 익혀두면 대화가 한 번에 끝납니다.

먼저 읽으면 좋은 개념: SQL 실습 — 테이블 만들고 데이터 넣고 조회까지
다음 추천: NoSQL 기초 — Redis · MongoDB 언제 쓰나
이전목록다음
트랜잭션 + 인덱스 — ACID·격리수준·MVCC·B+Tree - Database