#046. 실행계획 EXPLAIN 까막눈 벗어나기 🦅

쿼리가 효율적으로 작동하고 있는 지 알기 위해서 쿼리의 실행 계획을 읽어야 한다. 아래는 백엔드 수준에서 실행 계획(explain)을 읽는 팁을 Real MySQL 책(링크)의 10장 실행계획을 읽고 정리하고 및 실행해 본 기록이다.

MySQL에서는 tradition format으로 뽑아볼 수 있지만 회사에서 사용하는 Postgres는 tree/json/html 형식을 지원한다. 따라서 MySQL은 tradion 형태로 하되 tree 형태로도 데이터를 확인하려고 한다.

1.  들여쓰기 = 쿼리 실행 순서, id

tradition:
참고) id: select 쿼리가 몇 번 일어났는가. (select 쿼리 단위) 실행 순서와는 상관이 없다. id가 같은 테이블이 여러개라면 그 테이블들이 모두 조인되었다는 의미이다.
-> 같은 id의 위쪽 라인이 드라이빙 테이블이고 아래 라인이 드리븐 테이블이다.

select * from dept_emp de
where de.emp_no = (
    select e.emp_no
    from employees e
    where e.first_name = 'Georgi'
    and e.last_name = 'Facello' limit 1
    )

위의 실행쿼리를 tradition 포멧으로 뽑아보면 서브 쿼리의 id가 2이다. 하지만 1 이 먼저 실행되고 2가 나중 실행 되는 것은 아니다.

아래와 같이 tree 형식으로 실행해보면 실행 순서를 알 수 있는데 B부터 아래가 위의 sub-query이다. 이 부분이 먼저 실행되고 이 결과를 바탕으로 A가 실행된다. 즉 실행 순서는 2 -> 1이다. id는 같이 select 되었다는 의미이지 실행 순서를 나타내지 않는다.

