MySQL EXPLAIN 보는법: 5분만에 마스터하는 쿼리 최적화 비법

오늘은 MySQL에서 가장 중요한 도구 중 하나인 MySQL EXPLAIN 보는법 주제로 알아볼게요. EXPLAIN은 쿼리의 실행 계획을 보여주는 강력한 도구로, 이를 제대로 이해하고 활용하면 데이터베이스 성능을 크게 향상시킬 수 있어요.

여러분도 알다시피, 데이터베이스 성능은 애플리케이션의 전반적인 성능에 큰 영향을 미치죠. 특히 대규모 데이터를 다루는 현대 애플리케이션에서는 더욱 그렇습니다. 그래서 오늘은 MySQL EXPLAIN을 5분 만에 마스터할 수 있는 비법을 공유하려고 해요. 이 글을 통해 여러분의 쿼리 최적화 능력이 한 단계 업그레이드될 거예요. 자, 그럼 시작해볼까요?

MySQL EXPLAIN이란?

MySQL EXPLAIN은 쿼리 실행 계획을 분석하는 명령어예요. 이 명령어를 사용하면 MySQL이 쿼리를 어떻게 실행할 것인지, 어떤 인덱스를 사용할 것인지, 얼마나 많은 행을 조회할 것인지 등을 미리 알 수 있어요.

EXPLAIN을 사용하는 방법은 간단해요. 분석하고 싶은 쿼리 앞에 EXPLAIN 키워드를 붙이면 돼요. 예를 들면 이렇게요.

EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAIN 결과 해석하기

EXPLAIN의 결과는 여러 컬럼으로 구성되어 있어요. 각 컬럼의 의미를 이해하는 것이 EXPLAIN을 마스터하는 첫 걸음이죠. 주요 컬럼들을 살펴볼까요?

id: SELECT 식별자예요. 쿼리 내에서 SELECT의 순서를 나타내죠.
select_type: SELECT의 유형을 나타내요. SIMPLE, PRIMARY, SUBQUERY 등이 있어요.
table: 참조하는 테이블 이름이에요.
type: 조인 유형을 나타내요. 성능 순으로 나열하면 system > const > eq_ref > ref > range > index > ALL 순이에요.
possible_keys: 사용 가능한 인덱스 목록이에요.
key: 실제로 사용된 인덱스예요.
key_len: 사용된 인덱스의 길이예요.
ref: 인덱스와 비교되는 컬럼이나 상수값이에요.
rows: 검색해야 할 대략적인 행의 수예요.
Extra: 추가 정보를 제공해요. “Using index”, “Using where”, “Using temporary” 등이 있어요.

EXPLAIN 결과 분석하기

EXPLAIN 결과를 보면 쿼리의 성능을 직관적으로 파악할 수 있어요. 예를 들어, type이 ‘ALL’이라면 전체 테이블 스캔을 의미하므로 성능이 좋지 않다는 뜻이에요. 반면 ‘const’나 ‘eq_ref’라면 매우 효율적인 쿼리라고 볼 수 있죠.

또한 ‘possible_keys’와 ‘key’를 비교해보면 어떤 인덱스가 사용 가능하고 실제로 어떤 인덱스가 사용되었는지 알 수 있어요. 만약 ‘possible_keys’에는 인덱스가 있는데 ‘key’가 NULL이라면, 인덱스를 사용할 수 있음에도 사용하지 않은 것이므로 쿼리 최적화의 여지가 있다고 볼 수 있어요.

‘rows’ 컬럼은 MySQL이 쿼리를 처리하기 위해 검사해야 할 예상 행 수를 나타내요. 이 값이 크면 클수록 쿼리 처리에 더 많은 시간이 걸린다고 볼 수 있죠.

‘Extra’ 컬럼에서는 “Using index”가 보이면 커버링 인덱스를 사용한다는 의미로, 이는 매우 효율적인 쿼리임을 나타내요. 반면 “Using temporary”나 “Using filesort”가 보이면 성능 개선의 여지가 있다는 뜻이에요.

