MySQL - 我可以限制允许查询运行的最长时间吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4794747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - can I limit the maximum time allowed for a query to run?
提问by sa125
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.cnfconfiguration file, but couldn't find anything relevant in the docs. Anyone knows if this could be done? thanks.
我正在寻找一种方法来限制 mysql 服务器上查询的最大运行时间。我认为这可以通过my.cnf配置文件完成，但在文档中找不到任何相关内容。有谁知道这是否可以做到？谢谢。
采纳答案by TehShrike
There is no way to specify a maximum run time when sending a query to the server to run.
在向服务器发送查询以运行时，无法指定最长运行时间。
However, it is not uncommon to have a cron job that runs every second on your database server, connecting and doing something like this:
但是，在数据库服务器上每秒运行一次 cron 作业并进行连接并执行以下操作的情况并不少见：
- SHOW PROCESSLIST
- Find all connections with a query time larger than your maximum desired time
- Run KILL [process id] for each of those processes
- 节目编排员
- 查找查询时间大于所需最大时间的所有连接
- 为每个进程运行 KILL [process id]
回答by Alexander Kosolapov
You could use a query as follows:
您可以按如下方式使用查询：
SELECT MAX_STATEMENT_TIME=1000 * FROM table;
UPDATE:
You should use max_execution_timeinstead.
更新：您应该使用max_execution_time代替。
SELECT /*+ MAX_EXECUTION_TIME(1000)*/ * FROM table;
MAX_STATEMENT_TIME was renamed to max_execution_time in MySQL 5.7.8. http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time
MAX_STATEMENT_TIME 在 MySQL 5.7.8 中被重命名为 max_execution_time。 http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time
回答by faker
In the meantime the Twitter team released their changes to MySQL which implements this:
与此同时，Twitter 团队发布了他们对 MySQL 的更改，它实现了这一点：
- Reduce unnecessary work through improved server-side statement timeout support. This allows the server to proactively cancel queries that run longer than a millisecond-granularity timeout.
- Reduce unnecessary work through improved server-side statement timeout support. This allows the server to proactively cancel queries that run longer than a millisecond-granularity timeout.
See http://engineering.twitter.com/2012/04/mysql-at-twitter.htmland https://github.com/twitter/mysql/wiki/Statement-Timeout
请参阅http://engineering.twitter.com/2012/04/mysql-at-twitter.html和https://github.com/twitter/mysql/wiki/Statement-Timeout
回答by Grenville Tryon
http://mysqlserverteam.com/server-side-select-statement-timeouts/
http://mysqlserverteam.com/server-side-select-statement-timeouts/
Interesting upgrade. I will check it:
有趣的升级。我会查一下：
"MySQL 5.7.4 introduces the ability to set server side execution time limits, specified in milliseconds, for top level read-only SELECT statements".
“MySQL 5.7.4 引入了为顶级只读 SELECT 语句设置服务器端执行时间限制的能力，以毫秒为单位指定”。
SET GLOBAL MAX_STATEMENT_TIME=1000;
SET SESSION MAX_STATEMENT_TIME=2000;
SELECT MAX_STATEMENT_TIME=1000 * FROM table;
