MySQL 5.7-8.8.1 Optimizing Queries with EXPLAIN

Depending on the details of your tables, columns, indexes, and the conditions in your WHERE clause, the MySQL optimizer considers many techniques to efficiently perform the lookups involved in an SQL query.

根据表、列、索引和WHERE子句中的条件的详细信息，MySQL优化器考虑许多技术来有效地执行SQL查询中涉及的查找。

A query on a huge table can be performed without reading all the rows; a join involving several tables can be performed without comparing every combination of rows.

在大型表上执行查询时，不需要读取所有的行;涉及多个表的连接可以在不比较每个行组合的情况下执行。

The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan”, also known as the EXPLAIN plan.

优化器选择用于执行最有效查询的操作集称为“查询执行计划”，也称为EXPLAIN计划。

Your goals are to recognize the aspects of the EXPLAIN plan that indicate a query is optimized well, and to learn the SQL syntax and indexing techniques to improve the plan if you see some inefficient operations.

您的目标是识别EXPLAIN计划中表明查询优化良好的方面，并学习SQL语法和索引技术，以便在看到一些低效操作时改进计划。

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获取执行计划信息的信息， see Section 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 connection_id而不是一个可解释语句一起使用时，它将显示在指定连接中执行的语句的执行计划。

• 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显示这些信息。

• EXPLAIN is useful for examining queries involving partitioned tables. See EXPLAIN.

• EXPLAIN对于检查涉及分区表的查询很有用。

• 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以表格形式显示输出。如果没有FORMAT选项，这是默认设置。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.

在EXPLAIN的帮助下，您可以看到应该在哪些地方向表添加索引，以便通过使用索引查找行来更快地执行语句。

You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order.

还可以使用EXPLAIN检查优化器是否以最佳顺序连接表。

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.

要提示优化器使用与表在SELECT语句中命名的顺序相对应的连接顺序，请使用SELECT STRAIGHT_JOIN开始语句，而不仅仅是SELECT。

(See Section 13.2.9, “SELECT Statement”.) However, STRAIGHT_JOIN may prevent indexes from being used because it disables semijoin transformations.

(参见13.2.9节“SELECT语句”)但是，由于它禁用了半连接转换，因此可能会阻止使用索引。

See Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”.

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.

优化器跟踪有时可能提供与EXPLAIN跟踪相补充的信息。然而，优化器跟踪格式和内容在不同版本之间可能会发生变化。

For details, see MySQL Internals: Tracing the Optimizer.

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.

如果在您认为应该使用索引的时候没有使用索引，那么可以运行ANALYZE TABLE来更新表统计信息，比如键的基数，这可能会影响优化器做出的选择。

See Section 13.7.2.1, “ANALYZE TABLE Statement”.

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.

还可以使用EXPLAIN获取有关表中列的信息。EXPLAIN tbl_name与DESCRIBE tbl_name和SHOW COLUMNS FROM tbl_name同义。 For more information, see Section 13.8.1, “DESCRIBE Statement”, and Section 13.7.5.5, “SHOW COLUMNS Statement”.