大查询会不会把内存打爆

最新推荐文章于 2024-03-31 15:40:23 发布

基层搬砖的Panda

最新推荐文章于 2024-03-31 15:40:23 发布

阅读量370

点赞数 1

分类专栏： mysql 文章标签： mysql 网络 java

本文链接：https://blog.csdn.net/weixin_46935110/article/details/124987256

版权

mysql 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

假设你的主机内存只有100G，要对一个200G的大表做全表扫描，会不会把主机的内存用光了？答案是，不会。这么想，在做整库的逻辑备份的时候不就是全表扫描吗，所以不会有问题。但是，全表扫描对server层会有什么影响吗？今天我们就来聊聊这个话题。

全表扫描对server层的影响

使用以下命令对表t进行全表扫描并保存到客户端：

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

回忆之前我们学过的索引的知识，InnoDB 的数据是保存在主键索引树的叶子节点的，所以，全表扫描实际上就是扫描整个主键索引树，因为我们的命令中没有其他条件，所以，所有的查询结果都会返回给客户端。

server端取数据和发数据的流程如下：

获取一行，写到 net_buffer 中，这块内存大小由参数 net_buffer_length 控制，默认是16K；
重复获取一行数据，直到 net_buffer 写满，调用网络接口发出去；
如果发送成功，清空 net_buffer，然后取下一行，重复步骤1、2;
如果发送函数返回 EAGAIN 或者 WSAEWOULDBLOCK，表示本地网络栈（socket send buffer）写满了，进入等待状态，直到可以继续写。

以上流程说明：
一个查询在返回数据过程中，server端不需要保存全部的结果集，占用内存的最大为 net_buffer size；server端是边读边发的，如果客户端接收的慢，会导致该事务在server端的执行时间边长。

如果server端的网络栈被写满了，可以在server端用show processlist看到该事务的state为：“sending to client”。
在这里插入图片描述
如果你维护的Mysql实例里出现了大量的sending to client，这个时候你就需要联系业务开发人员，让他们优化查询结果。如果想快速减少这种状态，可以调大 net_buffer_length，最大1个G。

与**“sending to client”比较类似的“sending data”**状态，并不一定指正在发送数据，它有可能处于执行器过程中的任意阶段。比如：锁等待

在这里插入图片描述

可以看到 session B明显为锁等待，但是状态确实sending data。

小结：

全表扫描结果是分段发给客户端，因此查询大量的数据并不会把内存打爆；
“sending to client” 状态表示server端在等待客户端接收数据；
“sending data”状态表是正在执行。

全表扫描对InnoDB 引擎层的影响

全表扫描在server层的流程我们清楚了，那么在InnoDB引擎层又是怎么样的呢？下面我们就来看看。
前面我们在一条更新语句的执行流程文章里，学习过WAL，也就是先写日志，更新内存再刷磁盘。从而避免了随机写。在InnoDB中，内存的数据页都是在Buffer Pool（BP）中管理的。除了加速更新，BP还能加速读性能。如果有事务刚提交，马上就读，由于最新的数据还在内存中，所以直接就返回了。而BP对查询结果的加速效果取决于一个重要指标：内存命中率。可以使用 show engine innodb status 查看系统的内存命中率，一个稳定的线上服务系统，内存命中率需要在99%以上。

InnoDB 内存管理用的是最近最少使用（LRU）算法，算法的核心就是淘汰最久未使用的数据。
下图是一个LRU算法的基本模型。
在这里插入图片描述
InnoDB BP LRU算法采用链表实现。

在state 1里，链表的头部是P1，表示P1是最近刚刚被访问过的数据页；并且假设内存中就只能放下这么多数据页；
这个时候有个请求访问P3，就把P3移到链表的头部，变成state 2；
状态3表示，此次访问的数据页不在链表中，所以需要BP重新申请一个新的数据页Px，加入到链表的头部。但是由于内存已经满了，需要淘汰的链表末尾的数据页Pn。

这个算法乍一看没什么问题，但是，假设我们要扫描一个200G的表，而且这个表是一个历史数据表，平时没业务访问它。如果直接采用上述LRU算法，就会把BP中的数据全部淘汰掉，此时，BP里存放的全是这个历史数据。对于一个正在提供线上服务的库来说，此时，BP的内存命中率急剧下降，磁盘压力增加，SQL响应速度变慢。

所以，InnoDB 是在LRU的基础上做了优化：
在这里插入图片描述
InnoDB 在实现上，按照 5:3 的比例把整个LRU链表分成了young 和 old 两个区域。图中LRU old 指向的就是old区域的第一个位置，在整个链表的5/8处。

优化后的LRU算法流程如下：

状态1要访问P3，因为P3在young区域，直接移动到链表头部即可，变成状态2；
之后放的数据页不存在于当前LRU链表，新申请数据页Px，插入到LRU old位置，并淘汰数据页Pn；
每次访问处于old区域的数据页的时候都会做以下判断：改数据页在LRU链表中存在的时间是否超过1s？如果超过1s，就把该数据页移动到链表头部；如果没超过1s，位置保持不变。其中，1s这个时间由参数 innodb_old_blocks_time 控制。默认1000ms，也就是1s。

还是刚才那个历史数据表：