新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT百科 > 数据库 > MYSQL

MySQL执行计划命令EXPLAIN详解

时间:2019-10-10 09:30:25  来源:  作者:
MySQL执行计划命令EXPLAIN详解

 

前言

我们在写服务端代码时,经常会与SQL语句打交道,最初写SQL时主要注重的是能不能正常执行SQL,然后查询出的数据是否正常。如果这两方面没有问题,基本不会考虑SQL执行速度的问题,毕竟这个时候数据库中数据量还是很少的,怎么写都执行都很快。但是随着数据量的增加,会发现系统查询速度越来越慢,这时就需要对系统进行优化了。

最初优化的方向就是是否存在慢查询,如果存在我们应该怎么去优化这些SQL语句,今天我们就介绍一下日常中经常使用的EXPLAIN命令。

EXPLAIN命令简介

EXPLAIN命令可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

通过EXPLAIN我们可以分析出一下几个结果:

  1. 表的读取顺序;
  2. 数据读取操作的操作类型;
  3. 哪些索引可以使用;
  4. 哪些索引被实际使用;
  5. 表之间的引用;
  6. 每张表有多少行被优化器查询

EXPLAIN的使用方式:EXPLAIN + SQL语句,例如:

EXPLAIN SELECT * FROM t1

执行结果包含的信息,如下图:

MySQL执行计划命令EXPLAIN详解

 

执行计划返回有10个字段,分别是:id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面我们就具体分析执行结果中返回的每个字段的具体含义。

EXPLAIN执行计划字段含义详解

ID字段

ID字段是SELECT查询的序列号,表示查询中执行select子句或操作表的顺序;

ID字段大概可以出现3中不同的情况:

1、ID字段数值都相同,如下图:

MySQL执行计划命令EXPLAIN详解

 

此时所有的ID字段数值都相同,SQL语句执行顺序由上到下,顺序执行。SQL中不存在子查询等,如下SQL:

SELECT * FROM a , b WHERE a.id = b.id

2、ID字段数值按递增顺序出现,如下图:

MySQL执行计划命令EXPLAIN详解

 

此时ID字段的数值是递增出现的,ID数值越大优先级就越高,SQL语句就优先执行,对应的SQL语句如下:

SELECT * FROM a WHERE a.id = (SELECT id FROM b WHERE b.id = (SELECT id FROM c where c.sub_id = 1))

子查询级别越深,ID的数值就越大,也就优先执行。

3、ID字段中相同数值和不相同数值同时存在,如下图:

MySQL执行计划命令EXPLAIN详解

 

此时ID数值相同的算作一组,级别相同按顺序执行,ID数值越高的级别越高,越先执行。

注意:衍生出来的表定义为DERIVED,有多个时会在“DERIVED”后加入一个数字标识区分。

此种情况对应的SQL,如下:

SELECT *
FROM (
	SELECT a.id,a.sub FROM a
) b 
INNER JOIN c ON c.id = b.id

select_type字段

select_type字段主要用来区分查询的类型,如普通查询、联合查询、子查询等类型,具体可以分为以下几种类型:

  • SIMPLE 简单的select查询,查询中不包含子查询或者UNION;
  • PRIMARY 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY;
  • SUBQUERY 在SELECT或WHERE列表中包含了子查询;
  • DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中;
  • UNION 若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED;
  • UNION RESULT 从UNION表获取结果的SELECT

table字段

table字段指的是当前执行的表

type字段

type字段是比较重要的,平常我们进行优化时主要关注的也是这个字段,这个字段表示的是我们在查询时访问表的方式,大概分ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好。一般来说,得保证查询至少达到range级别,最好能达到ref)。

几种类型简介:

  • ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
  • index: Full Index Scan,index与ALL区别为index类型只遍历索引树
  • range:只检索给定范围的行,使用一个索引来选择行
  • ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
  • eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
  • const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system
  • NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys 和 key字段

  • possible_keys:显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
  • key:实际使用的索引,如果为NULL,则没有使用索引。(可能原因包括没有建立索引或索引失效) ;查询中若使用了覆盖索引(select 后要查询的字段刚好和创建的索引字段完全相同),则该索引仅出现在key列表中

key_len字段

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

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

ref字段

显示索引的那一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。

rows字段

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好。

Extra字段

该列包含MySQL解决查询的详细信息,有以下几种情况:

  1. Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤
  2. Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by
  3. Using filesort:当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”
  4. Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
  5. Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。
  6. Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
  7. No tables used:Query语句中使用from dual 或不含任何from子句

总结

虽然EXPLAIN很简单,但是对于优化SQL还是很有帮助的,当然如果数据量特别大的时候,我们需要另外想别的方法进行优化。



Tags:MySQL EXPLAIN   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
前言我们在写服务端代码时,经常会与SQL语句打交道,最初写SQL时主要注重的是能不能正常执行SQL,然后查询出的数据是否正常。如果这两方面没有问题,基本不会考虑SQL执行速度的问...【详细内容】
2019-10-10   MySQL EXPLAIN  点击:(11)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条