如何为mysql查询设置最大执行时间?

I would like to set a maximum execution time for sql queries like set_time_limit() in php. How can I do ?

我想为sql查询设置一个最大执行时间，比如php中的set_time_limit()。我怎么做呢?

6 个解决方案

#1

I thought it has been around a little longer, but according to 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 
MAX_EXECUTION_TIME = 1000 --in milliseconds
* 
FROM table;

Note that this only works for read-only SELECT statements.

注意，这只适用于只读选择语句。

Update: This variable was added in MySQL 5.7.4 and renamed to max_execution_time in MySQL 5.7.8. (source)

更新:该变量在MySQL 5.7.4中添加，并在MySQL 5.7.8中重命名为max_execution_time。(源)

#2

If you're using the mysql native driver (common since php 5.3), and the mysqli extension, you can accomplish this with an asynchronous query:

如果您使用的是mysql本地驱动程序(php 5.3)和mysqli扩展，您可以使用异步查询来实现这一点:

<?php

// Here's an example query that will take a long time to execute.
$sql = "
    select *
    from information_schema.tables t1
    join information_schema.tables t2
    join information_schema.tables t3
    join information_schema.tables t4
    join information_schema.tables t5
    join information_schema.tables t6
    join information_schema.tables t7
    join information_schema.tables t8
";

$mysqli = mysqli_connect('localhost', 'root', '');
$mysqli->query($sql, MYSQLI_ASYNC | MYSQLI_USE_RESULT);
$links = $errors = $reject = [];
$links[] = $mysqli;

// wait up to 1.5 seconds
$seconds = 1;
$microseconds = 500000;

$timeStart = microtime(true);

if (mysqli_poll($links, $errors, $reject, $seconds, $microseconds) > 0) {
    echo "query finished executing. now we start fetching the data rows over the network...\n";
    $result = $mysqli->reap_async_query();
    if ($result) {
        while ($row = $result->fetch_row()) {
            // print_r($row);
            if (microtime(true) - $timeStart > 1.5) {
                // we exceeded our time limit in the middle of fetching our result set.
                echo "timed out while fetching results\n";
                var_dump($mysqli->close());
                break;
            }
        }
    }
} else {
    echo "timed out while waiting for query to execute\n";
    var_dump($mysqli->close());
}

The flags I'm giving to mysqli_query accomplish important things. It tells the client driver to enable asynchronous mode, while forces us to use more verbose code, but lets us use a timeout(and also issue concurrent queries if you want!). The other flag tells the client not to buffer the entire result set into memory.

我给mysqli_query的标志完成了重要的事情。它告诉客户端驱动程序启用异步模式，同时强制我们使用更多详细的代码，但是允许我们使用超时(如果您愿意，还可以发出并发查询!)另一个标志告诉客户端不要将整个结果集缓冲到内存中。

By default, php configures its mysql client libraries to fetch the entire result set of your query into memory before it lets your php code start accessing rows in the result. This can take a long time to transfer a large result. We disable it, otherwise we risk that we might time out while waiting for the buffering to complete.

默认情况下，php会配置它的mysql客户端库，以便在php代码开始访问结果中的行之前将查询的整个结果集提取到内存中。这可能需要很长时间才能传递一个大的结果。我们禁用它，否则我们可能在等待缓冲完成时超时。

Note that there's two places where we need to check for exceeding a time limit:

请注意，有两个地方需要检查是否超过时限:

The actual query execution
实际的查询执行
while fetching the results(data)
同时抓取结果(数据)

You can accomplish similar in the PDO and regular mysql extension. They don't support asynchronous queries, so you can't set a timeout on the query execution time. However, they do support unbuffered result sets, and so you can at least implement a timeout on the fetching of the data.

您可以在PDO和常规的mysql扩展中完成类似的工作。它们不支持异步查询，因此不能设置查询执行时间的超时。但是，它们确实支持未缓冲的结果集，因此您至少可以实现数据获取的超时。

For many queries, mysql is able to start streaming the results to you almost immediately, and so unbuffered queries alone will allow you to somewhat effectively implement timeouts on certain queries. For example, a

对于许多查询，mysql几乎可以立即开始将结果流送给您，因此仅凭无缓冲查询就可以在某种程度上有效地实现某些查询的超时。例如,一个

select * from tbl_with_1billion_rows

can start streaming rows right away, but,

可以立即开始流线，但是，

select sum(foo) from tbl_with_1billion_rows

needs to process the entire table before it can start returning the first row to you. This latter case is where the timeout on an asynchronous query will save you. It will also save you from plain old deadlocks and other stuff.

需要对整个表进行处理，然后才能将第一行返回给您。在后一种情况下，异步查询的超时将挽救您。它还可以将您从普通的死锁和其他东西中拯救出来。

ps - I didn't include any timeout logic on the connection itself.

ps -我没有在连接本身包含任何超时逻辑。

#3

You can find the answer on this other S.O. question:

你可以在这个问题上找到答案。

MySQL - can I limit the maximum time allowed for a query to run?

我能限制查询运行的最大时间吗?

a cron job that runs every second on your database server, connecting and doing something like this:

在数据库服务器上运行每一秒的cron作业，连接并执行如下操作:

SHOW PROCESSLIST

显示PROCESSLIST

Find all connections with a query time larger than your maximum desired time

查找所有连接，查询时间大于最大期望时间

Run KILL [process id] for each of those processes

为每个进程运行KILL[进程id]。

#4

Please rewrite your query like

请重新编写您的查询

select /*+ MAX_EXECUTION_TIME(1000) */ * from table

从表中选择/*+ MAX_EXECUTION_TIME(1000) */ *

#5

pt_kill has an option for such. But it is on-demand, not continually monitoring. It does what @Rafa suggested. However see --sentinel for a hint of how to come close with cron.

pt_kill有这样的选项。但它是按需的，而不是持续的监控。它按照@Rafa的建议做。不过，看看——哨兵，看看如何接近克伦。

#6

-5

I'm not aware of a way of doing this in mysql, but since you're familiar with set_time_limit() in php, why not run the SQL query in a php script to limit the time?

我不知道在mysql中可以这样做，但是既然您熟悉php中的set_time_limit()，为什么不在php脚本中运行SQL查询来限制时间呢?

#1