SQL优化-慢查询+explain查询

利用EXPLAIN分析sql语句的性能

mysql 优化sql语句的几种方法

大量实例让你明白SQL分析命令explain的使用 

 

慢查询 

默认情况下，MySQL数据库并不启动慢查询日志

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，就必须修改配置文件。

 

linux配置文件（/etc/my.cnf）或 windows的my.ini文件 的[mysqld]中增加

 

log-slow-queries=/var/log/mysql/slowquery.log(指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)

long_query_time=2(记录超过的时间，默认为10s)

log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format (如果设置了，所有没有使用索引的查询也将被记录)

  

查看慢查询

使用mysql自带命令 mysqldumpslow 查看慢查询：

 

mysqldumpslow -s c -t 20 -g "left join" host-slow.log

-s,表示按照何种方式排序，c,t,l,r分别表示按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；

-t,是top n的意思，即为返回前面多少条的数据；

-g,后边可以写一个正则匹配模式，大小写不敏感的；

 

 mysqldumpslow -s r -t 20 host-slow.log

  上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

 mysqldumpslow -t 10 -s t -g “left join” host-slow.log

这个是按照时间返回前10条里面含有左连接的sql语句。

 

 

explain

使用explain分析SQL.在 explain的帮助下，您就知道什么时候该给表添加索引

用法：explain  select … from … where …

 

 显示结果分析：

id | select_type | table |  type | possible_keys | key |key_len  | ref | rows | Extra 

 

a)、id

表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下

id不同，【如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行】。

id相同不相同， 结合前面两种情况，id如何相同，可以认为是一组，从上往下执行；在所有组中，id值越大，优先级越高，越先执行。

 

b)、select_type

select语句的查询类型，主要是用于区分【普通查询、联合查询、子查询】等复杂查询。有六种情况：

 

SIMPlE（simple）：简单select查询，查询中不包含子查询或者UNION。

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为此值。

SUBQUERY：在select或者where列表中包含了子查询

DERIVERD：在FROM列表中包含的子查询被标记为DERIVED(衍生)。MySQL会递归执行这些子查询，把结果放在临时表里。

SELECT cr.cname 
FROM  ( SELECT * FROM course WHERE tid IN ( 1, 2 ) ) cr

 course表会被标记为DERIVED

 

UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVERD。

UNION RESULT：从UNION表中获取结果的SELECT。

 

c)、table 

显示这一行的数据是关于哪张表的 

d)、type 

【这是重要的列，显示连接使用了何种类型】。

从最好到最差的连接类型为 system > const > eq_reg > ref > range > index > ALL 

 

system：表中只有一条记录（等于系统表），这是const类型的特列，平时不会出现。

const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如果主键置于where列表中，mysql就能将该查询转换为一个常量。 

仅仅能查到一条数据的SQL，用于Primary key或unique索引。

【如果只是能查到一条数据，但是条件列上没有Primary key或unique索引，则不是const】

 

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref：非唯一性索引扫描，返回匹配摸个单独值得所有行。本质上也是一种索引访问，它返回所有匹配某个单独值得行，然后，它可能会找到多个符合条件的行，索引他应该属于查找和扫描的混合体

rang：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引（一般就是在where语句中出现了between、<、>、in等查询）。这种范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

 

index：Full Index  Scan，

index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读取全表，但index是从索引中读取的，而all是从硬盘中读的）

all：Full Table San，将遍历全表以找到匹配的行。

 

注：一般来说，保证查询至少达到rang级别，最好能达到ref。

  

e)、possible_keys 

显示【可能应用在这张表中的索引】。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 

f)、key 

实际使用的索引。【如果为NULL，则没有使用索引】。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引 

g)、key_len 

使用的索引的长度。在不损失精确性的情况下，长度越短越好 

 

h)、ref 

显示索引的哪一列被使用了，如果可能的话，是一个常数 

i)、rows 

大致估计出找到所需的记录所需要读取的行数。

 

j)、Extra 

额外信息。

 

using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySql中无法利用索引完成的排序操作称为“文件排序”。避免这种情况。

using temporary：使用了临时表保存中间结果，MySql在对查询结果排序时使用临时表。【常见于排序查询order by和分组查询group by】。避免这种情况

using index：表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错。如果同时出现using where，表示索引被用来执行索引键值得查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

 

using where：表明使用了where过滤。

using join buffer：使用了连接缓存。

impossible where：where子句的值总是false，不能用来获取任何元组。

 

distinct：优化distinct操作，在找到第一个匹配的元组后即停止找同样值得动作。