33 | 我查这么多数据，会不会把数据库内存打爆

我的主机内存只有100G，现在要对一个200G的大表做全表扫描，会不会把数据库主机的内存用光？

答案是对大表的扫描肯定是没问题，要不逻辑备份的时候不就是做整库扫描吗，不是早就挂了。

全表扫描对server层的影响

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

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

InnoDB的数据是保存在主键索引上的。这儿会做全表扫描。

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

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

流程图如下所示：

从这个流程可以看到：

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

也就是说，MySQL是“边读边发的”,这个概念很重要。这意味着，如果客户端接收的慢，会导致MySQL服务端由于长时间发布出去，这个事务的执行时间变长。

比如下面这个状态，故意不去读socket receive buffer中的内容，然后再服务端show processlist看到的结果：

如果看到State的值一直处于“Sending to client”，就表示服务器端的网络栈写满了。

因此，对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，都建议使用mysql_store_result这个接口，直接把查询结果保存到本地内存。

另一方面，如果在MySQL里看到多个线程都处于"Sending to client"这个状态，就意味着要优化查询，并评估这么多的返回结果是否合理。

如果要快速减少处于这个状态的线程的话，将net_buffer_length参数设置为一个更大的值是一个可选方案。

与“Sending to client”长的很相似的一个状态是“Sending data”，要小心弄混。自己维护的实例上看到很多查询语句的状态是“Sending data”，但是查看网络也没什么问题，为什么Sending data要这么久？

实际上，一个查询语句的状态是这样的（略去了其他无光状态）：

• MySQL查询语句进入执行阶段后，首先把状态设置成“Sending data”；
• 然后，发送执行结果的列相关信息（meta data）给客户端；
• 再继续执行语句的流程；
• 执行完成后，把状态设置成空字符串。

比如一个连接处于锁等待的时候，状态就会显示为“Sending data”；

也就是说，仅当一个线程处于“等待客户端接收结果”的状态，才会显示“Sending to client”；而如果显示成“Sending data”，它的意思只是“正在执行”（可能处于执行器过程中的任意阶段）。

因此，扫描全表，查询返回大量数据，并不会把内存打爆。

在server层的逻辑清楚了，在InnoDB引擎里面又是怎么处理的呢？出苗全表会不会对引擎系统造成影响呢？

全表扫描对InnoDB的影响

InnoDB内存的一个作用是保存更新的结果，再配合redo  log，就避免了随机写盘。

内存的数据页是在Buffer Pool（BP）中管理的，在WAL里Buffer Pool起到了加速更新的作用。而实际上，Buffer Pool还有一个更重要的作用，就是加速查询。

而Buffer Pool对查询加速的效果，依赖于一个重要的指标，即：内存命中率。

可以通过show engine innodb status结果查看一个系统当前的BP命中率。

InnoDB Buffer Pool的大小是由参数innodb_buffer_pool_size确定的，一般建议设置成可用物理内存的60%~80%。

如果一个Buffer Pool满了，而又要从磁盘读入一个数据页，那肯定要淘汰一个旧数据页。

InnoDB内存管理用的是最近最少使用（Least Recently Used，LRU）算法，这个算法的核心就是淘汰最久未使用的数据。

InnoDB管理Buffer Pool的LRU算法是用链表来实现的。

原来的算法是基于整个链表淘汰，这样有缺点；所以InnoDB对LRU算法做了改进：

按照5:3的比例把整个LRU链表分成了young区和old区。考经链表头部的5/8是young区，靠近尾部的3/8是old区。

改进后算法流程：

1. 上图总状态1，要访问数据页P3，由于P3在young区域，因此和优化前的算法一样，将其移到链表头部，变成状态2.
2. 之后要访问一个新的不存在当前链表的数据页，这时候依然是淘汰数据页Pm,但是新插入的数据页Px，是放在LRU old处。
3. 处于old区的数据页，每次被访问的时候都要做下面的判断：

           若这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部；

           如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变。1秒这个时间是由参数innodb_old_blocks_time控制的。其默认值是1000，单位是毫秒。

这个策略，就是为了处理类似全表扫描的操作量身定制的。

 

问题：“Sending  to client”表示服务端的网络栈写满了，那不是应该加大socket send buffer吗？跟加大net_buffer_length有什么关系？net_buffer_length加再大，但scoket send buffer很小的话，网络栈不还是处于写满状态？

答：是这样的，net_buffer_length的最大值是1G，这个值比socket send buffer大（一般都是几M）;比如一个业务，假如它的平均查询结果都是10M，但是如果把net_buffer_length改成10M，就不会有“Send to client”的情况。虽然网络栈还是慢慢发的，但是那些没发完的都缓存在net_buffer中，对于执行器来说，都是“已经写出去了”。

 

上一篇：31 | 误删数据后除了跑路，还能怎么办？

下一篇：34 | 到底可不可以使用join？