쿼리 최적화 (Query Optimization)#
데이터베이스 쿼리 최적화는 데이터베이스 시스템의 성능을 향상시키고 효율성을 높이기 위한 중요한 프로세스이다.
쿼리 최적화의 목적#
- 쿼리 응답 시간 단축
- 시스템 자원 사용 효율성 증대
- 전반적인 데이터베이스 성능 향상
- 사용자 경험 개선
주요 최적화 기법#
인덱스 최적화#
- 적절한 인덱스 생성 및 관리
- 복합 인덱스 활용
- 선택도가 높은 컬럼에 인덱스 생성
- 불필요한 인덱스 제거
1
2
3
4
5
6
7
8
9
| -- 자주 사용되는 조건절에 대한 인덱스 생성
CREATE INDEX idx_orders_amount_date ON orders(total_amount, order_date);
-- 위 인덱스를 활용하는 최적화된 쿼리
SELECT customers.name, orders.order_date, orders.total_amount
FROM orders -- 인덱스를 가진 테이블을 먼저 참조
JOIN customers ON customers.id = orders.customer_id
WHERE orders.total_amount > 1000
ORDER BY orders.order_date DESC;
|
쿼리 재작성#
- 복잡한 쿼리 단순화
- 서브쿼리 최소화 및 조인으로 대체
- WHERE 절 최적화
- 필요한 컬럼만 선택 (SELECT *)
- 테이블 조인 순서와 조인 유형을 적절히 선택
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| -- 비효율적인 서브쿼리
SELECT *
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
WHERE total_amount > 1000
);
-- 더 효율적인 조인으로 변환
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total_amount > 1000;
|
실행 계획 분석#
- 쿼리 실행 계획 확인 및 분석
- 비효율적인 실행 계획 개선
쿼리 실행 계획은 데이터베이스가 쿼리를 어떻게 처리할지를 보여주는 로드맵과 같다.
1
2
3
4
5
6
| EXPLAIN ANALYZE
SELECT customers.name, orders.order_date, orders.total_amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE orders.total_amount > 1000
ORDER BY orders.order_date DESC;
|
이 쿼리의 실행 계획을 분석하면, 데이터베이스가 어떤 순서로 테이블을 읽고, 어떤 인덱스를 사용하는지 알 수 있다. 여기서 발견할 수 있는 비효율적인 부분들을 개선해나갈 수 있다.
조인 최적화#
- 적절한 조인 방식 선택
- 조인 순서 최적화
- 조인 컬럼에 인덱스 생성
1
2
3
4
5
6
7
8
9
| -- 작은 결과셋을 먼저 필터링하여 조인 성능 향상
SELECT c.name, o.order_date
FROM (
SELECT customer_id, order_date
FROM orders
WHERE total_amount > 1000
AND order_date >= '2024-01-01'
) o
JOIN customers c ON c.id = o.customer_id;
|
데이터 모델링 개선#
- 정규화와 반정규화의 적절한 균형
- 효율적인 테이블 구조 설계
캐싱 활용#
- 자주 사용되는 쿼리 결과 캐싱
- 메모리 캐시 활용 (예: Redis)
1
2
3
4
5
6
7
8
9
10
11
| -- 자주 사용되는 복잡한 쿼리 결과를 임시 테이블에 저장
CREATE TEMPORARY TABLE tmp_sales_summary AS
SELECT customer_id,
SUM(total_amount) as total_sales
FROM orders
GROUP BY customer_id;
-- 이후 임시 테이블 활용
SELECT *
FROM tmp_sales_summary
WHERE total_sales > 5000;
|
페이징 및 LIMIT 사용#
- 대량 데이터 조회 시 페이징 구현
- LIMIT 절을 사용하여 결과 제한
1
2
3
4
5
6
7
8
9
10
11
12
| -- 비효율적인 페이징
SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 1000000;
-- 더 효율적인 키셋 페이징
SELECT *
FROM orders
WHERE order_date < :last_seen_date
ORDER BY order_date DESC
LIMIT 10;
|
최적화 프로세스#
- 성능 문제 식별
- 쿼리 분석 및 병목 지점 파악
- 최적화 전략 수립
- 최적화 기법 적용
- 성능 테스트 및 모니터링
- 결과 분석 및 추가 최적화
주의사항#
- 과도한 인덱스 생성은 오히려 성능 저하를 초래할 수 있음
- 쿼리 최적화는 지속적인 프로세스로 주기적인 검토 필요
- 데이터베이스의 크기와 복잡성에 따라 최적화 전략 조정 필요
참고 및 출처#