[쿼리 튜닝] 1. EXPLAIN을 활용한 실행 계획 분석

▶ 들어가기 전

---

최고의...선배랑 밥을 먹은적이 있었는데, 그때 선배가 많은 것을 전수해주셨다... 

그 중에서 Real MySQL 8.0 이라는 책을 추천받았다.

성능 최적화에 있어서 DB 부분이 가장 직관적이기 때문에 평소에 공부해야지...생각은 하고 있었는데 선배랑 얘기하다보니까 공부 자극도 받고... 아 나도 진짜 형처럼....열심히 해야겠다... 라는 생각도 들었다.

 

JPA를 사용하기는 하지만 Hibernate가 짜준 쿼리를 최적화 해야 할 수도 있으니 공부해보려고 한다.

 

 

 

▷ 이 실습에서 배울 것

---

✅ EXPLAIN을 활용해 쿼리의 실행 계획을 분석하는 방법

✅ MySQL이 데이터를 어떻게 읽고 처리하는지 이해하기

 

 

 

 

 

 

 

 

▶ 실습 환경 세팅(샘플 데이터베이스 준비)

---

기본적으로 MySQL은 깔려있다는 가정하에 

sakila라는 샘플 데이터베이스를 사용할 것이다.

wget https://downloads.mysql.com/docs/sakila-db.tar.gz
tar -xvf sakila-db.tar.gz
mysql -uroot -p < sakila-schema.sql
mysql -uroot -p < sakila-data.sql

이렇게 sakila-db를 다운 받아주고  mysql로 옮겨주자.

 

그리고 show databases;를 해주면

이렇게 sakila 라는 database가 있는 것을 볼 수 있다.

 

안에 table도 많다...

 

그리고 datagrip에 연결해주었다.

 

 

 

 

 

 

 

 

 

▶ 실행 계획(EXPLAIN) 이해하기

---

SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR'

우선 film 테이블에서 영화 정보를 조회하는 쿼리를 실행해보자.

 

오호....

 

 

이제 이 쿼리가 어떻게 실행되는지 실행 계획을 확인해보자.

위 쿼리문 앞에 EXPLAIN 만 붙여주면 된다.

EXPLAIN SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR';

 

오호... 이렇게 나왔다.

 

각 column들이 무엇을 의미할까??

처음부터 알아보자.

 

 

 

▷ 1️⃣ id(쿼리 실행 순서)

---

• 쿼리 내에서 실행되는 쿼리의 고유한 ID 값
• 보통 작은 id 값의 쿼리부터 실행되며, 큰 id 값을 가진 서브쿼리들이 나중에 실행됨.
• 같은 id 값을 가진 행들은 동시에 실행될 가능성 있음.
• 숫자가 클수록 나중에 실행됨.

 

▷ 2️⃣ select_type (쿼리 유형)

---

쿼리의 SELECT 유형을 나타냄

---

SIMPLE : 조인이 없는 단순 SELECT

---

PRIMARY :  최상위 SELECT -> 가장 바깥쪽 SELECT -> 가장 처음 실행되는 메인 쿼리

---

SUBQUERY : 서브쿼리 내부에서 실행되는 SELECT

---

DERIVED :  파생 테이블(서브쿼리의 결과를 임시 테이블로 생성)

 

---

DEPENDENT SUBQUERY : 상위 쿼리의 컬럼을 참조하는 서브쿼리

-> 메인쿼리의 각 행마다 서브쿼리가 실행될 때 발생

-> 서브쿼리가 메인쿼리의 값을 의존해야 함 -> 반복실행되는 경우 발생 -> 해결해야 함

 

발생 조건

서브쿼리가 메인 쿼리의 컬럼을 참조하는 경우

상관 서브쿼리(부모 명령과 자식인 서브쿼리가 특정 관계를 맺는)인 경우

MySQL이 서브쿼리를 먼저 실행하지 못하고, 메인 쿼리의 행마다 실행해야 하는 경우

---

UNCACHEABLE : 캐싱할 수 없는 서브쿼리(매번 실행)

일반적인 서브쿼리는 한번 실행된 후 결과를 캐싱해서 다시사용

BUT, 이 경우 같은 서브쿼리를 여러번 실행해도 결과를 재사용 못함 -> 캐싱 불가함

1️⃣ UNCACHEABLE SUBQUERY 예제 (랜덤 값 사용)
EXPLAIN 
SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = (
    SELECT customer_id 
    FROM rental 
    ORDER BY RAND() 
    LIMIT 1
);

---------
2️⃣ NOW() 같은 동적 함수 사용 시 발생
EXPLAIN 
SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = (
    SELECT customer_id 
    FROM rental 
    WHERE rental_date > NOW() - INTERVAL 30 DAY
    LIMIT 1
);

이런식으로 값이 계속해서 바뀔 때 발생함

---

UNION : UNION을 사용하는 경우 두 번째 이후의 SELECT

-> UNION을 사용할 때, 첫 번째 SELECT는 PRIMARY, 그 이후 SELECT들은 UNION으로 표시됨.

 

UNION RESULT : UNION의 최종 결과

 

예제

EXPLAIN 
SELECT id FROM users 
UNION 
SELECT id FROM admins;

 

결과

+----+-------------+--------+------+---------+------+-------+
| id | select_type | table  | type | key     | rows | Extra |
+----+-------------+--------+------+---------+------+-------+
|  1 | PRIMARY     | users  | ref  | PRIMARY |    5 | NULL  |
|  2 | UNION       | admins | ref  | PRIMARY |    3 | NULL  |
|  3 | UNION RESULT | NULL  | NULL | NULL    | NULL | NULL  |
+----+-------------+--------+------+---------+------+-------++

