【mysql】--explain命令解析

该篇文章主要介绍explain命令，原因是，在实际中去写一个SQL语句或分析一个SQL语句是否有优化空间【在不考虑实际高并发、快速响应的情况，任何SQL语句只要得到结果就行，这样的思想是不可取的】，往往就需要了解explain命令。

一、explain命令详解

explain命令主要如下：
id select_type table type possible_keys key ref rows filtered Extra

1、id是查询的序列号

这个没什么可讲的。

2、select_type查询的类型

用于区分 普通查询、联合查询、子查询等复杂查询。
SIMPLE：简单的select查询，查询中不包含子查询或union；
PRIMARY：查询中若包含任意复杂的子部分，最外层查询则标记为PRIMARY；
SUBQUERY:在select或where列表中包含了子查询；
DERIVED：在from列表中包含了子查询被标记为DERIVED（衍生），mysql会递归执行这些子查询，把结果放在临时表里；
UNION：若第二个select出现在union之后，则被标记为UNION；若UNION包含在from子句的子查询中，外层select将被标记为DERIVED；
UNION RESULT:从UNION表获取结果的select；

------这个也是不需要过多关注。

3、type访问类型排序

显示使用了何种类型。
最好到最差依次：
System----const----eq_ref----ref----range----index----ALL

system：表只有一行记录，很少出现，忽略。
const：表示通过索引一次就能查找到。主要用于primary key或unique索引。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见用于primary key或unique索引。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。它会找到多个符合条件的行。
range：只检索给定范围的行，使用一个索引来选择行。Key列显示使用了哪种索引，一般就是where语句中出现between、<、>、in等查询。这种范围扫描比全表扫描好。
index：只遍历索引树。也是读全表，但是从索引文件读取。
all：遍历全表以找到匹配的行。

-----------一般来说：查询至少达到range，最好是ref。

4、possible_keys

可能应用到的索引，但不一定被查询实际使用。

5、key

实际使用的索引，如果为null，那么是没使用到索引。
如果使用了覆盖索引，则该索引仅出现在key列表。

6、ref

显示索引的那一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值。

7、rows

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

8、filtered

返回结果的行占需要读到的行(rows列的值)的百分比。filtered的值一直是100%，也就是说失去了意义。

9、extra

Using fileSort：会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
Using temporary：使用临时表保存中间结果。常用于order by 和group by。
Using index：使用覆盖索引出现或索引用来读取数据而非执行查找动作。【如果同时还有using where，表明索引被用来执行索引键值的查找】
Using where;Using index：无法直接通过索引查找符合条件的数据。
【查询的列全在索引中，且where刷选条件不符合索引的前导列；
查询的列被索引覆盖，且where刷选条件是索引的前导列的一个范围。】
Using where：表明使用了where过滤。
Using index condition：查找使用了索引，但需要回表查询数据【查询的列的某一部分无法直接使用索引】。
NULL：用到了索引，但部分字段未被索引覆盖，需要“回表”返回所有数据。

---------按照本人的理解，上面的各个项，主要知道key、extra的思想就可以。了解真正含义，那么就明白一个SQL语句有没有走索引或索引是否设置合理等，从而实现高性能优化理念。

二、具体SQL分析

有测试表：

