MySQL 8.0中的 explain analyze（译）

原文地址：https://mysqlserverteam.com/mysql-explain-analyze/

MySQL 8.0.18刚刚发布(译者注：原文发表时间为October 17, 2019)，它包含了一个全新的特性来分析和理解查询是如何执行的:explain analyze。

explain analyze是什么

EXPLAIN ANALYZE是一个查询分析工具，它会告诉你MySQL在查询上花了多少时间以及原因。它将计划查询、度量查询并执行查询，同时计算行数并测量在执行计划中不同阶段花费的时间。
当执行完成时，EXPLAIN ANALYZE将打印计划和度量结果，而不是查询结果。(译者注：直白地说就是，explain analyze会真是地执行当前的查询，返回的执行计划以及代价信息，但是不会返回查询自身的结果)

这个新特性是在常规的EXPLAIN查询计划检查工具之上构建的，可以看作是先前在MySQL 8.0中添加的explain forat = tree的扩展。
除了普通的explain将打印的查询计划和估计成本之外，explain analyze还将输出执行计划中单个迭代器的实际成本。

如何使用explain analyze

作为一个示例，我们将使用来自Sakila Sample数据库的数据和一个查询，该查询列出了每个员工在2005年8月完成的工作总量。这个问题很简单:：

SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE ‘2005-08%‘
GROUP BY first_name, last_name;

+------------+-----------+----------+
| first_name | last_name | total    |
+------------+-----------+----------+
| Mike       | Hillyer   | 11853.65 |
| Jon        | Stephens  | 12218.48 |
+------------+-----------+----------+
2 rows in set (0,02 sec)

只有两个人，Mike和Jon，我们在2005年8月得到了他们每个人的总数，EXPLAIN FORMAT=TREE 将会显示执行计划和成本信息

EXPLAIN FORMAT=TREE
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE ‘2005-08%‘
GROUP BY first_name, last_name;

-> Table scan on <temporary>10     -> Aggregate using temporary table
        -> Nested loop inner join  (cost=1757.30 rows=1787)
            -> Table scan on staff  (cost=3.20 rows=2)
            -> Filter: (payment.payment_date like ‘2005-08%‘)  (cost=117.43 rows=894)
                -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043)

但是它没有告诉我们这些估计是否正确，或者查询计划中的哪些操作实际花费了时间。 EXPLAIN ANALYZE可以做到这一点:

EXPLAIN ANALYZE
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE ‘2005-08%‘
GROUP BY first_name, last_name;

-> Table scan on <temporary>  (actual time=0.001..0.001 rows=2 loops=1)
    -> Aggregate using temporary table  (actual time=58.104..58.104 rows=2 loops=1)
        -> Nested loop inner join  (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1)
            -> Table scan on staff  (cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1)
            -> Filter: (payment.payment_date like ‘2005-08%‘)  (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)
                -> Index lookup on payment 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行)。

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

过滤器的估计成本为117.43，估计返回894行，这些估计是查询优化器在执行查询之前根据可用的统计信息做出的。该信息也以EXPLAIN FORMAT=TREE输出的形式出现。
从循环数开，此筛选迭代器的循环次数为2。这是什么意思?要理解这个数字，我们必须查看查询计划中过滤迭代器上面的内容。
在第11行有一个嵌套循环联接，在第12行有一个对staff表的表扫描。
这意味着我们正在执行一个嵌套循环联接，其中我们扫描staff 表，对于该表中的每一行，我们使用索引查找和对付款日期进行筛选来查找付款表中相应的行。
因为staff表中有两行(Mike和Jon)，我们对过滤和第14行上的索引查找进行了两次循环迭代。
对于很多人来说，EXPLAIN ANALYZE提供的一个有趣的信息是实际消耗时间，“0.464..22.767”，
这意味着读取第一行平均需要0.464 ms，读取所有行平均需要22.767 ms。
是平均值吗?是的，因为循环，我们必须对迭代器计时两次，报告的数字是所有循环迭代的平均值。
这意味着过滤的实际执行时间是这些数字的两倍，因此，如果我们查看在嵌套循环迭代器(第11行)中接收所有行所需的时间，它是46.135 ms，比一次运行过滤迭代器所需的时间多一倍多。

译者注：

这里的时间成本计算规律就是，每一步的执行时间，是包含了其子步骤的执行时间的之和，这几个步骤的时间包含关系是这样的：
Nested loop inner join这一层总的时间是58.104ms，也就是整各join的时间成本，包含了
“Table scan on staff表” 和 “payment表上的Filter的时间”
filter的时间又包含了：“index lookup”+“where条件filter条件”的时间，其中最耗时的就是index lookup这一步，也即数据查询的过程。
Index lookup 这一步的时间是19.988*2，乘以2意思是两次循环迭代，因此整个loop join过程的时间大部分都耗费在这个index lookup这个查找上，
平均每次（两次）Filter（22.767）= payment_date like ‘2005-08%‘的筛选 + Index lookup on payment 查找（19.988）

