MySQL:Explain关键字(执行计划)

郎家岭伯爵 2023年11月06日 595次浏览

前言

在学习 MySQL 时看见别人使用 Explain 关键字,学习一下。

实现

什么是Explain?

Explain 被称为执行计划。在语句之前增加 Explain 关键字,MySQL 会在查询上设置一个标记,模拟 MySQL 优化器来执行 SQL 语句,执行查询时,会返回执行计划的信息,并不执行这条 SQL。(注意,如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中)。

Explain 可以用来分析 SQL 语句和表结构的性能瓶颈。通过 Explain 的结果,可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

Explain使用示例

执行如下 SQL 语句:

explain select * from user

在查询中的每个”表”会输出一行,这里的“表”的意义非常广泛,不仅仅是数据库表,还可以是子查询、一个 union 结果等。

Explain结果列说明

id列

id 列是一个有顺序的编号,是查询的顺序号,有几个 select 就显示几行。id 的顺序是按 select 出现的顺序增长的。

id 列的值越大执行优先级越高越先执行,id 列的值相同则从上往下执行,id 列的值为 NULL 最后执行。

select_type列

select_type 列的值标明查询的类型。

  • simple:表明当前行对应的 select 是简单查询,不包含子查询和union。
  • primary:表明当前行对应的 select 是复杂查询中最外层的 select。
  • subquery:表明当前行对应的 select 是包含在 select 中的子查询(不在 from 子句中)。
  • derived:表明当前行对应的 select 是包含在 from 子句中的子查询。

table列

table 列的结果表明当前行对应的 select 正在访问哪个表。

当查询的子句中有子查询时,table 列是 -> 格式,表示当前的 select 依赖 id=N 结果行对应的查询,要先执行 id 序号 =N 的查询。当存在 union 时,UNION RESULT 的 table 列的值为 <unionN1,N2>,N1和N2表示参与 union 的 select 行的 id 序号。

partitions列

如果查询涉及分区表,这一列将显示涉及的分区信息。

type列

type 列是一个非常重要的列,它表示 MySQL 在执行查询时使用的访问类型。type 列的结果表明当前行对应的 select 的关联类型或访问类型,也就是优化器决定怎么查找数据表中的行,以及查找数据行记录的大概范围。

该列的取值优化程度的优劣,从最优到最差依次为:null>system> const > eq_ref > ref > range > index > ALL。一般来说,要保证查询达到 range 级别,最好达到 ref。

  • null,MySQL 优化器在优化阶段分解查询语句,在优化过程中就已经可以得到结果,那么在执行阶段就不用再访问表或索引。

  • constsystemconst 出现在用 primary key(主键) 或 unique key(唯一键) 的所有列与常数比较时,优化器对查询进行优化并将其部分查询转化成一个常量。最多有一个匹配行,读取1次,速度非常快。而 systemconst 的特例,表中数据只有一条匹配时为 system。此时可以用 explain extended+show warnings 查看执行结果。

  • eq_ref:primary key(主键)或 unique key(唯一键) 索引的所有构成部分被 join 使用 ,只会返回一条符合条件的数据行。这是仅次于 const 的连接类型。

  • ref:与 eq_ref 相比,ref类型不是使用 primary key(主键) 或 unique key(唯一键)等唯一索引,而是使用普通索引或者联合唯一性索引的部分前缀,索引和某个值相比较,可能会找到符合条件的多个数据行。

  • range:出现在 in()between><>= 等操作符中。使用一个索引来查询给定范围的行。

  • index:扫描全表索引(index 是从索引中读取的,所有字段都有索引,而 all 是从硬盘中读取),比 ALL 要快。

  • all:即全表扫描,需要从头到尾去查找所需要的行。一般这种情况下这需要增加索引来进行查询优化了。

possible_keys列

这一列的结果表明查询可能使用到哪些索引。但有些时候也会出现出现possible_keys 列有结果,而 后面的 key 列显示 null 的情况,这是因为此时表中数据不多,优化器认为查询索引对查询帮助不大,所以没有走索引查询而是进行了全表扫描。

如果 possible_keys 列的结果是 null,则表明没有相关的索引。这时,可以通过优化 where 子句,增加恰当的索引来提升查询性能。

key列

这一列表明优化器实际采用哪个索引来优化对该表的访问。如果没有使用索引,则该列是 null。

key_len列

这一列表明了在索引里使用的字节数,通过这个值可以大致估算出具体使用了联合索引中的前几个列。

key_len 计算规则这里不再赘述,不同的数据类型所占的字节数是不一致的。

ref列

这一列表明了在 key 列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名,如 user.user_id。

rows列

这一列表明优化器大概要读取并检测的行数。跟实际的数据行数大部分情况是不一致的。

filtered列

这是对扫描行数的一个过滤百分比,表示实际扫描的行数占总行数的比例。较高的百分比通常是好的,因为它表示 MySQL 能够快速过滤出不必要的行。

Extra列

顾名思义,这一列表明的是额外信息,这一列的取值对优化 SQL 非常有参考意义。常见的重要取值如下:

  • using index:所有被查询的字段都是索引列(称为覆盖索引),并且 where 条件是索引的前导列,出现这样的结果,是性能高的表现。
  • using where:被查询的列未被索引覆盖,where 条件也并非索引的前导列,表示 MySQL 执行器从存储引擎接收到查询数据,再进行“后过滤”(Post-filter)。所谓“后过滤”,就是先读取整行数据,再检查此行是否符合 where 句的条件,符合就留下,不符合便丢弃。
  • using where Using index:被查询的列被索引覆盖,并且 where 条件是索引列之一但是不是索引的前导列,也就是没有办法直接通过索引来查询到符合条件的数据
  • null:被查询的列没有被索引覆盖,但 where 条件是索引的前导列,此时用到了索引,但是部分列未被索引覆盖,必须通过“回表查询”来实现,不是纯粹地用到了索引,也不是完全没用到索引
  • using index condition:与 using where 类似,查询的列不完全被索引覆盖,where 条件中是一个前导列的范围。这种情况未能通过示例显现,可能跟 MySQL 版本有关系。
  • using temporary:这表明需要通过创建临时表来处理查询。出现这种情况一般是要进行优化的,用索引来优化。创建临时表的情况:distinct,group by,orderby,子查询等。
  • usingfilesort:在使用 order by 的情况下出现,MySQL 会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。此时 MySQL 会根据连接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息。这种情况下要考虑使用索引来优化的。

使用Explain后的优化思路

结合前面的描述,首先看 type 列的结果,如果有类型是 all 时,表示预计会进行全表扫描(fulltable scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。

再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

  1. Using temporary,表示需要创建临时表以满足需求,通常是因为 GROUP BY 的列没有索引,或者 GROUP BY 和 ORDER BY 的列不一样,也需要创建临时表,建议添加适当的索引。
  2. Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。
  3. Using where,通常是因为全表扫描或全索引扫描时(type 列显示为 ALL 或 index),又加上了 WHERE 条件,建议添加适当的索引。

总结

学习下 MySQL 中 Explain 关键字的使用。