关于mysql: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，无论什么类型的数据一拜访到都会移到结尾。