必威体育Betway必威体育官网
当前位置:首页 > IT技术

MySQL系列-优化之explain执行计划详解

时间:2019-09-30 06:44:12来源:IT技术作者:seo实验室小编阅读:65次「手机版」
 

explain

1.id介绍

这个id不是主键的意思,他是用来标识select查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序。

会出现以下情况:

id相同:按从上到下顺序执行

id不同:id值越大,优先级越高,越先被执行

id相同不同的同时存在:优先执行id值大的,如果id值相同,则按从上到下的顺序执行

id为null表示是用来合并结果集的,在sql使用union关键字合并结果集就会出现他。

2.select_type介绍

顾名思义,表示查询的类型

类型 说明
simple 简单子查询,不包含子查询和union
primary 包含union或者子查询,最外层的部分标记为primary
subquery 一般子查询中的子查询被标记为subquery,也就是位于select列表中的查询
derived 派生表——该临时表是从子查询派生出来的,位于form中的子查询
union 位于union中第二个及其以后的子查询被标记为union,第一个就被标记为primary如果是union位于from中则标记为derived
union result 用来从匿名临时表里检索结果的select被标记为union result
dependent union 顾名思义,首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询
subquery 子查询中第一个SELECT语句
dependent subquery 和DEPENDENT UNION相对UNION一样

3.table介绍

对应行正在访问哪一个表,表名或者别名。也有可能是临时表等等,或者是union合并结果集。

  • 表名是derivedN的形式,表示使用了id为N的查询产生的衍生表
  • 当有union result的时候,表名是union n1,n2等的形式,n1,n2表示参与union的id

注意:mysql对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。

4.举例说明

说明:可以看到,先查询b2表再查询b1表,而且都是简单查询。

说明:b2表对应的id为2,所以先查询b2表,查询类型是依赖子查询,然后在查询b1表,查询类型是主查询。

说明:先查询b2表,在查询b3表,然后查询b1表,最后合并结果集。<union1,2>指的是将id为1和id为2查询的结果合并。

说明:先查询b1表得出一个临时表,然后在查询这个临时表,这个derived2表示使用了id为2的查询出来的结果集作为临时表,最后才是b2表。

5.type介绍

type意味着类型,这里的type官方全称是“join type”,意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型,称之为访问类型更加容易理解。访问类型表示我们是以何种方式去访问我们的数据的,当然很容易想的的是全表扫描了,直接暴力的遍历一张表取寻找需要的数据,效率非常之低下。访问类型的种类有很多,而且各个版本的MySQL可能会不一样,但是常见的就那么几种,按照效率最差到最好依次排列依次是:

all < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < ref < eq_ref < const<system

all : 这个就是全表扫描了,一般这样的出现这样的SQL而且数据量比较大的话那么就需要进行优化了,要么是这条SQL没有用上索引,要么是没有建立合适的索引。

index : 全索引扫描,这个比all效率要好一点,主要有几种情况,一是当前的查询是覆盖索引的,即我们需要的数据在索引中就可以获取(Extra中有Using Index,Extra也是explain的一个字段)(关于覆盖索引:MySQL系列-优化之覆盖索引),或者是使用了索引进行排序,这样就避免数据的重排序(extra中无 Using Index)。如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思。

range : 这个是index了范围限制,例如 >100、<1000之类的查询条件,这样避免的index的全索引扫描,当然限制的范围越小 效率就越高。

index_subquery : 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引

unique_subquery : 在某些 IN 查询中使用此种类型,而不是常规的 ref

index_merge : 说明索引合并优化被使用了

ref_or_null : 如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。

ref : 使用了非唯一性索引进行数据的查找,例如:我们对用户表的用户名这一列建立了非唯一索引,因为用户名可以重复,当我们查找用户的时候select * from user where username=“xxx”的时候就出现了ref,使用非唯一索引查找数据。

eq_ref : 这个就很好理解了,使用的唯一性索引进行数据查找,例如主键索引之类的。

const : 通常情况下,将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器。这个比eq_ref效率高一点。

system : 表只有一行。不过这种情况下就没意义了。

NULL : MySQL不用访问表或者索引就直接能到结果。

6.举例说明

可以看到,有三个字段,其中id是主键索引,n1是非唯一索引,n2是唯一索引。

select * from tb1; 简单粗暴的全表扫描

查询数据n1的时候使用了覆盖索引。

使用n1列的索引进行排序,所以extra中没有出现using index,因为当前查询不是索引覆盖的。这里我强制使用了idx_n1,MySQL也遵循了我的建议(如果强制使用的索引是用不上的话,MySQL会忽略)。

很明显,id>3,只需要范围扫描。

