Home Tags Posts tagged with "explain"

explain

0 70

对于explain命令的使用并不熟悉,只了解它是用来进行SQL优化的,今天特此记录.

 

以下来源于知乎:

刷面试题的时候,不知道你们有没有见过MySQL这两个命令:explainprofile(反正我就见过了)..

之前虽然知道这两个命令大概什么意思,但一直没有去做笔记。今天发现自己的TODO LIST有这么两个命令,于是打算来学习一番,记录一下~

使用的MySQL的版本为5.6.38

一、explain命令

1.1体验explain命令

首先我们来体验一下explain命令是怎么使用的,以及输出的结果是什么:

explain select * from jd_user ;

输出结果:

发现很使用起来很简单,只要explain后边跟着SQL语句就完事了(MySQL5.6之前的版本,只允许解释SELECT语句,从 MySQL5.6开始,非SELECT语句也可以被解释了)。

 

1.2为什么需要explain命令

我们很多时候编写完一条SQL语句,往往想知道这条SQL语句执行是否高效。或者说,我们建立好的索引在这条SQL语句中是否使用到了,就可以使用explain命令来分析一下!

  • 简单来说:通过explain命令我们可以学习到该条SQL是如何执行的随后解析explain的结果可以帮助我们使用更好的索引,最终来优化它!

通过explain命令我们可以知道以下信息:表的读取顺序数据读取操作的类型哪些索引可以使用哪些索引实际使用了,表之间的引用每张表有多少行被优化器查询等信息。

// 好了,我们下面看一下explain出来的结果是怎么看的。

1.3读懂explain命令结果

explain命令输出的结果有10列:id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

1.3.1id

包含一组数字,表示查询中执行SELECT子句或操作表的顺序

在id列上也会有几种情况:

  • 如果id相同执行顺序由上至下。
  • 如果id不相同,id的序号会递增,id值越大优先级越高,越先被执行
    • (一般有子查询的SQL语句id就会不同)

1.3.2select_type

表示select查询的类型

select_type属性下有好几种类型:

  • SIMPLLE:简单查询,该查询不包含 UNION 或子查询
  • PRIMARY:如果查询包含UNION 或子查询,则最外层的查询被标识为PRIMARY
  • UNION:表示此查询是 UNION 中的第二个或者随后的查询
  • DEPENDENT:UNION 满足 UNION 中的第二个或者随后的查询,其次取决于外面的查询
  • UNION RESULT:UNION 的结果
  • SUBQUERY:子查询中的第一个select语句(该子查询不在from子句中)
  • DEPENDENT SUBQUERY:子查询中的 第一个 select,同时取决于外面的查询
  • DERIVED:包含在from子句中子查询(也称为派生表)
  • UNCACHEABLE SUBQUERY:满足是子查询中的第一个 select 语句,同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中
  • UNCACHEABLE UNION:满足此查询是 UNION 中的第二个或者随后的查询,同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中

类型有点多啊,我加粗的是最常见的,起码要看得懂加粗的部分。

1.3.3table

该列显示了对应行正在访问哪个表(有别名就显示别名)。

当from子句中有子查询时,table列是 <derivenN>格式,表示当前查询依赖 id=N的查询,于是先执行 id=N 的查询

1.3.4type

该列称为关联类型或者访问类型,它指明了MySQL决定如何查找表中符合条件的行,同时是我们判断查询是否高效的重要依据

以下为常见的取值

  • ALL:全表扫描,这个类型是性能最差的查询之一。通常来说,我们的查询不应该出现 ALL 类型,因为这样的查询,在数据量最大的情况下,对数据库的性能是巨大的灾难
  • index:全索引扫描,和 ALL 类型类似,只不过 ALL 类型是全表扫描,而 index 类型是扫描全部的索引,主要优点是避免了排序,但是开销仍然非常如果在 Extra 列看到 Using index,说明正在使用覆盖索引,只扫描索引的数据,它比按索引次序全表扫描的开销要少很多。
  • range:范围扫描,就是一个限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行。这个类型通常出现在 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN() 的操作中,key 列显示使用了哪个索引,当 type 为该值时,则输出的 ref 列为 NULL,并且 key_len 列是此次查询中使用到的索引最长的那个。
  • ref:一种索引访问,也称索引查找,它返回所有匹配某个单个值的行。此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询。
  • eq_ref:使用这种索引查找,最多只返回一条符合条件的记录。在使用唯一性索引或主键查找时会出现该值,非常高效。
  • const、system:该表至多有一个匹配行,在查询开始时读取,或者该表是系统表,只有一行匹配。其中 const 用于在和 primary key 或 unique 索引中有固定值比较的情形。
  • NULL:在执行阶段不需要访问表。

1.3.5possible_keys

