[Tip] 실무에서 자주 쓰는 SQL 최적화 패턴 10가지

0. 개요

SQL 쿼리가 느릴 때 반드시 알아야 할 10가지 최적화 패턴! 인덱스 설계, 실행 계획 분석, 조인, 페이지네이션, 파티셔닝 등 MySQL / PostgreSQL 실무 기준으로 정리했습니다.

---

1. 핵심 요약 – SQL 성능 튜닝의 본질

SQL 최적화의 핵심은 단순합니다.
“필요한 데이터만, 최소한의 비용으로 읽는 것.”
즉, 불필요한 스캔/조인/정렬을 제거하고, 인덱스로 효율적으로 접근해야 합니다.

핵심 키워드	설명
인덱스 설계	WHERE / JOIN / ORDER 조건을 커버하는 인덱스 구성
실행 계획 (EXPLAIN)	풀스캔, 정렬, 조인 순서 등 비용 확인
리라이트	함수 제거, 서브쿼리→JOIN 변환, LIMIT 최적화
관측 & 모니터링	슬로우 쿼리 로그, 성능 스키마, auto_explain

---

2. 실무에서 자주 쓰는 SQL 최적화 패턴 10가지

- 복합 인덱스 (Composite Index) 설계

WHEN: WHERE + ORDER BY + JOIN 조합이 많은 쿼리
HOW: 선택도 높은 컬럼부터 복합 인덱스로 커버링

CREATE INDEX ix_order_user_date ON orders(user_id, created_at DESC);
SELECT * FROM orders
 WHERE user_id = ? AND created_at >= ?
 ORDER BY created_at DESC LIMIT 50;

주의: 조건 순서가 인덱스 순서와 맞아야 합니다.

---

- 커버링 인덱스 (Covering Index)

WHEN: SELECT 컬럼이 적고, 자주 읽는 리스트형 쿼리
HOW: SELECT 컬럼을 인덱스에 포함시켜 테이블 액세스 제거

CREATE INDEX ix_post_list ON posts(topic_id, created_at DESC) INCLUDE (title, author);
SELECT title, author, created_at
  FROM posts
 WHERE topic_id = $1
 ORDER BY created_at DESC
 LIMIT 20;

---

- 페이지네이션 최적화 (Seek 방식)

문제: OFFSET이 커질수록 성능 급락
해결: 커서 기반 조회로 시크(Seek) 방식 전환

-- 나쁨
SELECT * FROM logs ORDER BY id DESC LIMIT 50 OFFSET 100000;

-- 개선
SELECT * FROM logs WHERE id < :last_seen ORDER BY id DESC LIMIT 50;

---

- 조인 순서 고정 및 드라이빙 테이블 최적화

WHEN: 옵티마이저가 큰 테이블을 먼저 스캔할 때
HOW: 선택도 높은 테이블을 먼저 조인

SELECT u.id, o.id
  FROM users u
  JOIN orders o ON o.user_id = u.id
 WHERE u.country = 'KR' AND u.status = 'ACTIVE';

---

- 함수/표현식 제거 – 인덱스 타게 하기

문제: WHERE 절에 함수 사용 시 인덱스 무효화
해결: 범위 조건으로 리라이트

-- 나쁨
WHERE DATE(created_at) = '2025-10-01';

-- 좋음
WHERE created_at >= '2025-10-01' AND created_at < '2025-10-02';

---

- LIKE 최적화 및 전문 검색 전환

문제: LIKE '%keyword%' → 풀스캔
해결: 접두검색 또는 FullText 인덱스로 변환

ALTER TABLE docs ADD FULLTEXT KEY ft_body (title, body);
SELECT * FROM docs
 WHERE MATCH(title, body) AGAINST('+mysql +튜닝' IN BOOLEAN MODE);

---

- 서브쿼리 → JOIN / EXISTS 변환

문제: 상관 서브쿼리로 반복 실행 발생
해결: JOIN 또는 EXISTS로 변환

SELECT u.*
  FROM users u
 WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id AND o.state='PAID');

---

- 집계 최적화 (Precompute / Rollup)

WHEN: GROUP BY 연산이 대량 데이터에서 자주 수행될 때
HOW: 미리 집계 테이블을 만들어 읽기만 수행

CREATE TABLE daily_sales(product_id INT, day DATE, amount DECIMAL, PRIMARY KEY(product_id, day));
SELECT product_id, SUM(amount)
  FROM daily_sales
 WHERE day BETWEEN '2025-10-01' AND '2025-10-10'
 GROUP BY product_id;

---

- 파티셔닝 / 샤딩으로 범위 축소

WHEN: 테이블 수억 행 이상, 특정 기간만 조회
HOW: RANGE 파티션으로 I/O 범위 줄이기

PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p2025_10 VALUES LESS THAN (TO_DAYS('2025-11-01')),
  PARTITION pMAX VALUES LESS THAN MAXVALUE
);

---

- 트랜잭션 / 락 최소화

WHEN: 동시 업데이트가 많아 Deadlock 자주 발생
HOW: 트랜잭션 범위 축소 + 배치 분할

UPDATE items SET flag=1 WHERE id > :cursor LIMIT 500;

---

3. 실무 사례

사례 A) 주문 리스트 무한 스크롤 느림

• 문제: OFFSET 커질수록 느려짐
• 해결: 커서 기반 Seek 방식 + 복합 인덱스 적용
• 효과: 응답시간 3.2초 → 180ms, Rows 10만 → 60

사례 B) 검색 페이지 CPU 급등

• 문제: LIKE '%키워드%'로 풀스캔
• 해결: FullText 인덱스 적용
• 효과: QPS 3배 증가, CPU 40% 절감

---

4️. 고급 팁 & 체크리스트

• EXPLAIN으로 type=ALL(풀스캔)인지 확인
• Using filesort, Using temporary 발생 여부 체크
• JOIN 키 타입/콜레이션 일치 여부 점검
• 불필요한 인덱스 과다 등록 시 쓰기 성능 저하 주의
• 커넥션 풀/트랜잭션 타임아웃/격리수준 점검

도구 추천

• MySQL: EXPLAIN, SHOW PROFILE, Performance Schema
• PostgreSQL: EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements
• 대시보드: Grafana, Kibana, Percona PMM

---

5️. 요약 및 결론 – 80% 성능은 20% 최적화로

패턴	핵심 요약
인덱스 설계	WHERE + ORDER BY + JOIN 조건 커버
커서 페이지네이션	OFFSET 제거, Seek 방식 전환
FullText 검색	LIKE '%keyword%' 대체
서브쿼리 변환	JOIN / EXISTS 로 변경
프리컴퓨팅	집계 결과 사전 저장
파티셔닝	쿼리 범위 줄이기

결론: “인덱스 설계 + 쿼리 리라이트 + 실행계획 검증” 이 세 가지가 SQL 성능 최적화의 핵심입니다.