EXPLAIN을 활용한 SQL 쿼리 성능 분석 방법

데이터베이스 최적화는 효율적인 데이터 처리를 위해 필수적인 작업이다. DBMS에서 EXPLAIN 명령은 SQL 쿼리의 실행 계획을 분석하고 성능 병목 현상을 파악하는 데 중요한 도구이다.

 

---

EXPLAIN이란?

---

EXPLAIN은 SQL 쿼리 실행 시 DBMS가 선택한 실행 계획(Execution Plain)을 보여주는 명령어이다. 실행 계획은 DBMS가 데이터를 검색하고 처리하기 위해 사용할 인덱스, 테이블 액세스 방식, 조인 순서 등을 포함한다. 이를 통해 병목 지점을 파악하고 쿼리를 최적화할 수 있다.

 

EXPLAIN 명령은 MySQL, PostgreSQL, Oracle, SQL Server 등 대부분의 DBMS에서 지원하며, 데이터베이스 엔진이 쿼리를 실행하기 전에 어떤 방법으로 실행할지를 미리 확인할 수 있게 한다.

 

EXPLAIN의 목적

1. 쿼리 최적화: SQL 쿼리를 실행하기 전에 성능을 예측하고 효율성을 개선한다.
2. 병목 지점 파악: 인덱스가 올바르게 사용되고 있는지, 불필요한 테이블 스캔이 발생하는지 확인한다.
3. 테이블 간 관계 이해: 조인의 순서와 방법(Nested Loop, Hash Join 등)을 확인한다.

 

---

EXPLAIN을 사용하는 방법

---

EXPLAIN은 SQL 쿼리 앞에 추가하여 사용한다.

 

예제

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

 

EXPLAIN 결과 해석

---

컬럼	설명
id	쿼리 내의 SELECT 순서를 나타낸다.
select_type	SELECT의 유형(SIMPLE, PRIMARY, SUBQUERY 등)을 나타낸다.
table	데이터가 검색되는 테이블 이름이다.
type	테이블 접근 방식. FULL, INDEX, RANGE, REF, EQ_REF 등이 있다.
possible_keys	쿼리에서 사용 가능한 인덱스이다.
key	실제로 사용된 인덱스이다.
key_len	사용된 인덱스의 길이(바이트)이다.
ref	쿼리 조건에서 사용된(상수, 컬럼 등)이다.
rows	DBMS가 검색할 것으로 예상되는 행(row) 수이다.
filtered	조건을 만족하는 데이터의 비율(%)이다.
Extra	추가 작업(정렬, 파일 스캔 등)에 대한 정보이다.

 

type (테이블 접근 방식)

type은 쿼리에서 테이블 데이터를 검색하는 방식을 나타내며, 아래로 갈수록 효율이 낮아진다.

• const: 상수 값을 직접 사용하는 방식으로, 가장 빠르다.
• eq_ref: 인덱스와 조인을 통해 한 행만 검색
• ref: 인덱스를 사용하여 조건에 맞는 여러 행을 검색
• range: 조건에 따른 특정 범위 데이터를 검색
• index: 테이블 전체를 인덱스를 통해 검색
• ALL: 테이블 전체를 스캔하는 방식으로, 가장 비효율적이다.

 

Extra

• Using where: 조건 필터링 수행
• Using index: 인덱스만으로 결과를 반환(커버링 인덱스)
• Using filesort: ORDER BY 시 추갖거인 정렬 작업이 필요
• Using temporary: 중간 결과를 임시 테이블에 저장

 

---

EXPLAIN 활용 예시

---

단일 테이블에서 인덱스 활용

---

쿼리

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

 

결과

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	orders	ref	idx_customer_date	idx_customer_date	10	Using where

• key: idx_customer_date 인덱스
• type: ref는 인덱스를 사용한 효율적인 검색을 나타냄
• Extra: 조건 필터링(Using where)이 추가로 수행됨

 

인덱스 미사용

---

쿼리

EXPLAIN SELECT * FROM orders WHERE total_amount > 100;

 

결과

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	orders	ALL	null	null	1000	Using where

• type: ALL은 전체 테이블 스캔을 의미(성능 저하)
• possible_keys: 사용 가능한 인덱스가 없음
• 해결 방안: total_amount 컬럼에 인덱스를 추가

 

JOIN

---

쿼리

EXPLAIN
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE c.customer_name = 'John';

 

결과

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	c	const	PRIMARY	PRIMARY	1	Using where
1	SIMPLE	o	ref	idx_customer_date	idx_customer_date	10	Using index

• 조인 순서
  • customers 테이블이 먼저 처리된다(id = 1)
  • 조건 c.customer_name = 'JOIN'으로 인해 customers 테이블의 특정 행이 먼저 검색된다.
    • type = const: 단일 행을 검색하는 가장 빠른 방식
• 조인 방식
  • orders 테이블은 customer_id를 기준으로 customers 테이블과 조인된다.
  • type = ref: 인덱스를 활용해 여러 행을 검색하는 방식
• 사용된 인덱스
  • PRIMARY 인덱스: customers 테이블의 기본 키(customer_id)를 사용
  • idx_customer_date: orders 테이블의 customer_id를 기준으로 인덱스를 사용
• 성능
  • rows에서 예상 검색 행 수는 customers는 1개, orders는 10개입니다.
  • 조인이 효율적으로 처리되며 추가 작업은 필요하지 않다.

 

SUBQUERY

---

쿼리

EXPLAIN
SELECT order_id
FROM orders
WHERE customer_id = (
    SELECT customer_id
    FROM customers
    WHERE customer_name = 'John'
);

 

결과

id	select_type	table	type	possible_keys	key	rows	Extra
2	SUBQUERY	customers	const	PRIMARY	PRIMARY	1	Using where
1	SIMPLE	orders	ref	idx_customer_date	idx_customer_date	10	Using index

• 서브쿼리 실행 순서
  • id = 2 (SUBQUERY): customers 테이블의 조건 customer_name = 'John'에 해당하는 customer_id를 먼저 검색
    • type = const: 단일 값만 검색되므로 가장 효율적
  • id = 1 (SIMPLE): 서브쿼리의 결과(customer_id)를 사용해 orders 테이블에서 데이터를 검색
• 인덱스 사용 여부
  • PRIMARY 인덱스: customers 테이블의 기본 키(customer_id)가 사용됨
  • idx_customer_date: orders 테이블의 customer_id 인덱스를 사용해 검색
• 성능
  • 서브쿼리가 단일 행을 반환하므로 큰 성능 문제는 없지만, 서브쿼리가 반복적으로 실행되는 경우 성능이 저하될 가능성이 있다.

 

---

EXPLAIN을 활용한 최적화 전략

---

1. 적절한 인덱스 생성
   • 자주 사용되는 조건 컬럼(WHERE, JOIN, ORDER BY, GROUP BY)에 인덱스 생성
2. 쿼리 작성 방식 최적화
   
   • 조건 순서를 인덱스 순서와 맞추어 작성
   • 필요 없는 정렬(ORDER BY)이나 필터링(WHERE) 최소화
3. 결과 해석 및 개선
   • type이 ALL일 경우, 인덱스 추가 또는 쿼리 구조 변경
   • Extra에 Using filesort, Using temporary가 나타나면 쿼리와 인덱스를 조절