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