MySQL学习笔记-12-全表扫描

问题

数据库主机内存只有 100G，对一个 200G 的大表做全表扫描，会不会导致数据库主机OOM？

例子

对一个 200G 的 InnoDB 表 db1. t，执行一个全表扫描，把扫描结果保存在客户端

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

全表扫描server层

全表扫描执行流程

1、获取一行，写到 net_buffer （每个session一个）中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
2、重复获取行，直到 net_buffer 写满，调用网络接口发出去。
3、如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
4、如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

全表扫描问题

1、一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；
2、socket send buffer 也不可能达到 200G（默认定义 /proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。（由于socket send buffer内存是每个连接单独限制的，所以不会对服务端网路造成太大影响，当然如果并发很多查询，可能会影响网络。）
3、MySQL 是“边读边发的”，这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。（客户端为了快速处理接收的数据，默认是先把接收的数据缓存到本地内存，再做处理，这样做可以不拖慢MySQL服务器）
4、对于一个查询，执行器拿到的所有结果，如果可以一次性放入net_buffer, 对于执行器来说就意味着“全都写出去了”，也就不会有 sending to client 状态。 只有当查询的结果，不能够全部放入net_buffer，需要等net_buffer里的内容清空后再继续放入后续的结果，这时候状态才是显示 sending to client。 当查询结果可以全部放入net_buffer, 执行器也不管 net_buffer是否发送给 socket send buffer，都认为执行完了。
5、对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，建议使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存。

查询语句的状态变化

1、MySQL 查询语句进入执行阶段后，首先把状态设置成“Sending data”；
2、然后，发送执行结果的列相关的信息（meta data) 给客户端；
3、再继续执行语句的流程；
4、执行完成后，把状态设置成空字符串。

全表扫描引擎层

InnoDB 内存最近最少使用 (Least Recently Used, LRU) 算法

InnoDB 管理 Buffer Pool 的 LRU 算法，是用链表来实现的。

1、链表头部是 P1，表示 P1 是最近刚刚被访问过的数据页；假设内存里只能放下这么多数据页；
2、这时候有一个读请求访问 P3，因此变成状态 2，P3 被移到最前面；
3、状态 3 表示，这次访问的数据页是不存在于链表中的，所以需要在 Buffer Pool 中新申请一个数据页 Px，加到链表头部。但是由于内存已经满了，不能申请新的内存。于是，会清空链表末尾 Pm 这个数据页的内存，存入 Px 的内容，然后放到链表头部。
4、从效果上看，就是最久没有被访问的数据页 Pm，被淘汰了。

算法问题

按照这个算法扫描，会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。会导致Buffer Pool 的内存命中率急剧下降，磁盘压力增加，SQL 语句响应变慢。

InnoDB 对 LRU 算法的改进

在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。
LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。
靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。

改进后的 LRU 算法执行流程

1、要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
2、之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
3、处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
——如果这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
——如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

改进后的 LRU 算法的操作逻辑

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

新策略虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。
旧的原来就在young的，还是在young； 
新插入的，都在old；这样young始终都是那些热点数据；
普通的lru，无论什么类型的数据一访问到都会移到开头。