网站首页 > 技术文章正文

如何检查和定位Mysql数据库中存在的慢查询语句?

nanyue 2024-11-19 07:53:43 技术文章 4 ℃

如果在生产环境中监控到Mysql数据库CPU使用率过高的情况，那么很有可能是查询语句中存在慢查询，那么如何定位慢查询呢？

要定位慢查询，就需要开启慢查询日志功能。

（1）首先登录数据库服务器，连接数据库。

mysql -uroot -p

（2）然后查看慢查询日志是否开启，执行命令：show variables like '%slow%'

执行show variables like '%slow%' 这个命令的时候，有可能会报错：performance_schema.session_variables 不存在，无法显示上面的信息。那么就需要执行以下命令检查和升级mysql表，完成后要重启Mysql服务器。

mysql_upgrade -u root -p --force

上表列出了带有slow字样的变量：

log_slow_admin_statements：记录执行缓慢的管理类SQL。比如alter table, analyze table, check table, create index, drop index, optimize table, repair table等。
log_slow_slave_statements：记录从库上执行的慢查询语句。
slow_query_log：慢查询日志开关，ON为开启，OFF为关闭。
slow_query_log_file：慢查询日志文件路径，可通过日志查看慢sql语句及执行时间。
slow_lauch_time：这个值经常与慢查询阈值混淆，实际上和慢查询没有什么关系，而是和创建线程的耗时有关系。这里就不展开说了。与慢查询有关系的变量是long_query_time。
long_query_time：Sql查询的执行时间阈值，也就是慢查询阈值。大于该值的查询为慢查询，将被记录到日志中。这个值默认的是10s，可以根据需求进行配置。Mysql 5.5以上版本可以追踪到微秒。

其他与慢查询日志有关系的变量还包括：

log_queries_not_using_indexes：如果设置了该变量，那么未使用索引的查询也被记录到慢查询日志中。这就是为什么有时会在慢查询日志中看到执行时间低于慢查询阈值的sql语句。
log_output：日志存储方式。默认值是'FILE'，表示将日志存入文件。'TABLE'表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。日志写入到数据库专用日志表中，要比写入到文件耗费更多的系统资源，因此建议优先记录到文件。

（3）通过日志查看慢查询的sql语句，看sql语句的执行计划，看是否缺少索引，是否可以进行优化。

执行select sleep(11)语句, 即可在慢查询日志文件上看到刚刚执行的sql。

默认情况下slow_query_log 的值为OFF，表示慢查询日志是禁用的，可以通过设置 slow_query_log 的值来开启。

set global slow_query_log=1

这种方法只对当前数据库生效，如果MySQL重启后，这个设置则会失效。如果需要设置永久生效，就必须修改配置文件my.cnf。

用肉眼来分析慢查询日志，这个工作量可能不太有利于身心健康。所以有专门的分析工具来分析慢查询日志，比如mysqldumpslow。

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

-s 表示以何种方式来进行排序，t 表示查询时间。c 表示访问计数。l 表示锁定时间。r 表示返回记录。al 表示平均锁定时间。ar 表示平均返回记录数。at 表示平均查询时间。

-t 是top n的意思，即为返回前面多少条的数据。

以上命令的意思就是从慢查询日志中找出查询时间最长的10条sql语句。

随着时间积累，日志文件会越来越大，可能会大到mysqldumpslow 也处理不了。最好是通过定时任务crontab，按天切割归纳慢查询日志，方便定位和分析。

线上问题的解决：当发现生产环境cpu占用率过高，通常的做法是通过执行命令show processlist，先找出执行时间过长的线程，再把它们给kill掉。

当然这只是暂时性的做法，要想尽量避免出现线上问题，最好采取以下措施：

我会持续更新关于物联网、云原生以及数字科技方面的文章，用简单的语言描述复杂的技术，也会偶尔发表一下对IT产业的看法，请大家多多关注，欢迎留言和转发，希望与大家互动交流，谢谢。