大数据量查询不会把MySQL内存打爆，原因如下

取数据和发数据的流程（边读边发）

  1. MySQL服务端读取到一行数据后会将数据写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是16k
  2. 重复获取行，直到net_buffer写满，调用网络接口发出去
  3. 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer
  4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待，直到网络栈重新可写，再继续发送
    1. 如果MySQL服务的网络栈写满了，通过show processlist就可以看到State的值一直处于“Sending to client”

可以看到在整个流程中，MySQL内部最占内存的就是net_buffer；socket send buffer默认由/proc/sys/net/core/wmem_default控制，如果socket send buffer被写满，就会暂停读数据的流程。

可以通过修改使用的mysql方法来调整读取数据的策略

  • mysql_use_result方法是读一行处理一行

  • mysql_store_result方法是直接把查询结果保存到本地，如果一个查询的返回结果不会很多的话，建议使用这个接口

    • Java中使用JDBC的fetchSize()方法

      查询语句的状态变化

  • MySQL查询语句进入执行阶段后，首先把状态设置成“Sending data”，此状态不一定是指“正在发送数据”，而可能是出于执行器过程中的任意阶段

  • 发送执行结果的列相关的信息（meta data）给客户端
  • 再继续执行语句的流程
  • 执行完成后，把状态设置成空字符串

    当一个线程出于“等待客户端接收结果”的状态，才会显示“Sending to Client”，如果显示成“Sending data”，意思是正在执行

对InnoDB buffer pool的影响

在分析WAL机制的时候，说过了buffer pool是用来保存更新结果的，再配合redo log，就避免了随机写，buffer pool还有另一个作用就是加速查询，但是buff pool对查询的加速效果，依赖于内存命中率。
可以使用show engine innodb status查询buffer pool的命中率。

  1. Buffer pool hit rate 1000 / 1000 #表示命中率100%

buffer pool的大小由参数innodb_buffer_pool_size确定，具体大小可以通过具体数值 /1024/1024/1024算出(单位是G)，一般建议设置成可用物理内存的60%~80%。

淘汰机制

当buffer pool满了，而又要从磁盘读入一个数据页，肯定就要淘汰掉一个旧数据页，InnoDB内存管理是使用LRU算法，即最近最少使用，核心就是淘汰最久未使用的数据。
image.png

  • 假设目前bp中存在这些数据页，如果此次读取的数据页为P3，那就把P3移到前边， P1及P2后移
  • 假设要读取的数据页PX不在bp中，那就需要将tail的PM淘汰掉，将PX放到head。

存在的问题

当我们要扫描一个200G的表，而这个表是一个历史数据表，平时没有业务访问他，按照上边这种算法扫描，就会把当前的bp数据全部淘汰掉，bp中存放的都是这个历史数据表的数据，这对于正在正常运行的业务服务的库可不秒，会导致bp的内存命中率急剧下降，磁盘压力增加，SQL语句变慢，这就是InnoDB的缓存失效。

改进（分治思想）

InnoDB对上边的算法做了改进，按照5:3的比例把整个LRU链表分成了yong区域和old区域。
image.png

  • 当要访问yong区的P3时，和原来一样，将P3放到前边，P1及P2后移
  • 当要访问不在bp中的数据页PX时，就把PX放到old区的head处，PM也一样被淘汰
  • 处于old区的数据页每次被访问的时候都要做以下判断
    • 如果这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部
    • 如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变，1秒这个时间是由参数innodb_old_blocks_time控制的，默认值是1000毫秒。

这是策略是为了处理类似全表扫描的操作量身定制的。