PHP如何限制mysql SQL语句的执行时间

前言

当PHP查询mysql的时候，如果SQL语句的性能很低（例如全表扫描），或者mysql服务器负载很高，那么这个SQL语句可能要好几十分钟甚至更久才能查询完毕，PHP进程同样也会被这个查询语句阻塞很久，直至SQL执行完毕才能处理下一个请求，对于web请求来说这是不能忍受的，那么如何限制SQL语句的最大执行时间呢

确认驱动

PHP里可以使用pdo_mysql或mysqli扩展连接数据库（后续代码均已mysqli为示例），这两个扩展底层都是使用libmysql（又称MySQL Client Library）或mysqlnd驱动，首先要确认自己的扩展是使用哪个驱动的。最简单的方式就是通过phpinfo来查看：


上面是使用mysqlnd驱动的mysqli和pdo_mysql

注：从5.4版本开始，PHP就开始使用mysqlnd作为默认的驱动，不再建议使用libmysql

libmysql驱动

如果使用的是libmysql驱动，可以通过MYSQL_OPT_READ_TIMEOUT选项来设置超时时间，代码示例：

/* mysqli扩展没有将MYSQL_OPT_READ_TIMEOUT暴露出来，需要自行定义 */
if (!defined('MYSQL_OPT_READ_TIMEOUT')) {
    define ('MYSQL_OPT_READ_TIMEOUT', 11);
}
$conn = mysqli_init();
$conn->options(MYSQL_OPT_READ_TIMEOUT, 1); // 超时时间设置为1秒
$conn->real_connect('server', 'user', 'pass', 'database');

不过，因为在libmysql中有重试机制（尝试一次，重试两次），所以最终我们设置的超时阈值都会三倍于我们设置的值。
也就是说，如果我们设置了MYSQL_OPT_READ_TIMEOUT为1，最终会在3s以后超时结束，也就是说，我们目前能设置的最短超时时间就是3秒。

mysqlnd驱动

如果使用的是mysqlnd驱动，就不能像libmysql那样简单的在代码里使用MYSQL_OPT_READ_TIMEOUT选项来设置读写超时了，即使设置了也不会生效。只能通过修改php.ini文件来实现，具体是在php.ini里增加一个net_read_timeout的配置选项，如下：

需要注意的是，这个配置选项是个全局配置，pdo、mysqli扩展都会受到它的影响，使用这个配置文件的其它业务同样会受到波及。

注：PHP 5.3（含） ~ PHP 7.1（含） 只能通过修改php.ini文件来调整 net_read_timeout 的值，PHP 7.2（含）之后的版本可以在代码里通过 ini_set 函数来调整，例如：ini_set(‘mysqlnd.net_read_timeout’, 1)

如果希望不同的业务可以有不同的超时设置，还有一种稍复杂的方法，就是使用mysqli的异步查询功能，代码如下：

$conn = new mysqli();
$conn->connect('localhost', 'user', 'password', 'db_name', 3306);
$slowSql = "SELECT SLEEP(10)"; // 慢查询SQL
$conn->query($slowSql, MYSQLI_ASYNC); // 传入MYSQLI_ASYNC参数，使查询变为异步查询

$sqlResult = null;
$readTimeout = 3; // 超时时间，单位秒
$startTime = time();
do {
    if (time() - $startTime > $readTimeout) {
        // 超时了
        $sqlResult = false;
        continue;
    }
    $links[] = $errors[] = $reject[] = $conn;
    if (!mysqli_poll($links, $errors, $reject, 0, 500000)) {
        // SQL还没有执行完，继续循环
        continue;
    }

    $result = @$conn->reap_async_query();
    if (!$result) {
        // SQL执行发生错误了
        $sqlResult = false;
        continue;
    }

    if (is_object($result)) {
        $sqlResult = $result->fetch_all(MYSQLI_ASSOC);
        $result->free_result();
    } else {
        $sqlResult = $conn->affected_rows;
    }
} while ($sqlResult === null);

// 打印SQL执行结果
// 如果超时或者SQL执行发生错误，输出false
// 如果是SELECT语句，输出查询结果集；如果是UPDATE、INSERT、DELETE语句，输出受影响的行数
var_dump($sqlResult);

2020-11-19更新

如果你使用的是PHP 7.2(含)以上的版本，则可以使用MYSQLI_OPT_READ_TIMEOUT常量（注意跟libmysql的那个MYSQL_OPT_READ_TIMEOUT不是同一个常量，多了一个字母I）来控制超时时间了，代码示例：

$conn = new mysqli();
$conn->options(MYSQLI_OPT_READ_TIMEOUT, 1); // 超时时间设置为1秒
$conn->real_connect('localhost', 'user', 'password', 'db_name', 3306);

2024-03-29更新

如果你使用的是 5.7.8 版本以上的MySQL，还可以在MySQL端通过max_execution_time配置项来设置SQL最长执行时间，这个配置项的默认值是 0（单位毫秒），0代表不限制执行时长。

查询：

SHOW VARIABLES LIKE 'max_execution_time';        // SESSION级别
SHOW GLOBAL VARIABLES LIKE 'max_execution_time'; // 全局级别

设置：

SET SESSION MAX_EXECUTION_TIME = 1000;  // SESSION级别
SET GLOBAL MAX_EXECUTION_TIME = 1000;   // 全局级别

如果不想改动配置项，还可以通过SQL语句来设置：

SELECT /*+ MAX_EXECUTION_TIME(1) */ uid,name FROM users;

如果执行超时，会报以下错误：

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded 

注：

• 此配置项仅对只读的SELECT语句生效， INSERT ... SELECT 此类语句是没有作用的
• 不对SLEEP()函数生效，例如 SELECT SLEEP(10)

参考

https://blog.csdn.net/heiyeshuwu/article/details/5869813
https://www.php.net/manual/en/mysqlnd.config.php#ini.mysqlnd.net-read-timeout
https://www.laruence.com/2011/04/27/1995.html#comments
https://blog.csdn.net/wwaayyaaa/article/details/52639516
https://dev.mysql.com/downloads/connector/php-mysqlnd/