当前位置:   article > 正文

服务化-mysql-07 实战分析mysql慢查询_executing用时79秒

executing用时79秒

服务化-mysql-07 从架构师角度理解mysql优化

一、慢查询优化

1.1 慢查询配置
1.1.1 开启慢查询及配置默认慢的阈值
  • mysql8.0默认慢查询开关(OFF关)

  • show variables like ‘slow_query_log’;

  • 在这里插入图片描述

  • 手动开启(上图为开启后效果)

    set GLOBAL slow_query_log=1;

  • 检查默认慢查询时间阈值

    show VARIABLES like ‘long_query_time’;
    在这里插入图片描述

  • 修改慢查询阈值

    #自行根据业务场景配置

    set global long_query_time=1;
    在这里插入图片描述

1.1.2 配置全表扫描的语句记录至慢查询日志
  • 开启全表扫描记录值慢查询

    set global log_queries_not_using_indexes=on;
    在这里插入图片描述

  • 慢查询输出位置

show variables like ‘log_output’;

#修改输出慢查询日志的位置为文件或记录到表中

#默认存在日志中即可

set global log_output=‘FILE,TABLE’;

在这里插入图片描述

1.2 慢查询日志分析
1.2.1 mysqldumpslow慢查询日志排序

./mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar)

​ c:总次数

​ t:总时间

​ l:锁的时间

​ r:获得的结果行数

-s 对结果进行排序,怎么排,根据后面所带的 (c,t,l,r,at,al,ar),缺省为at

-t NUM just show the top n queries:仅显示前n条查询

-g PATTERN grep: only consider stmts that include this string:通过grep

来筛选语句。

1.2.2 pt-query-digest

依赖于python的查询慢查询的工具,比mysqldumpslow好用

1.3、手动分析线程状态及sql执行的耗时情况

1.3.1 show processlist;
在这里插入图片描述

https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

Mysql官网中定义了服务器中线程的状态值,诸如:

starting[语句执行开始的第一阶段]

preparing[查询优化器期间]

statistics[分析查询计划]

System lock[普遍状态]

Writing to net[服务器正在向网络写入数据包。]

如果statistics正占用大量的时间,则可能存在异常。

1.3.2 show profile分析SQL

分析线程在执行sql语句时,具体耗时在哪里。

查看是否支持profile

select @@have_profiling;
在这里插入图片描述

  • profiling默认关闭,需要手动开启session级别诊断

    select @@profiling;

    set profiling=1;
    在这里插入图片描述

  • 执行需要分析的sql语句

    select count(*) from price;

  • 通过show profiles语句,看到当前SQL的Query ID

    show profiles;
    在这里插入图片描述

  • 通过show profile for query语句能够看到执行过程中线程的每个状态和消耗的时间

    show profile for query 4;

    此时发现全表扫描一张1200w数据的表,耗时主要在执行上,executing耗时8.36s。
    在这里插入图片描述

  • 在获取到最消耗时间的线程状态后,MySQL 支持进一步选择all、cpu、block io、contextswitch、page faults等明细类型来查看MySQL在使用什么资源上耗费了过高的时间

    show profile all for query 4\G

    发现耗时主要是在CPU上,数据量太大或CPU处理器性能一般,前者的可能性更大些。
    在这里插入图片描述

  • 对比mysql官方文档查看具体耗时原因

    NULL值是因为windows上不支持查看,另外明显看出用户使用CPU时间+系统CPU时间 > Duration耗时,分析可能是系统函数的调用不支持,或者 看sql_union.cc的第1127行源码继续分析。

    https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/410020
推荐阅读
相关标签
  

闽ICP备14008679号