代码改变世界

MySQL的系统变量max_execution_time小结

2024-05-28 11:03  潇湘隐者  阅读(1696)  评论(0)  编辑  收藏  举报

参数介绍：

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