[MySQL] 쿼리 실행 계획(Query Execution Plan)을 통해 쿼리 최적화하기

쿼리 실행 계획은 데이터베이스에서 특정 SQL 쿼리문을 실행할 때, DB 옵티마이저가 해당 쿼리를 어떻게 실행할 지에 대한 계획을 의미합니다. 그래서 효율적인 데이터베이스 활용을 위해서는 쿼리 실행 계획에 대해 파악하는 것은 필수입니다. 저는 평소에 MySQL 데이터베이스를 많이 사용합니다. 그래서 오늘은 MySQL에서 쿼리 실행 계획을 확인하고 이를 통해 더 좋은 쿼리를 작성하는 방법에 대해 공유해보려합니다.

 

 

쿼리 실행 계획이란?

MySQL :: MySQL 8.4 Reference Manual :: 10.8 Understanding the Query Execution Plan

DBMS(MySQL, PostgreSQL 등)가 SQL 쿼리를 실제로 어떻게 처리할지에 대한 단계별 계획을 말합니다. 즉, 쿼리를 실행하기 전에 DB가 스스로 세우는 가장 빠른 처리 방법에 대한 절략이라고 생각할 수 있습니다. 이런 쿼리 실행 계획은 유동적입니다. 같은 쿼리문일지라도 상황에 따라 다른 쿼리 실행 계획을 가지며 다르게 실행될 수 있다는 뜻입니다.

mysql에서 쿼리 실행 계획을 확인하기 위해서는 EXPLAIN 명령어를 사용합니다. 예를 들어 SELECT * FROM users; 라는 쿼리가 있다고 가정하면 EXPLAIN SELECT * FROM users;처럼 쿼리문 앞에 EXPLAIN 명령어를 붙이기만 하면 됩니다.

 

 

 

쿼리 실행 계획 구조

mysql에서 EXPLAIN 명령어 사용 후 반환되는 값을 구성하는 컬럼 종류는 다음과 같습니다.

✅ id

실행 계획 쿼리별 식별자값입니다.

예시)
하나의 select 문에 대한 실행 계획을 확인한다면 id값이 1인 실행 계획 하나만 반환됩니다. 하지만 서브 쿼리로 select 문이 하나 더 있다면 id값이 1,2로 총 2개의 실행 계획이 반환됩니다.

서브 쿼리 예시

서브 쿼리 실행 계과 예시

 

✅ select_type

실행 계획 쿼리 타입입니다. insert문이라면 insert, delete라면 delete라고 표시됩니다. select문에 대해서는 여러 타입이 존재합니다. 아래 몇 가지 select 타입을 정리해보았습니다.

• SIMPLE

서브 쿼리도 없고 UNION도 없는 가장 단순한 형태의 쿼리문

-- 예시
EXPLAIN
SELECT
  *
FROM
  users u
;

 

• PRIMARY

아우터 쿼리문 ( 예를 들면 서브쿼리가 있을 때 서브 쿼리는 SUBQUERY, 메인 쿼리 혹은 아우터 쿼리는 PRIMARY  )

-- 예시
EXPLAIN
SELECT
  name 
FROM
  users 
WHERE
  id
IN (
  SELECT user_id FROM orders WHERE amount > 100
);

 

• SUBQUERY

서브 쿼리문 ( 예를 들면 서브쿼리가 있을 때 서브 쿼리는 SUBQUERY, 메인 쿼리 혹은 아우터 쿼리는 PRIMARY  )

-- 예시
EXPLAIN
SELECT
  name 
FROM
  users 
WHERE
  id
IN (
  SELECT user_id FROM orders WHERE amount > 100
);

 

✅ table

참조할 테이블명 ( 만약 별칭을 지정했다면 별칭이 표시됩니다 )

 

✅ partitions

파티셔닝이 되어있을 경우 사용될 파티션 표시 ( 만약 파티션되지 않은 테이블일 경우 null로 표시됩니다 )

 

✅ type

( 더 자세한 설명은 아래 '쿼리 실행 계획 예제' 참조 )

• ALL
  풀 스캔하는 경우
• index
  전체 인덱스 스캔하는 경우
• range
  인덱스를 이용한 범위 검색의 경우
• ref
  조인 시 사용되며, 해당 조인에 여러 행이 매칭되는 경우
• eq_ref
  조인 시 사용되며, 해당 조인에 단 하나의 행만 매칭되는 경우
