성능의 핵심: 쿼리 최적화(Query Optimization) 완벽 가이드

"느린 쿼리 하나가 전체 시스템을 마비시킨다." 데이터베이스 성능의 80%는 쿼리에서 결정됩니다. 아무리 좋은 하드웨어, 완벽한 아키텍처라도 잘못된 쿼리는 모든 것을 무용지물로 만듭니다. 쿼리 최적화는 단순히 빠른 응답 시간을 넘어, 리소스 효율성, 확장성, 사용자 경험에 직접적인 영향을 미칩니다. 이 글에서는 느린 쿼리를 식별하고, 분석하고, 최적화하는 실전 기법을 완벽하게 정리합니다.

쿼리 최적화란?

정의

쿼리 최적화는 SQL 쿼리의 실행 시간을 단축하고, 리소스 사용량을 최소화하여 데이터베이스 성능을 향상시키는 과정입니다.

왜 필요한가?

문제 시나리오:

sql

-- 느린 쿼리 (3초)
SELECT * FROM orders o
WHERE o.customer_id = 12345
  AND o.order_date >= '2024-01-01'
  AND o.status = 'COMPLETED';

-- 문제:
-- 1. 인덱스 없음
-- 2. SELECT * (불필요한 컬럼)
-- 3. 비효율적인 조건

최적화 후:

sql

-- 빠른 쿼리 (0.05초)
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345
  AND order_date >= '2024-01-01'
  AND status = 'COMPLETED';

-- + 복합 인덱스: (customer_id, order_date, status)

성능 차이:

응답 시간: 3000ms → 50ms (60배 향상)
읽은 행: 1,000,000 → 15 (66,666배 감소)
CPU 사용: 95% → 5%

느린 쿼리 식별

1. Slow Query Log (MySQL)

ini

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1  # 1초 이상 걸리는 쿼리
log_queries_not_using_indexes = 1

로그 분석:

bash

# 느린 쿼리 요약
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# 출력 예시:
Count: 1234  Time=3.42s (4221s)  Lock=0.00s (0s)  Rows=150.0 (185100)
  SELECT * FROM orders WHERE customer_id = N AND status = 'S'

2. pg_stat_statements (PostgreSQL)

sql

-- 확장 활성화
CREATE EXTENSION pg_stat_statements;

-- 가장 느린 쿼리 Top 10
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 특정 쿼리 상세 분석
SELECT 
    query,
    calls,
    total_exec_time / 1000 as total_sec,
    mean_exec_time / 1000 as avg_sec,
    stddev_exec_time / 1000 as stddev_sec,
    rows / calls as avg_rows
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

3. 애플리케이션 레벨 모니터링

java

@Component
@Aspect
public class QueryPerformanceMonitor {
    
    @Around("execution(* org.springframework.data.repository.Repository+.*(..))")
    public Object monitorQuery(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();
        String methodName = joinPoint.getSignature().toShortString();
        
        try {
            Object result = joinPoint.proceed();
            long duration = System.currentTimeMillis() - startTime;
            
            if (duration > 1000) {  // 1초 이상
                log.warn("Slow query detected: {} took {}ms", 
                    methodName, duration);
                
                // 메트릭 수집
                meterRegistry.timer(
                    "database.query.slow",
                    "method", methodName
                ).record(duration, TimeUnit.MILLISECONDS);
            }
            
            return result;
        } catch (Exception e) {
            throw e;
        }
    }
}

EXPLAIN 분석

MySQL EXPLAIN

sql

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01';

-- 결과:
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

주요 컬럼 분석:

1. type (접근 방식):

성능 순서: system > const > eq_ref > ref > range > index > ALL

const       : PK 또는 UNIQUE 인덱스로 단일 행 조회 ⭐ 최고
eq_ref      : JOIN에서 PK 또는 UNIQUE 인덱스 사용 ⭐
ref         : 비-UNIQUE 인덱스로 여러 행 조회 ✓ 좋음
range       : 인덱스 범위 스캔 ✓ 괜찮음
index       : 인덱스 풀 스캔 △ 주의
ALL         : 테이블 풀 스캔 ✗ 나쁨

2. rows (예상 행 수):

sql

-- 나쁜 예
rows = 1000000  -- 100만 행 스캔

-- 좋은 예
rows = 15  -- 15행만 스캔

3. Extra (추가 정보):

