Mysql如何定位慢查询?

在MySQL中,如何定位慢查询??

慢查询表象:页面加载过慢、接口压测响应时间过长(超过1s)

1. 方案一:开源工具

调试工具:Arthas 运维工具:Prometheus 、Skywalking

图片[1]-Mysql如何定位慢查询?-不念博客
图片[2]-Mysql如何定位慢查询?-不念博客

2.方案二:MySQL自带慢日志(重点)

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志

MySQL的慢查询日志默认没有开启,可以通过查看系统变量 slow_query_log

show variables like '%slow_query_log%'
图片[3]-Mysql如何定位慢查询?-不念博客

如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息

2.1. 开启SQL慢查询日志

开启SQL慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2

配置完毕之后,通过一下命令查看,日志文件地址

show variables like '%slow_query_log%';
图片[4]-Mysql如何定位慢查询?-不念博客

配置完毕之后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息

图片[5]-Mysql如何定位慢查询?-不念博客

2.2. 慢查询如何定位总结

  1. 介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢,压测的结果大概5秒钟)
  2. 我们系统中当时采用了运维工具( Skywalking ),可以监测出哪个接口,最终因为是sql的问题
  3. 在mysql中开启了慢日志查询,我们设置的值就是2秒,一旦sql执行超过2秒就会记录到日志中(调试阶段)

3. explain执行计划

思考:那这个SQL语句执行很慢, 如何分析呢?

可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息

语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
图片[6]-Mysql如何定位慢查询?-不念博客

Explain 执行计划中各个字段的含义:

字段含义
id表示执行顺序,id相同从上到下执行,不同值越大越先执行
select_type示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
type表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all
possible_key显示可能应用在这张表上的索引,一个或多个。
key实际使用的索引,如果为NULL,则没有使用索引。
key_len索引占用的大小
Extra额外的优化建议

通过它们keykey_len 和查看是否可能会命中索引

Extra含义
Using where; Using Index查找使用了索引,需要的数据都在索引列中能找到,不需要回表查询数据
Using index condition查找使用了索引,但是需要回表查询数据

3.1 type连接类型说明

  • system:一般查询系统中的表才会出现
  • const:根据主键查询
-- 语句中id表示的是主键
explain select * from   tb_user where id=1;
  • eq_ref;表示使用唯一索引或主键进行等值连接检索。通常出现在具有关联表的等值连接查询,其中连接条件使用了唯一索引或主键。例如,通过外键连接两个表,或者使用JOIN语句时,连接条件涉及到唯一索引或主键。
  • ref:表示使用非唯一索引进行等值连接检索。通常出现在使用非唯一索引进行查询的情况,其中每个索引键值可能匹配多行记录。
-- name 是索引
 explain SELECT * FROM tb_user WHERE name= '李四'; 
  • range::表示使用索引进行范围查询。通常出现在使用范围操作符(如BETWEEN、>、<)进行查询时
 explain SELECT * FROM tb_user WHERE id BETWEEN 1 AND 10;
  • index:示全索引扫描,即遍历整个索引来定位记录,而不是根据索引中的具体值进行查找。通常出现在没有合适的索引可用,或者查询需要遍历大部分或全部索引的情况。
  • all:表示全表扫描,表示没有使用索引,需要对整个表进行遍历来找到匹配条件的记录。通常出现在没有适用的索引、谓词或查询涉及表的大部分或全部数据的情况。
explain SELECT * FROM tb_user;

4. Mysql慢查询定位面试题

面试官:MySQL中,如何定位慢查询?

候选人:

我们当时做压测的时候有的接口非常的慢,接口的响应时间超过了2秒以上,因为我们当时的系统部署了运维的监控系统Skywalking ,在展示的报表中可以看到是哪一个接口比较慢,并且可以分析这个接口哪部分比较慢,这里可以看到SQL的具体的执行时间,所以可以定位是哪个sql出了问题

如果,项目中没有这种运维的监控系统,其实在MySQL中也提供了慢日志查询的功能,可以在MySQL的系统配置文件中开启这个慢日志的功能,并且也可以设置SQL执行超过多少时间来记录到一个日志文件中,我记得上一个项目配置的是2秒,只要SQL执行的时间超过了2秒就会记录到日志文件中,我们就可以在日志文件找到执行比较慢的SQL了。

面试官:那这个SQL语句执行很慢, 如何分析呢?

候选人:

如果一条sql执行很慢的话,我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况,比如在这里面可以通过key和key_len检查是否命中了索引,如果本身已经添加了索引,也可以判断索引是否有失效的情况,第二个,可以通过type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或全盘扫描,第三个可以通过extra建议来判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复

© 版权声明
THE END
喜欢就支持一下吧
点赞110赞赏 分享
评论 抢沙发
头像
欢迎光临不念博客,留下您的想法和建议,祝您有愉快的一天~
提交
头像

昵称

取消
昵称代码图片

    暂无评论内容