SQL语句优化：explain和show profile分析

sql语句出现查询时间长，需要通过sql语句的优化来解决此问题，通过expalin和show profile方法来分析sql查询慢的原因。

一、expalin分析

1、使用方法

   explain  sql

2、分析

（1）、执行explain方法的编号；

（2）、select_type查询的类型，是单表查询、联合查询、子查询等；

查询类型（select_type）	解释说明
SIMPLE	简单的select的查询，不使用union、子查询
PRIMARY	最外层的查询是select(使用主键作为查询条件)
UNION   UNION	第二个或者随后的查询select，不依赖于外部查询的结果集
DEPENDENT UNION	第二个或者随后的查询select，依赖于外部的查询结果集
SUBQUERY	子查询的第一个select查询，不依赖于外部查询的结果集
DEPENDENT SUBQUERY	子查询的第一个select查询，依赖于外部的查询结果集
DERIVED	用于from子句里面有查询的情况。mysql会递归执行子查询，会把结果放在临时的表里
UNCACHEABLE SUBQUERY	结果集不用用于缓存的子查询，必须重新为外层查询的每一行进行评估
UNCACHEABLE UNION UNION	第二个或者随后的select查询，不可用于缓存的查询

（3）、table，查询的表明；

（4）、连接使用的类型type(******) ；

  从最优到最差的排序：system >  const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range  > index > all。

（5）、possible_keys：在该表中使用哪些索引有助于查询；

（6）、key：实际使用的索引；

（7）、key_len：使用索引的长度，在保证精确的的情况下，越短越好；

（8）、ref：索引的哪一列被使用；

（9）、rows：返回结果的行数；

（10）、extra：其他说明

     Using temporary、Using filesort这两种情况会严重影响查询的效率。

extra	解释说明
Using temporary	表示mysql在查询结果时使用临时表，常见查询为group by（分组查询）和order by（排序查询）
Using filesort	表示mysql的查询结果会使用外部索引进行排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。mysql中无法利用索引排序的操作称为“文件排序”

二、show profile分析

1、使用方法

（1）、开启profiling    set profiling = 1; 

（2）、show profile 

                                                       

其中，sending data占用较长的时间，最主要的原因可能是查询的数据量大。

2、show profile 分析

show profile <type> [for QUERY <num>]

<type> 显示资源的类型，<num>显示哪条sql，通过show profile来查找最消耗性能的阶段，并对其进行优化。

三、sql优化

1、count优化

（1）、有where条件的情况下，查询速度由快到慢：count(where中使用的字段)>count(*)>count(未索引的字段)；

（2）、无where条件的情况下，查询速度由快到慢：count(*)>count(s索引字段)>count(未索引的字段)。

2、max优化

在max的字段上添加索引，可以实现查询效率的提升。

alter table tableName add index  indexName(tableColumn);

select max(tableColumn) from tableName;

3、子查询优化

如果查询的条件以左表的主键作为查询条件，使用in的子查询效率更高；如果查询的条件不是以左表的主键作为查询条件，使用连接（join）查询更加高效。

四、尽量避免使用select *

select后面只需要添加所需要表中的字段，select *会查询一些不必要的字段，形成资源的浪费。

五、汇总

1、where条件中、order by 、group by中尽量使用已索引的字段；

2、查询必要的字段和limit限定记录数；

3、尽量使用explain和首位profile来查看执行的效率，并对其进行sql的优化。