MySQL的系统变量max_execution_time小结

寒夜星空

参数介绍：

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

1. The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are not enabled.
   
2. 
   
3. max_execution_time applies as follows:
   
4. 
   
5. 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.
   
6. 
   
7. 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.
   
8. 
   
9. max_execution_time is ignored for SELECT statements in stored programs.or SELECT statements in stored programs.

复制代码

注意事项：

• max_execution_time只对只读的select有效，对DDL及UPDATE、DELETE等DML操作不生效
  
• 只有顶层（即不是子查询）SQL受到影响
  
• 存储程序中的 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：

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

复制代码

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

1. mysql> show variables like 'max_execution_time';
   
2. +--------------------+-------+
   
3. | Variable_name      | Value |
   
4. +--------------------+-------+
   
5. | max_execution_time | 0     |
   
6. +--------------------+-------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. mysql> 
   
10. mysql> select  @@global.max_execution_time, @@session.max_execution_time;

复制代码

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

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

复制代码

会话级别

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

复制代码

SQL语句级别

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

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

复制代码

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

1. mysql>  SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM TEST;
   
2. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
   
3. 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
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源:https://www.cnblogs.com/kerrycode/p/18217492
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！