MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224874 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

33 | 我查这么多数据,会不会把数据库内存打爆?

重启等待回滚结束或切换主备加速终止逻辑
高峰期避免在线上主库执行全表扫描
全表扫描仍然耗费IO资源
InnoDB对LRU算法做了改进
大查询不会将内存用光
全表扫描对Buffer Pool的影响
改进的LRU算法
Buffer Pool满时的淘汰策略
Buffer Pool命中率要保持在99%以上
内存命中率对查询性能的影响
Buffer Pool加速查询
调整net_buffer_length参数
优化查询结果,评估返回结果是否合理
使用mysql_store_result接口保存查询结果
客户端接收慢会导致事务执行时间变长
MySQL是"边读边发的"
Socket send buffer不会达到200G
MySQL内存占用不会达到200G
总结
InnoDB Buffer Pool的LRU算法
MySQL查询结果发送流程
数据库内存使用问题

该思维导图由 AI 生成,仅供参考

我经常会被问到这样一个问题:我的主机内存只有 100G,现在要对一个 200G 的大表做全表扫描,会不会把数据库主机的内存用光了?
这个问题确实值得担心,被系统 OOM(out of memory)可不是闹着玩的。但是,反过来想想,逻辑备份的时候,可不就是做整库扫描吗?如果这样就会把内存吃光,逻辑备份不是早就挂了?
所以说,对大表做全表扫描,看来应该是没问题的。但是,这个流程到底是怎么样的呢?

全表扫描对 server 层的影响

假设,我们现在要对一个 200G 的 InnoDB 表 db1. t,执行一个全表扫描。当然,你要把扫描结果保存在客户端,会使用类似这样的命令:
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
你已经知道了,InnoDB 的数据是保存在主键索引上的,所以全表扫描实际上是直接扫描表 t 的主键索引。这条查询语句由于没有其他的判断条件,所以查到的每一行都可以直接放到结果集里面,然后返回给客户端。
那么,这个“结果集”存在哪里呢?
实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的:
获取一行,写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的,默认是 16k。
重复获取行,直到 net_buffer 写满,调用网络接口发出去。
如果发送成功,就清空 net_buffer,然后继续取下一行,并写入 net_buffer。
如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

