文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL 存储过程中的只读语句超时怎么办?

2024-12-01 16:03

关注

max_execution_time applies as follows:

The global max_execution_time value provides the default for the session value for new connections. The session value applies to SELECT executions executed within the session that include no MAX_EXECUTION_TIME(*N*) optimizer hint or for which N is 0.

max_execution_time applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

max_execution_time is ignored for SELECT statements in stored programs.

那对这种非单独出现的 select 语句,该如何控制超时时间呢?

先来看下参数 max_execution_time 设置后的效果。此参数设置后,select 语句如果执行时间过长,会直接被 cancel 掉,并且报错,如下所示:

mysql> set @@max_execution_time=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(2) from t1 limit 1;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

或者是采用直接加 Hint 的方式,也能限制 select 语句的执行时间: 下面两种方式都能起到限制 select 语句执行时间的作用。

mysql> select  sleep(2) from t1 limit 2;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

mysql> select sleep(2) from t1 limit 2;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

那如果把这条 select 语句封装在存储过程内部,按照手册上对参数 max_execution_time 的解释,则不生效。比如新建一个存储过程 sp_test :

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_test`$$

CREATE DEFINER=`admin`@`%` PROCEDURE `sp_test`()
BEGIN
select sleep(2) from t1 limit 1;
END$$

DELIMITER ;

重新设置 max_execution_time 值为1秒:调用存储过程 sp_test , 可以正常执行,select 语句并没有被 cancel 掉!

mysql> call sp_test;
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 rows in set (2.01 sec)

Query OK, 0 rows affected (2.01 sec)

那如何解决这个问题呢?

为了更方便大家测试,把语句 select sleep(2) from t1 limit 1 改为 select sleep(2000) from t1 limit 1 。既然 MySQL 层面有这样的限制,那只能从非 MySQL 层面来想办法。最直接有效的就是写个脚本来主动 cancel 掉 select 语句。脚本如下:

root@ytt-normal:/home/ytt/script# cat kill_query 
#!/bin/sh
QUERY_ID=`mysql -ss -e "select id from information_schema.processlist where user='admin' and db='ytt' and time>10 and regexp_like(info,'^select','i')"`
if [ $QUERY_ID ];then
echo "kill query $QUERY_ID"
mysql -e "kill query $QUERY_ID"
fi

完后把脚本放到 crontab 或者 MySQL 自己的 event 里来定时执行即可。单独执行脚本效果如下:

root@ytt-normal:/home/ytt/script# ./kill_query 
kill query 50

除了自己编写脚本,还有一个工具可以实现类似的效果,它包含在我们熟知的 Percona-toolkit 工具箱里,叫 pt-kill 。

pt-kill 工具可以根据各种触发条件来执行指定动作:比如 cancel 掉指定 SQL 语句、kill 掉指定 session 等。所以完全可以使用 pt-kill 工具来实现 select 语句超时被自动 cancel 掉。如下所示:pt-kill 工具会在后台一直运行,监听 MySQL 进程,一旦触发条件被激活,即可执行相应动作。

root@ytt-normal:/home/ytt/script# pt-kill --match-db=ytt --match-user=admin --match-host=%  \--match-info='^select' --victims=all --busy-time='10s' --print --kill-query

# 2022-08-15T17:29:03 KILL QUERY 50 (Query 11 sec) select sleep(2000) from t1 limit 1

有一点需要注意:select 语句超时自动 cancel 掉这样的功能不适宜用在生产环境!因为你无法预知其执行结果的时效性、上下文是否相关等特点。​

来源:今日头条内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