SQL에서 explain 사용해보기

EXPLAIN 명령어는 SQL 쿼리의 실행계획을 확인하기 위해 사용된다. ( 사실 나도 실무에서는 안(못)써봤다.. ) 

 

실제로 쿼리에 대해서 확인해보고 속도를 조절하기 위해서 쓸 때에는

EXPLAIN을 찍고 쿼리가 어떻게 실행될지, 어떤 인덱스가 사용될지, 등의

각 단계에서의 예상비용등을 출력해보고 확인해서 쿼리를 수정한다.

 

MySQL을 기준으로 정리해볼 예정이다

 

1. EXPLAIN 명령어 쓰는법

예를 들어 , 아래와 같은 SELECT 쿼리가 있다고 가정하자.

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

 

이 쿼리에 대해 실행 계획을 확인하려면, EXPLAIN을 다음과 같이 사용한다. (그냥 앞에 EXPLAIN 붙이면 됨)

EXPLAIN SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

 

그럼 아래와 같은 내용이 출력된다.

+----+--------------------+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type        | table       | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+--------------------+-------------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | PRIMARY            | employees   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10000 | Using where |
|  2 | DEPENDENT SUBQUERY | departments | NULL       | ref  | idx_location  | idx_location | 1024  | const | 10    | Using index |
+----+--------------------+-------------+------------+------+---------------+------+---------+------+-------+-------------+

 

 

2. EXPLAIN 실행 계획 보는 방법

- id: 쿼리의 각 선택(SELECT) 단계에 대한 식별자

-  select_type: 각 선택의 유형 : PRIMARY는 최상위 쿼리, DEPENDENT SUBQUERY는 서브쿼리

-  table: 쿼리에서 참조하는 테이블의 이름

-  type: 조인 유형을 나타냅니다 (예: ALL, index, range). :  : 이는 ALL(전체 테이블 스캔), index(인덱스 사용), ref(얘도 인덱스 사용인데 좀 더 특정값이나 범위에 대해서만 인덱스 조회함), range(범위 스캔) 등의 값을 가질 수 있다. type이 ALL인 경우 성능이 좋지 않을 수있다. 

-  possible_keys: 쿼리에서 사용 가능한 인덱스

-  key: 실제로 사용된 인덱스

-  key_len: 사용된 인덱스 키의 길이

-  ref: 인덱스의 비교에 사용된 컬럼

-  rows: 쿼리 최적화기가 예측하는 결과 행 수

-  Extra: 추가적인 정보 (예: Using where, Using index).

 

이 실행 정보에서 알 수 있는 내용은 아래와 같다.

 

1. type 컬럼이 ALL인 경우, 이는 전체 테이블 스캔을 의미하며 성능 저하의 원인이 될 수 있다. 
2. rows 값이 큰 경우, 많은 행을 처리해야 함을 의미하며 쿼리 최적화가 필요할 수 있다.

 

3. 실행계획을 통해 쿼리 성능 개선을 하는 방법

- 인덱스 추가 : 인덱스가 사용되지 않는 경우, 인덱스를 추가하여 성능 개선

(근데 쿼리실행계획을 봤을떄 departments 테이블에 location 컬럼에 인덱스가 possible_key로 잡혀있는 것을 보니 해당 내용은 이 예제에서는 할 필요 없을 듯 하다)

CREATE INDEX idx_department_location ON departments(location);

 

 

- 쿼리 재작성 : 서브쿼리를 조인으로 변경

SELECT e.*
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location = 'New York';

 

등등의 작업을 통해 쿼리를 개선하곤한다.