MySQL EXPLAIN ANALYZE - 8.0.18

Share

Last Updated on 10월 19, 2021 by Jade(정현호)

New Feature

MySQL 8.0.18 버전은 2019년 10월 14일에 GA 가 되었으며 8.0.18 의 New Feature 로 EXPLAIN ANALYZE 기능이 추가 되었습니다.

            

EXPLAIN ANALYZE

MySQL 를 시작 하면서 실행계획 관련하여 처음 검색 과 확인 해본 내용이 통계정보를 기반의 estimated rows 가 아니라 actual rows 를 볼수 있는 기능이 있는지 확인해봤던 적이 있었습니다.

Oracle 에서는 10046 event 를 시작으로 gather_plan_statistics 힌트, _rowsource_execution_statistics 파라미터 , monitor 힌트  또는 5초 이상의 수행 등 과 같이 실제 수행된 SQL Plan 의 Actual Row Source Execution 정보를 확인 할 수 있는 방법이 있습니다. 

하지만 MySQL 의 explain 은 통계정보를 기반으로 한 estimated rows 정보만 제공하고 있었기 때문에 오퍼레이션 별 정확한 행수가 아니었으며 일치되지 않았습니다. 

8.0.18 에서 부터 explain analyze 기능이 추가되었으며 해당 기능은 쿼리 실행에 소비되는 시간과 수행에 처리되는 rows 그리고 측정한 결과 건수 등을 쿼리 결과 대신에 측정된 결과 값을 보여주게 됩니다 즉 수행한 SQL의 Actual rows 정보를 보여주게 됩니다.


간략하게 아래와 같이 테스트를 통해 내용을 확인 해보도록 하겠습니다.

[참고] 포스팅 테스트 MySQL 버전: 8.0.23


• 테스트 테이블 , 데이터 생성

mysql> create database test;

mysql> use test;

mysql> CREATE TABLE t1 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

mysql> insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);

mysql> insert into t1
select * from t1
union all
select * from t1
union all
select * from t1
union all
select * from t1;

-- insert select 문 x 3 더 수행


mysql> CREATE TABLE t2 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

mysql> insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5);

mysql> insert into t2
select * from t2
union all
select * from t2
union all
select * from t2
union all
select * from t2;

-- insert select 문 x 3 더 수행



mysql> CREATE TABLE t3 (
    pk INTEGER NOT NULL auto_increment PRIMARY KEY,
    i INTEGER DEFAULT NULL
);

mysql> insert into t3(i) values (1),(2),(3),(4),(5);

mysql> insert into t3(i)
select i from t3
union all
select i from t3
union all
select i from t3
union all
select i from t3;

-- insert select 문 x 3 더 수행


• 쿼리 플랜 확인

쿼리 수행은 Count(*) 를 통해 쿼리의 결과 수를 확인 하고 기존 방식의 explain 을 수행 한 결과 그리고 explain analyze 를 수행한 결과 순으로 실행한 결과 입니다. explain 도 format=tree 를 사용하여 같은 출력 형태로 진행하였습니다.

---- Query 1
mysql> SELECT count(*) FROM t1 JOIN t2 ON (t1.c1 = t2.c2);
+----------+
| count(*) |
+----------+
|  1953125 |
+----------+

mysql> EXPLAIN format=tree 
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=976879.74 rows=976563)
    -> Table scan on t2  (cost=0.01 rows=3125)
    -> Hash
        -> Table scan on t1  (cost=314.75 rows=3125)

mysql> EXPLAIN ANALYZE 
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G

*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=976879.74 rows=976563) (actual time=1.229..74.317 rows=1953125 loops=1)
    -> Table scan on t2  (cost=0.01 rows=3125) (actual time=0.006..2.386 rows=3125 loops=1)
    -> Hash
        -> Table scan on t1  (cost=314.75 rows=3125) (actual time=0.017..0.983 rows=3125 loops=1)




---- Query 2

mysql> SELECT count(*) FROM t3 WHERE i > 2;
+----------+
| count(*) |
+----------+
|     1875 |
+----------+


mysql> EXPLAIN format=tree 
SELECT * FROM t3 WHERE i > 2\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 2)  (cost=314.50 rows=1042)
    -> Table scan on t3  (cost=314.50 rows=3125)

mysql> EXPLAIN ANALYZE 
SELECT * FROM t3 WHERE i > 2\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 2)  (cost=314.50 rows=1042) (actual time=0.024..0.487 rows=1875 loops=1)
    -> Table scan on t3  (cost=314.50 rows=3125) (actual time=0.022..0.350 rows=3125 loops=1)





---- Query 3

mysql> SELECT count(*) FROM t3 WHERE pk > 17;
+----------+
| count(*) |
+----------+
|     3108 |
+----------+

mysql> EXPLAIN format=tree 
SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=622.59 rows=3108)
    -> Index range scan on t3 using PRIMARY  (cost=622.59 rows=3108)

mysql> EXPLAIN ANALYZE 
SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=622.59 rows=3108) (actual time=0.057..0.604 rows=3108 loops=1)
    -> Index range scan on t3 using PRIMARY  (cost=622.59 rows=3108) (actual time=0.055..0.453 rows=3108 loops=1)


[참고] 포스팅에서 사용된 버전은 MySQL 8.0.23 으로 hash join 이 사용되고 있습니다.

explain analyze 사용시 확인 되는 내용으로는 먼저 최종 결과 건수가 실제 count  한 결과와 동일하며,  각 오퍼레이션 별 처리 건수 , Cost , actual time  정보 등을 확인 할 수 있습니다.
           

확인 되는 정보

explain analyze 를 통해 SQL 수행시 아래와 같은 정보를 확인 할 수 있습니다.

• Estimated execution cost

    예상 실행 비용
    (Some iterators are not accounted for by the cost model, and so are not included in the estimate.)

• Estimated number of returned rows

• Time to return first row

    첫 번째 행을 반한하는 시간

• Time to return all rows (actual cost)(단위 milliseconds)

     모든 행을 반환하는데 걸린 시간(실제 비용)
     (When there are multiple loops, this figure shows the average time per loop.)

• Number of rows returned by the iterator

• Number of loops
    수행되는 루프의 수, explain analyze 결과에서 "loops"

• explain analyze 는 항상 tree 출력 형식을 사용

• explain analyze 는 select 문은 물론 update 과 delete 문에서도 사용 가능

• 8.0.19 버전 부터는 TABLE 문과도 사용가능
   관련 링크 : TABLE Statement [Link]

• 8.0.20 버전 부터 실행 중 CTRL+C 또는 Kill Query 를 통해서 종료 가능


이와 같은 여러 정보를 확인 할 수 있으며 새로 추가 된 explain analyze 를 통해 확인 가능한 실측 정보를 통해서 조금 더 SQL 최적화 나 튜닝하는데 기존 보다 더 도움이 될 것이라고 예상됩니다
         

Reference

Reference link
dev.mysql.com/news-8-0-18.html [Link]
dev.mysql.com/explain.html [Link]
mysqlserverteam.com/explain-analyze [Link]



다른 연관된 글

 

 

 

 

 

 

 

 

0
글에 대한 당신의 생각을 기다립니다. 댓글 의견 주세요!x