我的主机内存只有100G，当初要全表扫描一个200G大表，会不会把DB主机的内存用光？

逻辑备份时，可不就是做整库扫描吗？若这样就会把内存吃光，逻辑备份不是早就挂了？

所以大表全表扫描，看起来应该没问题。这是为啥呢？

全表扫描对server层的影响

假如，咱们当初要对一个200G的InnoDB表db1. t，执行一个全表扫描。当然，你要把扫描后果保留在客户端，会应用相似这样的命令：

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

InnoDB数据保留在主键索引上，所以全表扫描实际上是间接扫描表t的主键索引。这条查问语句因为没有其余判断条件，所以查到的每一行都能够间接放到后果集，而后返回给客户端。

那么，这个“后果集”存在哪里呢？

服务端无需保留一个残缺后果集。取数据和发数据的流程是这样的：

  • 获取一行，写到net\_buffer。这块内存的大小是由参数net\_buffer\_length定义，默认16k
  • 反复获取行，直到net\_buffer写满，调用网络接口收回去
  • 若发送胜利，就清空net\_buffer，而后持续取下一行，并写入net\_buffer
  • 若发送函数返回EAGAIN或WSAEWOULDBLOCK，就示意本地网络栈（socket send buffer）写满了，进入期待。直到网络栈从新可写，再持续发送

查问后果发送流程：

可见：

  • 一个查问在发送过程中，占用的MySQL外部的内存最大就是net\_buffer\_length这么大，不会达到200G
  • socket send buffer 也不可能达到200G（默认定义/proc/sys/net/core/wmem\_default），若socket send buffer被写满，就会暂停读数据的流程

所以MySQL其实是“边读边发”。这意味着，若客户端接管得慢，会导致MySQL服务端因为后果发不进来，这个事务的执行工夫变长。

比方上面这个状态，就是当客户端不读socket receive buffer内容时，在服务端show processlist看到的后果。

服务端发送阻塞：

若看到State始终是“Sending to client”，阐明服务器端的网络栈写满了。

若客户端应用–quick参数，会应用mysql_use_result办法：读一行解决一行。假如某业务的逻辑较简单，每读一行数据当前要解决的逻辑若很慢，就会导致客户端要过很久才取下一行数据，可能就会呈现上图后果。

因而，对于失常的线上业务来说，若一个查问的返回后果不多，举荐应用mysql_store_result接口，间接把查问后果保留到本地内存。

当然前提是查问返回后果不多。如果太多，因为执行了一个大查问导致客户端占用内存近20G，这种状况下就须要改用mysql\_use\_result接口。

若你在本人负责保护的MySQL里看到很多个线程都处于“Sending to client”，表明你要让业务开发同学优化查问后果，并评估这么多的返回后果是否正当。

若要疾速缩小处于这个状态的线程的话，能够将net_buffer_length设置更大。

有时，实例上看到很多查问语句状态是“Sending data”，但查看网络也没什么问题，为什么Sending data要这么久？

一个查问语句的状态变动是这样的：

  • MySQL查问语句进入执行阶段后，先把状态设置成 Sending data
  • 而后，发送执行后果的列相干的信息（meta data) 给客户端
  • 再继续执行语句的流程
  • 执行实现后，把状态设置成空字符串

即“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。比方，你能够结构一个锁期待场景，就能看到Sending data状态。

读全表被锁：

Sending data状态

可见session2是在等锁，状态显示为Sending data。

  • 仅当一个线程处于“期待客户端接管后果”的状态，才会显示"Sending to client"
  • 若显示成“Sending data”，它的意思只是“正在执行”

所以，查问的后果是分段发给客户端，因而扫描全表，查问返回大量数据，并不会把内存打爆。

以上是server层的解决逻辑，在InnoDB引擎里又是怎么解决？

全表扫描对InnoDB的影响

InnoDB内存的一个作用，是保留更新的后果，再配合redo log，防止随机写盘。

内存的数据页是在Buffer Pool (简称为BP)治理，在WAL里BP起减速更新的作用。

BP还能减速查问。

因为WAL，当事务提交时，磁盘上的数据页是旧的，若这时马上有个查问来读该数据页，是不是要马上把redo log利用到数据页？

不须要。因为此时，内存数据页的后果是最新的，间接读内存页即可。这时查问无需读磁盘，间接从内存取后果，速度很快。所以，Buffer Pool能减速查问。

