关于java:MySQL数据查询太多会OOM吗

我的主机内存只有 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 的主键索引。这条查问语句因为没有其余判断条件，所以查到的每一行都能够间接放到后果集，而后返回给客户端。

那么，这个“后果集”存在哪里呢？
服务端无需保留一个残缺后果集。取数据和发数据的流程是这样的：

1. 获取一行，写到 「net\_buffer」。这块内存的大小是由参数「net\_buffer\_length」 定义，默认 16k
2. 反复获取行，直到 「net\_buffer」 写满，调用网络接口收回去
3. 若发送胜利，就清空「net\_buffer」，而后持续取下一行，并写入「net\_buffer」
4. 若发送函数返回 「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 要这么久？
一个查问语句的状态变动是这样的：

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

即“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. 状态 1，要拜访 P3，因为 P3 在 New 区，和优化前 LRU 一样，将其移到链表头部 =》状态 2
2. 之后要拜访一个新的不存在于以后链表的数据页，这时仍然是淘汰掉数据页 Pm，但新插入的数据页 Px，是放在 「LRU\_old」 处
3. 处于 old 区的数据页，每次被拜访的时候都要做如下判断：

• 若该数据页在 LRU 链表中存在的工夫超过 1s，就把它挪动到链表头部
• 若该数据页在 LRU 链表中存在的工夫短于 1s，地位放弃不变。1s 是由参数 「innodb\_old\_blocks\_time」 管制，默认值 1000，单位 ms。

该策略，就是为了解决相似全表扫描的操作量身定制。还是扫描 200G 历史数据表：
4. 扫描过程中，须要新插入的数据页，都被放到 old 区域
5. 一个数据页外面有多条记录，这个数据页会被屡次拜访到，但因为是程序扫描，这个数据页第一次被拜访和最初一次被拜访的工夫距离不会超过 1 秒，因而还是会被保留在 old 区域
6. 再持续扫描后续的数据，之前的这个数据页之后也不会再被拜访到，于是始终没有机会移到链表头部（New 区），很快就会被淘汰进来。

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

小结

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

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

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