MySQL Explain(执行计划)详解

在日常工作中,我们会有时会开慢查询去记录一些执行时间比较久的SQL语句,找出这些SQL语句并不意味着完事了,因此,我们常常用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。

 

以我今天亲测为示例:

通过explain执行计划 查文章ID=7的TAG标签有哪些?

EXPLAIN SELECT * FROM `tag` LEFT JOIN `tagmap` on `tagmap`.`tag_id` = `tag`.`id` WHERE `article_id` = 7

EXPLAIN SELECT * FROM `tag` LEFT JOIN `tagmap` on `tagmap`.`tag_id` = `tag`.`id` WHERE `article_id` = 7

 

经过优化后的SQL语句:

EXPLAIN SELECT * FROM `tag` INNER JOIN `tagmap` on `tagmap`.`tag_id` = `tag`.`id` WHERE `article_id` = 7

EXPLAIN SELECT * FROM `tag` INNER JOIN `tagmap` on `tagmap`.`tag_id` = `tag`.`id` WHERE `article_id` = 7

可以清楚的看到,优化后:Extra那里为NULL,已经没有Using where了。

当然了,select * 也是不允许的,我这是因为在做SQL优化测试,后期也得改正。

 

expain出来的信息有12列,分别是:

id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

 

描述:

id:选择标识符;在一个大的查询中每一个查询关键字都对应一个id

select_type:表示查询的类型

table:输出结果集的表

partitions:匹配的分区;提供有关表分区的信息

type:表示表的连接类型

possible_keys:表示查询时,可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

ref:列与索引的比较;当索引列等值查询时,与索引列进行等值匹配的对象信息

rows:扫描出的行数(估算的行数)

filtered:按表条件过滤的行百分比;某个表经过搜索条件过滤后剩余记录条数的百分比

Extra:执行情况的描述和说明

 

下面对这些字段出现的可能进行详细的解释,具体如下:

 

一、id

SELECT识别符。这是SELECT的查询序列号;在一个大的查询中每一个查询关键字都对应一个id。

 

我的理解是SQL执行的顺序的标识,SQL从大到小的执行:

1、 id相同时,执行顺序由上至下

2、如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

3、id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

 

二、select_type

查询中每个select子句的类型,具体如下:

 

1、SIMPLE:简单SELECT,不使用UNION或子查询等;简单查询

2、 PRIMARY:子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY;主查询 (出现子查询的语句时会区分子和主查询)

3、UNION:UNION中的第二个或后面的SELECT语句

4、DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

5、UNION RESULT:UNION的结果,union语句中第二个select开始后面所有select;临时表

6、SUBQUERY:子查询中的第一个SELECT,结果不依赖于外部查询;非相关子查询,非相关子查询得到的结果表会被物化,只需要执行一遍

7、DEPENDENT SUBQUERY:子查询中的第一个SELECT,依赖于外部查询;相关子查询,相关子查询可能会被执行多次

8、DERIVED:派生表的SELECT, FROM子句的子查询;派生表,出现在有子查询时,如果为该类型则代表该查询是以物化的方式执行的

9、UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存,必须重新评估外链接的第一行

10、MATERIALIZED:当子查询物化后与外层查询进行连接时的查询类型。

 

三、table

显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的),有时不是真实的表名字,可能是简称,也可能是第几步执行的结果的简称。

 

四、partitions

匹配的表分区信息。

 

PARTITIONS表提供有关表分区的信息。 此表中的每一行对应于分区表的单个分区或子分区。

有关分区表的更多信息,请参见Mysql8.0分区链接:https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

 

五、type

对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。

 

最常用的类型有:

null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL(从左到右,性能从高到底)

注意:其中null、system、const只是理想类型,基本达不到;我们自己实际能优化到 eq_ref > ref  这两个类型,就是自己写SQL。如果你一点都没优化基本上就是ALL;如果你优化了,那就尽量达到这两个级别,最差的情况下优化也得做到 eq_ref ...... range 之间。

 

ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行,全表扫描

index:Full Index Scan,index与ALL区别为index类型只遍历索引树。

range:只检索给定范围的行,使用一个索引来选择行,范围查询时出现

index subquery:与unique类似,但访问的是普通的索引

unique subquery:通常出现在相关子查询把in优化为exists而且子查询可以使用主键进行查找时

index merge:索引合并查询,同时使用了多个索引的情况

ref or null:ref的情况+条件中出现null

fulltext:全文索引

ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件

const:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。const:主键或者唯一二级索引时的常量查询,例如 where a=1,a为主键。

system:system:innodb中不存在,MyISAM、Memory引擎中有,代表精确的查询。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system。

NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如,从一个索引列里选取最小值可以通过单独索引查找完成。

 

六、possible_keys

指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(该查询可以利用的索引,如果没有任何索引显示 null)

 

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询。

 

七、Key

key列显示MySQL实际决定使用的键(索引),必然包含在possible_keys中。

 

如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

 

八、key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

 

不损失精确性的情况下,长度越短越好。

 

九、ref

列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。

 

十、rows

估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。

 

十一、filtered

filtered:它指返回结果的行占需要读到的行(rows列的值)的百分比。

 

经常会遇到:filtered列值总是100%,这是因为:filtered值只对index和all的扫描有效,其它场合,通常rows值就等于估算的结果集大小。

 

十二、Extra

Extra列是用来说明一些额外信息的,我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。一般有以下几种情况:

 

1、no tables used:当查询语句的没有FROM子句时将会提示该额外信息。

2、impossible where:where子句永远为false

3、no matching min/max row:查询列表中有min或者max聚集函数,但是并没有where子句中的搜索条件记录时会提示该额外信息

4、using index:查询列表以及搜索条件中只包含属于某个索引的列,既索引覆盖

5、using index condition:搜索条件中虽然出现了索引列,但是有部分条件无法使用索引,会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件

6、using where:全表扫描并且where中有针对该表的搜索条件

7、using join buffer(Block Nested Loop):在连接查询执行过程中,当被驱动表不能有效的利用索引加快访问速度时就分配一块join buffer内存块来加快查询速度。

8、using filesort:多数情况下排序操作无法用到索引,只能在内存中(记录较少时)或者磁盘中进行排序,这种在情况统称为文件排序。

9、using temporary:在诸多查询过程中,可能会借助临时表来完成一些查询功能,比如去重、排序之类的,比如我们在执行许多包含distinct、group by、union等子句的查询过程中,如果不能有效利用索引完成查询,mysql可能通过建立内部临时表来执行查询。

10、Start temporary, End temporary:子查询可以优化成半连接,但通过了临时表进行去重

11、firstmatch(table_name):子查询时可以优化成半连接,但直接进行数据比较去重

 

重点关注以下几个:

Using index:仅使用索引树中的信息从表中检索列信息,而不需要进行附加搜索来读取实际行(使用二级覆盖索引即可获取数据,比较好)。当查询仅使用作为单个索引的一部分的列时,可以使用此策略。

 

Using index condition:会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行;

 

Using where:( 需 要 添 加 合 适 的 索 引 ) ,不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤,需要添加合适的索引。

 

Using index for group-by:数据访问和 Using index 一样,所需数据只须要读取索引,当Query
中使用GROUP BY或DISTINCT 子句时,如果分组字段也在索引中,Extra中的信息就会是 Using index for group-by。注:和Using index一样,只需读取覆盖索引。

 

Using temporary:( 需 要 添 加 合 适 的 索 引 ) ,表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by 、order by,需要添加合适的索引。

 

Using filesort:( 需 要 添 加 合 适 的 索 引 ) ,当Query中包含 order by操作,而且无法利用索引完成的排序操作称为“文件排序”,需要添加合适的索引。

 

Using join buffer:( 需 要 添 加 合 适 的 索 引 ) ,该值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

 

Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果),无需过多关注。

 

Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行,(比 较 好 的 结 果 之 一)。

 

No tables used:Query语句中使用from dual 或不含任何from子句 unique row not。

 

found:对于诸如SELECT … FROM tbl_name的查询,没有行满足表上的UNIQUE索引或PRIMARY KEY的条件。

 

注意:

如果MySQL的Explain结果中Extra为空值代表SQL语句不用优化,性能还可以。当然了,一条好的SQL语句不能只看Extra,还要看type等其它字段,都是综合考虑的。

 

总结:

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。

• EXPLAIN不考虑各种Cache。

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作。

• 部分统计信息是估算的,并非精确值。

• EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。

 

通过收集统计信息不可能存在结果。

    A+
发布日期:2020年12月29日 13:25:37  所属分类:MySQL
最后更新时间:2020-12-31 13:25:02
评分: (1 票;平均数5.00 ;最高评分 5 ;用户总数1;总得分 5;百分比100.00)
付杰
C++基础入门到进阶视频教程
  • ¥ 498.0元
  • 市场价:498.0元
Python爬虫教程:零基础上手Scrapy分布爬虫
  • ¥ 388.0元
  • 市场价:388.0元
Swoole从入门到精通(第一季)
  • ¥ 199.0元
  • 市场价:199.0元
Golang从入门到精通视频教程
  • ¥ 499.0元
  • 市场价:499.0元

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: