本文转载自“MySQL解决方案工程师”公众号，由 徐轶韬翻译

作者：Norvald H. Ryeng 译：徐轶韬

MySQL8.0.18刚刚发布，它包含一个全新的功能EXPLAIN ANALYZE，用来分析和理解查询如何执行。

EXPLAIN ANALYZE是什么？

EXPLAIN ANALYZE是一个用于查询的分析工具，它向用户显示MySQL在查询上花费的时间以及原因。它将产生查询计划，并对其进行检测和执行，同时计算行数并度量执行计划中不同点上花费的时间。执行完成后，EXPLAIN ANALYZE将输出计划和度量结果，而不是查询结果。

这项新功能建立在常规的EXPLAIN基础之上，可以看作是MySQL 8.0之前添加的EXPLAIN FORMAT = TREE的扩展。EXPLAIN除了输出查询计划和估计成本之外，EXPLAIN ANALYZE还会输出执行计划中各个迭代器的实际成本。

如何使用？

我们将使用Sakila样本数据库中的数据和一个查询举例说明，该查询列出了每个工作人员在2005年8月累积的总金额。查询非常简单：

1.  `SELECT first_name, last_name, SUM(amount) AS total`


  


2.  `FROM staff INNER JOIN payment`


  


3.   `ON staff.staff_id = payment.staff_id`


  


4.   `AND`


  


5.   `payment_date LIKE '2005-08%'`


  


6.  `GROUP BY first_name, last_name;`


  


7.  `+——————+—————+—————+`


  


8.  `| first_name | last_name | total |`


  


9.  `+——————+—————+—————+`


  


10.  `| Mike | Hillyer | 11853.65 |`


  


11.  `| Jon | Stephens | 12218.48 |`


  


12.  `+——————+—————+—————+`


  


13.  `2 rows in set (0,02 sec)`


 

只有两个人，Mike和Jon，我们在2005年8月获得了他们的总数。

EXPLAIN FORMAT = TREE将向我们显示查询计划和成本估算：

1.       
       
     
     
     

      
        
      
      
      `EXPLAIN FORMAT=TREE`
     
       
     
     
     

  


  


2.  `SELECT first_name, last_name, SUM(amount) AS total`


  


3.  `FROM staff INNER JOIN payment`


  


4.   `ON staff.staff_id = payment.staff_id`


  


5.   `AND`


  


6.   `payment_date LIKE '2005-08%'`


  


7.  `GROUP BY first_name, last_name;`


  


8.  `-> Table scan style="margin: 0px; padding: 0px; max-width: 100%; overflow-wrap: break-word !important; box-sizing: border-box !important; color: rgb(125, 151, 38); line-height: 20px; font-size: 13px !important; white-space: inherit !important;"><temporary>`


  


9.   `-> Aggregate using temporary table`


  


10.   `-> Nested loop inner join (cost=1757.30 rows=1787)`


  


11.   `-> Table scan style="margin: 0px; padding: 0px; max-width: 100%; overflow-wrap: break-word !important; box-sizing: border-box !important; color: rgb(244, 243, 236); line-height: 20px; font-size: 13px !important; white-space: inherit !important;">(cost=3.20 rows=2)`


  


12.   `-> Filter: (payment.payment_date like '2005-08%') (cost=117.43 rows=894)`


  


13.   `-> Index lookup style="margin: 0px; padding: 0px; max-width: 100%; overflow-wrap: break-word !important; box-sizing: border-box !important; color: rgb(95, 145, 130); line-height: 20px; font-size: 13px !important; white-space: inherit !important;">using idx_fk_staff_id (staff_id=staff.staff_id) (cost=117.43 rows=8043)`


 

但这并不能表明这些估计是否正确，或者查询计划实际上是在哪些操作上花费的时间。EXPLAIN ANALYZE将执行以下操作：

1.  `EXPLAIN ANALYZE`


  


2.  `SELECT first_name, last_name, SUM(amount) AS total`


  


3.  `FROM staff INNER JOIN payment`


  


4.   `ON staff.staff_id = payment.staff_id`


  


5.   `AND`


  


6.   `payment_date LIKE '2005-08%'`


  


7.  `GROUP BY first_name, last_name;`


  


8.  `-> Table scan style="margin: 0px; padding: 0px; max-width: 100%; overflow-wrap: break-word !important; box-sizing: border-box !important; color: rgb(125, 151, 38); line-height: 20px; font-size: 13px !important; white-space: inherit !important;"><temporary> (actual time=0.001..0.001 rows=2 loops=1)`


  


9.   `-> Aggregate using temporary table (actual time=58.104..58.104 rows=2 loops=1)`


  


10.   `-> Nested loop inner join (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1)`


  


