什么是MySQL对等的PostgreSQL的解释分析

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 show。有一个等价的吗?

4 个解决方案

#1

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提供解释和过程分析()http://dev.mysql.com/doc/refman/5.0/en/explain.html http://dev.mysql.com/doc/refman/5.0/en/explain.html

#2

I have not used PostgreSQL before MySQL has EXPLAIN EXTENDED which gives more information than EXPLAIN and may give you the information you are looking for.

在MySQL EXPLAIN EXTENDED之前，我还没有使用PostgreSQL，因为它提供的信息比EXPLAIN更多，可能会提供您正在寻找的信息。

#3

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

过程分析()是为不同的目的,解释,分析指定列的数据集,并建议最好的数据类型,即是有用的,当我们有1000行varchar(255)和要检查我们真的需要多少长度,远东可能知道varchar(23)就足够了

#4

EXPLAIN EXTENDED

MariaDB/MySQL provide something called EXPLAIN EXTENDED. However there is no substitute for EXPLAIN ANALYZE. EXPLAIN EXTENDED provides no timing information whatsoever, and the internal break down is far less verbose.

MariaDB/MySQL提供了解释扩展。然而，解释分析是无可替代的。解释扩展不提供任何时间信息，而内部的崩溃则远没有那么冗长。

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)