[MYSQL] 실행계획 - 2

코스트 모델

MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 한다.

1. 디스크로부터 데이터 페이지 읽기
2. 메모리(InnoDB 버퍼풀)로부터 데이터 페이지 읽기
3. 인덱스 키 비교
4. 레코드 평가
5. 메모리 임시 테이블 작업
6. 디스크 임시 테이블 작업

전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다.

MySQL 8.0 서버의 코스트 모델은 다음 2개 테이블에 저장돼 있는 설정값을 사용하는데, 두 테이블 모두 mysqlDB에 존재한다.

1. server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용관리
2. engine_cost : 레코드를 가진 데이터 페이를 가져오는 데 필요한 비용 관리

두 테이블은 5개의 공통 칼럼을 가지고 있다.

• cost_name: 코스트 모델의 각 단위 작업
• default_value: 각 단위 작업의 비용(기본값, MySQL 서버 소스 코드에 설정된 값)
• cost_value: DBMS 관리자가 설정한 값(NULL이면 default_value 값 사용)
• last_updated: 단위 작업의 비용이 변경된 시점
• comment: 비용에 대한 추가 설명

engine_cost 테이블은 아래 2개의 컬럼을 더 가지고 있다.

• engine_name: 비용이 적용된 스토리지 엔진
• device_type: 디스크 타입

보통 default로 설정되어 있는 값은 안건드리는게 좋다.

실행 계획 확인

MySQL의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있다.

실행 계획의 포맷은 아래와 같이 테이블, 트리, JSON 3가지 중 하나를 선택할 수 있다.

• EXPLAIN [FORMAT=TREE or JSON] (테이블이 기본값)

EXPLAIN ANALYZE 명령으로 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있다.

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_date<='1990-01-01'
						 AND s.to_date>'1990-01-01'
WHERE e.first_name='Matt'
GROUP BY e.hire_date;

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')
																			and (s.to_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)

 

위와 같은 TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미하며 규칙은 아래와 같다.

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

위 실행 계획을 풀어서 설명하면

1. D, employees 테이블의 ix_firstname 인덱스를 통해 first_name=’Matt’ 조건에 일치하는 레코드를 찾는다.
2. F, salaries 테이블의 PK를 이용해 1번 결과의 emp_no와 같은 emp_no를 가진 레코드를 s테이블에서 찾는다.
3. E, 2번 결과를 s.salary > 50000 and s.from_date <= DATE'1990-01-01' and s.to_date > DATE'1990-01-01' 조건으로 필터링한다.
4. C, 1번 결과와 3번 결과를 조인한다.
5. B, 임시 테이블에 결과를 저장하며 GROUP BY 집계를 실행한다.
6. A, 임시 테이블의 결과를 읽어서 결과를 반환한다.

실행 계획의 F라인을 자세히 분석해보자.

• actual time=0.007 ..0.009
  employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블에서 일치하는 레코드를 검색하는 데 걸린 시간(밀리초)을 의미한다. 이때 숫자값이 2개인데 앞이 초기값 마지막 레코드를 가져오는데 걸린 평균