浅谈MySQL数据查询太多会OOM吗

逻辑备份时，可不就是做整库扫描吗？若这样就会把内存吃光，逻辑备份不是早就挂了？
所以大表全表扫描，看起来应该没问题。这是为啥呢？

全表扫描对server层的影响

假设，我们现在要对一个200G的InnoDB表db1. t，执行一个全表扫描。当然，你要把扫描结果保存在客户端，会使用类似这样的命令：

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

那么，这个“结果集”存在哪里呢？
服务端无需保存一个完整结果集。取数据和发数据的流程是这样的：

获取一行，写到net_buffer。这块内存的大小是由参数net_buffer_length定义，默认16k
重复获取行，直到net_buffer写满，调用网络接口发出去
若发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer
若发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送

查询结果发送流程

可见：

一个查询在发送过程中，占用的MySQL内部的内存最大就是net_buffer_length这么大，不会达到200G
socket send buffer 也不可能达到200G（默认定义/proc/sys/net/core/wmem_default），若socket send buffer被写满，就会暂停读数据的流程

所以MySQL其实是“边读边发”。这意味着，若客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长。

比如下面这个状态，就是当客户端不读socket receive buffer内容时，在服务端show processlist看到的结果。

服务端发送阻塞

若看到State一直是“Sending to client”，说明服务器端的网络栈写满了。

若客户端使用–quick参数，会使用mysql_use_result方法：读一行处理一行。假设某业务的逻辑较复杂，每读一行数据以后要处理的逻辑若很慢，就会导致客户端要过很久才取下一行数据，可能就会出现上图结果。

因此，对于正常的线上业务来说，若一个查询的返回结果不多，推荐使用mysql_store_result接口，直接把查询结果保存到本地内存。

当然前提是查询返回结果不多。如果太多，因为执行了一个大查询导致客户端占用内存近20G，这种情况下就需要改用mysql_use_result接口。

若你在自己负责维护的MySQL里看到很多个线程都处于“Sending to client”，表明你要让业务开发同学优化查询结果，并评估这么多的返回结果是否合理。

若要快速减少处于这个状态的线程的话，可以将net_buffer_length设置更大。

有时，实例上看到很多查询语句状态是“Sending data”，但查看网络也没什么问题，为什么Sending data要这么久？
一个查询语句的状态变化是这样的：

即“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。比如，你可以构造一个锁等待场景，就能看到Sending data状态。

读全表被锁：

session1	session2
begin select * from t where id=1 for update	启动事务
	select * from t lock in share mode (blocked)

Sending data状态

可见session2是在等锁，状态显示为Sending data。

所以，查询的结果是分段发给客户端，因此扫描全表，查询返回大量数据，并不会把内存打爆。