而BP对查问的减速成果，依赖于一个重要的指标，即：内存命中率。

能够在show engine innodb status后果中，查看一个零碎以后的BP命中率。个别状况下，一个稳固服务的线上零碎，要保障响应工夫符合要求的话，内存命中率要在99%以上。

执行show engine innodb status ，能够看到“Buffer pool hit rate”字样，显示的就是以后的命中率。比方下图命中率，就是100%。

若所有查问须要的数据页都可能间接从内存失去，那是最好的，对应命中率100%。

InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定，个别倡议设置成可用物理内存的60%~80%。

在大概十年前，单机的数据量是上百个G，而物理内存是几个G；当初尽管很多服务器都能有128G甚至更高的内存，然而单机的数据量却达到了T级别。

所以，innodb_buffer_pool_size小于磁盘数据量很常见。若一个 Buffer Pool满了，而又要从磁盘读入一个数据页，那必定是要淘汰一个旧数据页的。

InnoDB内存治理

应用的最近起码应用 (Least Recently Used, LRU)算法，淘汰最久未应用数据。

根本LRU算法

InnoDB治理BP的LRU算法，是用链表实现的：

  • state1，链表头部是P1，示意P1是最近刚被拜访过的数据页
  • 此时，一个读申请拜访P3，因而变成状态2，P3被移到最前
  • 状态3示意，这次拜访的数据页不存在于链表，所以须要在BP中新申请一个数据页Px，加到链表头。但因为内存已满，不能申请新内存。于是清空链表开端Pm数据页内存，存入Px的内容，放到链表头部

最终就是最久没有被拜访的数据页Pm被淘汰。

若此时要做一个全表扫描，会咋样？若要扫描一个200G的表，而这个表是一个历史数据表，平时没有业务拜访它。

那么，按此算法扫描，就会把以后BP里的数据全副淘汰，存入扫描过程中拜访到的数据页的内容。也就是说BP里次要放的是这个历史数据表的数据。

对于一个正在做业务服务的库，这可不行呀。你会看到，BP内存命中率急剧下降，磁盘压力减少，SQL语句响应变慢。

所以，InnoDB不能间接应用原始的LRU。InnoDB对其进行了优化。

改良的LRU算法

InnoDB按5:3比例把链表分成New区和Old区。图中LRU\_old指向的就是old区域的第一个地位，是整个链表的5/8处。即凑近链表头部的5/8是New区域，凑近链表尾部的3/8是old区域。

改良后的LRU算法执行流程：

  • 状态1，要拜访P3，因为P3在New区，和优化前LRU一样，将其移到链表头部 =》状态2
  • 之后要拜访一个新的不存在于以后链表的数据页，这时仍然是淘汰掉数据页Pm，但新插入的数据页Px，是放在LRU\_old处
  • 处于old区的数据页，每次被拜访的时候都要做如下判断：
  • 若该数据页在LRU链表中存在的工夫超过1s，就把它挪动到链表头部
  • 若该数据页在LRU链表中存在的工夫短于1s，地位放弃不变。1s是由参数innodb\_old\_blocks\_time管制，默认值1000，单位ms。

该策略，就是为了解决相似全表扫描的操作量身定制。还是扫描200G历史数据表：

  • 扫描过程中，须要新插入的数据页，都被放到old区域
  • 一个数据页外面有多条记录，这个数据页会被屡次拜访到，但因为是程序扫描，这个数据页第一次被拜访和最初一次被拜访的工夫距离不会超过1秒，因而还是会被保留在old区域
  • 再持续扫描后续的数据，之前的这个数据页之后也不会再被拜访到，于是始终没有机会移到链表头部（New区），很快就会被淘汰进来。

能够看到，这个策略最大的收益，就是在扫描这个大表的过程中，尽管也用到了BP，但对young区齐全没有影响，从而保障了Buffer Pool响应失常业务的查问命中率。

小结

MySQL采纳的是边算边发的逻辑，因而对于数据量很大的查问后果来说，不会在server端保留残缺的后果集。所以，如果客户端读后果不及时，会堵住MySQL的查问过程，然而不会把内存打爆。

而对于InnoDB引擎外部，因为有淘汰策略，大查问也不会导致内存暴涨。并且，因为InnoDB对LRU算法做了改良，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。

全表扫描还是比拟消耗IO资源的，所以业务高峰期还是不能间接在线上主库执行全表扫描的。