慢查询原因分析

慢查询日志

开启SlowLog，默认是关闭的，由参数slow_query_log决定，在MySQL命令终端中输入下面的命令：

sql
# 设置为开启，默认情况下是off
set global slow_query_log=on;

# 设置慢查询阈值，单位是 s，默认为10s，这边的意思是查询耗时超过0.5s，便会记录到慢查询日志里面
set global long_query_time=1;

# 确定慢查询日志的文件名和路径
show global variables like 'slow_query_log_file';

#检查慢查询的详细指标
show global variables like '%quer%';

使用Explain进行查询语句分析

explain关注的字段一般有 select_type、type、possible_keys、key、rows、Extra。

select_type：代表表示查询中每个select子句的类型，是简单查询还是联合查询还是子查询，一目了然。

select_type的值	解释
SIMPLE	简单查询（不使用关联查询或子查询）
PRIMARY	如果包含关联查询或者子查询，则最外层的查询部分标记primary
UNION	联合查询（UNION）中第二个及后面的查询
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果，union语句中第二个select开始后面所有select
SUBQUERY	子查询中的第一个查询
DEPENDENT SUBQUERY	子查询中的第一个查询，并且依赖外部查询
DERIVED	派生表的SELECT, FROM子句的子查询
MATERIALIZED	被物化的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

type：表示MySQL在表中查找所需数据的方式，也称“访问类型”。

type的值	解释
system	查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表，这是最好的情况
const	基于主键或唯一索引查询，最多返回一条结果
eq_ref	类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
ref	表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
fulltext	全文检索
ref_or_null	表连接类型是ref，但进行扫描的索引列中可能包含NULL值
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	只检索给定范围的行，使用一个索引来选择行
index	Full Index Scan，index与ALL区别为index类型只遍历索引树
ALL	Full Table Scan， MySQL将遍历全表以找到匹配的行

possible_keys：应该或建议使用的索引，表示MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

key：实际使用的索引，没有的情况下为NULL。

rows：预估扫描了了多少行，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。基本表现为实际扫描过的行数。

优化

一些使用规范

1、尽量避免使用select *，join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

2、严禁使用select * from t_name，不加任何where条件，这样会变成全表全字段扫描。

3、MySQL中的text类型字段存储：不与其他普通字段存放在一起，因为读取效率低，也会影响其他轻量字段存取效率。大宽表、大字段表，整体性能也不好。如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下。

4、在取出字段上可以使用相关函数，但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。

5、分页查询语句全部都需要带有排序条件, 否则很容易引起乱序

6、用in()/union替换or，效率会好一些，并注意in的个数小于300

7、严禁使用%前缀进行模糊前缀查询。

8、尽量避免使用子查询，可以把子查询优化为join操作，通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。子查询性能差的原因：

• 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
• 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
• 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

9、在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。

10、分页查询，当limit起点较高时，可先用过滤条件进行过滤。

sql
-- 如 
select a,b,c from t1 limit 10000,20;

-- 优化为:
select a,b,c from t1 where id > 10000 limit 20;