MySQL做大表扫描，会不会数据库主机把内存用光？

逃离我推掉我的手 2023-06-13 03:24 77阅读 0赞

假设有这样一个场景：数据库主机内存有128G，需要扫描的数据库大表有200G，会不会数据库主机把内存用光？

**现在来分析这个扫描流程**：  
对于InnoDB引擎，表中的所有数据字段都保存在主键索引上，因此直接扫描表的主键索引，将符合条件的每一行数据放入结果集，然后返回给客户端。具体步骤如下：

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

因此，MySQL采用的是“边读边发”的策略，因此并不会把数据库主机内存用光。

MySQL作为一个存储系统，还有一个缓冲池（buffer pool）机制，缓冲池是用来缓存数据的，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO操作，起到加速访问的作用。buffer-pool的大小有参数 innodb\_buffer\_pool\_size决定，一般设为物理内存的60%~80%。那么进行大表扫描的时候，buffer-pool满了怎么办？

**缓冲池的淘汰策略**  
缓冲池一般按照页的大小来存储数据，InnoDB使用LRU(Least recently used)算法来管理这些数据页，LRU是用链表来实现的：把新放入缓冲池的页放到LRU的头部，作为最近访问的元素，从而最晚被淘汰，当访问数据的时候：

1.  页已经在缓冲池里，那就只做“移至”LRU头部的动作，而没有页被淘汰；
2.  页不在缓冲池里，除了做“放入”LRU头部的动作，还要做“淘汰”LRU尾部页的动作；

上面的LRU算法有两个缺陷是：预读失效和缓冲池污染

**预读**：磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K），如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。（这里使用的是一个“局部性原理”，即使用一些数据，大概率会使用附近的数据）。  
**预读失效**：由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据，称为预读失效。  
**缓冲池污染**：当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。

为了解决预读失效和缓冲池污染，InnoDB 对 LRU 算法做了改进：按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。**改进后的LRU算法如下**：

1.  如果要访问的数据页 在 young 区域，因此和优化前的 LRU 算法  
    一样，将其移到链表头部。
2.  如果要访问的数据页不在 young 区域，则依然是淘汰掉链表上的最后一个数据页，并把新的数据页插入old区的头部。
3.  处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：  
    3.1 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；  
    3.2 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb\_old\_blocks\_time 控制的。其默认值是 1000，单位毫秒。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]  
因此，一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在old 区域；扫描大表的过程中，对yong域完全没有影响，而且使用LRU算法，也不会导致buffer-pool不够用。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191113170129718.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw,size_16,color_FFFFFF,t_70