MySQL数据查询太多会OOM吗？

来源丨经授权转自 JavaEdge（ID：Java-Edge）

作者丨JavaEdgeJavaEdge

线上 MySQL 直接 Select 千万条的100G数据，服务器会裂开吗?

假设对某100G表t执行全表扫描，把扫描结果保存在客户端：

 # 该语句无任何判断条件，所以全表扫描，查到的每行都可直接放到结果集，然后返给客户端
 mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from t" > $target_file

1 那这“结果集”存在哪？

实际上MySQL读取、发送数据流程的如下：

1. 获取一行，写到net_buffer。该内存大小由参数net_buffer_length定义，默认16k

2. 继续获取行，直到写满net_buffer，发出去！

3. 若发送成功，则清空net_buffer，继续读取下一行，并写入net_buffer

4. 若发送返回EAGAIN或WSAEWOULDBLOCK，表示本地网络栈（socket send buffer）写满，进入等待。直到网络栈重新可写，再继续发送

以上过程执行流程图如下：

可以看出一个查询在发送过程中：占用MySQL内部的内存最大就是net_buffer_length，根本达不到100G。同理socket send buffer 也达不到，若socket send buffer被写满，就会暂停读数据。

所以MySQL是边读取边发送，若客户端接收得比较慢，会导致MySQL Server由于结果发不出去，该事务的执行时间就会变得很长。

经过分析，我们现在知道了，查询结果是分段发给客户端的，因此扫描全表，即使查询返回大量数据，也不会把内存搞满。

以上都是Server层的处理逻辑，InnoDB引擎层又是如何处理的呢？

2 InnoDB如何处理全表扫描？

内存中的数据页在Buffer Pool (后文简称为BP)管理，BP能够加速查询。由于WAL机制，当事务提交时，磁盘上的数据页是旧的，若这时立即就有个查询请求读该数据页，是不是得立即将redo log应用到数据页呢？并不！因为此时，内存数据页的结果就是最新的，直接读内存页即可，所以速度就很快啊，Buffer Pool在此就加速了查询。

但其实BP对查询的加速效果依赖于内存命中率。可使用如下命令查看当前BP命中率

show engine innodb status

一般稳定服务的线上系统，要保证响应性能，内存命中率得在99%以上。

InnoDB Buffer Pool的大小由参数 innodb_buffer_pool_size 确定，推荐设成可用物理内存的60%~80%。

3 InnoDB内存管理

使用最近最少使用 (Least Recently Used，LRU)算法，淘汰最久未使用的数据。若此时做个全表扫描，会咋样？若要扫描一个200G的表，而这个表是一个历史数据表，平时没有业务访问它。按此算法扫描，就会把当前BP里的数据全部淘汰，存入扫描过程中访问到的数据页的内容。即BP里主要放的是这个历史数据表数据。

对于一个正在做业务服务的库，这可不行呀。你会看到，BP内存命中率急剧下降，磁盘压力增加，SQL语句响应变慢。所以，InnoDB不能直接使用原生LRU。

改良版LRU

InnoDB按 5:3 把链表分成New区和Old区，改良版LRU执行流程：

• 首先，访问New区的D1，和常规LRU一样，将其移到链首

• 然后，访问一个新的不存在于当前链表的数据页，这时依旧是淘汰掉链尾数据页P但新插入的数据页DX，放在old处

• 处于old区的数据页，每次被访问时，都要判断：

• 若该数据页在LRU链表中存在时间＞1s，就把它移动到链表头部

• 若该数据页在LRU链表中存在时间＜1s，位置保持不变

  1s由参数innodb_old_blocks_time控制

这种改良是专门为处理类似全表扫描的操作。还是扫描上百G的历史数据表：

• 扫描过程中，需要新插入的数据页，都被放到old区域

• 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1s，因此还是保留在old区

• 再继续扫描后续数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（New区），很快就会被淘汰

可见该策略最大的收益，就是在扫描大表时，虽然也用到BP，但对young区全无影响，从而保证了Buffer Pool响应正常业务的查询命中率。

小结

MySQL采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集。所以，如果客户端读结果不及时，会堵住MySQL的查询过程，但是不会把内存打爆。

而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于InnoDB对LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。

全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

---

参考：

• [1]. https://cloud.tencent.com/developer/article/1767570

• [2]. https://juejin.cn/post/6854573221258199048

• [3].https://time.geekbang.org/column/article/79407

点分享

点点赞

点在看