8.8.1 Optimizing Queries with EXPLAIN

The EXPLAIN statement provides information about how MySQL executes statements:

EXPLAIN语句提供有关MySQL如何执行语句的信息：

• EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

• EXPLAIN与SELECT、DELETE、INSERT、REPLACE和UPDATE语句一起协作

• When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about using EXPLAIN to obtain execution plan information, see Section 8.8.2, “EXPLAIN Output Format”.

• 当EXPLAIN与可解释语句一起使用时，MySQL显示来自优化器的关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关如何关联表以及以何种顺序关联表的信息。有关使用EXPLAIN获取执行计划信息的信息，请参阅第8.8.2节“EXPLAIN Output Format”。

• When EXPLAIN is used with FOR CONNECTION connection_id rather than an explainable statement, it displays the execution plan for the statement executing in the named connection. See Section 8.8.4, “Obtaining Execution Plan Information for a Named Connection”.

• 当EXPLAIN与FOR CONNECTION connect_id而不是可解释语句一起使用时，它将显示在该连接中执行语句的执行计划。请参阅第8.8.4节“获取命名连接的执行计划信息”。

• For SELECT statements, EXPLAIN produces additional execution plan information that can be displayed using SHOW WARNINGS. See Section 8.8.3, “Extended EXPLAIN Output Format”.

• 对于SELECT语句，EXPLAIN生成可以使用SHOW WARNINGS显示的其他执行计划信息。见第8.8.3节“扩展解释输出格式”。

• EXPLAIN is useful for examining queries involving partitioned tables. See Section 24.3.5, “Obtaining Information About Partitions”.

• EXPLAIN对于检查涉及分区表的查询也是有用的。参见第24.3.5节“获取分区信息”。

• The FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSON format displays the information in JSON format.

• “FORMAT”可选择输出格式。TRADITIONAL以表格格式显示输出。如果不存在格式选项，这是默认设置。JSON选项则以JSON格式显示信息。

With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order. To give a hint to the optimizer to use a join order corresponding to the order in which the tables are named in a SELECT statement, begin the statement with SELECT STRAIGHT_JOIN rather than just SELECT. (See Section 13.2.10, “SELECT Statement”.) However, STRAIGHT_JOIN may prevent indexes from being used because it disables semijoin transformations. See Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”在EXPLAIN的帮助下，您可以看到应该在哪里向表添加索引，以便通过使用索引查找行，使语句执行得更快。还可以使用EXPLAIN检查优化器是否以最佳顺序连接表。要提示优化器使用与SELECT语句中表的命名顺序相对应的联接顺序，请在语句开头使用SELECT STRIGHT_join，而不仅仅是SELECT。（请参阅第13.2.10节“SELECT语句”。）但是，直接连接可能会阻止使用索引，因为它禁用半连接转换。请参阅第8.2.2.1节，“使用半联接转换优化IN和EXISTS子查询谓词”。

The optimizer trace may sometimes provide information complementary to that of EXPLAIN. However, the optimizer trace format and content are subject to change between versions. For details, see MySQL Internals: Tracing the Optimizer.

优化器跟踪有时可以提供与EXPLAIN补充的信息。但是，优化器跟踪格式和内容在版本之间可能会发生更改。有关详细信息，请参见MySQL内部：跟踪优化器。

If you have a problem with indexes not being used when you believe that they should be, run ANALYZE TABLE to update table statistics, such as cardinality of keys, that can affect the choices the optimizer makes. See Section 13.7.3.1, “ANALYZE TABLE Statement”.

如果在您认为应该使用索引时但是索引没有被使用时，那么运行ANALYZE TABLE来更新表统计信息，例如键的基数，这可能会影响优化器所做的选择。见第13.7.3.1节“分析表报表”。

Note

EXPLAIN can also be used to obtain information about the columns in a table. EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name and SHOW COLUMNS FROM tbl_name. For more information, see Section 13.8.1, “DESCRIBE Statement”, and Section 13.7.7.5, “SHOW COLUMNS Statement”.

EXPLAIN还可用于获取有关表中列的信息。EXPLAINtbl_name 与 DESCRIBE tbl_name  以及SHOW COLUMNS FROM tbl_name 具有同样的意义。有关更多信息，请参见第13.8.1节“描述声明”和第13.7.7.5节“显示列声明”。