长时间运行的查询可能会严重影响 MySQL 数据库性能，导致从响应时间缓慢到影响每个用户的全面瓶颈等各种问题。处理这些烦人的查询——了解它们是什么、为什么发生以及如何管理它们——是保持数据库平稳运行的关键。

无论是尽早发现它们、阻止它们，还是设置自动处理它们的方法，本指南都能满足您的要求。

什么是长时间运行的查询？

MySQL 中的长时间运行查询是指执行时间异常长的查询。
将查询归类为“长时间运行”的具体持续时间可能会有所不同，具体取决于应用程序的性能标准。一般来说，如果查询运行时间比平常长并且开始减慢数据库速度，则它被视为长时间运行。

长时间运行查询的根本原因

长时间运行查询的原因可能多种多样：

• 缺乏适当的索引 – 如果没有适当的索引，MySQL 必须扫描整个表才能检索所需的数据。此过程效率非常低，特别是对于大型表，因为它消耗大量时间和资源。

• 重负载情况 – 当服务器处理大量查询或同时处理一些复杂的查询时，可用资源（如 CPU 和内存）就会紧张。这种资源竞争可能会延迟查询的执行，从而导致运行时间更长，尤其是在高峰使用期间。

• 锁争用 – 当多个事务需要同时访问相同数据但由于其他操作持有必要的锁而被阻止时，就会发生这种情况。例如，如果一个事务正在更新一行，则想要读取或更新同一行的另一个事务将必须等待第一个事务完成并释放锁。

• 不正确的规范化 – 虽然规范化有助于避免数据冗余并提高数据完整性，但过度规范化的数据库可能会导致涉及多个联接的复杂查询。这些会降低性能。另一方面，标准化不足可能会导致过多的数据重复，从而导致表更大和查询速度更慢。

• 大型联接 – 涉及联接大型表的查询，尤其是没有适当索引的查询可能会很慢。数据库必须根据连接条件匹配表中的行，如果没有有效的索引，这个过程可能会占用大量资源并且速度很慢。

识别长时间运行的查询

为了有效管理长时间运行的查询，您首先需要识别它们。这里有几个方法：

1.使用 SHOW PROCESSLIST

显示进程列表;命令是获取服务器上运行的所有活动查询快照的快速方法。此命令显示每个查询以及几条关键信息，包括每个查询运行的时间。那些具有较高“时间”值的查询可能是您长时间运行的查询。以下是如何使用此命令：

显示完整流程列表；

此命令将列出所有当前进程，显示谁启动了它们，它们正在运行什么类型的命令，以及最重要的是，它们已经运行了多长时间。如果您发现任何查询运行时间异常长，那么这些查询就是长时间运行的查询。然后，您可以决定是否深入优化它们，或者如果它们拖累了系统性能，则干脆将它们杀死。

2. 慢查询日志

设置慢查询日志是捕获这些有问题的查询的另一个好策略。这个方便的 MySQL 功能会记录执行时间超过特定阈值的任何查询。它不仅仅是捕获长时间运行的查询 - 它还可以帮助您识别未有效使用索引的查询。

要启动并运行慢查询日志，您需要调整 MySQL 配置文件（my.cnf 或 my.ini）中的一些设置：

• Slow_query_log – 将其设置为 1 以启用日志。
• Slow_query_log_file – 指定要保存日志的文件路径。
• Long_query_time – 设置记录查询的最短执行时间（以秒为单位）。 例如，将其设置为“2”将记录任何执行时间超过两秒的查询。

3. 性能架构

MySQL 的性能模式对于更详细的调查非常有价值。该工具旨在监控服务器事件并跟踪性能指标，让您更清晰地了解查询执行和整体系统性能。

通过添加以下行确保在 MySQL 配置中启用它：

[mysqld]
性能模式 = 开

激活后，您可以探索各种性能架构表来分析查询的性能。例如，如果您要精确定位长时间运行的查询，您可能需要查看 events_statements_history_long 表。查询方法如下：

SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS '持续时间（秒）'
FROM Performance_schema.events_statements_history_long
WHERE TIMER_WAIT > 10000000000;

此查询可帮助您查找任何运行时间超过 10 秒的查询。它为您提供 SQL 文本以及每个查询运行时间等详细信息。

手动终止长时间运行的查询