本文深入分析了全表扫描对数据库内存的影响,重点讨论了全表扫描对server层和InnoDB引擎的影响,并提出了相应的优化建议。文章首先解答了全表扫描对内存的影响,指出MySQL内部的内存占用不会超过net_buffer_length的大小,并介绍了查询结果的发送流程和状态变化。强调了客户端接收速度对MySQL服务端执行时间的影响,并建议对于大查询结果,使用mysql_store_result接口将结果保存到本地内存。此外,文章还解释了“InnoDB引擎中全表扫描对系统的影响”,并给出了相应的建议。在讨论InnoDB内存管理时,详细介绍了Buffer Pool的LRU算法和改进,以及对查询的加速效果和内存命中率的重要性。最后,总结了大查询不会导致内存暴涨,且对Buffer Pool的影响可控,但仍需注意全表扫描对IO资源的耗费。整体而言,本文深入剖析了全表扫描对数据库内存的影响及相应的优化策略,对读者了解全表扫描的影响和优化具有重要参考价值。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(92)

  • 最新
  • 精选
  • 700
    置顶
    老师,您好。根据文章内容,提炼如下信息: 如果你看到 State 的值一直处于“Sending to client”,就表示服务器端的网络栈写满了。 如何处理? 1)使用 mysql_store_result 这个接口,直接把查询结果保存到本地内存。 2)优化查询结果,并评估这么多的返回结果是否合理。 3)而如果要快速减少处于这个状态的线程的话,将 net_buffer_length 参数设置为一个更大的值是一个可选方案。 对于第3)方案不是很懂,“Sending to client” 表示服务器端的网路栈写满了,那不是应该加大 socket send buffer 吗?跟加大 net_buffer_length 有什么关系?net_buffer_length 加再大,但 socket send buffer 很小的话,网络栈不还是处于写满状态?

    作者回复: 好问题👍 很好的思考👍 是这样的,net_buffer_length 的最大值是 1G,这个值比 socket send buffer大(一般是几M) 比如假设一个业务,他的平均查询结果都是10M (当然这个业务有有问题,最终是要通过业务解决) 但是如果我把net_buffer_length 改成10M,就不会有“Sending to client” 的情况。虽然网络栈还是慢慢发的,但是那些没发完的都缓存在net_buffer中,对于执行器来说,都是“已经写出去了”。

    2019-01-28
    10
    106
  • 长杰
    遇到过一个场景,用mysqldump对业务db做逻辑备份保存在客户端,客户端是虚拟机,磁盘很快满了,导致server端出现sending to client状态,更糟糕的是业务db更新频繁,导致undo表空间变大,db服务堵塞,服务端磁盘空间不足。

    作者回复: 非常好,正是我要说明的一个场景呢,直接用你的例子放在下篇答疑部分哈

    2019-01-28
    4
    91
  • IceGeek17
    老师你好,几个问题: 按照文中所述,net_buffer是属于MySQL Server层的,在InnoDB引擎层,会使用buffer pool (以page的形式),也就是一个查询所占用的内存是: net_buffer + buffer pool里相关的page页 是不是可以这么理解? 当net_buffer写满,会调用网络接口发出去,net_buffer里的内容是如何发给socket send buffer的, 是一行一行的扔给socket send buffer,还是把net_buffer 里的内容一下子全部扔给 socket send buffer ? 文中说发送成功然后清空net_buffer, 这里net_buffer是如何清空的,是等net_buffer里的内容全部发送成功,然后一次性清理,还是发送成功一部分清理一部分? 看了置顶的700问题和回复,几点疑问: 对于一个查询,执行器拿到的所有结果,如果可以一次性放入net_buffer, 对于执行器来说是不是意味着“全都写出去了”,也就不会有 sending to client 状态? 只有当查询的结果,不能够全部放入net_buffer,需要等net_buffer里的内容清空后再继续放入后续的结果,这时候状态才是显示 sending to client ? 当查询结果可以全部放入net_buffer, 执行器也不管 net_buffer是否发送给 socket send buffer,都认为执行完了 ? 是不是这么理解? 对buffer pool,当通过LRU 淘汰数据页的时候,如果此时该页的内容是新的(也就是磁盘上的内容是老的),是不是需要强制先走一个刷脏页的流程,等脏页刷完了,然后才能淘汰该数据页?

    作者回复: 1. “是一行一行的扔给socket send buffer,还是把net_buffer 里的内容一下子全部扔给 socket send buffer ?” ---- net_buffer写满,一起发,然后清空net_buffer,组装下一批 。好问题 2. 跟上一个问题同一个答案; 3. “对于一个查询,执行器拿到的所有结果,如果可以一次性放入net_buffer, 对于执行器来说是不是意味着“全都写出去了”,也就不会有 sending to client 状态?” ----是的 4. 是的 5. 对,这个就是我们其他文章中介绍的,“带着邻居节点一起刷”的那个阶段。

    2019-02-14
    8
    68
  • XXL
    请教老师一个问题, 之前在开发工程中实际有碰到这样的业务,批量从MySQL中查询大量数据,每次通过限制起始+limit数量的来分批次查询,后来有同事推荐使用MySQL JDBC中的fetchSize()方法,不做分页通过一次大查询然后客户端流式读取来批量查询数据,这个内部原理是否就是文中所说的使用了mysql_use_result接口读一行处理一行实现的流式?或者也是mysql_store_result方式客户端边缓存边处理?请老师指教

    作者回复: 对,这种一般就是用mysql_use_result 各有优劣吧 一次性取的好处是,对服务端只全表,只扫描一遍;坏处是可能会出现大事务。 一般更常见的做法是,分批取,然后每一批拿到最大的一个id(主键值) 下一批查询的时候用 where Id > N 这种写法 好问题

    2019-02-14
    2
    61
  • geraltlaush
    如果客户端读结果不及时,会堵住 MySQL 的查询过程,但是不会把内存打爆。这个是指客户端的tcp滑动窗口处理没有及时确认,导致server端的网络协议栈没有多余的空间可以发送数据,导致server的处理线程停止从db读取数据发送给client,是这样理解吗

    作者回复: 对的

    2019-01-30
    50
  • 清风
    net_buffer 应该是针对每个请求线程单独分配的,还是共享net_buffer . 我的理解应该是每个线程一块。mysql 可以根据最大请求连接数,能够算出来mysql 使用net_buffer 的总大小。同时如果mysql 占用的内存不大,也将影响到Mysql 能够处理连接连接数的大小。 不知道这种猜测是否准确。 后面那个改进型的LRU 算法真的非常好,就跟JVM 中年轻带 老年代的内存区域划分和淘汰机制一样。在做系统设计的时候可以把这种设计应用一下。

    作者回复: 你的理解是对的,每个线程(session)一个

    2019-03-30
    2
    36
  • 老杨同志
    本身是研发没过这种经历。猜一种吧 如果客户端A性能慢,迟迟不去读取socket receive buffer,server端就不能发送,此时如果客户端A要读取的数据被其他线程频繁update,由于mvcc的实现,这个变更会记录到undo log,大量的日志会不会使io飙升?可能比较极端才会吧。如果此时客户端性能恢复,服务端要读取最新数据,并通过undo log计算较早的版本,是不是要也占用大量的cpu资源或者io资源?谢谢老师

    作者回复: 👍 再考虑下都是update的情况 😆

    2019-01-28
    3
    28
  • Zzz
    林老师,有几个问题想请教以下: 1、哪种查询语句下MySQL 是“边读边发的”的呢?对于order by这种语句肯定是需要先全部拿到内存再做排序处理最后返回结果。 2、MySQL是怎么判断出可以“边读边发的”,是不是看下语句是否带order by这种关键字? 3、我有办法知道该执行语句是否“边读边发的”吗?

    作者回复: 这三个问题其实是同一个 “边读边发”的意思是,算出来的结果才能发 像order by,得先排序得到结果,然后才发出去,如果读了数据直接发,那肯定不行,那是错误的结果。 所以要排序了以后再发,这时候就需要中间数据结构,sort buffer

    2019-01-28
    7
    22
  • 冰点18
    InnoDB改进的LRU算法,如果遇到连续两次的全表扫描,会不会就把young区的3/5给覆盖掉了?因为两次扫描时间间隔会超过一秒?

    作者回复: 会的

    2019-04-01
    4
    20
  • ipofss
    MySQL是“边读边发”的,所以对于一个大查询,不会在server层把数据库内存打爆。 而对于innodb内部,也使用了改进的LRU算法,去使用内存,所以也不会把内存打爆。 老师,有个问题: 既然数据是“边读边发”的,对于一个读请求,如果时间太长了,而没有处理完,另外一个写请求进来了,如何保证前面的读请求不会读到脏数据? 我的理解是MVCC控制的,只去读取当时的数据,即使后来进行了数据的增、删、改,但是读的时候,只去读取当时的那个版本。

    作者回复: 理解正确的👌

    2020-03-16
    14
收起评论
显示
设置
留言
92
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部