MySQL数据查询太多会OOM服务器裂开吗?InnoDB如何处理全表扫描?

2022-02-2717:23:49数据库教程Comments1,630 views字数 1935阅读模式

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

假设对某100G表t执行全表扫描,把扫描结果保存在客户端:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

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

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

实际上MySQL读取、发送数据流程的如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

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

    MySQL数据查询太多会OOM服务器裂开吗?InnoDB如何处理全表扫描?

  2. 继续获取行,直到写满net_buffer,发出去!
  3. 若发送成功,则清空net_buffer,继续读取下一行,并写入net_buffer
  4. 若发送返回EAGAIN或WSAEWOULDBLOCK,表示本地网络栈(socket send buffer)写满,进入等待。直到网络栈重新可写,再继续发送

以上过程执行流程图如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

MySQL数据查询太多会OOM服务器裂开吗?InnoDB如何处理全表扫描?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

可以看出一个查询在发送过程中:占用MySQL内部的内存最大就是net_buffer_length,根本达不到100G。同理socket send buffer 也达不到,若socket send buffer被写满,就会暂停读数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

所以MySQL是边读取边发送,若客户端接收得比较慢,会导致MySQL Server由于结果发不出去,该事务的执行时间就会变得很长。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

经过分析,我们现在知道了,查询结果是分段发给客户端的,因此扫描全表,即使查询返回大量数据,也不会把内存搞满。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

以上都是Server层的处理逻辑,InnoDB引擎层又是如何处理的呢?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

MySQL数据查询太多会OOM服务器裂开吗?InnoDB如何处理全表扫描?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

2 InnoDB如何处理全表扫描?

内存中的数据页在Buffer Pool (后文简称为BP)管理,BP能够加速查询。由于WAL机制,当事务提交时,磁盘上的数据页是旧的,若这时立即就有个查询请求读该数据页,是不是得立即将redo log应用到数据页呢?并不!因为此时,内存数据页的结果就是最新的,直接读内存页即可,所以速度就很快啊,Buffer Pool在此就加速了查询。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

但其实BP对查询的加速效果依赖于内存命中率。可使用如下命令查看当前BP命中率文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

show engine innodb status

一般稳定服务的线上系统,要保证响应性能,内存命中率得在99%以上。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

InnoDB Buffer Pool的大小由参数 innodb_buffer_pool_size 确定,推荐设成可用物理内存的60%~80%。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

3 InnoDB内存管理

使用最近最少使用 (Least Recently Used,LRU)算法,淘汰最久未使用的数据。若此时做个全表扫描,会咋样?若要扫描一个200G的表,而这个表是一个历史数据表,平时没有业务访问它。按此算法扫描,就会把当前BP里的数据全部淘汰,存入扫描过程中访问到的数据页的内容。即BP里主要放的是这个历史数据表数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

对于一个正在做业务服务的库,这可不行呀。你会看到,BP内存命中率急剧下降,磁盘压力增加,SQL语句响应变慢。所以,InnoDB不能直接使用原生LRU。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

改良版LRU文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

MySQL数据查询太多会OOM服务器裂开吗?InnoDB如何处理全表扫描?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

InnoDB按 5:3 把链表分成New区和Old区,改良版LRU执行流程:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

  • 首先,访问New区的D1,和常规LRU一样,将其移到链首
  • 然后,访问一个新的不存在于当前链表的数据页,这时依旧是淘汰掉链尾数据页P但新插入的数据页DX,放在old处
  • 处于old区的数据页,每次被访问时,都要判断:
    • 若该数据页在LRU链表中存在时间>1s,就把它移动到链表头部
    • 若该数据页在LRU链表中存在时间<1s,位置保持不变

      1s由参数innodb_old_blocks_time控制

这种改良是专门为处理类似全表扫描的操作。还是扫描上百G的历史数据表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

  • 扫描过程中,需要新插入的数据页,都被放到old区域
  • 一个数据页里面有多条记录,这个数据页会被多次访问到,但由于顺序扫描,这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1s,因此还是保留在old区
  • 再继续扫描后续数据,之前的这个数据页之后也不会再被访问到,于是始终没有机会移到链表头部(New区),很快就会被淘汰

可见该策略最大的收益,就是在扫描大表时,虽然也用到BP,但对young区全无影响,从而保证了Buffer Pool响应正常业务的查询命中率。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

来源 | JavaEdge (ID:JavaEdge)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html


参考:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html

  • [1]. https://cloud.tencent.com/developer/article/1767570
  • [2]. https://juejin.cn/post/6854573221258199048
  • [3].https://time.geekbang.org/column/article/79407
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/23308.html
  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/23308.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定