实际读取的行数为2844，而估计值为894行。优化器漏掉了一个因子3（译者注：这一句话不太明白是什么意思，漏掉了什么）。
同样，由于循环的原因，估计的和实际的数字都是所有循环迭代的平均值。
如果我们查看表结构，payment_date列上没有索引或直方图，因此提供给优化器用于计算筛选器选择性的统计信息是有限的。
对于更好的统计信息会产生更准确的估计的示例，我们可以再次查看索引查找迭代器。我们看到索引提供了更精确的统计数据:8043行与8024行实际读取的比较。
这很好，出现这种情况是因为索引附带了额外的统计信息，而非索引列则没有。

那么你能利用这些信息做些什么呢?分析查询并理解为什么它们执行得不好需要一些实践。但一些简单的提示，让你开始:

• 如果你想知道为什么花了这么长时间，看看时间，执行的时候时间都花费在哪一步?
• 如果您想知道为什么优化器选择了该计划，请查看行计数器。估算的行数与实际的行数之间有很大的差异（即几个数量级或更多），这表明您应该仔细看一下。
  优化器根据估算值选择计划，但是查看实际执行情况可能会告诉您，另一个计划会更好。

如果您想知道优化器为什么选择该计划，请查看行计数器。巨大的差异。在估计的行数和实际行数之间的几个数量级或更多)是一个标志，表明您应该更仔细地查看它。
优化器根据估计值选择计划，但是查看实际执行情况可能会告诉您另一个计划会更好。

就是这样！MySQL查询分析工具箱中的另一个工具: 

• 要检查查询计划：EXPLAIN FORMAT=TREE
• 要跟踪查询执行：EXPLAIN ANALYZE
• 要理解计划选择：Optimizer trace

我希望您喜欢这个新特性的快速浏览，解释分析将帮助您分析和理解慢速查询。

译者补充：

关于MySQL执行计划的几种展示方式，explain/explain format=tree/explain format=json/optimizer_trace
其实本质上都是一样的，只是详细程度不一样，对于explain analyze同时可以显式预估的+实际执行的信息，以下是将译文中使用的示例数据库导入到本地后，展示出来的一些信息，与上文中的信息稍有差异。
1，explain
最简洁或者粗略的执行计划显式方式，可以显式：表的访问方式、表之间的驱动顺序，以及Extra列中的其他信息，包括是否产生排序，使用临时表空间等等。
2，expalin format = tree
与explain analyze类似，同时包含了以预估的每一步的代价信息，仅仅是预估信息，并不包含实际执行信息

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Nested loop inner join  (cost=1757.30 rows=1787)
            -> Table scan on staff  (cost=3.20 rows=2)
            -> Filter: (payment.payment_date like ‘2005-08%‘)  (cost=117.43 rows=894)
                -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043)

3，explain format = json
以json的格式显式与expalin format = tree的信息类似，说实话，可读性并不入expalin format = tree

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1757.30"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "staff",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows_examined_per_scan": 2,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "3.00",
              "eval_cost": "0.20",
              "prefix_cost": "3.20",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "staff_id",
              "first_name",
              "last_name"
            ]
          }
        },
        {
          "table": {
            "table_name": "payment",
            "access_type": "ref",
            "possible_keys": [
              "idx_fk_staff_id"
            ],
            "key": "idx_fk_staff_id",
            "used_key_parts": [
              "staff_id"
            ],
            "key_length": "1",
            "ref": [
              "sakila.staff.staff_id"
            ],
            "rows_examined_per_scan": 8043,
            "rows_produced_per_join": 1787,
            "filtered": "11.11",
            "cost_info": {
              "read_cost": "145.50",
              "eval_cost": "178.72",
              "prefix_cost": "1757.30",
              "data_read_per_join": "41K"
            },
            "used_columns": [
              "payment_id",
              "staff_id",
              "amount",
              "payment_date"
            ],
            "attached_condition": "(`sakila`.`payment`.`payment_date` like ‘2005-08%‘)"
          }
        }
      ]
    }
  }
}

4，trace
set session optimizer_trace=‘enabled=ON‘;
explain sql
其实这些信息，都是跟explain format = json或者说explain analyze中，预估部分的一致的，这些数据都跟expalin format = tree一致，只不过trace中会枚举出来标访问时候每种可能性。