引言:见过很多关于MySQL的查询语句优化的文章,但是当你用过EXPLAIN后,了解其中的原理后,发现那些文章都太业余了。总之一句,EXPLAIN简直不要太好用了!

 

EXPLAIN是MySQl必不可少的一个分析工具,主要用来测试sql语句的性能及对sql语句的优化,或者说模拟优化器执行SQL语句。

简单的说是execute plan, 获取MySQL数据库的执行计划。本文重点介绍explain的使用和各参数的意义。

 

首先看一个例子,现有一张表格filesinfo,主键FileId,建立索引 create index DevInfoIndex on FilesInfo
(CamID, SliceStartTime,
SliceStopTime);表格除DevInfoIndex显示的字段外还有其他几个字段。目前表格有110左右条数据。

然后我们执行下面语句:

(1)

该SQL语句,对主键FileId进行排序,且只查找开始四条。而rows也是4,因为可以看到key中直接用的主键索引。

(2)我们再执行下一条语句:

 和上面比较,我们把排序改成了索引DevInfoIndex 的第一个字段。rows还是4,而key变化了,使用索引DevInfoIndex
,最后Extra显示使用了索引。

(3)接着我们继续执行:

 和上一条语句比较,我们只是修改排序的字段,SliceStartTime是索引的第二个字段。而rows变成了53。

从Extra看,虽然查找了索引,但是还用了filesort文件排序
,也就是说明联合索引的数据先按第一个字段的顺序进行存储,后面的字段单独需要排序查找时,需要再对文件进行排序。

(4)继续

这次语句的变化主要是查找内容,*表示所有字段;排序字段还是索引的首个字段CamID,可是type变成了ALL,全表扫描;所以Extra显示filesort。

 

以上几步操作只是 对explain工具显示的结果的几个主要参数进行分析和展示。

下面再逐一进行介绍。

 

id: 查询的序列号;查询中执行select子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。

select_type :  查询的类型,主要包括普通查询、联合查询、子查询。主要取值:

  SIMPLE------简单的select查询,不使用union及子查询.

  PRIMARY------最外层的select查询

  UNION------UNION 中的第二个或随后的select查询,不依赖于外部查询的结果集

  DEPENDENT UNION------UNION 中的第二个或随后的select查询,依赖于外部查询的结果集

  SUBQUERY------子查询中的第一个select查询,不依赖于外 部查询的结果集

  DEPENDENT SUBQUERY------子查询中的第一个select查询,依赖于外部查询的结果集

  DERIVED------用于 from 子句里有子查询的情况。 MySQL会递归执行这些子查询, 把结果放在临时表里

  UNCACHEABLE SUBQUERY------结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估

  UNCACHEABLE UNION------UNION 中的第二个或随后的selec查询,属于不可缓存的子查询 

table:所访问数据库的表的名称

type:联合查询使用的类型显示的访问类型是重要指标,结果从好到坏:

  system------系统表;表仅有一行(=系统表)。这是const连接类型的一个特例。

  const------读常量;const用于用常数值比较 PRIMARY KEY 时。当查询的表仅有一行时,使用System。

  eq_ref ------最多一条匹配结果,通常是通过主键访问;

  ref------;连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。叫做ref是因为索引要
跟某个参考值相比较。这个参考值或者是一个常数,或者是来自一个表里的多表查询的结果值。

  fulltext------全文索引检索;

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

  index_merge------合并索引结果集;说明索引合并优化被使用了。

  unique_subquery------;在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT
primary_key FROM single_table WHERE some_expr)

  index_subquery()------子查询返回的是索引,但非主键;
在某些IN查询中使用此种类型,与unique_subquery类似,但是查询的是非唯一性索引:value IN (SELECT key_column FROM
single_table WHERE some_expr)

  range------索引范围扫描;只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS
NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用range。

  index------全索引扫描;全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。

  all------全表扫描;最坏的情况,从头到尾全表扫描。

 

possible_keys: 指出mysql能使用哪个索引有助于在该表中找到该行,如果这个值为空,则表示没有相关的索引。

key:显示mysql实际决定使用的建,如果没有索引被选择,键是null。

key_len:显示mysql决定使用索引的长度,在不损失精确性的情况 下,长度越短越好。如果key是空,则长度就是null。

ref:显示哪个字段或常数与key一起使用。如果使用了索引则显示const,否则,显示null。

rows:MYSQL 认为必须检查的用来返回请求数据的行数。

Extra: 出现以下2项意味着MYSQL根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

  Using filesort------表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。

          MySQL 中无法利用索引完成的排序操作称为“文件排序”。

  Using temporary------表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

 

以上对命令explain及其参数的意义基本介绍完成。希望以后对于SQL语句的性能分析,是否需要优化,做到心中有数。

 

技术
©2019-2020 Toolsou All rights reserved,
一道经典的MySQL面试题,答案出现三次反转第一个使用Python完成的数据化运营案例—销售预测Linux下网络通信编程实现程序员面试金典--题目解析-3.3 实现数据结构SetOfStacks,维护多个栈事务操作数据结构之【数组和广义表】复习题前端搜索功能(五)linux错误码大全canvas下雨打雷效果【Tiny4412移植Linux--2】uboot烧写到sd卡