Mysql 简明教程

MySQL - EXPLAIN

The MySQL EXPLAIN Statement

MySQL EXPLAIN 语句用于提供查询的执行计划。此语句的工作方式类似于 DESCRIBE 查询；DESCRIBE 查询提供表的结构计划，而 EXPLAIN 语句描述查询如何执行。

The MySQL EXPLAIN statement is used to provide the execution plan of a query. This statement works similar to the DESCRIBE query; while the DESCRIBE query provides the structure plan of a table, the EXPLAIN statement describes how a query is being executed.

您可以在查询执行时间过长的情况下使用 EXPLAIN 语句。它显示此类较慢查询的执行计划，允许您在任何必要的地方应用索引以加快执行过程。

You can use the EXPLAIN statement in situations where a query is taking too much time in order to be executed. It displays the execution plan of such slower queries, allowing you to apply indexes wherever necessary to speed up the execution process.

此语句适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。

This statement works with the SELECT, DELETE, INSERT, REPLACE and UPDATE statements.

Syntax

以下是 EXPLAIN 语句的语法：

Following is the syntax of the EXPLAIN statement −

EXPLAIN tbl_name [col_name | wild]

Example

假设我们在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表，如下所示：

Assume we have created a table named CUSTOMERS in MySQL database as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   ADDRESS CHAR (25),
   PRIMARY KEY (ID)
);

您可以使用 EXPLAIN 语句查看此表的执行计划，如下所示：

You can use the EXPLAIN statement to view the execution plan of this table as shown below −

EXPLAIN CUSTOMERS;

输出将提供有关表结构的信息，包括列及其属性，如下所示：

The output will provide information about the table’s structure, including columns and their attributes as follows −

您还可以使用 EXPLAIN 语句获取有关特定列的详细信息，如下所示：

You can also use the EXPLAIN statement to obtain details about a specific column as shown below −

EXPLAIN CUSTOMERS NAME;

Output

以下是所获得的输出 −

Following is the output obtained −

EXPLAIN 最常与 SELECT 查询一起使用，以分析其执行计划。考虑以下查询：

EXPLAIN is most commonly used with SELECT queries to analyze their execution plans. Consider the following query −

EXPLAIN SELECT * FROM CUSTOMERS WHERE NAME LIKE 'k%';

获得的表如下 −

The table obtained is as follows −

请注意，表中并非所有列都显示在上面的输出中；还有其他列。

Note that not all columns in the table have been displayed in the output above; there are additional columns present.

EXPLAIN and ANALYZE

如果我们在 EXPLAIN 语句中使用 ANALYZE，它会提供其他信息，例如执行时间和基于迭代器的信息，例如：

If we use the EXPLAIN statement with ANALYZE, it gives additional information such as timing of the execution and iterator-based information like −

  1. Estimated execution cost.

  2. Estimated number of returned rows.

  3. Time to return first row.

  4. Time to return all rows (actual cost), in milliseconds.

  5. Number of rows returned by the iterator.

  6. Number of loops.

Example

以下是 ANALYZE − 的 EXPLAIN 语句示例

Following is an example of the EXPLAIN statement with ANALYZE −

EXPLAIN ANALYZE SELECT * FROM CUSTOMERS;

它显示的输出包含更详细的时序和开销相关信息，如下所示 −

It displays the output that includes more timing and cost-related details as shown below −

Example

首先，让我们使用 INSERT 语句将值插入上面创建的 CUSTOMERS 表中 −

First, let us insert values into the CUSTOMERS table created above using the INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 'Ahmedabad' ),
(2, 'Khilan', 'Delhi' ),
(3, 'kaushik', 'Kota'),
(4, 'Chaitali', 'Mumbai' ),
(5, 'Hardik', 'Bhopal' ),
(6, 'Komal', 'MP' ),
(7, 'Muffy', 'Indore' );

让我们创建另一个表 ORDERS，其中包含订单的详细信息以及下单日期 −

Let us create another table ORDERS, containing the details of orders made and the date they are made on −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUST_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

现在，我们向 ORDERS 表中插入一些数据，如下所示 −

Now, we are inserting some data into the ORDERS table as follows −

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

以下查询从上面创建的表中删除记录 −

Following query deletes records from the above created tables −

SELECT * FROM CUSTOMERS
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID;

我们获得以下输出 −

We get the following output −

要获取有关此查询执行的信息，可以使用 EXPLAIN ANALYZE 语句，如下所示−

To obtain information about this query’s execution, you can use the EXPLAIN ANALYZE statement as follows−

EXPLAIN ANALYZE SELECT * FROM CUSTOMERS
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID\G;

产生的结果如下 −

The result produced is as follows −

*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=2.05 rows=4) (actual time=0.117..0.145 rows=4 loops=1)
    -> Table scan on ORDERS  (cost=0.65 rows=4) (actual time=0.078..0.095 rows=4 loops=1)
    -> Single-row index lookup on CUSTOMERS using PRIMARY (ID=orders.CUST_ID)  (cost=0.28 rows=1) (actual time=0.010..0.010 rows=1 loops=4)

1 row in set (0.00 sec)

The explain_type Option

您还可以使用 explain_type 选项指定要检索信息的格式。它允许您在 TRADITIONAL、JSON 和 TREE 格式之间进行选择。这些不同的格式提供了相同的信息，但针对您的分析以更有条理的方式提供了这些信息。

You can also specify the format in which you want to retrieve the information using the explain_type option. It allows you to choose between TRADITIONAL, JSON, and TREE formats. These different formats provide the same information but in a more structured manner for your analysis.

Example

在这里，我们使用 explain_type 选项以 TREE 格式检索信息 −

In here, we are retrieving the information in TREE format using the explain_type option −

EXPLAIN ANALYZE FORMAT = TREE SELECT * FROM CUSTOMERS
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID;

以下是所获得的输出 −

Following is the output obtained −

-> Nested loop inner join  (cost=2.05 rows=4) (actual time=0.111..0.136 rows=4 loops=1)
    -> Table scan on ORDERS  (cost=0.65 rows=4) (actual time=0.073..0.089 rows=4 loops=1)
    -> Single-row index lookup on CUSTOMERS using PRIMARY (ID=orders.CUST_ID)

现在，我们以 JSON 格式检索信息 −

Now, we are retrieving information in JSON format −

EXPLAIN FORMAT = JSON SELECT * FROM CUSTOMERS;

执行上面的代码后，我们得到以下输出： -

After executing the above code, we get the following output −

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.95"
    },
    "table": {
      "table_name": "CUSTOMERS",
      "access_type": "ALL",
      "rows_examined_per_scan": 7,
      "rows_produced_per_join": 7,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.70",
        "prefix_cost": "0.95",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "ID",
        "NAME",
        "ADDRESS"
      ]
    }
  }
}