作者: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月累积的总金额。查询非常简单:-
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)
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 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>
-
-> Aggregate using temporary table
-
-> Nested loop inner join (cost=1757.30 rows=1787)
-
-> 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)
-
-> Filter: (payment.payment_date like '2005-08%') (cost=117.43 rows=894)
-
-> 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
-
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 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)
-
-> 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 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)
-
-> Filter: (payment.payment_date like '2005-08%') (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)
-
-> 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行)。
-
Filter: (payment.payment_date like '2005-08%')
-
(cost=117.43 rows=894)
-
(actual time=0.464..22.767 rows=2844 loops=2)
- 如果疑惑为何花费这么长时间,请查看时间。执行时间花在哪里?
- 如果您想知道为什么优化器选择了该计划,请查看行计数器。如果估计的行数与实际的行数之间存在较大差异(即,几个数量级或更多),需要仔细看一下。优化器根据估算值选择计划,但是查看实际执行情况可能会告诉您,另一个计划会更好。
EXPLAIN ANALYZE是MySQL查询分析工具里面的一个新工具:
- 检查查询计划:EXPLAIN FORMAT = TREE
- 分析查询执行:EXPLAIN ANALYZE
- 了解计划选择:Optimizer trace