SQL 실행 계획(EXPLAIN) 읽는 법 — 느린 쿼리 잡기

-- 실행 계획 확인하기
EXPLAIN
SELECT u.name, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date >= '2024-01-01'
  AND o.status = 'completed';

-- MySQL 8.0 이상에서 더 자세한 정보 보기
EXPLAIN ANALYZE
SELECT u.name, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date >= '2024-01-01'
  AND o.status = 'completed';
  

-- 실행 계획 예시 결과
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | key           | key_len | ref     | rows  | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| 1  | SIMPLE      | o     | ALL  | NULL          | NULL    | NULL    | 50000 | Using where |
| 1  | SIMPLE      | u     | ref  | PRIMARY       | 4       | o.user_id| 1   | NULL        |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

-- 문제 발견: orders 테이블이 type=ALL (전체 스캔)
-- 해결: order_date와 status에 복합 인덱스 생성
CREATE INDEX idx_orders_date_status 
ON orders(order_date, status);

-- 인덱스 생성 후 다시 확인
EXPLAIN
SELECT u.name, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date >= '2024-01-01'
  AND o.status = 'completed';
  

-- 문제의 쿼리 (10초 소요)
SELECT p.product_name, COUNT(*) as order_count
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.status = 'completed'
GROUP BY p.product_id, p.product_name
ORDER BY order_count DESC
LIMIT 10;

-- EXPLAIN 결과 분석
-- orders 테이블: type=ALL, rows=500000 (전체 스캔 발생!)
-- order_items 테이블: type=ALL, rows=2000000
-- Extra: Using temporary; Using filesort (임시 테이블과 디스크 정렬)

-- 해결 1: orders 테이블에 복합 인덱스 생성
CREATE INDEX idx_orders_date_status 
ON orders(order_date, status);

-- 해결 2: order_items의 외래키에 인덱스 생성
CREATE INDEX idx_order_items_order_id 
ON order_items(order_id);

-- 최적화 후 다시 실행 (0.1초로 개선!)
-- orders: type=range, rows=15000 (인덱스 범위 스캔)
-- order_items: type=ref, rows=3 (인덱스 조인)
  

EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 결과
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | key           | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1  | SIMPLE      | users | ALL  | NULL          | 100000 | Using where                   |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+