n1是非唯一索引,所以type为ref。

n2这一列是唯一索引,MySQL直接把他优化到了const级别。

接下来的就不列举了,因为在开发中能优化到range就很不错了,而且有些情况因为业务的关系根本就不能优化。

7.possible_keys介绍

这个显示可能用到的索引,一个列上可能有多个复合索引,但最后只能选择其中一个使用。当然很可能一个都不会使用,也可能显示没有可能用上的索引,但最后却用上了某个索引,所以这个possible_keys没什么太大意义。

8.key

这个就真实的反应了MySQL使用了哪个索引。这个字段很关键,因为MySQL优化器的能力有限,有时候他使用的索引不一定是最优的,所以我们需要知道他到底使用了那个索引,以及强制MySQL使用某个DBA认为最优的索引。当然如果当前的查询是覆盖索引的话,这个索引也会出现在key中。

9.key_len介绍

他表示索引中使用的字节数,可通过该key_len计算查询中使用的索引长度,当然在不损失精度的情况下长度越短越好。但是这个key_len只是表示使用索引的长度最大可能是多少,并不是真实的长度。

10.举例说明

对n1和n2字段建立一个复合索引

可以看到,possible_key是null,实际使用了索引idx_n1n2,索引长度为1536,并且是覆盖索引。

那如果我们强制使用索引idx_n1呢?

可以看到,key_len变短了,同时也从覆盖索引变成了非覆盖索引。

11.ref介绍

显示索引的哪一列被使用了,如果可能的话,是一个常数。

12.rows介绍

根据表的统计信息及索引使用情况,大致估算出找出所需的记录需要读取的行数。这个rows非常重要,直接反应的SQL找了多少数据,从前面的举例说明中我们也可以看到,在完成目的的情况下当然是越少越好。这个更SQL使用的索引息息相关,可见索引的重要性。

13.extra介绍

这个是包含了一些十分重要的额外信息,也是非常重要的一个字段。

他可能包含以下一个或多个值:

1、using filesort,这个说明mysql无法利用索引进行排序,那他只能用排序算法重新进行排序了,这会额外消耗资源。出现这个的话那么说明这条SQL需要优化了,需要正确使用索引或者建立合适的索引。

下面是一个让MySQL使用正确索引的例子,同样的结果,不一样的过程。

但是具体那条SQL更快呢,也不一定,根据具体情况而定,比如数据规模之类的。还有就是开发当中一般是不允许用select * 的,要什么字段就拿什么字段,不然会造成性能浪费。

2、using temporary,建立了临时表来保存中间结果,查询完成之后又要把临时表删除。出现这样的情况说明这条SQL语句请一定要优化,如果这样的SQL一多的话非常影响系统的性能。

3、using index,前面也说过,这个表示当前的查询是覆盖索引的,直接从索引中读取数据,而不用访问数据表。如果同时出现using where表明索引被用来执行索引键值的查找,如果没有using where,表面索引被用来读取数据,而不是进行查找。

4、using where,使用了where进行条件过滤。

5、using join buffer、表示使用了连接缓存。

6、impossible where,where语句的值总是false。

7、Distinct,一旦MySQL找到了与行相联合匹配的行,就不再搜索了。

14.重点

type:访问类型,查看SQL到底是以何种类型访问数据的。

key:使用的索引,MySQL用了哪个索引,有时候MySQL用的索引不是最好的,需要force index()。

rows:最大扫描的列数。

extra:重要的额外信息,特别注意损耗性能的两个情况,using filesort和using temporary。

参考资料:

MySQL explain详解

谷粒学院

mysql explain type连接类型示例

相关阅读

回归分析详解及matlab实现

回归分析方法想要资源的请关注公众号: 在一起的足球自动获取资源和数十种经典算法,帮助各位提升自己之前留的是自己的qq号 感觉好多

Android LayoutParams详解

提示:本文的源码均取自Android 7.0 前言 在平时的开发过程中,我们一般是通过XML文件去定义布局,所以对于LayoutParams的使用可能

Android之LinearLayout详解

Android布局控件之LinearLayout详解 LinearLayout是线性布局控件,

英孚教育助力企业国际化进程 英语培训计划制定五项注

(2019年7月22日,上海)企业战略转型,尤其是海外扩张之际,第一个要匹配的就是人才战略计划,企业整体英语能力的培养往往会被一举提升到战

JSP的 页面访问执行流程

一个JSP页面有多个客户访问,下面是第一个客户访问JSP页面时候,JSP页面的执行流程: 1,客户通过浏览器向服务器端的JSP页面发送请求 2,JS

分享到:

栏目导航

推荐阅读

热门阅读