MySQL - 我可以限制允许查询运行的最长时间吗?

2021-11-20 00:00:00 mysql long-running-processes

我正在寻找一种方法来限制 mysql 服务器上查询的最大运行时间.我认为这可以通过 my.cnf 配置文件完成，但在文档中找不到任何相关内容.有谁知道这是否可以做到?谢谢.

I'm looking for a way to limit the max running time of a query on mysql server. I figured this could be done through the my.cnf configuration file, but couldn't find anything relevant in the docs. Anyone knows if this could be done? thanks.

推荐答案

更新

从 MySQL 5.7 开始，您可以包含 MAX_EXECUTION_TIME 优化器提示在 SELECT 查询中指示服务器在指定时间后终止它.

Update

As of MySQL 5.7, you can include a MAX_EXECUTION_TIME optimizer hint in your SELECT queries to instruct the server to terminate it after the specified time.

据我所知，如果您想强制执行服务器范围的超时，或者您关心除 SELECT 之外的查询，那么原始答案仍然是您唯一的选择.

As far as I know, if you want to enforce a server-wide timeout, or if you care about queries besides SELECTs, the original answer is still your only option.

在向服务器发送查询以运行时，无法指定最长运行时间.

There is no way to specify a maximum run time when sending a query to the server to run.

但是，在您的数据库服务器上每秒运行一次 cron 作业并进行连接并执行以下操作的情况并不少见:

However, it is not uncommon to have a cron job that runs every second on your database server, connecting and doing something like this:

  1. 显示处理程序
  2. 查找查询时间大于所需最大时间的所有连接
  3. 为每个进程运行 KILL [进程 ID]

相关文章