您当前的位置:首页 > 电脑百科 > 数据库 > 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   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
Explain是一个非常有的命令,可以用来获取关于查询执行计划的信息,以及如何解释输出。Explain命令是查看查询优化器如何决定执行查询的主要方法。这个功能有一定的局限性,并不总...【详细内容】
2020-01-03  Tags: MySQL EXPLAIN  点击:(81)  评论:(0)  加入收藏
前言我们在写服务端代码时,经常会与SQL语句打交道,最初写SQL时主要注重的是能不能正常执行SQL,然后查询出的数据是否正常。如果这两方面没有问题,基本不会考虑SQL执行速度的问...【详细内容】
2019-10-10  Tags: MySQL EXPLAIN  点击:(131)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(32)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条