MySQL EXPLAIN ANALYZE - 8.0.18

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

Contents

New Feature
EXPLAIN ANALYZE
확인 되는 정보
Reference

New Feature

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

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.18 (2019-10-14, General Availability)

NDB Cluster: A query handled using a pushed condition produced incorrect results when it included an ORDER BY clause. (Bug #29595346)

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]

다른 연관된 글

MySQL – Xtrabackup 8.0.23 릴리즈

Percona 사의 온라인 백업 도구인 Xtrabackup 이 3월 22일에 8.0.23-16.0 버전이 릴리즈 되었습니다.

MySQL MSR Replication 구성 – Multi Source Replication

MSR - Multi Source Replication 는 1개의 Slave(Replica) 서버에 여러개의 Master DB를 연결하여 복제하는 구조로 여러개의 Master DB의 내용을 1곳의 Slave(Replica) 에 모으는 역활을 하게 됩니다.

MySQL GTID 를 사용한 Replication(복제) 설정

GTID 는 Global Transaction IDentifier 약자로 MySQL 데이터베이스에서 커밋되는 각 트랜잭션과 함께 생성되고 트랜잭션에 연결되는 고유한 식별자입니다

MySQL Shell – Dump Utility – mysqlsh

MySQL Shell은 MySQL 용 고급 클라이언트 및 코드 편집기입니다 mysql 클라이언트 와 유사한 SQL 기능 외에도 MySQL Shell은 JavaScript 및 Python에 대한 스크립팅 기능을 제공하고 MySQL 작업을 위한 API를 포함합니다

오라클 클라우드 MySQL Database Service (1) – MDS – Oracle Cloud

MDS 는 MySQL Database Service 의 약자로 MySQL 데이터베이스의 PaaS형 클라우드 서비스를 의미 합니다.

MySQL 쿼리 프로파일링(Query Profiling)

MySQL 에서 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인 할 수 있는 기능을 제공하며 쿼리 프로파일링(Query Profiling) 기능을 제공하고 있습니다

MySQL 8.0 invisible columns

MySQL 은 MySQL 8.0.23부터 invisible columns을 지원합니다 invisible columns은 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 참조되는 경우 액세스 할 수 있습니다.

MySQL8 해시조인(Hash Join)

MySQL 에서는 오랜 기간 조인의 Method가 nested loop join 방식만 지원되어왔습니다

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io

bug.mysql.com

Statistics

MySQL EXPLAIN ANALYZE - 8.0.18

New Feature

EXPLAIN ANALYZE

확인 되는 정보

Reference

You may also like...

New Feature

EXPLAIN ANALYZE

확인 되는 정보

Reference

You may also like...

MySQL 8.0 버전 Audit 기능 사용 - MySQL Community Edition

MySQL - 아키텍처 정리 (1) - Real MySQL 8.0

MySQL Clustered Index 와 Fast Index Creation In InnoDB