图文结合带你搞懂MySQL日志之Slow Query Log(慢查询日志)
什么是慢查询日志
MySQL 的慢查询日志,用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过 long_query_time 值的SQL,则会被记录到慢查询日志中。long_query_time 的默认值为10,意思是运行10秒以上(不含10秒)的语句,认为是超出了我们的最大忍耐时间值。
它的主要作用是,帮助我们发现那些执行时间特别长的SQL查询,并且有针对性地进行优化,从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候,检查一下慢查询日志,找到那些慢查询,对解决问题很有帮助。比如一条sq|执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合explain进行全面分析。
默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数。如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响
慢查询日志支持将日志记录写入文件。
如何开启慢查询日志
开启slow_query_log
然后我们再来查看下慢查询日志是否开启,以及慢查询日志文件的位置:
你能看到这时慢查询分析已经开启,同时文件保存在 /var/lib/mysql/KAiTO-slow.log 文件中。
修改long_query_time阈值
接下来我们来看下慢查询的时间阈值设置,使用如下命令:
意思就是超过10秒的SQL语句就会被记录慢查询日志中,那要如何修改这个阈值呢?
或修改 my.cnf 文件,[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后,然后重启MySQL服务器。
如果不指定存储路径,慢查询日志将默认存储到 MySQL 数据库的数据文件夹下。如果不指定文件名,默认文件名为hostname-slow.log。
补充
- min_examined_row_limit
除了上述变量,控制慢查询日志的还有一个系统变量: min_examined_row_limit。这个变量的意思是,查询扫描过的最少记录数。这个变量和查询执行时间,共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值,并且查询执行时间超过long_query_time的值,那么,这个查询就被记录到慢查询日志中; 反之,则不被记录到慢查询日志中。
你也可以根据需要,通过修改 my.cnf 文件,来修改min_examined_row_limit的值。
除了记录普通的慢查询之外,MySQL 还提供了两个参数来让我们记录未使用索引的查询,它们分别是:log-queries-not-using-indexes 和 log_throttle_queries_not_using_indexes
- log-queries-not-using-indexes
系统变量log-queries-not-using-indexes作用是未使用索引的查询也被记录到慢查询日志中。
- log_throttle_queries_not_using_indexes
可通过设置 log_throttle_queries_not_using_indexes 来限制每分钟写入慢日志中的不走索引的SQL语句个数,该参数默认为 0,表示不开启,也就是说不对写入SQL语句条数进行控制。
在生产环境下,如果没有使用索引,那么此类 SQL 语句会频繁地被记录到 slow log,从而导致 slow log 文件大小不断增加,我们可以通过调整此参数进行配置。
- log_slow_extra
如果启用 log_slow_extra 系统变量(从 MySQL 8.0.14 开始提供),服务器会在日志写入几个额外字段。若要记录bytes_received 与 bytes_sent这两个字段则需要开启
- percona slow log
GreatSQL是源于Percona Server的分支版本,除了Percona Server已有的稳定可靠、高效、管理更方便等优势外,特别是进一步提升了MGR(MySQL Group Replication)的性能及可靠性,以及众多bug修复。这就是为什么在使用GreatSQL查看慢查询日志时,会有Query_time、Lock_time等信息,这些都是我们GreatSQL源于Percona Server的原因,使查询内容更加丰富,更多的数据可以使得我们更好的排查错误。
通过一个简单的案例来展示:我们先把慢查询日志打开且设置时间阈值大于1秒就记录:
写一条SQL语句使得使用时间大于1秒
可以看到此条SQL已经被记录,接下来我们去查看慢查询日志:
可以看到慢查询日志记录的非常详细,从上述日志中能看到几个信息:
1.这个SQL的耗时3.985637秒。
2.返回结果有165346行,总共需要扫描9900000行数据。如果扫描行数很多,但返回行数很少,说明该SQL效率很低,可能索引不当。
3.Read_* 等几个指标表示这个SQL读记录的方式,是否顺序读、随机读等。
4.Sort_* 等几个指标表示该SQL是否产生了排序,及其代价。如果有且代价较大,需要想办法优化。
5.tmp 等几个指标表示该SQL是否产生临时表,及其代价。如果有且代价较大,需要想办法优化。
6.Full_scan/Full_join表示是否产生了全表扫描或全表JOIN,如果有且SQL耗时较大,需要想办法优化。
7.InnoDB_IO_* 等几个指标表示InnoDB逻辑读相关数据。
8.InnoDB_rec_lock_wait 表示是否有行锁等待。
9.InnoDB_queue_wait 表示是否有排队等待。
10.InnoDB_pages_distinct 表示该SQL总共读取了多少个InnoDB page,是个非常重要的指标。
GreatSQL可以作为MySQL或Percona Server的可选替代方案,用于线上生产环境。完全免费并兼容MySQL或Percona Server。综上,如果在生产环境中已经用上Percona Server的话,那么也可以放心使用GreatSQL。详情可见:(https://greatsql.cn/doc/#!&v=47_6_0)了解更多GreatSQL内容
查看慢查询数目
查询当前系统中有多少条慢查询记录
慢查询日志分析工具
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具 mysqldumpslow ,或者是可以使用另一个工具pt-query-digest。它可以从logs、processlist、和 tcpdump 来分析 MySQL 的状况,logs包括 slow log、general log、binlog。也可以把分析结果输出到文件中,或则把文件写到表中。分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。
关闭慢查询日志
作者建议除了调优需要开,正常还是不要开了
MySQL服务器停止慢查询日志功能的方法:
- 方式1
- 方式2
删除慢查询日志
通过以上查询可以看到慢查询日志的目录,在该目录下手动删除慢查询日志文件即可。或使用命令 mysqladmin 来删除,mysqladmin 命令的语法如下:mysqladmin -uroot -p flush-logs执行该命令后,命令行会提示输入密码。输入正确密码后,将执行删除操作。新的慢查询日志会直接覆盖旧的查询日志,不需要再手动删除。
注意慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意,一旦执行了这个命令,慢查询日志都只存在新的日志文件中,如果需要旧的查询日志,就必须事先备份。
参考文章
《MySQL是怎样运行的--从根儿上理解MySQL》—小孩子
4919(https://juejin.cn/book/6844733769996304392)
相关文章
- Kroger EDI 850 采购订单报文详解
- Pyhon基础知识之Json序列化与反序列化
- 寻亲32年后找回被拐儿子!全国打拐第一数据库立功,为0-14岁儿童预存DNA信息
- R语言-基础+向量
- mysql脏读、幻读、不可重复读
- R语言随机森林RandomForest、逻辑回归Logisitc预测心脏病数据和可视化分析|附代码数据
- 振弦采集模块监测传感器频率值不稳定
- 终于!SOFATracer 完成了它的链路可视化之旅
- ESXI主机bond下配置
- 数据库概述
- 阿里云数据中台升级品牌新主张,发布智能风控引擎Quick Decision和隐私计算DataTrust
- ABAP 编程语言里的 Reference Semantic - 引用语义
- 81. 使用 SAP ABAP Memory Inspector 对应用程序消耗内存进行检测时常犯的错误
- Redis 实现用户积分和积分排行榜微服务优化
- GaussDB(DWS)数据库的数据迁移实操【玩转PB级数仓GaussDB(DWS)】
- 直观感受PromQL及其数据类型
- 数据框、矩阵和列表20230202
- 正确的做网站搜索——如何避免XAHWW的社死悲剧
- 云数据库RDS MySQL版备份恢复最佳实践
- 程序员必备的数据库知识:数据存储结构