---
name: sql-optimizer
description: |
  트리거: "쿼리 최적화", "slow query", "인덱스 추가", "쿼리가 느려요", "EXPLAIN 분석"
  수행: 슬로우 쿼리 분석 → EXPLAIN/EXPLAIN ANALYZE 해석 → 인덱스 전략 제안 → 서브쿼리/JOIN 리팩토링 → 최적화 쿼리 출력
  출력: 문제 진단 요약 + 개선된 SQL + 인덱스 DDL + 예상 성능 향상 근거
---

# SQL Optimizer

## 목적

느린 SQL 쿼리를 분석하여 실행 계획(EXPLAIN) 해석, 인덱스 전략 설계, 쿼리 구조 리팩토링을 통해 성능을 최적화한다.

## 실행 절차

### 1단계: 쿼리 및 컨텍스트 수집

사용자로부터 다음을 확인한다.
- 원본 SQL 쿼리
- DBMS 종류 (MySQL, PostgreSQL, SQLite 등)
- 테이블 스키마 (DDL 또는 컬럼 목록)
- EXPLAIN / EXPLAIN ANALYZE 출력 (있는 경우)
- 현재 인덱스 목록
- 데이터 규모 (행 수, 테이블 크기)

### 2단계: EXPLAIN 출력 분석

EXPLAIN 결과에서 다음 항목을 확인한다.

**MySQL EXPLAIN 핵심 컬럼:**
| 컬럼 | 위험 신호 |
|------|-----------|
| `type` | ALL (풀스캔), index (인덱스 풀스캔) |
| `key` | NULL (인덱스 미사용) |
| `rows` | 예상 행 수가 실제 결과보다 과도하게 많음 |
| `Extra` | Using filesort, Using temporary |

**PostgreSQL EXPLAIN ANALYZE 핵심 항목:**
- `Seq Scan` → 풀 테이블 스캔 (인덱스 부재)
- `Sort` with high cost → ORDER BY 최적화 필요
- `Hash Join` vs `Nested Loop` 선택 적절성
- `actual rows` vs `estimated rows` 괴리 → 통계 갱신 필요

### 3단계: 인덱스 전략 설계

**단일 컬럼 인덱스:**
```sql
-- WHERE 조건 컬럼
CREATE INDEX idx_orders_status ON orders(status);

-- 외래 키 (JOIN 성능)
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
```

**복합 인덱스 (컬럼 순서 원칙: 선택도 높은 것 → 낮은 것):**
```sql
-- WHERE + ORDER BY 조합
CREATE INDEX idx_orders_user_created
  ON orders(user_id, created_at DESC);

-- 커버링 인덱스 (SELECT 컬럼까지 포함)
CREATE INDEX idx_orders_covering
  ON orders(status, created_at)
  INCLUDE (total_amount, user_id);  -- PostgreSQL
```

**인덱스 설계 체크리스트:**
- WHERE 절에 자주 등장하는 컬럼
- JOIN ON 조건 컬럼 (양쪽 테이블)
- ORDER BY / GROUP BY 컬럼
- 카디널리티가 낮은 컬럼 단독 인덱스 지양 (status ENUM 등)
- 쓰기 부하 고려 (인덱스가 많을수록 INSERT/UPDATE 느려짐)

### 4단계: 쿼리 리팩토링

**서브쿼리 → JOIN 변환:**
```sql
-- Before: 상관 서브쿼리 (N+1 위험)
SELECT u.id, u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- After: LEFT JOIN + GROUP BY
SELECT u.id, u.name, 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;
```

**IN 서브쿼리 → EXISTS / JOIN 변환:**
```sql
-- Before: IN 서브쿼리 (대용량 시 풀스캔)
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

-- After: EXISTS (인덱스 활용)
SELECT p.* FROM products p
WHERE EXISTS (
  SELECT 1 FROM categories c
  WHERE c.id = p.category_id AND c.active = 1
);
```

**페이지네이션 최적화 (Keyset Pagination):**
```sql
-- Before: OFFSET 방식 (N이 클수록 느려짐)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- After: Keyset (커서 기반)
SELECT * FROM posts
WHERE created_at < '2024-01-15 12:00:00'
ORDER BY created_at DESC
LIMIT 20;
```