11.   `-> Table scan style="margin: 0px; padding: 0px; max-width: 100%; overflow-wrap: break-word !important; box-sizing: border-box !important; color: rgb(244, 243, 236); line-height: 20px; font-size: 13px !important; white-space: inherit !important;">(cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1)`


  


12.   `-> Filter: (payment.payment_date like '2005-08%') (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)`


  


13.   `-> Index lookup style="margin: 0px; padding: 0px; max-width: 100%; overflow-wrap: break-word !important; box-sizing: border-box !important; color: rgb(95, 145, 130); line-height: 20px; text-align: left; font-size: 13px !important;">using idx_fk_staff_id (staff_id=staff.staff_id) (cost=117.43 rows=8043) (actual time=0.450..19.988 rows=8024 loops=2`


 

这里有几个新的度量：

• 获取第一行的实际时间（以毫秒为单位）

• 获取所有行的实际时间（以毫秒为单位）

• 实际读取的行数

• 实际循环数

让我们看一个具体的示例，使用过滤条件的迭代器成本估算和实际度量，该迭代器过滤2005年8月的数据（上面EXPLAIN ANALYZE输出中的第13行）。

1.  `Filter: (payment.payment_date like '2005-08%')`


  


2.  `(cost=117.43 rows=894)`


  


3.  `(actual time=0.464..22.767 rows=2844 loops=2)`


 

我们的过滤器的估计成本为117.43，并且估计返回894行。这些估计是由查询优化器根据可用统计信息在执行查询之前进行的。该信息也会在EXPLAIN FORMAT = TREE输出中。

我们将从最后面的循环数开始。此过滤迭代器的循环数为2。这是什么意思？要了解此数字，我们必须查看查询计划中过滤迭代器上方的内容。在第11行上，有一个嵌套循环联接，在第12行上，是在staff表上进行表扫描。这意味着我们正在执行嵌套循环连接，在其中扫描staff表，然后针对该表中的每一行，使用索引查找和过滤的付款日期来查找payment表中的相应条目。由于staff表中有两行（Mike和Jon），因此我们在第14行的索引查找上获得了两个循环迭代。

对于许多人来说，EXPLAIN ANALYZE提供的最有趣的新信息是实际时间“ 0.464..22.767”，这意味着平均花费0.464毫秒读取第一行，而花费22.767毫秒读取所有行。平均时间？是的，由于存在循环，我们必须对该迭代器进行两次计时，并且报告的数字是所有循环迭代的平均值。这意味着过滤的实际执行时间是这些数字的两倍。如果我们看一下在嵌套循环迭代器（第11行）中上一级接收所有行的时间，为46.135毫秒，这是运行一次过滤迭代器的时间的两倍多。

这个时间反映了整个子树在执行过滤操作时的根部时间，即，使用索引查找迭代器读取行，然后评估付款日期为2005年8月的时间。如果我们查看索引循环迭代器（第14行），我们看到相应的数字分别为0.450和19.988 ms。这意味着大部分时间都花在了使用索引查找来读取行上，并且与读取数据相比，实际的过滤成本相对低廉。

实际读取的行数为2844，而估计为894行。优化器错过了3倍的因素。同样，由于循环，估计值和实际值都是所有循环迭代的平均值。如果我们查看schema，发现payment_date列上没有索引或直方图，因此提供给优化器的统计信息是有限的。如果使用更好的统计信息可以得出更准确的估计值，我们可以再次查看索引查找迭代器。我们看到该索引提供了更加准确的统计信息：估计8043行与8024实际读取行。发生这种情况是因为索引附带了额外的统计信息，而这些数据对于非索引列是不存在的。

那么用户可以使用这些信息做什么？需要一定的练习，用户才可以分析查询并理解为什么它们表现不佳。但是，这里有一些帮助入门的简单提示：

• 如果疑惑为何花费这么长时间，请查看时间。执行时间花在哪里？

• 如果您想知道为什么优化器选择了该计划，请查看行计数器。如果估计的行数与实际的行数之间存在较大差异（即，几个数量级或更多），需要仔细看一下。优化器根据估算值选择计划，但是查看实际执行情况可能会告诉您，另一个计划会更好。

EXPLAIN ANALYZE是MySQL查询分析工具里面的一个新工具：

• 检查查询计划： EXPLAIN FORMAT = TREE

• 分析查询执行： EXPLAIN ANALYZE

• 了解计划选择： Optimizer trace

希望您喜欢这个新功能，EXPLAIN ANALYZE将帮助您分析和了解缓慢的查询。

---

扫码加入MySQL技术Q群

（群号：****650149401）

本文分享自微信公众号 - 老叶茶馆（iMySQL_WX）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。