浅谈性能瓶颈定位之MySQL慢查询
- 2019 年 12 月 2 日
- 笔记
在性能测试过程中，我们会发现各种各样的性能问题，其中数据库相关的问题尤为居多。在鄙人做过的项目中，超过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生效。
