SQL: 슬로우 쿼리 패턴

※ SQL: Slow Query Patterns.

 

안녕하세요. 듀스트림입니다.

 

요즘 그냥 순간순간이 퀘스트의 연속입니다.

정보보호 심사 시즌도 시작해서 하루에 n개씩 처리해도 퀘스트가 계속 쌓이고 있습니다.

그래도 일할 때는 바쁜 게 재밌습니다. (물론 휴식도 매우 중요합니다.)

 

오늘 포스팅에서는 어렵지 않지만, 꼭 알아야 하는 슬로우 쿼리 패턴에 대해 정리해 보겠습니다.

---

1. 데이터 접근 패턴 관련

• 풀 테이블 스캔 (Full Table Scan)
  
  • 인덱스를 활용하지 못하고 전체 데이터를 읽는 경우.
    (예: WHERE 조건에 함수 적용, 타입 불일치, 와일드카드 %값)
• 비효율적인 조인 (Join)
  • 큰 테이블끼리의 Nested Loop Join
  • 조인 조건에 인덱스가 없어 Hash Join → Disk Spill 발생.
  • 다대다(M:N) 조인으로 중간 결과가 폭증
• 서브쿼리 남용
  • 상관 서브쿼리(Correlated Subquery)로 인한 반복 실행.
  • 동일 조건을 여러 번 조회하는 중첩 서브쿼리

---

2. 집계/정렬 관련

• GROUP BY / DISTINCT 남용
  
  • 인덱스 없이 대용량 데이터 전체를 정렬/집계해야 하는 경우.
• ORDER BY + LIMIT
  
  • 정렬을 전부 수행한 뒤 상위 일부만 반환 → 쓸데없는 정렬 부하.
    (커버링 인덱스나 적절한 인덱스로 개선 가능)
• 윈도우 함수(Window Function)
  
  • 파티션이 크거나 정렬 조건이 복잡할 때 쿼리 시간이 급증.

---

3. 트랜잭션/동시성 관련

• 락 대기(Lock Waits)
  
  • 단순 SELECT도 다른 세션의 EXCLUSIVE LOCK 때문에 느려질 수 있음.
• 긴 트랜잭션
  
  • 커밋/롤백 지연으로 VACUUM/UNDO 정리 불가 → 성능 저하.

---

4. 쿼리 작성 습관 문제

• SELECT * 패턴
  • 불필요한 컬럼까지 다 읽어 I/O 증가.
• 불필요한 함수 호출
  • WHERE 절에서 CAST, SUBSTR, UPPER 등 사용 → 인덱스 미사용.
  • EXTRACT, DATE_FORMAT 등도 마찬가지.
• CASE문 중첩 남용
  • 복잡한 조건문이 쿼리 플랜 최적화에 방해.

---

5. 환경/설정 이슈

• work_mem, sort_buffer 크기 부족
  
  • 정렬/해시 작업이 디스크 Spill로 넘어감.
• 통계 정보 갱신 안됨 (ANALYZE 부족)
  
  • 옵티마이저가 잘못된 실행 계획 선택.
• 파라미터 미스매치
  • PostgreSQL: random_page_cost, effective_cache_size 잘못 설정.
  • MySQL: join_buffer_size, tmp_table_size 부족.

---

정리하자면, 슬로우 쿼리의 전형적인 패턴은 '인덱스 미활용 + 대량 데이터 처리 + 잘못된 실행 계획 + 환경 설정 부족' 조합에서 발생합니다.

 

오늘은 여기까지~