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> 

參考資料