데이터베이스: 실행계획(EXPLAIN) 읽는 법과 쿼리 튜닝, 반정규화까지

지난 글에서 인덱스가 성능에 미치는 영향을 살펴봤죠. 이번엔 한 걸음 더 들어가서, 실행계획(EXPLAIN)을 직접 읽는 법과 그걸 바탕으로 쿼리를 튜닝하는 방법, 그리고 정규화만으로는 부족할 때 쓰는 반정규화·파티셔닝까지 정리해볼게요.

B-Tree 인덱스, 조금 더 깊이 보기

B-Tree 인덱스는 정렬된 키 값을 담은 트리이고, 리프 노드(leaf node)들이 서로 연결 리스트로 이어져 있어요. 이 구조가 중요한 이유는 조건 종류에 따라 동작이 달라지기 때문이에요.

동등 조건(=): 루트에서 리프까지 O(log N)으로 내려가서 값을 찾아요.
범위 조건(BETWEEN, >, <, ORDER BY): 시작점만 O(log N)으로 찾고, 그다음은 연결된 리프 노드를 옆으로 쭉 훑기만 하면 돼요. → 그래서 B-Tree는 정렬/범위 조회에 특히 강해요.

이게 해시 인덱스(Hash Index)와의 결정적 차이예요.

구분	B-Tree Index	Hash Index
동등 비교 (=)	O(log N)	O(1) — 더 빠름
범위 조회 (>, BETWEEN)	가능 (리프를 옆으로 훑음)	불가능
정렬(ORDER BY) 활용	가능	불가능 (해시값엔 순서 개념이 없음)

해시는 동등 비교만 놓고 보면 더 빠르지만, 범위 조회나 정렬에는 전혀 쓸 수 없어서 대부분의 RDBMS는 기본 인덱스로 B-Tree를 써요.

실행계획에서 자주 보는 스캔 종류

PostgreSQL 기준으로, 실행계획에서 가장 자주 보는 스캔 방식 네 가지예요.

스캔 종류	동작 방식
Seq Scan (Full Table Scan)	테이블을 처음부터 끝까지 순서대로 다 읽어요. 인덱스를 안 쓰거나 못 쓸 때, 또는 조건에 맞는 행이 너무 많아 오히려 이게 더 빠를 때 선택돼요.
Index Scan	인덱스로 조건에 맞는 행의 위치를 찾은 뒤, 그 위치로 가서 실제 테이블(heap)의 행을 읽어요.
Index Only Scan	필요한 컬럼이 전부 인덱스 안에 있어서, 테이블 본체를 아예 안 읽고 인덱스만으로 결과를 반환해요. (지난 글에서 본 커버링 인덱스가 활용될 때 나오는 스캔이에요.)
Bitmap Heap Scan	인덱스로 조건에 맞는 행들의 위치 목록(비트맵)을 먼저 만든 다음, 그 위치들을 물리적 순서대로 한 번에 모아 읽어요. Index Scan처럼 행마다 왔다갔다 하지 않아서 random I/O를 줄여줘요.

핵심은 “인덱스를 탄다고 무조건 빠른 게 아니다”라는 거예요. 조건에 맞는 행이 테이블의 대부분(대략 10~20% 이상)이면, 옵티마이저는 오히려 Seq Scan을 선택해요 — 인덱스를 거쳐 테이블을 왔다갔다하는 것보다 그냥 순서대로 쭉 읽는 게 더 빠르기 때문이에요. 이게 카디널리티(선택도)가 인덱스 설계에서 중요한 이유예요.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN은 계획만 보여주고, EXPLAIN ANALYZE는 실제로 쿼리를 실행해서 진짜 수치까지 함께 보여줘요. PostgreSQL 기준 예시예요.

EXPLAIN ANALYZE
SELECT * FROM enrollment WHERE student_id = 1001;

Index Scan using idx_enrollment_student_id on enrollment
  (cost=0.29..8.31 rows=3 width=24)
  (actual time=0.015..0.017 rows=3 loops=1)
  Index Cond: (student_id = 1001)
Planning Time: 0.089 ms
Execution Time: 0.032 ms

읽는 순서와 의미

