MySQL调优之慢查询日志应用

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL调优之慢查询日志应用

【1】慢查询日志

① 什么是慢查询日志

慢查询日志是用于记录SQL执行时间超过某个临界值的SQL日志文件,可用于快速定位慢查询,为我们的SQL优化做参考。


具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的SQL语句,认为是超出了我们的最大忍耐时间值。


它的主要作用是帮助我们发现那些执行时间特别长的SQL查询,并且有针对性地进行优化,从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候,检查一下慢查询日志,找到那些慢查询,对解决问题有帮助。


默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数。如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。


② 开启慢查询日志

查看是否开启

show variables like '%slow_query_log%'
# 本文这里结果如下
slow_query_log  ON
slow_query_log_file DESKTOP-KIHKQLG-slow.log

slow_query_log_file指的是慢查询日志文件。如果slow_query_log 状态值为OFF,可以使用set GLOBAL slow_query_log = on来开启,如果想永久生效,那么在MySQL的配置文件中进行配置。

[mysqld]
slow_query_log=ON  # 开启慢查询日志
slow_query_log_file=/var/data/mysql-slow.log  # 慢查询日志的文件信息
#如果不指定日志文件,那么系统会默认一个hostnam-slow.log
long_query_time=3 #设置慢查询的阈值为3秒,超过此设定值的SQL即被记录到慢查询日志
log_output=FILE

查看时间阈值

默认值是10秒,可以根据需求自行调整。

show variables like 'long_query_time';
# 临时设置为1 秒,重启失效
set GLOBAL long_query_time= 1
#针对当前会话级别设置
set  long_query_time= 1

查询当前慢查询SQL条数

# 查询当前系统中有多少条慢查询记录
show global status like '%Slow_queries%'

③ 慢查询日志格式

需要注意的是,慢查询日志文件里面不止有Query哦,只要执行时间大于我们设置的阈值都会进入。

如下所示是一个慢查询实例,其load了21W条数据。

# Time: 2022-09-14T05:43:57.174825Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:  2497
# Query_time: 1.697595  Lock_time: 0.000226 Rows_sent: 210001  Rows_examined: 210001
SET timestamp=1663134237;
/* ApplicationName=DBeaver 7.3.0 - SQLEditor <Script-236.sql> */ select * from tb_sys_user tsu limit 210001;

④ min_examined_row_limit


除了上述变量外,控制慢查询日志的还有一个系统变量:min_examined_row_limit 。这个变量的意思是,查询扫描过得最少记录数。这个变量和查询执行时间,共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过得记录数大于等于这个变量的值,并且查询执行时间超过 long_query_time 的值,那么这个查询就被记录到慢查询日志中,反之则不被记录到慢查询日志中。

# 默认值 0
show variables like 'min_examined_row_limit';

这个值默认是0。 与 long_query_time=10 结合在一起,表示只要查询的执行时间超过10秒钟,哪怕一个记录也没有扫描过,都要被记录到慢查询日志中。


⑤ 关闭慢查询日志

永久性关闭


修改my.cnf或者my.ini文件,把 slow_query_log设置为OFF,然后重启MySQL服务。

[mysqld]
slow_query_log=OFF

临时性关闭

set global slow_query_log=off

⑥ 删除慢查询日志

可以通过查到慢查询日志文件位置,手动删除日志文件。

show variables like 'slow_query_log_file'


使用命令 mysqladmin flush-logs 来重新生成慢查询日志文件,执行完毕会在数据目录下重新生成慢查询日志文件。

mysqladmin -uroot -p flush-logs slow

通常慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意,一旦执行了这个命令,慢查询日志都只存在新的日志文件中,如果需要旧的查询日志,就必须事先备份。

⑦ 慢查询日志场景应用

慢查询的优化首先要搞明白慢的原因是什么, 是查询条件没有命中索引?是 load了不需要的数据列?还是数据量太大?所以优化也是针对这三个方向来的。


首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写。


分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引。


如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表。

【2】慢查询日志分析工具mysqldumpslow

mysql提供了日志分析工具mysqldumpslow来帮助我们快速定位问题。

# 查看mysqldumpslow 的帮助信息
[root@VM-24-14-centos ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time


得到返回记录集最多的10个SQL

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

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/data/mysql-slow.log

得到按照时间排序的前10条SQL中包含左连接的语句

mysqldumpslow -s t -t 10 -g "left join" /var/data/mysql-slow.log

【3】全局查询日志

其同样可以帮助我们定位SQL问题,通常不建议在生产环境开启。可以在配置文件my.cnf下进行启用:

# 开启
general_log=1
#记录日志文件的路径
general_log_file=/var/data/mysql_general_log
#输出格式
log_output=FILE

或者临时开启:

set global general_log=1;
set global log_output='TABLE'

此时SQL语句将会记录到MySQL库的mysql.general_log表中。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
34
分享
相关文章
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
MySQL事务日志-Undo Log工作原理分析
【赵渝强老师】Redis的慢查询日志
Redis慢查询日志用于记录执行时间超过预设阈值的命令,帮助开发和运维人员定位性能问题。每条慢查询日志包含标识ID、发生时间戳、命令耗时及详细信息。配置参数包括`slowlog-max-len`(默认128)和`slowlog-log-slower-than`(默认10000微秒)。实战中可通过`slowlog get`获取日志、`slowlog len`查看长度、`slowlog reset`重置日志。建议线上环境将`slowlog-max-len`设为1000以上,并根据并发量调整`slowlog-log-slower-than`。需要注意的是,慢查询只记录命令执行时间。
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
70 18
Mysql并发控制和日志
通过深入理解和应用 MySQL 的并发控制和日志管理技术,您可以显著提升数据库系统的效率和稳定性。
97 10
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
47 3
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
41 7
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
73 5
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理,包括它们如何确保数据的一致性和完整性,以及在事务提交过程中各自的角色。同时,文章还探讨了这些日志在故障恢复中的重要性,强调了合理配置相关参数对于提高系统稳定性的必要性。
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
45 3
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE &#39;log_%&#39;;`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
62 2