虛擬主機域名注冊-常見問題 → 數據庫問題 → MySQL | |||||
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';
|
|||||
>> 相關文章 | |||||
沒有相關文章。 | |||||
發表評論 | |||||
網友評論 | |||||
沒有相關評論。 |