A -> Filter: (de.emp_no = (select #2))  (cost=1.1 rows=1) (actual time=0.0655..0.0674 rows=1 loops=1)
    -> Index lookup on de using PRIMARY (emp_no=(select #2))  (cost=1.1 rows=1) (actual time=0.065..0.0668 rows=1 loops=1)
    B -> Select #2 (subquery in condition; run only once)
        -> Limit: 1 row(s)  (cost=65.8 rows=1) (actual time=1.54..1.54 rows=1 loops=1)
            -> Filter: (e.last_name = 'Facello')  (cost=65.8 rows=25.3) (actual time=1.54..1.54 rows=1 loops=1)
                -> Index lookup on e using ix_firstname (first_name='Georgi')  (cost=65.8 rows=253) (actual time=1.54..1.54 rows=1 loops=1)

트리 형태:
들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행된다.
들여쓰기가 다른 레벨에서는 가장 안쪽에 위차한 라인이 먼저 실행된다.
표의 라인 수 = 사용된 테이블의 수 (임시 테이블 포함)

예시)

explain analyze
SELECT e.emp_no, AVG(s.salary)
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
    AND s.salary > 50000
    AND s.from_date <= '1990-01-01'
    AND s.to_date > '1990-01-01'
WHERE e.first_name = 'Matt'
GROUP BY e.hire_date, e.emp_no

아래의 실행 계획을 살펴보면 아래 순서와 같이 실행된다.
D: employees 테이블의 ix_firstname 인덱슬르 사용해 first_nae = 'Matt'을 찾는다
F: primary key를 통해 emp_no = e.emp_no 가 같은 레코드를 찾는다
E: (s.salary > 50000) (s.from_date <= '1990-01-01') (s.to_date > '1990-01-01')  조건에 일치하는 것만 가져온다
C:  D와 E를 조인한다 (Nested Loop)
B: 임시 테이블에 결과를 저장하며 GROUP BY를 집계하고
A: 임시 테이블의 결과를 읽어서 반환한다.

A -> Table scan on <temporary>  (actual time=3.78..3.78 rows=48 loops=1)
    B -> Aggregate using temporary table  (actual time=3.78..3.78 rows=48 loops=1)
        C -> Nested loop inner join  (cost=532 rows=82.2) (actual time=0.451..3.72 rows=48 loops=1)
            D -> Index lookup on e using ix_firstname (first_name='Matt')  (cost=81.5 rows=233) (actual time=0.434..1.06 rows=233 loops=1)
            E -> Filter: ((s.salary > 50000) and (s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01'))  (cost=0.98 rows=0.353) (actual time=0.0101..0.0112 rows=0.206 loops=233)
                F -> Index lookup on s using PRIMARY (emp_no=e.emp_no)  (cost=0.98 rows=9.53) (actual time=0.00752..0.00995 rows=9.53 loops=233)

 

 

2. selection_type = 어떤 타입의 SELECT 쿼리인가

SIMPLE: union이나 subquery 등을 사용하지 않은 단순한 select 쿼리라는 뜻. 일반적으로 제일 바깥쪽 쿼리는 SIMPLE로 표시된다.

PRIMARY: 가장 바깥쪽 쿼리, PRIMARY인 단위 SELECT 는 하나만 존재한다.

DERIVED: UNION 되는 쿼리 결과들을 모아서 저장하는 임시테이블이라는 의미. 
-> 쿼리 튜닝 포인트: 쿼리 튜닝을 하기 위해 실행계획을 확인할 때 가장 먼저 select_type 칼럼 값이 DERIVED 인 것이 있는 지 확인해야한다. 서브 쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리 보다는 조인을 사용할 것을 강력하게 권장한다. 다시 말해 DERIVED 형태의 실행 계획은 조인으로 해결할 수 있게 쿼리를 바꿔야한다. 
-> table에 써진 값을 읽어들여 임시 테이블을 만들었다는 의미이다.

3. type = 테이블 접근 방법

<drived 2>: 라고 표시된다면, id=2 인 실행계획으로부터 만들어진 파생테이블이라는 의미이다.(그림 10.5의 설명 참고할 것)

효율적인 인덱스 사용: const, ref, eq_ref, range
-> 일반적으로 "인덱스 레인지 스캔" 또는 "레인지 스캔"이라고 언급한다.

const: 반드시 1건을 반환한다.  프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절울 가진다.(인덱스 제약)

ref: 조인의 순서나 인덱스의 제약 없이 동등 조건으로 검색
-> 인덱스와 상관없이 동등(Equal) whrjsdlaus ref 로 접근한다

eq_ref: 조인에서 두번째 이후 읽는 테이블은 1건만 존재해서 equal로 조회할 수 있을 때 (두번째 테이블 입장에서는 pk 또는 unique)

range: 인덱스를 범위로 검색한다. 주로 > < IS NULL BETWEEN IN LIKE 연산자에서 사용된다.

개선 해야 하는 접근 방법: index_merge, index, all

index_merge: 2개 이상의 인덱스를 이용했다는 의미. 인덱스가 제대로 되어 있지 않아 여러개를 사용했어햐 했다는 의미.
-> extra 에 보완적인 내용이 표시된다.

index: 인덱스를 잘 쓴다는 의미가 아니다...! 인덱스 full-scan 한다는 의미이다. 

all: 테이블 풀스캔, 온라인 트랜젝션 처리 환경에서는 가장 비효율적인 스캔 방법. 

4. rows / filtered / loops

rows: 테이블에서 읽은 평균 레코드 건수를 의미한다.

loops: 레코드를 찾는 작업이 반복된 횟수.
(참고) 하나의 loop 마다 읽은 데이터의 갯수가 다를 수 있는데, 이를 평균 낸 값이 rows (loops가 1보다 클 때)

filtered: 필터링 되고 남은 레코드의 비율
예를 들어 아래와 같이 이미지로 보여주는 경우 99% 라고 뜨는 경우가 있는데, 설명에 Removed by Filter라고 되어있다. 99% 이상이 쓰이지 않고 버려졌다는 의미이다. filtered 값은 1% 미만이었을 것이라고 추측 가능하다.

5. actual time

actual_time: 첫번째 숫자값은 첫번째 레코드를 가져오는 데 걸린 평균 시간(밀리초)를 의미한다. 두번째 숫자 값은 마지막 레코드를 가져오는 데 걸린 평균 시간(밀리초)를 의미한다. 
-> 하나의 loop 마다 데이터를 가져오는 속도가 다른데, 이를 평균 낸 값을 나타낸다 (loops가 1보다 클 때)

6. key 와 key_len

key: 최종 선택된 인덱스

key_len: 실행 계획이 쿼리를 처리하기 위해 몇개의 칼럼까지 사용했는지
-> CHAR은 4byte, utf8mb4는 항상 메모리공간을 고정 4byte로 계산.

7.  ref

접근 방법이 ref일 때 어떤 값으로 제공됐는 지 보여준다.
상숫값이라면 const, 다른 테이블이면 테이블명+칼럼명 을 보여준다.(예: employees.de.emp_no)

8.  extra

비효율적일 가능성이 높은 내용: distinct / using filesort / using temporary / range checked for each record / using join buffer

distinct: 두 테이블을 조인해서 결과에 다시 distinct 처리를 했다는 의미.

using filesort: ORDER BY를 처리하기 위한 인덱스를 적절히 사용하지 못해 조회한 레코드를 다시 한번 정렬한다. 

using temporary: 임시 테이블을 사용했다.

range checked for each record: extra가 이 내용이면 type 칼럼에는 all이 표시된다. 즉 index map에 표시된 후보 인덱스 중 도움이 되는 후보가 없어 최종적으로 풀 테이블 스캔을 선택했다는 뜻이다.

using join buffer:

 

using index: 커버링 인덱스를 사용했다.

참고

- 8.0.26 등의 최신 버전 에서는 mysql의 format=tree이 되지 않는다. explain analyse 를 사용하면 된다.
https://ryumodrn.tistory.com/47