如何限制SQL执行时间 [英] How to limit the sql execution time

问题描述

某些sql编写得不好.有时，搜索需要花费数小时的申请时间. 当一个应用程序(可能是一个网站)提交运行时间很长的查询时，我必须重新启动mysql. 如何在数据库端限制sql查询的执行时间?

Some sql is not well written. Sometimes a search costs hours in applications. When a application(maybe a website) submit a query which run long time, I have to restart the mysql. How can I limit a sql query's execution time in the database side?

推荐答案

要在较长的执行时间后自动终止MySQL中的查询:

To auto kill a query in MySQL after a long execution time:

1. 创建存储过程为:

1. Create a stored procedure as:

DECLARE CURSOR cur1 FOR SELECT ID 
                    FROM INFORMATION_SCHEMA.PROCESSLIST 
                    WHERE COMMAND = 'Query' AND TIME > 120;

然后在curosr的循环中执行以下操作:

then inside curosr's loop do:

FETCH ID INTO @var_kill_id;
KILL QUERY @var_kill_id;

创建EVENT FOR EVERY 5 SECONDS并仅在其中执行上述步骤的CALL.

Create EVENT FOR EVERY 5 SECONDS and just CALL the above procedure inside it.

注意:KILL QUERY只是杀死查询，并且MySQL连接没有中断.参见此处.

Note: KILL QUERY just kills the query and MySQL connection is not broken. see here.

这篇关于如何限制SQL执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！