Jamzy Wang

life is a struggle,be willing to do,be happy to bear~~~

MySQL中的慢查询

2015-03-28 23:40

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

在基于mysql的应用中,你可能会碰到一些很慢的查询(通常耗时几秒以上),大多数情况下,这些很慢的查询都可能会与我们使用mysql有关,都有优化的空间和解决的方案,那么如何来找出这些瓶颈所在呢?本文将简单总结一下慢查询分析方法。

1. 监控慢查询

首先,要监控慢查询,就需要对mysql进行配置,使得其记录下mysql在运行过程中的一些行为。监控慢查询方法就是用日志记录下慢查询时的行为。Mysql的日志分为普通日志和慢查询日志两种。如何设置呢?

设置配置文件my.cnf,你可以把如下配置增加到my.cnf中。

1
2
3
log-slow-queries = <file_name>
long_query_time	= 1
log-queries-not-using-indexes

解释一下上述配置项:

log-slow-queries:设置了慢查询日志保存的位置。

long_query_time:设置了超过多长时间则认为其需要记录在慢速日志,上述设置为1秒。该配置只能精确到秒级别,对于某些应用来说控制粒度可能过粗了。

Log-queries-not-using-indexes:则使服务器把没有使用到索引的查询记录到慢查询日志中,无论他们的查询是否超过了这个限制值。

2. 分析慢查询

监控了慢查询后,我们就可以从慢查询日志中找到查询耗时的操作,然后想办法优化这些操作。

下面是慢查询日志的一个片段:

1
2
3
4
5
6
7
8
# Time: 101126 18:12:06
# User@Host: test[test] @ localhost []
# Query_time: 0.001778  Lock_time: 0.000128 Rows_sent: 0  Rows_examined: 235
SET timestamp=1290766326;
SELECT id FROM log_task  WHERE (state = "DONE") AND ( TIME_TO_SEC(TIMEDIFF(NOW(),`refreshTime`)) > 5)  LIMIT 0 , 99999999;
# User@Host: noah[noah] @ localhost []
# Query_time: 0.001560  Lock_time: 0.000043 Rows_sent: 1  Rows_examined: 235
SET timestamp=1290766326;

上述慢查询片段中,第一行显示了日志记录的时间,第二行显示谁执行了这些语句,第三行显示了执行这行语句所花费的时间,在mysql服务器级别等待表锁的时间,执行语句返回的行数,以及语句检索的行数。

从慢查询日志中得到具体的慢查询语句后,接下来就需要对慢查询语句进行分析,常用的分析工具有explainprofiling,下面将分别介绍这两个工具的使用方法。

2.1 用explain分析query

我们先来看一下query请求的处理过程。当MySQL连接线程接收到client端发送过来的query请求时,首先进行解析(parse),然后会通过查询优化器模块(MySQL query optimizer)根据该query所涉及的数据表相关统计信息进行计算分析,再得出一个MySQL认为最合理的执行方式,即“执行计划”,最后按照执行计划,通过调用存储引擎接口来获取相应数据并返回给client端应用程序,流程如下:

此处输入图片的描述

如果MySQL开启了Query Cache且请求命中则不走上述流程。

MySQL Query Optimizer就是通过执行explain命令来告诉我们它是怎样的一个执行计划来优化query的。应该说explain是最直接有效验证我们想法的工具。

下面拿3个简单的例子来说明它的使用:

  • 自然联接

此处输入图片的描述

这是一个简单的内联查询语句,对照附录A的解释,我们发现对于表a,optimizer没能为该语句找到合适的索引也没有任何键作为比较,它只能采用了效率极低的all(全表查询),而为了排序后能与表b进行自然联接,optimizer不得不为表a使用了临时表并使用了具体的排序算法(using temporary、using filesort)。 对于表b情况要稍好,因为我们使用了b的主键ID作为查询条件,range则表示这是一个范围查询。

  • 派生表查询

此处输入图片的描述

最终的查询的表tblTempT是tblVersion查询结果集的派生表,所以id=2的select_type字段值为DERIVED。在表tblVersion子查询中,使用了主键索引,查询条件为范围查询。

  • UNION查询

此处输入图片的描述

UNION操作只能针对两个列完全相同的表进行操作,这里为了举例方便都用tblLemma这个表。 我们看到select_type=PRIMARY说明id=1的查询是最外层查询,select_type=UNION则为UNION关键字后的第一个select及之后的查询。在id=2即union后边的查询中,我们使用了id=100的条件且使用了主键作为索引(唯一匹配),所以type字段为const。最后一行id为NULL,且select_type为UNION RESULT,表明这不是一个Query,仅仅是一个结果集的合并。

2.2 用profiling分析query

Profiling功能是MySQL 5.0版本之后出现的功能,它是一个非常方便、非常强大的Query诊断分析工具,通过该工具可以获得一条Query在整个执行过程中多种资源的消耗情况,如CPU、IO、IPC、SWAP等。具体的使用可以查看MySQL手册。

Comments