online - ¿Cuál es el equivalente en MySQL de EXPLICAR ANALIZAR de PostgreSQL?

pgloader

Question

Me gustaría obtener un plan de consulta detallado en MySQL similar al EXPLAIN ANALYZE que se muestra en PostgreSQL. ¿Hay un equivalente?

Answer 1

EXPLAIN EXTENDED

MariaDB / MySQL provee algo llamado EXPLAIN EXTENDED . Sin embargo, no hay sustituto para EXPLAIN ANALYZE . EXPLAIN EXTENDED no proporciona información de temporización alguna, y el desglose interno es mucho menos detallado.

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/

Por ejemplo, esto se toma de este ejemplo ,

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
);

Producirá algo como esto en 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)

Si bien este es el equivalente de 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)

Answer 2

EDITAR: Aunque no es un equivalente directo o tan detallado como Explain Analyze, aquí hay algunas herramientas que puede consultar.

mysql ofrece EXPLICAR y analizar el procedimiento ()
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

Answer 3

solo para mayor claridad, comente sobre la respuesta aceptada (no tiene suficiente karma para agregar comentarios)

Procedure Analyse () es para un propósito diferente que EXPLICA, analiza el conjunto de datos de la columna especificada y sugiere el mejor tipo de datos, es decir, es útil cuando tenemos 1000 filas de varchar (255) y queremos verificar cuánta longitud realmente necesito, fe, podría decir que varchar (23) sería suficiente

Answer 4

No he usado PostgreSQL antes de que MySQL tenga EXPLAIN EXTENDED que proporciona más información que EXPLAIN y puede brindarle la información que está buscando.