33 | MySQL全表扫描会将内存打爆？（看了这篇你就赚）

一、前言

本篇主要分析全表扫描对server层、引擎层的影响。

二、全表扫描对server层的影响

2.1.server端对结果集的保存流程是什么样的？MySQL 是“边读边发的”这个特性会存在什么问题？sending to data和sending to client的不同点是什么？

• 存在net_buffer（net_buffer_length=16k）中，直到 net_buffer 写满，调用网络接口通过send_socket_buffer发出去。如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffe。客户端通过receive_socket_buffer获取数据。

• 客户端处理慢的话，就会造成服务端大量 State 的值一直处于“Sending to client”，就表示服务器端的网络栈写满了。 

• sending to data可能是处于执行器过程中的任意阶段，比如：一个锁等待的场景。仅当一个线程处于“等待客户端接收结果”的状态，才会显示"Sending to client"

三、全表扫描对 InnoDB 的影响

3.1.InnDB内部是怎么处理全表扫描返回的数据？Buffer Pool作用是什么？默认设置为多大？如何查看BP的效果？BP内部使用什么算法管理内存页？针对算法进行了什么优化？

• 内存的数据页是在 Buffer Pool (BP) 中管理的。 

• 1）加速查询。2）加速更新。

• 默认为物理内存的60%-80%

• 通过show engine innodb status查看BP一列

• LRU

• 为避免大量热点数据被换出内存页，将起分为Young区和Old区，比例为5:3，每次新访问的放到插入Old区头部，如果此数据在Old区存在时间超过1s就放到Young区头部，否则不动。好处就是：在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。

四、思考题

举出由于客户端的性能问题，对数据库影响更严重的例子吗？怎么优化的？

答：用mysqldump对业务db做逻辑备份保存在客户端，客户端是虚拟机，磁盘很快满了，导致server端出现sending to client状态，更糟糕的是业务db更新频繁，导致undo表空间变大，db服务堵塞，服务端磁盘空间不足。

--通俗点：server端不能发送，此时如果客户端A要读取的数据被其他线程频繁update，由于mvcc的实现，这个变更会记录到undo log，导致undo表空间变大，db服务堵塞，服务端磁盘空间不足

五、评论出英雄

5.1.“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中，对于执行器来说，都是“已经写出去了”。

5.2.mysql内存命中率和缓存命中率qcache hits是什么关系？

• 内存命中率通常说的是BP；qcache hits是指缓存命中率