데이터베이스 — 2강: 고급 SQL과 트랜잭션

고급 SQL — JOIN

JOIN의 유형:

INNER JOIN:
→ 두 테이블에 모두 존재하는 레코드만 반환
→ 가장 일반적인 JOIN

예시:
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

LEFT OUTER JOIN:
→ 왼쪽 테이블 모두 + 오른쪽 일치하는 것
→ 오른쪽에 없으면 NULL

RIGHT OUTER JOIN:
→ 오른쪽 테이블 모두 + 왼쪽 일치하는 것

FULL OUTER JOIN:
→ 양쪽 모두 반환, 일치 없으면 NULL

CROSS JOIN:
→ 두 테이블의 카테시안 곱 (모든 조합)
→ WHERE 없이 사용 시 행 수 폭발 주의

SELF JOIN:
→ 같은 테이블을 두 번 참조
→ 계층 구조 (상사-부하) 표현

다중 JOIN:
SELECT o.order_id, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

고급 SQL — 집계와 서브쿼리

집계 함수:
COUNT(), SUM(), AVG(), MAX(), MIN()

GROUP BY와 HAVING:
→ GROUP BY: 그룹화 기준
→ HAVING: 그룹 필터링 (WHERE는 행 필터)

예시:
SELECT dept_id, COUNT(*) as emp_count, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5
ORDER BY emp_count DESC;

서브쿼리 (Subquery):
→ SQL 안의 SQL

인라인 서브쿼리 (FROM 절):
SELECT dept_name, avg_salary
FROM (
  SELECT dept_id, AVG(salary) as avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_stats
JOIN departments ON dept_stats.dept_id = departments.id;

WHERE 절 서브쿼리:
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

EXISTS vs IN:
→ EXISTS: 존재 여부만 확인 (큰 데이터셋에 효율적)
→ IN: 값 목록과 비교

상관 서브쿼리:
→ 외부 쿼리 행에 따라 서브쿼리가 달라짐
→ 각 행마다 실행 → 성능 주의

윈도우 함수 (Window Functions)

윈도우 함수의 특성:
→ GROUP BY와 달리 행을 유지하면서 집계
→ OVER() 절 사용

기본 구문:
함수명() OVER (
  PARTITION BY 컬럼
  ORDER BY 컬럼
  ROWS BETWEEN ...
)

순위 함수:
→ ROW_NUMBER(): 순차 번호 (동점 없음)
→ RANK(): 동점 있으면 같은 순위, 다음 순위 건너뜀
→ DENSE_RANK(): 동점 있어도 순위 연속

예시:
SELECT name, salary, dept_id,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as dept_rank
FROM employees;

집계 윈도우:
SELECT name, salary,
  AVG(salary) OVER (PARTITION BY dept_id) as dept_avg,
  salary - AVG(salary) OVER (PARTITION BY dept_id) as diff_from_avg
FROM employees;

누적 합:
SELECT date, sales,
  SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM daily_sales;

트랜잭션과 ACID

트랜잭션(Transaction)의 정의:
→ 하나의 논리적 작업 단위
→ 완전히 완료되거나 전혀 실행 안 된 것처럼 처리

ACID 속성:

A — 원자성 (Atomicity):
→ 트랜잭션의 모든 연산이 완전히 수행되거나
  전혀 수행되지 않음
→ 이체: 출금 + 입금 모두 성공해야 커밋

C — 일관성 (Consistency):
→ 트랜잭션 전후 DB가 일관된 상태 유지
→ 무결성 제약 조건 항상 만족

I — 격리성 (Isolation):
→ 동시 트랜잭션이 서로 간섭하지 않음
→ 완료 전 중간 상태 다른 트랜잭션에 노출 안 됨

D — 지속성 (Durability):
→ 커밋된 트랜잭션 결과는 영구 저장
→ 시스템 장애 후에도 유지

트랜잭션 제어:
BEGIN TRANSACTION;
...SQL 문들...
COMMIT;   -- 또는 ROLLBACK;

잠금과 격리 수준

동시성 문제:

더티 읽기 (Dirty Read):
→ 커밋 안 된 데이터 읽기

비반복 읽기 (Non-repeatable Read):
→ 같은 쿼리 실행 시 다른 결과 (행 갱신)

팬텀 읽기 (Phantom Read):
→ 같은 쿼리 실행 시 행 수 달라짐 (행 삽입/삭제)

격리 수준 (Isolation Level):

READ UNCOMMITTED:
→ 더티 읽기 허용 (가장 낮음, 가장 빠름)

READ COMMITTED:
→ 커밋된 데이터만 읽음 (더티 읽기 방지)
→ PostgreSQL, Oracle 기본값

REPEATABLE READ:
→ 같은 쿼리는 같은 결과 (비반복 읽기 방지)
→ MySQL InnoDB 기본값

SERIALIZABLE:
→ 완전한 순차 실행처럼 동작 (가장 높음, 가장 느림)
→ 팬텀 읽기까지 방지

잠금 유형:
→ 공유 잠금(S Lock): 읽기 가능, 쓰기 불가
→ 배타 잠금(X Lock): 읽기·쓰기 모두 불가
→ 교착 상태(Deadlock): 상호 잠금 → DB가 자동 감지·해제

인덱스

인덱스(Index)의 목적:
→ 검색 속도 향상 (풀 스캔 방지)
→ B-Tree 구조 (기본)

B-Tree 인덱스:
→ 균형 트리 구조 (검색: O(log n))
→ 범위 검색, 정렬에 효율적
→ 가장 일반적 인덱스 유형

Hash 인덱스:
→ 등호(=) 검색에 O(1)
→ 범위 검색 불가
→ 메모리 테이블에 주로 사용

인덱스 생성:
CREATE INDEX idx_emp_name ON employees(last_name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_composite ON orders(customer_id, order_date);

인덱스 사용 고려사항:
→ 장점: 검색 속도 향상
→ 단점: 추가 저장 공간, INSERT/UPDATE/DELETE 느려짐
→ WHERE, JOIN, ORDER BY에 자주 쓰는 컬럼에 생성
→ 카디널리티 높은 컬럼 우선 (gender보다 email)

실행 계획 (EXPLAIN):
→ EXPLAIN SELECT ...
→ 쿼리 최적화기가 인덱스 사용 여부 확인
→ 풀 스캔(Seq Scan) 발생 시 인덱스 추가 검토

Q. JOIN vs 서브쿼리 중 어느 것이 더 빠른가요? A. 일반적으로 JOIN이 더 효율적입니다. 현대 데이터베이스는 JOIN 최적화가 잘 되어 있고, 쿼리 최적화기가 실행 계획을 최적화합니다. 서브쿼리는 특히 상관 서브쿼리의 경우 각 행마다 반복 실행되어 느릴 수 있습니다. 단, 서브쿼리가 더 읽기 쉬운 경우도 있어 성능과 가독성의 균형을 고려하세요. EXPLAIN으로 실제 실행 계획을 확인하는 습관이 중요합니다.

Q. 인덱스를 많이 만들수록 좋은가요? A. 아닙니다. 인덱스는 읽기 성능을 높이지만 쓰기(INSERT/UPDATE/DELETE) 성능을 낮춥니다. 인덱스 수가 많아지면 데이터 변경 시 모든 인덱스를 갱신해야 합니다. 실제로 자주 사용되는 WHERE, JOIN 조건의 컬럼에만 선택적으로 인덱스를 만들고, 사용하지 않는 인덱스는 제거하는 것이 좋습니다.