常用慢查询分析工具简介在日常业务开发中，MySQL出现慢查询的情况非常普遍。大多数情况下有两种情况：1、业务增长太快2，或者SQL写得太xx，所以对慢查询SQL进行分析和优化非常重要。其中mysqldumpslow是MySQL服务自带的一个很好的分析调优工具。、什么是MySQL慢查询日志MySQL提供了慢查询日志记录，用于记录MySQL查询中响应时间超过阈值的记录，具体是指运行时间超过long_query_time值的SQL，会记录在慢查询日志2.如何查看慢查询设置。慢查询的时间阈值设置。显示像'%slow_query_log%'这样的变量；explainslow_query_log//默认是否启用。建议调优时开启slow_query_log_file。//慢查询日志存储路径3.如何启用慢查询日志记录1)命令启用setglobalslow_query_log=1;//只对当前会话有效，重新开始无效执行，重新执行showvariableslike'%slow_query_log%'；先关闭客户端连接，再重新连接，即可看到设置生效，mysqldumpslow调优工具启用。mysql>showvariableslike'%slow_query_log%';+--------------------+-------------------------------------------+|变量名|价值|+---------------------+----------------------------------------+|慢查询日志|开||慢查询日志文件|/opt/mysql-5.7.28/data/linux-141-slow.log|+--------------------+--------------------------------------+2rowsinset(0.02sec)mysql>2）配置文件打开vimmy.cnf，在[mysqld]下添加：slow_query_log=1slow_query_log_file=/opt/mysql-5.7.28/data/linux-141-slow.log重启MySQL服务修改重启发现mysqldumpslow调优工具mysql>showvariableslike'%slow_query_log%';+---------------------+--------------------------------------------+|变量名|值|+--------------------+--------------------------------------------+|慢查询日志|开||慢查询日志文件|/opt/mysql-5.7.28/data/linux-141-slow.log|+--------------------+--------------------------------------+2rowsinset(0.02sec)mysql>3)记录哪些SQL在慢查询日志--查看Threshold（大于），默认10s显示像'long_query_time%'这样的变量；默认值为10秒4)如何设置查询阈值命令设置——设置慢查询阈值setgloballong_query_time=1;备注：再开一个session或者重启Onlyyouconnect，会看到更改执行成功，发送慢sql的时间变成1秒。配置文件设置为vimmy.cnf[mysqld]long_query_time=1log_output=FILE重启MySQL服务执行成功，发送慢sql的时间变成了1秒我们发现默认情况下，没有使用索引的sql是不会记录在慢查询日志中的。配置集globallog_queries_not_using_indexes=on;执行如下6)模拟数据--sleep2s然后执行selectsleep(2);--检查慢查询的数量显示全局状态，如“%Slow_queries%”；我们发现每次执行selectsleep(2)，然后通过showglobalstatus...命令，它的值都会是+13.1.2调优工具mysqldumpslow常用命令的语法格式[OPTS...][LOGS...]//命令行格式常用的格式组合-s表示排序方式c访问次数l锁定时间r返回记录t查询时间al平均锁定时间ar平均返回平均查询时间的记录数-t返回之前数据的个数-g后接正则匹配模式，不区分大小写1.获取慢日志路径showvariableslike'%slow_query_log%';日志路径为：/opt/mysql-5.7.28/data/linux-141-slow.log查看日志[root@linux-141mysql-5.7.28]#cat/opt/mysql-5.7.28/data/linux-141-slow.log/opt/mysql-5.7.28/bin/mysqld，版本：5.7.28-log（MySQL社区服务器(GPL)）。开始于：Tcp端口：3306Unix套接字：/tmp/mysql.sockTimeId命令参数#时间：2021-09-15T01:40:31.342430Z#User@Host:root[root]@[192.168.36.1]Id:2#Query_time:2.000863Lock_time:0.000000Rows_sent:1Rows_examined:0useitcast;SETtimestamp=1631670031;--sleep2s然后执行selectsleep(2);[root@linux-141mysql-5.7.28]#2。获取访问量最大的10个SQL[root@linux-141mysql-5.7.28]#./bin/mysqldumpslow-sr-t10/opt/mysql-5.7.28/data/linux-141-slow.log-bash：./bin/mysqldumpslow:/usr/bin/perl:Badinterpreter:Nosuchfileordirectory[root@linux-141mysql-5.7.28]#yum-yinstallperlperl-devel[root@linux-141mysql-5.7.28]#./bin/mysqldumpslow-sr-t10/opt/mysql-5.7.28/data/linux-141-slow.log3，按时间排序的前10个条目包含leftjoinSQL[root@linux-141mysql-5.7.28]#./bin/mysqldumpslow-st-t10-g"leftjoin"/opt/mysql-5.7.28/data/linux-141-slow.log从/opt/读取mysql慢查询日志mysql-5.7.28/data/linux-141-slow.log死于./bin/mysqldumpslowline167,<>chunk28.[root@linux-141mysql-5.7.28]#3.1.3慢日志文件分析1.查看慢查询日志[root@linux-141mysql-5.7.28]#cat/opt/mysql-5.7.28/data/linux-141-slow.log/opt/mysql-5.7.28/bin/mysqld，版本：5.7.28-log（MySQL社区服务器(GPL)）。开始于：Tcp端口：3306Unix套接字：/tmp/mysql.sockTimeId命令参数#时间：2021-09-15T01:40:31.342430Z#User@Host:root[root]@[192.168.36.1]Id:2#Query_time:2.000863Lock_time:0.000000Rows_sent:1Rows_examined:0useitcast;SETtimestamp=1631670031;--睡眠2s再执行selectsleep(2);#Time:2021-09-15T01:50:32.130305Z#User@Host:root[root]@[192.168.36.1]Id:2#Query_time:3.001904Lock_time:0.000000Rows_sent:1Rows_examined:0SETtimestamp=1631670632;selectsleep(3);#Time:2021-09-15T01:50:55.064372Z#User@Host:root[root]@[192.168.36.1]Id:2#Query_time:4.008082Lock_time:0.000000Rows_sent:1Rows_examined:0SETtimestamp=1631670655;selectsleep(4);#Time:2021-09-15T01:51:01.343463Z#User@Host:root[root]@[192.168.36.1]Id:2#Query_time:5.007035Lock_time:0.000000Rows_sent:1Rows_examined:0SETtimestamp=1631670661;selectsleep(5);#Time:2021-09-15T01:51:07.737834Z######SQL执行时间#User@Host:root[root]@[192.168.36.1]id:2######SQL执行主机信息#Query_time:6.009129Lock_time:0.000000Rows_sent:1Rows_examined:0######SQL执行信息SETtimestamp=1631670667;######SQL执行时间selectsleep(6);######SQL内容[root@linux-141mysql-5.7.28]#属性解释#Time:2021-09-15T01:51:07.737834Z######执行SQL时间#User@Host:root[root]@[192.168.36.1]id:2######执行SQL主机信息#Query_time:6.009129Lock_time:0.000000Rows_sent:1Rows_examined:0######SQL执行信息SETtimestamp=1631670667;######SQL执行时间selectsleep(6);######SQL内容3.2调优工具showprofiletips：showprofile，也是一个调优工具，也是MySQL服务自带的分析调优工具，但是这是一个更高级的调优工具，更贴近底层hardwareparameters:showprofile是MySQL服务自带的比较高级的分析调优工具，更接近底层硬件参数的调优。1、查看showprofile设置——默认关闭，保存近15次showvariables的运行结果，如'profiling%'；通过上面我们发现，showprofile工具默认是关闭的，15表示已经保存了将近15个运行结果。2、打开调优工具，执行以下命令启用SETprofiling=ON；再次检查状态显示变量，如“profiling%”；3.查看最近15次运行结果--查看最近15次运行结果showprofiles;--可以显示警告和错误信息showwarnings;--慢查询语句SELECT*FROMproduct_listWHEREstore_name='联想北大兴科店';显示最近15条运行结果4.诊断运行SQL接下来我们一起来诊断queryid为23的慢查询--SyntaxSHOWPROFILEcpu,blockioFORQUERYqueryid;--例子SHOWPROFILEcpu,blockioFORQUERY129;开始执行说明：通过Status栏可以看到整个SQL1.starting//start2.checkingpermissions//checkpermissions3.openingtables//打开数据表4.init//初始化5.系统锁//锁机制6.优化//优化器7.统计//分析语法树8.准备//准备9.执行//引擎执行开始10.结束//引擎执行结束11.查询结束//查询结束12.关闭tables//释放数据表13.freeingitems//释放内存14.cleaningup//彻底清理视图类型选项SHOWPROFILE...之后的列，即：SHOWPROFILEALL,BLOCKIO,...FORQUERY209;ALL//显示索引的开销信息BLOCKIO//显示块IO相关的开销CONTEXTSWITCHES//上下文切换相关的开销CPU//显示CPU相关的开销信息IPC//显示发送接收相关的开销信息MEMORY//显示内存relatedoverheadinformationPAGEFAULTS//显示pagefault相关的开销信息SOURCE//显示与source_function,source_file,source_line相关的SW的开销信息APS//显示与交换次数相关的开销信息。如果出现以下一种或多种情况，说明SQL执行性能极低，需要优化*convertingHEAPtoMyISAM//查询结果太大，内存不够。Movedonthedisk*Creatingtmptable//创建临时表：复制数据到临时表，使用后删除*Copyingtotmptableondisk//将内存中的临时表复制到磁盘，危险*锁定//出现僵局本文由传智教育博学谷-荒野建筑师教研组发布，转载请注明出处！如果本文对您有帮助，请关注并点赞；有什么建议也可以留言或私信。您的支持是我坚持创作的动力
