[Daily morning study] 쿼리 최적화와 실행 계획(EXPLAIN)
#daily morning study
쿼리 최적화란?
같은 결과를 내는 SQL이라도 실행 방법에 따라 성능 차이가 수십~수백 배 날 수 있다. 쿼리 최적화는 데이터베이스 엔진이 요청을 처리할 때 가장 효율적인 경로(실행 계획)를 선택하도록 유도하는 작업이다.
최적화를 논하기 전에 먼저 DB 엔진이 쿼리를 어떻게 실행하는지 이해해야 한다.
쿼리 실행 과정
SQL 파싱 → 분석/검증 → 최적화(실행 계획 생성) → 실행 → 결과 반환
- 파싱: SQL 문법을 파스 트리로 변환
- 최적화: 옵티마이저가 통계 정보를 기반으로 여러 실행 계획 후보 중 가장 비용이 낮은 것 선택
- 실행: 선택된 실행 계획대로 데이터 접근
여기서 핵심은 옵티마이저(Query Optimizer)다. 옵티마이저가 잘못된 실행 계획을 선택하면 쿼리가 느려진다.
EXPLAIN으로 실행 계획 확인하기
MySQL / MariaDB
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
EXPLAIN ANALYZE를 쓰면 실제 실행 시간까지 측정한다(MySQL 8.0+):
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;
PostgreSQL
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;
PostgreSQL의 ANALYZE는 실제로 쿼리를 실행하므로 DELETE/UPDATE를 분석할 때는 트랜잭션 내에서 실행 후 롤백해야 한다.
MySQL EXPLAIN 결과 읽기
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | NULL | ref | idx_user_id | idx_user_id | 4 | const | 150 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
주요 컬럼 설명
| 컬럼 | 의미 |
|---|---|
id | 쿼리 내 SELECT 순서. 서브쿼리나 UNION이 있으면 번호가 달라짐 |
select_type | 쿼리 유형 (SIMPLE, SUBQUERY, DERIVED 등) |
type | 테이블 접근 방식. 가장 중요한 컬럼 |
possible_keys | 사용 가능한 인덱스 후보 |
key | 실제로 선택된 인덱스 |
rows | 옵티마이저가 읽을 것으로 예측하는 행 수 |
Extra | 추가 정보 (Using filesort, Using temporary 등) |
type 컬럼 — 성능 순서
성능이 좋은 순서대로:
| type | 설명 |
|---|---|
system | 테이블에 행이 하나뿐 (거의 없음) |
const | PK나 유니크 인덱스로 단 1건 조회 |
eq_ref | JOIN 시 PK/유니크 인덱스로 1건씩 매칭 |
ref | 인덱스를 사용하지만 여러 행 반환 가능 |
range | 인덱스 범위 스캔 (BETWEEN, >, <, IN) |
index | 인덱스 전체 스캔 (테이블 풀 스캔보다 낫지만 느림) |
ALL | 테이블 풀 스캔. 피해야 함 |
type: ALL이 나오면 인덱스가 없거나 옵티마이저가 풀 스캔을 선택한 것이므로 반드시 원인을 확인해야 한다.
Extra 컬럼 주요 값
| 값 | 의미 |
|---|---|
Using index | 커버링 인덱스 사용. 테이블 접근 없이 인덱스만으로 처리 (좋음) |
Using where | WHERE 조건으로 필터링 |
Using filesort | ORDER BY를 인덱스로 처리 못 하고 별도 정렬 수행 (느릴 수 있음) |
Using temporary | 임시 테이블 사용. GROUP BY, ORDER BY 혼합 시 발생 (느림) |
Using index condition | Index Condition Pushdown(ICP) 적용 |
PostgreSQL EXPLAIN 결과 읽기
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;
Index Scan using idx_orders_user_id on orders (cost=0.43..8.45 rows=1 width=72) (actual time=0.023..0.025 rows=1 loops=1)
Index Cond: (user_id = 100)
Planning Time: 0.123 ms
Execution Time: 0.051 ms
cost=시작비용..총비용: 옵티마이저의 예측 비용 (임의 단위)rows=N: 예측 행 수actual time=..: 실제 수행 시간(ms).ANALYZE사용 시에만 표시loops=N: 해당 노드가 반복 실행된 횟수
중첩 노드(Nested Loop 등)가 있으면 트리 구조로 표시되며, 안쪽(들여쓰기 깊은) 노드부터 실행된다.
느린 쿼리 패턴과 해결 방법
1. 인덱스 미사용 — type: ALL
-- 느림: full scan
SELECT * FROM users WHERE YEAR(created_at) = 2025;
-- 빠름: 인덱스 범위 스캔
SELECT * FROM users WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
함수를 컬럼에 적용하면 인덱스를 타지 못한다.
2. 묵시적 타입 변환
-- phone_number가 VARCHAR인데 숫자로 비교 → 인덱스 무효화
SELECT * FROM users WHERE phone_number = 01012345678;
-- 올바른 방법
SELECT * FROM users WHERE phone_number = '01012345678';
3. 불필요한 SELECT *
필요한 컬럼만 명시하면 커버링 인덱스 활용 가능성이 높아지고 네트워크/메모리 부담도 줄어든다.
-- 피해야 함
SELECT * FROM orders WHERE user_id = 100;
-- 권장
SELECT id, amount, created_at FROM orders WHERE user_id = 100;
4. Using filesort 제거
-- idx_created_at 인덱스가 없으면 filesort 발생
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
-- 복합 인덱스 (user_id, created_at) 생성 시 filesort 제거
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
5. N+1 문제
ORM을 사용할 때 연관 데이터를 루프로 개별 조회하면 N+1번의 쿼리가 발생한다. JOIN이나 IN절을 활용해 한 번에 조회해야 한다.
커버링 인덱스(Covering Index)
쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있으면 테이블 본체(힙)를 전혀 읽지 않아도 된다. 이를 커버링 인덱스라 하며, Extra: Using index로 확인할 수 있다.
-- (user_id, amount) 복합 인덱스가 있다면
SELECT amount FROM orders WHERE user_id = 100;
-- → 테이블 접근 없이 인덱스만으로 처리
자주 사용하는 SELECT 컬럼을 인덱스에 포함시켜 커버링 인덱스를 만들면 성능이 크게 향상된다.
통계 정보와 옵티마이저
옵티마이저는 테이블의 통계 정보(행 수, 컬럼 분포, 카디널리티 등)를 기반으로 실행 계획을 세운다. 통계가 오래되면 잘못된 계획을 선택할 수 있다.
-- MySQL: 통계 정보 갱신
ANALYZE TABLE orders;
-- PostgreSQL: 통계 정보 갱신
ANALYZE orders;
대량 INSERT/DELETE 후에는 통계를 갱신해 주는 것이 좋다.
인덱스 힌트 (최후 수단)
옵티마이저가 잘못된 인덱스를 선택할 경우 힌트로 강제할 수 있다. 단, 통계 갱신이나 쿼리 리팩터링이 우선이며 힌트는 최후 수단으로만 사용한다.
-- MySQL: 인덱스 강제 지정
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 100;
-- PostgreSQL: 특정 플래너 옵션 비활성화 (예: seq scan 금지)
SET enable_seqscan = off;
정리
EXPLAIN으로 실행 계획을 확인하고,type: ALL·Using filesort·Using temporary를 없애는 방향으로 최적화한다.- 인덱스 컬럼에 함수·타입 변환을 적용하지 않는다.
- 필요한 컬럼만 SELECT하고, 커버링 인덱스 활용을 고려한다.
- 통계 정보를 최신 상태로 유지한다.
- 인덱스 힌트는 최후 수단이고, 근본 원인 해결이 우선이다.
