C++ 쿼리 최적화 완벽 가이드 | 인덱스 선택·실행 계획·통계·비용 모델·프로덕션 패턴 [#49-3]
![C++ 쿼리 최적화 완벽 가이드 | 인덱스 선택·실행 계획·통계·비용 모델·프로덕션 패턴 [#49-3]](/og/s/cpp-series-49-3-query-optimization.webp)
이 글의 핵심
C++ 쿼리 최적화 완벽 가이드에 대한 실전 가이드입니다. 인덱스 선택·실행 계획·통계·비용 모델·프로덕션 패턴 [#49-3] 등을 예제와 함께 상세히 설명합니다.
들어가며: “프로덕션에서 쿼리가 3초 걸려요”
왜 쿼리 최적화인가
REST API 서버를 운영 중인데, 사용자 목록 조회 API가 3초 이상 걸립니다. 동시 접속자 100명만 되어도 CPU 사용률이 90%를 넘고, 타임아웃 에러가 발생합니다. 쿼리 최적화는 이런 병목을 찾아 인덱스·실행 계획·통계·비용 모델을 활용해 해결하는 과정입니다.
이 글에서 다루는 것:
- 문제 시나리오: 실제 프로덕션에서 겪는 7가지 상황
- 인덱스 선택: 단일/복합/커버링 인덱스, 선택 기준과 완전한 예제
- 실행 계획: EXPLAIN·EXPLAIN ANALYZE로 풀 스캔·인덱스 스캔 분석
- 통계·비용 모델: ANALYZE, pg_stat, 옵티마이저 비용 추정
- 자주 하는 실수: N+1, 풀 스캔, 인덱스 미사용, Prepared Statement 등 13가지
- 베스트 프랙티스: 인덱스 설계, 쿼리 작성, 모니터링
- 프로덕션 패턴: 연결 풀, 읽기/쓰기 분리, 배치 처리
관련 글: 데이터베이스 쿼리 최적화 #51-8, 데이터베이스 기초.
개념을 잡는 비유
이 글의 주제는 여러 부품이 맞물리는 시스템으로 보시면 이해가 빠릅니다. 한 레이어(저장·네트워크·관측)의 선택이 옆 레이어에도 영향을 주므로, 본문에서는 트레이드오프를 숫자와 패턴으로 정리합니다.
목차
- 문제 시나리오
- 인덱스 선택 완전 가이드
- 실행 계획 분석 (EXPLAIN)
- 통계와 비용 모델
- 완전한 쿼리 최적화 예제
- 자주 하는 실수와 해결법
- 베스트 프랙티스
- 프로덕션 패턴
- 체크리스트
- 정리
핵심: 인덱스 선택 → 실행 계획 확인 → 통계 갱신 → N+1 제거 → Prepared Statement. 이 순서로 적용하면 대부분의 쿼리 병목을 해결할 수 있습니다.
1. 문제 시나리오
시나리오 1: “사용자 목록 API가 3초 걸린다”
상황: GET /users API가 1000명 조회 시 3.2초가 걸립니다. 각 사용자별로 주문을 추가 조회하는 N+1 패턴이 원인입니다.
// ❌ 문제: 1 + 1000 = 1001번 쿼리
void get_users_with_orders(PGconn* conn) {
PGresult* users = PQexec(conn, "SELECT id, name FROM users");
for (int i = 0; i < PQntuples(users); ++i) {
int id = atoi(PQgetvalue(users, i, 0));
PGresult* orders = PQexecParams(conn,
"SELECT * FROM orders WHERE user_id = $1", 1, nullptr,
(const char*[]){std::to_string(id).c_str()}, nullptr, nullptr, 0);
// ... 처리 ...
PQclear(orders);
}
PQclear(users);
}원인: 루프 안에서 매 사용자마다 별도 쿼리 실행. 1000명 × 3ms ≈ 3초.
해결: JOIN 또는 IN 배치 쿼리로 1~2번으로 축소.
시나리오 2: “인덱스가 있는데도 풀 스캔이 발생한다”
상황: orders 테이블에 idx_orders_user_id 인덱스가 있는데, EXPLAIN 결과 Seq Scan이 나옵니다.
-- 쿼리
SELECT * FROM orders WHERE LOWER(status) = 'pending';주의사항: 대소문자 구분 규칙(Collation)이 바뀌면 함수 인덱스와 결과가 어긋날 수 있으니 마이그레이션 시 함께 검증하세요.
원인: LOWER(status)처럼 함수를 컬럼에 적용하면 인덱스가 사용되지 않습니다. 옵티마이저는 status의 원본 값으로 인덱스를 탐색할 수 없기 때문입니다.
해결: 함수 적용을 제거하거나, 함수 기반 인덱스 생성.
-- ✅ 함수 기반 인덱스 (PostgreSQL)
CREATE INDEX idx_orders_status_lower ON orders (LOWER(status));시나리오 3: “통계가 오래되어 잘못된 실행 계획이 선택된다”
상황: 테이블에 1000만 행이 추가되었는데, 옵티마이저는 여전히 1000행으로 추정해 Nested Loop 조인을 선택합니다. 실제로는 Hash Join이 더 빠릅니다.
원인: pg_stat_user_tables의 통계가 갱신되지 않음. ANALYZE를 실행하지 않아 옵티마이저가 구식 통계를 사용합니다.
해결: 주기적으로 ANALYZE 실행. 대량 INSERT/UPDATE 후 즉시 ANALYZE 호출.
ANALYZE orders;
-- 또는 특정 컬럼만
ANALYZE orders (user_id, created_at);시나리오 4: “복합 인덱스 순서를 잘못 설계했다”
상황: WHERE user_id = ? AND created_at > ? 쿼리에 idx_orders_created_user (created_at, user_id) 인덱스를 만들었습니다. 인덱스가 사용되지 않습니다.
원인: 복합 인덱스는 왼쪽 컬럼부터 사용됩니다. created_at이 먼저면 user_id 조건만으로는 인덱스 활용이 제한됩니다.
해결: 선택도(selectivity) 가 높은 컬럼을 앞에. user_id가 1000만 행 중 10행을 반환하면 user_id를 먼저.
-- ✅ 올바른 순서: user_id (선택도 높음) → created_at (범위)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);시나리오 5: “배치 INSERT가 1만 건에 30초 걸린다”
상황: 로그 테이블에 1만 건을 INSERT하는데 30초가 걸립니다.
// ❌ 1만 번 개별 INSERT
for (int i = 0; i < 10000; ++i) {
PQexecParams(conn, "INSERT INTO logs (ts, msg) VALUES ($1, $2)", ...);
}원인: 매 INSERT마다 트랜잭션 커밋, WAL 플러시, 인덱스 갱신. 1만 번 왕복.
해결: BEGIN/COMMIT으로 트랜잭션 묶기, 또는 COPY 사용.
// ✅ 배치 INSERT
PQexec(conn, "BEGIN");
// 100개씩 배치 INSERT
PQexec(conn, "COMMIT");시나리오 6: “동일 쿼리를 1만 번 실행하는데 파싱 비용이 누적된다”
상황: 사용자 로그인 API에서 SELECT * FROM users WHERE email = $1을 초당 1000회 실행합니다. CPU 사용률이 높고, pg_stat_activity에서 Parse 단계가 눈에 띕니다.
원인: 매 요청마다 쿼리 파싱·계획 수립을 반복. Prepared Statement를 사용하지 않아 동일 작업이 반복됩니다.
해결: Prepared Statement로 파싱·계획을 한 번만 수행하고, 바인딩만 변경해 재사용.
// ✅ Prepared Statement 사용 (PostgreSQL)
// 1. PREPARE로 한 번만 파싱
PQexec(conn, "PREPARE get_user (text) AS SELECT * FROM users WHERE email = $1");
// 2. EXECUTE로 바인딩만 변경해 반복 실행
PQexecPrepared(conn, "get_user", 1, (const char*[]){"[email protected]"}, nullptr, nullptr, 0);시나리오 7: “대량 조인 쿼리가 메모리 부족으로 OOM을 일으킨다”
상황: users 100만 행 × orders 1000만 행을 JOIN하는 쿼리가 32GB 메모리 서버에서 OOM으로 종료됩니다.
원인: Nested Loop 조인으로 중간 결과가 메모리에 과도하게 적재. work_mem 초과 시 디스크 스왑으로 인한 성능 저하.
해결: EXPLAIN으로 조인 방식 확인 후, ANALYZE로 통계 갱신해 Hash Join 유도. 필요 시 work_mem 조정.
-- Hash Join 유도를 위한 통계 갱신
ANALYZE users;
ANALYZE orders;
-- work_mem 조정 (세션별, 주의해서 사용)
SET work_mem = '256MB';문제 시나리오 다이어그램
flowchart TB
subgraph Problems["쿼리 성능 문제"]
P1[N+1 쿼리]
P2[풀 스캔]
P3[구식 통계]
P4[잘못된 인덱스 순서]
P5[개별 INSERT]
end
subgraph Solutions["해결책"]
S1[JOIN/IN 배치]
S2[인덱스 추가/함수 제거]
S3[ANALYZE]
S4[복합 인덱스 순서 수정]
S5[배치 INSERT/COPY]
end
P1 --> S1
P2 --> S2
P3 --> S3
P4 --> S4
P5 --> S5
2. 인덱스 선택 완전 가이드
인덱스가 필요한 이유
인덱스 없이 WHERE user_id = 123을 검색하면 100만 행을 전부 읽어야 합니다. B-Tree 인덱스가 있으면 O(log N) 검색으로 수 ms만에 찾을 수 있습니다.
flowchart TB
subgraph NoIndex["인덱스 없음"]
N1[행 1] --> N2[행 2]
N2 --> N3[행 3]
N3 --> N4["..."]
N4 --> N5[행 100만]
N5 --> N6["Full Table Scan: 100만 행 읽음"]
end
subgraph WithIndex["인덱스 있음"]
I1["B-Tree 인덱스"] --> I2["로그₂(100만) ≈ 20 노드"]
I2 --> I3["직접 해당 행 접근"]
end
인덱스 선택 기준표
| 컬럼 용도 | 인덱스 유형 | 예시 | 이유 |
|---|---|---|---|
| WHERE 등호 | 단일 B-Tree | idx_users_email | 정확히 일치 검색 |
| WHERE 범위 | 복합 (등호 먼저) | idx_orders_user_created | user_id 등호 + created_at 범위 |
| JOIN 키 | 양쪽 테이블 | orders.user_id, users.id | 조인 성능 |
| ORDER BY | 복합 인덱스 | (user_id, created_at DESC) | 정렬 생략 |
| SELECT 컬럼 포함 | 커버링 인덱스 | INCLUDE (amount, status) | 테이블 접근 불필요 |
단일 컬럼 인덱스
-- 이메일로 사용자 검색 (등호 조건)
CREATE INDEX idx_users_email ON users(email);
-- 생성일 기준 정렬
CREATE INDEX idx_users_created_at ON users(created_at);// C++에서 인덱스 생성 (SQLite)
#include <sqlite3.h>
void create_indexes(sqlite3* db) {
const char* sql = R"(
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_created ON users(created_at);
)";
char* err = nullptr;
sqlite3_exec(db, sql, nullptr, nullptr, &err);
if (err) {
sqlite3_free(err);
}
}복합 인덱스와 컬럼 순서
규칙: 선택도(selectivity)가 높은 컬럼을 앞에 배치. 등호 조건을 범위 조건보다 앞에.
-- ✅ user_id가 1000만 행 중 ~10행 반환, created_at은 범위
-- 순서: user_id (등호, 선택도 높음) → created_at (범위)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
-- ❌ 잘못된 순서: created_at이 먼저면 user_id만 조건일 때 비효율
CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);사용 가능한 쿼리 패턴:
| 인덱스 (user_id, created_at) | 사용 가능 | 비고 |
|---|---|---|
WHERE user_id = ? | ✅ | 앞쪽 컬럼만 사용 |
WHERE user_id = ? AND created_at > ? | ✅ | 둘 다 사용 |
WHERE created_at > ? | ❌ | user_id 없으면 비효율 |
WHERE user_id = ? ORDER BY created_at DESC | ✅ | 정렬 생략 |
커버링 인덱스 (Index Only Scan)
SELECT 컬럼을 모두 인덱스에 포함하면 테이블 접근 없이 인덱스만 읽습니다.
-- PostgreSQL: INCLUDE 절
CREATE INDEX idx_orders_user_cover ON orders(user_id) INCLUDE (amount, status);
-- SQLite: 복합 인덱스에 컬럼 포함
CREATE INDEX idx_orders_user_cover ON orders(user_id, amount, status);-- 이 쿼리는 Index Only Scan 가능
SELECT user_id, amount, status FROM orders WHERE user_id = 123;부분 인덱스 (Partial Index)
특정 조건을 만족하는 행만 인덱스에 포함합니다. 인덱스 크기 감소, 성능 향상.
-- status='active'인 주문만 인덱스
CREATE INDEX idx_orders_active ON orders(user_id) WHERE status = 'active';
-- 최근 30일 로그만 인덱스
CREATE INDEX idx_logs_recent ON logs(created_at) WHERE created_at > NOW() - INTERVAL '30 days';인덱스 사용 불가 케이스
-- ❌ 함수 적용 시 인덱스 미사용
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- ✅ 인덱스 사용
SELECT * FROM users WHERE email = '[email protected]';
-- ❌ 암시적 타입 변환 (PostgreSQL)
SELECT * FROM users WHERE id = '123'; -- id가 integer
-- ✅ 명시적 타입
SELECT * FROM users WHERE id = 123;
-- ❌ OR 조건 (인덱스 병합 가능하나 비효율)
SELECT * FROM users WHERE email = '[email protected]' OR name = 'Alice';
-- ✅ UNION으로 분리
SELECT * FROM users WHERE email = '[email protected]'
UNION ALL
SELECT * FROM users WHERE name = 'Alice' AND email != '[email protected]';3. 실행 계획 분석 (EXPLAIN)
실행 계획이란
DB 엔진이 쿼리를 어떻게 실행할지를 보여줍니다. EXPLAIN으로 풀 스캔·인덱스 스캔·조인 순서를 확인할 수 있습니다.
flowchart LR
A[SQL 쿼리] --> B[파서]
B --> C[쿼리 최적화기]
C --> D[실행 계획]
D --> E[Index Scan]
D --> F[Seq Scan]
D --> G[Nested Loop]
PostgreSQL EXPLAIN ANALYZE
-- 실제 실행 시간 포함 (ANALYZE)
-- 버퍼 접근 정보 (BUFFERS)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;인덱스 사용 시 (좋음):
Limit (cost=0.42..8.44 rows=10 width=40) (actual time=0.05..0.08 rows=10 loops=1)
-> Index Scan using idx_orders_user_created on orders
Index Cond: (user_id = 123)
Buffers: shared hit=4
Planning Time: 0.12 ms
Execution Time: 0.15 ms풀 스캔 시 (나쁨):
Limit (cost=0.00..20834.00 rows=10 width=40) (actual time=45.2..182.3 rows=10 loops=1)
-> Seq Scan on orders
Filter: (user_id = 123)
Rows Removed by Filter: 999990
Buffers: shared read=8000
Planning Time: 0.08 ms
Execution Time: 182.5 ms실행 계획 용어 해석
| 용어 | 의미 | 대응 |
|---|---|---|
| Seq Scan | 전체 테이블 스캔 | 인덱스 추가 |
| Index Scan | 인덱스 사용, 테이블 접근 | 양호 |
| Index Only Scan | 인덱스만 읽음 (커버링) | 최적 |
| Bitmap Index Scan | 인덱스로 비트맵 생성 후 테이블 접근 | 대량 행 시 |
| Nested Loop | 중첩 루프 조인 | 작은 테이블에 유리 |
| Hash Join | 해시 조인 | 대용량 조인, 등호 조건 |
| Merge Join | 정렬 병합 조인 | 정렬된 데이터 |
C++에서 EXPLAIN 실행
#include <libpq-fe.h>
#include <iostream>
#include <string>
void explain_query(PGconn* conn, const std::string& sql) {
std::string explain_sql = "EXPLAIN (ANALYZE, BUFFERS) " + sql;
PGresult* res = PQexec(conn, explain_sql.c_str());
if (PQresultStatus(res) == PGRES_TUPLES_OK) {
for (int i = 0; i < PQntuples(res); ++i) {
const char* row = PQgetvalue(res, i, 0);
if (row) std::cout << row << "\n";
}
}
PQclear(res);
}SQLite EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
SELECT id, name FROM users WHERE email = '[email protected]';-- 인덱스 사용 시
SEARCH users USING INDEX idx_users_email (email=?)
-- 인덱스 미사용 시
SCAN users실행 계획 분석 체크리스트
| 확인 항목 | 좋은 신호 | 나쁜 신호 | 조치 |
|---|---|---|---|
| 스캔 방식 | Index Scan, Index Only Scan | Seq Scan | 인덱스 추가/수정 |
| Rows Removed by Filter | 0 또는 작음 | 10만 이상 | WHERE 컬럼 인덱스 |
| Buffers shared hit | 높은 비율 | read 위주 | 캐시 warm-up |
| actual time | 10ms 이하 | 100ms 이상 | 쿼리/인덱스 재검토 |
| Planning Time | 1ms 이하 | 10ms 이상 | 통계 갱신, 파라미터 검토 |
// C++에서 SQLite EXPLAIN
void explain_sqlite(sqlite3* db, const std::string& sql) {
sqlite3_stmt* stmt = nullptr;
std::string explain_sql = "EXPLAIN QUERY PLAN " + sql;
sqlite3_prepare_v2(db, explain_sql.c_str(), -1, &stmt, nullptr);
while (sqlite3_step(stmt) == SQLITE_ROW) {
const char* detail = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
if (detail) std::cout << detail << "\n";
}
sqlite3_finalize(stmt);
}4. 통계와 비용 모델
통계가 실행 계획에 미치는 영향
옵티마이저는 테이블·인덱스 통계를 기반으로 비용을 추정합니다. 통계가 오래되면 잘못된 실행 계획이 선택됩니다.
flowchart TB
A[pg_stat_user_tables] --> B[옵티마이저]
C[pg_stats] --> B
B --> D[비용 추정]
D --> E[실행 계획 선택]
ANALYZE로 통계 갱신
-- 전체 데이터베이스
ANALYZE;
-- 특정 테이블
ANALYZE orders;
-- 특정 컬럼만 (대용량 테이블)
ANALYZE orders (user_id, created_at);// C++에서 ANALYZE 실행
void refresh_statistics(PGconn* conn) {
PGresult* res = PQexec(conn, "ANALYZE orders");
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
std::cerr << "ANALYZE failed: " << PQerrorMessage(conn) << "\n";
}
PQclear(res);
}pg_stat 시스템 카탈로그
-- 테이블별 스캔 통계
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- 인덱스별 사용 통계
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';| 컬럼 | 의미 |
|---|---|
| seq_scan | Sequential Scan 횟수 |
| seq_tup_read | Seq Scan으로 읽은 행 수 |
| idx_scan | Index Scan 횟수 |
| idx_tup_fetch | Index로 가져온 행 수 |
pg_stat 활용: 풀 스캔 빈도 모니터링
-- Seq Scan이 많은 테이블 찾기 (인덱스 추가 후보)
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
seq_scan::float / NULLIF(seq_scan + idx_scan, 0) AS seq_ratio
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 10;-- 사용되지 않는 인덱스 찾기 (제거 후보)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey';// C++에서 pg_stat 조회해 모니터링
void log_table_stats(PGconn* conn) {
const char* sql = R"(
SELECT relname, seq_scan, idx_scan, seq_tup_read, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 1000 OR seq_tup_read > 100000
)";
PGresult* res = PQexec(conn, sql);
for (int i = 0; i < PQntuples(res); ++i) {
printf("Table: %s, seq_scan=%s, idx_scan=%s\n",
PQgetvalue(res, i, 0), PQgetvalue(res, i, 1), PQgetvalue(res, i, 2));
}
PQclear(res);
}비용 모델 (PostgreSQL)
PostgreSQL은 비용 단위로 실행 계획을 비교합니다. 기본 설정:
seq_page_cost = 1.0: Sequential Scan 1페이지 비용random_page_cost = 4.0: Random I/O 1페이지 비용 (SSD는 1.1 권장)cpu_tuple_cost = 0.01: 행 1개 처리 비용
-- 비용 파라미터 확인
SHOW seq_page_cost;
SHOW random_page_cost;
-- SSD 환경 권장
ALTER SYSTEM SET random_page_cost = 1.1;비용 해석 예시
Limit (cost=0.42..8.44 rows=10 width=40)cost=0.42..8.44: 시작 비용 0.42, 총 비용 8.44rows=10: 추정 반환 행 수width=40: 평균 행 크기 (바이트)
비용이 높은 경우: cost=0.00..20834.00 → 2만 비용 단위, 풀 스캔 추정.
5. 완전한 쿼리 최적화 예제
예제: 사용자별 최근 주문 10건 조회
요구사항: 1000명 사용자 각각에 대해 최근 주문 10건을 조회. 3초 이내 응답.
Step 1: 현재 상태 분석
-- 테이블 구조
-- users: id, name, email, created_at (100만 행)
-- orders: id, user_id, amount, status, created_at (1000만 행)
-- 현재 쿼리 (N+1)
SELECT * FROM users;
-- 루프: SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 10EXPLAIN 결과:
- users: Seq Scan (인덱스 없음)
- orders: Seq Scan 1000번 (user_id 인덱스 없음)
Step 2: 인덱스 설계
-- users: 이메일 검색용 (선택)
CREATE INDEX idx_users_email ON users(email);
-- orders: user_id + created_at 복합 인덱스 (핵심)
-- user_id 등호 + created_at DESC 정렬
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);Step 3: N+1 해결 — IN 배치 쿼리
#include <libpq-fe.h>
#include <vector>
#include <string>
#include <unordered_map>
struct Order {
int id, user_id, amount;
std::string status;
};
std::unordered_map<int, std::vector<Order>> get_recent_orders_batch(
PGconn* conn, const std::vector<int>& user_ids, int limit = 10)
{
if (user_ids.empty()) return {};
// IN 절 동적 생성
std::string sql = "SELECT user_id, id, amount, status FROM (";
sql += "SELECT user_id, id, amount, status, "
"ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn ";
sql += "FROM orders WHERE user_id = ANY($1::int[])) sub ";
sql += "WHERE rn <= " + std::to_string(limit);
// 배열 파라미터 (PostgreSQL)
std::string array_str = "{";
for (size_t i = 0; i < user_ids.size(); ++i) {
if (i > 0) array_str += ",";
array_str += std::to_string(user_ids[i]);
}
array_str += "}";
const char* params[] = {array_str.c_str()};
PGresult* res = PQexecParams(conn, sql.c_str(), 1, nullptr, params, nullptr, nullptr, 0);
std::unordered_map<int, std::vector<Order>> result;
if (PQresultStatus(res) == PGRES_TUPLES_OK) {
for (int i = 0; i < PQntuples(res); ++i) {
Order o;
o.user_id = atoi(PQgetvalue(res, i, 0));
o.id = atoi(PQgetvalue(res, i, 1));
o.amount = atoi(PQgetvalue(res, i, 2));
o.status = PQgetvalue(res, i, 3) ? PQgetvalue(res, i, 3) : "";
result[o.user_id].push_back(o);
}
}
PQclear(res);
return result;
}Step 4: 실행 계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, id, amount, status FROM (
SELECT user_id, id, amount, status,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
FROM orders WHERE user_id = ANY(ARRAY[1,2,3,...,1000])
) sub WHERE rn <= 10;기대 결과: Index Scan using idx_orders_user_created 또는 Bitmap Heap Scan.
Step 5: 성능 비교
| 방식 | 쿼리 수 | 예상 시간 |
|---|---|---|
| N+1 (인덱스 없음) | 1001 | 3,200ms |
| N+1 (인덱스 있음) | 1001 | 800ms |
| IN 배치 (인덱스 있음) | 2 | 95ms |
Step 6: Prepared Statement로 파싱 비용 제거
동일 쿼리를 반복 실행할 때 Prepared Statement를 사용하면 파싱·계획 수립을 한 번만 수행합니다. SQL 인젝션도 방지됩니다.
// PostgreSQL: PQprepare + PQexecPrepared
#include <libpq-fe.h>
#include <string>
class PreparedQuery {
PGconn* conn_;
std::string name_;
int n_params_;
public:
PreparedQuery(PGconn* conn, const std::string& name,
const std::string& sql, int n_params)
: conn_(conn), name_(name), n_params_(n_params)
{
PGresult* res = PQprepare(conn_, name_.c_str(), sql.c_str(), n_params_, nullptr);
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
PQclear(res);
throw std::runtime_error(PQerrorMessage(conn_));
}
PQclear(res);
}
PGresult* execute(const char* const* params) {
return PQexecPrepared(conn_, name_.c_str(), n_params_, params, nullptr, nullptr, 0);
}
};
// 사용 예: 사용자 조회 (초당 1000회 호출 시 파싱 1회만)
// PreparedQuery get_user(conn, "get_user", "SELECT id, name FROM users WHERE email = $1", 1);
// PGresult* res = get_user.execute((const char*[]){"[email protected]"});// SQLite: sqlite3_prepare_v2 + sqlite3_bind_* + sqlite3_step
// 한 번 준비 후 재사용
sqlite3_stmt* stmt = nullptr;
sqlite3_prepare_v2(db, "SELECT id, name FROM users WHERE email = ?", -1, &stmt, nullptr);
// 루프에서 바인딩만 변경
for (const auto& email : emails) {
sqlite3_bind_text(stmt, 1, email.c_str(), -1, SQLITE_TRANSIENT);
while (sqlite3_step(stmt) == SQLITE_ROW) {
// 결과 처리
}
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);Step 7: JOIN으로 N+1 완전 제거
IN 배치 대신 한 번의 JOIN으로 사용자와 주문을 함께 조회할 수 있습니다.
-- 사용자 + 최근 주문 10건 조회 (1회 쿼리)
WITH ranked AS (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) rn
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.created_at > '2025-01-01' -- 필요한 사용자만
)
SELECT u.id, u.name, r.id AS order_id, r.amount, r.status
FROM users u
JOIN ranked r ON u.id = r.user_id AND r.rn <= 10
ORDER BY u.id, r.rn;// C++에서 JOIN 결과 처리
std::vector<UserWithOrders> get_users_with_orders_join(PGconn* conn) {
const char* sql = R"(
WITH ranked AS (
SELECT o.user_id, o.id AS order_id, o.amount, o.status,
ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) rn
FROM orders o JOIN users u ON o.user_id = u.id
)
SELECT u.id, u.name, r.order_id, r.amount, r.status
FROM users u
JOIN ranked r ON u.id = r.user_id AND r.rn <= 10
ORDER BY u.id, r.rn
)";
PGresult* res = PQexec(conn, sql);
std::vector<UserWithOrders> result;
int current_user_id = -1;
UserWithOrders* current = nullptr;
for (int i = 0; i < PQntuples(res); ++i) {
int uid = atoi(PQgetvalue(res, i, 0));
if (uid != current_user_id) {
result.push_back({uid, PQgetvalue(res, i, 1), {}});
current = &result.back();
current_user_id = uid;
}
if (current)
current->orders.push_back({atoi(PQgetvalue(res, i, 2)),
atoi(PQgetvalue(res, i, 3)),
PQgetvalue(res, i, 4)});
}
PQclear(res);
return result;
}Step 8: 실행 계획 단계별 분석
최적화 전후 실행 계획을 비교하는 절차입니다.
1. EXPLAIN (ANALYZE, BUFFERS) <쿼리> 실행
2. Seq Scan 확인 → Rows Removed by Filter가 크면 인덱스 추가 검토
3. Index Scan 확인 → Buffers: shared hit 비율이 높으면 캐시 hit
4. cost, actual time 비교 → cost는 추정, actual은 실제
5. Nested Loop vs Hash Join → 행 수에 따라 옵티마이저 선택-- 통계 기반 실행 계획 비교 (ANALYZE 전후)
-- BEFORE: rows=1000 (구식 통계)
-- AFTER: rows=10000000 (ANALYZE 후)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.id, u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;6. 자주 하는 실수와 해결법
에러 1: N+1 쿼리
증상: 사용자 목록 API 3초 이상.
원인: 루프 안에서 매번 DB 쿼리.
// ❌ 나쁜 예
for (auto& user : users) {
auto orders = query_orders(conn, user.id);
}해결: JOIN 또는 IN 배치 쿼리.
에러 2: 풀 스캔 (Seq Scan)
증상: EXPLAIN에서 Seq Scan, 100만 행 스캔.
원인: WHERE 조건 컬럼에 인덱스 없음.
-- 해결
CREATE INDEX idx_orders_user_id ON orders(user_id);에러 3: 함수 적용으로 인덱스 미사용
증상: 인덱스가 있는데도 Seq Scan.
-- ❌ LOWER() 적용
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- ✅ 함수 제거 또는 함수 기반 인덱스
SELECT * FROM users WHERE email = '[email protected]';
CREATE INDEX idx_users_email_lower ON users (LOWER(email));에러 4: 복합 인덱스 순서 오류
증상: (created_at, user_id) 인덱스인데 WHERE user_id = ?만 사용.
해결: (user_id, created_at) 순서로 재생성.
-- ❌ user_id만 조건일 때 인덱스 비효율
DROP INDEX idx_orders_created_user;
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);에러 5: Connection Leak
증상: too many connections, FATAL: remaining connection slots.
원인: 연결을 닫지 않음.
// ✅ RAII 가드 사용
PgConnectionGuard guard(pool);
PGconn* conn = guard.get();에러 6: SQL 인젝션
증상: 악의적 입력으로 데이터 유출.
// ❌ 절대 금지
std::string sql = "SELECT * FROM users WHERE id = " + user_input;
// ✅ 파라미터 바인딩
PQexecParams(conn, "SELECT * FROM users WHERE id = $1::int", 1, nullptr, params, nullptr, nullptr, 0);에러 7: 구식 통계
증상: 대량 데이터 추가 후 잘못된 조인 방식 선택.
해결: ANALYZE 실행.
에러 8: SELECT * 사용
증상: 불필요한 컬럼까지 전송, 커버링 인덱스 미활용.
-- ❌
SELECT * FROM orders WHERE user_id = 123;
-- ✅ 필요한 컬럼만
SELECT id, amount, status FROM orders WHERE user_id = 123;에러 9: 인덱스 과다
증상: INSERT/UPDATE가 느려짐. 인덱스마다 갱신 비용 발생.
해결: 실제로 사용하는 쿼리 패턴에 맞춰 인덱스 최소화. 사용하지 않는 인덱스 제거.
에러 10: 캐시 무효화 누락
증상: 캐시된 데이터가 DB와 불일치.
해결: INSERT/UPDATE/DELETE 후 캐시 무효화 또는 갱신.
void update_user(..., LruCache<int, User>& cache, ...) {
// DB 업데이트
cache.put(id, new_user); // 캐시 갱신
}에러 11: Prepared Statement 미사용
증상: 동일 쿼리 반복 시 CPU 사용률 상승, pg_stat_statements에서 calls 대비 total_parse_time 비율 높음.
해결: PQprepare + PQexecPrepared 또는 sqlite3_prepare_v2 재사용.
// ❌ 매번 파싱
for (int i = 0; i < 10000; ++i) {
PQexecParams(conn, "SELECT * FROM users WHERE id = $1", 1, nullptr, params, nullptr, nullptr, 0);
}
// ✅ Prepared Statement
PQprepare(conn, "get_user", "SELECT * FROM users WHERE id = $1", 1, nullptr);
for (int i = 0; i < 10000; ++i) {
PQexecPrepared(conn, "get_user", 1, params, nullptr, nullptr, 0);
}에러 12: IN 절 파라미터 과다
증상: WHERE id IN (1,2,...,10000)처럼 IN 절에 수천 개를 넣으면 쿼리 계획이 비효율적이거나 실패.
해결: 500~1000개 단위로 배치 분할, 또는 임시 테이블 + JOIN.
// ✅ 500개씩 배치
const size_t BATCH = 500;
for (size_t i = 0; i < ids.size(); i += BATCH) {
auto end = std::min(i + BATCH, ids.size());
query_batch(conn, ids.data() + i, end - i);
}에러 13: 트랜잭션 없이 배치 INSERT
증상: 1만 건 INSERT 시 30초 이상, 각 INSERT마다 WAL 플러시.
해결: BEGIN/COMMIT으로 묶거나 COPY 사용.
// ✅ 트랜잭션으로 묶기
PQexec(conn, "BEGIN");
for (const auto& row : rows) {
PQexecParams(conn, "INSERT INTO logs (...) VALUES ($1, $2)", ...);
}
PQexec(conn, "COMMIT");7. 베스트 프랙티스
1. 인덱스 설계
- WHERE, JOIN, ORDER BY 컬럼에 인덱스
- 복합 인덱스: 선택도 높은 컬럼을 앞에
- 커버링 인덱스로 Index Only Scan 유도
- 사용하지 않는 인덱스 제거
2. 쿼리 작성
SELECT *지양, 필요한 컬럼만- 파라미터 바인딩 (Prepared Statement)
- N+1 패턴 회피 (JOIN, IN 배치)
- LIMIT 적용 (대량 조회 시)
3. 통계 유지
- 대량 INSERT/UPDATE 후
ANALYZE - 주기적
ANALYZE(cron, pg_cron) pg_stat_user_tables모니터링
4. 실행 계획 검증
- 새 인덱스 추가 후
EXPLAIN ANALYZE확인 - 풀 스캔 → 인덱스 스캔 전환 확인
- 비용·실행 시간 비교
5. 연결 관리
- 연결 풀 사용 (pool_size 10~20)
- RAII로 acquire/release 쌍 보장
- Connection Leak 방지
6. 모니터링
- 느린 쿼리 로깅 (100ms 이상)
pg_stat_activity로 현재 쿼리 확인- 쿼리 타임아웃 설정
7. 인덱스 선택 의사결정 플로우
flowchart TD
A[쿼리 분석] --> B{WHERE 조건?}
B -->|등호 1개| C[단일 B-Tree]
B -->|등호+범위| D[복합: 등호 먼저]
B -->|범위만| E[단일 또는 부분 인덱스]
C --> F[선택도 높은 컬럼]
D --> F
E --> F
F --> G{SELECT 컬럼?}
G -->|인덱스에 포함 가능| H[커버링 INCLUDE]
G -->|아니오| I[일반 인덱스]
8. N+1 방지 패턴 요약
| 패턴 | 사용 시점 | 장점 | 단점 |
|---|---|---|---|
| JOIN | 1:N 관계, 한 번에 조회 | 쿼리 1회만 | 결과 중복, 메모리 |
| IN 배치 | N개 ID로 조회 | 유연함, 페이지네이션 | IN 크기 제한 |
| Lazy Loading | 필요 시에만 | 초기 로딩 빠름 | N+1 위험 |
| Lazy + 캐시 | 반복 조회 | 캐시 hit 시 빠름 | 캐시 관리 복잡 |
8. 프로덕션 패턴
패턴 1: 연결 풀 + Prepared Statement
struct DbContext {
PgConnectionPool& pool;
PgConnectionGuard guard;
PGconn* conn;
PreparedStatementCache& stmt_cache;
explicit DbContext(PgConnectionPool& p, PreparedStatementCache& c)
: pool(p), guard(p), conn(guard.get()), stmt_cache(c) {}
};패턴 2: 읽기/쓰기 분리 (Replica)
flowchart TB
App[앱] --> Router[라우터]
Router -->|SELECT| Replica[Replica]
Router -->|INSERT/UPDATE| Primary[Primary]
Primary -.->|복제| Replica
class ReadWriteSplit {
PgConnectionPool* primary_;
PgConnectionPool* replica_;
public:
PGconn* acquire_read() { return replica_->acquire(); }
PGconn* acquire_write() { return primary_->acquire(); }
};패턴 3: 배치 INSERT
PQexec(conn, "BEGIN");
for (size_t i = 0; i < batch.size(); i += 100) {
// 100개씩 배치 INSERT
batch_insert(conn, batch, i, std::min(i + 100, batch.size()));
}
PQexec(conn, "COMMIT");패턴 4: 쿼리 타임아웃
PQexec(conn, "SET statement_timeout = 5000"); // 5초패턴 5: 느린 쿼리 로깅
auto start = std::chrono::steady_clock::now();
PGresult* res = PQexecParams(conn, sql.c_str(), ...);
auto elapsed = std::chrono::duration_cast<std::chrono::milliseconds>(
std::chrono::steady_clock::now() - start).count();
if (elapsed > 100) {
log_warn("Slow query: {}ms - {}", elapsed, sql);
}패턴 6: 주기적 ANALYZE
-- pg_cron 또는 cron
-- 0 3 * * * psql -c "ANALYZE"패턴 7: Prepared Statement 캐시
// 연결별 Prepared Statement 캐시
class PreparedStatementCache {
PGconn* conn_;
std::unordered_map<std::string, std::string> stmts_;
public:
explicit PreparedStatementCache(PGconn* conn) : conn_(conn) {}
PGresult* execute(const std::string& name, const std::string& sql,
int n_params, const char* const* params) {
if (stmts_.find(name) == stmts_.end()) {
std::string prepare_sql = "PREPARE " + name + " AS " + sql;
PQexec(conn_, prepare_sql.c_str());
stmts_[name] = sql;
}
return PQexecPrepared(conn_, name.c_str(), n_params, params, nullptr, nullptr, 0);
}
};패턴 8: COPY로 대량 로드
// PostgreSQL COPY: 1만 건 INSERT를 1초 이내로
void bulk_insert_logs(PGconn* conn, const std::vector<Log>& logs) {
PQexec(conn, "BEGIN");
PGresult* res = PQexec(conn, "COPY logs (ts, msg, level) FROM STDIN WITH (FORMAT text)");
if (PQresultStatus(res) != PGRES_COPY_IN) {
PQclear(res);
return;
}
PQclear(res);
for (const auto& log : logs) {
std::string row = log.ts + "\t" + log.msg + "\t" + log.level + "\n";
PQputCopyData(conn, row.c_str(), static_cast<int>(row.size()));
}
PQputCopyEnd(conn, nullptr);
PQgetResult(conn); // COPY 완료 대기
PQexec(conn, "COMMIT");
}패턴 9: 읽기/쓰기 라우팅 (SQL 기반)
// SELECT는 replica, 그 외는 primary
PGconn* acquire_for_query(PgConnectionPool* primary, PgConnectionPool* replica,
const std::string& sql) {
return (sql.find("SELECT") == 0) ? replica->acquire() : primary->acquire();
}9. 체크리스트
인덱스
- WHERE 조건 컬럼에 인덱스
- JOIN 키 컬럼에 인덱스
- ORDER BY 컬럼 고려 (복합 인덱스)
- EXPLAIN으로 인덱스 사용 확인
- 함수 적용 시 함수 기반 인덱스 또는 쿼리 수정
쿼리
- Prepared Statement (파라미터 바인딩)
- N+1 해결 (JOIN 또는 IN 배치)
- SELECT 시 필요한 컬럼만
- LIMIT 적용
통계
- 대량 변경 후 ANALYZE
- pg_stat 모니터링
연결
- 연결 풀 사용
- RAII 가드
- Connection Leak 방지
보안
- SQL 인젝션 방지
- 쿼리 타임아웃
- 느린 쿼리 로깅
10. 정리
| 항목 | 핵심 |
|---|---|
| 인덱스 | WHERE, JOIN, ORDER BY에 B-Tree. 복합 인덱스 순서: 선택도 높은 컬럼 먼저 |
| 실행 계획 | EXPLAIN ANALYZE로 Seq Scan → Index Scan 전환 확인 |
| 통계 | ANALYZE로 옵티마이저에 정확한 통계 제공 |
| 비용 모델 | cost, rows 해석. SSD는 random_page_cost 낮춤 |
| N+1 | JOIN 또는 IN 배치로 쿼리 수 축소 |
| Prepared Statement | 파싱 비용 제거, SQL 인젝션 방지 |
| 프로덕션 | 연결 풀, 읽기/쓰기 분리, 배치 INSERT, COPY, 타임아웃, 모니터링 |
한 줄 요약: 문제 시나리오를 파악하고, 인덱스 선택·실행 계획·통계·비용 모델을 활용해 3초 쿼리를 100ms 이하로 줄일 수 있습니다.
자주 묻는 질문 (FAQ)
Q. 이 내용을 실무에서 언제 쓰나요?
A. 프로덕션에서 쿼리가 3초 걸릴 때. 문제 시나리오, 인덱스 선택 기준, EXPLAIN 실행 계획 분석, 통계·비용 모델, 자주 하는 실수, 베스트 프랙티스, 프로덕션 패턴까지 실전 코드로 구현합니다. 실무에서는 위 본문의 예제와 선택 가이드를 참고해 적용하면 됩니다.
Q. 선행으로 읽으면 좋은 글은?
A. 각 글 하단의 이전 글 링크를 따라가면 순서대로 배울 수 있습니다. C++ 시리즈 목차에서 전체 흐름을 확인할 수 있습니다.
Q. 더 깊이 공부하려면?
A. cppreference와 해당 라이브러리 공식 문서를 참고하세요. 글 말미의 참고 자료 링크도 활용하면 좋습니다.
다음 글: 데이터베이스 쿼리 최적화 #51-8 — Prepared Statement, 연결 풀, 캐싱 심화.
참고 자료:
같이 보면 좋은 글 (내부 링크)
이 주제와 연결되는 다른 글입니다.
- C++ 데이터베이스 쿼리 최적화 완벽 가이드 | 인덱스·실행 계획·캐싱·N+1 해결 [#51-8]
- C++ 데이터베이스 연동 완벽 가이드 | SQLite·PostgreSQL·연결 풀·트랜잭션 [#31-3]
- C++ 데이터베이스 엔진 구현 | B-Tree·트랜잭션·쿼리 최적화 [#50-4]
이 글에서 다루는 키워드 (관련 검색어)
C++, 데이터베이스, 쿼리최적화, 인덱스, 실행계획, EXPLAIN, PostgreSQL, SQLite 등으로 검색하시면 이 글이 도움이 됩니다.
관련 글
- C++ 시리즈 전체 보기
- C++ Adapter Pattern 완벽 가이드 | 인터페이스 변환과 호환성
- C++ ADL |
- C++ Aggregate Initialization |