腾讯一面:你平时怎么排查并调优慢 SQL 的
上一篇我们说了 腾讯一面:说一说 MySQL 中索引的底层原理,相信你对索引有个很清晰的认识了,这一篇我们来说一说慢 SQL 的排查以及调优。为啥面试官要问这个问题,其实跟上一篇的索引底层原理有一定关联关系的,一般慢 SQL 很大一部分原因对索引底层原理不够特别了解导致的,比如没建索引、索引失效、索引没满足最左前缀匹配原则导致慢 SQL,像腾讯这样数据量很大的公司,人家肯定有专门的 DBA 去做优化的,面试官考察的主要是你排查问题的能力以及知道索引的底层原理、以及知道优化的方向,不至于让你进来把人家规规矩矩的数据库搞乱了。
我们下面直接进入正题了,首先来说下怎么排查慢 SQL 的。
二、开启慢查询日志MySQL 中与慢 SQL 有关的几个重要系统变量如下:
执行如下语句看是否启用了慢查询日志,ON 为启用,OFF 为未启用,默认为 OFF。
SHOWVARIABLESLIKE"%slow_query_log%";
可以看到,我这里是已经开启了的。如果你的没有开启,可以使用如下两种方式来开启慢查询。
2.1 修改配置文件修改配置文件 vim /etc/my.cnf,在 [mysqld] 段落在加入如下配置:
[mysqld]slow_query_log=1slow_query_log_file=/var/lib/mysql/data/slow.loglong_query_time=3log_output=FILE,TABLE
需要重启 MySQL 才可以生效,命令为 service mysqld restart
2.2 设置全局变量如下打开慢查询日志,设置超时时间为 3 秒,并且将日志记录到文件以及 mysql.show_log 表中。
SET GLOBAL slow_query_log = 1;SET GLOBAL slow_query_log_file="/var/lib/mysql/data/slow.log";SET GLOBAL long_query_time=3;SET GLOBAL log_output="FILE,TABLE";
想要永久生效得用上面那个配置文件里配置,否则数据库重启后,这些配置失效。
三、分析慢查询日志3.1 获取慢 SQL 信息查看慢查询日志记录数:
SHOWGLOBALSTATUSLIKE"%Slow_queries%";
模拟语句:
selectsleep(5);
查看日志:
cat/var/lib/mysql/data/slow.log3.2 mysqldumpslow
MySQL 内置了 mysqldumpslow 这个工具来帮我们分析慢查询日志。
#得到返回记录集最多的10个SQLmysqldumpslow-sr-t10/var/lib/mysql/data/slow.log#得到访问次数最多的10个SQLmysqldumpslow-sc-t10/var/lib/mysql/data/slow.log#得到按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow-st-t10-g"LEFTJOIN"/var/lib/mysql/data/slow.log#结合|more使用,防止爆屏情况mysqldumpslow-sr-t10/var/lib/mysql/data/slow.log|mores:表示按何种方式排序c:访问次数l:锁定时间r:返回记录t:查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间t:返回前面多少条的数据g:后边搭配一个正则匹配模式,大小写不敏感3.3 pt-query-digest
pt-query-digest 是一款很强大的慢查询日志分析工具,可以分析 MySQL 数据库的 binary log 、 general log 日志,同时也可以使用 show processlist 或从 tcpdump 抓取的 MySQL 协议数据来进行分析。
这里老周不带大家搭建以及使用了哈,想进一步了解的可以看这份文档:pt-query-digest
四、Explain 执行计划分析慢 SQL上一篇我们非常详细的去介绍了,一文读懂 MySQL Explain 执行计划
五、Show Profile 分析慢 SQLShow Profile 也可以分析慢 SQL,比 explain 获取的信息更详细,比如能分析当前会话中语句执行的资源消耗情况,能分析这条 SQL 整个生命周期的耗时。但没有上面 pt-query-digest 那款慢查询日志分析工具强大,但 pt-query-digest 是外置的需要单独下载,如果你想用内置的话,能够满足你的需求的话,选择 Show Profile 就行。
5.1 如何开启默认关闭。开启后,会在后台保存最近 15 次的运行结果,然后通过 Show Profile 命令查看结果。
--开启SETprofiling=ON;--查看SHOWVARIABLESLIKE"profiling%";5.2 SHOW profiles 查看 SQL 的耗时5.3 SQL 整个生命周期的耗时
通过 Query_ID 可以得到具体 SQL 从连接——服务——引擎——存储四层结构完整生命周期的耗时
SHOWprofileCPU,BLOCKIOFORQUERY4;
5.4 危险状态可用参数 type:ALL # 显示所有的开销信息BLOCK IO # 显示块IO相关开销CONTEXT SWITCHES # 上下文切换相关开销CPU # 显示CPU相关开销信息IPC # 显示发送和接收相关开销信息MEMORY # 显示内存相关开销信息PAGE FAULTS # 显示页面错误相关开销信息SOURCE # 显示和 Source_function,Source_file,Source_line 相关的开销信息SWAPS # 显示交换次数相关开销的信息
SHOWprofileCPU,BLOCKIOFORQUERY2;
GROUP BY 可能创建了临时表
converting HEAP to MyISAM # 查询结果太大,内存不够用了,在往磁盘上搬。Creating tmp table # 创建了临时表,回先把数据拷贝到临时表,用完后再删除临时表。Copying to tmp table on disk # 把内存中临时表复制到磁盘locked # 记录被锁了
看到这些危险状态可以进行相应的调优,然后我们线上也会针对慢 SQL 进行监控,存在慢 SQL 的话会触发告警机制,通知相应的人员快速定位慢 SQL 并优化。
欢迎大家关注我的公众号【老周聊架构】,Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。
相关阅读
-
世界热推荐:今晚7:00直播丨下一个突破...
今晚19:00,Cocos视频号直播马上点击【预约】啦↓↓↓在运营了三年... -
NFT周刊|Magic Eden宣布支持Polygon网...
Block-986在NFT这样的市场,每周都会有相当多项目起起伏伏。在过去... -
环球今亮点!头条观察 | DeFi的兴衰与...
在比特币得到机构关注之后,许多财务专家预测世界将因为加密货币的... -
重新审视合作,体育Crypto的可靠关系才能双赢
Block-987即使在体育Crypto领域,人们的目光仍然集中在FTX上。随着... -
简讯:前端单元测试,更进一步
前端测试@2022如果从2014年Jest的第一个版本发布开始计算,前端开发... -
焦点热讯:刘强东这波操作秀
近日,刘强东发布京东全员信,信中提到:自2023年1月1日起,逐步为...