调用EXPLAIN

只需要在查询中的SELECT关键字前增加EXPLAIIN这个词。MySQL会在查询上做个标记。当执行查询时，这个标记会使其返回关于执行计划中每一步的信息，而不执行它。它返回一行或多行信息，显示出执行计划中的每一部分和执行的次序。
下面是一个最简单的EXPLAIN结果

  1. MariaDB [(none)]> explain select 1 \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: NULL
  6.          type: NULL
  7. possible_keys: NULL
  8.           key: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: NULL
  12.         Extra: No tables used
  13. 1 row in set (0.000 sec)

EXPLAIN有两个主要的变种

  • EXPLAIN EXTENDED看起来和正常的EXPLAIN一样，但它告诉服务器“逆向编译”执行计划为一个SELECT语句，可以通过紧随其后运行的SHOW WARNINGS 看到这个生成语句。
  • EXPLAIN PARTITION 会显示查询将访问的分区，如果查询是基于分区表的话。

EXPLAIN只是一个近似结果。有时候它是一个很好的近似，但在其他时候，可能与真相相差甚远。以下是一些相关限制。

  • EXPLAIN根本不会告诉你触发器、存储过程或UDF会如何影响查询。
  • 它不支持存储过程，尽管可以手动抽取查询并单独地对其进行EXPLAIN操作。
  • 它并不会告诉你关于MySQL在查询执行中所做的特定优化
  • 它并不会显示关于查询的执行计划的所有信息
  • 它并不区分具有相同名字的事物。例如它对内存排序和临时文件排序都使用“filesort”，并且对于磁盘上和内存中的临时表都显示“Using temporary”
  • 可能会误导。(早期版本不考虑LIMIT)

EXPLAIN中的列

id列

标识SELECT所属的行。如果在语句当中没有子查询或联合，那么只会有唯一的SELECT，于是每一行在这个列中都将显示为1，否则，内层的SELECT语句一般hi顺序编号，对应于其在原始语句中的位置。
MySQL将SELECT查询分为简单和复杂类型，复杂类型可以分为三类：简单子查询、所谓的派生表(在FROM子句中的子查询)，以及UNION查询。下面是一个简单的子查询

  1. mariadb> explain SELECT (SELECT 1 FROM sakila.actor LIMIT 1) FROM sakila.film ;
  2. +----+-------------+-------+………
  3. | id | select_type | table |………
  4. +----+-------------+-------+………
  5. |  1 | PRIMARY     | film  |………
  6. |  2 | SUBQUERY    | actor |………
  7. +----+-------------+-------+………
  8. 2 rows in set (0.03 sec)

select_type 列

这一列显示了对应行是简单还是复杂SELECT(如果是后者，那么是三种复杂类型中的哪一种)。SIMMPLE值意味着查询不包括子查询和UNION。如果查询有任何复杂的子部分，则最外层标记为PRIMARY。其他部分标记如下

SUBQUERY

包含在SELECT列表中的子查询的SELECT标记为SUBQUERY

DEREVED

用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表。服务器内部称其“派生表”，因为该临时表是从子查询总派生出来的。

UNION

在UNION中的第二个和随后的SELECT被标记为UNION。

UNION RESULT

用来从UNION的匿名临时表检索结果的SELECT被标记为UNION。

table列

这一列显示正在访问那个表。
可以在这一列中从上往下观察MySQL关联优化器为查询选择的关联顺序。例如，看到在下面的查询中MySQL选择的关联顺序不同于语句中指定的顺序

  1. mariadb> EXPLAIN SELECT film.film_id 
  2. from sakila.film
  3. INNER JOIN sakila.film_actor USING(film_id)
  4. INNER JOIN sakila.actor USING(actor_id);
  5. +----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
  6. | id | select_type | table      | type   | possible_keys          | key     | key_len | ref                       | rows | Extra       |
  7. +----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
  8. |  1 | SIMPLE      | actor      | ALL    | PRIMARY                | NULL    | NULL    | NULL                      | 200  |             |
  9. |  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | sakila.actor.actor_id     | 13   |             |
  10. |  1 | SIMPLE      | film       | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.film_actor.film_id | 1    | Using index |
  11. +----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
  12. 3 rows in set (0.05 sec)

派生表和联合

当FROM子句中有子查询或UNION时，table会变的复杂得多。在这些场景下，确实没有一个“表”可以参考到，因为MySQL创建的匿名临时表仅在查询执行过程中存在。

type列

ALL

全表扫描，

index

按照索引次序扫描。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。
如果看到Extra列中有“Using index”。说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描开销要少很多。

range

范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行，这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是带BETWEEN或在WHERE子句中带有>的查询。
当使用索引去查询一系列值是，例如IN 和OR列表，也会显示范围扫描。

ref

这是一种索引访问(有时也叫作索引查找)，它返回所有匹配某个单个值的行。然而，它可能会找打多个符合条件的行，因此，它是查找和扫描的混合体，此类索引访问只有当使用非唯一索引或唯一索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值想比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。

ref_or_null 是ref之上的一个变体，它意味着MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目。

eq_ref

使用这种索引查找，MySQL知道最多只返回一条符合条件的记录。这种访问方法可以在MySQL使用主键或者唯一索引查找是看到，它会将它们与某个值做比较。MySQL对于这类访问类型的优化做的非常好，因为它知道无需估计匹配行的范围或在找到匹配行后继续查找。

const,system

