5.7.8新增的一个很有的参数:max_execution_time

介绍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';

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20892230/viewspace-2127474/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20892230/viewspace-2127474/