深入解析MySQL大表全表扫描：为何不会导致OOM及InnoDB的性能优化策略

前言

mysql持久化存储的数据，一般都是上G，甚至上百G。如果对100G的大表做全表扫描，是否会导致OOM，把mysql搞崩呢？ 其实不会，让我们来详细拆解下这个问题。

全表扫描对mysql-server的影响

首先我们知道，要对一张表扫描，就需要mysql-server把数据加载到内存中，然后再返回客户端。但如果是一张大表，这就需要保证每个server实例的内存，变得和磁盘一样大，才能返回给客户端。

显然，这个要求是不合理的。

为了解决成本问题，我们考虑开辟一块buffer，专门用于网络数据的读写，实现边读边写。我们把这块buffer叫net_buffer。通过new_buffer，我们可以一边返回

全表扫描对mysql-Innodb的影响

首先，我们先看下查询过程：

1. 全表扫描会先去查询InnoDB内存。
2. 查询会先访问Buffer Pool，BP的作用是在内存中维护最新的数据。它配合redolog（磁盘日志），可以保证对磁盘的顺序写，提高了读写性能。
3. 如果Buffer Pool中存在，直接返回。
4. 如果不存在，把redolog的数据持久化到磁盘

容易想到，Buffer Pool的空间是有限的，而大表扫描，一但数据页都不在Buffer Pool里，如果不能命中缓存，还会导致效率变低。

因此，如果能合理的管理Buffer Pool的内存读写策略，决定了大表扫描效率到底有多快。

LRU算法对Buffer Pool的影响

InnoDB内存管理，用的是LRU算法，最近最少访问算法，会优先淘汰最久访问的数据。

而大表扫描场景，buffer Pool的数据只访问一次，会导致LRUcache里的数据频繁替换，需要频繁读取磁盘，增加了磁盘的IO成本。

Innodb对LRU算法的改进

一个简单思路是，除了数据本身的使用频率，再增加一个时间维度，用于判断新增数据之后用不用得上。如果新增的数据待的久，就前置他的优先级（优先给这个小伙子晋升）。如果新增数据走得快（小伙子没干多久，可能就要跑路了），那就不着急把它替换到队头（对组织的忠诚度不高，最多让他当个m比较靠前的，不能给m+ hhh）。

这么做的好处，能最大化保证内存的高效性。写到这，想到团队、组织的运转也如此类似，晋升、选拔人才，也得根据不同的维度，才能保证组织运转的高效性。

总结

1. MySQL全表扫描不会导致OOM：

   • MySQL通过net_buffer实现边读边写，避免一次性加载大表数据到内存。
   • net_buffer逐步从磁盘读取数据并发送给客户端，减少内存压力。

2. InnoDB的Buffer Pool优化：

   • 查询优先访问Buffer Pool，配合redo log提升读写性能。
   • 缓存命中时直接返回数据，未命中时从磁盘加载数据到Buffer Pool。

3. LRU算法的局限性：

   • 传统LRU算法在大表扫描场景下，频繁替换数据页，增加磁盘IO成本。
   • 数据只访问一次时，LRU缓存效率降低。

4. InnoDB对LRU算法的改进：

   • 引入时间维度，判断新增数据的未来使用概率。
   • 长期驻留的数据优先级提升，短期访问的数据不急于移动到LRU队列前部。
   • 减少频繁数据替换，最大化内存使用效率。