MySQL实战45讲第33讲

全表扫描如100G内存的主机对200G的大表做全表扫描会把数据库主机内存用光吗？逻辑备份也是全表扫描。答案不会。原理：

1.server层对全表扫描的处理：server端获取一行数据写到server端的net_buffer中（该内存默认16k，由参数net_buffer_length定义），重复获取行，写满net_buffer，调用网络接口发出去，发送成功清空net_buffer，继续读取下一行并写入net_buffer。
若发送函数返回EAGAIN或WSAEWOULDBLOCK，表示本地网络栈（socket send buffer）写满了，进入等待，直到网络栈重新可写再继续发送。
所以在发送过程中，占用的mysql内部内存最大就是net_buffer_length大小。而socket send buffer默认212992字节，是在操作系统的/proc/sys/net/core/wmem_default定义的（默认的TCP数据发送窗口大小（字节））。
mysql边读编发，因此客户端接收变慢导致socket receive buffer中的内容堆积满了，则服务端结果发不出去，导致事务执行时间变长，在show processlist能看到某个事务的状态处于Sending to client，服务端发不出去，导致net_buffer满了，不再读取行，所以这种情况可适当调大net_buffer_length，使得语句能够执行完，把查询结果放到net_buffer中，虽然还是Sending to client状态，但是对执行器来说语句执行完不会再占着资源（如MDL读锁）。
另一个知识点：客户端使用-quick参数，会使用mysql_use_result方法，读一行处理一行，假设业务逻辑复杂，每读一行数据后续处理很慢，导致客户端很久才取下一行数据，也会出现上述情况。
所以线上业务，若查询结果不会很多则建议使用mysql_store_result接口，将查询结果保存到应用程序本地内存。查询结果太多则使用mysql_use_result，避免应用程序OOM，mysql jdbc的fechSize()方法一次大查询然后客户端流式读取，也是用了mysql_use_result原理，这种场景也可采用分批读取策略。
另一个知识点：show processlist还有一种状态叫Sending data，查询语句进入执行阶段会把状态置为Sending data，然后发送执行结果列相关信息（meta data）给客户端，再继续执行语句流程，执行完成后把状态设置为空字符串，所以Sending data指执行器正在执行，并非正在发送数据。

2.InnoDB对全表扫描的处理：WAL机制，InnoDB内存数据页在Buffer Pool（BP）中管理，用于保存更新结果，配合redo log，避免随机写盘，WAL里Buffer Pool起到加速更新作用，Buffer Pool另一重要作用加速查询。
由于WAL机制，事务提交时，磁盘上的数据是旧的，查询请求读取该数据页，直接读最新的内存页即可，不需读磁盘，加速查询，因此有个重要指标：内存命中率，可通过show engine innodb status查看（Buffer pool hit rate），显示稳定服务要保证响应时间，内存命中率要>99%。InnodbDB Buffer Pool大小由参数innodb_buffer_pool_size确定，建议设置为可用物理内存60%-80%，但不会大于单机磁盘存储数据量，因此Buffer Pool满了以后要从磁盘读取新的数据页，需要淘汰旧的数据页，InnoDB内存管理使用链表存储数据页，使用最近改进版的最少使用算法LRU来淘汰最久未使用的数据，LRU将访问到的数据页移动到链表头，新从磁盘读取数据时，淘汰链表尾的数据页。改进版的LRU算法将链表分为5/8的young区域和3/8区域，young区域被访问的数据页移动到链表头，新从磁盘读取数据时，淘汰链表尾的数据页，新插入的数据页插入old区域，访问old区域的数据页要做判断，若该数据页在链表中存在时间>1s，移动到链表头部，<1s则保持不变，可设置参数innodb_old_blocks_time，默认是1000ms即1s。该策略让全表扫描加载到内存中的大量只访问一次的数据页一直放在old区域，方便及时淘汰并且不影响业务正常使用的young区域数据页，若不用改进版的LRU，原生的LRU在全表扫描时导致内存中的数据页都被淘汰，则正常业务用到的表都需要重新查询磁盘（内存命中率急剧下降），响应时间下降，且此时磁盘IO压力加大，性能进一步降低。因此不建议在业务系统做冷数据全部扫描（耗费IO资源）。

注：从上述可看出，链表old区域实际存放的是新近从磁盘读取的数据页，官方叫它old区域，确实容易造成误解。old区域的数据页存在1s以上且被访问到就会挪到young区域，有点类似jvm堆内存的老年代新生代的感觉。