SQL性能调优利器-Explain详解

 

一、概述

在日常数据库维护工作中，我们常常需要查看一个SQL在数据库中到底是怎么运行的，那么Explain就是这样一个查看查询类SQL（其他语句要转换为查询语句进行分析）执行计划的工具，比如查看该SQL语句有没有使用上了索引，有没有做全表扫描以及该sql扫描的类型等，那么这篇文章详细解释了Explain的用法以及相关示例，希望帮助大家更深层次的了解MySQL执行计划。

 

二、详情

格式：Explain 需要分析的SQL语句

输出主要为以下几个部分：

1. ID

id列中的数据为一组数字，表示执行SQL语句或者操作表的顺序，id值相同时，执行顺序由上至下，id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

示例1：

->explain select test2.*

-> from test1,test2,test3

-> where test1.id=test2.id2 and test1.id=test3.id3

-> and test1.name=\'\';

id相同，从上到下依次执行。

示例2：

->explain select test1.id

-> from test1

-> where id =(select id2 from test2 where id2 = (select id3 from test3 where name3=\'\') ) ;

子查询，id越大，优先级越高，越先被执行。

 

2. select_type

select_type主要有以下几种： 

SIMPLE:是一个简单查询，不包含子查询或是union操作的查询

示例：->explain select id from test1;

PRIMARY:查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMAY

示例：

->explain select test1.id

-> from test1

-> where id =(select id2 from test2 where id2 = (select id3 from test3 where name3=\'\') ) ;

SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

示例：

->explain select test1.id 

-> from test1

-> where id =(select id3 from test3 where name3=\'\');

UNION:Union操作的第二个或是之后的查询值为union；若UNION包含在  FROM子句的子查询中，外层SELECT将被标记为：DERIVED

示例：

->explain select id from test1 union select id2 from test2;

UNION RESULT:从UNION表获取结果的查询

示例：

->explain select name from test1 where id =(select id2 from test2 union select id3 from test3);

DERIVED：出现在FORM子句中的子查询

->explain select id,name2 from test1 t,(select name2 from test2) d where t.name=d.name2;

 

3. Table

执行计划中数据是哪个表输出的；

输出数据所在的表的名称或者别名；

由ID为M,N查询union操作后参数的结果集，这个结果集其实是一个临时表，<unionM,N> 查询是有M和N产生的。

4. Type

表示MySQL在表中找到所需行的方式，又称为“访问类型”，常见类型如下：

ALL：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

示例：

->explain select amount from test1;

Index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

示例：

->explain select name from test1;

Range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

示例：

->explain select amount from test1 where id in (1,2);

Ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

示例：

->explain select amount from test1 where name=\'\';

eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

示例：

-> explain select test2.*

-> from test1,test2,test3

-> where test1.id=test2.id2 and test1.id=test3.id3

-> and test1.name=\'\';

NULL:MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

示例：

->explain select id from test1 where amount=\'\';

Const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

示例：

->explain select * from t_car_brand where id=1;

 

5. possible_keys

指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

 

6. Key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

注意：select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

 

7. key_len

key_len显示的值为索引字段的最大可能长度(单位为字节)，可通过该列计算查询中使用的索引的长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

示例：

->desc test1;

->explain select amount,name,amount from test1; 

->explain  select name from test1;

8. Ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

示例：

->explain select test1.id

-> from test1

-> where id = (select id3 from test3 where name3=\'\');

9. Rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

未添加索引前：

添加索引后：

10. Extra

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

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

示例：

->explain select name from test1 where name=\'alice\';

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

示例：

->explain select name from test1 where id =(select id2 from test2 union select id3 from test3);

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

示例：

->explain select amount2 from test2 order by amount2;

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

示例：

->explain select sum(amount) from test1 where id = -1;

补充说明

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• EXPLAIN部分统计信息是估算的，并非精确值

• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划