MySQL数据库读超时/SELECT查询超时 杂记

本文环境 阿里云RDS MySQL 8.0.34

当客户端向MySQL数据库发送一条SQL之后,由于SQL很慢很慢,它会在什么时候结束呢?
查看 max_execution_time 变量值

mysql> show variables like 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+

查看官方文档

如果 max_execution_time = 0 则SELECT语句的执行不会超时, 直到查询结果返回.

进行模拟实验, 通过 root 用户登录到数据库.(一个用于查看监控数据,一个用于执行慢查询)

mysql> select * from information_schema.processlist where user='root' order by id asc;
+--------+------+----------------------+------+---------+------+-----------+--------------------------------------------------------------------------------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE     | INFO                                                                           |
+--------+------+----------------------+------+---------+------+-----------+--------------------------------------------------------------------------------+
| 125901 | root | 183.94.140.242:17228 | NULL | Query   |    0 | executing | select * from information_schema.processlist where user='root' order by id asc |
| 125905 | root | 183.94.140.242:16471 | db1  | Sleep   |   38 |           | NULL                                                                           |
+--------+------+----------------------+------+---------+------+-----------+--------------------------------------------------------------------------------+

接下来在其中一个客户端执行以下语句, 模拟耗时查询

mysql> select * from operation_record where record_id=460156845005578240 and sleep(120);

而在另一个客户端执行如下监控语句(监控数据库连接的情况), 可以看到 ID = 125905 的客户端(即上面那个客户端)在执行耗时查询.

mysql> select * from information_schema.processlist where user='root' order by id asc;
+--------+------+----------------------+------+---------+------+------------+----------------------------------------------------------------------------------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE      | INFO                                                                             |
+--------+------+----------------------+------+---------+------+------------+----------------------------------------------------------------------------------+
| 125901 | root | 183.94.140.242:17228 | NULL | Query   |    0 | executing  | select * from information_schema.processlist where user='root' order by id asc   |
| 125905 | root | 183.94.140.242:16471 | db1  | Query   |   49 | User sleep | select * from operation_record where record_id=460156845005578240 and sleep(120) |
+--------+------+----------------------+------+---------+------+------------+----------------------------------------------------------------------------------+

经过120秒之后, 查询会正常返回.

我们把以上流程的慢查询语句在 MySQLWorkbench 工具里执行.

同样也可以监控到该慢查询

mysql> select * from information_schema.processlist where user='root' order by id asc;
+--------+------+----------------------+------+---------+------+------------+----------------------------------------------------------------------------------------------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE      | INFO                                                                                         |
+--------+------+----------------------+------+---------+------+------------+----------------------------------------------------------------------------------------------+
| 125901 | root | 183.94.140.242:17228 | NULL | Query   |    0 | executing  | select * from information_schema.processlist where user='root' order by id asc               |
| 125905 | root | 183.94.140.242:16471 | db1  | Sleep   |  687 |            | NULL                                                                                         |
| 125934 | root | 183.94.140.242:16963 | NULL | Sleep   |  232 |            | NULL                                                                                         |
| 125940 | root | 183.94.140.242:17078 | db1  | Query   |   12 | User sleep | select * from operation_record where record_id=460156845005578240 and sleep(120) LIMIT 0, 50 |
+--------+------+----------------------+------+---------+------+------------+----------------------------------------------------------------------------------------------+

可是在经过30秒之后, 客户端收到了错误响应

而且从监控中也发现少了一个客户端连接(ID = 125940 不见了)

mysql> select * from information_schema.processlist where user='root' order by id asc;
+--------+------+----------------------+------+---------+------+-----------+--------------------------------------------------------------------------------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE     | INFO                                                                           |
+--------+------+----------------------+------+---------+------+-----------+--------------------------------------------------------------------------------+
| 125901 | root | 183.94.140.242:17228 | NULL | Query   |    0 | executing | select * from information_schema.processlist where user='root' order by id asc |
| 125905 | root | 183.94.140.242:16471 | db1  | Sleep   |  727 |           | NULL                                                                           |
| 125934 | root | 183.94.140.242:16963 | NULL | Sleep   |  272 |           | NULL                                                                           |
+--------+------+----------------------+------+---------+------+-----------+--------------------------------------------------------------------------------+

是因为 MySQLWorkbench 这个工具设置了默认读超时的最大值 30 秒.

如果把这个默认超时时间设置大于120秒,那么慢查询就可以执行到120秒返回结果,而不会在30秒的时候出现异常了.

需要重新打开一个新的查询窗口或者重新打开MySQLWorkbench工具

有时候,需要控制查询的最大执行时长,可以通过 MAX_EXECUTION_TIME(N) 控制.

# 设置最大执行时长 10 秒
mysql> select /*+ MAX_EXECUTION_TIME(10000) */ * from operation_record where record_id=460156845005578240 and sleep(120);# 10 秒之后,会返回如下错误
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

如果客户端不主动断开连接, 如果客户端不主动设置最大执行时长, 而数据库端设置最大执行时长.

把 RDS MySQL max_execution_time 值修改成 60 秒

客户端重连数据库,再次查看 max_execution_time

mysql> show variables like 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 60000 |
+--------------------+-------+

已生效

再次执行慢查询

mysql> select * from operation_record where record_id=460156845005578240 and sleep(120);                           # 60 秒之后,会返回如下错误
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

【总结】 当一个SQL语句在慢查询的执行过程中
1.如果客户端主动断开连接, 则数据库连接会消失.毕竟断开了连接
2.如果客户端设置了SELECT语句最大执行时长, 则数据库连接依然在, 如果查询耗时超过设置的最大执行时长,语句将被中断,返回给客户端如下错误
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
3.如果数据库端设置最大执行时长,则数据库连接依然在, 如果查询耗时超过设置的最大执行时长,语句将被中断,返回给客户端如下错误
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

另外 如果客户端开启一个事务之后, 长时间没有再与数据库有过'沟通', 比如很长时间没有再向数据库发送DML语句, 那么经过 wait_timeout/interactive_timeout 时间之后, 数据库会主动断开该连接, 数据库连接消失,事务会被回滚. 好在客户端的程序都有连接池, 连接池进行活性检测.

个人站点 https://www.infuq.com