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

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

         以下从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列子句类型表

Select_type

说明

SIMPLE

简单的select语句（不包括UNION操作或子查询操作）

PRIMARY

查询中最外层的select（如两表做UNION或存在有子查询，外层的表被称作primary，内层被作为UNION）

UNION

UNION操作中，查询中处于内层的select（内存的select语句与外层的select语句没有依赖关系）

DEPENDENT UNION

UNION操作中，查询中处于内层的select（内存的select语句与外层的select语句有依赖关系）

UNION RESULT

UNION操作的结果，ID通常为NULL

SUBQUERY

子查询中的首个select（如果有多个子查询存在）

DEPENDENT SUBQUERY

子查询中的首个select，但依赖于外层的表（如果有多个子查询存在）

DERIVED

被驱动的select子查询（子查询位于from子句）

MATERIALIZED

被物化的子查询

UNCACHEABLE SUBQUERY

对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）

UNCACHEABLE UNION

UNION操作中，内层的不可物化的子查询（类似于UNCACHEABLE SUBQUERY）

 

连接操作数据访问方式表

代码表示方式

说明

JT_SYSSTEM

常量表情况一，表上只有一条元组匹配

JT_COUNST

常量表情况二，where条件筛选后表上至多有一条元组匹配，如：where table.pk=2（pk列是主键列，值为2的要么有一条，要么没有）

JT_EQ_REF

参与连接运算的表，是内表（在嗲吗实现的算法中，两表连接时作为循环中的内循环遍历对象，这样的表为内表）。基于索引（连接字段上存在唯一索引或主机索引，且操作符必须是“=”谓词，索引的值不能为NULL）做扫描，使得对外表的一条元组，内表只有唯一一条元组与之对应

JT_REF

可用于单表索引或连接。参与连接运算的表，是内表。基于索引（连接字段上的索引是非唯一索引，操作符必须是“=”谓词，连接字段值不可为NULL）做扫描，是的对外表的一条元组，内表可有若干条元组与之对应

JT_REF_OR_NULL

类似ref，只是搜索条件敖阔：连接字段的值可为NULL的情况，如：where col=… or col IS NULL

JT_RANGE

范围扫描。基于索引做范围扫描，为诸如 BETWEEN，IN，>=，like类操作提供支持

JT_INDEX_SCAN

索引做扫描。是基于索引在索引的叶子结点上找满足条件的数据（不需要访问数据文件）

JT_ALL

全表扫描或范围扫描；不使用索引，顺序扫描，直接读取表上的数据（访问数据文件）

JT_UNIQUE_SUBQUERY

在子查询中，基于唯一索引进行扫描。类似于eq_ref

JT_INDEX_SUBQUERY

在子查询中，基于除唯一索引之外的索引进行扫描

JT_INDEX_MERGE

多重范围扫描，两表连接的每个表的连接字段上均有索引存在且索引有序，结果合并在一起。适用于做集合的并、交操作

JT_FT

FT，FullText，全文检索

 

 

Extra信息解释表

Extra值

说明

Child of ‘table’ pushed join@l

MySQL cluster使用的参数。表示在连接操作中某个表被下推到NDB引擎的某个结点上执行

Const row not found

在连接中，查询一个没有元组的空表

Deleting all rows

对于删除操作，某些存储引擎支持快速删除数据

Distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

FirstMatch(tb1_name)

半连接算法中对表采取了受此匹配的连接策略

Full scan on NULL key

子查询中，优化器不能使用基于索引的访问方式时（因有空值存在）所采取的一种数据访问策略

Impossible HAVING

Having的子句值总是FALSE（不能获取任何元组）

Impossible WHERE

Where子句的值总是FALSE（不能获取任何元组）

Materialize,Scan

MySQL 5.6.7版本之前，表示使用单一被物化的临时表；之后的版本，物化是通过select_type列表达的