前言
当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/