如何排查MySQL查询慢的问题

如何排查MySQL查询慢的问题

在数据库的日常维护中，我们经常会遇到查询性能问题，特别是当数据库的规模逐渐增大，查询变慢的情况会越来越多。为了排查和优化这些慢查询，我们需要一些工具和方法来帮助我们定位问题。本文将介绍几种常用的方法来排查MySQL查询慢的问题。

1. 使用SHOW PROCESSLIST

SHOW PROCESSLIST是一个基本的命令，它可以显示当前MySQL服务器上所有线程的状态。通过这个命令，我们可以查看哪些查询正在执行以及它们已经运行了多长时间。

SHOW PROCESSLIST;

这个命令会列出所有当前运行的进程，包括每个进程的ID、用户、主机、数据库、执行的命令、执行时间和状态等信息。如果发现某个查询的Time字段值很大，那么可能就是一个慢查询。

2. 开启通用查询日志

通用查询日志可以记录MySQL服务器执行的所有SQL语句。我们可以通过设置general_log参数为ON来开启它，并选择将日志输出到table或file。

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'table'; -- 或者 'file'

开启后，我们可以通过查询mysql.general_log表来查看执行的SQL语句：

SELECT * FROM mysql.general_log WHERE command_type='Execute' ORDER BY event_time DESC;

这将帮助我们找到最近执行的SQL语句，特别是那些执行时间较长的。

3. 开启慢查询日志

慢查询日志是专门用来记录执行时间超过特定阈值的查询。我们可以通过以下命令来开启慢查询日志：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设置为2秒

这样，所有执行时间超过2秒的查询都会被记录到慢查询日志中。我们可以通过查看这个日志来分析慢查询的原因。

4. 使用performance_schema

在MySQL 5.6及以上版本中，performance_schema提供了更详细的性能监控数据。特别是events_statements_history表，它记录了SQL语句的执行历史。

SELECT * FROM performance_schema.events_statements_history ORDER BY TIMER_END DESC;

这个表包含了每个语句的执行时间、返回的行数、扫描的行数等信息，是分析慢查询的有力工具。

5. 分析和优化

在收集到慢查询的信息后，我们需要对这些查询进行分析和优化。常见的优化方法包括：

• 添加索引：为查询中的过滤列添加索引可以显著提高查询效率。
• 优化查询语句：避免使用SELECT *，尽量指定需要的列；减少子查询和复杂的JOIN操作。
• 调整配置：根据实际情况调整MySQL的配置参数，如缓冲区大小、连接数等。
• 使用查询缓存：如果查询经常被执行且结果集不经常变化，可以考虑使用查询缓存。

6. 使用第三方工具

除了MySQL自带的工具外，还有很多第三方工具可以帮助我们分析和优化查询性能，如Percona Toolkit、MySQL Workbench等。这些工具提供了更直观的界面和更丰富的功能。

总结

数据库的优化,无非就是找SQL,找哪个SQL跑的慢,然后再优化SQL,优化索引,分区,分表,主要用到排查SQL如下:

#快看,看进程情况
show processlist ;
#慢慢分析,记录一下SQL,事件,筛查慢SQL
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'table'; -- 或者 'file'
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设置为2秒
SELECT event_time,argument FROM mysql.general_log where general_log.command_type='Execute' ORDER BY event_time DESC; -- 查最近执行SQL有哪些
SELECT digest_text,TIMER_WAIT,(TIMER_END-TIMER_START) TIMER_USE FROM performance_schema.events_statements_history
                                                                ORDER BY TIMER_END DESC;
# 查完要记得关掉是,否则性能会有影响
SET GLOBAL slow_query_log = 'OFF';
SET GLOBAL general_log = 'OFF';