分析查询语句：EXPLAIN

1. 概述

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户
端请求的Qury提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。
这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来
具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

1.1 能做什么？

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

1.2 官网介绍

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

1.3 版本情况

MySQL 5.6.3以前只能 EXPLAIN SELECT ；
MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE， DELETE 在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令想要显示 filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和 filtered中的信息

2. 基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
#或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

explain select * from table1;

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以
使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及
UPDATE语句等都可以加上EXPLAIN,用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣。
注意：执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。

EXPLAIN 语句输出的各个列的作用如下：

3. 数据准备

CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
 ) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
   id INT AUTO_INCREMENT,
   key1 VARCHAR(100),
   key2 INT,
   key3 VARCHAR(100),
   key_part1 VARCHAR(100),
   key_part2 VARCHAR(100),
   key_part3 VARCHAR(100),
   common_field VARCHAR(100),
   PRIMARY KEY (id),
   INDEX idx_key1 (key1),
   UNIQUE INDEX idx_key2 (key2),
   INDEX idx_key3 (key3),
   INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

分别向两个表中插入1万条数据

4. EXPLAIN各列作用

4.1 table

站内文章链接： EXPLAIN：select_table字段详解

4.2 id

站内文章链接： EXPLAIN：id字段详解

4.3 select_type

站内文章链接： EXPLAIN：select_type字段详解

4.4 partitions

代表分区表中的命中情况，非分区表，该项为NULL。一般情况下我们的额查询语句的执行计划的 partitions 列的值为 NULL 。
https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

4.5 type（*）

站内文章链接： EXPLAIN：type字段详解

4.6 possible_keys和key

在EXPLAIN语句输出的执行计划中， possible_keys 列表示在某个查询语句中，对某个列执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。 key 列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。比方说下面这个查询：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

上述执行计划的 possible_keys 列的值是 idx_key1, idx_key3 ，表示该查询可能使用到 idx_key3 两个索引，然后最后决定采用 key 列的值是 idx_key1, idx_key3 ，表示经过查询优化器计算使用不同索引的成本后， idx_key3 。

4.7 key_len（*）

站内文章链接： EXPLAIN：key_len字段详解

4.8 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq-ref、ref、ref_or-nul1、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。

4.9 rows（越小越好）（*）

预估的需要读取的记录条数，值越小越好。

4.10 filtered （越高越好）

某个表经过搜索条件过滤后剩余记录条数的百分比
如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

对于单表查询来说，这个filtered的值没有什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered值，它决定了被驱动表要执行的次数 (即: rows * filtered)

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE 
s1.common_field = 'a';

从执行计划中可以看出来，查询优化器打算把s1作为驱动表，s2当做被驱动表。我们可以看到驱动表 s1 表的执行计划的 rows 列为 9688 ，filtered列为 10.00 ，这意味着驱动表扇出值 9688 *10.00% = 968.8 ，这说明还要对被驱动表执行大约968 次查询。

4.11 Extra（*）

站内文章链接： EXPLAIN：Extra字段详解

5. EXPLAINI的进一步使用

5.1 EXPLAIN四种输出格式

这里谈谈EXPLAINE的输出格式。EXPLAINT可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。

1. 传统格式

传统格式简单明了，输出是一个表格形式，概要说明查询计划。

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

2. JSON格式

第1种格式中介绍的 EXPLAIN 语句输出中缺少了一个衡量执行好坏的重要属性 —— 成本。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。

EXPLAIN FORMA T= JSON SELECT ....

3. TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = 
s2.key2 WHERE s1.common_field = 'a'\G

4. 可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。

6. SHOW WARNINGS的使用

在我们使用 EXPLAIN 语句查看了某个查询的执行计划后，紧接着还可以使用与这个查询的执行计划有关的一些扩展信息，比如这样：

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = 
s2.key1 WHERE s2.common_field IS NOT NULL;

SHOW WARNINGS\G

可以看到 SHOW WARNINGS 展示出来的信息有三个字段，分别是 Level、Code、Message 。我们最常见的就是Code为1003的信息，当Code值为1003时， Message 字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。比如我们上边的查询本来是一个左(外)连接查询，但是有一个 s2.common_field IS NOT NULL的条件，这就会导致查询优化器把左(外)连接查询优化为内连接查询，从SHOW WARNINGS的 Message 字段也可以看出来，原本的LEFE JOIN已经变成了JOIN。

我们说 Message 字段展示的信息类似于查询优化器将我们的查询语句句，并不是等价于，也就是说重写后的语 Message 字段展示的信息并不是标准的查询语句，在很多情况下并不能直接拿到黑框框中运行，它只能作为帮助我们理解MySQL将如何执行查询语句的一个参考依据而已。

7. 分析优化器执行计划：trace

OPTIMIZER_TRACE是MySQL5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法，各种开销计算、各种转换等)，并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
此功能默认关闭。开启trace,并设置格式为JSoN,同时设置trace最大能够使用的内存大小，避免解析过程中因
为默认内存过小而不能够完整展示。
开启：

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

开启后，可分析如下语句：
SELECT
INSERT
REPLACE
UPDATE
DELETE
EXPLAIN
SET
DECLARE
CASE IF
RETURN
CALL
测试：执行如下SQL语句

select * from information_schema.optimizer_trace\G

当前页面是本站的「Baidu MIP」版。发表评论请点击：完整版 »

MySQL性能分析工具的使用:EXPLAIN的概述及各列的作用

分析查询语句：EXPLAIN

1. 概述

1.1 能做什么？

1.2 官网介绍

1.3 版本情况

2. 基本语法

3. 数据准备

4. EXPLAIN各列作用

4.1 table

4.2 id

4.3 select_type

4.4 partitions

4.5 type（*）

4.6 possible_keys和key

4.7 key_len（*）

4.8 ref

4.9 rows（越小越好）（*）

4.10 filtered （越高越好）

4.11 Extra（*）

5. EXPLAINI的进一步使用

5.1 EXPLAIN四种输出格式

1. 传统格式

2. JSON格式

3. TREE格式

4. 可视化输出

6. SHOW WARNINGS的使用

7. 分析优化器执行计划：trace