MySQL的系统变量max_execution_time小结

参数介绍:

MySQL社区版MySQL 5.7.8开始,新增了MAX_EXECUTION_TIME这个系统变量,它用来限制SQL语句的执行时间,确切来说是限制只读SELECT语句。如果查询语句的执行时长超过这个阈值,MySQL将自动停止该SQL语句的执行。如果其值为 0,表示不启用该超时限制功能。该参数/变量单位是毫秒(milliseconds),千万要注意,单位是毫秒。不是秒。

官方文档[1]关于系统变量max_execution_time的描述如下:

The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are not enabled.

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.or SELECT statements in stored programs.

注意事项:

  1. max_execution_time只对只读的select有效,对DDL及UPDATE、DELETE等DML操作不生效
  2. 只有顶层(即不是子查询)SQL受到影响
  3. 存储程序中的 SELECT 语句,不受影响。即使存储过程中SELECT语句使用MAX_EXECUTION_TIME提示也不受影响

生产环境较常见的情况是,由于没有正确配置JDBC等驱动,导致业务代码已经超时退出了与数据库的交互,但是数据库里依旧运行着发起的SQL,如果频繁发起重试,则慢SQL会越来越多,导致数据库负载高,影响稳定性及可用性。因此,建议设置参数max_execution_time来解决这种情况。但是,由于生产环境的复杂性,需要跟开发人员先协商,另外,这个值可能需要设置的相对大一些,避免影响业务。总之来说是谨慎使用。

其实在MySQL 5.6的时候,其他分支版本(从所查资料来看,最开始是twitter的分支版本[2]引入的,也被Percona Server 5.6分支版本并入)引入了系统变量max_statement_time来控制MySQL的最长执行时间。如果超过该系统变量的值,查询将会自动失败,默认是不限制。

不过MySQL社区版在MySQL 5.6并未引入这个系统变量,从社区版MySQL 5.7.4开始,才开始引入了系统变量max_statement_time。不过这个系统变量在MySQL 5.7.8被移除。具体可以参考官方文档[3]。其实这个系统变量之所以在MySQL 5.7.8被移除,是因为用系统变量max_execution_time替换了系统变量max_statement_time

  • max_statement_time: Statement execution timeout value. Added in MySQL 5.7.4.
  • max_statement_time: Statement execution timeout value. Removed in MySQL 5.7.8.

参数设置

全局级别

方法1.在参数文件my.cnf中设置

max_execution_time=20000

注意:这种方法,需要重启MySQL数据库实例才能生效。

方法2:

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

注意,设置全局系统变量后,对当前会话不生效,正确来说已经建立连接的会话是不生效的,它只对全局系统变量设置后,新建立的会话生效。如下所示,另外,重启后会系统变量值会失效。

mysql> show variables like 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> select  @@global.max_execution_time, @@session.max_execution_time;

如果想重启后也能生效,可以使用下面SQL设置

mysql> set persist max_execution_time=20000;
Query OK, 0 rows affected (0.01 sec)

会话级别

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

SQL语句级别

其实这里使用HINT来限制SELECT查询的最大时长,也的确是在语句级别执行限制。

SELECT /*+ MAX_EXECUTION_TIME(3000) */ * FROM table_name;

上述SQL语句表示,执行该查询,并设置执行时间为3秒,在超时时间到达时,MySQL将自动停止该查询的执行。注意:该选项只在使用InnoDB的默认存储引擎时有效,如果需要在其他存储引擎中使用该选项,请自行查阅文档或手册。

mysql>  SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM TEST;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql> 

参考资料

[1]

1: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time

[2]

2: https://github.com/twitter-forks/mysql/wiki/Statement-Timeout

[3]

3: https://dev.mysql.com/doc/refman/5.7/en/added-deprecated-removed.html

扫描上面二维码关注我
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.