EXPLAIN ANALYZE 활용법

EXPLAIN ANALYZE란?

MySQL에서 쿼리 실행 계획뿐만 아니라, 쿼리를 실제로 실행해 본 후 실제 실행 시간과 처리된 행 수 같은 실행 통계까지 함께 보여주는 명령어

• 단순 EXPLAIN과 달리, 쿼리를 실제 실행하므로 정확한 성능 정보를 알 수 있다.
• 옵티마이저가 예측한 실행 계획과 실제 수행 결과를 비교해, 실행 계획의 적절성을 검증할 수 있다.

---

EXPLAIN ANALYZE 활용법 및 개선 방향

• 실제 실행 시간과 옵티마이저 예상 비용을 비교한다.
• 조인 방식(Nested Loop, Hash Join 등)과 인덱스 활용 여부를 확인한다.
• 불필요한 컬럼 조회를 피하고, 필요한 컬럼만 선택한다. (커버링 인덱스 유도)
• 통계 정보를 최신으로 유지해 옵티마이저가 정확한 계획을 세우도록 한다.
• 변경 전후 성능을 수치로 비교해 튜닝 효과를 검증한다.

---

EXPLAIN ANALYZE 활용 예제

EXPLAIN ANALYZE
SELECT o.order_id, o.total_price, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.total_price > 1000000
AND c.region = 'Seoul';

 

예상 결과 예시

• orders 테이블이 테이블 스캔을 수행하고 있음 (cost=15000, 실제 120ms 소요)
• customers 테이블 인덱스 조회는 인덱스가 있으나, orders 테이블에서 4800건을 모두 스캔한 후 각각에 대해 4800번 조회 수행 (loops=4800)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop join                                                                                                                                                |
|    -> Table scan on orders  (cost=15000 rows=5000) (actual time=120.500..122.700 rows=4800 loops=1)                                                                |
|    -> Index lookup on customers using idx_customers_region (region = 'Seoul')  (cost=100 rows=50) (actual time=0.5..0.6 rows=48 loops=4800)                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.130 sec)

 

 

결과 해석

• Nested loop join 방식으로 두 테이블을 조인함을 확인할 수 있다.
  • 두 테이블을 조인할 때, 외부 테이블(orders)을 한 행씩 순회하면서 내부 테이블(customers)에서 해당 조건에 맞는 행을 찾는 방식
• orders 테이블에 대해 풀 테이블 스캔을 수행하고, 그 행(4800개) 각각에 대해 customers 테이블에서 region='Seoul' 인덱스를 이용해 고객을 찾는다.
• 내부 테이블 인덱스 조회가 외부 테이블의 모든 행마다 반복되므로, 비용이 많이 든다.

 

쿼리 튜닝 방법

1. 조인 방식 변경

• MySQL 기본 조인 방식은 Nested Loop Join이다.
• 큰 테이블을 먼저 필터링하거나 작은 테이블을 먼저 탐색하도록 조인 순서를 조정하여 성능 개선 가능
• STRAIGHT_JOIN 키워드로 조인 순서를 강제할 수 있다.
• 서브쿼리, 윈도우 함수 등 MySQL 8+ 기능을 활용해 복잡한 조인을 간결하게 표현하고 최적화 시도 가능

2. 인덱스 추가

• 조인 조건 컬럼(customer_id 등)과 WHERE 절 필터 컬럼(total_price, region)에 인덱스를 추가
• 복합 인덱스 생성으로 다중 컬럼 조건을 한 번에 커버해 디스크 I/O 감소
• 인덱스 컬럼 순서도 중요, WHERE와 JOIN 조건 우선순위에 맞게 설계
• 불필요한 인덱스는 제거해 쓰기 성능 저하 방지

3. 쿼리 리팩토링

• 필요한 컬럼만 조회 (SELECT * 지양)
• 복잡한 쿼리는 뷰(View)나 임시 테이블로 분할해 가독성 및 유지보수성 향상
• 서브쿼리를 조인으로 변환해 옵티마이저가 더 좋은 계획 선택하도록 유도
• 불필요한 함수 호출, 계산 제거로 비용 절감
• LIMIT, OFFSET 등 페이징 조건 최적화 (예: keyset pagination)

4. 기타 팁

• ANALYZE TABLE로 통계 정보 갱신
• 실행 계획과 실제 실행 시간 간 차이가 크면 데이터 분포 및 통계 문제 의심
• 대용량 데이터는 파티셔닝도 검토

 

 

리팩토링 후 쿼리 예시

• orders 테이블에 total_price에 대한 인덱스 추가
• 서브쿼리로 필터링된 고객만 조인

EXPLAIN ANALYZE
SELECT o.order_id, o.total_price, c.customer_name
FROM orders o
JOIN (
  SELECT customer_id, customer_name
  FROM customers
  WHERE region = 'Seoul'
) c ON o.customer_id = c.customer_id
WHERE o.total_price > 1000000;

 

예상 결과 예시

• orders 테이블이 Index lookup을 수행해 필터링 비용이 크게 줄었음 (cost=500, 실제 5ms)
• customers 테이블도 인덱스 조회를 하고, orders에서 필터링된 190건에 대해서만 190번 루프 실행 (loops=190)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop join                                                                                                                                                |
|    -> Index lookup on orders using idx_orders_total_price (total_price > 1000000)  (cost=500 rows=200) (actual time=5.0..5.5 rows=190 loops=1)                      |
|    -> Index lookup on customers using idx_customers_region (region = 'Seoul')  (cost=100 rows=50) (actual time=0.5..0.6 rows=48 loops=190)                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.008 sec)

 

결과 해석

• Nested loop join 방식 사용
  
  • MySQL 에서는 조인 알고리즘을 직접 지정하기 어렵다...
  • 특정 DBMS 에서는 조인 힌트(Hint) 를 통해 조인 방식을 명시하거나 유도할 수 있다
• orders 테이블에서 total_price > 1000000 조건으로 인덱스를 통해 190개 정도의 행만 필터링해서 가져옴 → 훨씬 효율적이다.
• 이후 각 190개의 주문 행마다 customers 테이블에서 region='Seoul' 조건으로 인덱스 조회를 수행함.
• orders 테이블에 인덱스를 활용해 필터링 후 조인하여, 외부 루프가 훨씬 줄어든 덕분에 쿼리가 더 빠르게 실행되었다.