• const
  테이블에 단 하나의 행만 매칭되는 경우 (ex. PRIMARY KEY나 UNIQUE 인덱스를 통한 조회)
• system
  테이블에 단 하나의 행만 존재하는 테이블

 

✅ possible_keys

옵티마이저가 해당 쿼리에 사용할 수 있다고 판단한 인덱스 목록

 

✅ key

선택된 인덱스

 

✅ key_len

선택된 인덱스 길이(단위: 바이트)

 

✅ ref

조인에서 인덱스를 통해 참조한 컬럼 혹은 상수

 

✅ rows

예측된 액세스 행 수

 

✅ filtered

조건을 만족하는 행의 비율

 

✅ Extra

추가 정보

 

 

쿼리 실행 계획 예제

쿼리를 최적화하기 위해 쿼리 실행 계획에서 집중할 부분은 type 컬럼입니다. 여러 SQL 쿼리 예시를 통해 type 컬럼에 대해 더 자세히 알아봅시다. 먼저 다음과 같은 테이블 구조가 있다고 가정합시다.

예시 다이어그램(users, posts 테이블 구조)

테이블은 총 2가지로 users 테이블과 posts 테이블이 존재합니다. 그리고 각 테이블은 id라는 컬럼명으로 PK값을 사용합니다. 이때 posts 테이블은 user_id 컬럼을 가지고 있고, 이는 users 테이블 id에 대한 FK입니다. 이 상황에서 여러 SQL을 사용하고 각 SQL 쿼리문에 대한 EXPLAIN 결과를 확인해봅시다.

 

• 전체 행 조회

전체 users 테이블 데이터를 조회합니다.

EXPLAIN
SELECT
  *
FROM
  users u
;

Name         |Value |
-------------+------+
id           |1     |
select_type  |SIMPLE|
table        |u     |
partitions   |      |
type         |ALL   |
possible_keys|      |
key          |      |
key_len      |      |
ref          |      |
rows         |4     |
filtered     |100.0 |
Extra        |      |

type이 ALL로 표시됩니다. 즉, 모든 데이터를 풀 스캔으로 조회 중임을 알 수 있습니다. 또한 rows, filtered 결과를 통해 현재 users 테이블에는 총 4개의 데이터가 존재하여 이 행의 모두 조회했음을 확인할 수 있습니다.

 

• PK로 조회

users 테이블의 PK 컬럼 id를 활용하여 id가 1인 단 하나의 데이터만 조회합니다.

 

EXPLAIN
SELECT
  *
FROM
  users u
WHERE
  u.id=1
;

Name         |Value  |
-------------+-------+
id           |1      |
select_type  |SIMPLE |
table        |u      |
partitions   |       |
type         |const  |
possible_keys|PRIMARY|
key          |PRIMARY|
key_len      |4      |
ref          |const  |
rows         |1      |
filtered     |100.0  |
Extra        |       |

type이 const로 표시됩니다. 즉, 특정 PK값을 만족하는 데이터는 테이블에서 단 한개만 존재하기 때문에 const로 표시됨을 알 수 있습니다. possible_keys에는 PRIMARY값만을 해당 쿼리에 사용할 수 있는 인덱스 목록으로 선정했고, key에서도 PRIMARY값을 인덱스로 사용하기로 했음을 알 수 있습니다.

 

 

 

• 서브 쿼리 조회

users 테이블의 age 컬럼을 통해 조회합니다. 이때 서브 쿼리문을 추가해봅니다.

EXPLAIN
SELECT
  *
FROM
  users u
WHERE
  u.age = (
	SELECT age FROM users WHERE id=1
  )
;

Name         |Value      |
-------------+-----------+
id           |1          |
select_type  |PRIMARY    |
table        |u          |
partitions   |           |
type         |ALL        |
possible_keys|           |
key          |           |
key_len      |           |
ref          |           |
rows         |4          |
filtered     |25.0       |
Extra        |Using where|




Name         |Value   |
-------------+--------+
id           |2       |
select_type  |SUBQUERY|
table        |users   |
partitions   |        |
type         |const   |
possible_keys|PRIMARY |
key          |PRIMARY |
key_len      |4       |
ref          |const   |
rows         |1       |
filtered     |100.0   |
Extra        |        |

아우터 쿼리와 서브 쿼리에 대한 실행 계획 결과값이 2개 출력됩니다.