실행 계획

[wishoon]

주제

MySQL 8.0 에서의 실행 계획 확인 및 분석

선정 이유

MySQL은 데이터를 빠르게 조회할 수 있게 해주기 위해 옵티마이저가 쿼리를 최적으로 처리될 수 있게 쿼리의 실행 계획을 수립할 수 있게 해준다. 하지만 매번 좋은 실행 계획을 만드는 것이 아니기 떄문에 이를 확인해야 하는데 어떻게 확인을 할 수 있는지, 어떻게 분석할 수 있는지 궁금해서 선정하였다.

해당 텍스트

실행 계획 출력 포맷

MySQL 8.0 이전 버전에서는 EXPLAIN EXTENDED 또는 EXPLAIN PARTITIONS 명령이 구분되어 있었음. MySQL 8.0 버전부터는 모든 내용이 통합되어 보이도록 개선됨.

추가적으로 FORMAT 옵션을 사용해 실행 계획의 표시 방법을 JSON이나 TREE, 단순 테이블 형태로 선택할 수 있음.

테이블 포맷 표시

EXPLAIN
SELECT *
FROM employees e 
INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE first_name = 'ABC';

트리 포맷 표시

EXPLAIN FORMAT = TREE
SELECT *
FROM employees e
INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE fisrt_name = 'ABC'\G

EXPLAIN: -> Nested loop innner join (cost=2.40 rows=10)
    -> Index lookup on e using ix_firstname (first_name='ABC') (cost=0.35 rows=1)
    -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.25 rows=10)

JSON 포맷 표시

EXPLAIN FORMAT = JSON
SELECT *
FROM employees e
INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE first_name = 'ABC'\G

실행 계획 분석

실행 계획 출력 포맷으로 어떻게 볼 수 있는지 보다, 어떤 접근 방법을 사용해서 어떤 최적화를 수행하는지, 어떤 인덱스를 사용하는지 등을 이해하는 것이 더 중요하다.

글을 읽으면서 “실행 계획의 모든 것을 외울 필요가 있을까?” 라는 생각을 했다. 
큰 틀만 이해하고 필요한 부분이 생겼을 때 찾아보려고 한다. 이번 정리글에서는 id 칼럼을 대표적으로 살펴보자.

id 칼럼

하나의 SELECT 문장은 다시 1개 이상의 서브쿼리 문장을 포함할 수 있는데 다음과 같이 분리해서 생각한 것을 “단위 쿼리"라고 표현한다.

SELECT ... FROM tb_test1;
SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;

실행 계획에서 가장 왼쪽에 표시되는 Id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다. 위의 쿼리의 경우 실행 계획에서 최소 2개의 id 값이 표시될 것이다.

만약, 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.

하지만 쿼리 문장이 3개의 단위 SELECT 쿼리로 구성돼 있으므로 실행 계획의 각 레코드가 각기 다른 id 값을 지닌 것을 확인할 수 있다. 한 가지 주의해야 할 것은 실행 계획의 id 칼럼이 테이블의 접근 순서를 의미하지는 않는다는 것이다.

이제 앞에서 정리되었던 실행 계획의 포맷을 통해서 순서를 확인해보자.

-> Filter: (do.emp_no = (select #2)) (cost=1.10 rows=1)
    -> Index lookup on de using ix_empno_fromdate (emp_no=select #2)) (cost=1.10 rows=1)
    -> Select #2 (subquery in condition; run only once)
        -> Limit: 1 row(s)
            -> Filter: (e.last_name = 'Facello') (cost=70.49 rows=25)
                -> Index lookup on e using ix_firstname (first_name='Georgi') (cost=70.49 rows=253)

실행 계획을 통해서 employees 테이블을 먼저 읽고, 해당 결과를 통해서 dept_name 테이블을 읽는 순서로 실행이 된 것을 확인할 수 있다. 또한 employees 테이블의 ix_firstname 인덱스를 읽는 부분이 가장 들여쓰기가 많이 된 것을 확인할 수도 있다.

관련 페이지

412p ~ 419p