MySQL - EXPLAIN详解

---

---

MySQL EXPLAIN 语句

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

当查询执行时间过长时，可以使用 EXPLAIN 语句。它显示较慢查询的执行计划，允许您在需要的地方应用索引以加快执行过程。

请注意，也不应在查询中使用过多的索引；因为它可能会使查询速度变慢。

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

语法

以下是 EXPLAIN 语句的语法：

EXPLAIN tbl_name [col_name | wild]

示例

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

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

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

EXPLAIN CUSTOMERS;

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

字段	类型	空值	键	默认值	额外信息
ID	int	否	PRI	NULL
姓名 (NAME)	varchar(20)	否		NULL
地址 (ADDRESS)	char(25)	是		NULL

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

EXPLAIN CUSTOMERS NAME;

输出

以下是获得的输出：

字段	类型	空值	键	默认值	额外信息
姓名 (NAME)	varchar(20)	否		NULL

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

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

获得的表如下：

id	select_type	table	partitions	type	possible_keys
1	SIMPLE	CUSTOMERS	NULL	ALL	NULL

请注意，以上输出中并未显示表中的所有列；还存在其他列。

EXPLAIN 和 ANALYZE

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

• 估计执行成本。
• 估计返回的行数。
• 返回第一行的时间。
• 返回所有行的时间（实际成本），以毫秒为单位。
• 迭代器返回的行数。
• 循环次数。

示例

以下是带有 ANALYZE 的 EXPLAIN 语句的示例：

EXPLAIN ANALYZE SELECT * FROM CUSTOMERS;

它显示的输出包括更多与时间和成本相关的详细信息，如下所示：

EXPLAIN
–> 对 CUSTOMERS 进行表扫描 (成本=0.35 行=1) (实际时间=0.070..0.070 行=0 循环=1)

示例

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

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 的表，其中包含所做订单的详细信息以及订单日期：

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

现在，我们将一些数据插入到 ORDERS 表中，如下所示：

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

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

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

我们得到以下输出：

ID	姓名 (NAME)	地址 (ADDRESS)	OID	日期 (DATE)	CUST_ID	金额 (AMOUNT)
3	Kaushik	Kota	102	2009-10-08 00:00:00	3	3000.00
3	Kaushik	Kota	100	2009-10-08 00:00:00	3	1500.00
2	Khilan	Delhi	101	2009-11-20 00:00:00	2	1560.00
4	Chaitali	Mumbai	103	2008-05-20 00:00:00	4	2060.00

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

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

产生的结果如下：

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

explain_type 选项

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

示例

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

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

以下是获得的输出：

-> 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 格式检索信息：

EXPLAIN FORMAT = JSON SELECT * FROM CUSTOMERS;

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

{
  "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"
      ]
    }
  }
}