MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点3：EXPLAIN ANALYZE

MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1：sys.statement_analysis视图

MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2：系统变量的确认

EXPLAIN ANALYZE介绍

EXPLAIN ANALYZE是MySQL 8.0.18版本以后引入的新功能，用于对EXPLAIN命令的扩展。
其输出结果类似EXPLAIN （ format=tree ）一样输出查询计划和估计成本之外，还会输出执行计划中各个迭代器的实际成本和执行时间等信息，帮助用户分析和了解慢SQL的瓶颈。

-预估的执行成本
-预估的返回行数
-实际返回第一条的时间 （ms）
-实际返回所有行的时间 （ms）
-实际迭代器返回的行数 （rows） 
-实际循环次数  loops

复制

EXPLAIN ANALYZE具体语法如下。

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

复制

虽然我们常常使用DESCRIBE获取表结构的信息，EXPLAIN用于获取查询执行计划，实际上EXPLAIN和DESCRIBE 语句其实是同义词也就是功能相同。

例：

mysql> desc t1;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| actor_id   | smallint unsigned | NO   |     | 0       |       |
| first_name | varchar(45)       | NO   |     | NULL    |       |
| last_name  | varchar(45)       | NO   |     | NULL    |       |
| film_info  | text              | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> explain t1;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| actor_id   | smallint unsigned | NO   |     | 0       |       |
| first_name | varchar(45)       | NO   |     | NULL    |       |
| last_name  | varchar(45)       | NO   |     | NULL    |       |
| film_info  | text              | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


复制

EXPLAIN ANALYZE的特性

1. EXPLAIN ANALYZE 不仅可以分析SELECT语句，也可以分析UPDATE和DELETE语句，MySQL 8.0.19开始还支持TABLE语句。

2. 通过EXPLAIN ANALYZE分析SQL语句的时候，会执行SQL但是不返回结果，同时收集执行信息。

例：使用 EXPLAIN ANALYZEDELETE语句

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.02 sec)

mysql>  explain analyze delete from t1 where t1.actor_id in (select actor_id from actor_info);
+--------------------------------------------------------------------------------------------------------
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------
| -> Delete from t1 (immediate)  (cost=23642.12 rows=235996) (actual time=848.799..848.799 rows=0 loops=1)
    -> Nested loop inner join  (cost=23642.12 rows=235996) (actual time=848.443..848.790 rows=200 loops=1)
        -> Table scan on t1  (cost=22.50 rows=200) (actual time=0.042..0.273 rows=200 loops=1)
        -> Single-row index lookup on <subquery2> using <auto_distinct_key> (actor_id=t1.actor_id)  (cost=5240.57..5240.57 rows=1) (actual time=4.242..4.242 rows=1 loops=200)
            -> Materialize with deduplication  (cost=5240.57..5240.57 rows=1180) (actual time=848.395..848.395 rows=200 loops=1)
                -> Table scan on actor_info  (cost=5105.35..5122.57 rows=1180) (actual time=848.265..848.305 rows=200 loops=1)
                    -> Materialize  (cost=5105.33..5105.33 rows=1180) (actual time=848.262..848.262 rows=200 loops=1)
                        -> Group aggregate: group_concat(distinct concat(c.`name`,': ',(select #4)) order by c.`name` ASC separator '; ')  (cost=4987.33 rows=1180) (actual time=2.783..847.454 rows=200 loops=1)
                            -> Nested loop left join  (cost=4441.13 rows=5462) (actual time=0.415..39.124 rows=5462 loops=1)
                                -> Nested loop left join  (cost=2529.43 rows=5462) (actual time=0.406..28.275 rows=5462 loops=1)
                                    -> Nested loop left join  (cost=617.73 rows=5462) (actual time=0.393..7.094 rows=5462 loops=1)
                                        -> Sort: a.actor_id, a.first_name, a.last_name  (cost=20.25 rows=200) (actual time=0.374..0.460 rows=200 loops=1)
                                            -> Table scan on a  (cost=20.25 rows=200) (actual time=0.009..0.274 rows=200 loops=1)
                                        -> Covering index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=0.27 rows=27) (actual time=0.003..0.031 rows=27 loops=200)
                                    -> Covering index lookup on fc using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1) (actual time=0.003..0.004 rows=1 loops=5462)
                                -> Single-row index lookup on c using PRIMARY (category_id=fc.category_id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=5462)
                        -> Select #4 (subquery in projection; dependent)
                            -> Aggregate: group_concat(f.title order by f.title ASC separator ', ')  (cost=24.84 rows=1) (actual time=0.144..0.144 rows=1 loops=5462)
                                -> Nested loop inner join  (cost=22.10 rows=27) (actual time=0.046..0.142 rows=3 loops=5462)
                                    -> Nested loop inner join  (cost=12.55 rows=27) (actual time=0.005..0.074 rows=28 loops=5462)
                                        -> Covering index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=2.99 rows=27) (actual time=0.002..0.028 rows=28 loops=5462)
                                        -> Single-row index lookup on f using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=154076)
                                    -> Single-row covering index lookup on fc using PRIMARY (film_id=fa.film_id, category_id=c.category_id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=0 loops=154076)
 |