当MySQL能对查询的某部分进行优化并将其转换成一个常量时，他就是使用这些访问类型。举例来说，如果你通过将某一行的主键放入WHERE子句里的方式来选取此行的主键，MySQL就能把这个查询转换为一个常量，然后就可以高效地将表从联接执行中移除。

NULL

这种方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着在访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表。

possible_keys 列

这列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。这个列是在优化过程中早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。

key 列

这一列显示了MySQL决定采用哪个索引来优化对该表的访问。如果该索引没用出现在possible_keys列中，那么MySQL选用它是处于另外的原因——例如，它可能选择了一个覆盖索引，哪怕没用WHERE子句。
换而言之，possible_keys揭示了哪一个索引能有助于高效地查找，而key显示的是优化哪一个索引可以最小化查询成本。

  1. MariaDB [(none)]> EXPLAIN SELECT actor_id ,film_id from sakila.film_actor \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: film_actor
  6.          type: index
  7. possible_keys: NULL
  8.           key: idx_fk_film_id
  9.       key_len: 2
  10.           ref: NULL
  11.          rows: 5462
  12.         Extra: Using index
  13. 1 row in set (0.000 sec)

key_len列

该列显示了MySQL在索引里使用的字节数。如果MySQL正在使用的只是索引里的某些列，那么就可以用这些值来计算出具体是哪些列。需要记住的是，MySQL5.5以及之前版本只能使用索引的最左前缀。

  1. CREATE table t(
  2. a char(3) not NULL,
  3. b int(11) not NULL,
  4. c char(1) NOT NULL,
  5. PRIMARY key(a,b,c)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  7. INSERT into t 
  8. SELECT distinct left(table_schema,3),ord(table_name),left(column_name,1) FROM information_schema.COLUMNS;
  9. EXPLAIN select a FROM t where a='ask' and b=112 ;
  11. +----+-------------+-------+------+---------------+---------+---------+-------------+------+--------------------------+
  12. | id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra                    |
  13. +----+-------------+-------+------+---------------+---------+---------+-------------+------+--------------------------+
  14. |  1 | SIMPLE      | t     | ref  | PRIMARY       | PRIMARY | 13      | const,const | 1    | Using where; Using index |
  15. +----+-------------+-------+------+---------------+---------+---------+-------------+------+--------------------------+

这个查询的平均长度是13字节，即为a列和b列的总长度。(b列是一个4个字节整型)

key_len列显示了在索引字段中可能的最大长度，而不是表中数据使用的实际字节数。

ref列

这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。

  1. mariadb> EXPLAIN SELECT STRAIGHT_JOIN f.film_id FROM sakila.film as f 
  2. INNER JOIN sakila.film_actor as fa
  3. on f.film_id =fa.film_id and fa.actor_id=1
  4. INNER JOIN sakila.actor as a using (actor_id);
  5. ……+-------+……+--------------------+---------+------------------------+ 
  6. ……| table |……| key                | key_len | ref                    | 
  7. ……+-------+……+--------------------+---------+------------------------+ 
  8. ……| f     |……| idx_fk_language_id | 1       | NULL                   | 
  9. ……| fa    |……| PRIMARY            | 4       | const,sakila.f.film_id | 
  10. ……| a     |……| PRIMARY            | 2       | const                  | 
  11. ……+-------+……+--------------------+---------+------------------------+

rows列

这一列是MySQL估计为了找到所需要的行而读取的行数。这个数字是内嵌循环关联计划里的循环数目，也就是说他不是MySQL认为它最终要从表里读取出来的行数，而是MySQL为了找到符合查询的每一点上标准的那些航而必须读取的平均数。

  1. MariaDB [(none)]> EXPLAIN SELECT * FROM sakila.film LIMIT 1\G
  2. ………………………………
  3.          rows: 1000

通过把所有的rows列的值相乘，可以粗略地估算整个查询会检查的行数。例如 下面这个查询大约会检查2600行

  1. mysql> EXPLAIN SELECT f.film_id FROM sakila.film as f 
  2. INNER JOIN sakila.film_actor as fa USING(film_id)
  3. INNER JOIN sakila.actor AS a USING(actor_id);
  4. +------+- 
  5. | rows |  
  6. +------+- 
  7. | 200  |  
  8. | 13   |  
  9. | 1    |  
  10. +------+-

要记住这个数字是MySQL任务它要检查的行数，而不是结果集的行数。同时也要认识到有很多优化手段，同时关联缓冲区和缓存，无法影响到行数的显示。MySQL可能不必真的读所有它估计到的行，它也不知道任何关于操作系统或硬件缓存的信息。

filtered列

这一列是MySQL5.1里新加进去的，在使用EXPLAIN EXTENDED时出现。它显示的是针对表里符合某个条件(WHER子句或连接条件)的记录数的百分比所做的一个悲观估算。如果你把rows列和这个百分比相乘，就能看到MySQL估算它将和查询计划里前一个表关联的行数。

Extra列

这一列包含的是不适合在其他列显示的额外信息。

Using index

次值表示MySQL将使用覆盖索引，以避免访问表。

Using where

这意味着MySQL服务器将在存储引擎检查索引行后再进行过滤。许多WHERE条件里涉及索引中的列，当(并且如果)它读取索引是，就能被存储引用检验，因此不是所有带WHERE子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可以受益于不同的索引。

Using temproary

这意味着MySQL在对查询结果排序时会使用一个临时表。

Using filesort

这意味着MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。