MySQL 常见超时参数解析与实战调优

Session 级别的 timeout 也是由这些全局参数控制，具体取值取决于客户端类型。下面从这些变量中挑几个常用的逐个分析。

连接握手阶段：connect_timeout

connect_timeout 指的是连接过程中 TCP 三次握手的超时时间。在 5.0.52 版本以后默认为 10 秒，之前版本默认是 5 秒。官方文档说明这是服务端等待客户端连接包的时间，超时后会返回 Bad handshake。

MySQL 的基本原理是有个监听线程循环接收请求，当有请求来时，创建线程（或者从线程池中取）来处理这个请求。由于 MySQL 连接采用 TCP 协议，在此之前势必需要进行 TCP 三次握手。TCP 三次握手成功之后，客户端会进入阻塞，等待服务端的消息。服务端这个时候会创建一个线程（或者从线程池中取一个线程）来处理请求，主要验证部分包括 host 和用户名密码验证。

由此可见，整个连接握手可能会有各种可能出错。所以这个 connect_timeout 值就是指这个超时时间了。可以简单测试下，运行下面的 telnet 命令会发现客户端会在 10 秒后超时返回：

telnet localhost 3306

在超时之前 MySQL 中该连接状态如下：

256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL 

空闲连接阶段：wait_timeout & interactive_timeout

这两个参数都是指不活跃的连接超时时间。连接线程启动的时候，wait_timeout 会根据是交互模式还是非交互模式被设置为这两个值中的一个。如果我们运行 mysql -uroot -p 命令登陆到 MySQL，wait_timeout 就会被设置为 interactive_timeout 的值。

如果在 wait_timeout 时间内没有进行任何操作，那么再次操作的时候就会提示超时，这时 MySQL Client 会重新连接。

我们可以做个小实验来观察这个机制：

mysql> set global interactive_timeout=3; -- 设置交互超时为 3 秒

重新进入 MySQL，这时候可以看到 wait_timeout 已经被设置为 3 秒：

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| ...                         | ...      |
| interactive_timeout         | 3        |
| ...                         | ...      |
| wait_timeout                | 3        |
+-----------------------------+----------+

这样，我们 3 秒后再执行其他命令，会提示如下错误：

mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 50 Current database: *** NONE ***

InnoDB 锁等待：innodb_lock_wait_timeout & innodb_rollback_on_timeout

这两个值是针对 InnoDB 引擎的，是 InnoDB 中行锁的等待超时时间，默认为 50 秒。如果超时，则当前语句会回滚。如果设置了 innodb_rollback_on_timeout，则会回滚整个事务，否则，只回滚事务等待行锁的这个语句。

同样来测试下（先创建一个 InnoDB 引擎的表 test，只有一列，列名为 a）：

mysql> CREATE TABLE `test` (
  `a` int primary key
) engine=innodb;

首先插入三条测试数据：

mysql> insert into test values(1),(2),(3);

当前 innodb_rollback_on_timeout=OFF，设置 innodb_lock_wait_timeout=1，我们开启两个事务：

事务 1：加行锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where a=2 for update;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.01 sec)

事务 2：请求行锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test where a=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from test where a=2;
## 请求行锁超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此时查询表内容，发现只剩下 2 和 3。如果这里我们显示的 rollback，则会回滚整个事务，保持 1、2、3 不变。

那么如果 innodb_rollback_on_timeout=ON，同样事务 2 会超时，但是这个时候如果我们 begin 开启新的事务，那么会回滚请求锁超时的整个事务，而不是像前面那样只回滚了超时的那条语句。

元数据锁：lock_wait_timeout

lock_wait_timeout 是元数据锁等待超时，任意锁元数据的语句都会用到这个超时参数，默认为一年。元数据锁可以保证事务可串行化，不管是 MyISAM 还是 InnoDB 引擎的表，只要是开始一个事务，就会获取操作表的元数据锁，这时候如果另一个事务要对表的元数据进行修改，则会阻塞直到超时。

测试例子：我们用一个 MyISAM 引擎的表 myisam_test 来测试。其中有一条记录 (1,1)，现在我们先开启一个事务，然后执行一个 select 语句。另外打开一个 session，然后执行表的元数据操作，如删除表，会发现操作阻塞直到 lock_wait_timeout 秒后提示超时。

第一个 Session：获取 metadata lock

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myisam_test;
+---+------+
| i | j    |
+---+------+
| 2 | 1    |
+---+------+
1 row in set (0.00 sec)

另一个 Session：删除表提示超时

mysql> drop table myisam_test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

其中更改表结构的元数据操作指令有如下这些：

• DROP TABLE t;
• ALTER TABLE t ...;
• LOCK TABLE t ... WRITE；

当然，对于 MyISAM 表的加锁以及并发插入等，相关原理可参考 MyISAM 表锁机制详解。

网络传输阶段：net_read_timeout & net_write_timeout

这两个参数在网络条件不好的情况下起作用。比如我在客户端用 load data infile 的方式导入很大的一个文件到中，然后中途用 iptables 禁用掉 MySQL 的 3306 端口，这个时候服务器端该连接状态是 reading from net，在等待 net_read_timeout 后关闭该连接。

同理，在程序里面查询一个很大的表时，在查询过程中同样禁用掉端口，制造网络不通的情况，这样该连接状态是 writing to net，然后在 net_write_timeout 后关闭该连接。

测试：我创建一个 120M 的数据文件 data.txt。然后登陆到 MySQL：

mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1

导入过程设置 iptables 禁用 3306 端口：

iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP

可以看到连接状态为 reading from net，然后经过 net_read_timeout 秒后关闭。

总结

经过几个实验可以发现，connect_timeout 在握手认证阶段（authenticate）起作用，interactive_timeout 和 wait_timeout 在连接空闲阶段（sleep）起作用，而 net_read_timeout 和 net_write_timeout 则是在连接繁忙阶段（query）或者网络出现问题时起作用。理解这些差异，能帮助我们在遇到连接断开或超时错误时快速定位根因。