SQL 优化之 Explain 命令


SQL 优化之 Explain 命令

背景

在SQL优化的时候我们都会使用Explain命令去查看该条SQL的执行计划信息。

使用 Explain 关键字可以模拟优化器执行SQL语句,分析查询语句或是结构的性能瓶颈。在select语句之前增加Explain关键字,MySQL会在查询上设置一个标记,执行查询会返回执行计划的信息。因此我们可以根据这个信息去判断该条SQL是否按照我们的预期执行,他的效率和性能是不是良好的

Explain 信息结构详解

执行Explain命令后会返回如下信息:

Image Text

根据上面的信息,我们讲解下各列的作用。

id列

id列的编号是select的序列号,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的。id越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行

出现多个id的情况通常适用于关联子查询,即查询的表是select查询后的虚拟表(临时表)

select_type列

他表示对应行是简单还是复杂的查询,有以下几个值

  • simple: 简单查询。查询不包含子查询和union
  • primary: 查询中若包含任何复杂的子查询,最外层查询则标记为primary,也就是最后加载的那个select
  • subquery:包含在select或者where子句中的子查询(不在from子句中)
  • derived: 包含在from子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表

    derived只会在子查询作为临时表时出现

  • union:在union关键字随后的select

该列的例子sql语句

explain select (select 1 from actor where id = 1) from (select * from film
where id = 1) der;

explain select 1 union all select 1;

table列

这一列标识Explain的一行正在访问哪个表

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

当有union时,union result 的table列的值为 <union 1,2> 1和2标识参与union的select行id值

type列

这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行对应的大概范围。

依次从最优到最差分别为: system > const > eq_ref > ref > range > index > All

一般来说,除了All都会使用到索引,得保证查询能达到range级别,最好是ref。

NULL: MySQL能够在优化阶段分解查询语句,在执行阶段用不着访问表或者索引。例如:在索引列中取最小值,可以单独查询索引来完成,不需要执行时访问表。

下面依次说下各值的含义:

  • const 、 system: mysql能对查询的某部分进行优化并将其转换成一个常量(可以看做是show warnings的结果)。 用于primary key或者unique key的所有列与常数比较时,所以表最多有一个匹配行,读取依次,读速较快。system是const的特例,表中只有一行元素匹配时为system。
  • eq_ref: primary key或者unique key索引的所有部分被连接使用,最多返回一条符合条件的记录。这可能是const之外最好的联接类型,简单的select查询不会出现这种type。
  • ref: 相比eq_ref,不适用唯一索引(unique key),而是使用普通索引或者唯一索引的部分前缀,索引要和某个值比较,可能会找到多个符合条件的行
  • range: 范围扫描通常出现在in(),between,>,<,>=等操作中。使用一个索引来检索给定范围的行
  • index:扫描全表索引,通常比ALl快一些
  • All:全表扫描,意味着MySQL需要从头到尾去查找所有行。这种情况下需要增加索引来进行优化。

possible_keys列

这一列显示select可能会使用那些查询来查找(通常展示会用到的所有索引名称,以,连接)

key列

这一列显示MySQL实际采用哪个索引对该表的访问。

如果没有使用索引,则该列为NULL。

key_len列

这一列显示了mysql在索引里使用的字节数,通过这个值可以估算出具体使用了索引中的那些列

例如复合索引中,没有使用全部字段,只使用了部分,可以通过该列推断出具体情况

计算规则:

  1. 字符串
    • char(n):n字节长度
    • varchar(n):n字节存储字符串长度,如果是ntf-8,则长度为3n+2
  2. 数值类型:
    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节
  3. 时间类型
    • date:3字节
    • timestamp:4字节
    • datetime:8字节

ref列

这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有: const(常量),字段名等。一般是查询条件或关联条件中等号右边的值,如果是常量,那么ref列是const,非常量的话就是字段名

row列

这一列是mysql估计要读取并检测的行数,注意这个不是结果集的行数。

filtered列

5.7版本后默认出现这个列,标识存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量比例(是百分比不是具体记录数)。

Extra列

这一列是额外信息。

  • Using index: 使用覆盖索引(结果集的字段是索引,即select后的字段是film_id)
  • Using index condition: 查询的列不完全被索引覆盖,where条件是一个前导的范围
  • Using where:使用where语句来处理结果,查询的列未被索引覆盖
  • Using temporary: mysql需要创建一张临时表来处理查询。出现这种情况一般要进行优化,首先就是索引优化

参考资料:Mysql Explain详解


文章作者: 我若为侠
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 我若为侠 !
 上一篇
事务的特性以及隔离级别 事务的特性以及隔离级别
事务的特性以及隔离级别什么是事务个人理解,事务就是一连串的sql语句或者说一个独立的工作单元。如果数据库能成功的对该工作单元中所有的sql语句执行成功那么该工作单元就完成。如果其中任何一个sql语句因为奔溃或者其他原因无法执行,那么所有的语
下一篇 
Redis五种数据类型以及其应用场景 Redis五种数据类型以及其应用场景
Redis五种数据类型以及其应用场景背景Redis是nosql数据库中的一种,也是目前市面上广泛被使用的数据库之一,因为Redis也是内存型数据库,数据存储在内存里,因此它的IO非常之高,我们也常常利用他做缓存的设计来解决并发的问题。 在使
  目录