EXPLAIN을 활용한 쿼리 최적화 전략

EXPLAIN 결과를 바탕으로 쿼리를 최적화하는 몇 가지 전략을 소개할게요.

인덱스 활용: ‘possible_keys’에 인덱스가 있는데 ‘key’가 NULL이라면, 해당 인덱스를 활용할 수 있도록 쿼리를 수정해보세요.
조인 최적화: ‘type’이 ‘ALL’인 경우, 적절한 인덱스를 추가하거나 조인 순서를 변경해보세요.
서브쿼리 최적화: 서브쿼리를 조인으로 변경하면 성능이 개선될 수 있어요.
LIMIT 사용: 대량의 데이터를 조회할 때는 LIMIT을 사용해 필요한 만큼만 가져오세요.
불필요한 컬럼 제거: SELECT * 대신 필요한 컬럼만 명시적으로 선택하세요.

EXPLAIN 활용 예시

실제 예시를 통해 EXPLAIN을 어떻게 활용하는지 살펴볼까요?

EXPLAIN SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'completed' AND u.country = 'USA';

이 쿼리의 EXPLAIN 결과가 다음과 같다고 가정해볼게요:

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	o	ALL	NULL	NULL	1000	Using where
1	SIMPLE	u	ALL	PRIMARY	NULL	5000	Using where; Using join buffer

MySQL EXPLAIN 예시

이 결과를 보면, 두 테이블 모두 전체 테이블 스캔(type: ALL)을 하고 있어요. 이는 성능이 좋지 않다는 뜻이에요. 이를 개선하기 위해 다음과 같은 조치를 취할 수 있어요.

orders 테이블의 status 컬럼에 인덱스 추가
users 테이블의 country 컬럼에 인덱스 추가
orders 테이블의 user_id 컬럼에 인덱스 추가 (외래 키에는 보통 인덱스를 추가하는 것이 좋아요)

이렇게 인덱스를 추가한 후 다시 EXPLAIN을 실행하면, type이 ‘ref’나 ‘range’로 변경되고 rows 값도 크게 줄어들 거예요.

EXPLAIN 심화: 실행 계획 최적화

EXPLAIN 결과를 더 깊이 이해하고 활용하기 위해, 몇 가지 추가적인 개념과 팁을 소개해드릴게요.

1. 인덱스 활용도 높이기

인덱스는 쿼리 성능 향상의 핵심이에요. 하지만 단순히 인덱스를 추가하는 것만으로는 충분하지 않을 수 있어요. 다음과 같은 점들을 고려해보세요:

복합 인덱스 순서: 복합 인덱스를 생성할 때는 컬럼 순서가 중요해요. 가장 자주 사용되는 컬럼을 먼저 두는 것이 좋습니다.
인덱스 선택성: 선택성이 높은 컬럼(고유한 값이 많은 컬럼)에 인덱스를 추가하면 더 효과적이에요.
커버링 인덱스: 쿼리에서 필요한 모든 컬럼이 인덱스에 포함되어 있으면, 테이블 접근 없이 인덱스만으로 쿼리를 처리할 수 있어요. 이를 커버링 인덱스라고 하며, 매우 빠른 성능을 제공합니다.

2. 조인 최적화

복잡한 쿼리에서는 조인 순서가 성능에 큰 영향을 미칠 수 있어요. EXPLAIN 결과의 ‘id’ 컬럼을 통해 조인 순서를 확인할 수 있습니다.

Nested Loop Join: MySQL에서 가장 흔히 사용되는 조인 방식이에요. 외부 테이블의 각 행에 대해 내부 테이블을 스캔하는 방식입니다.
Hash Join: MySQL 8.0부터 지원되는 조인 방식으로, 대용량 데이터 처리에 효과적이에요.

조인 최적화를 위해 다음과 같은 전략을 사용할 수 있어요.

작은 테이블을 먼저 조인하세요.
조인 조건에 사용되는 컬럼에 인덱스를 추가하세요.
불필요한 조인을 제거하세요.

3. 서브쿼리 최적화

서브쿼리는 때때로 성능 저하의 원인이 될 수 있어요. EXPLAIN 결과에서 ‘select_type’이 ‘SUBQUERY’나 ‘DEPENDENT SUBQUERY’로 나타나면 주의가 필요합니다.

서브쿼리를 조인으로 변환: 가능한 경우 서브쿼리를 조인으로 변환하면 성능이 향상될 수 있어요.
Exists 최적화: IN 대신 EXISTS를 사용하면 더 효율적일 수 있습니다.

예를 들어, 다음과 같은 쿼리를

SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');

이렇게 변경할 수 있어요.

SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';

4. 파티셔닝 활용

대용량 테이블의 경우, 파티셔닝을 통해 성능을 크게 향상시킬 수 있어요. EXPLAIN 결과에서 ‘partitions’ 컬럼을 통해 어떤 파티션이 사용되는지 확인할 수 있습니다.

파티셔닝의 장점은 다음과 같아요.

큰 테이블을 작은 단위로 나누어 관리할 수 있어요.
특정 파티션만 검색하므로 쿼리 성능이 향상돼요.
데이터 백업과 복구가 더 쉬워집니다.

5. 쿼리 재작성

때로는 쿼리 자체를 재작성하는 것이 가장 효과적인 최적화 방법일 수 있어요. 다음과 같은 방법을 고려해보세요:

UNION 대신 UNION ALL 사용: 중복 제거가 필요 없는 경우 UNION ALL이 더 빠릅니다.
HAVING 대신 WHERE 사용: 가능한 경우 HAVING 대신 WHERE를 사용하세요.
LIMIT과 OFFSET 최적화: 대량의 OFFSET은 성능 저하를 일으킬 수 있어요. 마지막으로 조회한 ID를 기억하고 그 이후부터 조회하는 방식으로 변경해보세요.

실전 EXPLAIN 활용 사례

실제 상황에서 EXPLAIN을 어떻게 활용할 수 있는지 더 자세히 살펴볼까요?

사례 1: 대용량 로그 테이블 쿼리 최적화

로그 테이블은 시간이 지날수록 크기가 계속 커지는 특성이 있어요. 다음과 같은 쿼리가 있다고 가정해봅시다:

SELECT * FROM logs 
WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31'
ORDER BY created_at DESC 
LIMIT 100;

이 쿼리의 EXPLAIN 결과가 다음과 같다면

id	select_type	table	type	key	rows	Extra
1	SIMPLE	logs	ALL	NULL	1000000	Using filesort

이는 전체 테이블 스캔과 파일 정렬이 발생하고 있음을 나타내요. 이를 개선하기 위해 다음과 같은 조치를 취할 수 있습니다.

created_at 컬럼에 인덱스 추가
파티셔닝 적용 (예: 월별 파티셔닝)

개선 후 EXPLAIN 결과

id	select_type	table	type	key	rows	Extra
1	SIMPLE	logs	range	idx_created_at	1000	Using index condition

사례 2: 복잡한 조인 쿼리 최적화

여러 테이블을 조인하는 복잡한 쿼리의 경우, 조인 순서와 인덱스 사용이 중요해요. 다음과 같은 쿼리를 살펴봅시다:

SELECT o.id, o.order_date, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date > '2023-01-01'
AND c.country = 'USA'
AND p.category = 'Electronics';

이 쿼리의 EXPLAIN 결과를 분석하고, 다음과 같은 최적화를 적용할 수 있어요.

orders 테이블의 order_date에 인덱스 추가
customers 테이블의 country에 인덱스 추가
products 테이블의 category에 인덱스 추가
조인 순서 최적화 (작은 결과셋을 먼저 조인)

