MySQL Explain 详解(新) 性能优化必杀技
近期有个产品的统计信息超过了2000万行，后台的查询直接超时了，单纯sql在命令行执行都需要20s以上。用EXPLAIN后发现因为索引太多这条SQL没有用上计划的索引，更改后直接秒级出结果，下面看看EXPLAIN（Navicat里的解释按钮同样效果）的威力。
explain执行后的效果大致如下：
mysql> explain select * from student where classid=1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
总共有12列，分别是id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra,其中在我看来最重要的是key列和extra列。
key直接显示有没有使用索引，extra显示是否根据条件查询、有没有利用文件排序等。
能够把SQL优化（修改）到使用索引基本上就可以通吃绝大多数情况了。
| 列名 | 解释 |
|---|---|
| id | 查询的唯一标识 |
| select_type | 查询类型 |
| table | 查询的那个表 |
| partitions | 匹配的分区 |
| type | join类型 |
| possible_keys | 可能使用的索引 |
| key | 最终使用的索引 |
| key_len | 最终使用的索引的长度 |
| ref | 与索引一起被使用的字段或常数 |
| rows | 查询扫描的行数，是个估算值 |
| filtered | 查询条件所过滤的数据的百分比 |
| Extra | 额外的信息 |
1、id
查询的唯一标识，当这行是联合查询中的被驱动的表时这个值是Null。
2、select_type的可能的值有如下：
| 值 | 解释 |
|---|---|
| SIMPLE | 表示此查询不包含 UNION 查询或子查询 |
| PRIMARY | 表示此查询是最外层的查询 |
| UNION | 表示此查询是 UNION 的第二或随后的查询 |
| DEPENDENT UNION | UNION 中的第二个或后面的查询语句, 取决于外面的查询 |
| UNION RESULT | UNION 的结果 |
| SUBQUERY | 子查询中的第一个 SELECT |
| DEPENDENT SUBQUERY | 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果 |
| DERIVED | 驱动表 |
| MATERIALIZED | 子查询的实现（Materialized subquery） |
| UNCACHEABLE SUBQUERY | 不能被缓存的子查询 |
| UNCACHEABLE UNION | 在联合查询中的且不能被缓存的子查询中的后续查询 |
3. table
查询涉及的表名。
4. partitions
查询的记录所在的分区，如果表没有被拆分这个值是Null。
5. type
连接的类型，不同的类型及说明如下：
| 值 | 解释 |
|---|---|
| system | 这是下面类型const的一种特殊情况。表里只有一条数据。 |
| const | 针对主键或者索引的等值查询，该表最多有一个匹配的行，由于只有一行，因此该行中列的值可以被优化器的其余部分视为常量。 |
| eq_ref | 当连接使用索引的所有部分并且索引是a PRIMARY KEY或UNIQUE NOT NULL索引时。 |
| ref | 所有具有匹配索引值的行都从这个表中读取 |
| fulltext | 使用FULLTEXT 索引执行 |
| ref_or_null | ref的基础上包含了NULL值的搜索 |
| index_merge | 使用索引合并优化 |
| unique_subquery | 只是一个索引查找函数，可以完全替代子查询以提高效率。 |
| index_subquery | 取代了IN子查询，但它适用于非唯一索引的子查询。 |
| range | 只有在给定范围内的行才能被检索，使用索引来选择行。的key 输出行中的列指示使用哪个索引。将key_len包含已使用的时间最长的关键部分。该ref列是 NULL用于这种类型的。 |
| index | 使用索引查找，如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多 |
| ALL | 全表扫描 |
6. possible_keys
可能被使用的索引，如果此列是NULL，则表示没有相关的索引，可以通过WHERE 子句来检查是否引用某些适合索引或列，从而提高查询的性能。
要查看表有哪些索引，请使用 SHOW INDEX FROM tbl_name
7. key
上面的possible_keys是可能被使用的key，这个式实际被使用的键。
如果 key是NULL，表示MySQL没有发现有索引可以用于更有效地执行查询。要强制MySQL使用索引或忽略索引，可以使用FORCE INDEX，USE INDEX或IGNORE INDEX语句。
8. key_len
被使用索引的长度。取决于key列，如果key为NULL，这列也是NULL。
9. ref
该列显示将哪些列或常量与列中指定的索引进行比较，以 key从表中选择行。
10. rows
MySQL认为查询必须扫描的行数。
对于InnoDB存储引擎，这个数字是估计值，并不总是准确的。
11. filtered
条件过滤后的行占表内总行数的百分比。
12. Extra,
表示MySQL解析查询的其他信息。
Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。
| 值 | 解释 |
|---|---|
| Using filesort | MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。 |
| Using temporary | 用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。 |
| Not exists | MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。 |
| Using index | 说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。 |
| Using index condition | 这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。 |
| Using where | 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。 |
| Using join buffer | 使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接 |
| impossible where | where子句的值总是false，不能用来获取任何元组 |
| select tables optimized away | 在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 |
| distinct | 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。 |
参考
http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/explain-output.com.coder114.cn.html#explain-extra-information
https://www.jianshu.com/p/ea3fc71fdc45
http://www.deituicms.com/mysql8cn/cn/web.html
