mysql阅读查询执行计划的简要说明

缺乏、安全感 2022-08-06 03:07 103阅读 0赞

学习数据库查询优化技术，第一步需要看明白查询执行计划，根据查询执行计划理解查询优化器的执行过程，体会优化技术的运用情况。所以，读懂查询执行计划是掌握查询优化技术的必要条件。

以下从MySQL查询执行计划的格式和关键字，介绍MySQL的查询执行计划，并结合实例，帮助读者理解查询执行计划。

示例  演示如何阅读MySQL的查询执行计划。先创建5张表，命令如下（各表的数据量为：t1表10000行数据，t2表100行数据，t3表100行数据，t4表7行数据，t5表10行数据）：

CREATE TABLE t1(id1 INT,a1 INT,b1INT,PRIMARY KEY(id1));

CREATE TABLE t2(id2 INT,a2 INT,b2 INT);

CREATE TABLE t3(id3 INT UNIQUE,a3 INT,b3INT);

CREATE TABLE t4(id4 INT,a4 INT,b4 INT);

CREATE TABLE t5(id5 INT UNIQUE,a5 INT,b5INT);

使用下面的存储过程插入表中的数据：

DELIMITER //

CREATE PROCEDURE proc01()

begin

declare var int;

set var=0;

while var<1000 do

insert into t1 values(var,var,var);

set var=var+1;

end while;

end;

DELIMITER ;

注：具体的测试环境搭建过程，我在另一个博客中有所描述，可以参考。

## MySQL的查询执行计划解释 ##

MySQL 5.6.10 版本使用Explain类表示查询执行计划。通过EXPLAIN这个SQL语句触发，显示查询执行计划。

### 1.     EXPLAIN功能 ###

语法格式：

EXPLAN \[explain\_type\] explainable\_stmt

可选项包括：

EXTENDED | PARTITIONS | FORMAT=format\_name

Format\_name:

TRADITIONAL |JSON

说明：

EXPLAIN命令：显示SQL语句的查询执行计划。

EXPLAIN EXTENDED命令：显示SQL语句的详细的查询执行计划；之后可以通过 show warnings命令查看详细的信息。

EXPLAIN PARTITIONS命令：显示SQL语句的带有分区表信息的查询执行计划。

EXPLAIN命令：输出格式有以下两种：

TRADITIONAL：传统类型；按行隔离，每行标识一个子操作。

JSON：JSON格式。

Explainable\_stmt：可被EXPLAIN执行的SQL语句，包括的类型有：select、insert、update、delete。

### 2.     查询执行计划 ###

理解MySQL查询执行计划，需要理解执行顺序和结点解析两个部分。

#### 1)   执行顺序 ####

执行5表连接的查询语句，演示MySQL的查询执行计划样式，语句如下：

EXPLAIN SELECT * FROM (t1 LEFT JOIN t2 ON true),(t3 FULL JOIN t4 ON true),t5 WHERE id1=id2 AND id2=id3 AND id3=id4 AND id4=id5;

执行SQL，结果如下所示：

EXPLAIN SELECT * FROM (t1 LEFT JOIN t2 ON true),(t3 FULL JOIN t4 ON true),t5 WHERE id1=id2 AND id2=id3 AND id3=id4 AND id4=id5;
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
    | id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra                                              |
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
    |  1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL        |  192 | Using where                                        |
    |  1 | SIMPLE      | FULL  | ref    | id3           | id3     | 5       | test.t2.id2 |    1 | NULL                                               |
    |  1 | SIMPLE      | t5    | ref    | id5           | id5     | 5       | test.t2.id2 |    1 | NULL                                               |
    |  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.id2 |    1 | NULL                                               |
    |  1 | SIMPLE      | t4    | ALL    | NULL          | NULL    | NULL    | NULL        |  400 | Using where; Using join buffer (Block Nested Loop) |
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
    5 rows in set (0.00 sec)

> EXPLAIN SELECT * FROM (t1 LEFT JOIN t2 ON true),(t3 FULL JOIN t4 ON true),t5 WHERE id1=id2 AND id2=id3 AND id3=id4 AND id4=id5;
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
    | id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra                                              |
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
    |  1 | SIMPLE      | t4    | ALL    | NULL          | NULL    | NULL    | NULL        |    7 | Using where                                        |
    |  1 | SIMPLE      | FULL  | ref    | id3           | id3     | 5       | test.t4.id4 |    1 | NULL                                               |
    |  1 | SIMPLE      | t5    | ref    | id5           | id5     | 5       | test.t4.id4 |    1 | NULL                                               |
    |  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t4.id4 |    1 | NULL                                               |
    |  1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL        |  100 | Using where; Using join buffer (Block Nested Loop) |
    +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
    5 rows in set (0.00 sec)

