[Real Mysql 8.0] EXPLAIN ANALYZE 실행계획

 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있다. 이전 버전에서는 EXPLAIN EXTENDED와 EXPLAIN PARTITIONS 명령이 구분돼 있었지만, 모든 내용이 통합되어 보이도록 개선되었다.

쿼리의 실행 시간 확인

EXPLAIN ANALYZE
SELECT e.emp_no, avg(s.salary)
FROM employees e 
	INNER JOIN salaries s 
    	ON s.emp_no=e.emp_no
		AND s.salary > 50000
		AND s.from_data <= '1990-01-01'
		AND s.to_date > '1990-01-01'
WHERE e.first_name='Matt'
GROUP BY e.hire_date \G

 

 

EXPLAIN ANALYZE 실행 결과

A) -> Table scan on <temporary> (actual time=0.001..0.004 rows=48 loops=1)
B)      -> Aggregate using temporary table (actual time=3.799..3.808 rows=48 loops=1)
C)           -> Nested loop inner join (cost=685.24 rows=135)
                            (actual time=0.367..3.602 rows=48 loops=1)
D)                -> Index lookup on e using ix_firstname (first_name='Matt')
                            (cost=215.08 rows=233)
                            (actual time=0.348..1.046 rows=233 loops=1)
E)                 -> Filter: ((s.salary > 50000) and (s.from_date <= DATE'1990-01-01')) 
                            (cost=0.98 rows=1)
                            (actual time=0.009..0.011 rows=0 loops=233)
F)                      -> Index lookup on s using PRIMARY (emp_no=e.emp_no) 
                            (cost=0.98 rows=10)
                            (actual time=0.007..0.009 rows=10 loops=233)

실행 규칙

• 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
• 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행

 

실행 계획의 순서

• D) Index lookup on e using ix_firstname
• F) Index lookup on e using PRIMARY
• E) Filter
• C) Nested loop inner join
• B) Aggregate using temporary table
• A) Table scan on <temporary>

 

실행계획 분석

1. employees 테이블의 ix_firstname 인덱스를 통해 first_name='Matt' 조건에 일치하는 레코드를 찾고
2. salaries 테이블의 PRIMARY 키를 통해 emp_no가 (1)번 결과의 emp_no와 동일한 레코드를 찾아서
3. ((s.salary > 50000) and (s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01')) 조건에 일치하는 건만 가져와
4. (1)번과 (3)번의 결과를 조인해서
5. 임시 테이블에 결과를 저장하면서 GROUP BY 집계를 실행하고
6. 임시 테이블의 결과를 읽어서 결과를 반환한다.

 

ANALYZE 명령의 결과 분석

 

ANALYZE 명령의 결과에는 단계별로 실제 소요된 시간(actual time)과 처리한 레코드 건수(rows), 반복 횟수(loops)가 표시된다. 실행계획의 F) 라인에 나열된 필드들의 의미를 한 번 살펴보자

• actual time=0.007..0.009
  첫 번째 숫자 값은 첫 번째 레코드를 가져오는데 걸리는 평균 시간(밀리초)를 의미한다. 두 번째 숫자 값은 마지막 레코드를 가져오는데 걸린 평균 시간(밀리초)를 의미한다.
• rows=10
  employees테이블에서 읽은 emp_no에 일치하는 salaries테이블의 평균 레코드 건수를 의미한다.
• loops=233
  employees테이블에서 읽은 emp_no를 이용해 salaries 테이블의 레코드를 찾는 작업이 반복된 횟수를 의미한다.
  결국 여기서는 employees 테이블에서 읽은 emp_no의 개수가 233개임을 의미한다.
• actual time, rows에서 평균이라는 값이 들어간 이유는 loops가 233이기 때문에 루프 233번의 평균값의 산출이다.