浅谈性能瓶颈定位之MySQL慢查询

在性能测试过程中，我们会发现各种各样的性能问题，其中数据库相关的问题尤为居多。在鄙人做过的项目中，超过40%以上性能问题是跟开发人员编写的SQL有关。今天从性能测试工程师角度谈谈如何定位mysql中的 SQL慢查询。

一、通过慢查询日志获取存在性能问题的SQL

鄙人所在公司的项目的测试流程如下：

问题来了，如何做到在功能测试结束后，性能测试开始前发现单元测试/API测试/功能测试中出现的慢查询以及未走索引的查询呢？答案就是：开启这两个测试环境的mysql数据库慢查询日志，然后在日志文件里自动记录这些慢查询，以及not using index的查询。下面做一个简单的演示：

通过客户端连上mysql数据库（这里演示用的是Navicat Premium），执行下面几个命令：

set global slow_query_log=on  /*开启慢查询日志*/set log_queries_not_using_indexes=on /*开启未用查询的日志*/set global slow_query_log= “你想要的路径” /*该演示里使用默认路径*/

然后，执行如下命令，可以看到慢查询日志已经打开，并且可以看到慢查询日志存放的路径。

Show variables like ‘%slow%’

到这里我们已经开启了慢查询以及未走索引的查询日志记录，但是还差一步，那就是多慢的查询算慢查询？鄙人所在微服务项目开发和测试讨论的结果是超过100毫秒的查询，都得记录分析调优。所以还需要执行：

Set long_query_time=0.1 /*超过100毫秒的都是慢查询*/

在测试开发环境和测试环境做完上面设置后，就可以定期取日志，看到我们想看的慢查询记录，以及没用用到索引的查询。下面是工作中的一个真实示例截图：

此外，还有一款工具叫pt-query-digest，用来分析统计慢查询日志，有兴趣可以下载使用试试。

pt-query-digest --explain h=127.0.0.1, u=root,p=wwwslow-mysql.log

二、实时获取慢查询

在性能测试执行过程中，有时会发现某一个非常慢，此刻就需要实时找出正在执行的慢查询。我们可以借助下面的命令实现：

select id, 'user','host,db,command,'time',state, infofrom information_schema.processlist where time >=0.1

这里的0.1秒可以根据自己需要调整，information_schema对应数据库的processlist表

三、分析利器Showprofiles

作为性能测试人员，在我的工作中比较少用showprofiles，其中一个主要原因是执行showprofiles后得到的数据虽然更详细，但对这些数据的使用无从下手。我们看看如何使用这个工具：

set profiling = ON; /*开启profiling功能*/show variables like "profiling";  /*查看开启是否生效*/show profiles /*开启profile收集到所有sql执行的profile*/

Show profile for query 65 /*查看query_id为65的query对应时间花在什么地方了*/

show profile block io,cpu for query 65 /*查看query id为65的对应cpu, io等信息*/

四、explain：真正的好帮手

上面主要讲了怎么找到慢查询，有了慢查询sql后，我们怎么分析呢？借助explain。

一个典型的sql语句执行过程如下：

①、客户端发送sql请求给服务器

②、服务器端检查是否可以在查询缓存中命中该sql

③、服务器端进行sql解析，预处理，再由优化器生成执行计划

④、依据执行计划，调用存储引擎API来查询数据

⑤、结果返回给客户端

这里我们着重讲讲第四点执行计划，如何用explain去查看分析执行计划。：

简单使用 explain+ sql 语句来查看执行计划：

主要关注id(执行顺序)、type(表的扫描方式)、rows(检索数据需要扫描的行)、key(使用到的索引)、key_len(索引长度),extra(额外信息)。

示例1：explain语句分析发现“索引字段进行数学运算和函数运算，导致索引失效”

Payment表的paytime字段建立了索引，执行explain + SQL语句，我们可以看到：

Type=ALL,key=null, rows=14661, extra: using where

说明该查询做了全表扫描，索引没有起作用。

在第二个查询里去掉了date_format()，执行 exaplain + SQL语句，我们可以看到：

Type=range, key=payment_index3, rows=344, Extra:using index

这就是通过explain工具分析发现“索引字段进行数学运算和函数运算，导致索引失效”

示例2：explain语句分析发现“包含or的多条件查询无法通过索引检索数据，走全表扫描”

select  * fromXXX_login_account  where  actived=1 and (mobile =18504000006 orCREATE_time > '2019-07-03');

分析该表的索引，mobile和create_time都有索引，但是从执行计划看，没有走索引，走的是全表扫描：

同步到开发同事后，优化SQL，优化后的SQL分析如下：

如上图所示，index生效。