MySQL 如何设置mysql查询的最长执行时间？

How to set a maximum execution time for a mysql query?

提问by

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

我想为 php 中的 set_time_limit() 等 sql 查询设置最大执行时间。我能怎么做 ？

回答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_EXECUTION_TIME = 1000 */ --in milliseconds
* 
FROM table;

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

请注意，这仅适用于只读 SELECT 语句。

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

更新：这个变量是在 MySQL 5.7.4 中添加的，并max_execution_time在 MySQL 5.7.8 中重命名为。（来源）

回答by goat

If you're using the mysql native driver(common since php 5.3), and the mysqliextension, 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_queryaccomplish 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 - 我没有在连接本身上包含任何超时逻辑。

回答by Manu Mohan Thekkedath

Please rewrite your query like

请重写您的查询，如

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

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

回答by Rafa

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

您可以在另一个 SO 问题上找到答案：

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

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

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

• SHOW PROCESSLIST
• Find all connections with a query time larger than your maximum desired time
• Run KILL [process id] for each of those processes

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

• 节目编排员
• 查找查询时间大于所需最大时间的所有连接
• 为每个进程运行 KILL [process id]

回答by Rick James

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

pt_kill有这样的选择。但它是按需的，而不是持续监控。它按照@Rafa 的建议进行操作。但是，请参阅--sentinel有关如何接近cron.