mysql 5.6 及以后，有語句執行超時時間變量，用于在服務端對 select 語句進行超時時間限制;

mysql 5.6 中，名為： max_statement_time (毫秒)

mysql 5.7 以后，改成： max_execution_time (毫秒)

超過這個時間，mysql 就終止 select 語句的執行，客戶端拋異常：

1907: Query execution was interrupted, max_execution_time exceeded.

介紹5.7.8新增的一個很有的參數:max_execution_time

這個參數是控制select的時間,能有效控制在主庫的慢查詢情況.

如下例子:

mysql> show variables like 'max_execution_time';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| max_execution_time | 7000  |

+--------------------+-------+

1 row in set (0.00 sec)

mysql> select sleep(5);

+----------+

| sleep(5) |

+----------+

|        0 |

+----------+

1 row in set (5.00 sec)

mysql> select sleep(8);

+----------+

| sleep(8) |

+----------+

|        1 |

+----------+

1 row in set (7.00 sec)

mysql> select count(*) from t2 where tatus ='wudagewfsldfs';

+----------+

| count(*) |

+----------+

| 12582912 |

+----------+

1 row in set (5.60 sec)

mysql> set max_execution_time=3000;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t2 where tatus ='wudagewfsldfs';

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

mysql> select count(*) from t2;

+----------+

| count(*) |

+----------+

| 12582933 |

+----------+

1 row in set (2.03 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t2 set tatus='12dwsd' where id =623990;

Query OK, 1 row affected (0.29 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count(*) from t2 where tatus ='wudagewfsldfs';

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

mysql> commit;

Query OK, 0 rows affected (0.07 sec)

mysql> select count(*) from t2 where tatus ='wudagewfsldfs' for update;

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

設置了超時時間為7s(max_execution_time參數的單位是ms),如果在這個時間范圍內,就返回正確的查詢結果,如果超過這個時間,整個select也就只執行7s,超過7s,就終止了該查詢

后面又設置為3s,得到同樣的結果;

這個參數在控制業務高峰期或者讀寫都在一臺實例上的慢查詢,可以將時間設置為2s,超過2s直接讓他終止.

不過,要結合實際業務來控制,別讓業務執行不下去了..

select /*+ max_execution_time(3000)*/ count(*) from t2 where tatus='wudagewfsldfs';