Index Scan using idx_enrollment_student_id on enrollment: 어떤 스캔 방식으로, 어떤 인덱스를 써서, 어떤 테이블에 접근했는지예요.
cost=0.29..8.31: 시작 비용..총 비용이에요. 실제 시간(ms)이 아니라 옵티마이저 내부 단위의 상대적 비용 추정치예요.
rows=3: 옵티마이저가 예상한 반환 행 수예요.
width=24: 행 하나의 평균 크기(바이트)예요.
actual time=0.015..0.017: 실제로 첫 행이 나오기까지 걸린 시간과, 전체가 끝나기까지 걸린 시간(ms)이에요.
rows=3 (actual 쪽): 실제로 반환된 행 수예요.
loops=1: 이 노드가 몇 번 실행됐는지예요. (JOIN에서 바깥 루프 수에 따라 여러 번 반복될 수 있어요 — 바로 아래에서 볼게요.)
Index Cond: 인덱스를 탈 때 실제로 사용된 조건이에요.
Planning Time / Execution Time: 계획을 세우는 데 걸린 시간 / 실제로 쿼리를 실행하는 데 걸린 시간이에요.

가장 중요한 튜닝 신호는 예상(rows)과 실제(actual rows)의 차이예요. 이 차이가 크면 위험 신호예요 — 옵티마이저가 잘못된 통계 정보를 바탕으로 계획을 짰다는 뜻이라, 최악의 경우 좋은 인덱스가 있어도 옵티마이저가 안 쓰거나 JOIN 순서를 잘못 잡을 수 있어요. (해결: ANALYZE table_name;으로 통계를 갱신해요.)

JOIN이 있을 때 계획 읽기

EXPLAIN ANALYZE
SELECT s.name, e.course_id
FROM student s
JOIN enrollment e ON s.student_id = e.student_id
WHERE s.grade = 2;

Nested Loop  (cost=0.29..16.58 rows=5) (actual time=0.02..0.05 rows=5 loops=1)
  -> Index Scan using idx_student_grade on student s
       (actual rows=2 loops=1)
  -> Index Scan using idx_enrollment_student_id on enrollment e
       Index Cond: (student_id = s.student_id)
       (actual rows=2 loops=2)   -- s가 2행이라 이 노드가 2번 반복됨

계획 트리는 들여쓰기로 자식-부모 관계를 나타내요. 들여쓰여진 -> 줄이 먼저 실행돼서 그 결과를 부모 노드가 받아 써요. 이 예시에서는 먼저 student에서 grade = 2인 2건을 찾고(loops=1), 그 2건 각각에 대해 enrollment를 검색하는 과정을 반복해요(loops=2) — 이게 아래에서 볼 Nested Loop의 동작 방식이에요.

JOIN 알고리즘 3가지

알고리즘	동작 방식	유리한 상황
Nested Loop	바깥 테이블의 각 행마다 안쪽 테이블을 검색해요 (바깥 행 수만큼 `loops` 반복)	바깥 테이블이 작고, 안쪽 테이블에 조인 키 인덱스가 있을 때
Hash Join	한쪽 테이블(보통 작은 쪽)로 해시 테이블을 메모리에 만든 뒤, 다른 쪽을 훑으며 해시로 매칭해요	동등 조건(=) JOIN이고, 인덱스가 없는 큰 테이블끼리 조인할 때
Merge Join	양쪽 테이블이 이미 조인 키로 정렬돼 있으면, 두 리스트를 처음부터 동시에 훑으며 매칭해요	데이터가 이미 정렬돼 있거나, 정렬용 인덱스가 있을 때

SARGable하게 쿼리 쓰기

SARGable(Search ARGument ABLE)은 “인덱스를 탈 수 있는 형태로 조건을 쓴다”는 뜻이에요. 컬럼에 함수나 연산을 걸면 인덱스를 못 타요.

-- ❌ 인덱스 못 탐 — YEAR() 함수가 컬럼을 감쌈
WHERE YEAR(created_at) = 2026

-- ✅ 인덱스 탐 — 컬럼 그대로, 범위로 표현
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- ❌ 앞쪽에 와일드카드 — 인덱스 못 탐 (정렬 구조를 활용 못함)
WHERE name LIKE '%철수'

-- ✅ 앞쪽이 고정 — 인덱스 탐
WHERE name LIKE '김%'

암묵적 타입 변환도 주의해야 해요. 컬럼이 VARCHAR인데 숫자로 비교하면(WHERE student_id = 1001인데 컬럼이 문자열) DB가 내부적으로 형변환을 하면서 인덱스를 못 타는 경우가 있어요.

필요한 컬럼만 SELECT

SELECT *는 커버링 인덱스(Index Only Scan) 기회를 날려요. 필요한 컬럼만 명시하면, 인덱스에 그 컬럼들이 다 포함돼 있을 때 테이블 본체를 아예 안 읽고 끝낼 수 있는 경우가 늘어나요.

복합 인덱스 컬럼 순서 — 최좌측 접두 규칙 복습