当您发现某个查询花费的时间过长并导致系统资源紧张时，您可以选择手动终止它。这是使用 KILL 命令后跟查询的特定进程 ID 来完成的。

您可以通过运行SHOW PROCESSLIST命令找到进程ID，该命令显示所有当前正在运行的进程及其各自的ID。查看列表中是否有任何显示高“时间”值的查询，该值表示它们已经运行了多长时间。

一旦您确定了有问题的查询并记下其进程 ID，您就可以使用 KILL 命令终止它：

KILL [进程 ID];

将 [进程 ID] 替换为 SHOW PROCESSLIST 输出中的实际编号。

这种方法要小心。突然停止查询有时会导致问题，例如，如果查询正在写入或更新信息，则数据会处于不一致的状态。

如何自动终止长时间运行的查询

设置自动化来处理长时间运行的查询可以成为真正的救星，防止那些缓慢或未优化的查询占用数据库资源并减慢甚至锁定整个系统。但请谨慎行事 - 在没有进行正确检查的情况下使用此工具实际上可能会隐藏需要您注意的更深层次的性能问题。

始终确保您有全面的日志记录和监控，以分析被终止的查询对应用程序的影响，并考虑改进这些查询，而不是仅仅自动终止它们。将自动终止视为优化性能的更大策略的一部分，而不是作为解决所有问题的解决方案。

1. 启用事件调度程序

首先，您需要启用MySQL事件调度程序，默认情况下该功能是禁用的。事件计划程序允许您创建和计划您希望服务器在预定义时间自动执行的任务。运行以下命令：

设置全局 event_scheduler = ON;

2. 创建一个事件来终止长时间运行的查询

启用调度程序后，下一步是定义将监视和终止长时间运行的查询的实际事件。该事件将每分钟运行一次，以检查运行时间超过指定阈值（例如 60 秒）的查询。一旦识别，它将自动终止这些查询。以下是设置此事件的 SQL 代码的细分：

`创建事件kill_long_running_queries
ON SCHEDULE EVERY 1 MINUTE -- 指定事件运行的频率
做
开始
声明完成 INT DEFAULT FALSE;
声明 proc_id INT； -- 存储每个查询的进程 ID 的变量
声明 cur1 游标用于从 information_schema.processlist
中选择 ID WHERE 命令 = '查询' AND 时间 > 60； -- 将“60”更改为以秒为单位的阈值
声明未找到集的继续处理程序完成 = TRUE;

打开 cur1;

read_loop：循环
FETCH cur1 INTO proc_id;
如果完成则
离开read_loop;
结束如果;
杀死 proc_id； -- 杀死由 proc_id
标识的进程 结束循环；

关闭 cur1;
结束;`

3. 在 MySQL 中设置最大查询执行时间

控制查询的最大执行时间有助于防止数据库被运行时间过长的查询占用。这是使用 MySQL 5.7.8 及更高版本中的 max_execution_time 系统变量通过为所有只读 SELECT 查询设置系统范围的执行时间限制来完成的：

设置全局 max_execution_time = 2000;

这将限制设置为 2000 毫秒（2 秒）

请记住，此设置不适用于存储过程、函数或触发器，并且会在服务器重新启动时重置为默认值，除非添加到您的 MySQL 配置文件中：

[mysqld]
最大执行时间 = 2000

4. 在 MariaDB 中设置最大语句时间

MariaDB 虽然是从 MySQL 派生出来的，但它提供了一种类似但不同的方法来管理查询执行时间。从 MariaDB 10.1.1 开始，您可以使用 max_statement_time 系统变量来实现此目的：

设置全局 max_statement_time = 2;

这将所有查询的执行时间限制为 2 秒。

要通过服务器重新启动进行持久配置，请将此行添加到您的 MariaDB 配置文件中：

[mysqld]
最大语句时间 = 2

使用 Releem 的查询分析来识别和修复长时间运行的查询

Releem 的查询分析工具彻底改变了您监控和优化数据库性能的方式。它会自动收集前 100 个查询的详细信息，提供关键指标，例如平均执行时间以及每个查询对数据库运行效率的总体影响。

使用 Releem，无需手动挖掘 PROCESSLIST 输出或查看慢速查询日志来识别性能不佳的查询。该工具具有直观的仪表板，可让您轻松排序和发现滞后或消耗过多时间的查询。这种即时洞察力可以帮助您立即识别并解决瓶颈。