第十章：第5节 MySQL进阶篇——explain详解

第九章16节我们提到过“explain”，并用它做过演示。explain命令是MySQL做优化很重要的一个工具，我们叫它“查询优化器”。它主要是用来检验select语句有没有用到索引，从而判断select语句是否是高效的，以此为依据我们对select语句或者数据表做些优化改动。

使用方法，在select语句前加上explain就可以了。如：

下面的内容是楠神搜集多篇相关内容总结而来：

explain是如何工作的？

MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。

EXPLAIN 的每个输出行提供一个表的相关信息,并且每个行包括下面的列:

1、id:

SELECT识别符。这是SELECT查询序列号。这个不重要，查询序号即为sql语句执行的顺序。id 值越大优先级越高，越先被执行。id 相同，执行顺序由上至下。如：

EXPLAIN SELECT * FROM (SELECT* FROMuchome_space LIMIT10) AS s;

EXPLAIN SELECT s.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address
FROM uchome_space AS s,uchome_spacefield AS f
WHERE 1 
AND s.groupid=0
AND s.uid=f.uid;

2、select_type

查询类型，它有以下几种值：

3、table

显示这一行的数据是关于哪张表的，好理解。

4、type

这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_ref、ref、range、index和ALL

详细说明：

const:

记住一定是用到primary key 或者unique，并且只检索出两条数据的 情况下才会是const,看下面这条语句

explain SELECT * FROM `asj_admin_log` limit 1,结果是

虽然只搜索一条数据，但是因为没有用到指定的索引，所以不会使用const。继续看下面这个

explain SELECT * FROM `asj_admin_log` where log_id = 111

log_id是主键，所以使用了const。所以说可以理解为const是最优化的。

eq_ref:

对于eq_ref的解释，mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。看下面的语句

explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid

得到的结果是下图所示。很明显，mysql使用eq_ref联接来处理uchome_space表。

目前的疑问：

      为什么是只有uchome_space一个表用到了eq_ref,并且sql语句如果变成

       explain select * from uchome_space,uchome_spacefield where uchome_space.uid = uchome_spacefield.uid

       结果还是一样，需要说明的是uid在这两个表中都是primary

ref:

ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。

explain select * from nbl_member_borrow_repayment where invest_id = 3319;

range：

range 给定范围内的检索，使用一个索引来检查行。看下面两条语句

explain select * from uchome_space where uid in (1,2)

explain select * from uchome_space where groupid in (1,2)

uid有索引，groupid没有索引，结果是第一条语句的联接类型是range,第二个是ALL。

explain select * from uchome_space where friendnum = 17

这样的语句是不会使用range的，它会使用更好的联接类型就是上面介绍的ref

index：

全索引扫描。该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

5、possible_keys

指出 MySQL 能在该表中使用哪些索引有助于查询。如果为空,说明没有可用的索引。

6、key

MySQL 实际从 possible_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引。

7、key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好。索引的长度指的是索引列的数据的字节数。

8、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

9、rows

显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引

10、Extra

关于MYSQL如何解析查询的额外信息。

详细说明：

Using index

这里又会讲到索引的一个概念“覆盖索引Covering Index”

什么是覆盖索引？很好理解。它不是具体的一种索引，而是索引（主键、唯一、普通索引）被使用时的一种形式。

select语句用到一个索引，只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后再根据物理地址定位到具体数据。所以，一个select语句能用到覆盖索引，那查询起来是非常快的。Extra信息中如果有“Using Index”，就表示select使用了覆盖索引。

使用覆盖索引一定要注意所查询的字段必须是索引中的字段，比如

KEY `borrow_id` (`borrow_id`,`batch`),

select `borrow_id`,`batch` from tab where `borrow_id` = 1 and `batch` > 2;

如果能用到“borrow_id”索引，它一定使用到覆盖索引了。

select * from tab where `borrow_id` = 1 and `batch` > 2;

即便用到了“borrow_id”索引，它也用不到覆盖索引。

注意：如果表是InnoDB，id是主键ID。

select `id`,`borrow_id`,`batch` from tab where `borrow_id` = 1 and `batch` > 2;

如果能用到“borrow_id”索引，它一定也使用到覆盖索引。为什么呢？还记得InnoDB的索引实现方式吗？（本章第3节的内容）普通索引存储的相应数据不是物理地址，而是数据相对应的主键ID，所以id也可以从索引中获得，这也能用到覆盖索引。

Using temporary

为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

出现using temporary就说明语句需要优化了，举个例子来说

EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = 'online'   AND city.ads_id=ads.id ORDER BY ads.id desc

这条语句会使用using temporary,而下面这条语句则不会

EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = 'online'   AND city.ads_id=ads.id ORDER BY city.ads_id desc

这是为什么呢？他俩之间只是一个order by不同，MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。EXPLAIN 结果中，第一行出现的表就是驱动表。以上两个查询语句，驱动表都是 city，如上面的执行计划所示！

对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序。因此，order by ads.id desc 时，就要先 using temporary 了！

驱动表的定义

wwh999 在 2006年总结说，当进行多表连接查询时， [驱动表] 的定义为：
1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
2）未指定联接条件时，行数少的表为[驱动表]。

永远用小结果集驱动大结果集