什么是 MySQL 相当于 PostgreSQL 的 EXPLAIN ANALYZE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6812655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the MySQL equivalent of PostgreSQL's EXPLAIN ANALYZE
提问by foolish
I'd like to get a detailed query plan in MySQL similar to EXPLAIN ANALYZE shows in PostgreSQL. Is there an equivalent?
我想在 MySQL 中获得一个详细的查询计划，类似于 PostgreSQL 中的 EXPLAIN ANALYZE 显示。有等价物吗？
采纳答案by plague
EDIT: While not a direct equivalent or as detailed as Explain Analyze here are some tools that you can look at
编辑：虽然不是直接等效或像解释分析那样详细，但您可以查看一些工具
mysql offers EXPLAIN and procedure analyse()
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html
mysql 提供 EXPLAIN 和 procedure analysis()
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/procedure-analysis .html
回答by bash-
I have not used PostgreSQL before MySQL has EXPLAIN EXTENDEDwhich gives more information than EXPLAINand may give you the information you are looking for.
我在 MySQL 之前没有使用过 PostgreSQL，EXPLAIN EXTENDED它提供的信息比EXPLAIN并且可能会提供您正在寻找的信息。
回答by Evan Carroll
EXPLAIN EXTENDED
EXPLAIN EXTENDED
MariaDB/MySQL provide something called EXPLAIN EXTENDED. However there is no substitute for EXPLAIN ANALYZE. EXPLAIN EXTENDEDprovides no timing information whatsoever, and the internal break down is far less verbose.
MariaDB/MySQL 提供了一个叫做EXPLAIN EXTENDED. 然而，没有替代品EXPLAIN ANALYZE。EXPLAIN EXTENDED不提供任何时间信息，内部分解也没有那么冗长。
Name: 'EXPLAIN'
Description:
Syntax:
EXPLAIN [explain_type] SELECT select_options
explain_type:
EXTENDED
| PARTITIONS
Or:
EXPLAIN tbl_name
The EXPLAIN statement can be used either as a way to obtain information
about how MySQL executes a statement, or as a synonym for DESCRIBE:
o When you precede a SELECT statement with the keyword EXPLAIN, MySQL
displays information from the optimizer about the query execution
plan. That is, MySQL explains how it would process the statement,
including information about how tables are joined and in which order.
EXPLAIN EXTENDED can be used to obtain additional information.
For information about using EXPLAIN and EXPLAIN EXTENDED to obtain
query execution plan information, see
https://mariadb.com/kb/en/explain/.
o EXPLAIN PARTITIONS is useful only when examining queries involving
partitioned tables. For details, see
http://dev.mysql.com/doc/refman/5.5/en/partitioning-info.html.
o EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS
FROM tbl_name. For information about DESCRIBE and SHOW COLUMNS, see
[HELP DESCRIBE], and [HELP SHOW COLUMNS].
URL: https://mariadb.com/kb/en/explain/
For instance this is taken from this example,
例如这是取自这个例子，
EXPLAIN ANALYZE SELECT *
FROM history AS h1
WHERE EXISTS (
SELECT 1
FROM history AS h2
WHERE h1.lead_id = h2.lead_id
GROUP BY lead_id
HAVING count(is_first OR NULL) > 1
);
Will produce something like this on PostgreSQL,
会在 PostgreSQL 上产生这样的东西，
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on history h1 (cost=0.00..82680.50 rows=1100 width=9) (actual time=0.048..0.065 rows=3 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 3
SubPlan 1
-> GroupAggregate (cost=0.00..37.57 rows=1 width=5) (actual time=0.007..0.007 rows=0 loops=6)
Group Key: h2.lead_id
Filter: (count((h2.is_first OR NULL::boolean)) > 1)
Rows Removed by Filter: 0
-> Seq Scan on history h2 (cost=0.00..37.50 rows=11 width=5) (actual time=0.003..0.004 rows=2 loops=6)
Filter: (h1.lead_id = lead_id)
Rows Removed by Filter: 4
Planning time: 0.149 ms
Execution time: 0.123 ms
(13 rows)
While this is the MySQL equivalent,
虽然这是 MySQL 的等价物，
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | h1 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | h2 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
回答by timtofan
just for clarity, comment on accepted answer (don't have enough karma to add comment)
只是为了清楚起见，对接受的答案发表评论（没有足够的业力来添加评论）
procedure analyse() is for a different purpose that EXPLAIN, it analyzes the data set of specified column and suggests the best data type, i.e. it's useful when we have 1000 rows of varchar(255) and want to check how much length do we really need, f.e. it might tell that varchar(23) would suffice
过程 analysis() 与 EXPLAIN 的目的不同，它分析指定列的数据集并建议最好的数据类型，即当我们有 1000 行 varchar(255) 并想检查我们真的有多少长度时它很有用需要，它可能会告诉 varchar(23) 就足够了
回答by Lukasz Szozda
MySQL 8.0.18 introduces natively EXPLAIN ANALYZE:
MySQL 8.0.18 原生引入了EXPLAIN ANALYZE：
MySQL 8.0.18 introduces EXPLAIN ANALYZE, which runs a query and produces EXPLAIN output along with timing and additional, iterator-based information about how the optimizer's expectations matched the actual execution. For each iterator, the following information is provided:
Estimated execution cost
Estimated number of returned rows
Time to return first row
Time to return all rows (actual cost)
Number of rows returned by the iterator
Number of loops
EXPLAIN ANALYZE can be used only with SELECT statements.
MySQL 8.0.18 引入了 EXPLAIN ANALYZE，它运行查询并产生 EXPLAIN 输出以及关于优化器的期望如何与实际执行相匹配的计时和额外的、基于迭代器的信息。对于每个迭代器，提供以下信息：
估计执行成本
估计的返回行数
返回第一行的时间
返回所有行的时间（实际成本）
迭代器返回的行数
循环次数
EXPLAIN ANALYZE 只能与 SELECT 语句一起使用。