Using index        : 커버링 인덱스 (인덱스만으로 해결) ⭐ 최고
Using where        : WHERE 조건 필터링 ✓ 정상
Using temporary    : 임시 테이블 사용 △ 주의
Using filesort     : 파일 정렬 △ 주의
Using join buffer  : 조인 버퍼 사용 △ 주의

PostgreSQL EXPLAIN ANALYZE

sql

EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01';

-- 결과:
Seq Scan on orders  (cost=0.00..2500.00 rows=100 width=100) 
                    (actual time=0.020..30.542 rows=15 loops=1)
  Filter: ((customer_id = 12345) AND (order_date >= '2024-01-01'::date))
  Rows Removed by Filter: 99985
Planning Time: 0.123 ms
Execution Time: 30.589 ms

분석:

Seq Scan: 순차 스캔 (인덱스 없음) ✗
cost=0.00..2500.00: 예상 비용
actual time=30.542ms: 실제 시간
Rows Removed by Filter: 99985: 99,985개 행 필터링 (비효율)

인덱스 최적화

1. 단일 인덱스

sql

-- 인덱스 없을 때
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- type: ALL, rows: 1000000

-- 인덱스 생성
CREATE INDEX idx_customer_id ON orders(customer_id);

-- 인덱스 후
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- type: ref, rows: 15

2. 복합 인덱스 (중요!)

sql

-- 쿼리
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01'
  AND status = 'COMPLETED';

-- 나쁜 예: 단일 인덱스 3개
CREATE INDEX idx_customer ON orders(customer_id);
CREATE INDEX idx_date ON orders(order_date);
CREATE INDEX idx_status ON orders(status);
-- 문제: 하나의 인덱스만 사용됨

-- 좋은 예: 복합 인덱스
CREATE INDEX idx_customer_date_status 
ON orders(customer_id, order_date, status);

-- 인덱스 컬럼 순서 규칙:
-- 1. 등호(=) 조건을 앞에
-- 2. 범위(>, <, BETWEEN) 조건을 중간에
-- 3. 정렬(ORDER BY) 컬럼을 마지막에

복합 인덱스 순서 예제:

sql

-- 쿼리 1: WHERE customer_id = ? AND order_date >= ?
CREATE INDEX idx_1 ON orders(customer_id, order_date);  ✓

-- 쿼리 2: WHERE order_date >= ? AND customer_id = ?
CREATE INDEX idx_2 ON orders(customer_id, order_date);  ✓ (순서 중요!)

-- 쿼리 3: WHERE status = ? ORDER BY order_date
CREATE INDEX idx_3 ON orders(status, order_date);  ✓

-- 쿼리 4: WHERE order_date >= ? ORDER BY customer_id
CREATE INDEX idx_4 ON orders(order_date, customer_id);  ✓

3. 커버링 인덱스

인덱스만으로 쿼리를 처리 (테이블 접근 불필요)

sql

-- 일반 인덱스
CREATE INDEX idx_customer ON orders(customer_id);

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345;

-- 인덱스로 customer_id 찾기
-- → 테이블에서 order_id, order_date, total_amount 읽기 (추가 I/O)

-- 커버링 인덱스
CREATE INDEX idx_customer_covering 
ON orders(customer_id, order_id, order_date, total_amount);

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345;

-- 인덱스만으로 모든 데이터 조회 가능! (빠름)
-- Extra: Using index

4. 부분 인덱스 (PostgreSQL)

sql

-- 전체 인덱스 (비효율)
CREATE INDEX idx_status ON orders(status);

-- 자주 조회하는 상태만 인덱스
CREATE INDEX idx_active_orders 
ON orders(customer_id, order_date)
WHERE status IN ('PENDING', 'PROCESSING');

-- 장점:
-- - 인덱스 크기 감소
-- - 쓰기 성능 향상
-- - 메모리 효율

5. 함수 기반 인덱스

sql

-- 문제: 함수 사용 시 인덱스 무효화
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 인덱스 사용 불가!

-- 해결: 함수 기반 인덱스
CREATE INDEX idx_email_lower ON users(LOWER(email));

-- 또는 계산 컬럼 인덱스
ALTER TABLE users ADD email_lower VARCHAR(255) 
GENERATED ALWAYS AS (LOWER(email)) STORED;

CREATE INDEX idx_email_lower ON users(email_lower);

쿼리 작성 최적화