下面对该执行结果进行简要分析：

从第1行到第9行，表示了完成的查询计划；

第1行到第3行，表明查询计划的结构；id表示对象被操作的顺序；ID值大，先被执行；如果相同，执行顺序从上到下；

从第4行起，每一行为一个结点，表示本结点被操作对象的可用信息，如索引等；

表的连接次序为：t4,t5,t3,t1,t2.这和初始给定的连接次序不同，经过优化，外连接被消除；

T4表的元组数最少，安装mysql多表连接算法，表经过排序后，顺序为t4,t5,t2,t3,t1.

因为t5,t3,t1上有索引可以利用，所以t4上的一条元组确定后，则额可以利用索引之间定位t5,t3,t1表上的元组，所以第5,6,7行的key列又索引可用；ref列表明了这3个表都是引用了t4表的id4列。

t2表的数据相对较多，且又没有索引，最后被连接，连接使用了Extra列表明的嵌套循环连接算法，并且使用了连接缓存。

#### 2)   结点解析 ####

MySQL查询计划的输出列的含义如下：

**id**：每个被独立执行的操作的标识，表示对象被操作的顺序；ID值大，先被执行；如果相同，执行顺序从上到下；

**select\_type**:查询每个select子句中的类型；具体值如下表所示；

**table:**名字，被操作的对象名称，通常是表明，但又其他格式。

**partitions**:匹配的分区信息（对于非分区表值为NULL）

**type**:连接操作的类型；具体值见后面的表格；

**possible\_keys**:备选的索引（列出可能被使用到的索引）

**key**:经过优化器选定的索引；常用analyzetable命令，可以使优化器正确地选择索引。

**key\_len:**被优化器选定的索引键的长度，单位是字节。

**ref:**表示本行被操作的对象的参照对象（被参照的对象可能是一个常量用const表示，也可能是其他表的key指向的对象）。

**rows**:查询执行所扫描的元组个数（对于InnoDB，此值是估计值）。

**filtered**：安装条件表上数据被元组过滤的元组个数的百分比，rows\*filetered/100可以求出过滤后的元组数即实际的元组数。

**extra**:MySQL查询优化器执行查询过程中对查询计划的重要补充信息。

**MySQL查询执行计划select\_type列子句类型表**

<table> 
 <tbody> 
 <tr> 
 <td> Select_type </td> 
 <td> 说明 </td> 
 </tr> 
 <tr> 
 <td> SIMPLE </td> 
 <td> 简单的select语句（不包括UNION操作或子查询操作） </td> 
 </tr> 
 <tr> 
 <td> PRIMARY </td> 
 <td> 查询中最外层的select（如两表做UNION或存在有子查询，外层的表被称作primary，内层被作为UNION） </td> 
 </tr> 
 <tr> 
 <td> UNION </td> 
 <td> UNION操作中，查询中处于内层的select（内存的select语句与外层的select语句没有依赖关系） </td> 
 </tr> 
 <tr> 
 <td> DEPENDENT UNION </td> 
 <td> UNION操作中，查询中处于内层的select（内存的select语句与外层的select语句有依赖关系） </td> 
 </tr> 
 <tr> 
 <td> UNION RESULT </td> 
 <td> UNION操作的结果，ID通常为NULL </td> 
 </tr> 
 <tr> 
 <td> SUBQUERY </td> 
 <td> 子查询中的首个select（如果有多个子查询存在） </td> 
 </tr> 
 <tr> 
 <td> DEPENDENT SUBQUERY </td> 
 <td> 子查询中的首个select，但依赖于外层的表（如果有多个子查询存在） </td> 
 </tr> 
 <tr> 
 <td> DERIVED </td> 
 <td> 被驱动的select子查询（子查询位于from子句） </td> 
 </tr> 
 <tr> 
 <td> MATERIALIZED </td> 
 <td> 被物化的子查询 </td> 
 </tr> 
 <tr> 
 <td> UNCACHEABLE SUBQUERY </td> 
 <td> 对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作） </td> 
 </tr> 
 <tr> 
 <td> UNCACHEABLE UNION </td> 
 <td> UNION操作中，内层的不可物化的子查询（类似于UNCACHEABLE SUBQUERY） </td> 
 </tr> 
 </tbody> 
</table>

**连接操作数据访问方式表**

