mysql进阶-查询优化-慢查询日志

文章目录

一、什么是慢查询日志

日志对一个程序来说非常重要，他能帮助程序员在系统出现问题时快速定位问题，慢查询日志（

slow query log

）是MySQL自带的几种日志文件中非常重要的一种日志(另还有错误日志、查询日志、二进制日志)。MySQL的慢查询日志是用于记录执行时间超过指定阈值的SQL查询语句的一种机制。这个阈值由MySQL的参数

long_query_time

指定，**默认值为

10秒

**。当一个查询的执行时间超过这个阈值时，MySQL就会将这个查询的详细信息记录到慢查询日志中，以供进一步分析和优化使用。慢查询日志可以帮助开发人员和管理员发现查询缓慢或性能低下的SQL查询，以及找出相关的问题和瓶颈。下面将详细介绍MySQL慢查询日志的作用、用途和使用方法。

二、慢查询日志能干什么

2.1 性能分析和优化

慢查询日志可以帮助开发人员和管理员找出执行缓慢或性能低下的SQL查询，从而进行进一步的优化和调整。通过读取日志文件，可以获取SQL查询的具体执行时间和执行次数，以及相应的服务器资源占用情况和响应时间等指标，从而确定查询语句的瓶颈和性能问题所在，并根据实际执行计划进行优化，以提高查询性能和执行效率。

2.2 诊断和排查问题

慢查询日志可以用于诊断和排查数据库中的各种问题和异常。例如，可以通过查询日志文件，找出导致数据库响应时间延长的查询语句、语法错误、索引缺失等问题，并针对性地进行解决和优化，从而提高数据库的可用性和稳定性。

2.3 数据分析和探索

慢查询日志可以作为数据库性能的一种指标进行分析和探索。通过对日志文件的分析，可以研究数据库操作的历史记录、访问模式、查询分布等重要特征，从而深入了解数据库的运行状况和性能瓶颈，帮助开发人员和管理员进行针对性的优化和调整，以提升数据库的性能和可靠性。

三、慢查询日志实战

3.1 永久开启开启慢查询日志

要开启MySQL慢查询日志，需要修改MySQL服务器的配置文件my.cnf（my.ini），
mysql的配置文件路径：

• linux路径

/etc/mysql/my.cnf

• windows路径 5.7 是版本号

C:\ProgramData\MySQL\MySQL Server 5.7/my.ini

也可以通过命令行的形式进行查询目录：

show variables like'%slow_query_log%';

并添加如下配置信息, 如果路径不存在一定要手动创建，否者可能会因为权限不够，无法记录慢查询日志。

# 开启慢查询日志
slow_query_log =1# 指定慢查询的阈值，默认为10秒
long_query_time =10# 指定慢查询日志的保存位置
slow_query_log_file =/var/log/mysql/mysql-slow.log

其中，slow_query_log为开启慢查询日志的开关，1为开启，0为关闭。long_query_time为慢查询的阈值时间，单位为秒，默认情况下为10秒。最后，slow_query_log_file为慢查询日志文件的保存位置，也可以指定文件名。

完成配置后，需要重启MySQL服务器才能使配置生效。

3.2 临时开启慢查询日志

我们也可以在命令行，或者Navicat 中执行如下命令开启慢查询日志：

SETGLOBAL slow_query_log =1;SETGLOBAL slow_query_log_file ='/mysql-slow.log';SETGLOBAL long_query_time =10;SETGLOBAL log_output ='FILE';

• 优点：不需要重启mysql，不区分系统，简单方便
• 缺点：重启mysql配置就失效了。

3.4 常用命令

# 查看慢查询日志的完整路径和文件名show variables like'slow_query_log_file';# 查看当前MySQL服务器的慢查询日志是否开启show variables like'slow_query_log';# 查询慢查询的阈值show variables like'%long_query_time%';# 查询慢查询日志输出方式show variables like'%log_output%';

四、如何分析慢查询日志

mysqldumpslow 工具是慢查询自带的分析慢查询工具，一般只要安装了mysql，就会有该工具。
完成语法如下：

Usage: mysqldumpslow [ OPTS...][ LOGS...]-- 后跟参数以及log文件的绝对地址;-s            what to sort by(al, at, ar, c, l, r, t),'at'isdefault
                al: average locktime
                ar: average rows sent
                at: average query time
                c: count
                l: locktime
                r: rows sent
                t: query time-r           reverse the sort order(largest last instead offirst)-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 locktimefrom total time

mysqldumpslow 命令 常见的分析和优化查询日志文件语句如下：

取出使用最多的10条慢查询

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

取出查询时间最慢的3条慢查询

mysqldumpslow -s t -t 3/var/log/mysql/mysql-slow.log  

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10-g “leftjoin” /var/log/mysql/mysql-slow.log 

按照扫描行数最多的前10条语句

 mysqldumpslow -s r -t 10-g 'left join'/var/log/mysql/mysql-slow.log  

按查询总时间排序，显示前10条查询信息

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

查询次数排序，显示前20条查询信息

mysqldumpslow -s c -t 20/var/log/mysql/mysql-slow.log 

其中，-s t 表示按查询总时间排序，-s c表示按查询次数排序，-t 10表示显示前10条查询信息。

widows下使用mysqldumpslow 可以参考：Windows下安装使用mysqldumpslow

五、优化慢查询语句

通过分析和统计MySQL慢查询日志文件，可以找出不同查询语句的执行时间和执行次数等指标，以便进一步优化和调整查询语句的性能和执行效率。优化的方法通常包括以下几个方面：

（1）添加或修改表索引，以提高查询速度。

（2）优化SQL查询语句，使用优化的SQL语句可以使查询性能得到提升。

（3）调整MySQL服务器参数，如增加缓存区大小、减少线程数等，以达到更好的性能优化效果。

（4）升级数据库版本，选择更高效的数据库版本可以对查询性能产生显著影响。

五、总结

MySQL慢查询日志是MySQL数据库优化和性能调整的重要工具之一，通过记录和分析执行缓慢的SQL查询语句，可以找出查询瓶颈和性能问题所在，并针对性地进行优化和调整。尽管慢查询日志会产生一定的系统负担和日志存储量，但是它对于开发人员和管理员来说确实是一种非常有价值的工具，可以大幅提高MySQL性能的稳定性和可靠性。