- name
- sql-optimization-patterns
- description
- SQL 쿼리 최적화, 인덱스 전략 및 EXPLAIN 분석을 마스터하여 데이터베이스 성능을 획기적으로 향상시키고 느린 쿼리를 제거합니다. 느린 쿼리 디버깅, 데이터베이스 스키마 설계 또는 애플리케이션 성능 최적화 시 사용하세요.
SQL Optimization Patterns
체계적인 최적화, 올바른 인덱싱 및 쿼리 실행 계획 분석을 통해 느린 데이터베이스 쿼리를 번개처럼 빠른 작업으로 변환하세요.
적용 시기
- 느리게 실행되는 쿼리 디버깅
- 성능이 뛰어난 데이터베이스 스키마 설계
- 애플리케이션 응답 시간 최적화
- 데이터베이스 부하 및 비용 절감
- 데이터 증가에 따른 확장성 개선
- EXPLAIN 쿼리 실행 계획 분석
- 효율적인 인덱스 구현
- N+1 쿼리 문제 해결
핵심 개념 (Core Concepts)
1. 쿼리 실행 계획 (EXPLAIN)
EXPLAIN 출력을 이해하는 것은 최적화의 기본입니다.
PostgreSQL EXPLAIN:
-- 기본 실행 계획 확인
EXPLAIN SELECT * FROM users WHERE email = ' [email protected] ';
-- 실제 실행 통계 포함
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = ' [email protected] ';
-- 더 많은 세부 정보를 포함한 상세 출력
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';주의 깊게 봐야 할 주요 지표:
- Seq Scan: 전체 테이블 스캔 (대용량 테이블에서는 대개 느림)
- Index Scan: 인덱스 사용 (좋음)
- Index Only Scan: 테이블 접근 없이 인덱스만 사용 (가장 좋음)
- Nested Loop: 조인 방식 (작은 데이터셋에는 괜찮음)
- Hash Join: 조인 방식 (큰 데이터셋에 좋음)
- Merge Join: 조인 방식 (정렬된 데이터에 좋음)
- Cost: 추정된 쿼리 비용 (낮을수록 좋음)
- Rows: 추정된 반환 행 수
- Actual Time: 실제 실행 시간
2. 인덱스 전략 (Index Strategies)
인덱스는 가장 강력한 최적화 도구입니다.
인덱스 유형:
- B-Tree: 기본값, 등호(=) 및 범위 쿼리에 좋음
- Hash: 등호(=) 비교에만 사용
- GIN: 전체 텍스트 검색, 배열 쿼리, JSONB
- GiST: 기하학적 데이터, 전체 텍스트 검색
- BRIN: 데이터 간 상관관계가 있는 매우 큰 테이블을 위한 블록 범위 인덱스
-- 표준 B-Tree 인덱스
CREATE INDEX idx_users_email ON users(email);
-- 복합 인덱스 (순서가 중요합니다!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- 부분 인덱스 (행의 일부만 인덱싱)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';
-- 표현식 인덱스 (함수 기반 인덱스)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- 커버링 인덱스 (추가 컬럼 포함)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);
-- 전체 텍스트 검색 인덱스
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));
-- JSONB 인덱스
CREATE INDEX idx_metadata ON events USING GIN(metadata);3. 쿼리 최적화 패턴
**SELECT * 피하기:**
-- 나쁨: 불필요한 모든 컬럼을 가져옴
SELECT * FROM users WHERE id = 123;
-- 좋음: 필요한 컬럼만 명시
SELECT id, email, name FROM users WHERE id = 123;WHERE 절의 효율적 사용:
-- 나쁨: 함수 사용으로 인덱스 활용 불가
SELECT * FROM users WHERE LOWER(email) = ' [email protected] ';
-- 좋음: 함수 기반 인덱스(functional index) 생성 또는 정확한 일치 사용
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- 그 다음:
SELECT * FROM users WHERE LOWER(email) = ' [email protected] ';
-- 또는 데이터를 정규화하여 저장
SELECT * FROM users WHERE email = ' [email protected] ';JOIN 최적화:
-- 나쁨: 카테시안 곱 생성 후 필터링
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
-- 좋음: 조인 전 필터링
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';
-- 더 좋음: 두 테이블 모두 사전 필터링
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;최적화 패턴 (Optimization Patterns)
패턴 1: N+1 쿼리 제거
문제: N+1 쿼리 안티 패턴
# 나쁨: N+1개의 쿼리를 실행함
users = db.query("SELECT * FROM users LIMIT 10")
for user in users:
orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
# orders 처리해결책: JOIN 또는 배치 로딩(Batch Loading) 사용
-- 해결책 1: JOIN 사용
SELECT
u.id, u.name,
o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);
-- 해결책 2: 배치 쿼리
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5);# 좋음: JOIN 또는 배치 로드를 통한 단일 쿼리 실행
# JOIN 사용 시
results = db.query("""
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5)
""")
# 또는 배치 로드(Batch load)
users = db.query("SELECT * FROM users LIMIT 10")
user_ids = [u.id for u in users]
orders = db.query(
"SELECT * FROM orders WHERE user_id IN (?)",
user_ids
)
# user_id별로 orders 그룹화
orders_by_user = {}
for order in orders:
orders_by_user.setdefault(order.user_id, []).append(order)패턴 2: 페이지네이션(Pagination) 최적화
나쁨: 대용량 테이블에서의 OFFSET 사용
-- 큰 offset 값에서 속도 저하 발생
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000; -- 매우 느림!좋음: 커서 기반 페이지네이션 (Cursor-Based Pagination)
-- 훨씬 빠름: 커서(마지막 확인된 ID) 사용
SELECT * FROM users
WHERE created_at < '2024-01-15 10:30:00' -- 마지막 커서
ORDER BY created_at DESC
LIMIT 20;
-- 복합 정렬 시
SELECT * FROM users
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 인덱스 필요
CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);패턴 3: 효율적인 집계 (Aggregate Efficiently)
COUNT 쿼리 최적화:
-- 나쁨: 모든 행을 카운트함
SELECT COUNT(*) FROM orders; -- 큰 테이블에서 느림
-- 좋음: 근사치를 위한 추정치(estimates) 사용
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';
-- 좋음: 카운트 전 필터링 적용
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';
-- 더 좋음: 인덱스 전용 스캔(index-only scan) 활용
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';GROUP BY 최적화:
-- 나쁨: 그룹화 후 필터링
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 10;
-- 좋음: 가능한 경우 먼저 필터링 후 그룹화
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) > 10;
-- 가장 좋음: 커버링 인덱스 활용
CREATE INDEX idx_orders_user_status ON orders(user_id, status);패턴 4: 서브쿼리 최적화
상관 서브쿼리(Correlated Subqueries) 변환:
-- 나쁨: 상관 서브쿼리 (각 행마다 실행됨)
SELECT u.name, u.email,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;
-- 좋음: 집계가 포함된 JOIN
SELECT u.name, u.email, 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, u.email;
-- 더 좋음: 윈도우 함수 사용
SELECT DISTINCT ON (u.id)
u.name, u.email,
COUNT(o.id) OVER (PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;가독성을 위한 CTE 사용:
-- 공통 테이블 식별자(CTE) 활용
WITH recent_users AS (
SELECT id, name, email
FROM users
WHERE created_at > NOW() - INTERVAL '30 days'
),
user_order_counts AS (
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
)
SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders
FROM recent_users ru
LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id;패턴 5: 배치 작업 (Batch Operations)
배치 INSERT:
-- 나쁨: 다수의 개별 insert 수행
INSERT INTO users (name, email) VALUES ('Alice', ' [email protected] ');
INSERT INTO users (name, email) VALUES ('Bob', ' [email protected] ');
INSERT INTO users (name, email) VALUES ('Carol', ' [email protected] ');
-- 좋음: 배치 insert
INSERT INTO users (name, email) VALUES
('Alice', ' [email protected] '),
('Bob', ' [email protected] '),
('Carol', ' [email protected] ');
-- 더 좋음: 대량 insert 시 COPY 활용 (PostgreSQL)
COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;배치 UPDATE:
-- 나쁨: 반복문 내 업데이트
UPDATE users SET status = 'active' WHERE id = 1;
UPDATE users SET status = 'active' WHERE id = 2;
-- ... 많은 ID를 반복
-- 좋음: IN 절을 활용한 단일 UPDATE
UPDATE users
SET status = 'active'
WHERE id IN (1, 2, 3, 4, 5, ...);
-- 더 좋음: 대량 배치 시 임시 테이블 활용
CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR);
INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...;
UPDATE users u
SET status = t.new_status
FROM temp_user_updates t
WHERE u.id = t.id;고급 기범 (Advanced Techniques)
구체화된 뷰 (Materialized Views)
비용이 많이 드는 쿼리를 미리 계산해 둡니다.
-- 구체화된 뷰 생성
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
u.id,
u.name,
COUNT(o.id) as total_orders,
SUM(o.total) as total_spent,
MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- 구체화된 뷰에 인덱스 추가
CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);
-- 구체화된 뷰 갱신
REFRESH MATERIALIZED VIEW user_order_summary;
-- 동시 갱신 (PostgreSQL, 락 최소화)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;
-- 구체화된 뷰 쿼리 (매우 빠름)
SELECT * FROM user_order_summary
WHERE total_spent > 1000
ORDER BY total_spent DESC;파티셔닝 (Partitioning)
성능 향상을 위해 대형 테이블을 나눕니다.
-- 날짜별 범위 파티셔닝 (PostgreSQL)
CREATE TABLE orders (
id SERIAL,
user_id INT,
total DECIMAL,
created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
-- 파티션 생성
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
-- 쿼리는 자동으로 적절한 파티션을 사용함
SELECT * FROM orders
WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
-- orders_2024_q1 파티션만 스캔함쿼리 힌트 및 최적화
-- 인덱스 사용 강제 (MySQL)
SELECT * FROM users
USE INDEX (idx_users_email)
WHERE email = ' [email protected] ';
-- 병렬 쿼리 (PostgreSQL)
SET max_parallel_workers_per_gather = 4;
SELECT * FROM large_table WHERE condition;
-- 조인 힌트 (PostgreSQL)
SET enable_nestloop = OFF; -- hash join 또는 merge join 강제모범 사례 (Best Practices)
- 선별적인 인덱싱: 인덱스가 너무 많으면 쓰기 작업이 느려집니다.
- 쿼리 성능 모니터링: 느린 쿼리 로그(slow query logs)를 활용하세요.
- 통계 정보 업데이트 유지: 정기적으로 ANALYZE를 실행하세요.
- 적절한 데이터 타입 사용: 작은 타입일수록 성능이 좋습니다.
- 사려 깊은 정규화: 정규화와 성능 사이의 균형을 맞추세요.
- 자주 접근하는 데이터 캐싱: 애플리케이션 레벨 캐싱을 활용하세요.
- 커넥션 풀링 (Connection Pooling): 데이터베이스 연결을 재사용하세요.
- 정기적인 유지보수: VACUUM, ANALYZE, 인덱스 재빌드 등을 수행하세요.
-- 통계 업데이트
ANALYZE users;
ANALYZE VERBOSE orders;
-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users; -- 공간 회수 (테이블 락 발생)
-- 인덱스 재구성
REINDEX INDEX idx_users_email;
REINDEX TABLE users;자주 발생하는 문제 (Common Pitfalls)
- 과도한 인덱싱: 각 인덱스는 INSERT/UPDATE/DELETE 속도를 늦춥니다.
- 사용되지 않는 인덱스: 공간을 낭비하고 쓰기 성능을 저하시킵니다.
- 인덱스 누락: 쿼리 속도 저하, 전체 테이블 스캔 유발.
- 암시적 타입 변환: 인덱스 사용을 방해합니다.
- OR 조건: 인덱스를 효율적으로 사용하기 어렵게 만들 수 있습니다.
- 와일드카드가 앞에 붙은 LIKE:
LIKE '%abc'는 인덱스를 탈 수 없습니다. - WHERE 절의 함수: 기능 기반 인덱스가 없다면 인덱스 사용을 방해합니다.
쿼리 모니터링
-- 느린 쿼리 찾기 (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
-- 누락된 인덱스 찾기 (PostgreSQL)
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;
-- 사용되지 않는 인덱스 찾기 (PostgreSQL)
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;리소스
- references/postgres-optimization-guide.md: PostgreSQL 전용 최적화
- references/mysql-optimization-guide.md: MySQL/MariaDB 최적화
- references/query-plan-analysis.md: EXPLAIN 실행 계획 심층 분석
- assets/index-strategy-checklist.md: 인덱스 생성 시점 및 방법
- assets/query-optimization-checklist.md: 단계별 최적화 가이드
- scripts/analyze-slow-queries.sql: 데이터베이스 내 느린 쿼리 식별
- scripts/index-recommendations.sql: 인덱스 권장 사항 생성