关于mysql:技术分享-MySQL-存储过程中的只读语句超时怎么办

30次阅读

共计 2852 个字符，预计需要花费 8 分钟才能阅读完成。

作者：杨涛涛

资深数据库专家，专研 MySQL 十余年。善于 MySQL、PostgreSQL、MongoDB 等开源数据库相干的备份复原、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相干技术支持、MySQL 相干课程培训等工作。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

MySQL 有一个参数叫 max_execution_time，用来设置只读语句执行的超时工夫，然而仅对独自执行的 select 语句无效；对于非独自执行的 select 语句，比方蕴含在存储过程、触发器等内置事务块里则不失效。官网手册上对这个参数解释如下：

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 /*+ max_execution_time(1000) */ sleep(2) from t1 limit 2;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

mysql> select /*+ set_var(max_execution_time=1000) */ 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 掉这样的性能不合适用在生产环境！因为你无奈预知其执行后果的时效性、上下文是否相干等特点。

正文完