+--------------------------------------------------------------------------------------------------------
1 row in set, 2 warnings (0.86 sec)

mysql>
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.02 sec)

复制

通过上面的输出，我们可以看到delete的执行过程和具体执行信息，但是不会实际删除数据。

EXPLAIN 和EXPLAIN ANALYZE的结果对比

下面我们分别通过explain format=tree 和EXPLAIN ANALYZE对SQL语句进行分析。

mysql> explain format=tree select count(*) from actor_info where actor_id in (select actor_id from actor);
+----------------------------------------------------------------------------------------
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------
| -> Aggregate: count(0)  (cost=48584.74 rows=1)
    -> Nested loop inner join  (cost=24985.12 rows=235996)
        -> Index scan on actor using idx_actor_last_name  (cost=20.25 rows=200)
        -> Index lookup on actor_info using <auto_key0> (actor_id=actor.actor_id)  (cost=5105.58..5112.17 rows=27)
            -> Materialize  (cost=5105.33..5105.33 rows=1180)
                -> Group aggregate: group_concat(distinct concat(c.`name`,': ',(select #4)) order by c.`name` ASC separator '; ')  (cost=4987.33 rows=1180)
                    -> Nested loop left join  (cost=4441.13 rows=5462)
                        -> Nested loop left join  (cost=2529.43 rows=5462)
                            -> Nested loop left join  (cost=617.73 rows=5462)
                                -> Sort: a.actor_id, a.first_name, a.last_name  (cost=20.25 rows=200)
                                    -> Table scan on a  (cost=20.25 rows=200)
                                -> Covering index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=0.27 rows=27)
                            -> Covering index lookup on fc using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1)
                        -> Single-row index lookup on c using PRIMARY (category_id=fc.category_id)  (cost=0.25 rows=1)
                -> Select #4 (subquery in projection; dependent)
                    -> Aggregate: group_concat(f.title order by f.title ASC separator ', ')  (cost=24.84 rows=1)
                        -> Nested loop inner join  (cost=22.10 rows=27)
                            -> Nested loop inner join  (cost=12.55 rows=27)
                                -> Covering index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=2.99 rows=27)
                                -> Single-row index lookup on f using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1)
                            -> Single-row covering index lookup on fc using PRIMARY (film_id=fa.film_id, category_id=c.category_id)  (cost=0.25 rows=1)
 |
+----------------------------------------------------------------------------------------
1 row in set, 2 warnings (0.00 sec)