**DISTINCT 제거:**
```sql
-- Before: DISTINCT로 중복 제거
SELECT DISTINCT u.id, u.name FROM users u
JOIN orders o ON o.user_id = u.id;

-- After: EXISTS로 대체 (더 빠름)
SELECT u.id, u.name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
```

### 5단계: 추가 최적화 기법

**파티셔닝 (대용량 테이블):**
```sql
-- PostgreSQL 범위 파티셔닝
CREATE TABLE orders (
  id BIGINT,
  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');
```

**통계 갱신 (PostgreSQL):**
```sql
ANALYZE orders;                    -- 통계만 갱신
VACUUM ANALYZE orders;             -- 데드 튜플 정리 + 통계
```

**쿼리 힌트 (MySQL):**
```sql
SELECT /*+ INDEX(orders idx_orders_status) */ *
FROM orders
WHERE status = 'pending';
```

## 출력 형식

```
## SQL 최적화 분석 리포트

### 문제 진단
- [CRITICAL] type=ALL: orders 테이블 풀스캔 (예상 rows: 1,200,000)
- [WARNING] Using filesort: ORDER BY created_at에 인덱스 없음
- [INFO] 상관 서브쿼리 N+1 패턴 감지

### 인덱스 추가 권고
\`\`\`sql
-- 예상 개선: 풀스캔 → 인덱스 스캔, rows 1.2M → 500
CREATE INDEX idx_orders_status_created
  ON orders(status, created_at DESC);
\`\`\`

### 최적화된 쿼리
\`\`\`sql
-- [개선된 쿼리]
\`\`\`

### 예상 성능 향상
- 실행 시간: ~3,200ms → ~45ms (약 70x 개선)
- 스캔 행 수: 1,200,000 → 500
```

## 사용 예시

**입력:**
```
이 쿼리가 너무 느려요. MySQL 5.7, orders 테이블 200만 건.

SELECT u.name, COUNT(o.id) as cnt
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01'
GROUP BY u.id
ORDER BY cnt DESC
LIMIT 10;

EXPLAIN 결과: type=ALL, rows=2000000, Extra=Using filesort; Using temporary
```

**출력:**
```
## SQL 최적화 분석 리포트

### 문제 진단
- [CRITICAL] orders 테이블 풀스캔 (type=ALL, 2,000,000 rows)
- [CRITICAL] Using temporary + Using filesort → 임시 테이블 정렬 발생
- [INFO] status + created_at 복합 필터에 인덱스 없음

### 인덱스 추가 권고
\`\`\`sql
-- orders: WHERE + ORDER BY 커버링 인덱스
CREATE INDEX idx_orders_status_created_user
  ON orders(status, created_at, user_id);

-- users: GROUP BY 기준 인덱스 (이미 PK라면 불필요)
-- CREATE INDEX idx_users_id ON users(id);  -- 보통 PK로 존재
\`\`\`

### 최적화된 쿼리
\`\`\`sql
SELECT u.name, o.cnt
FROM users u
JOIN (
  SELECT user_id, COUNT(id) AS cnt
  FROM orders
  WHERE status = 'completed'
    AND created_at >= '2024-01-01'
  GROUP BY user_id
  ORDER BY cnt DESC
  LIMIT 10
) o ON o.user_id = u.id
ORDER BY o.cnt DESC;
\`\`\`

### 예상 성능 향상
- 스캔 행 수: 2,000,000 → ~12,000 (인덱스 범위 스캔)
- filesort 대상: 2,000,000 → 집계 후 상위 10건
- 예상 실행 시간: ~4,500ms → ~80ms
```

## 주의사항

- 인덱스는 읽기 성능을 높이지만 쓰기(INSERT/UPDATE/DELETE) 성능을 낮춘다. 쓰기 빈도가 높은 테이블에는 신중하게 추가한다.
- EXPLAIN 없이 쿼리만 받은 경우, 스키마와 데이터 규모를 반드시 질문한 후 분석한다.
- MySQL 8.0+, PostgreSQL 14+ 등 버전에 따라 사용 가능한 힌트·기능이 다르므로 버전을 확인한다.
- 파티셔닝은 운영 중인 테이블에 적용 시 중단 시간(downtime)이 발생할 수 있음을 경고한다.
- 통계(ANALYZE)가 오래된 경우 EXPLAIN 예측 행 수가 부정확하므로 먼저 갱신을 권고한다.