全表扫描对内存的影响

全表扫描对 server 层的影响

• 假如扫描的是InnoDB引擎表，那么全表扫描会扫描所有的主键索引。将所查到的每一行符合条件的数据放到结果集里面，然后返给客户端。
• 服务端并不需要保存一个完整的结果集，取数据和发数据流程如下：
  1.获取一行，写到net_buffer中，它的内存的大小由参数net_buffer_length确定，默认16k；
  2.重复获取行，直到net_buffer写满，调用网络接口发出去；
  3.如果发送成功，清空net_buffer，然后继续取下一行，重复1、2过程；
  4.如果发送函数返回EAGAIN 或者 WSAEWOULDBLOCK，此时表示“本地网络栈”（socket send buffer）写满了，进入等待（为什么会写满，因为没发出去）。直到网络栈重新可写，再继续发送。
• 由取、发数据流程可知：
  1.一个查询在发送过程中，占用MySQL的内部内存最大就是net_buffer_length，它是有上限控制的，并不会肆意占用内存；
  2.socket send buffer （默认定义/proc/sys/net/core/wmem_default）也是有上限控制的，如果它被写满，就会暂停读数据的流程。
• socket send buffer写满表现及使用建议：
  1.写满表现，show processlist 中State值一直处于“Sending to client”的进程；
  2.可能导致写满的原因是客户端处理数据不及时，比如客户端使用-quick参数，此时会使用mysql_use_result方法，这个方法边读边处理，对于客户端来说是提速的，但是如果业务逻辑较复杂，每读一行处理逻辑较慢，可能就导致socket send buffer写满
  3.建议如果一个查询的返回结果不很多，就使用mysql_store_result接口，将查询结果保存到本地内存，之后再做处理。缓解socket send buffer压力
• Sending data 和 Sending to client：
  MySQL查询语句进入执行阶段后首先把状态置为“Sending data”，所以它的意思只是“正在执行”。

全表扫描对InnoDB的影响

• InnoDB内存作用之一就是保存更新结果，配合redo log，避免随机写盘。
• 内存的数据页是在Buffer Pool(BP)中管理的，在WAL(Write Ahead Log)机制中，BP起到加速更新的作用。实际上，它还有一个更重要的作用，就是加速查询，怎么回事呢？
  由于有WAL机制，当事务提交时，磁盘上的数据页是旧的，如果这时马上有一个查询要来读这个数据页，如果此时内存中的该数据页是最新的（也有可能被刷没了），直接读取即可，并不需要马上把redo log应用到数据页。这样直接从内存中拿结果，不需要读磁盘，起到加速作用。
• BP对查询的加速效果，依赖于一个重要指标——内存命中率，可通过show engine innodb status 查询，“Buffer pool hit rate”显示的就是当前命中率。
• 内存命中率很难做到100%，因为BP大小小于磁盘数据量是很常见的。如果一个BP满了，而又要从磁盘读入一个数据页，肯定要淘汰一个旧的数据页。BP的大小由参数innodb_buffer_pool_size确定，一般建议设置成可用物理内存的60%~80%。
• InnoDB淘汰旧数据页的规则（内存管理）使用的是（LRU，Least Recently Used）算法（进行改进），它的核心就是淘汰最久未使用的数据。
• 为什么不直接使用LRU算法？
  假设使用LRU算法，扫描一个大的冷表（平时不怎么访问），按照这个算法，会把当前BP里的数据全部淘汰，而存入扫描过程中访问到的数据页的内容，也就是说存了一堆正常业务用不到的缓存数据，大大降低了内存命中率，磁盘压力增加，导致SQL语句响应变慢，所以不能直接使用LRU算法。
• InnoDB中的LRU做了什么改进？
  InnoDB在实现上，按5:3比例把整个LRU链表分成了young和old两个区，young靠近头部，old靠近尾部。
• 改进后的变化？
  1.如果要查询的数据页在young区，这时和改进前机制一样，将该数据移到头部。
  2.如果要查询的数据页不存在与链表中，此时要淘汰old的尾节点，而新插入的数据页，要放在old区域的头部。
  3.处于old区域的数据页，每次被访问时做判断，如果数据页在LRU链表中存活时间超过1s，就把它移动到young头部；反之，位置保持不变。1s这个限定，由参数innodb_lod_blocks_time控制，默认1000，单位毫秒。
• 改进后对全表扫描的影响？
  1.扫描过程中，需要插入的新的数据页都被放到old区；
  2.一个数据页里有多条记录，这个数据页会被多次访问到，但是由于是顺序扫描，这个数据页第一次和最后一次被访问的时间间隔不会超过1s，也就是说它是无法上位的，还会被保留在old区（冷宫😝）；
  3.再继续扫描后续数据，之前的数据页也不会被访问到，于是没有机会移到链表头部，很快就会被淘汰
  注意，查询时间超过1s这个限定，有2个契机打破，第一个是顺序查询本数据页的时间超过1s，第二个是在本页被淘汰前再次被查询，而这个时间超过1s。
• 整个优化策略相当于在LRU链表中划分一小块给全表扫描这类操作，保证了对young区域没有影响，但是，也没那么完美，如果被扫描的表足够大，那么业务正常访问的数据页是没有机会进入young区域的，也就是说你得young区域在这个大表扫描的过程中无法被及时更新，只能通过后续的查询请求慢慢恢复内存命中率。

此文由 慧鑫coming 总结自《MySQL实战45讲》--林晓斌