该篇文章主要介绍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查询优化的要点。总结不对的,望大家指点!