Contents
see List데이터베이스 성능 문제의 80%는 비효율적인 SQL 쿼리에서 발생합니다. 테이블 규모가 커질수록 잘못된 쿼리 하나가 전체 시스템 성능을 급격히 저하시킬 수 있습니다. 이 글에서는 EXPLAIN 분석법, 인덱스 전략, 쿼리 리팩토링 패턴 등 실무에서 즉시 적용할 수 있는 SQL 최적화 테크닉을 다룹니다.
EXPLAIN ANALYZE 완벽 해석
쿼리 최적화의 첫 단계는 실행 계획을 읽는 것입니다. EXPLAIN ANALYZE는 실제 쿼리를 실행하면서 각 단계의 비용과 소요 시간을 보여줍니다.
-- 기본 EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;
-- 실행 계획 읽는 법
-- Limit (cost=15234.56..15234.58 rows=10 width=40)
-- (actual time=45.123..45.130 rows=10 loops=1)
-- -> Sort (cost=15234.56..15284.56 rows=20000 width=40)
-- (actual time=45.120..45.125 rows=10 loops=1)
-- Sort Key: (count(o.id)) DESC
-- Sort Method: top-N heapsort Memory: 25kB
-- -> HashAggregate (...)
-- -> Hash Join (...)
-- Hash Cond: (o.user_id = u.id)
-- -> Seq Scan on orders o (...)
-- Filter: (created_at >= '2026-01-01')
-- Rows Removed by Filter: 500000
-- Buffers: shared hit=8234 read=3456
-- -> Hash (...)
-- -> Seq Scan on users u (...)핵심 지표 해석
-- cost: 추정 비용 (시작비용..총비용)
-- actual time: 실제 소요 시간 (ms)
-- rows: 실제 처리한 행 수
-- loops: 반복 횟수
-- Buffers: shared hit(캐시) / read(디스크)
-- 주의해야 할 패턴:
-- 1. Seq Scan + 높은 Rows Removed = 인덱스 필요
-- 2. Nested Loop + 높은 loops = JOIN 방식 변경 필요
-- 3. actual rows와 estimated rows 차이 큼 = 통계 갱신 필요인덱스 전략
복합 인덱스 컬럼 순서
-- 잘못된 순서: 선택도가 낮은 컬럼이 앞
CREATE INDEX idx_bad ON orders (status, user_id, created_at);
-- status는 'pending', 'shipped', 'delivered' 3가지뿐 (선택도 낮음)
-- 올바른 순서: 동등 조건 -> 범위 조건 -> 정렬
CREATE INDEX idx_good ON orders (user_id, status, created_at);
-- 이 인덱스가 최적인 쿼리:
SELECT * FROM orders
WHERE user_id = 123 -- 동등 조건 (첫 번째)
AND status = 'shipped' -- 동등 조건 (두 번째)
AND created_at >= '2026-01-01' -- 범위 조건 (세 번째)
ORDER BY created_at DESC; -- 정렬 (세 번째 컬럼과 동일)부분 인덱스 (Partial Index)
-- 전체 인덱스 대신 필요한 행만 인덱싱
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';
-- 'pending' 상태만 자주 조회한다면 인덱스 크기 대폭 감소
-- NULL 제외 인덱스
CREATE INDEX idx_non_null_email ON users (email)
WHERE email IS NOT NULL;
-- 실전: 최근 30일 활성 주문만 인덱싱
CREATE INDEX idx_recent_active ON orders (user_id, product_id)
WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
AND status NOT IN ('cancelled', 'refunded');커버링 인덱스 (INCLUDE)
-- 인덱스만으로 쿼리 응답 (Index Only Scan)
CREATE INDEX idx_covering ON orders (user_id, status)
INCLUDE (total_amount, created_at);
-- 이 쿼리는 테이블 접근 없이 인덱스만으로 처리
SELECT status, SUM(total_amount), MAX(created_at)
FROM orders
WHERE user_id = 123
GROUP BY status;
-- Index Only Scan 발생!쿼리 안티패턴과 해결법
1. SELECT * 회피
-- 나쁜 예: 불필요한 컬럼까지 전부 로드
SELECT * FROM users WHERE status = 'active';
-- 좋은 예: 필요한 컬럼만 선택
SELECT id, name, email FROM users WHERE status = 'active';
-- 네트워크 전송량 감소 + 커버링 인덱스 활용 가능2. 인덱스 무효화 패턴
-- 나쁜 예: 컬럼에 함수 적용 -> 인덱스 사용 불가
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE LOWER(email) = 'user@test.com';
-- 좋은 예: 범위 조건으로 변환
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- 함수 인덱스 사용
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@test.com';3. N+1 쿼리 제거
-- 나쁜 예: 사용자별 주문을 개별 쿼리로 (N+1)
-- 1. SELECT * FROM users WHERE department = 'sales';
-- 2. 각 사용자마다: SELECT * FROM orders WHERE user_id = ?;
-- 좋은 예: JOIN 또는 서브쿼리로 한 번에
SELECT u.name, o.order_id, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.department = 'sales';
-- 또는 LATERAL JOIN으로 상위 N개만
SELECT u.name, recent.*
FROM users u
CROSS JOIN LATERAL (
SELECT order_id, total_amount, created_at
FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) recent
WHERE u.department = 'sales';4. EXISTS vs IN vs JOIN
-- 대량 데이터에서 EXISTS가 보통 가장 빠름
-- EXISTS: 첫 매칭에서 중단 (short-circuit)
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.status = 'pending'
);
-- IN: 서브쿼리 결과가 작을 때 적합
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'pending');
-- JOIN: 주문 데이터도 함께 필요할 때
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'pending';대량 데이터 처리 패턴
-- 커서 기반 페이지네이션 (OFFSET 회피)
-- 나쁜 예: OFFSET이 커질수록 느려짐
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- 좋은 예: 커서 기반 (Keyset Pagination)
SELECT * FROM orders
WHERE id > 100000 -- 마지막으로 본 id
ORDER BY id
LIMIT 20;
-- 배치 삭제: 한 번에 삭제하면 테이블 락 발생
-- 나쁜 예
DELETE FROM logs WHERE created_at < '2025-01-01';
-- 좋은 예: 배치로 나눠서 삭제
DO $$
BEGIN
LOOP
DELETE FROM logs
WHERE id IN (
SELECT id FROM logs
WHERE created_at < '2025-01-01'
LIMIT 10000
);
EXIT WHEN NOT FOUND;
COMMIT;
END LOOP;
END $$;SQL 최적화는 한 번의 작업이 아니라 지속적인 모니터링과 개선이 필요한 과정입니다. EXPLAIN ANALYZE를 습관적으로 사용하고, 인덱스 전략을 쿼리 패턴에 맞게 설계하며, 안티패턴을 피하는 것만으로도 대부분의 성능 문제를 해결할 수 있습니다. 특히 커버링 인덱스, 부분 인덱스, 커서 기반 페이지네이션은 실무에서 가장 효과가 큰 최적화 기법입니다.