지난 글에서 본 최좌측 접두 규칙을 실전 쿼리 튜닝 관점에서 다시 보면, 등호(=) 조건 컬럼을 앞에, 범위(>, BETWEEN) 조건 컬럼을 뒤에 놓는 게 일반적으로 유리해요.

-- WHERE student_id = ? AND enrolled_at > ? 에 최적
CREATE INDEX idx ON enrollment(student_id, enrolled_at);

student_id로 먼저 좁혀서 정확한 지점을 찾고, 그다음 정렬된 enrolled_at 순서를 따라 범위만큼 훑으면 되기 때문이에요. 순서를 반대로 하면(enrolled_at, student_id) 범위 조건이 앞에 와서 정렬 구조를 제대로 활용하지 못해요.

N+1 문제

ORM을 쓸 때 흔한 함정이에요. 목록을 가져온 뒤 각 항목마다 추가 쿼리를 날리는 패턴이에요.

SELECT * FROM student;                          -- 1번

SELECT * FROM enrollment WHERE student_id = ?;   -- 학생 수만큼 N번 반복

JOIN이나 IN (...) 배치 조회로 1~2번의 쿼리로 합치는 게 정석이에요. 실행계획만 봐서는 이 문제가 안 보이고, 애플리케이션 로그나 쿼리 카운트로 잡아야 하는 경우가 많다는 점도 면접에서 종종 나와요.

LIMIT + 정렬 조합 주의

ORDER BY ... LIMIT N은 정렬 기준 컬럼에 인덱스가 있으면 전체를 정렬하지 않고 필요한 만큼만 읽고 멈출 수 있어요. 인덱스가 없으면 전체를 정렬한 뒤 앞부분만 자르는 비효율이 생겨요.

통계 최신화

데이터가 크게 바뀌었는데 통계(ANALYZE)를 안 갱신하면, 옵티마이저가 옛날 분포를 기준으로 잘못된 계획(예상 rows가 실제와 크게 다름)을 세워요. 배치 삽입/삭제 후에는 통계 갱신을 습관화하는 게 좋아요.

반정규화 — 조회 성능을 위해 무결성 일부를 포기하기

**반정규화(Denormalization)**는 정규화된(중복 없이 잘게 쪼갠) 테이블 구조를, 조회 성능을 높이기 위해 일부러 중복을 허용하며 다시 합치거나 요약 데이터를 추가하는 설계 기법이에요.

정규화가 “데이터 무결성”을 위한 것이라면, 반정규화는 “조회 속도”를 위해 그 무결성 일부를 의도적으로 포기하는 트레이드오프예요. 무작정 정규화를 안 하는 것과는 달라요 — 반정규화는 정규화를 제대로 한 뒤에, 성능 병목이 실측으로 확인된 지점에 국소적으로 적용하는 것이 정석이에요.

왜 필요한가 — 앞서 배운 정규화의 대가

앞서 3NF까지 쪼갠 예시를 다시 보면 이래요.

[student]        [course]         [enrollment]
student_id (PK)  course_id (PK)   student_id (FK)
name              course_name      course_id (FK)
                  professor

“학생 이름과 수강 과목 이름을 함께 보여주는 목록”을 만들려면 매번 3개 테이블을 JOIN해야 해요. 트래픽이 많은 조회(예: 메인 화면에 매번 뜨는 리스트)에서 이 JOIN 비용이 누적되면 병목이 돼요. 반정규화는 이 JOIN을 줄이자는 아이디어예요.

대표적인 반정규화 기법

1. 컬럼 중복 저장 (JOIN 없애기)

-- enrollment 테이블에 student의 name을 미리 복사해둠
CREATE TABLE enrollment (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  student_name VARCHAR(50), -- 반정규화: student 테이블과 중복
  course_id INT
);

학생 이름이 바뀌면 enrollment의 모든 관련 행도 같이 갱신해줘야 해요 (갱신 이상의 위험을 다시 감수하는 거예요).

2. 요약/집계 컬럼 추가 (계산 비용 미리 지불)

-- 매번 COUNT(*)로 집계하지 않고 컬럼에 캐시
ALTER TABLE course ADD COLUMN enrolled_count INT DEFAULT 0;

수강신청/취소 시점에 이 컬럼을 함께 갱신하면, 조회할 땐 집계 쿼리 없이 컬럼 하나만 읽으면 돼요. 대신 쓰기 시점에 추가 작업과 정합성 관리 책임이 생겨요 (트랜잭션으로 함께 묶어야 해요).

3. 파생 테이블 / 요약 테이블 (별도 테이블로 분리)

자주 쓰는 복잡한 JOIN·집계 결과를 별도 테이블에 주기적으로(또는 트리거로) 미리 만들어둬요. 원본 테이블은 정규화 상태를 유지하고, 조회 전용 테이블만 반정규화하는 방식이라 비교적 안전해요.