1. SELECT * 지양

sql

-- 나쁜 예
SELECT * FROM orders WHERE order_id = 12345;
-- 모든 컬럼 조회 (50개 컬럼)

-- 좋은 예
SELECT order_id, customer_id, order_date, total_amount
FROM orders WHERE order_id = 12345;
-- 필요한 4개 컬럼만

-- 성능 차이:
-- - 네트워크 트래픽: 5KB → 200B
-- - 커버링 인덱스 가능성 증가

2. WHERE 조건 최적화

sql

-- 나쁜 예: 함수 사용
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;
-- 인덱스 사용 불가! (함수 적용)

-- 좋은 예: 범위 조건
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';
-- 인덱스 사용 가능!

-- 나쁜 예: OR 조건
SELECT * FROM orders 
WHERE customer_id = 123 OR customer_id = 456;
-- 인덱스 비효율

-- 좋은 예: IN 절
SELECT * FROM orders 
WHERE customer_id IN (123, 456);
-- 인덱스 효율적 사용

-- 나쁜 예: LIKE 시작 와일드카드
SELECT * FROM products 
WHERE name LIKE '%phone%';
-- 인덱스 사용 불가!

-- 좋은 예: LIKE 끝 와일드카드
SELECT * FROM products 
WHERE name LIKE 'phone%';
-- 인덱스 사용 가능!

3. JOIN 최적화

sql

-- 나쁜 예: 서브쿼리
SELECT o.*, 
       (SELECT c.name FROM customers c WHERE c.id = o.customer_id) as customer_name
FROM orders o
WHERE o.order_date >= '2024-01-01';
-- 각 행마다 서브쿼리 실행! (N+1)

-- 좋은 예: JOIN
SELECT o.*, c.name as customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';
-- 단일 조인 쿼리

-- JOIN 순서 최적화
-- 작은 테이블을 먼저 조인
SELECT o.*, p.name
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id  -- 큰 테이블
INNER JOIN products p ON oi.product_id = p.id    -- 작은 테이블
WHERE o.customer_id = 12345;

-- 최적화: 작은 테이블부터
SELECT o.*, p.name
FROM orders o
INNER JOIN products p ON ... -- 작은 테이블 먼저
INNER JOIN order_items oi ON ... -- 큰 테이블
WHERE o.customer_id = 12345;

4. EXISTS vs IN

sql

-- IN: 서브쿼리 결과를 메모리에 로드
SELECT * FROM customers c
WHERE c.id IN (
    SELECT DISTINCT customer_id FROM orders WHERE order_date >= '2024-01-01'
);
-- 서브쿼리 결과가 크면 메모리 부담

-- EXISTS: 존재 여부만 확인 (효율적)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
      AND o.order_date >= '2024-01-01'
);
-- 첫 번째 매치에서 즉시 반환

-- 규칙:
-- - 서브쿼리 결과가 작으면: IN
-- - 서브쿼리 결과가 크면: EXISTS
-- - 존재 여부만 확인: EXISTS

5. LIMIT과 OFFSET

sql

-- 나쁜 예: 큰 OFFSET
SELECT * FROM orders 
ORDER BY order_date DESC
LIMIT 20 OFFSET 100000;
-- 100,020개 행을 읽고 100,000개 버림 (비효율)

-- 좋은 예: Seek Method (Keyset Pagination)
SELECT * FROM orders 
WHERE order_date < '2024-01-01'  -- 마지막 페이지의 날짜
ORDER BY order_date DESC
LIMIT 20;
-- 필요한 20개만 읽음

-- 또는: 커서 기반 페이징
SELECT * FROM orders 
WHERE id < 12345  -- 마지막 ID
ORDER BY id DESC
LIMIT 20;

6. GROUP BY 최적화

sql

-- 나쁜 예: 모든 행 GROUP BY
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;
-- 전체 테이블 스캔

-- 좋은 예: WHERE로 먼저 필터링
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;
-- 필터 후 GROUP BY

-- 인덱스 활용
CREATE INDEX idx_date_customer ON orders(order_date, customer_id);

-- HAVING vs WHERE
-- 나쁜 예: HAVING으로 필터링
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING customer_id = 12345;
-- 모든 그룹을 만들고 필터링

-- 좋은 예: WHERE로 먼저 필터링
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE customer_id = 12345
GROUP BY customer_id;
-- 필터 후 그룹화

