MySQL的系統變數max_execution_time小結

潇湘隐者發表於2024-05-28
MySql變數

引數介紹：

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

相關文章