SQL中的执行计划explain关键字的作用

最新推荐文章于 2024-03-01 09:34:09 发布

置顶在代码的路上奔跑

最新推荐文章于 2024-03-01 09:34:09 发布

阅读量782

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_38427272/article/details/105751542

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

explain关键字
使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。通过explain我们可以获得以下信息

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

使用方法：explain + sql语句。包含的字段如下
在这里插入图片描述其中个字段的含义具体说明一下：
1、id字段
（1） id相同执行顺序由上至下；
（2） id不同，如果是子查询id的序号会递增，id值越大优先级越高，越先被执行
（3）id相同不同，id相同可以被认为是一组，同一组中由上往下执行，所有组中id越大的越先被执行。
2、select_type字段
（1）SIMPLE
简单的SELECT语句（不包括UNION操作或子查询操作）
（2）PRIMARY、UNION
查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）

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

（3）DEPENDENT UNION、UNIOIN RESULT
DEPENDENT UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）

UNION RESULT：UNION操作的结果，id值通常为NULL

（4）SUBQUERY、DEPENDENT SUBQUERY
SUBQUERY：子查询中首个SELECT（如果有多个子查询存在）：

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

重点解释子查询的查询方式依赖于外面的查询结果.用这个例子就是,先进行子查询外部的查询,得到一个结果集,.然后这个结果的每一行在跟select子查询的结果集进行匹配,也就是说,外部结果集的每一行都要关联内部结果集一次

（5）DERIVED、MATERIALIZED
DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）
MATERIALIZED：被物化的子查询
（6）UNCACHEABLE SUBQUERY、UNCACHEABLE UNION

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

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

注意：
特别关注 DEPENDENT SUBQUERY

<1> 、会严重消耗性能

<2> 、不会进行子查询，会先进行外部查询,生成结果集,再在内部进行关联查询

<3>、子查询的执行效率受制于外层查询的记录数

<4>、可以尝试改成join查询

3、table字段
tale用于显示当前查询语句表名
4、type
type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。

mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。

（1）all
这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
为什么这么说呢？因为all是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。
以employee表为例，下面一种情形便是all类型的查找：

mysql> explain select * from employee where `no` = '20150001';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

这是因为no列既不是主键也不是索引，因此只能采用全表扫描来查找目标no。

index
index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。
如果一定要比效率，只需要获取这个表的数据并且排序便可以看出来谁比谁效率高了：

mysql> explain select * from employee order by `no` ;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
mysql> explain select * from employee order by rec_id ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | employee | index | NULL          | PRIMARY | 4       | NULL |    5 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

上面可以看出，根据no列排序的连接类型是all型的，但是注意extra列是用到了排序（Using filesort），而根据rec_id列排序的连接类型是index，而且得到的结果自然是有序的，不许额外的排序。可能正是因为这个缘故，index的效率比all高，但注意这需要相同的条件才成立（既需要排序）。

如果连接类型为type，而且extra列中的值为‘Using index’，那么称这种情况为索引覆盖；
索引覆盖意味着什么呢？想象这样一种场景，如果说一本新华字典是一张表，当然前面的索引部分（假设按照部首的索引）是这张表的索引，那么索引覆盖就相当于根据部首索引获取第一个字到最后一个字（新华字典的所有字）。我们获得了字典中所有的字，然而我们并没有查一次表，因为我们想要的都早索引中，即索引覆盖。

range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。

ref
出现该连接类型的条件是：查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）

alter table employee add key I_EMPLOYEE_NAME(name);

接下来，在employee表中根据name查找数据的时候，mysql优化器便选择了ref的连接类型。

ref_eq
ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
在单个表中，曾尝试了很多方法想出现ref_eq的连接类型，然而很多时候出现的都是const，因此不得不随手连接了一张表得到了想要的连接类型，该表的建表代买为。（博主比较懒，连接了两个没有关系的表，o(╯□╰)o）

employee表中有五条数据，score表中有对应的五条数据，其中employee的rec_id 和score的stu_id 是一一对应的。

上面就可以看到score表是全表扫描的类型，rows=5代表外层表循环了五次（因为有五条数据），但是employee表的rows怎么是1，怎么可能？刚开始也是很疑惑，这与mysql的查询原理息息相关，rows实际反映的是查询的内循环数，针对外层的每一条数据匹配，employee的确一枪就可以命中，因此rows为1。

const

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

总结

explain 就像一面镜子，有事没事写完sql记得explain一下。同时，在写文章也发现，有很多东西和细节，想要明白清楚，也是没有那么简单的，需要对操作系统以及数据库的底层查询和运行原理要有一个清楚的理解。同时type的几种类型几乎都是基于索引之上的，因此需要对索引有个深入的了解，而且explain的结果可以指导我们什么时候加索引，什么时候不加索引，从而让我们更好的使用索引。

在代码的路上奔跑

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
SQL中的执行计划explain关键字的作用

explain关键字使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。通过explain我们可以获得以下信息（1）表的读取顺序（2）数据读取操作的操作类型（3）哪些索引可以使用（4）哪些索引被实际使用（5）表之间的引用（6）每张表有多少行被优化器查询使用方法：explain + sql语句...
复制链接

扫一扫