当你执行SQL查询时，MySQL查询优化程序会尝试为SQL执行设计一个最佳方案。你可以使用EXPLAIN前缀来查看有关执行计划相关信息。EXPLAIN是用于优化慢查询的最强大的工具之一。在本文中，将介绍EXPLAIN的输出以及如何使用它来优化SQL查询。

EXPLAIN的输出

使用EXPLAIN就像在SELECT查询之前预先挂起一样简单，我们分析一个简单SQL查询的输出，来熟悉EXPLAIN命令返回的列。

mysql> EXPLAIN SELECT * FROM user_tokens\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_tokens
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: NULL
1 row in set (0.00 sec)

• id: 查询中每个SELECT的顺序标识符（当有嵌套子查询时）；
• select_type: 表示SELECT的类型，常见的值有：
  • SIMPLE: 一个简单的SELECT查询，不包含表连接和子查询；
  • PRIMARY: 主查询，即外层的查询；
  • DERIVED: 派生表SELECT（FROM子句中的子查询）;
  • SUBQUERY: 子查询的第一个SELECT；
  • DEPENDENT SUBQUERY: 在子查询中的第一个SELECT，依赖于外部查询;
  • UNCACHEABLE SUBQUERY: 不能缓存结果的子查询，并且必须对外部查询的每一行进行重新评估
  • UNION: UNION中的第二个或者后面的SELECT语句；
  • DEPENDENT UNION: UNION中第二个或后面的SELECT，依赖于外部查询
  • UNION RESULT: SELECT是联合查询的结果；
• table: 输出结果集的表；
• type: 表示MySQL在表中找到所需行的方式，或者叫访问类型，常见的类型如下：

从上到下，性能由最差到最好。

1. type=ALL: 全表扫描，MySQL遍历全表来找到匹配的行；
2. type=index: 索引全扫描，MySQL遍历整个索引来查询匹配的行；
3. type=range: 索引范围查找，常见于<,<=,>,>=,between等操作；
4. type=ref: 使用非唯一索引或唯一索引的前缀扫描，返回某个单独值的记录行；
5. type=eq_ref: 类似ref，区别就是使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配。也就是在多表连接中使用primary key或者unique index作为关联条件；
6. type=const/system: 单表中最多有一个匹配行，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，比如：根据primary key或者unique index进行的查询；
7. type=NULL: MySQL不访问表或者索引，就可以直接得到结果，比如：SELECT 1 FROM dual;.

• possible_keys: 表示查询时可能使用到的索引，如果为空表明没有找到相关的索引；
• key: 表明实际使用到的索引；
• key_len: 使用索引字段的长度；
• ref: 显示哪个列或者常量与key列中命名的索引做比较从表中查出记录。
• rows: 扫描的行数，对InnoDB表，这个数字是预估的，通常并不准确；
• Extra: 执行情况的说明和描述，包括不适合在其他列显示但是对执行计划非常重要的额外信息；
  • Using filesort:将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，需要添加合适的索引;
  • Using temporary:需要创建一个临时表来存储结果，这通常发生在对没有索引的列进行GROUP BY时，或者ORDER BY里的列不都在索引里，需要添加合适的索引;
  • Using index:表示MySQL使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一。
  • Using where:通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤，需要添加合适的索引
  • Impossible WHERE:对WHERE子句判断的结果总是false而不能选择任何数据，例如where 1=0，无需过多关注
  • Select tables optimized away:使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如MIN()、MAX()，这种也是比较好的结果之一。

---

EXPLAIN EXTENDED

我们还可以在SQL查询前添加EXPLAIN EXTENDED关键字，MySQL将向我们显示有关执行查询的方式额外信息。 要查看这些信息，请在执行完EXPLAIN EXTENDED语句之后使用SHOW WARNINGS进行查看。这主要用于查看查询优化器在任何转换后执行的查询。

mysql> explain extended select escalationid,actionid,triggerid,eventid,r_eventid,nextcheck,esc_step,status,itemid from escalations where triggerid is not null order by actionid,triggerid,itemid,escalationid\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: escalations
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 91
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `zabbix`.`escalations`.`escalationid` AS `escalationid`,`zabbix`.`escalations`.`actionid` AS `actionid`,`zabbix`.`escalations`.`triggerid` AS `triggerid`,`zabbix`.`escalations`.`eventid` AS `eventid`,`zabbix`.`escalations`.`r_eventid` AS `r_eventid`,`zabbix`.`escalations`.`nextcheck` AS `nextcheck`,`zabbix`.`escalations`.`esc_step` AS `esc_step`,`zabbix`.`escalations`.`status` AS `status`,`zabbix`.`escalations`.`itemid` AS `itemid` from `zabbix`.`escalations` where (`zabbix`.`escalations`.`triggerid` is not null) order by `zabbix`.`escalations`.`actionid`,`zabbix`.`escalations`.`triggerid`,`zabbix`.`escalations`.`itemid`,`zabbix`.`escalations`.`escalationid`
1 row in set (0.00 sec)

---

EXPLAIN PARTITIONS

针对分区表还可以使用关键字： EXPLAIN PARTITIONS关键字来获取SQL所需要访问的分区名字：

mysql> CREATE TABLE `partitions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  PARTITION BY HASH (id) PARTITIONS 8;
Query OK, 0 rows affected (1.36 sec)

mysql> insert into partitions (id) values (null);
Query OK, 1 row affected (0.39 sec)

mysql> insert into partitions (id) values (null);
Query OK, 1 row affected (0.39 sec)

mysql> insert into partitions (id) values (null);
Query OK, 1 row affected (0.39 sec)

mysql> select * from partitions;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> explain partitions select * from partitions where id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: partitions
   partitions: p1
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

参考资料：

• EXPLAIN Output Format
• Using EXPLAIN to Write Better MySQL Queries
• mysql-faq-some-important-in-explain