mysql>
mysql> explain analyze select count(*) from actor_info where actor_id in (select actor_id from actor);
+----------------------------------------------------------------------------------------
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------
| -> Aggregate: count(0)  (cost=48584.74 rows=1) (actual time=677.721..677.722 rows=1 loops=1)
    -> Nested loop inner join  (cost=24985.12 rows=235996) (actual time=677.413..677.696 rows=200 loops=1)
        -> Covering index scan on actor using idx_actor_last_name  (cost=20.25 rows=200) (actual time=0.051..0.093 rows=200 loops=1)
        -> Index lookup on actor_info using <auto_key0> (actor_id=actor.actor_id)  (cost=5105.58..5112.17 rows=27) (actual time=3.388..3.388 rows=1 loops=200)
            -> Materialize  (cost=5105.33..5105.33 rows=1180) (actual time=677.355..677.355 rows=200 loops=1)
                -> Group aggregate: group_concat(distinct concat(c.`name`,': ',(select #4)) order by c.`name` ASC separator '; ')  (cost=4987.33 rows=1180) (actual time=2.301..676.262 rows=200 loops=1)
                    -> Nested loop left join  (cost=4441.13 rows=5462) (actual time=0.236..31.592 rows=5462 loops=1)
                        -> Nested loop left join  (cost=2529.43 rows=5462) (actual time=0.227..22.852 rows=5462 loops=1)
                            -> Nested loop left join  (cost=617.73 rows=5462) (actual time=0.211..3.611 rows=5462 loops=1)
                                -> Sort: a.actor_id, a.first_name, a.last_name  (cost=20.25 rows=200) (actual time=0.186..0.297 rows=200 loops=1)
                                    -> Table scan on a  (cost=20.25 rows=200) (actual time=0.033..0.088 rows=200 loops=1)
                                -> Covering index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=0.27 rows=27) (actual time=0.007..0.014 rows=27 loops=200)
                            -> Covering index lookup on fc using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1) (actual time=0.003..0.003 rows=1 loops=5462)
                        -> Single-row index lookup on c using PRIMARY (category_id=fc.category_id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=5462)
                -> Select #4 (subquery in projection; dependent)
                    -> Aggregate: group_concat(f.title order by f.title ASC separator ', ')  (cost=24.84 rows=1) (actual time=0.114..0.114 rows=1 loops=5462)
                        -> Nested loop inner join  (cost=22.10 rows=27) (actual time=0.041..0.112 rows=3 loops=5462)
                            -> Nested loop inner join  (cost=12.55 rows=27) (actual time=0.009..0.053 rows=28 loops=5462)
                                -> Covering index lookup on fa using PRIMARY (actor_id=a.actor_id)  (cost=2.99 rows=27) (actual time=0.007..0.012 rows=28 loops=5462)
                                -> Single-row index lookup on f using PRIMARY (film_id=fa.film_id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=154076)
                            -> Single-row covering index lookup on fc using PRIMARY (film_id=fa.film_id, category_id=c.category_id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=0 loops=154076)
 |
+----------------------------------------------------------------------------------------
1 row in set, 2 warnings (0.68 sec)

mysql>

复制

我们可以看到explain analyze 和explain format=tree的结果的基础上多了(actual time=XXX…XXX rows=XXX loops=XXX)的内容。
也就是上面介绍的如下内容：

-actual time：实际返回第一条的时间 （ms）-
-实际返回所有行的时间 （ms）
-实际迭代器返回的行数 （rows） 
-loops：实际循环次数  loops

复制

例题

Choose two. Examine this query and output:

mysql> EXPLAIN ANALYZE
               SELECT city.CountryCode, country.Name AS Country_Name , city.Name, city.District, city.Population
               FROM world.city
                           INNER JOIN world.country ON country.Code = city.CountryCode
               WHERE country.Continent = ' Asia ' AND city.Population > 1000000
               ORDER BY city.Population DESC\G


复制

Which two statements are true?
A) The country table is accessed as the first table, and then joined to the city table. 
B) 35 rows from the city table are included in the result. 
C) The optimizer estimates that 51 rows in the country table have Continent = ' Asia '. 
D) It takes more than 8 milliseconds to sort the rows. 
E) The query returns exactly 125 rows.

复制

例题解析

• A 正确：我们可以看到使用的是Nest loop方式进行结合（inner join），所以首先会访问驱动表（也就是第一个表）country，然后针对驱动表country的每条记录，利用country.Code = city.CountryCode这个条件，扫描一次内部表city表。

• B 错误：每次通过索引扫描city时返回行数是rows =35，但是 通过Filter后返回行数是rows=2。

• C 错误：优化器预估country表中满足条件（country.Continent = ’ Asia '）的记录为rows =34，实际行数为actual … rows=51。

• D 错误：sort消耗时间是8.431 - 8.033

• E 查询实际返回行数 actual … rows=125。

• country表中满足条件的记录为rows=51，所以会对city循环执行51次 （loops=51）

参考答案：AE

参考

https://dev.mysql.com/doc/refman/8.0/en/explain.html
https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze