33 | 我查这么多数据，会不会把数据库内存打爆？
该思维导图由 AI 生成，仅供参考
全表扫描对 server 层的影响
荧光笔
直线
曲线- 深入了解
- 翻译
- 解释
- 总结
本文深入分析了全表扫描对数据库内存的影响，重点讨论了全表扫描对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-2810106 
长杰遇到过一个场景，用mysqldump对业务db做逻辑备份保存在客户端，客户端是虚拟机，磁盘很快满了，导致server端出现sending to client状态，更糟糕的是业务db更新频繁，导致undo表空间变大，db服务堵塞，服务端磁盘空间不足。作者回复: 非常好，正是我要说明的一个场景呢，直接用你的例子放在下篇答疑部分哈
2019-01-28491
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-14868
XXL请教老师一个问题， 之前在开发工程中实际有碰到这样的业务，批量从MySQL中查询大量数据，每次通过限制起始+limit数量的来分批次查询，后来有同事推荐使用MySQL JDBC中的fetchSize()方法，不做分页通过一次大查询然后客户端流式读取来批量查询数据，这个内部原理是否就是文中所说的使用了mysql_use_result接口读一行处理一行实现的流式？或者也是mysql_store_result方式客户端边缓存边处理？请老师指教作者回复: 对，这种一般就是用mysql_use_result 各有优劣吧 一次性取的好处是，对服务端只全表，只扫描一遍；坏处是可能会出现大事务。 一般更常见的做法是，分批取，然后每一批拿到最大的一个id（主键值） 下一批查询的时候用 where Id > N 这种写法 好问题
2019-02-14261
geraltlaush如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。这个是指客户端的tcp滑动窗口处理没有及时确认，导致server端的网络协议栈没有多余的空间可以发送数据，导致server的处理线程停止从db读取数据发送给client，是这样理解吗作者回复: 对的
2019-01-3050
清风net_buffer 应该是针对每个请求线程单独分配的，还是共享net_buffer . 我的理解应该是每个线程一块。mysql 可以根据最大请求连接数，能够算出来mysql 使用net_buffer 的总大小。同时如果mysql 占用的内存不大，也将影响到Mysql 能够处理连接连接数的大小。 不知道这种猜测是否准确。 后面那个改进型的LRU 算法真的非常好，就跟JVM 中年轻带 老年代的内存区域划分和淘汰机制一样。在做系统设计的时候可以把这种设计应用一下。作者回复: 你的理解是对的，每个线程(session)一个
2019-03-30236
老杨同志本身是研发没过这种经历。猜一种吧 如果客户端A性能慢，迟迟不去读取socket receive buffer，server端就不能发送，此时如果客户端A要读取的数据被其他线程频繁update，由于mvcc的实现，这个变更会记录到undo log，大量的日志会不会使io飙升？可能比较极端才会吧。如果此时客户端性能恢复，服务端要读取最新数据，并通过undo log计算较早的版本，是不是要也占用大量的cpu资源或者io资源？谢谢老师作者回复: 👍 再考虑下都是update的情况 😆
2019-01-28328- Zzz林老师，有几个问题想请教以下： 1、哪种查询语句下MySQL 是“边读边发的”的呢？对于order by这种语句肯定是需要先全部拿到内存再做排序处理最后返回结果。 2、MySQL是怎么判断出可以“边读边发的”，是不是看下语句是否带order by这种关键字？ 3、我有办法知道该执行语句是否“边读边发的”吗？
作者回复: 这三个问题其实是同一个 “边读边发”的意思是，算出来的结果才能发 像order by，得先排序得到结果，然后才发出去，如果读了数据直接发，那肯定不行，那是错误的结果。 所以要排序了以后再发，这时候就需要中间数据结构，sort buffer
2019-01-28722 
冰点18InnoDB改进的LRU算法，如果遇到连续两次的全表扫描，会不会就把young区的3/5给覆盖掉了？因为两次扫描时间间隔会超过一秒？作者回复: 会的
2019-04-01420
ipofssMySQL是“边读边发”的，所以对于一个大查询，不会在server层把数据库内存打爆。 而对于innodb内部，也使用了改进的LRU算法，去使用内存，所以也不会把内存打爆。 老师，有个问题： 既然数据是“边读边发”的，对于一个读请求，如果时间太长了，而没有处理完，另外一个写请求进来了，如何保证前面的读请求不会读到脏数据？ 我的理解是MVCC控制的，只去读取当时的数据，即使后来进行了数据的增、删、改，但是读的时候，只去读取当时的那个版本。作者回复: 理解正确的👌
2020-03-1614
