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