실행 계획 기반 최적화

1. 통계 정보 업데이트

sql

-- MySQL
ANALYZE TABLE orders;

-- PostgreSQL
ANALYZE orders;

-- 주기적 업데이트 (cron)
-- 데이터 변화가 많은 테이블은 매일
-- 안정적인 테이블은 주간

2. 인덱스 힌트 (조심스럽게 사용)

sql

-- MySQL: 특정 인덱스 강제
SELECT * FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01';

-- PostgreSQL: 힌트 없음 (옵티마이저 신뢰)

-- 주의: 힌트는 최후의 수단
-- 옵티마이저가 잘못 선택하는 경우만 사용

3. 쿼리 재작성

sql

-- 원본 쿼리 (느림)
SELECT * FROM orders o
WHERE o.customer_id IN (
    SELECT c.id FROM customers c 
    WHERE c.city = 'Seoul'
)
AND o.order_date >= '2024-01-01';

-- 재작성 1: JOIN으로 변환
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'Seoul'
  AND o.order_date >= '2024-01-01';

-- 재작성 2: EXISTS로 변환
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.id = o.customer_id 
      AND c.city = 'Seoul'
)
AND o.order_date >= '2024-01-01';

-- EXPLAIN으로 비교하여 가장 빠른 것 선택

대용량 데이터 처리

1. 배치 처리

java

// 나쁜 예: 한 번에 모두 조회
List<Order> orders = orderRepository.findAll();
for (Order order : orders) {
    processOrder(order);  // OutOfMemoryError!
}

// 좋은 예: 페이징 처리
@Transactional
public void processAllOrders() {
    int pageSize = 1000;
    int pageNumber = 0;
    Page<Order> page;
    
    do {
        page = orderRepository.findAll(
            PageRequest.of(pageNumber++, pageSize)
        );
        
        for (Order order : page.getContent()) {
            processOrder(order);
        }
        
        entityManager.clear();  // 메모리 정리
        
    } while (page.hasNext());
}

2. 벌크 연산

sql

-- 나쁜 예: 개별 UPDATE
UPDATE orders SET status = 'COMPLETED' WHERE id = 1;
UPDATE orders SET status = 'COMPLETED' WHERE id = 2;
...
-- N개 쿼리

-- 좋은 예: 벌크 UPDATE
UPDATE orders 
SET status = 'COMPLETED'
WHERE id IN (1, 2, 3, ..., 1000);
-- 1개 쿼리

java

// JPA 벌크 연산
@Modifying
@Query("UPDATE Order o SET o.status = :status WHERE o.id IN :ids")
int bulkUpdateStatus(@Param("status") OrderStatus status, 
                     @Param("ids") List<Long> ids);

// 사용
List<Long> orderIds = Arrays.asList(1L, 2L, 3L, ...);
int updatedCount = orderRepository.bulkUpdateStatus(
    OrderStatus.COMPLETED, 
    orderIds
);

3. 파티셔닝

sql

