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