CREATE TABLE `t_hg_p2` (
  `id` bigint(11) NOT NULL COMMENT '序号',
  `a` varchar(12) DEFAULT NULL COMMENT 'a',
  `b` varchar(12) DEFAULT NULL COMMENT 'b',
  `c` varchar(12) DEFAULT NULL COMMENT 'c',
  `d` bigint(11) NOT NULL COMMENT 'd',
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='t_hg_p2表';

1、Using where;Using index

SELECT a,b,c from t_hg_p2 WHERE b=‘11’ GROUP BY a,c;

SELECT a,b,c from t_hg_p2 WHERE c=‘11’ GROUP BY a,b;

SELECT a,b,c from t_hg_p2 WHERE a=‘11’ GROUP BY b,c;

SELECT a,b,c from t_hg_p2 WHERE b=‘11’ and c=‘22’ GROUP BY a;

SELECT a,b,c from t_hg_p2 WHERE b=‘11’ GROUP BY a;

SELECT a,b,c from t_hg_p2 WHERE c=‘11’ GROUP BY a;

SELECT a,b,c from t_hg_p2 WHERE a=‘11’ GROUP BY b;

SELECT a,b,c from t_hg_p2 WHERE b=‘11’;

—上面的sql语句发现，无论where和group by怎么组合，都是使用了索引和where过滤。
原因是：select条件是查询a,b,c三列，而这三列已经有联合索引，那么mysql就不需要"不走索引的全表扫描"，而是直接在"扫描联合索引"即可。另外，上面的 sql语句where条件和group by条件组合也是使用了"联合索引"【部分或全部满足最左原则】， 因此是sql语句在联合索引基础上直接使用where过滤，来获取信息。

2、Using where; Using index; Using temporary; Using filesort

一般Using temporary是group by或order by处理过程中需要创建临时表。

SELECT a,b,c from t_hg_p2 WHERE b=‘11’ GROUP BY c;

SELECT a,b,c from t_hg_p2 WHERE a=‘11’ GROUP BY c;

SELECT a,b,c from t_hg_p2 WHERE c=‘11’ GROUP BY b;

SELECT a,c from t_hg_p2 WHERE b=‘11’ GROUP BY c;

SELECT b,c from t_hg_p2 where b=‘1212’ GROUP BY c;
—由where条件和group by知，不满足“最左前缀原则”，所以看不出来会用索引。但select条件，查询b,c，这两个字段信息完全可以在联合索引(a,b,c)来返回，所以使用了索引。所以接下来是在全局遍历（联合索引）表信息，根据where条件过滤，在排序分组，获取信息。

---------上面的解释：select字段都在联合索引<a,b,c>中。 那么在联合索引下，又使用where过滤、临时表和fileSort排序。

3、Using where

SELECT a,b,c,d from t_hg_p2 WHERE b=‘11’ GROUP BY a,c;
这是在联合索引上使用where过滤，然后会二次查询到主键索引上来获取相关字段的值。

SELECT a,b,c,d from t_hg_p2 WHERE c=‘11’ GROUP BY a,b;

SELECT a,b,c,d from t_hg_p2 WHERE b=‘11’ and c=‘22’ GROUP BY a;

SELECT a,b,c,d from t_hg_p2 WHERE b=‘11’ GROUP BY a;

SELECT a,b,c,d from t_hg_p2 WHERE c=‘11’ GROUP BY a;

SELECT a,b,c,d from t_hg_p2 WHERE b=‘11’;
这个直接没使用索引，where条件使得全表扫描来获取信息。

4、Using where; Using temporary; Using filesort

SELECT a,b,c,d from t_hg_p2 WHERE b=‘11’ GROUP BY c;

SELECT a,b,c,d from t_hg_p2 WHERE c=‘11’ GROUP BY b;

----没用索引，使用了where过滤。再有type=ALL，说明遍历全表了。同时有使用临时表和fileSort排序。

5、Using index condition

SELECT a,b,c,d from t_hg_p2 WHERE a=‘11’ GROUP BY b,c;

SELECT a,b,c,d from t_hg_p2 WHERE a=‘11’ GROUP BY b;

----使用了联合索引，但select的字段某一部分无法直接使用索引，需要回表获取相关字段信息。

6、Using index condition; Using temporary; Using filesort

SELECT a,b,c,d from t_hg_p2 WHERE a=‘11’ GROUP BY c;

----使用了联合索引，但只匹配到a字段。另外select的字段某一部分无法直接使用索引，所以上面的语句是使用了联合索引，又利用临时表和fileSort排序，再回表获取相关字段信息。

7、NULL

SELECT * from t_hg_p2;

SELECT a,b,c,d from t_hg_p2;

-----上面的两条语句，没where或order by或group by等条件，那么可以不用走索引；另外，select的字段也是在辅助索引中不能获取到，那么这是全表扫描。

8、Using index

SELECT a,b,c from t_hg_p2;

SELECT id from t_hg_p2;

如果在增加一个索引： KEY idx_d (d) USING BTREE
SELECT id from t_hg_p2;

-------使用覆盖索引出现或索引用来读取数据而非执行查找动作。从上可以看出，我们select的字段在联合索引中能直接获取，那么会选择一个联合索引来执行。

三、个人总结

上面举例一些SQL，然后分析Mysql执行查询时，是否使用了索引、临时表等。这些分析只是为了让自己有一个认识，一般来说，根据业务场景，能使用索引或降低二次查询、减少临时表、fileSort排序等，在性能上是一个很大的优化提高空间。
后面将继续讲解SQL查询优化的要点。总结不对的，望大家指点！