-- 날짜별 파티셔닝 (MySQL)
CREATE TABLE orders (
    order_id BIGINT,
    order_date DATE,
    customer_id BIGINT,
    ...
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 쿼리 시 특정 파티션만 스캔
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';
-- p2024 파티션만 스캔 (빠름)

캐싱 전략

1. 쿼리 결과 캐싱

java

@Service
public class ProductService {
    
    @Cacheable(value = "products", key = "#id")
    public Product getProduct(Long id) {
        return productRepository.findById(id).orElseThrow();
    }
    
    @CacheEvict(value = "products", key = "#product.id")
    public Product updateProduct(Product product) {
        return productRepository.save(product);
    }
}

2. Materialized View

sql

-- 복잡한 집계 쿼리 (느림)
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id, c.name;

-- Materialized View 생성 (PostgreSQL)
CREATE MATERIALIZED VIEW customer_stats AS
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id, c.name;

-- 인덱스 생성
CREATE INDEX idx_customer_stats ON customer_stats(customer_id);

-- 주기적 갱신
REFRESH MATERIALIZED VIEW customer_stats;

-- 조회 (빠름)
SELECT * FROM customer_stats WHERE customer_id = 12345;

모니터링과 분석

1. 쿼리 프로파일링

sql

-- MySQL: 쿼리 프로파일링
SET profiling = 1;

SELECT * FROM orders WHERE customer_id = 12345;

SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 결과:
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000070 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000032 |
| init                 | 0.000024 |
| System lock          | 0.000010 |
| optimizing           | 0.000015 |
| statistics           | 0.000025 |
| preparing            | 0.000018 |
| executing            | 0.000007 |
| Sending data         | 0.025342 |  -- 대부분의 시간
| end                  | 0.000009 |
| query end            | 0.000008 |
| closing tables       | 0.000008 |
| freeing items        | 0.000012 |
| cleaning up          | 0.000013 |
+----------------------+----------+

2. 성능 메트릭

java

@Component
public class QueryMetrics {
    
    @Autowired
    private MeterRegistry meterRegistry;
    
    public void recordQuery(String queryType, long durationMs, long rowCount) {
        // 응답 시간
        meterRegistry.timer(
            "database.query.duration",
            "type", queryType
        ).record(durationMs, TimeUnit.MILLISECONDS);
        
        // 행 수
        meterRegistry.counter(
            "database.query.rows",
            "type", queryType
        ).increment(rowCount);
        
        // 느린 쿼리
        if (durationMs > 1000) {
            meterRegistry.counter(
                "database.query.slow",
                "type", queryType
            ).increment();
        }
    }
}

3. Prometheus 쿼리

promql

# 평균 쿼리 시간
avg(rate(database_query_duration_seconds_sum[5m])) by (type)

# P95 쿼리 시간
histogram_quantile(0.95, 
  rate(database_query_duration_seconds_bucket[5m])
)

# 느린 쿼리 비율
rate(database_query_slow_total[5m]) / 
rate(database_query_total[5m])

# 쿼리당 평균 행 수
rate(database_query_rows_total[5m]) / 
rate(database_query_total[5m])

실전 체크리스트

개발 단계

SELECT * 지양, 필요한 컬럼만
WHERE 조건에 함수 사용 지양
JOIN 대신 서브쿼리 지양
LIMIT + OFFSET 대신 Keyset Pagination
인덱스 설계 (복합 인덱스 순서)

테스트 단계

EXPLAIN으로 실행 계획 확인
쿼리 로깅 활성화
대용량 데이터 테스트
동시성 테스트
인덱스 효과 측정

배포 단계

인덱스 생성 (OFF-PEAK 시간)
통계 정보 업데이트
Slow Query Log 모니터링
성능 메트릭 수집
롤백 계획 수립

운영 단계

주기적 인덱스 리빌드
통계 정보 자동 업데이트
느린 쿼리 알림
쿼리 성능 트렌드 분석
인덱스 사용률 모니터링

실전 안티패턴

안티패턴 1: 과도한 인덱스

sql

-- 나쁜 예: 모든 컬럼에 인덱스
CREATE INDEX idx_1 ON orders(customer_id);
CREATE INDEX idx_2 ON orders(order_date);
CREATE INDEX idx_3 ON orders(status);
CREATE INDEX idx_4 ON orders(total_amount);
CREATE INDEX idx_5 ON orders(created_at);
...

-- 문제:
-- - 쓰기 성능 저하 (INSERT/UPDATE/DELETE 시 모든 인덱스 갱신)
-- - 디스크 공간 낭비
-- - 메모리 낭비

-- 좋은 예: 실제 쿼리 패턴 기반
CREATE INDEX idx_customer_date_status 
ON orders(customer_id, order_date, status);
-- 3개 컬럼을 조합한 하나의 인덱스

안티패턴 2: SELECT * 남용

java

// 나쁜 예
@Query("SELECT o FROM Order o WHERE o.customer.id = :customerId")
List<Order> findByCustomerId(@Param("customerId") Long customerId);

// 모든 연관 엔티티까지 조회 (N+1)

// 좋은 예
@Query("SELECT new com.example.OrderDto(o.id, o.orderDate, o.totalAmount) " +
       "FROM Order o WHERE o.customer.id = :customerId")
List<OrderDto> findOrderDtoByCustomerId(@Param("customerId") Long customerId);

안티패턴 3: 서브쿼리 남용

sql

-- 나쁜 예
SELECT 
    o.*,
    (SELECT c.name FROM customers c WHERE c.id = o.customer_id) as customer_name,
    (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) as item_count,
    (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) as total_paid
FROM orders o;

-- 각 컬럼마다 서브쿼리 실행 (N*3)

-- 좋은 예
SELECT 
    o.*,
    c.name as customer_name,
    COUNT(DISTINCT oi.id) as item_count,
    COALESCE(SUM(p.amount), 0) as total_paid
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN payments p ON o.id = p.order_id
GROUP BY o.id, c.name;

안티패턴 4: DISTINCT 남용

sql

-- 나쁜 예
SELECT DISTINCT * FROM orders;
-- 모든 컬럼을 비교하여 중복 제거 (비효율)

-- 좋은 예: 중복 원인 해결
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'Seoul';
-- JOIN 조건을 명확히 하여 중복 방지

고급 최적화 기법

1. 파티션 프루닝

sql

-- 파티셔닝된 테이블
CREATE TABLE orders_partitioned (
    ...
) PARTITION BY RANGE (order_date);

-- 쿼리
EXPLAIN SELECT * FROM orders_partitioned
WHERE order_date >= '2024-01-01' 
  AND order_date < '2024-02-01';

-- 결과: Partitions: p202401
-- p202401 파티션만 스캔 (다른 파티션 무시)

2. 인덱스 머지

sql

-- 복수 인덱스 활용
CREATE INDEX idx_customer ON orders(customer_id);
CREATE INDEX idx_status ON orders(status);

SELECT * FROM orders
WHERE customer_id = 12345 AND status = 'COMPLETED';

-- Index Merge: 두 인덱스를 모두 사용하여 결과 병합
-- (MySQL에서 자동 최적화)

3. Covering Index 활용

sql

-- 쿼리
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345
ORDER BY order_date DESC;

-- 커버링 인덱스
CREATE INDEX idx_covering 
ON orders(customer_id, order_date DESC, order_id, total_amount);

-- 테이블 접근 없이 인덱스만으로 처리
-- Extra: Using index

마치며

쿼리 최적화는 데이터베이스 성능의 핵심입니다.

핵심 원칙:

측정 먼저: EXPLAIN으로 실행 계획 분석
인덱스가 핵심: 적절한 인덱스가 성능의 80%
필요한 것만: SELECT *, JOIN, 서브쿼리 신중히
대용량은 배치: 한 번에 모두 처리하지 말 것
지속적 모니터링: 느린 쿼리 추적 및 개선

최적화 우선순위:

인덱스 추가/수정 (가장 효과적)
쿼리 재작성 (로직 개선)
캐싱 적용 (반복 조회)
하드웨어 업그레이드 (최후의 수단)

기억하세요: "빠른 쿼리는 좋은 인덱스에서 시작됩니다. 하지만 인덱스가 만능은 아닙니다."

쿼리 최적화(Query Optimization)

성능의 핵심: 쿼리 최적화(Query Optimization) 완벽 가이드

쿼리 최적화란?

정의

왜 필요한가?

느린 쿼리 식별

1. Slow Query Log (MySQL)

2. pg_stat_statements (PostgreSQL)

3. 애플리케이션 레벨 모니터링

EXPLAIN 분석

MySQL EXPLAIN

PostgreSQL EXPLAIN ANALYZE

인덱스 최적화

1. 단일 인덱스

2. 복합 인덱스 (중요!)

3. 커버링 인덱스

4. 부분 인덱스 (PostgreSQL)

5. 함수 기반 인덱스

쿼리 작성 최적화

1. SELECT * 지양

2. WHERE 조건 최적화

3. JOIN 최적화

4. EXISTS vs IN

5. LIMIT과 OFFSET

6. GROUP BY 최적화

실행 계획 기반 최적화

1. 통계 정보 업데이트

2. 인덱스 힌트 (조심스럽게 사용)

3. 쿼리 재작성

대용량 데이터 처리

1. 배치 처리

2. 벌크 연산

3. 파티셔닝

캐싱 전략

1. 쿼리 결과 캐싱

2. Materialized View

모니터링과 분석

1. 쿼리 프로파일링

2. 성능 메트릭

3. Prometheus 쿼리

실전 체크리스트

개발 단계

테스트 단계

배포 단계

운영 단계

실전 안티패턴

안티패턴 1: 과도한 인덱스

안티패턴 2: SELECT * 남용

안티패턴 3: 서브쿼리 남용

안티패턴 4: DISTINCT 남용

고급 최적화 기법

1. 파티션 프루닝

2. 인덱스 머지

3. Covering Index 활용

마치며

댓글

티스토리툴바