这一列显示查询可能使用哪些索引来查找

 

1.3.6key

这一列显示MySQL实际决定使用的索引。如果没有选择索引,键是NULL。

1.3.7key_len

这一列显示了在索引里使用的字节数,当key列的值为 NULL 时,则该列也是 NULL

1.3.8ref

这一列显示了哪些字段或者常量被用来和key配合从表中查询记录出来。

1.3.9row

这一列显示了估计要找到所需的行而要读取的行数,这个值是个估计值,原则上值越小越好。

1.3.10extra

其他的信息

常见的取值如下:

  • Using index:使用覆盖索引,表示查询索引就可查到所需数据不用扫描表数据文件,往往说明性能不错
  • Using Where:在存储引擎检索行后再进行过滤,使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。
  • Using temporary:在查询结果排序时会使用一个临时表,一般出现于排序、分组和多表 join 的情况,查询效率不高,建议优化。
  • Using filesort:对结果使用一个外部索引排序,而不是按索引次序从表里读取行,一般有出现该值,都建议优化去掉,因为这样的查询 CPU 资源消耗大。

0 61

正文

数据库调优其实一般情况都是我们的SQL调优,SQL的调优就可以解决大部分问题了,当然也不排除SQL执行环节的调优。

数据库的组成可能很多小伙伴都忘记了,那我们再看一遍结构图吧。

我们所谓的调优也就是在执行器执行之前的分析器优化器阶段完成的,那我们开发工作中怎么去调优的呢?

帅丙一般在开发涉及SQL的业务都会去本地环境跑一遍SQL,用explain去看一下执行计划,看看分析的结果是否符合自己的预期,用没用到相关的索引,然后再去线上环境跑一下看看执行时间(这里只有查询语句,修改语句也无法在线上执行)。

遇SQL不决explain,但是这里就要说到第一个坑了。

排除缓存干扰

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的,我之前就被坑过,因为存在缓存,我发现我sql怎么执行都是很快,当然第一次其实不快但是我没注意到,以至于上线后因为缓存经常失效,导致rt(Response time)时高时低。

后面就发现了是缓存的问题,我们在执行SQL的时候,记得加上SQL NoCache去跑SQL,这样跑出来的时间就是真实的查询时间了。

我说一下为什么缓存会失效,而且是经常失效。

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存,那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的,大家也看到我们的结构图了,一个请求会先去看缓存是否存在,不存在才会走解析器。

缓存失效比较频繁原因就是,只要我们一对表进行更新,那这个表所有的缓存都会被清空,其实我们很少存在不更新的表,特别是我之前的电商场景,可能静态表可以用到缓存,但是我们都走大数据离线分析,缓存也就没用了。

大家如果是8.0以上的版本就不用担心这个问题,如果是8.0之下的版本,记得排除缓存的干扰。

Explain

最开始提到了用执行计划去分析,我想explain是大家SQL调优都会回答到的吧。

因为这基本上是写SQL的必备操作,那我现在问大家一个我去阿里面试被问过的一个问题:explain你记得哪些字段,分别有什么含义?

当时我就回答上来三个,我默认大家都是有数据库基础的,所以每个我这里不具体讨论每个字段,怕大家忘记我贴一遍图大家自己回忆一下。

那我再问大家一下,你们认为统计这个统计的行数就是完全对的么?索引一定会走到最优索引么?

当然我都这么问了,你们肯定也知道结果了,行数只是一个接近的数字不是完全正确的,索引也不一定就是走最优的,是可能走错的。

我的总行数大概有10W行,但是我去用explain去分析sql的时候,就会发现只得到了9.4W,为啥行数只是个近视值呢?

看过基础章节的小伙伴都知道,MySQL中数据的单位都是页,MySQL又采用了采样统计的方法,采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。

我们数据是一直在变的,所以索引的统计信息也是会变的,会根据一个阈值,重新做统计。

至于MySQL索引可能走错也很好理解,如果走A索引要扫描100行,B所有只要20行,但是他可能选择走A索引,你可能会想MySQL是不是有病啊,其实不是的。

一般走错都是因为优化器在选择的时候发现,走A索引没有额外的代价,比如走B索引并不能直接拿到我们的值,还需要回到主键索引才可以拿到,多了一次回表的过程,这个也是会被优化器考虑进去的。

他发现走A索引不需要回表,没有额外的开销,所有他选错了。

如果是上面的统计信息错了,那简单,我们用analyze table tablename 就可以重新统计索引信息了,所以在实践中,如果你发现explain的结果预估的rows值跟实际情况差距比较大,可以采用这个方法来处理。

还有一个方法就是force index强制走正确的索引,或者优化SQL,最后实在不行,可以新建索引,或者删掉错误的索引。