최적화 후에는 EXPLAIN 결과에서 각 테이블의 ‘type’이 ‘ref’ 또는 ‘range’로 변경되고, ‘rows’ 값이 크게 줄어든 것을 확인할 수 있을 거예요.

결론

지금까지 MySQL EXPLAIN 보는법 주제로 알아봤습니다. MySQL EXPLAIN은 강력한 도구지만, 그 결과를 제대로 해석하고 활용하는 것이 중요해요. 이 글에서 소개한 내용들을 바탕으로 여러분의 쿼리를 분석해보세요. 처음에는 어려울 수 있지만, 꾸준히 연습하다 보면 점점 익숙해질 거예요.

지금까지 MySQL EXPLAIN 보는법 결과로 쿼리 최적화는 단순히 성능 향상을 위한 것만은 아니에요. 이는 데이터베이스의 동작 원리를 이해하고, 더 나은 데이터 모델링과 애플리케이션 설계를 할 수 있게 해주는 중요한 스킬이에요.

여러분도 이제 MySQL EXPLAIN 보는법 활용해 쿼리 최적화의 달인이 되어보는 건 어떨까요? 데이터베이스 성능 향상을 통해 더 빠르고 효율적인 애플리케이션을 만들 수 있을 거예요. 화이팅!

자주 묻는 질문 (Q&A)

Q1: EXPLAIN은 실제로 쿼리를 실행하나요?
A1: 아니요, EXPLAIN은 실제로 쿼리를 실행하지 않고 실행 계획만을 보여줍니다. 따라서 데이터를 변경하는 INSERT, UPDATE, DELETE 쿼리에도 안전하게 사용할 수 있어요.

Q2: EXPLAIN EXTENDED와 일반 EXPLAIN의 차이는 무엇인가요?
A2: EXPLAIN EXTENDED는 일반 EXPLAIN보다 더 자세한 정보를 제공합니다. 특히 필터링된 컬럼의 정보나 가상 테이블의 정보 등을 추가로 볼 수 있어요.

Q3: type 컬럼에서 가장 좋은 값은 무엇인가요?
A3: 일반적으로 ‘system’과 ‘const’가 가장 좋은 값입니다. 이는 테이블에서 최대 한 개의 행만을 읽는다는 의미예요. 그 다음으로는 ‘eq_ref’, ‘ref’, ‘range’ 순으로 좋다고 볼 수 있습니다.

Q4: rows 값이 실제 결과 행 수와 다른 이유는 무엇인가요?
A4: rows는 예상 값이기 때문에 실제 결과와 다를 수 있어요. MySQL은 통계 정보를 바탕으로 이 값을 추정하는데, 항상 정확할 수는 없답니다.

Q5: Extra 컬럼에서 “Using temporary”와 “Using filesort”는 무엇을 의미하나요?
A5: “Using temporary”는 임시 테이블을 사용한다는 뜻이고, “Using filesort”는 정렬을 위해 추가적인 패스가 필요하다는 뜻입니다. 둘 다 성능에 부정적인 영향을 줄 수 있으므로, 가능하면 피하는 것이 좋아요.

Q6: EXPLAIN 결과를 개선하기 위한 가장 일반적인 방법은 무엇인가요?
A6: 적절한 인덱스 추가가 가장 일반적이고 효과적인 방법입니다. 그 외에도 쿼리 재작성, 테이블 구조 변경, 데이터 분할 등의 방법을 사용할 수 있어요.

Q7: EXPLAIN을 자주 사용해야 하나요?
A7: 네, 특히 복잡한 쿼리나 대용량 데이터를 다루는 쿼리를 작성할 때는 항상 EXPLAIN을 사용해보는 것이 좋습니다. 이를 통해 잠재적인 성능 문제를 미리 발견하고 해결할 수 있어요.