<table> 
 <tbody> 
 <tr> 
 <td> 代码表示方式 </td> 
 <td> 说明 </td> 
 </tr> 
 <tr> 
 <td> JT_SYSSTEM </td> 
 <td> 常量表情况一，表上只有一条元组匹配 </td> 
 </tr> 
 <tr> 
 <td> JT_COUNST </td> 
 <td> 常量表情况二，where条件筛选后表上至多有一条元组匹配，如：where table.pk=2（pk列是主键列，值为2的要么有一条，要么没有） </td> 
 </tr> 
 <tr> 
 <td> JT_EQ_REF </td> 
 <td> 参与连接运算的表，是内表（在嗲吗实现的算法中，两表连接时作为循环中的内循环遍历对象，这样的表为内表）。基于索引（连接字段上存在唯一索引或主机索引，且操作符必须是“=”谓词，索引的值不能为NULL）做扫描，使得对外表的一条元组，内表只有唯一一条元组与之对应 </td> 
 </tr> 
 <tr> 
 <td> JT_REF </td> 
 <td> 可用于单表索引或连接。参与连接运算的表，是内表。基于索引（连接字段上的索引是非唯一索引，操作符必须是“=”谓词，连接字段值不可为NULL）做扫描，是的对外表的一条元组，内表可有若干条元组与之对应 </td> 
 </tr> 
 <tr> 
 <td> JT_REF_OR_NULL </td> 
 <td> 类似ref，只是搜索条件敖阔：连接字段的值可为NULL的情况，如：where col=… or col IS NULL </td> 
 </tr> 
 <tr> 
 <td> JT_RANGE </td> 
 <td> 范围扫描。基于索引做范围扫描，为诸如 BETWEEN，IN，&gt;=，like类操作提供支持 </td> 
 </tr> 
 <tr> 
 <td> JT_INDEX_SCAN </td> 
 <td> 索引做扫描。是基于索引在索引的叶子结点上找满足条件的数据（不需要访问数据文件） </td> 
 </tr> 
 <tr> 
 <td> JT_ALL </td> 
 <td> 全表扫描或范围扫描；不使用索引，顺序扫描，直接读取表上的数据（访问数据文件） </td> 
 </tr> 
 <tr> 
 <td> JT_UNIQUE_SUBQUERY </td> 
 <td> 在子查询中，基于唯一索引进行扫描。类似于eq_ref </td> 
 </tr> 
 <tr> 
 <td> JT_INDEX_SUBQUERY </td> 
 <td> 在子查询中，基于除唯一索引之外的索引进行扫描 </td> 
 </tr> 
 <tr> 
 <td> JT_INDEX_MERGE </td> 
 <td> 多重范围扫描，两表连接的每个表的连接字段上均有索引存在且索引有序，结果合并在一起。适用于做集合的并、交操作 </td> 
 </tr> 
 <tr> 
 <td> JT_FT </td> 
 <td> FT，FullText，全文检索 </td> 
 </tr> 
 </tbody> 
</table>

**Extra信息解释表**

<table> 
 <tbody> 
 <tr> 
 <td> Extra值 </td> 
 <td> 说明 </td> 
 </tr> 
 <tr> 
 <td> Child of ‘table’ pushed join@l </td> 
 <td> MySQL cluster使用的参数。表示在连接操作中某个表被下推到NDB引擎的某个结点上执行 </td> 
 </tr> 
 <tr> 
 <td> Const row not found </td> 
 <td> 在连接中，查询一个没有元组的空表 </td> 
 </tr> 
 <tr> 
 <td> Deleting all rows </td> 
 <td> 对于删除操作，某些存储引擎支持快速删除数据 </td> 
 </tr> 
 <tr> 
 <td> Distinct </td> 
 <td> 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作 </td> 
 </tr> 
 <tr> 
 <td> FirstMatch(tb1_name) </td> 
 <td> 半连接算法中对表采取了受此匹配的连接策略 </td> 
 </tr> 
 <tr> 
 <td> Full scan on NULL key </td> 
 <td> 子查询中，优化器不能使用基于索引的访问方式时（因有空值存在）所采取的一种数据访问策略 </td> 
 </tr> 
 <tr> 
 <td> Impossible HAVING </td> 
 <td> Having的子句值总是FALSE（不能获取任何元组） </td> 
 </tr> 
 <tr> 
 <td> Impossible WHERE </td> 
 <td> Where子句的值总是FALSE（不能获取任何元组） </td> 
 </tr> 
 <tr> 
 <td> Materialize,Scan </td> 
 <td> MySQL 5.6.7版本之前，表示使用单一被物化的临时表；之后的版本，物化是通过select_type列表达的 </td> 
 </tr> 
 </tbody> 
</table>