如果时间太长，如何停止 MySQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2137084/
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
How can I stop a MySQL query if it takes too long?
提问by acheruns
Is it possible to timeout a query in MySQL?
是否可以在 MySQL 中超时查询？
That is, if any query exceeds the time I specify, it will be killed by MySQL and it will return an error instead of waiting for eternity.
也就是说，如果任何查询超过了我指定的时间，它将被 MySQL 杀死并返回一个错误而不是永远等待。
回答by Erik
There is a nice Perl script on CPAN to do just this: http://search.cpan.org/~rsoliv/mysql-genocide-0.03/mysql-genocide
CPAN 上有一个很好的 Perl 脚本可以做到这一点：http: //search.cpan.org/~rsoliv/mysql-genocide-0.03/mysql-genocide
One only needs to schedule it to run with the proper parameters. Create a CRONtab file /etc/cron.d/mysql_query_timeoutto schedule it to run every minute:
人们只需要安排它以正确的参数运行。创建一个 CRONtab 文件/etc/cron.d/mysql_query_timeout来安排它每分钟运行一次：
* * * * * root /path/to/mysql-genocide -t 7200 -s -K
* * * * * root /path/to/mysql-genocide -t 7200 -s -K
Where 7200 is the maxiumum allowed execution time in seconds. The -s switch filters out all except SELECT queries. The -K switch instructs the script to kill the matching processes.
其中 7200 是允许的最大执行时间（以秒为单位）。-s 开关过滤掉除 SELECT 查询之外的所有查询。-K 开关指示脚本终止匹配的进程。
The root user should be able to run local mysql tools without authentication otherwise you will need to provide credentials on the command line.
root 用户应该能够在没有身份验证的情况下运行本地 mysql 工具，否则您将需要在命令行上提供凭据。
回答by aroth
I just set up the following bash script as a cron job to accomplish this with MySQL 5.0 (kills any query that has been executing for more than 30 seconds). Sharing it here in case it proves useful to anyone (apologies if my bash scripting style is inefficient or atrocious, it is not my primary development language):
我只是将以下 bash 脚本设置为使用 MySQL 5.0 完成此任务的 cron 作业（终止任何已执行超过 30 秒的查询）。在这里分享它，以防它对任何人有用（如果我的 bash 脚本风格低效或残暴，我深表歉意，它不是我的主要开发语言）：
#!/bin/bash
linecount=0
processes=$(echo "show processlist" | mysql -uroot -ppassword)
oldIfs=$IFS
IFS='
'
echo "Checking for slow MySQL queries..."
for line in $processes
do
if [ "$linecount" -gt 0 ]
then
pid=$(echo "$line" | cut -f1)
length=$(echo "$line" | cut -f6)
query=$(echo "$line" | cut -f8)
#Id User Host db Command Time State Info
if [ "$length" -gt 30 ]
then
#echo "$pid = $length"
echo "WARNING: Killing query with pid=$pid with total execution time of $length seconds! (query=$query)"
killoutput=$(echo "kill query $pid" | mysql -uroot -ppassword)
echo "Result of killing $pid: $killoutput"
fi
fi
linecount=`expr $linecount + 1`
done
IFS=$oldIfs
回答by Westy92
I thought it has been around a little longer, but according to this,
我以为它已经存在了一段时间，但根据this，
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 语句设置服务器端执行时间限制的功能，以毫秒为单位指定。
SELECT
MAX_STATEMENT_TIME = 1000 --in milliseconds
*
FROM table;
Note that this only works for read-only SELECT statements.
请注意，这仅适用于只读 SELECT 语句。
回答by Matthew Montgomery
Starting with MySQL 5.1 you can create a stored procedure to query the information_schmea.PROCESSLIST table for all queries that match your criteria for "long running" then iterate over a cursor to kill them. Then setup that procedure to execute on a recurring basis in the event scheduler.
从 MySQL 5.1 开始，您可以创建一个存储过程来查询 information_schmea.PROCESSLIST 表以查找与您的“长时间运行”条件匹配的所有查询，然后遍历游标以终止它们。然后将该过程设置为在事件调度程序中重复执行。
回答by David
The MySQL forum has some threads about this.
MySQL 论坛有一些关于此的主题。
This postdetails how to set up timeouts on the server using innodb_lock_wait_timeout.
这篇文章详细介绍了如何使用 innodb_lock_wait_timeout 在服务器上设置超时。
Here's a way to do it programmatically, assuming you're using JDBC.
这是一种以编程方式执行的方法，假设您使用的是 JDBC。
回答by fred
I don't think the egrep above would find "2000".
Why not try just selecting the id as well, and avoiding all of that posh shell stuff:
我认为上面的 egrep 不会找到“2000”。
为什么不尝试只选择 id，并避免所有那些华丽的 shell 东西：
mysql -e 'select id from information_schema.processlist where info is not null and time > 30;'
回答by Ali Hashemi
I think this old question needs an updated answer.
我认为这个老问题需要一个更新的答案。
You can set a GLOBALtimeout for all your read-only SELECTqueries like this:
您可以GLOBAL为所有只读SELECT查询设置超时，如下所示：
SET GLOBAL MAX_EXECUTION_TIME=1000;
SET GLOBAL MAX_EXECUTION_TIME=1000;
The time specified is in milliseconds.
指定的时间以毫秒为单位。
If you want the timeout only for a specific query, you can set it inline like this:
如果您只想要特定查询的超时，您可以像这样设置内联：
SELECT /*+ MAX_EXECUTION_TIME(1000) */ my_column FROM my_table WHERE ...
SELECT /*+ MAX_EXECUTION_TIME(1000) */ my_column FROM my_table WHERE ...
MySQL returns an error instead of waiting for eternity.
MySQL 返回错误而不是等待永恒。
Note that this method only works for read-only SELECTs. If a SELECTstatement is determined not to be read-only, then any timer set for it is cancelled and the following NOTE message is reported to the user:
请注意，此方法仅适用于只读SELECTs。如果SELECT确定语句不是只读的，则取消为其设置的任何计时器，并向用户报告以下 NOTE 消息：
Note 1908 Select is not a read only statement, disabling timer
Note 1908 Select is not a read only statement, disabling timer
For statements with subqueries, it limits the top SELECTonly. It does not apply to SELECTstatements within stored programs. Using the MAX_EXECUTION_TIMEhint in SELECTstatements within a stored program will be ignored.
对于带有子查询的语句，它只限制顶部SELECT。它不适用于SELECT存储程序中的语句。在存储程序MAX_EXECUTION_TIME中的SELECT语句中使用提示将被忽略。
回答by Alexander Ushakov
Since MySQL 5.7.8 there is max_execution_timeoption that defines the execution timeout for SELECT statements.
从 MySQL 5.7.8 开始，max_execution_time选项定义了 SELECT 语句的执行超时。
回答by Srinivas Mutyala
Here is my script :
这是我的脚本：
mysql -e 'show processlist\G' |\
egrep -b5 'Time: [6-9]{3,10}' |\
grep 'Id:' |\
cut -d':' -f2 |\
grep -v '155' |\ ## Binary Log PID
sed 's/^ //' |\
while read id
do
mysql -e "kill $id;"
done