• id=1 → users 테이블에서 SELECT 실행 (PRIMARY).
• id=2 → admins 테이블에서 SELECT 실행 (UNION).
• id=3 → UNION RESULT: 두 결과를 합쳐서 최종 결과를 반환.

---

UNION ALL : 중복을 제거하지 않는 UNION

-> DISTINCT 연산을 자동으로 수행하는 UNION과는 달리 자동으로 수행하지 않음

-> 데이터가 중복이든 말든 신경 안쓰고 그냥 합치는거

-> 빠름

 

즉, UNION ALL이 성능이 더 좋으므로, 중복이 신경 쓰이지 않는다면 UNION ALL을 사용하는 것이 좋음

하지만 중복을 제거해야 할 때에는 UNION 사용

 

 

 

 

▷ 3️⃣ table

---

• 해당 행에서 다루는 테이블의 이름을 나타냄
• 임시 테이블이나 서브쿼리의 결과가 사용될 경우, derivedN 또는 subqueryN 같은 형식으로 표시됨.

 

 

 

 

▷ 4️⃣ partitions

---

• 사용된 파티션을 보여줌
• 만약 테이블이 파티셔닝 되어있다면, 어느 파티션이 사용되었는지 확인할 수 있음.
• 파티션을 사용하지 않았다면 NULL이 표시됨.

 

 

 

 

▷ 5️⃣ type(조인 유형 또는 접근 방식)

---

• MySQL이 어떤 방식으로 데이터를 조회하는지를 나타냄.
• 좋은 순서대로 정리하면 다음과 같다.

system	1개의 행만 있는 테이블 (최고 효율)	🚀
const	PK나 UNIQUE 인덱스를 사용해 1개의 행만 조회	🚀
eq_ref	조인 시, PK 또는 UNIQUE 인덱스를 사용하여 1:1 매칭	✅
ref	조인 시, 일반적인 인덱스를 사용하여 N:1 매칭	✅
range	BETWEEN, <, >, IN 등의 범위 검색	⚠️
index	테이블 전체를 인덱스만으로 스캔 (테이블 스캔보다 빠름)	⚠️
ALL	테이블 전체를 스캔 (풀 테이블 스캔)	❌

• ALL이 나온다면 인덱스 추가를 고려하는 것이 좋음

 

 

 

 

▷ 6️⃣ possible_keys

---

해당 쿼리에서 사용할 수 있는 인덱스 목록을 보여줌.

하지만 실제 사용되었는지는 key 컬럼을 확인해야 함.

NULL 이라면 해당 테이블에 사용 가능한 인덱스가 없음을 의미함.

 

 

 

 

 

▷ 7️⃣ key

---

 

MySQL이 실제로 사용한 인덱스.

NULL 이라면 인덱스가 사용되지 않은 것 -> 튜닝이 필요할 수 있음

 

 

 

 

▷ 8️⃣ key_len

---

사용된 인덱스의 바이트 길이를 나타냄

 

 

 

 

 

▷ 9️⃣ ref

---

쿼리가 실행될 때, MySQL은 인덱스의 특정 값과 비교하면서 데이터를 찾는데,

key 컬럼에서 사용된 인덱스가 어떤 값과 비교되었는지를 보여주는 컬럼임.

const	상수와 비교됨 (WHERE id = 10)	최적 (빠름) 🚀
table.column	조인할 때, 다른 테이블의 컬럼과 비교됨	좋음 ✅
func	함수 결과와 비교됨 (LOWER(name) = 'test')	비효율적 ⚠️
NULL	인덱스 사용 안 함 (LIKE '%test%')	매우 비효율적 ❌

ref를 보면 MySQL이 어떤 값과 비교하면서 데이터를 찾는지 알 수 있고,

이를 통해 인덱스 활용이 잘 되고 있는지 점검할 수 있다.

 

 

 

 

 

▷ 🔟 rows

---

MySQL이 예측한 조회해야 할 행의 개수

이 값이 클수록 성능 저하 가능성이 높음

 

 

 

 

 

▷ 1️⃣1️⃣ filtered

---

조건을 적용한 후, 남는 행의 비율을 나타냄

예를 들어 filtered = 10.0 이라면, 전체 행의 10%만 남게 된다는 의미

 

 

 

 

 

 

▷ 1️⃣2️⃣ Extra

---

MySQL이 추가적으로 수행한 작업을 설명하는 부분

자주 등장하는 값들:

• Using index → 커버링 인덱스(모든 데이터가 인덱스에 포함됨)
• Using where → WHERE 절을 사용하여 필터링
• Using temporary → 임시 테이블을 사용 (성능 저하 가능)
• Using filesort → 파일 정렬을 수행 (ORDER BY 시 주의)

 

 

---

 

▶ 다음 글 읽으러 가기!

---

https://learning-study.tistory.com/entry/%EC%BF%BC%EB%A6%AC-%ED%8A%9C%EB%8B%9D-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EB%9E%80-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0%EB%93%9C-%EC%9D%B8%EB%8D%B1%EC%8A%A4-Clustered-Index-%EB%85%BC-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0%EB%93%9C-%EC%9D%B8%EB%8D%B1%EC%8A%A4-Non-Clustered-Index

[쿼리 튜닝] 2-1. 인덱스란? / 클러스터드 인덱스 (Clustered Index), 논 클러스터드 인덱스 (Non Clustered I