4. 테이블 병합

1:1 관계이거나 거의 항상 함께 조회되는 두 테이블을 아예 하나로 합쳐요. (예: user와 user_profile을 매번 JOIN해서 쓴다면 처음부터 합치는 것도 고려해요.)

트레이드오프 정리

	정규화	반정규화
데이터 중복	없음 (최소화)	의도적으로 허용
쓰기 비용	상대적으로 단순함 (한 곳만 수정)	여러 곳(중복 컬럼)을 함께 갱신해야 해서 복잡함
읽기 성능	JOIN이 많아 상대적으로 느릴 수 있음	JOIN이 줄어 빠름
정합성 관리	DB 제약조건으로 비교적 쉽게 보장	애플리케이션/트랜잭션/트리거로 직접 관리해야 함
적합한 상황	쓰기가 잦고 정합성이 중요한 도메인	읽기가 압도적으로 많고 조회 성능이 병목인 경우

실무에서의 접근 순서 (면접에서도 잘 먹히는 논리)

먼저 정규화된 구조로 설계해요 (데이터 정합성을 기본값으로).
실제 트래픽에서 실행계획/모니터링으로 병목을 측정해요 (추측으로 반정규화하지 않아요).
병목이 확인된 특정 쿼리·테이블에만 국소적으로 반정규화를 적용해요.
반정규화로 생긴 중복은 트랜잭션, 트리거, 배치 동기화 등으로 정합성을 관리할 계획을 함께 세워요.

“정규화는 무결성을 위해 중복을 없애고, 반정규화는 측정된 성능 병목을 해결하기 위해 그 중복을 다시 국소적으로 허용하는 트레이드오프다.”

파티셔닝 — 테이블을 물리적으로 쪼개기

**파티셔닝(Partitioning)**은 하나의 논리적 테이블을 여러 개의 물리적 조각(파티션)으로 나눠 저장하는 기법이에요. 사용하는 입장에서는 여전히 테이블 하나처럼 보이지만, 내부적으로는 조건에 따라 여러 조각에 나뉘어 저장돼요.

테이블이 너무 커지면 인덱스도 함께 커지고, 백업·삭제·스캔 비용이 같이 늘어나요. 파티셔닝을 해두면 조건에 맞는 파티션만 골라서 훑을 수 있는데, 이걸 파티션 프루닝(Partition Pruning)이라고 해요 — 나머지 파티션은 아예 쳐다보지도 않는 거예요.

종류	나누는 기준	예시
Range Partitioning	값의 범위	날짜별 월 단위 파티션 (로그, 주문 테이블에 흔함)
List Partitioning	특정 값 목록	지역 코드별
Hash Partitioning	해시값	특정 파티션에만 데이터가 몰리는 걸 막고 싶을 때

반정규화와의 차이는, 반정규화는 데이터 구조와 중복 자체를 바꾸는 것이고 파티셔닝은 물리적 저장 방식만 바꾼다는 점이에요 — 쿼리 결과(정합성)에는 영향을 주지 않고, 순수하게 성능·관리 목적으로 쓰여요.

다만 파티션 키가 아닌 조건으로 조회하면 오히려 모든 파티션을 다 훑어야 해서 손해일 수 있어요. 그래서 파티션 키를 고르는 일은 인덱스 설계만큼 신중해야 해요.

정리하며

B-Tree의 리프 노드 연결 구조 덕분에 인덱스는 정렬·범위 조회에 강하고, 실행계획은 어떤 스캔·조인 방식이 선택됐는지, 예상과 실제가 얼마나 다른지를 보여주는 도구예요. 그 위에서 SARGable하게 조건을 쓰고, 필요한 컬럼만 골라 쓰고, 복합 인덱스 순서를 신경 쓰는 게 쿼리 튜닝의 기본기였어요. 그래도 조회 부하가 감당이 안 되는 지점에서는 반정규화나 파티셔닝처럼 데이터 구조·저장 방식 자체를 바꾸는 트레이드오프를 국소적으로 적용하게 돼요.

다음에 더 파볼 것

파티션 프루닝이 실제로 일어나는지 EXPLAIN에서는 어떻게 확인할까?
샤딩(Sharding)은 파티셔닝과 무엇이 다르고, 언제 파티셔닝을 넘어 샤딩까지 고려해야 할까?
읽기 복제본(Read Replica)은 반정규화·파티셔닝과 어떻게 함께 조합해서 쓰일까?
Redis 같은 애플리케이션 레벨 캐시는 반정규화(요약 컬럼)와 언제 나눠 써야 할까?

테크블로그